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,//设置后台运行可见性为falseDisplayAlerts = 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,//设置后台运行可见性为falseDisplayAlerts = 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];//第一个sheetExcel.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;}