文章标题

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();
    }