public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string productCode = Request.QueryString["productCode"] ?? ""; decimal minLimited = 100000; decimal maxLimited = 100000; decimal assemblyTime = 3600; if (Request.QueryString["minLimited"] != null && Request.QueryString["minLimited"] != "") { minLimited = decimal.Parse(Request.QueryString["minLimited"]); } if (Request.QueryString["maxLimited"] != null && Request.QueryString["maxLimited"] != "") { maxLimited = decimal.Parse(Request.QueryString["maxLimited"]); } if (Request.QueryString["assemblyTime"] != null && Request.QueryString["assemblyTime"] != "") { assemblyTime = decimal.Parse(Request.QueryString["assemblyTime"]); } THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = ProductWarningService.GetProductWarning(page, rows, productCode, minLimited, maxLimited, assemblyTime); ep.HeadTitle1 = "产品预警信息设置"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return(new FileStreamResult(ms, "application/ms-excel")); }
public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string productCode = Request.QueryString["productCode"] ?? ""; decimal minLimited = 100000; decimal maxLimited = 100000; decimal assemblyTime = 3600; if (Request.QueryString["minLimited"] != null && Request.QueryString["minLimited"] != "") { minLimited = decimal.Parse(Request.QueryString["minLimited"]); } if (Request.QueryString["maxLimited"] != null && Request.QueryString["maxLimited"] != "") { maxLimited = decimal.Parse(Request.QueryString["maxLimited"]); } if (Request.QueryString["assemblyTime"] != null && Request.QueryString["assemblyTime"] != "") { assemblyTime = decimal.Parse(Request.QueryString["assemblyTime"]); } THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = ProductWarningService.GetProductWarning(page, rows, productCode, minLimited, maxLimited, assemblyTime); ep.HeadTitle1 = "产品预警信息设置"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return new FileStreamResult(ms, "application/ms-excel"); }
//转换成execl; public FileStreamResult CreateExcelToClient() { string tablestructstr = Request.QueryString["tablestructstr"]; string head = Request.QueryString["heard"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); THOK.Common.PrintHandle.setbaseinfodata(tablestructstr); ep.DT1 = THOK.Common.PrintHandle.baseinfoprint; ep.HeadTitle1 = head; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return new FileStreamResult(ms, "application/ms-excel"); }
public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string queryString = Request.QueryString["queryString"]; string value = Request.QueryString["value"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CellService.GetCellByE(page, rows, queryString, value); ep.HeadTitle1 = "储位卷烟预设"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return new FileStreamResult(ms, "application/ms-excel"); }
public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string type = Request.QueryString["type"]; string id = Request.QueryString["id"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CellService.GetCell(page, rows, type, id); ep.HeadTitle1 = "仓库信息"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return new FileStreamResult(ms, "application/ms-excel"); }
public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string type = Request.QueryString["type"]; string id = Request.QueryString["id"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CellService.GetCell(page, rows, type, id); ep.HeadTitle1 = "仓库信息"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return(new FileStreamResult(ms, "application/ms-excel")); }
public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string queryString = Request.QueryString["queryString"]; string value = Request.QueryString["value"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CellService.GetCellByE(page, rows, queryString, value); ep.HeadTitle1 = "储位卷烟预设"; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return(new FileStreamResult(ms, "application/ms-excel")); }
// /LoginLog/CreateExcelToClient/ public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string CITY_NAME = Request.QueryString["CITY_NAME"]; string DESCRIPTION = Request.QueryString["DESCRIPTION"]; string IS_ACTIVE = Request.QueryString["IS_ACTIVE"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CityService.GetCityExcel(page, rows, CITY_NAME, DESCRIPTION, IS_ACTIVE); ep.HeadTitle1 = "城市信息"; ep.BigHeadColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; ep.ColHeadColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; ep.ContentColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return new FileStreamResult(ms, "application/ms-excel"); }
// /LoginLog/CreateExcelToClient/ public FileStreamResult CreateExcelToClient() { int page = 0, rows = 0; string CITY_NAME = Request.QueryString["CITY_NAME"]; string DESCRIPTION = Request.QueryString["DESCRIPTION"]; string IS_ACTIVE = Request.QueryString["IS_ACTIVE"]; THOK.NPOI.Models.ExportParam ep = new THOK.NPOI.Models.ExportParam(); ep.DT1 = CityService.GetCityExcel(page, rows, CITY_NAME, DESCRIPTION, IS_ACTIVE); ep.HeadTitle1 = "城市信息"; ep.BigHeadColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; ep.ColHeadColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; ep.ContentColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; System.IO.MemoryStream ms = THOK.NPOI.Service.ExportExcel.ExportDT(ep); return(new FileStreamResult(ms, "application/ms-excel")); }
/// <summary>填充内容</summary> static void FillContent(HSSFRow hssfRow , DataColumn column, DataRow row , HSSFCellStyle contentStyle, HSSFFont contentFont , HSSFCellStyle contentDateStyle , HSSFCellStyle contentStyleDailyBalance, HSSFFont fontDailyBalance , THOK.NPOI.Models.ExportParam ep , HSSFSheet sheet, string headTextStrA) { HSSFCell newCell = hssfRow.CreateCell(column.Ordinal) as HSSFCell; #region 当数据访问转换DataTime时生效 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; contentDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #endregion string columnF = row[column].ToString(); decimal i; bool b = decimal.TryParse(columnF, out i); #region 判断如果是仓库库存日结核对 if (ep.ContentModule == "DailyBalance" && sheet == workbook.GetSheet(headTextStrA) && ((column.Ordinal == 5 && b == false) || (column.Ordinal == 6 && b == false) || (column.Ordinal == 7 && b == false) || (column.Ordinal == 8 && b == false) || (column.Ordinal == 9 && b == false) || (column.Ordinal == 10 && b == false))) { fontDailyBalance.FontName = ep.ColHeadFont; fontDailyBalance.FontHeightInPoints = ep.ColHeadSize; fontDailyBalance.Color = ep.ContentModuleColor; contentStyleDailyBalance.SetFont(fontDailyBalance); if (ep.ColHeadBorder == true) { contentStyleDailyBalance.BorderBottom = BorderStyle.THIN; contentStyleDailyBalance.BorderLeft = BorderStyle.THIN; contentStyleDailyBalance.BorderRight = BorderStyle.THIN; contentStyleDailyBalance.BorderTop = BorderStyle.THIN; } hssfRow.GetCell(column.Ordinal).CellStyle = contentStyleDailyBalance; } #endregion else { contentFont.FontName = ep.ColHeadFont; contentFont.FontHeightInPoints = ep.ColHeadSize; contentFont.Color = ep.ContentColor; contentStyle.SetFont(contentFont); //画边框 if (ep.ColHeadBorder == true) { contentStyle.BorderBottom = BorderStyle.THIN; contentStyle.BorderLeft = BorderStyle.THIN; contentStyle.BorderRight = BorderStyle.THIN; contentStyle.BorderTop = BorderStyle.THIN; } hssfRow.GetCell(column.Ordinal).CellStyle = contentStyle; } string drValue = row[column].ToString(); THOK.NPOI.Common.ExportExcelHeper.ChangeFormat(column, drValue, newCell, contentDateStyle); }
/// <summary>导出EXCEL单表双表</summary> public static MemoryStream ExportDT(THOK.NPOI.Models.ExportParam ep) { #region 变量 string exportDate = "导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); double columnWidth = ep.ColHeadSize - 9; short printHeight = 0; short printWidth = 10; int sheetCount = 65500; //一个Sheet中最多存65536行数据 int page = 0; #endregion #region 浏览器下载 THOK.NPOI.Common.ExportExcelHeper.BrowserLoad(ep.HeadTitle1); #endregion #region 创建工作表 workbook = new HSSFWorkbook(); HSSFSheet sheet = null; #endregion #region 创建样式 HSSFCellStyle contentDateStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容时间 单元格 HSSFCellStyle styleHead = workbook.CreateCellStyle() as HSSFCellStyle; //大标题 单元格 HSSFFont fontHead = workbook.CreateFont() as HSSFFont; //大标题 字体 HSSFCellStyle styleDate = workbook.CreateCellStyle() as HSSFCellStyle; //导出时间 单元格 HSSFCellStyle styleColHead = workbook.CreateCellStyle() as HSSFCellStyle; //列头 单元格 HSSFFont fontColHead = workbook.CreateFont() as HSSFFont; //列头 字体 HSSFCellStyle contentStyle = workbook.CreateCellStyle() as HSSFCellStyle; //内容 单元格 HSSFFont fontContent = workbook.CreateFont() as HSSFFont; //内容 字体 contentStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //内容 设置所有列整型格式,也可以通过数据访问层来改变内容格式 HSSFCellStyle contentStyleDailyBalance = workbook.CreateCellStyle() as HSSFCellStyle; //特殊模块 单元格 HSSFFont fontDailyBalance = workbook.CreateFont() as HSSFFont; //特殊模块 字体 #endregion #region 全局样式 HSSFCellStyle headStyle = GetTitleStyle(ep.BigHeadFont, ep.BigHeadSize, ep.BigHeadColor, styleHead, fontHead); HSSFCellStyle dateStyle = GetExportDate(styleDate); HSSFCellStyle colHeadStyle = GetColumnStyle(ep.ColHeadFont, ep.ColHeadSize, ep.ColHeadColor, ep.ColHeadBorder, styleColHead, fontColHead); #endregion #region 取得列宽 int[] arrColWidth1 = new int[0]; int[] arrColWidth2 = new int[0]; if (ep.DT1 != null && ep.HeadTitle1 != null) { arrColWidth1 = new int[ep.DT1.Columns.Count]; GetColumnWidth(ep.DT1, arrColWidth1); } if (ep.DT2 != null && ep.HeadTitle2 != null) { arrColWidth2 = new int[ep.DT2.Columns.Count]; GetColumnWidth(ep.DT2, arrColWidth2); } #endregion #region 创建EXCEL 表一 if (ep.DT1 != null && ep.HeadTitle1 != null) { int dt1count = ep.DT1.Rows.Count; #region 判断多少页 if (dt1count % sheetCount == 0) { page = dt1count / sheetCount; } else { page = dt1count / sheetCount + 1; } #endregion for (int a = 0; a < page; a++) { #region SHEET分页标题 string sheetNum = a.ToString(); if (a == 0) { sheetNum = sheetNum.Substring(0, a.ToString().Length - 1); } string headTitle1_sheetNum = ep.HeadTitle1 + sheetNum; sheet = workbook.CreateSheet(headTitle1_sheetNum) as HSSFSheet; #endregion #region SHEET打印设置 sheet.PrintSetup.FitHeight = printHeight; sheet.PrintSetup.FitWidth = printWidth; #endregion int rowIndex1 = 0; #region 内容分页 DataTable newdt1 = THOK.NPOI.Common.ExportExcelHeper.SetPage(ep.DT1, a + 1, sheetCount); #endregion #region 填充数据 foreach (DataRow row in newdt1.Rows) { if (rowIndex1 == 0) { if (rowIndex1 != 0) { sheet = workbook.CreateSheet() as HSSFSheet; sheet.PrintSetup.FitHeight = printHeight; sheet.PrintSetup.FitWidth = printWidth; } #region 填充“表头”和它的样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4); headerRow.CreateCell(0).SetCellValue(ep.HeadTitle1); headerRow.GetCell(0).CellStyle = headStyle; CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt1.Columns.Count - 1); sheet.AddMergedRegion(region); if (ep.BigHeadBorder == true) { sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index);//给合并的画线 } } #endregion #region 填充“导出时间”和它的样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; headerRow.CreateCell(0).SetCellValue(exportDate); headerRow.GetCell(0).CellStyle = dateStyle; CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt1.Columns.Count - 1); sheet.AddMergedRegion(region); if (ep.ColHeadBorder == true) { sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index); } } #endregion #region 填充“列头”和它的样式 { HSSFRow headerRow = sheet.CreateRow(2) as HSSFRow; headerRow.HeightInPoints = Convert.ToInt16(ep.ColHeadSize * 1.4); foreach (DataColumn column in newdt1.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle; sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth1[column.Ordinal] + columnWidth) * 256));//设置列宽 } } #endregion rowIndex1 = 3; } #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex1) as HSSFRow; foreach (DataColumn column in newdt1.Columns) { FillContent(dataRow, column, row, contentStyle, fontContent , contentDateStyle , contentStyleDailyBalance, fontDailyBalance , ep , sheet, headTitle1_sheetNum); } rowIndex1++; #endregion } #endregion } } #endregion #region 创建EXCEL 表二 if (ep.DT2 != null && ep.HeadTitle2 != null) { int dt2count = ep.DT2.Rows.Count; if (dt2count % sheetCount == 0) { page = dt2count / sheetCount; } else { page = dt2count / sheetCount + 1; } for (int a = 0; a < page; a++) { int rowIndex2 = 0; DataTable newdt2 = THOK.NPOI.Common.ExportExcelHeper.SetPage(ep.DT2, a + 1, sheetCount); string strA = a.ToString(); if (a == 0) { strA = strA.Substring(0, a.ToString().Length - 1); } string headText2strA = ep.HeadTitle2 + strA; #region 填充数据 foreach (DataRow row in newdt2.Rows) { if (rowIndex2 == 0) { HSSFRow headerRow; if (rowIndex2 != 1) { sheet = workbook.CreateSheet(headText2strA) as HSSFSheet; sheet.PrintSetup.FitHeight = printHeight; sheet.PrintSetup.FitWidth = printWidth; } #region 填充表头、样式 { headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = Convert.ToInt16(ep.BigHeadSize * 1.4); headerRow.CreateCell(0).SetCellValue(ep.HeadTitle2); headerRow.GetCell(0).CellStyle = headStyle; CellRangeAddress region = new CellRangeAddress(0, 0, 0, newdt2.Columns.Count - 1); sheet.AddMergedRegion(region); if (ep.BigHeadBorder == true) { sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index); } } #endregion #region 导出时间、样式 { headerRow = sheet.CreateRow(1) as HSSFRow; headerRow.CreateCell(0).SetCellValue(exportDate); headerRow.GetCell(0).CellStyle = dateStyle; CellRangeAddress region = new CellRangeAddress(1, 1, 0, newdt2.Columns.Count - 1); sheet.AddMergedRegion(region); if (ep.ColHeadBorder == true) { sheet.SetEnclosedBorderOfRegion(region, BorderStyle.THIN, HSSFColor.BLACK.index); } } #endregion #region 填充列头、样式 { headerRow = sheet.CreateRow(2) as HSSFRow; foreach (DataColumn column in newdt2.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = colHeadStyle; sheet.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth2[column.Ordinal] + columnWidth) * 256)); } } rowIndex2 = 3; #endregion } #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex2) as HSSFRow; foreach (DataColumn column in newdt2.Columns) { FillContent(dataRow, column, row, contentStyle, fontContent , contentDateStyle , contentStyleDailyBalance, fontDailyBalance , ep , sheet, headText2strA); } rowIndex2++; #endregion } #endregion } } #endregion #region 页眉 页脚 sheet.Header.Left = ep.HeaderFooter.ToString(); sheet.Header.Center = ep.HeaderFooter[1].ToString(); sheet.Header.Right = ep.HeaderFooter[2].ToString(); sheet.Footer.Left = ep.HeaderFooter[3].ToString(); sheet.Footer.Center = ep.HeaderFooter[4].ToString(); sheet.Footer.Right = ep.HeaderFooter[5].ToString(); #endregion #region 返回内存流 MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); #endregion }