使用COM接口对Excel的一些常用操作

前提

操作系统需要安装有Excel
添加Microsoft Excel 16.0 Object Library引用(以系统安装的版本为准)

阅读材料：https://docs.microsoft.com/zh-cn/office/client-developer/excel/excel-home

创建Excel电子表格

类库引用 using Excel = Microsoft.Office.Interop.Excel;

Excel.Application app = new Excel.Application();// 始化Excel应用程序对象
if (app == null) // 检查系统是否已安装Excel
{
    MessageBox.Show("Excel未正确安装!");
    return;
}
object misValue = System.Reflection.Missing.Value;// 工作簿模板
Excel.Workbook book = app.Workbooks.Add(misValue);// 如果此参数为指定现有 Microsoft Excel 文件名的字符串，那么创建新工作簿将以该指定的文件作为模板。如果此参数为常量，新工作簿将包含一个指定类型的工作表。 可以是以下 XlWBATemplate 常量之一：xlWBATChart、xlWBATExcel4IntlMacroSheet、xlWBATExcel4MacroSheet 或 xlWBATWorksheet。如果省略此参数，Microsoft Excel创建一个包含大量空白工作表的新工作簿(由 SheetsInNewWorkbook 属性设置)

Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets.get_Item(1); // 获取工作表
sheet.Cells[1, 1] = "ID";
sheet.Cells[1, 2] = "Name";
sheet.Cells[2, 1] = "1001";
sheet.Cells[2, 2] = "张三";
sheet.Cells[3, 1] = "1002";
sheet.Cells[3, 2] = "李四";
book.SaveAs("名单表.xls",Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); // 保存电子表格

book.Close(true, misValue, misValue);
app.Quit();

// 资源清理
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(app);

打开Excel电子表格

object misValue = System.Reflection.Missing.Value;
Excel.Application app = new Excel.Application();// 始化Excel应用程序对象
var book = app.Workbooks.Open("名单表.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); // 打开电子表格
var sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);
var cells = sheet.get_Range("A1", "B2").Value; // 获取单元格范围
Console.WriteLine(cells[2, 2]);

book.Close(true, misValue, misValue);
app.Quit();

// 资源清理
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(app);

读取Excel电子表格

