1、导入命名空间：
using Microsoft.Office.Interop.Excel;
2、如何打开已有excel文档，或者创建一个新的excel文档
Application app = new Application();
Workbooks wbks = app.Workbooks;
_Workbook _wbk = wbks.Add(excelTempPath + "quotaExcelTemp.xls");
Sheets shs = _wbk.Sheets;
_Worksheet _wsh = (_Worksheet)shs.get_Item(1);
若打开已有excel，把“xxx”替换成该excel的文件路径；
注：若新建一个excel文档，“xxx”替换成true即可；不过这里新建的excel文档默认只有一个sheet。
3、 取得、删除和添加sheet
3.1、取得：
//i是要取得的sheet的index _Worksheet _wsh = (_Worksheet)shs.get_Item(i)
3.2 删除：
//删除sheet必须的设置 app.DisplayAlerts = false; _wsh.Delete();
3.3 添加：
//a(before)，b(after)：确定添加位置；c：数目；d：类型 app.Worksheets.Add(a，b，c，d);
3.4 sheet的重命名
//重命名 _wsh.Name = "xxx";
4、删除行和列
4.1 删除行：
((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);
4.2 删除列：
//删除列 _wsh.get_Range( _wsh.Cells[1, 2], _wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft );
5、添加行和列
5.1 添加行：
((Range)_wsh.Rows[11, Missing.Value]) .Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
5.2 添加列：
//添加列 _wsh.get_Range( _wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1]) .Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);
6、 单元格操作
6.1 单元格的取得
//获得单元格对象 _wsh.Cells[row, cell]
6.2 设置公式
//在对应的单元格输入公式即可 _wsh.Cells[row, cell] = "=Sum(A1/B1)";
6.3 合并单元格
//合并单元格 Microsoft.Office.Interop.Excel.Range mergeRange = _wsh.Range[_wsh.Cells[19 + i, 2], _wsh.Cells[19 + i, 4]]; //合并单元格 mergeRange.Merge(mergeRange.MergeCells);
6.4 设置行高和列宽
((Range)_wsh.Rows[3, Missing.Value]).RowHeight = 5; ((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5;
6.5 设置单元格颜色 颜色共有56中，详情请参照附录的[颜色对照表]
((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;
6.6 设置字号
((Range)_wsh.Cells[1, "B"]).Font.Size = 8;
6.7 是否设置粗体
((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false;
6.8 单元格/区域、水平垂直居中
((Range)_wsh.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //靠左 ((Range)_wsh.Cells[15, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //靠右 ((Range)_wsh.Cells[30, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
6.9 设置区域边框
((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3;
6.10 设置边框的上、下、左、右线条
//左 _wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;// //右 _wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;// //上 _wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//下 //下 _wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;
7、指定区域的复制
_Worksheet _wsh = (_Worksheet)shs.get_Item(1);//复制选中区域的内容 Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.Columns.Count]); range.Select(); range.Copy(Type.Missing); //选中粘贴的起始位置 Range test = ((Range)_wsh.Cells[11, 1]); test.Select(); //屏蔽掉Alert，默认确定粘贴 app.DisplayAlerts = false; test.Parse(Missing.Value, Missing.Value);
注：Type.Missing和Missing.Value，在excel的操作中被视为某些参数的默认值，他们起到的作用很多时候是形式补足参数
8、excel文件的保存，及后续处理
8.1 文件保存
//屏蔽掉系统跳出的Alert app.AlertBeforeOverwriting = false; //保存到指定目录 SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
注：这个地方只能采用该方法保存，不然在指定路径下保存文件外，在我的文档中也会生成一个对应的副本
8.2 后续处理：退出和释放
//退出 app.Quit(); //释放掉多余的excel进程 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
说明：在application关闭的过程中，通常我们有两种方案：
#直接退出app
#先关闭workbook，然后关闭workbooks，最后在退出app
鉴于这两种方式，或许本质上是一样的(这点需要证明)，但是依据我们软件开发的原则：哪里需要哪里声明，哪里结束哪里释放回收。
既然在直接退出app的时候，我们不清楚workbook和workbooks具体在什么时间关闭，不如在结束的时候直接手动关闭，这样做可以做到资源的快速直接回收；
所以，建议采用先关闭workbook，然后关闭workbooks，最后在退出app。
9、关于单元格设置域和取得域里需要的数据
9.1 若单元格已经设置为下拉框
//这里的“1，2，3”设置的就是下拉框的值 ((Range)_wsh.Cells[2, 1]) .Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing, "1,2,3", Type.Missing);
9.2 若单元格还没有设置为下拉框的形式
((Range)_wsh.Cells[2, 1]) .Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing,"1,2,3", Type.Missing);
9.3 取得下拉框域的值
string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;
注：若在excel模板中通过有效性设定了下拉框的值，strValue得到的将会是excel里的公式，需将其转换, 取得strValue后，可以根据其索引得到你需要的数值；
10、 隐藏行和隐藏列
10.1 隐藏行
_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true;
10.2 隐藏列
_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1]) .EntireColumn.Hidden = true;
11、案例
private string SaveExcel(PrintQuotationOrderViewModel model) { if (model == null) return string.Empty; string excelTempPath = Server.MapPath("/Resources/Temp/"); if (!Directory.Exists(excelTempPath)) { Directory.CreateDirectory(excelTempPath); } Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; _Workbook _wbk = wbks.Add(excelTempPath + "quotaExcelTemp.xls"); Sheets shs = _wbk.Sheets; _Worksheet _wsh = (_Worksheet)shs.get_Item(1); //设置客户单号 _wsh.Cells[7, 8] = model.QuotationOrder.CustomerPurchaseNumber; ((Range)_wsh.Cells[7, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //设置报价单号 _wsh.Cells[8, 8] = model.QuotationOrder.Number; ((Range)_wsh.Cells[8, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //设置报价日期 _wsh.Cells[9, 8] = model.QuotationOrder.QuotedOn.ToString("yyyy-MM-dd"); ((Range)_wsh.Cells[9, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //设置销售人员 _wsh.Cells[10, 8] = model.QuotationOrder.SalesmanName; //设置客户公司 _wsh.Cells[12, 3] = model.QuotationOrder.Buyer.Company.Name; //设置客户部门 _wsh.Cells[13, 3] = model.QuotationOrder.Buyer.Department; //设置联系人 _wsh.Cells[13, 7] = model.QuotationOrder.Buyer.Name; //设置收货公司 _wsh.Cells[14, 3] = model.QuotationOrder.Receiver.Company; //设置收货部门 _wsh.Cells[14, 7] = model.QuotationOrder.Receiver.Department; //设置收货人 _wsh.Cells[15, 3] = model.QuotationOrder.Receiver.Name; //设置联系电话 _wsh.Cells[15, 7] = model.QuotationOrder.Receiver.Mobile; ((Range)_wsh.Cells[15, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //设置收货地址 _wsh.Cells[16, 3] = model.QuotationOrder.Receiver.Address; int count = model.QuotationItems.Count; for (int i = 0; i < count; i++) { ((Range)_wsh.Rows[19 + i, System.Reflection.Missing.Value]).Insert(System.Reflection.Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); _wsh.Cells[19 + i, 1] = (i+1).ToString(); //第一项 设置编号 _wsh.Cells[19 + i, 2] = $"{model.QuotationItems[i].CustomCode} {model.QuotationItems[i].Product.Code} {model.QuotationItems[i].Product.Name}"; //第二项 设置 客户零件号/模号/料号 产品型号 产品名称 //合并单元格 Microsoft.Office.Interop.Excel.Range mergeRange = _wsh.Range[_wsh.Cells[19 + i, 2], _wsh.Cells[19 + i, 4]]; //合并单元格 mergeRange.Merge(mergeRange.MergeCells); //((Range)_wsh.Rows[19 + i, System.Reflection.Missing.Value]).Merge(mergeRange.MergeCells); _wsh.Cells[19 + i, 5] = model.QuotationItems[i].Quantity; //第三项 设置数量 _wsh.Cells[19 + i, 6] = model.QuotationItems[i].Quotation.UnitPriceWithTax >=0 ? ((decimal)model.QuotationItems[i].Quotation.UnitPriceWithTax).ToString("f2") : ""; //第四项 设置含税单价（元） _wsh.Cells[19 + i, 7] = model.QuotationItems[i].Quotation.SubtotalWithTax; //第五项 设置小计（元） _wsh.Cells[19 + i, 8] = model.QuotationItems[i].Quotation.DispatchDays>=0 ? ((int)model.QuotationItems[i].Quotation.DispatchDays).ToString() : ""; //第六项 设置发货天数（工作日） _wsh.Cells[19 + i, 9] = model.QuotationItems[i].Remark; //第七项 设置备注 ((Range)_wsh.Rows[19 + i, System.Reflection.Missing.Value]).RowHeight = 45; } //设置结算方式 _wsh.Cells[24 + count, 3] = model.Payment; //设置产品未税总价 _wsh.Cells[23 + count, 8] = model.QuotationOrder.TotalWithoutTax.ToString("f2"); //设置优惠金额0.00 _wsh.Cells[24 + count, 8] = model.QuotationOrder.Preferential != null ? model.QuotationOrder.Preferential.Discount.HasValue ? ((decimal)model.QuotationOrder.Preferential.Discount).ToString("f2") : "0.00" : "0.00"; //设置运费 _wsh.Cells[25 + count, 8] = model.QuotationOrder.Shipping.Amount.ToString("f2"); //设置运费折扣 _wsh.Cells[26 + count, 8] = model.QuotationOrder.Shipping.Discount.ToString("f2"); //设置增值税 _wsh.Cells[27 + count, 8] = model.QuotationOrder.Tax.ToString("f2"); //设置含税总金额（小写） _wsh.Cells[29 + count, 8] = model.QuotationOrder.TotalWithTax.ToString("f2"); //设置含税总金额（大写） _wsh.Cells[30 + count, 8] = model.QuotationOrder.TotalWithTaxInChinese; ((Range)_wsh.Cells[30 + count, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; excelTempPath = Server.MapPath("/Resources/Excel/"); if (!Directory.Exists(excelTempPath)) { Directory.CreateDirectory(excelTempPath); } string savePath = excelTempPath + DateTime.Now.ToFileTime() + ".xls"; _wbk.SaveCopyAs(savePath); wbks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); return savePath; }