NPOI+.net Excel操作Excel模板并填充数据
/// <summary>
/// 导出样品标签
/// </summary>
protected void ExcelSampleLabel()
{
#region 根据委托ID获取样品信息
List<Model_Tb_Sam_Sample> sampleList = null;
List<Entity_GE_Sample> GeSampleList = null;
int sampleNum = 0;
string consignID = Request.QueryString["cid"] as string;//委托ID
string consignCode = consignManger.GetSampleConsignInfoByConsignID(consignID).ConsignCode;
GeSampleList = bll_GE_Sample.GetGeSampleListByConsignID(consignID);
sampleNum = GeSampleList.Count;//获取样品数量并根据数量进行区域复制
#endregion
#region 初始化Excel操作模板
Microsoft.Office.Interop.Excel.XlFileFormat version = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8;
Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
if (ExcelApp == null) return;
ExcelApp.Visible = false; ExcelApp.UserControl = true;
Workbooks workbooks = ExcelApp.Workbooks;
_Workbook workTmplete = workbooks.Add(Server.MapPath("~/ExcelReport/SampleLable/样品标签.xls"));//加载模板
Sheets sheets = workTmplete.Sheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //第一个工作簿
if (worksheet == null) return;
Range rage = worksheet.get_Range("AH1:CC25");//设置要复制的区域
rage.Copy();
int startRowIndex = 25;//复制的行数
int count = 26;//初始化第一个复制的位置
for (int i = 1; i < sampleNum; i++)//判断要复制多少次
{
string target = "AH" + count + ":CC" + (count + startRowIndex - 1);//复制到的目标
Range rage1 = worksheet.get_Range(target);
rage1.PasteSpecial(XlPasteType.xlPasteAllUsingSourceTheme, XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false,
false);
count = count + startRowIndex;
}
//设置打印区域
string printArea = "AI1:CB" + (25 * sampleNum);
worksheet.PageSetup.PrintArea = printArea;
//因为不能直接操作原模板，所以要根据格式将模板复制一份到另外的地方再进行填充数据
string savePath = "~/ExcelReport/SampleLable/SampleLable" + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".xls";
workTmplete.SaveAs(Server.MapPath(savePath), version, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//按顺序释放资源
workbooks.Close();
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
workTmplete = null;
workbooks = null;
ExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
#endregion
#region 导出Excel
//获取模板路径
string path = Server.MapPath(savePath);
//读取模板向模板中导入数据
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
MemoryStream ms = new MemoryStream();
//获取Excel项
Sheet sheet = workbook.GetSheet("样品标签");
//初始化行和列
Row row;
Cell cell;
//样品处置方式
CellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;//排列顺序
NPOI.SS.UserModel.Font font = workbook.CreateFont();
font.FontHeight = 17 * 15;
style.VerticalAlignment = VerticalAlignment.CENTER;
font.FontName = "Wingdings 2";//此代码是在单元格中插入复选框
style.SetFont(font);
#region 通用委托
for (int i = 0; i < sampleNum; i++)
{
row = sheet.GetRow(6 + 25 * i);//样品名称
cell = row.GetCell(46);
cell.SetCellValue(GeSampleList[i].SampleName);
row = sheet.GetRow(9 + 25 * i);
cell = row.GetCell(46);
cell.SetCellValue(GeSampleList[i].SampleCode);
row = sheet.GetRow(12 + 25 * i);//代表数量
cell = row.GetCell(46);
cell.SetCellValue(GeSampleList[i].SampleCount);
row = sheet.GetRow(15 + 25 * i);//接样日期
cell = row.GetCell(46);
cell.SetCellValue(GeSampleList[i].CreateDate.Substring(0, 10));
row = sheet.GetRow(18 + 25 * i);//样品状态
cell = row.GetCell(47);
cell.SetCellValue("R");
cell.CellStyle = style;
row = sheet.GetRow(21 + 25 * i);//样品处置状态
//样品处置方式：1、留样 2、取回 3、废弃（自行处理）
string iskeep = bllSample.GetSampleProcessTypeByConsignCode(consignCode);
if (iskeep == "3")
cell = row.GetCell(47);
else if (iskeep == "1")
cell = row.GetCell(55);
else if (iskeep == "2")
cell = row.GetCell(63);
else
cell = row.GetCell(47);
cell.SetCellValue("R");
cell.CellStyle = style;
}
#endregion
System.IO.File.Delete(Server.MapPath(savePath));
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("p_w_upload;filename=" + consignCode + ".xls"));
Response.ContentType = "application/ms-excel";
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
#endregion
}
示例图如下：
