C#操作Excel总结
2021-06-06 08:02
标签：居中 height ann 位置 int inter ddr any release 1、导入命名空间： 2、如何打开已有excel文档，或者创建一个新的excel文档 若打开已有excel，把“xxx”替换成该excel的文件路径； 注：若新建一个excel文档，“xxx”替换成true即可；不过这里新建的excel文档默认只有一个sheet。 3、 取得、删除和添加sheet 3.1、取得： 3.2 删除： 3.3 添加： 3.4 sheet的重命名 4、删除行和列 4.1 删除行： 4.2 删除列： 5、添加行和列 5.1 添加行： 5.2 添加列： 6、 单元格操作 6.1 单元格的取得 6.2 设置公式 6.3 合并单元格 6.4 设置行高和列宽 6.5 设置单元格颜色 颜色共有56中，详情请参照附录的[颜色对照表] 6.6 设置字号 6.7 是否设置粗体 6.8 单元格/区域、水平垂直居中 6.9 设置区域边框 6.10 设置边框的上、下、左、右线条 7、指定区域的复制 注：Type.Missing和Missing.Value，在excel的操作中被视为某些参数的默认值，他们起到的作用很多时候是形式补足参数 8、excel文件的保存，及后续处理 8.1 文件保存 注：这个地方只能采用该方法保存，不然在指定路径下保存文件外，在我的文档中也会生成一个对应的副本 8.2 后续处理：退出和释放 说明：在application关闭的过程中，通常我们有两种方案： #直接退出app #先关闭workbook，然后关闭workbooks，最后在退出app 鉴于这两种方式，或许本质上是一样的(这点需要证明)，但是依据我们软件开发的原则：哪里需要哪里声明，哪里结束哪里释放回收。 既然在直接退出app的时候，我们不清楚workbook和workbooks具体在什么时间关闭，不如在结束的时候直接手动关闭，这样做可以做到资源的快速直接回收； 所以，建议采用先关闭workbook，然后关闭workbooks，最后在退出app。 9、关于单元格设置域和取得域里需要的数据 9.1 若单元格已经设置为下拉框 9.2 若单元格还没有设置为下拉框的形式 9.3 取得下拉框域的值 注：若在excel模板中通过有效性设定了下拉框的值，strValue得到的将会是excel里的公式，需将其转换, 取得strValue后，可以根据其索引得到你需要的数值； 10、 隐藏行和隐藏列 10.1 隐藏行 10.2 隐藏列 11、案例 C#操作Excel总结 标签：居中 height ann 位置 int inter ddr any release 原文地址：https://www.cnblogs.com/zoro-zero/p/10783580.htmlusing Microsoft.Office.Interop.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);
//i是要取得的sheet的index
_Worksheet _wsh = (_Worksheet)shs.get_Item(i)
//删除sheet必须的设置
app.DisplayAlerts = false;
_wsh.Delete();
//a(before)，b(after)：确定添加位置；c：数目；d：类型
app.Worksheets.Add(a，b，c，d);
//重命名
_wsh.Name = "xxx";
((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);
//删除列
_wsh.get_Range(
_wsh.Cells[1, 2],
_wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft
);
((Range)_wsh.Rows[11, Missing.Value])
.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//添加列
_wsh.get_Range(
_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);
//获得单元格对象
_wsh.Cells[row, cell]
//在对应的单元格输入公式即可
_wsh.Cells[row, cell] = "=Sum(A1/B1)";
//合并单元格
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[3, Missing.Value]).RowHeight = 5;
((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5;
((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;
((Range)_wsh.Cells[1, "B"]).Font.Size = 8;
((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false;
((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;
((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3;
//左
_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;
_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);
//屏蔽掉系统跳出的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);
//退出
app.Quit();
//释放掉多余的excel进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//这里的“1，2，3”设置的就是下拉框的值
((Range)_wsh.Cells[2, 1])
.Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing, "1,2,3", Type.Missing);
((Range)_wsh.Cells[2, 1])
.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Type.Missing,"1,2,3", Type.Missing);
string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;
_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true;
_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.EntireColumn.Hidden = true;
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 )
{
((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}\r\n{model.QuotationItems[i].Product.Code}\r\n{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;
}