protected void txtout_Click(object sender, EventArgs e) {
string word = ViewState["SelectedWOID"].ToString(); List<Entity.QualManage.PieceStem> list = bllPS.GetexcelInfo(word); List<Entity.QualManage.BladeConstitute> list1 = bllPI.GetexcelInfo(word); int count = list.Count/10; string time = list[0].DetectTime; NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1"); //ICellStyle cellStyle = book.CreateCellStyle(); //设置单元格的样式：水平对齐居中 //cellStyle.VerticalAlignment = VerticalAlignment.Justify;//垂直对齐(默认应该为center，如果center无效则用justify) //cellStyle.Alignment = HorizontalAlignment.Center;//水平对齐 //row2.CreateCell(0).CellStyle = cellstyle; //NPOI .SS.UserModel.i //标题 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("叶片结构原始表"); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 80);//合并单元格 sheet.AddMergedRegion(region); // NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue("日期"); row1.CreateCell(1).SetCellValue(time); row1.CreateCell(5).SetCellValue("厂家"); row1.CreateCell(6).SetCellValue(""); row1.CreateCell(8).SetCellValue("班次"); string team = list [0].TeamName; string banci = list [0].ShiftName ; row1 .CreateCell (9).SetCellValue (team); // 第一行 NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue("项目"); CellRangeAddress region0 = new CellRangeAddress(2, 4, 0, 0);//合并单元格 sheet.AddMergedRegion(region0); for (int i = 1; i <=count ; i++) { row2.CreateCell(1).SetCellValue("检测次数"); string no = list[i-1].DetectNo; row2.CreateCell(2*i ).SetCellValue(no); CellRangeAddress region1 = new CellRangeAddress(2, 2, 2*i, 2*i+1 );//合并单元格 sheet.AddMergedRegion(region1); } // 第二行 NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(3); row3.CreateCell(1).SetCellValue("取样时间"); row3.CreateCell(2).SetCellValue("8：00"); CellRangeAddress region2 = new CellRangeAddress(3, 3, 2, 3);//合并单元格 sheet.AddMergedRegion(region2); /// NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(4); row4.CreateCell(1).SetCellValue("单位"); row4.CreateCell(2).SetCellValue("g"); row4.CreateCell(3).SetCellValue("%"); // 叶片结构 NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(5); row5.CreateCell(0).SetCellValue("叶片结构"); row5.CreateCell(1).SetCellValue("叶片总重"); for (int i = 1; i <= count; i++) { string sw = list[i-1].SampleTotalWt.ToString(); row5.CreateCell(2*i).SetCellValue(sw); CellRangeAddress region9 = new CellRangeAddress(5, 5, 2*i, 2*i+1);//合并单元格 sheet.AddMergedRegion(region9); } CellRangeAddress region3 = new CellRangeAddress(5, 13, 0, 0);//合并单元格 sheet.AddMergedRegion(region3); CellRangeAddress region4 = new CellRangeAddress(5, 5, 2, 3);//合并单元格 sheet.AddMergedRegion(region4); //≥25.4mm NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(6); row6.CreateCell(1).SetCellValue("≥25.4mm"); for (int i = 1; i <=count; i++) { string w1 = list[i-1].SampleWt.ToString(); row6.CreateCell(2*i).SetCellValue(w1); } for (int i = 1; i <= count; i++) { string p1 = list[i - 1].DetectProp.ToString(); row6.CreateCell(2 * i+1).SetCellValue(p1); } //12.7-25.4mm NPOI.SS.UserModel.IRow row7 = sheet.CreateRow(7); row7.CreateCell(1).SetCellValue("12.7-25.4mm"); for (int i = 1 ; i <= count; i++) { string w2 = list[i +7*count -1].SampleWt.ToString(); row7.CreateCell(2*i).SetCellValue(w2); } for (int i = 1; i <= count; i++) { string p2 = list[i+7*count -1].DetectProp.ToString(); row7.CreateCell(2*i+1).SetCellValue(p2); } //≥12.7mm NPOI.SS.UserModel.IRow row8 = sheet.CreateRow(8); row8.CreateCell(1).SetCellValue("≥12.7mm"); for (int i = 1; i <= count; i++) { string w3 = list[i +count -1].SampleWt.ToString(); row8.CreateCell(2*i).SetCellValue(w3); } for (int i = 1; i <= count; i++) { string p3 = list[i +count -1].DetectProp.ToString(); row8.CreateCell(2*i+1).SetCellValue(p3); } //6.35-12.7mm NPOI.SS.UserModel.IRow row9 = sheet.CreateRow(9); row9.CreateCell(1).SetCellValue("6.35-12.7mm"); for (int i = 1; i <= count; i++) { string w4 = list[i +8*count - 1].SampleWt.ToString(); row9.CreateCell(2*i).SetCellValue(w4); } for (int i = 1; i <= count; i++) { string p4 = list[i +8*count- 1].DetectProp .ToString(); row9.CreateCell(2 * i+1).SetCellValue(p4); } //≥6.35mm NPOI.SS.UserModel.IRow row10 = sheet.CreateRow(10); row10.CreateCell(1).SetCellValue("≥6.35mm"); for (int i = 1; i <= count; i++) { string w5 = list[i+2*count - 1].SampleWt.ToString(); row10.CreateCell(2 * i).SetCellValue(w5); } for (int i = 1; i <= count; i++) { string p5 = list[i+2*count - 1].DetectProp.ToString(); row10.CreateCell(2 * i + 1).SetCellValue(p5); } //3.18-6.35mm NPOI.SS.UserModel.IRow row11 = sheet.CreateRow(11); row11.CreateCell(1).SetCellValue("3.18-6.35mm"); for (int i = 1; i <= count; i++) { string w6 = list[i+9*count - 1].SampleWt.ToString(); row11.CreateCell(2 * i).SetCellValue(w6); } for (int i = 1; i <= count; i++) { string p6 = list[i+9*count - 1].DetectProp.ToString(); row11.CreateCell(2 * i + 1).SetCellValue(p6); } //≥3.18mm NPOI.SS.UserModel.IRow row12 = sheet.CreateRow(12); row12.CreateCell(1).SetCellValue("≥3.18mm"); for (int i = 1; i <= count; i++) { string w7 = list[i+3*count - 1].SampleWt.ToString(); row12.CreateCell(2 * i).SetCellValue(w7); } for (int i = 1; i <= count; i++) { string p7 = list[i +3*count- 1].DetectProp.ToString(); row12.CreateCell(2 * i + 1).SetCellValue(p7); } //≤3.18mm NPOI.SS.UserModel.IRow row13 = sheet.CreateRow(13); row13.CreateCell(1).SetCellValue("≤3.18mm"); for (int i = 1; i <= count; i++) { string w8 = list[i+6*count - 1].SampleWt.ToString(); row13.CreateCell(2 * i).SetCellValue(w8); } for (int i = 1; i <= count; i++) { string p8 = list[i + 6 * count - 1].DetectProp.ToString(); row13.CreateCell(2 * i + 1).SetCellValue(p8); } //叶中含梗 NPOI.SS.UserModel.IRow row14 = sheet.CreateRow(14); row14.CreateCell(0).SetCellValue("叶重含梗"); CellRangeAddress region5 = new CellRangeAddress(14, 16, 0, 0);//合并单元格 sheet.AddMergedRegion(region5); row14.CreateCell(1).SetCellValue("总梗重"); for (int i = 1; i <= count; i++) { string w9 = list[i+4 * count - 1].SampleWt .ToString(); row14.CreateCell(2*i).SetCellValue(w9); CellRangeAddress region10 = new CellRangeAddress(14, 14, 2*i, 2*i+1);//合并单元格 sheet.AddMergedRegion(region10); } CellRangeAddress region6 = new CellRangeAddress(14, 14, 2, 3);//合并单元格 sheet.AddMergedRegion(region6); //叶中含梗 NPOI.SS.UserModel.IRow row15 = sheet.CreateRow(15); row15.CreateCell(1).SetCellValue("叶中含梗"); for (int i = 1; i <= count; i++) { string w9 = list[i+4*count -1].SampleWt .ToString (); row15.CreateCell(2*i).SetCellValue(w9); } for (int i = 1; i <= count; i++) { string p9 = list[i+4 * count - 1].DetectProp .ToString(); row15.CreateCell(2*i+1).SetCellValue(p9); } //OBJ NPOI.SS.UserModel.IRow row16 = sheet.CreateRow(16); row16.CreateCell(1).SetCellValue("OBJ"); for (int i = 1; i <= count; i++) { string w10 = list[i+5 * count - 1].SampleWt .ToString(); row16.CreateCell(2*i).SetCellValue(w10); } for (int i = 1; i <= count; i++) { string p10 = list[i+5* count - 1].DetectProp.ToString(); row16.CreateCell(2*i +1).SetCellValue(p10); } // 梗重含叶 NPOI.SS.UserModel.IRow row17= sheet.CreateRow(17); row17.CreateCell(0).SetCellValue("梗重含叶"); CellRangeAddress region7 = new CellRangeAddress(17, 19, 0, 0);//合并单元格 sheet.AddMergedRegion(region7); row17.CreateCell(1).SetCellValue("梗重"); for (int i = 1; i <= count; i++) { string w = list1[i-1].SampleTotalWt.ToString(); row17.CreateCell(2*i).SetCellValue(w); CellRangeAddress region11 = new CellRangeAddress(17, 17, 2*i, 2*i+1);//合并单元格 sheet.AddMergedRegion(region11); } CellRangeAddress region8 = new CellRangeAddress(17, 17, 2, 3);//合并单元格 sheet.AddMergedRegion(region8); NPOI.SS.UserModel.IRow row18= sheet.CreateRow(18); row18.CreateCell(1).SetCellValue("叶重"); for (int i = 1; i <= count; i++) { string w11 = list1[i - 1].LeafWt.ToString(); row18.CreateCell(2 * i).SetCellValue(w11); } for (int i = 1; i <= count; i++) { string p11 = list1[i - 1].LeafProp.ToString(); row18.CreateCell(2 * i + 1).SetCellValue(p11); } NPOI.SS.UserModel.IRow row19= sheet.CreateRow(19); row19.CreateCell(1).SetCellValue("≥20mm烟梗"); for (int i = 1; i <= count; i++) { string w12 = list1[i+count - 1].LeafWt.ToString(); row19.CreateCell(2 * i).SetCellValue(w12); } for (int i = 1; i <= count; i++) { string p12 = list1[i+count - 1].LeafProp.ToString(); row19.CreateCell(2 * i + 1).SetCellValue(p12); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }