C#对Excel的基本操作

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;

    }