Office2007尚未普及,Office2000~2003仍然是主流。本文介绍一些C#处理Excel得方法,都是自己封装的~
1. 添加COM组件的Reference
2. 引入名字空间
using Microsoft.Office.Interop.Excel
3. 层次结构
1个Application包含多个Workbook,1个Workbook包含多个Worksheet。
public class ExcelOperator
{
#region Fields
private ApplicationClass excOperator;
private Workbook wb;
private Worksheet[] wss;
private object oMissing = System.Reflection.Missing.Value;
#endregion Fields
public ExcelOperator()
{
this.excOperator = new ApplicationClass();
this.excOperator.SheetsInNewWorkbook = 1;
wb = this.excOperator.Workbooks.Add(oMissing);
this.CreateSheets(5);
}
……………………………
private void CreateWorkSheets(int iNumOfWorkSheets)
{
this.wss = new Worksheet[iNumOfWorkSheets];
this.wss[0] = (Worksheet)this.wb.Worksheets[1];
for (int i = 1; i < iNumOfWorkSheets; i++)
{
wss[i] = (Worksheet)this.wb.Worksheets.Add(oMissing, this.wb.Sheets[i], 1, oMissing);
}
this.excIssue.Visible = true;
}
}
* this private object oMissing = System.Reflection.Missing.Value;
Excel的很多函数包含的部分或者全部参数可以省略,然而在C#中并没有按照其不同的参数个数进行函数重载,因此,即便可以省略的参数也必须找东西填补位置。System.Reflection.Missing.Value就是用来当作空值,占位子的东西。
* this.excOperator = new ApplicationClass();
新建一个Excel。
* this.excOperator.SheetsInNewWorkbook = 1;
新建一个Workbook时都会默认新建一定数量的Worksheet(一般情况下是3个)。这里将其设定为1(不允许设置为0,一个Workbook最少也要包含一个以上的Worksheet),注意:一定要在添加Workbook之前进行设定!
* this wb = this.excOperator.Workbooks.Add(oMissing);
新建一个Workbook。
* this.CreateSheets(5);
创建5个Worksheet。注意:创建的过程中实际上只添加了4个Worksheet——创建Workbook时已经默认添加了1个。具体过程参考private void CreateWorkSheets(int iNumOfWorkSheets)。
4. Excel的操作单元:Range
Range是Excel所有操作的基本对象,可以用以下方法获得Range。
* Range rgSelect = ws.get_Range(“A1”, “B2”);
Ws是一个Worksheet。”A1”和”A2”是需要的这篇区域中,左上角和右下角的两个单元格在Excel中的位置。
5. 我自己封装的一些Excel操作。
* // 合并单元格
private void MergeAndCenter(Worksheet ws, string cell1, string cell2)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.MergeCells = false;
rgSelect.Merge(false);
}
* // 加边框、网格
private void PrintBorders(Worksheet ws, string cell1, string cell2)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
rgSelect.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;
rgSelect.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
rgSelect.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
rgSelect.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
rgSelect.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
rgSelect.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
rgSelect.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
}
* // 填充背景色
private void FillColor(Worksheet ws, string cell1, string cell2)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.Interior.Pattern = XlPattern.xlPatternSolid;
rgSelect.Interior.Color = 10092543;
}
* // 写入数据
private void FillWord(Worksheet ws, string cell, string word)
{
ws.get_Range(cell, cell).FormulaR1C1 = word;
}
* // 对齐
private void AdjustAlignment(Worksheet ws, string cell1, string cell2, XlHAlign algHorzontal, XlVAlign algVertical)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.HorizontalAlignment = algHorzontal;
rgSelect.VerticalAlignment = algVertical;
}
* // 设定单元格范畴
private void AdjustCellCategory(Worksheet ws, string cell1, string cell2, string strCategory)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.NumberFormat = strCategory;
}
* // 拷贝
private void CopyTable(Worksheet ws, string cell1, string cell2)
{
Range rgSelect = ws.get_Range(cell1, cell2);
rgSelect.Copy(this.oMissing);
}
* // 粘贴
private void PasteTable(Worksheet ws, string cell)
{
Range rgSelect = ws.get_Range(cell, this.oMissing);
rgSelect.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, this.oMissing, this.oMissing);
}
* // 设定列宽
private void SetColumnWidth(Worksheet ws, String Columns, double width)
{
Range cols = (Range)ws.Columns[Columns, this.oMissing];
cols.ColumnWidth = width;
}