Microsoft.Office.Interop.Excel 的简单操作

1、安装 Microsoft.Office.Interop.Excel

新建 C# 工程后，在【项目】菜单中点击【管理 NuGet 程序包】，浏览搜索 Microsoft.Office.Interop.Excel，点击下载安装。

2、声明引用 Microsoft.Office.Interop.Excel

using System.Data;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel; //指定别名

3、简单的新建 EXCEL 操作代码

            Excel.Application excelApp = new Excel.Application //定义Excel应用对象，别名以区别 C# 应用
            {
                Visible = false,//设置后台运行可见性为false
                DisplayAlerts = false,//禁止弹出警告
                AlertBeforeOverwriting = false//禁止覆盖前弹出提醒
            };

            if (excelApp == null) return;//系统没有Excel对象
            
            Excel.Workbook workbook = excelApp.Workbooks.Add();// 添加 Excel 工作簿（Workbook）

            workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[1], 2, Type.Missing);// 在默认的 sheet1之后添加 2 个工作表

            Excel.Worksheet sheet1 = (Excel.Worksheet)workbook.Worksheets[1] as Excel.Worksheet;//定义Excel工作表
            Excel.Worksheet sheet2 = (Excel.Worksheet)workbook.Worksheets[2] as Excel.Worksheet; 
            Excel.Worksheet sheet3 = (Excel.Worksheet)workbook.Worksheets[3] as Excel.Worksheet; 

            // 命名工作表
            sheet1.Name = "测试";
            sheet2.Name = "宋体标题";
            sheet3.Name = "黑体标题";

            string[] headers = new string[] { "单位", "名称", "属性", "型号", "序列号" };
            Excel.Range headerRange = sheet2.Range[sheet2.Cells[1, 1], sheet2.Cells[1, headers.Length]];
            headerRange.Value2 = headers;
            headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
            headerRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;//垂直居中
            headerRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
            headerRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细
            headerRange.WrapText = true;//自动换行
            headerRange.NumberFormatLocal = "@";//文本格式
            headerRange.Font.Name = "宋体";//设置字体
            headerRange.Font.Size = 12;//字体大小
            headerRange.Font.Bold = false;//字体加粗
            sheet2.Columns.AutoFit();//设置列宽和数据一致

            headerRange = sheet3.Range[sheet3.Cells[1, 1], sheet3.Cells[1, headers.Length]];
            headerRange.Value2 = headers;
            headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
            headerRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;//垂直居中
            headerRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
            headerRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细
            headerRange.WrapText = true;//自动换行
            headerRange.NumberFormatLocal = "@";//文本格式
            headerRange.Font.Name = "黑体";//设置字体
            headerRange.Font.Size = 12;//字体大小
            headerRange.Font.Bold = true;//字体加粗
            sheet3.Columns.AutoFit();

            workbook.SaveAs(Application.StartupPath +  @"\1234.xlsx");//保存文件

            workbook.Close(false);//关闭工作簿

            excelApp.Quit();//退出对象

            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);//释放COM对象的引用
            workbook = null;
            excelApp = null;
            if (excelApp == null) MessageBox.Show("已经创建EXCEL文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

4、将 DataGridView 表数据写到 EXCEL 操作代码

  public void WriteExcelFromDgv(DataGridView dgv)
  {
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application //定义Excel应用对象
            {
                Visible = false,//设置后台运行可见性为false
                DisplayAlerts = false,//禁止弹出警告
                AlertBeforeOverwriting = false//禁止覆盖前弹出提醒
            };

            Excel.Workbook workbook = excelApp.Workbooks.Add();//定义Excel工作簿
                                                               // Worksheet worksheet = workbook.ActiveSheet;//定义Excel工作表
            Excel.Worksheet worksheet = workbook.Worksheets[1];//定义默认Excel工作表
            int rowCount = dgv.Rows.Count;//获取总行数
            int columnCount = dgv.Columns.Count;//获取总列数

            for (int i = 0; i < columnCount; i++)
            {
                worksheet.Cells[1, i + 1] = dgv.Columns[i].HeaderText;//填写列标题
                worksheet.Cells[1, i + 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
                worksheet.Cells[1, i + 1].VerticalAlignment= Excel.XlHAlign.xlHAlignCenter;//垂直居中
            }


            for (int i = 0; i < rowCount - 1; i++)
            {
                for (int j = 0; j < columnCount; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dgv.Rows[i + 1].Cells[j].Value;//填写表格数据
                    worksheet.Cells[i + 2, j + 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
                    worksheet.Cells[i + 2, j + 1].VerticalAlignment= Excel.XlHAlign.xlHAlignCenter;//垂直居中 
                }
            }
            worksheet.Columns.AutoFit();//设置列宽和数据一致
            worksheet.SaveAs(Application.StartupPath + @"\DataGridViewData.xlsx");//保存文件
            workbook.Close(false);//关闭工作簿
            excelApp.Quit();//退出对象
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);//释放COM对象的引用
            workbook = null;
            excelApp = null;
            if (excelApp == null) MessageBox.Show("数据已经写入到 EXCEL 文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
  }

5、将 EXCEL 表数据读取到 C# 数据表 DataTable 操作代码

        /// <summary>将 EXCEL 表数据读取到 C# 数据表 DataTable</summary>
        /// <param name="filePath">EXCEL 文件路径</param>
        /// <param name="columnsToExtract">读取列数</param>
        /// <param name="skipRows">跳过行数</param>
        /// <returns>返回数据表 dataTable </returns>
        public DataTable ReadExcelToDataTable(string filePath,int[] columnsToExtract, int skipRows=2)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Open(filePath);

            Excel.Worksheet worksheet = workbook.Sheets[1];//第一个sheet

            Excel.Range usedRange = worksheet.UsedRange;

            int rowCount = usedRange.Rows.Count;
            int colCount = columnsToExtract.Length;

            DataTable dataTable=new DataTable();

            object[,] valueArray = (object[,])usedRange.Value;

            for (int row = skipRows; row <= rowCount; row++)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int col = 0; col < colCount; col++)
                {
                    int colIndex = columnsToExtract[col];

                    dataRow[col] = valueArray[row, colCount]?.ToString() ?? string.Empty;

                }
                dataTable.Rows.Add(dataRow);

            }
            workbook.Close(false);
            excelApp.Quit();

            Marshal.ReleaseComObject(excelApp);//释放COM对象的引用
            return dataTable;
        }