425 lines
10 KiB
C#
425 lines
10 KiB
C#
using GoldPrinter.ExcelConstants;
|
||
using Microsoft.Office.Interop.Excel;
|
||
using System;
|
||
using System.Drawing;
|
||
using System.IO;
|
||
using System.Reflection;
|
||
|
||
namespace GoldPrinter
|
||
{
|
||
public class ExcelBase
|
||
{
|
||
private Application _xlApp;
|
||
|
||
private Workbook _xlWorkbook;
|
||
|
||
private bool _IsVisibledExcel;
|
||
|
||
private string _FormCaption;
|
||
|
||
private object oMissing = Missing.Value;
|
||
|
||
public Application Application
|
||
{
|
||
get
|
||
{
|
||
return this._xlApp;
|
||
}
|
||
}
|
||
|
||
public Workbook Workbooks
|
||
{
|
||
get
|
||
{
|
||
return this._xlWorkbook;
|
||
}
|
||
}
|
||
|
||
public bool IsVisibledExcel
|
||
{
|
||
get
|
||
{
|
||
return this._IsVisibledExcel;
|
||
}
|
||
set
|
||
{
|
||
this._IsVisibledExcel = value;
|
||
}
|
||
}
|
||
|
||
public string FormCaption
|
||
{
|
||
get
|
||
{
|
||
return this._FormCaption;
|
||
}
|
||
set
|
||
{
|
||
this._FormCaption = value;
|
||
}
|
||
}
|
||
|
||
public ExcelBase()
|
||
{
|
||
this._IsVisibledExcel = false;
|
||
this._FormCaption = "打印预览";
|
||
try
|
||
{
|
||
this._xlApp = new ApplicationClass();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw new ExceptionExcelCreateInstance("创建Excel类实例时错误，详细信息：" + ex.Message);
|
||
}
|
||
this._xlApp.DisplayAlerts = false;
|
||
}
|
||
|
||
public void Open()
|
||
{
|
||
try
|
||
{
|
||
this._xlWorkbook = this._xlApp.Workbooks.Add(this.oMissing);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw new ExceptionExcelOpen("打开Excel时错误，详细信息：" + ex.Message);
|
||
}
|
||
}
|
||
|
||
public void Open(string p_templateFileName)
|
||
{
|
||
if (File.Exists(p_templateFileName))
|
||
{
|
||
try
|
||
{
|
||
this._xlWorkbook = this._xlApp.Workbooks.Add(p_templateFileName);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw new ExceptionExcelOpen("打开Excel时错误，详细信息：" + ex.Message);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
this.Open();
|
||
}
|
||
}
|
||
|
||
public void Close()
|
||
{
|
||
this._xlApp.Workbooks.Close();
|
||
this._xlWorkbook = null;
|
||
this._xlApp.Quit();
|
||
this._xlApp = null;
|
||
this.oMissing = null;
|
||
GC.Collect();
|
||
}
|
||
|
||
public void ShowExcel()
|
||
{
|
||
this._xlApp.Visible = true;
|
||
}
|
||
|
||
public void PrintPreview()
|
||
{
|
||
this._xlApp.Caption = this._FormCaption;
|
||
this._xlApp.Visible = true;
|
||
try
|
||
{
|
||
this._xlApp.ActiveWorkbook.PrintPreview(this.oMissing);
|
||
}
|
||
catch
|
||
{
|
||
}
|
||
this._xlApp.Visible = this.IsVisibledExcel;
|
||
}
|
||
|
||
public void Print()
|
||
{
|
||
this._xlApp.Visible = this.IsVisibledExcel;
|
||
object value = Missing.Value;
|
||
try
|
||
{
|
||
this._xlApp.ActiveWorkbook.PrintOut(value, value, value, value, value, value, value, value);
|
||
}
|
||
catch
|
||
{
|
||
}
|
||
}
|
||
|
||
public bool SaveAs(string p_fileName, bool p_ReplaceExistsFileName)
|
||
{
|
||
bool result = false;
|
||
if (File.Exists(p_fileName))
|
||
{
|
||
if (p_ReplaceExistsFileName)
|
||
{
|
||
try
|
||
{
|
||
File.Delete(p_fileName);
|
||
result = true;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
string message = ex.Message;
|
||
}
|
||
}
|
||
}
|
||
try
|
||
{
|
||
this._xlApp.ActiveWorkbook.SaveCopyAs(p_fileName);
|
||
result = true;
|
||
}
|
||
catch
|
||
{
|
||
result = false;
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public Range GetRange(int p_rowIndex, int p_colIndex)
|
||
{
|
||
return this.GetRange(p_rowIndex, p_colIndex, p_rowIndex, p_colIndex);
|
||
}
|
||
|
||
public Range GetRange(int p_rowIndex, string p_colChars)
|
||
{
|
||
return this.GetRange(p_rowIndex, p_colChars, p_rowIndex, p_colChars);
|
||
}
|
||
|
||
public Range GetRange(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex)
|
||
{
|
||
return this._xlApp.get_Range(this._xlApp.Cells[p_startRowIndex, p_startColIndex], this._xlApp.Cells[p_endRowIndex, p_endColIndex]);
|
||
}
|
||
|
||
public Range GetRange(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars)
|
||
{
|
||
return this._xlApp.get_Range(p_startColChars + p_startRowIndex.ToString(), p_endColChars + p_endRowIndex.ToString());
|
||
}
|
||
|
||
public void MergeCells(Range p_Range)
|
||
{
|
||
p_Range.HorizontalAlignment = Constants.xlCenter;
|
||
p_Range.VerticalAlignment = Constants.xlCenter;
|
||
p_Range.WrapText = false;
|
||
p_Range.Orientation = 0;
|
||
p_Range.AddIndent = false;
|
||
p_Range.IndentLevel = 0;
|
||
p_Range.ShrinkToFit = false;
|
||
p_Range.MergeCells = false;
|
||
p_Range.Merge(this.oMissing);
|
||
}
|
||
|
||
public void InsertVPageBreaks(int p_rowIndex)
|
||
{
|
||
}
|
||
|
||
public void InsertHPageBreaks(int p_colIndex)
|
||
{
|
||
}
|
||
|
||
public void InsertHPageBreaks(string p_colChars)
|
||
{
|
||
}
|
||
|
||
public void InsertRow(int p_rowIndex)
|
||
{
|
||
Range range = this.GetRange(p_rowIndex, "A");
|
||
range.Select();
|
||
range.EntireRow.Insert(this.oMissing, Missing.Value);
|
||
}
|
||
|
||
public void InsertRow(int p_rowIndex, int p_templateRowIndex)
|
||
{
|
||
Range range = (Range)this._xlApp.Rows[p_templateRowIndex.ToString() + ":" + p_templateRowIndex.ToString(), this.oMissing];
|
||
range.Select();
|
||
range.Copy(this.oMissing);
|
||
this.InsertRow(p_rowIndex);
|
||
}
|
||
|
||
public void InsertColumn(int p_colIndex)
|
||
{
|
||
Range range = this.GetRange(1, p_colIndex);
|
||
range.Select();
|
||
range.EntireColumn.Insert(this.oMissing, Missing.Value);
|
||
}
|
||
|
||
public void InsertColumn(string p_colChars)
|
||
{
|
||
Range range = this.GetRange(1, p_colChars);
|
||
range.Select();
|
||
range.EntireColumn.Insert(this.oMissing, Missing.Value);
|
||
}
|
||
|
||
public void DeleteRow(int p_rowIndex)
|
||
{
|
||
Range range = this.GetRange(p_rowIndex, "A");
|
||
range.Select();
|
||
range.EntireRow.Delete(this.oMissing);
|
||
}
|
||
|
||
public void DeleteColumn(int p_colIndex)
|
||
{
|
||
Range range = this.GetRange(1, p_colIndex);
|
||
range.Select();
|
||
range.EntireColumn.Delete(this.oMissing);
|
||
}
|
||
|
||
public void DeleteColumn(string p_colChars)
|
||
{
|
||
Range range = this.GetRange(1, p_colChars);
|
||
range.Select();
|
||
range.EntireColumn.Delete(this.oMissing);
|
||
}
|
||
|
||
public void SetRowHeight(int p_rowIndex, float p_rowHeight)
|
||
{
|
||
Range range = this.GetRange(p_rowIndex, "A");
|
||
range.Select();
|
||
range.RowHeight = p_rowHeight;
|
||
}
|
||
|
||
public void SetColumnWidth(int p_colIndex, float p_colWidth)
|
||
{
|
||
Range range = this.GetRange(1, p_colIndex);
|
||
range.Select();
|
||
range.ColumnWidth = p_colWidth;
|
||
}
|
||
|
||
public void SetColumnWidth(string p_colChars, float p_colWidth)
|
||
{
|
||
Range range = this.GetRange(1, p_colChars);
|
||
range.Select();
|
||
range.ColumnWidth = p_colWidth;
|
||
}
|
||
|
||
public void SetFont(Range p_Range, System.Drawing.Font p_Font)
|
||
{
|
||
this.SetFont(p_Range, p_Font, Color.Black);
|
||
}
|
||
|
||
public void SetFont(Range p_Range, System.Drawing.Font p_Font, Color p_color)
|
||
{
|
||
p_Range.Select();
|
||
p_Range.Font.Name = p_Font.Name;
|
||
p_Range.Font.Size = p_Font.Size;
|
||
p_Range.Font.Bold = p_Font.Bold;
|
||
p_Range.Font.Italic = p_Font.Italic;
|
||
p_Range.Font.Strikethrough = p_Font.Strikeout;
|
||
p_Range.Font.Underline = p_Font.Underline;
|
||
}
|
||
|
||
public void SetBordersEdge(Range p_Range, BordersEdge p_BordersEdge)
|
||
{
|
||
this.SetBordersEdge(p_Range, p_BordersEdge, BordersLineStyle.xlContinuous, BordersWeight.xlThin);
|
||
}
|
||
|
||
public void SetBordersEdge(Range p_Range, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
|
||
{
|
||
p_Range.Select();
|
||
Border border = null;
|
||
switch (p_BordersEdge)
|
||
{
|
||
case BordersEdge.xlLineStyleNone:
|
||
p_Range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
p_Range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlLineStyleNone;
|
||
break;
|
||
case BordersEdge.xlLeft:
|
||
border = p_Range.Borders[XlBordersIndex.xlEdgeLeft];
|
||
break;
|
||
case BordersEdge.xlRight:
|
||
border = p_Range.Borders[XlBordersIndex.xlEdgeRight];
|
||
break;
|
||
case BordersEdge.xlTop:
|
||
border = p_Range.Borders[XlBordersIndex.xlEdgeTop];
|
||
break;
|
||
case BordersEdge.xlBottom:
|
||
border = p_Range.Borders[XlBordersIndex.xlEdgeBottom];
|
||
break;
|
||
case BordersEdge.xlDiagonalDown:
|
||
border = p_Range.Borders[XlBordersIndex.xlDiagonalDown];
|
||
break;
|
||
case BordersEdge.xlDiagonalUp:
|
||
border = p_Range.Borders[XlBordersIndex.xlDiagonalUp];
|
||
break;
|
||
case BordersEdge.xlInsideHorizontal:
|
||
border = p_Range.Borders[XlBordersIndex.xlInsideHorizontal];
|
||
break;
|
||
case BordersEdge.xlInsideVertical:
|
||
border = p_Range.Borders[XlBordersIndex.xlInsideVertical];
|
||
break;
|
||
}
|
||
if (border != null)
|
||
{
|
||
XlLineStyle xlLineStyle = XlLineStyle.xlContinuous;
|
||
switch (p_BordersLineStyle)
|
||
{
|
||
case BordersLineStyle.xlContinuous:
|
||
xlLineStyle = XlLineStyle.xlContinuous;
|
||
break;
|
||
case BordersLineStyle.xlDash:
|
||
xlLineStyle = XlLineStyle.xlDash;
|
||
break;
|
||
case BordersLineStyle.xlDashDot:
|
||
xlLineStyle = XlLineStyle.xlDashDot;
|
||
break;
|
||
case BordersLineStyle.xlDashDotDot:
|
||
xlLineStyle = XlLineStyle.xlDashDotDot;
|
||
break;
|
||
case BordersLineStyle.xlDot:
|
||
xlLineStyle = XlLineStyle.xlDot;
|
||
break;
|
||
case BordersLineStyle.xlDouble:
|
||
xlLineStyle = XlLineStyle.xlDouble;
|
||
break;
|
||
case BordersLineStyle.xlLineStyleNone:
|
||
xlLineStyle = XlLineStyle.xlLineStyleNone;
|
||
break;
|
||
case BordersLineStyle.xlSlantDashDot:
|
||
xlLineStyle = XlLineStyle.xlSlantDashDot;
|
||
break;
|
||
}
|
||
border.LineStyle = xlLineStyle;
|
||
XlBorderWeight xlBorderWeight = XlBorderWeight.xlThin;
|
||
switch (p_BordersWeight)
|
||
{
|
||
case BordersWeight.xlHairline:
|
||
xlBorderWeight = XlBorderWeight.xlHairline;
|
||
break;
|
||
case BordersWeight.xlMedium:
|
||
xlBorderWeight = XlBorderWeight.xlMedium;
|
||
break;
|
||
case BordersWeight.xlThick:
|
||
xlBorderWeight = XlBorderWeight.xlThick;
|
||
break;
|
||
case BordersWeight.xlThin:
|
||
xlBorderWeight = XlBorderWeight.xlThin;
|
||
break;
|
||
}
|
||
border.Weight = xlBorderWeight;
|
||
}
|
||
}
|
||
|
||
public void ClearBordersEdge(Range p_Range)
|
||
{
|
||
this.SetBordersEdge(p_Range, BordersEdge.xlLineStyleNone);
|
||
}
|
||
|
||
public string GetCellText(Range p_Range)
|
||
{
|
||
return p_Range.Text.ToString();
|
||
}
|
||
|
||
public void SetCellText(Range p_Range, string p_text)
|
||
{
|
||
p_Range.Cells.FormulaR1C1 = p_text;
|
||
}
|
||
}
|
||
}
|