首页 > 代码库 > NOPI导出Excel

NOPI导出Excel

            DataTable dt = GetMonthQsEditData(mainId);//数据              using (FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Files/Templetes/月清算模板.xls"), FileMode.Open, FileAccess.Read))            {                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);                ISheet sheet = hssfworkbook.GetSheetAt(0);                int startRow = 2;//开始行                int count = 0;                List<int> merg = new List<int>();                string unitId = "";                IRow row = null;                for (int i = 0; i < dt.Rows.Count; i++)                {                    if (i == 0)                    {                        row = sheet.GetRow(startRow);                    }                    else                    {                        row = CopyRow(hssfworkbook, sheet, startRow, startRow + i,false);                    }                    if (unitId != dt.Rows[i]["UNITID"].ToString())                    {                        if (count != 0)                        {                            merg.Add(count);                        }                        count = 0;                        row.GetCell(0).SetCellValue(dt.Rows[i]["UNITNAME"].ToString());                        row.GetCell(1).SetCellValue(dt.Rows[i]["SFNAME"].ToString());                        row.GetCell(2).SetCellValue(dt.Rows[i]["SFID"].ToString());                        row.GetCell(3).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARTOTAL"]));                        row.GetCell(4).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["MONTHPROPORTION"]));                        row.GetCell(5).SetCellFormula("D" + (3 + i) + "*E" + (3 + i) + "/100");                        row.GetCell(6).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["ACTUAL"]));                        unitId = dt.Rows[i]["UNITID"].ToString();                    }                    else                    {                        row.GetCell(0).SetCellValue(dt.Rows[i]["UNITNAME"].ToString());                        row.GetCell(1).SetCellValue(dt.Rows[i]["SFNAME"].ToString());                        row.GetCell(2).SetCellValue(dt.Rows[i]["SFID"].ToString());                        row.GetCell(3).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["YEARTOTAL"]));                        row.GetCell(4).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["MONTHPROPORTION"]));                        row.GetCell(5).SetCellFormula("D" + (3 + i) + "*E" + (3 + i) + "/100");                        row.GetCell(6).SetCellValue(string.Format("{0:0.0}", dt.Rows[i]["ACTUAL"]));                    }                    count++;                }                int rowNum = 2;                foreach (int v in merg)                {                    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum, rowNum + v - 1, 0, 0);                    sheet.AddMergedRegion(cellRangeAddress);                    rowNum = rowNum + v;                }                MemoryStream ms = new MemoryStream();                hssfworkbook.Write(ms);                HttpContext curContext = HttpContext.Current;                curContext.Response.ContentType = "application/vnd.ms-excel";                curContext.Response.ContentEncoding = Encoding.UTF8;                curContext.Response.Charset = "";                curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(pjName + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));                curContext.Response.BinaryWrite(ms.ToArray());                // curContext.Response.End();                curContext.ApplicationInstance.CompleteRequest();                hssfworkbook = null;                ms.Close();                ms.Dispose();            }

 

NOPI导出Excel