object misValue = System.Reflection.Missing.Value;
Excel.Application app = new Excel.Application();// 始化Excel应用程序对象
var book = app.Workbooks.Open(@"名单表.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);
Excel.Range range = sheet.UsedRange; //指定工作表上的已用区域。 此为只读属性


for (int i = 1; i <= range.Rows.Count; i++)
{
    for (int j = 1; j <= range.Columns.Count; j++)
    {
        Console.WriteLine((range.Cells[i, j] as Excel.Range).Value);
        // Console.WriteLine((range.Cells[i, j] as Excel.Range).Value2); // 此属性和 Value 属性之间的唯一区别是 Value2 属性不使用 Currency 和 Date 数据类型。 可以通过使用 Double 数据类型，以浮点数形式返回这些数据类型格式的数值。https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.value2
    }
}

book.Close(true, null, null);
app.Quit();

Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(app);

新增一个工作表

Excel.Application app = new Excel.Application();// 始化Excel应用程序对象
app.DisplayAlerts = false;

var book = app.Workbooks.Open(@"名单表.xls", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Sheets sheets = book.Worksheets;// 该集合代表指定工作簿中所有工作表。 Sheets对象只读

var newSheet = (Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); // 邢增一个工作表
newSheet.Name = $"Sheet{book.Worksheets.Count}";
newSheet.Cells[1, 1] = "新增的工作表";

newSheet = (Excel.Worksheet)book.Worksheets.get_Item(2);
newSheet.Select(); // 选择 (替换),若要使单个工作表成为活动工作表，请使用 Activate 方法。 https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheet.select

book.Save();

book.Close(true, null, null);
app.Quit();

Marshal.ReleaseComObject(newSheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(app);

打开文件时注意设置文件的ReadOnly属性为false

删除一个工作表

Excel.Application app = new Excel.Application();// 始化Excel应用程序对象
app.DisplayAlerts = false; // 要删除工作表但不显示对话框，请将 Application.DisplayAlerts 属性设置为 False。

var book = app.Workbooks.Open(@"名单表.xls", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Sheets sheets = book.Worksheets;
sheets["Sheet1"].Delete(); // https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheet.delete
book.Save();

book.Close(true, null, null);
app.Quit();

Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(app);

加载Excel数据到DataGridView

string filePath = @"C:\Users\zhm\Documents\成绩表.xlsx";
string connectString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES'";
// xls 连接串 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"
string sqltext = "select * from [Sheet1$]";
using (var connection = new OleDbConnection(connectString))
using (var adapter = new OleDbDataAdapter(sqltext, connection))
{
    adapter.TableMappings.Add("Table", "Score");
    var dtSet = new DataSet();
    adapter.Fill(dtSet);
    dataGridView1.DataSource = dtSet.Tables[0];
}

通过ODBC可以更简单的实现Excel表格数据的增删查改操作https://docs.microsoft.com/zh-cn/office/vba/api/excel.odbcconnection

格式化电子表格

 var book = app.Workbooks.Open(@"成绩表.xlsx", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 var sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);

 var headerRange = sheet.get_Range("A1", "D1");
 headerRange.Font.Bold = true;// 字体加粗
 headerRange.Font.Color = Color.Red;// 字体颜色设置
 headerRange.Interior.Color = Color.LightBlue; // 单元格内部颜色

 var courseRange = sheet.get_Range("C1", "C7");
 courseRange.VerticalAlignment = XlVAlign.xlVAlignCenter;// 垂直居中 https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlvalign
 courseRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 水平居中 https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlhalign

 var cellsRange = sheet.get_Range("A1", "D7");
 cellsRange.Borders.Color = Color.Black; // 边框线颜色  https://docs.microsoft.com/zh-cn/office/vba/api/excel.borders

格式调整后

更多设置参考https://docs.microsoft.com/zh-cn/office/vba/api/excel.range(object)

向工作表中插入一张图片

// ...省略打开文件的代码

var sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);
sheet.Shapes.AddPicture(@"C:\Users\zhm\Pictures\vs.png", MsoTriState.msoFalse, MsoTriState.msoCTrue, 120, 150, 300, 45); // 向工作表中插入一张图片
sheet.SetBackgroundPicture(@"C:\Users\zhm\Pictures\vsbg.jpg"); // 设置工作表背景图
book.Save();

// ...省略释放资源的代码

添加图片参数说明https://docs.microsoft.com/zh-CN/office/vba/api/Excel.Shapes.AddPicture

向工作表中添加图表

// ...省略打开文件的代码

  Excel.ChartObjects charts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing);
  Excel.ChartObject chartPosition = (Excel.ChartObject)charts.Add(240, 20, 300, 250);
  Excel.Chart chartPage = chartPosition.Chart;

  Excel.Range chartRange = sheet.get_Range("A1", "D7");
  chartPage.SetSourceData(chartRange, misValue); // 设置源数据区域
  chartPage.ChartType = Excel.XlChartType.xl3DLine; // 图标类型设置

  book.Save();

// ...省略释放资源的代码

还可以通过图标对象的Export方法将生成的图表以图形格式导出

// 导出图表
chartPage.Export(@"C:\Users\zhm\Desktop\scores.bmp", "BMP",misValue );

图表类型设置参考https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlcharttype

电子表格数据验证

//... 打开电子表格

var sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);
var cellsRange = sheet.get_Range("D2", "D7");
cellsRange.Validation.Add(Excel.XlDVType.xlValidateCustom, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, 0,
100);
cellsRange.Validation.IgnoreBlank = true; // 指定区域内的数据有效性检验允许空值，则该值为 True
cellsRange.Validation.InputTitle = "提示";
cellsRange.Validation.InputMessage = "请输入分数"; // 设置数据有效性检验输入信息
cellsRange.Validation.ErrorMessage = "只允许输入>=0或者<=100的分数"; //设置数据有效性检验错误消息
cellsRange.Validation.ErrorTitle = "分数区域0~100";

//... 保存关闭电子表格

数据验证设置参考https://docs.microsoft.com/zh-cn/office/vba/api/excel.validation

获取更多关于Excel的工具https://www.nuget.org/packages?q=Excel