<%@ WebHandler Language = "C#" Class="DownLoadExcel_dachai" %> using System; using System.Web; using Newtonsoft.Json; using System.Text; using System.Linq; using System.Collections.Generic; using System.IO; using System.Web.Script.Serialization; using System.Data; using BasicData; using DataLinkMesWork; using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.SS.UserModel; using System.IO; using NPOI.SS.Util; using NPOI.HSSF.Util; using System.Collections; using System.Data; using NPOI.XSSF.UserModel; using DataLinkMesWork; using NPOI.XSSF.Streaming; using Spire.Xls; using NPOI.SS.Formula.Functions; using MESDownloadExcel; public class DownLoadExcel_dachai : IHttpHandler { public bool IsReusable { get { return false; } } public void ProcessRequest(HttpContext context) { context.Response.ContentType = "application/json"; var data = context.Request; var stream = new StreamReader(data.InputStream).ReadToEnd(); string InvoiceNum; //JsonData jsonData; string responseText = "NULL"; byte[] bytes = null; string fileName = "test.xsl"; try { JsonData jsonData=null; try { jsonData = JsonMapper.ToObject(stream); } catch(Exception err) { } int type=0; try { if(jsonData!=null) { if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { } jsonobj dataobj=null; try { dataobj = new JavaScriptSerializer().Deserialize(stream); if(dataobj!=null) { type = Int32.Parse(dataobj.Type); } } catch(Exception err) { dataobj = null; } if(dataobj==null) { try { InvoiceNum = HttpContext.Current.Request["param"]; if(InvoiceNum!=null) { jsonData = JsonMapper.ToObject(InvoiceNum); if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { jsonData = null; } } string fileExtension=""; HttpFileCollection files; string suffix; string name; switch(type) { case 2005: WriteExcel_2005(jsonData, out bytes, out fileName, out fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; } } catch(Exception err) { context.Response.Write(responseText); } } public static void ExcelFile(JsonData json,out byte[] bytesFile, out string fileName, ref string fileExtension) { bytesFile = null; fileName = ""; string tabletype="00"; string tablecode = "00"; DataSet ds;//------ try { SqlServerCmd.SqlCmd.GetDataSetByJson(json, out tabletype, out tablecode, out ds, out fileName); if(tabletype=="17") { DataTable dt = new DataTable(); dt.Columns.Add("t0"); DataRow dr = dt.NewRow(); dr[0] = fileExtension.Split(',')[1].Split('\n')[0]; ; dt.Rows.Add(dr); ds.Tables.Add(dt); } } catch { fileExtension = ""; } return ; } static ICellStyle CellStyle_Border = null; static ICellStyle CellStyle_center = null; ///

/// 根据模板填写报表 ///

/// /// /// /// public static bool WriteExcel_2005(JsonData json, out byte[] bytes, out string fileName, out string fileExtension) { DataSet ds;//------ string tabletype="00"; string tablecode = "00"; SqlServerCmd.SqlCmd.GetDataSetByJson(json, out tabletype, out tablecode, out ds, out fileName); fileExtension = "xls"; ISheet sheet = null; bytes = null; // 模板数据 DataTable dt1; // 表头数据 DataTable dt2; // 表格1数据 DataTable dt3; // 表格2数据 DataTable dt4; // 表格3数据 DataTable dt5; try { dt1 = ds.Tables[0]; // 模板数据 dt2 = ds.Tables[1]; // 表头数据 dt3 = ds.Tables[2]; // 表格1数据 dt4 = ds.Tables[3]; // 表格2数据 dt5 = ds.Tables[4]; // 表格3数据 tablecode = dt1.Rows[0]["tabletype"].ToString(); int rowCount = dt1.Rows.Count + dt2.Rows.Count + dt3.Rows.Count + dt4.Rows.Count + dt5.Rows.Count;//行数 if (rowCount > 20000) { XSSFWorkbook wk = new XSSFWorkbook(); fileExtension = "xlsx"; sheet = wk.CreateSheet("Sheet1"); // 表头的内容 WriteHead2005FirstRow(sheet, 0, dt2); // 过站信息 WriteHead2005_PS(sheet, 4, dt3); // 物料信息 WriteHead2005_Material(sheet, 0 + 4 + dt3.Rows.Count + 2, dt4); // 质量信息 WriteHead2005_Quality(sheet, 0 + 4 + dt3.Rows.Count + 2 + dt4.Rows.Count + 2, dt5); OutExcelFileMemoryStream(wk, out bytes); } else { HSSFWorkbook wk = new HSSFWorkbook(); sheet = wk.CreateSheet("Sheet1"); WriteHead2005_Style_border(wk); WriteHead2005_Style_center(wk); fileExtension = "xls"; // 表头的内容 WriteHead2005FirstRow(sheet, 0, dt2); // 过站信息 WriteHead2005_PS(sheet, 4, dt3); // 物料信息 WriteHead2005_Material(sheet, 0 + 4 + dt3.Rows.Count + 2, dt4); // 质量信息 WriteHead2005_Quality(sheet, 0 + 4 + dt3.Rows.Count + 2 + dt4.Rows.Count + 2, dt5); OutExcelFileMemoryStream(wk, out bytes); } return true; } catch { return false; } } private static void WriteHead2005FirstRow(ISheet sheet, int startIndex, DataTable dt) { try { IRow row1 = sheet.CreateRow(0); string tableFirstRowName = dt.Rows[0]["tableTitle"].ToString(); ICell cell1 = row1.CreateCell(0); //合并单元格 CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 10); sheet.AddMergedRegion(region1); cell1.SetCellValue(tableFirstRowName); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell1.CellStyle = CellStyle_Border; cell1.CellStyle.Alignment = HorizontalAlignment.Center; cell1.CellStyle.VerticalAlignment = VerticalAlignment.Center; string orderCode = dt.Rows[0]["订单号"].ToString(); string enignerCode = dt.Rows[0]["发动机号"].ToString(); string enignerType = dt.Rows[0]["发动机型号"].ToString(); string uplineTime = dt.Rows[0]["上线时间"].ToString(); // 订单号 IRow row2 = sheet.CreateRow(1); ICell cell2_1 = row2.CreateCell(0); CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 1); sheet.AddMergedRegion(region2); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell2_1.CellStyle = CellStyle_Border; cell2_1.SetCellValue("订单号:"); ICell cell2_2 = row2.CreateCell(2); CellRangeAddress region3 = new CellRangeAddress(1, 1, 2, 4); sheet.AddMergedRegion(region3); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region3, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell2_2.CellStyle = CellStyle_Border; cell2_2.SetCellValue(orderCode); // 发动机号 ICell cell2_3 = row2.CreateCell(5); CellRangeAddress region4 = new CellRangeAddress(1, 1, 5, 6); sheet.AddMergedRegion(region4); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region4, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell2_3.CellStyle = CellStyle_Border; cell2_3.SetCellValue("发动机号:"); ICell cell2_4 = row2.CreateCell(7); CellRangeAddress region5 = new CellRangeAddress(1, 1, 7, 10); sheet.AddMergedRegion(region5); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region5, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell2_4.CellStyle = CellStyle_Border; cell2_4.SetCellValue(enignerCode); // 发动机型号 IRow row3 = sheet.CreateRow(2); ICell cell3_1 = row3.CreateCell(0); CellRangeAddress region6 = new CellRangeAddress(2, 2, 0, 1); sheet.AddMergedRegion(region6); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region6, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell3_1.CellStyle = CellStyle_Border; cell3_1.SetCellValue("发动机型号:"); ICell cell3_2 = row3.CreateCell(2); CellRangeAddress region7 = new CellRangeAddress(2, 2, 2, 4); sheet.AddMergedRegion(region7); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region7, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell3_2.CellStyle = CellStyle_Border; cell3_2.SetCellValue(enignerType); // 上线时间 ICell cell3_3 = row3.CreateCell(5); CellRangeAddress region8 = new CellRangeAddress(2, 2, 5, 6); sheet.AddMergedRegion(region8); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region8, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell3_3.CellStyle = CellStyle_Border; cell3_3.SetCellValue("上线时间:"); ICell cell3_4 = row3.CreateCell(7); CellRangeAddress region9 = new CellRangeAddress(2, 2, 7, 10); sheet.AddMergedRegion(region9); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region9, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell3_4.CellStyle = CellStyle_Border; cell3_4.SetCellValue(uplineTime); IRow row4 = sheet.CreateRow(3); ICell cell3_5 = row4.CreateCell(0); CellRangeAddress region10 = new CellRangeAddress(3, 3, 0, 10); sheet.AddMergedRegion(region10); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region10, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell3_5.CellStyle = CellStyle_Border; } catch { } } private static void WriteHead2005_PS(ISheet sheet, int startIndex, DataTable dt) { try { IRow row4 = sheet.CreateRow(startIndex); ICell cell4_1 = row4.CreateCell(0); CellRangeAddress region1 = new CellRangeAddress(startIndex, startIndex, 0, 10); sheet.AddMergedRegion(region1); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell4_1.CellStyle = CellStyle_Border; cell4_1.SetCellValue("过站信息"); IRow row5 = sheet.CreateRow(startIndex + 1); // 工位号 ICell cell5_1 = row5.CreateCell(0); cell5_1.CellStyle = CellStyle_Border; cell5_1.SetCellValue("工位号"); // 到达时间 ICell cell5_2 = row5.CreateCell(1); CellRangeAddress region2 = new CellRangeAddress(startIndex + 1, startIndex + 1, 1, 2); sheet.AddMergedRegion(region2); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_2.CellStyle = CellStyle_Border; cell5_2.SetCellValue("到达时间"); // 离开时间 ICell cell5_3 = row5.CreateCell(3); CellRangeAddress region3 = new CellRangeAddress(startIndex + 1, startIndex + 1, 3, 4); sheet.AddMergedRegion(region3); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region3, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_3.CellStyle = CellStyle_Border; cell5_3.SetCellValue("离开时间"); // 在线时间 ICell cell5_4 = row5.CreateCell(5); CellRangeAddress region4 = new CellRangeAddress(startIndex + 1, startIndex + 1, 5, 6); sheet.AddMergedRegion(region4); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region4, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_4.CellStyle = CellStyle_Border; cell5_4.SetCellValue("在线时间"); // 操作者工号 ICell cell5_5 = row5.CreateCell(7); CellRangeAddress region5 = new CellRangeAddress(startIndex + 1, startIndex + 1, 7, 8); sheet.AddMergedRegion(region5); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region5, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_5.CellStyle = CellStyle_Border; cell5_5.SetCellValue("操作者工号"); // 是否合格 ICell cell5_6 = row5.CreateCell(9); CellRangeAddress region6 = new CellRangeAddress(startIndex + 1, startIndex + 1, 9, 10); sheet.AddMergedRegion(region6); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region6, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_6.CellStyle = CellStyle_Border; cell5_6.SetCellValue("是否合格"); //填充数据 for (int i = 0; i < dt.Rows.Count; i++) { int rowIndex = i + startIndex + 2; IRow row = sheet.CreateRow(rowIndex); // 工位号 ICell cell_opName = row.CreateCell(0); cell_opName.SetCellValue(dt.Rows[i]["工位号"].ToString()); cell_opName.CellStyle = CellStyle_Border; // 到达时间 ICell cell_arriveTime = row.CreateCell(1); CellRangeAddress region7 = new CellRangeAddress(rowIndex, rowIndex, 1, 2); sheet.AddMergedRegion(region7); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region7, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_arriveTime.CellStyle = CellStyle_Border; cell_arriveTime.SetCellValue(dt.Rows[i]["到达时间"].ToString()); // 离开时间 ICell cell_leaveTime = row.CreateCell(3); CellRangeAddress region8 = new CellRangeAddress(rowIndex, rowIndex, 3, 4); sheet.AddMergedRegion(region8); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region8, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_leaveTime.CellStyle = CellStyle_Border; cell_leaveTime.SetCellValue(dt.Rows[i]["离开时间"].ToString()); // 在线时间 ICell cell_onlineTime = row.CreateCell(5); CellRangeAddress region9 = new CellRangeAddress(rowIndex, rowIndex, 5, 6); sheet.AddMergedRegion(region9); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region9, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_onlineTime.CellStyle = CellStyle_Border; cell_onlineTime.SetCellValue(dt.Rows[i]["在线时间"].ToString()); // 操作者工号 ICell cell_opCode = row.CreateCell(7); CellRangeAddress region10 = new CellRangeAddress(rowIndex, rowIndex, 7, 8); sheet.AddMergedRegion(region10); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region10, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_opCode.CellStyle = CellStyle_Border; cell_opCode.SetCellValue(dt.Rows[i]["操作者工号"].ToString()); // 是否合格 ICell cell_isOK = row.CreateCell(9); CellRangeAddress region11 = new CellRangeAddress(rowIndex, rowIndex, 9, 10); sheet.AddMergedRegion(region11); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region11, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_isOK.CellStyle = CellStyle_Border; cell_isOK.SetCellValue(dt.Rows[i]["是否合格"].ToString()); } } catch { } } private static void WriteHead2005_Material(ISheet sheet, int startIndex, DataTable dt) { try { IRow row4 = sheet.CreateRow(startIndex); ICell cell4_1 = row4.CreateCell(0); CellRangeAddress region1 = new CellRangeAddress(startIndex, startIndex, 0, 10); sheet.AddMergedRegion(region1); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell4_1.CellStyle = CellStyle_Border; cell4_1.SetCellValue("物料信息"); IRow row5 = sheet.CreateRow(startIndex + 1); // 工位号 ICell cell5_1 = row5.CreateCell(0); cell5_1.CellStyle = CellStyle_Border; cell5_1.SetCellValue("工位号"); // 零件数量 ICell cell5_2 = row5.CreateCell(1); CellRangeAddress region2 = new CellRangeAddress(startIndex + 1, startIndex + 1, 1, 2); sheet.AddMergedRegion(region2); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_2.CellStyle = CellStyle_Border; cell5_2.SetCellValue("零件数量"); // 零件名称 ICell cell5_3 = row5.CreateCell(3); CellRangeAddress region3 = new CellRangeAddress(startIndex + 1, startIndex + 1, 3, 4); sheet.AddMergedRegion(region3); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region3, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_3.CellStyle = CellStyle_Border; cell5_3.SetCellValue("零件名称"); // 物料号 ICell cell5_4 = row5.CreateCell(5); CellRangeAddress region4 = new CellRangeAddress(startIndex + 1, startIndex + 1, 5, 6); sheet.AddMergedRegion(region4); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region4, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_4.CellStyle = CellStyle_Border; cell5_4.SetCellValue("物料号"); // 物料条码 ICell cell5_5 = row5.CreateCell(7); CellRangeAddress region5 = new CellRangeAddress(startIndex + 1, startIndex + 1, 7, 8); sheet.AddMergedRegion(region5); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region5, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_5.CellStyle = CellStyle_Border; cell5_5.SetCellValue("物料条码"); // 操作时间 ICell cell5_6 = row5.CreateCell(9); CellRangeAddress region6 = new CellRangeAddress(startIndex + 1, startIndex + 1, 9, 10); sheet.AddMergedRegion(region6); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region6, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell5_6.CellStyle = CellStyle_Border; cell5_6.SetCellValue("操作时间"); //填充数据 for (int i = 0; i < dt.Rows.Count; i++) { int rowIndex = i + startIndex + 2; IRow row = sheet.CreateRow(rowIndex); // 工位号 ICell cell_opName = row.CreateCell(0); cell_opName.SetCellValue(dt.Rows[i]["工位号"].ToString()); cell_opName.CellStyle = CellStyle_Border; // 零件数量 ICell cell_arriveTime = row.CreateCell(1); CellRangeAddress region7 = new CellRangeAddress(rowIndex, rowIndex, 1, 2); sheet.AddMergedRegion(region7); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region7, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_arriveTime.CellStyle = CellStyle_Border; cell_arriveTime.SetCellValue(dt.Rows[i]["零件数量"].ToString()); // 零件名称 ICell cell_leaveTime = row.CreateCell(3); CellRangeAddress region8 = new CellRangeAddress(rowIndex, rowIndex, 3, 4); sheet.AddMergedRegion(region8); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region8, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_leaveTime.CellStyle = CellStyle_Border; cell_leaveTime.SetCellValue(dt.Rows[i]["零件名称"].ToString()); // 物料号 ICell cell_onlineTime = row.CreateCell(5); CellRangeAddress region9 = new CellRangeAddress(rowIndex, rowIndex, 5, 6); sheet.AddMergedRegion(region9); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region9, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_onlineTime.CellStyle = CellStyle_Border; cell_onlineTime.SetCellValue(dt.Rows[i]["物料号"].ToString()); // 物料条码 ICell cell_opCode = row.CreateCell(7); CellRangeAddress region10 = new CellRangeAddress(rowIndex, rowIndex, 7, 8); sheet.AddMergedRegion(region10); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region10, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_opCode.CellStyle = CellStyle_Border; cell_opCode.SetCellValue(dt.Rows[i]["物料条码"].ToString()); // 操作时间 ICell cell_isOK = row.CreateCell(9); CellRangeAddress region11 = new CellRangeAddress(rowIndex, rowIndex, 9, 10); sheet.AddMergedRegion(region11); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region11, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell_isOK.CellStyle = CellStyle_Border; cell_isOK.SetCellValue(dt.Rows[i]["操作时间"].ToString()); } } catch { } } private static void WriteHead2005_Quality(ISheet sheet, int startIndex, DataTable dt) { try { IRow row4 = sheet.CreateRow(startIndex); ICell cell4_1 = row4.CreateCell(0); CellRangeAddress region1 = new CellRangeAddress(startIndex, startIndex, 0, 10); sheet.AddMergedRegion(region1); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); cell4_1.CellStyle = CellStyle_Border; cell4_1.SetCellValue("质量信息"); IRow row5 = sheet.CreateRow(startIndex + 1); // 工位号 ICell cell5_1 = row5.CreateCell(0); cell5_1.CellStyle = CellStyle_Border; cell5_1.SetCellValue("工位号"); // 测量位置 ICell cell5_2 = row5.CreateCell(1); cell5_2.CellStyle = CellStyle_Border; cell5_2.SetCellValue("测量位置"); // 测量项目 ICell cell5_3 = row5.CreateCell(2); cell5_3.CellStyle = CellStyle_Border; cell5_3.SetCellValue("测量项目"); // 测量值 ICell cell5_4 = row5.CreateCell(3); cell5_4.CellStyle = CellStyle_Border; cell5_4.SetCellValue("测量值"); // 理论值 ICell cell5_5 = row5.CreateCell(4); cell5_5.CellStyle = CellStyle_Border; cell5_5.SetCellValue("理论值"); // 上限值 ICell cell5_6 = row5.CreateCell(5); cell5_6.CellStyle = CellStyle_Border; cell5_6.SetCellValue("上限值"); // 下限值 ICell cell5_7 = row5.CreateCell(6); cell5_7.CellStyle = CellStyle_Border; cell5_7.SetCellValue("下限值"); // 测量单位 ICell cell5_8 = row5.CreateCell(7); cell5_8.CellStyle = CellStyle_Border; cell5_8.SetCellValue("测量单位"); // 生产日期 ICell cell5_9 = row5.CreateCell(8); cell5_9.CellStyle = CellStyle_Border; cell5_9.SetCellValue("生产日期"); // 操作者工号 ICell cell5_10 = row5.CreateCell(9); cell5_10.CellStyle = CellStyle_Border; cell5_10.SetCellValue("操作者工号"); // 是否合格 ICell cell5_11 = row5.CreateCell(10); cell5_11.CellStyle = CellStyle_Border; cell5_11.SetCellValue("是否合格"); //填充数据 for (int i = 0; i < dt.Rows.Count; i++) { int rowIndex = i + startIndex + 2; IRow row = sheet.CreateRow(rowIndex); // 工位号 ICell cell_opName = row.CreateCell(0); cell_opName.CellStyle = CellStyle_Border; cell_opName.SetCellValue(dt.Rows[i]["工位号"].ToString()); // 测量位置 ICell cell_arriveTime = row.CreateCell(1); cell_arriveTime.CellStyle = CellStyle_Border; cell_arriveTime.SetCellValue(dt.Rows[i]["测量位置"].ToString()); // 测量项目 ICell cell_leaveTime = row.CreateCell(2); cell_leaveTime.CellStyle = CellStyle_Border; cell_leaveTime.SetCellValue(dt.Rows[i]["测量项目"].ToString()); // 测量值 ICell cell_onlineTime = row.CreateCell(3); cell_onlineTime.CellStyle = CellStyle_Border; cell_onlineTime.SetCellValue(dt.Rows[i]["测量值"].ToString()); // 理论值 ICell cell_opCode = row.CreateCell(4); cell_opCode.CellStyle = CellStyle_Border; cell_opCode.SetCellValue(dt.Rows[i]["理论值"].ToString()); // 上限值 ICell cell_isOK = row.CreateCell(5); cell_isOK.CellStyle = CellStyle_Border; cell_isOK.SetCellValue(dt.Rows[i]["上限值"].ToString()); // 下限值 ICell cell_isOK2 = row.CreateCell(6); cell_isOK2.CellStyle = CellStyle_Border; cell_isOK2.SetCellValue(dt.Rows[i]["下限值"].ToString()); // 测量单位 ICell cell_isOK3 = row.CreateCell(7); cell_isOK3.CellStyle = CellStyle_Border; cell_isOK3.SetCellValue(dt.Rows[i]["测量单位"].ToString()); // 生产日期 ICell cell_isOK4 = row.CreateCell(8); cell_isOK4.CellStyle = CellStyle_Border; cell_isOK4.SetCellValue(dt.Rows[i]["生产日期"].ToString()); // 操作者工号 ICell cell_isOK5 = row.CreateCell(9); cell_isOK5.CellStyle = CellStyle_Border; cell_isOK5.SetCellValue(dt.Rows[i]["操作者工号"].ToString()); // 是否合格 ICell cell_isOK6 = row.CreateCell(10); cell_isOK6.CellStyle = CellStyle_Border; cell_isOK6.SetCellValue(dt.Rows[i]["是否合格"].ToString()); } } catch { } } private static void WriteHead2005_Style_border(HSSFWorkbook wk) { CellStyle_Border = wk.CreateCellStyle(); IFont font = CellStyle_Border.GetFont(wk); font.FontName = "等线"; //字体 font.FontHeightInPoints = 11; //字体高度（Excel中的字号） //上边框 CellStyle_Border.BorderTop = BorderStyle.Thin; //下边框 CellStyle_Border.BorderBottom = BorderStyle.Thin; //左边框 CellStyle_Border.BorderLeft = BorderStyle.Thin; //右边框 CellStyle_Border.BorderRight = BorderStyle.Thin; } private static void WriteHead2005_Style_center(HSSFWorkbook wk) { CellStyle_center = wk.CreateCellStyle(); //水平方向对齐 居中对齐 CellStyle_center.Alignment = HorizontalAlignment.Center; //垂直方向对齐 底部对齐 CellStyle_center.VerticalAlignment = VerticalAlignment.Bottom; } static private void OutExcelFileMemoryStream(IWorkbook wk, out byte[] bytes) { MemoryStream ms = new MemoryStream(); wk.Write(ms); bytes = ms.ToArray(); } }