C#_操作Excel总结

首先，你需要安装 Microsoft.Office.Interop.Excel 包。

将以下语句添加到开头以减少代码量：

using Excel = Microsoft.Office.Interop.Excel;

• 初始化:

Excel.Application xlapp = new Excel.Application();

Excel._Workbook XLWB;

Excel._Worksheet wsht;

Excel.Range range;

• 创建一个新workbook:

XLWB = (Excel._Workbook)(xlApp.Workbooks.Add(""));

• 打开一个workbook:

Excel._Workbook XLWB = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

• 得到一个activeSheet:

wsht = (Excel._Worksheet)XLWB.ActiveSheet;

• 向Excel表中添加数据：

            wsht.Cells[1, 1] = "First Name";
            wsht.Cells[1, 2] = "Last Name";
            wsht.Cells[2, 1] = "Full Name";
            wsht.Cells[3, 1] = "Salary";

• 通过数组向Excel表中添加数据：

            string[,] array = new string[3, 2];
            array[0, 0] = "array[0, 0]";
            array[0, 1] = "array[0, 1]";
            array[1, 0] = "array[1, 0]";
            array[2, 1] = "array[2, 1]";
            wsht.get_Range("C1", "D3").Value2 = array;

• 加粗：

wsht.get_Range("A1", "B1").Font.Bold = true;

• 合并列:

            range = wsht.get_Range("C1", "C3");
            range.Formula = "=A1 & \" \" & B1";

• 自动适应宽度:

            range = wsht.get_Range("A1", "D1");
            range.EntireColumn.AutoFit();

• 以固定格式填充100以内的随机数字:

            range = wsht.get_Range("A1", "A3");
            range.Formula = "=RAND()*100";
            range.NumberFormat = "$0.00";

• 保存:

            xlApp.Visible = false;
            XLWB.SaveAs("your file path");

• 得到表中最后使用的数据:

            wsht = XLWB.Sheets["your sheet name"];
            Excel.Range last = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            var s = last.Value;

• 将Excel列转换成数字:

例如: A -> 1, B -> 2, D -> 4.

        public static int ExcelColumnNameToNumber(string columnName)
        {
            if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

            columnName = columnName.ToUpperInvariant();

            int sum = 0;

            for (int i = 0; i < columnName.Length; i++)
            {
                sum *= 26;
                sum += (columnName[i] - 'A' + 1);
            }

            return sum;
        }

• 将数字转换成列名,例如： 1 -> A, 3 -> C.

        public static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }
            return columnName;
        }

• 得到Excel表中所有的表名:

            string[] excelSheets = new string[XLWB.Worksheets.Count];
            int i = 0;
            foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in XLWB.Worksheets)
            {
                excelSheets[i] = wSheet.Name;
                i++;
            }