/// <summary> /// Set the font for this style /// </summary> /// <param name="font">a font object Created or retreived from the HSSFWorkbook object</param> public void SetFont(NPOI.SS.UserModel.IFont font) { _format.IsIndentNotParentFont = (true); short fontindex = font.Index; _format.FontIndex = (fontindex); }
private void CopyFontStyle(IWorkbook wb, XSSFCell oldCell, XSSFCellStyle newCellStyle) { NPOI.SS.UserModel.IFont font = destinationWb.CreateFont(); NPOI.SS.UserModel.IFont sourceFont = oldCell.CellStyle.GetFont(wb); font.FontName = sourceFont.FontName; font.FontHeightInPoints = sourceFont.FontHeightInPoints; font.Boldweight = sourceFont.Boldweight; newCellStyle.SetFont(font); }
private void ExportDataItem(System.Data.DataTable TotalDT) { string filename = "成本调整单" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("成本调整单查询.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); #region 写入数据 for (int i = 0; i < TotalDT.Rows.Count; i++) { IRow row = sheet0.CreateRow(i + 3); row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号 row.CreateCell(1).SetCellValue(TotalDT.Rows[i]["SI_MARID"].ToString()); row.CreateCell(2).SetCellValue(TotalDT.Rows[i]["MNAME"].ToString()); row.CreateCell(3).SetCellValue(TotalDT.Rows[i]["CAIZHI"].ToString()); row.CreateCell(4).SetCellValue(TotalDT.Rows[i]["GUIGE"].ToString()); row.CreateCell(5).SetCellValue(TotalDT.Rows[i]["SI_YEAR"].ToString()); row.CreateCell(6).SetCellValue(TotalDT.Rows[i]["SI_PERIOD"].ToString()); row.CreateCell(7).SetCellValue(TotalDT.Rows[i]["DIFF"].ToString()); NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 11; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 7; j++) { row.Cells[j].CellStyle = cells; } } #endregion for (int i = 0; i <= 7; i++) { sheet0.AutoSizeColumn(i); } sheet0.ForceFormulaRecalculation = true; MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
/// <summary> /// 写入DataTable到Excel /// </summary> /// <param name="dt"></param> /// <param name="excelFile"></param> public virtual void writeDataTableToExcel(DataTable dt, string excelFile) { //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dt); #region 输出文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); #endregion }
private static ICellStyle GetCellStyleHead(IWorkbook hssfworkbook) { ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); CellStyleBasic(cellStyle); cellStyle.WrapText = true;//表头自动换行 NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyle.SetFont(font);//字体加粗 return(cellStyle); }
private void CopyCellStyle(IWorkbook toBook, IWorkbook fromBook, ICell toCell, ICell fromCell) { ICellStyle style = toBook.CreateCellStyle(); IFont toFont = toBook.CreateFont(); CopyCellStyle(fromCell.CellStyle, style); IFont fromFont = fromCell.CellStyle.GetFont(fromBook); CopyFont(toFont, fromFont); //style.SetFont(toFont); toCell.CellStyle = style; }
private static void CopyFont(IFont toFont, IFont fromFont) { toFont.Color = fromFont.Color; toFont.FontHeightInPoints = fromFont.FontHeightInPoints; toFont.IsBold = fromFont.IsBold; toFont.IsItalic = fromFont.IsItalic; toFont.Underline = fromFont.Underline; //toFont.Boldweight = fromFont.Boldweight; //toFont.Charset = fromFont.Charset; //toFont.FontHeight = fromFont.FontHeight; toFont.FontName = fromFont.FontName; //toFont.IsStrikeout = fromFont.IsStrikeout; //toFont.TypeOffset = fromFont.TypeOffset; }
/// <summary> /// 设置字体颜色大小 /// </summary> public static Stream ApplyStyleToFile(Stream inputStream, string fontName, short fontSize) { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); ICellStyle style = workbook.CreateCellStyle(); ICellStyle style1 = workbook.CreateCellStyle(); IFont cellFont = workbook.CreateFont(); cellFont.FontHeightInPoints = 10; cellFont.FontName = "黑体"; IFont font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; font.FontName = fontName; font.Boldweight = short.MaxValue; style.SetFont(font); style.Alignment = HorizontalAlignment.Center; style1.SetFont(cellFont); style1.Alignment = HorizontalAlignment.Center; MemoryStream ms = new MemoryStream(); int i; for (i = 0; i < workbook.NumberOfSheets; i++) { ISheet sheets = workbook.GetSheetAt(0); for (int k = sheets.FirstRowNum; k <= sheets.LastRowNum; k++) { sheets.AutoSizeColumn(k); IRow row = sheets.GetRow(k); for (int l = row.FirstCellNum; l < row.LastCellNum; l++) { if (k == 0) { ICell cell = row.GetCell(l); cell.CellStyle = style; } else { ICell cell = row.GetCell(l); cell.CellStyle = style1; } } } } workbook.Write(ms); ms.Flush(); return(ms); }
private void ExportDataItem(DataTable dt) { string filename = "售后服务处理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("售后服务.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int i = 0, length = dt.Rows.Count; i < length; i++) { IRow row = sheet0.CreateRow(i + 2); row.CreateCell(0).SetCellValue(i + 1); row.Cells[0].CellStyle = cells; for (int j = 0, m = dt.Columns.Count; j < m; j++) { row.CreateCell(j + 1).SetCellValue(dt.Rows[i][j].ToString()); row.Cells[j + 1].CellStyle = cells; } } MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
public static void GridToExcelByNPOI(string sheetname, string lotid, string date, string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, int mergeColIndex = -1) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet(sheetname); //汇总栏 ICellStyle SummStyle = workbook.CreateCellStyle(); SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont summfont = workbook.CreateFont(); summfont.Boldweight = (short)FontBoldWeight.Bold; summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.SetFont(summfont); SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //Lot ID IRow summRow_1 = sheet.CreateRow(0); summRow_1.Height = 20 * 20; ICell cell_1 = summRow_1.CreateCell(0); cell_1.SetCellValue("Lot ID:"); cell_1.CellStyle = SummStyle; ICell cell_2 = summRow_1.CreateCell(1); cell_2.SetCellValue(lotid); cell_2.CellStyle = SummStyle; ICell cell_3 = summRow_1.CreateCell(2); cell_3.SetCellValue("Date:"); cell_3.CellStyle = SummStyle; ICell cell_4 = summRow_1.CreateCell(3); cell_4.SetCellValue(date); cell_4.CellStyle = SummStyle; ICell cell_5 = summRow_1.CreateCell(4); cell_5.SetCellValue("Create Date:"); cell_5.CellStyle = SummStyle; ICell cell_6 = summRow_1.CreateCell(5); cell_6.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); cell_6.CellStyle = SummStyle; if (summflag) { //汇总 IRow summRow_2 = sheet.CreateRow(1); summRow_2.Height = 20 * 20; ICell cell_21 = summRow_2.CreateCell(0); cell_21.SetCellValue(summ1[0]); cell_21.CellStyle = SummStyle; ICell cell_22 = summRow_2.CreateCell(1); cell_22.SetCellValue(summ1[1]); cell_22.CellStyle = SummStyle; ICell cell_23 = summRow_2.CreateCell(2); cell_23.SetCellValue(summ1[2]); cell_23.CellStyle = SummStyle; ICell cell_24 = summRow_2.CreateCell(3); cell_24.SetCellValue(summ1[3]); cell_24.CellStyle = SummStyle; IRow summRow_3 = sheet.CreateRow(2); summRow_3.Height = 20 * 20; ICell cell_31 = summRow_3.CreateCell(0); cell_31.SetCellValue(summ2[0]); cell_31.CellStyle = SummStyle; ICell cell_32 = summRow_3.CreateCell(1); cell_32.SetCellValue(summ2[1]); cell_32.CellStyle = SummStyle; ICell cell_33 = summRow_3.CreateCell(2); cell_33.SetCellValue(summ2[2]); cell_33.CellStyle = SummStyle; ICell cell_34 = summRow_3.CreateCell(3); cell_34.SetCellValue(summ2[3]); cell_34.CellStyle = SummStyle; IRow summRow_4 = sheet.CreateRow(3); summRow_4.Height = 20 * 20; ICell cell_41 = summRow_4.CreateCell(0); cell_41.SetCellValue(summ3[0]); cell_41.CellStyle = SummStyle; ICell cell_42 = summRow_4.CreateCell(1); cell_42.SetCellValue(summ3[1]); cell_42.CellStyle = SummStyle; if (summ3.Length > 2) { ICell cell_43 = summRow_4.CreateCell(2); cell_43.SetCellValue(summ3[2]); cell_43.CellStyle = SummStyle; ICell cell_44 = summRow_4.CreateCell(3); cell_44.SetCellValue(summ3[3]); cell_44.CellStyle = SummStyle; } } //表格头 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //HeadercellStyle.FillBackgroundColorColor HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //用column name 作为列名 int icolIndex = 0; int ridx = (summflag ? 4 : 1); IRow headerRow = sheet.CreateRow(ridx); headerRow.Height = 20 * 20; foreach (DataGridViewColumn item in gv.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow.CreateCell(icolIndex); sheet.SetColumnWidth(icolIndex, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = (summflag ? 5 : 2); int iCellIndex = 0; //merge params string mergeColValue = string.Empty; int mergeFirstRow = iRowIndex; int mergeLastRow = iRowIndex; if (gv.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { var colValue = Rowitem.Cells[Colitem.Name].FormattedValue.ToString(); if (iCellIndex == mergeColIndex) { if (mergeColValue != colValue && iRowIndex != mergeFirstRow) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex)); mergeFirstRow = iRowIndex; } mergeColValue = colValue; cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; } else { cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.General; cellStyle.VerticalAlignment = VerticalAlignment.None; } ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(colValue); cell.CellStyle = cellStyle; iCellIndex++; } } iCellIndex = 0; iRowIndex++; } if (mergeColIndex != -1) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex)); } ////自适应列宽度 //for (int i = 0; i < icolIndex; i++) //{ // sheet.AutoSizeColumn(i); //} } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { throw ex; } finally { workbook = null; } }
public static void GridToExcelLotYield(string sheetname, string lotid, string date, string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, string[] summ4 = null, DataGridView gv1 = null) { HSSFWorkbook workbook = new HSSFWorkbook(); try { var rIndex = 0; ISheet sheet = workbook.CreateSheet(sheetname); //汇总栏 ICellStyle SummStyle = workbook.CreateCellStyle(); SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont summfont = workbook.CreateFont(); summfont.Boldweight = (short)FontBoldWeight.Bold; summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.SetFont(summfont); SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //Lot ID IRow summRow_1 = sheet.CreateRow(0); summRow_1.Height = 20 * 20; if (summ4 == null) { ICell cell_1 = summRow_1.CreateCell(0); cell_1.SetCellValue("Lot ID:"); cell_1.CellStyle = SummStyle; ICell cell_2 = summRow_1.CreateCell(1); cell_2.SetCellValue(lotid); cell_2.CellStyle = SummStyle; ICell cell_3 = summRow_1.CreateCell(2); cell_3.SetCellValue("Date:"); cell_3.CellStyle = SummStyle; ICell cell_4 = summRow_1.CreateCell(3); cell_4.SetCellValue(date); cell_4.CellStyle = SummStyle; } if (summflag) { //汇总 IRow summRow_2 = sheet.CreateRow(1); summRow_2.Height = 20 * 20; ICell cell_21 = summRow_2.CreateCell(0); cell_21.SetCellValue(summ1[0]); cell_21.CellStyle = SummStyle; ICell cell_22 = summRow_2.CreateCell(1); cell_22.SetCellValue(summ1[1]); cell_22.CellStyle = SummStyle; ICell cell_23 = summRow_2.CreateCell(2); cell_23.SetCellValue(summ1[2]); cell_23.CellStyle = SummStyle; ICell cell_24 = summRow_2.CreateCell(3); cell_24.SetCellValue(summ1[3]); cell_24.CellStyle = SummStyle; ICell cell_25 = summRow_2.CreateCell(4); cell_25.SetCellValue("Create Date:"); cell_25.CellStyle = SummStyle; ICell cell_26 = summRow_2.CreateCell(5); cell_26.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); cell_26.CellStyle = SummStyle; IRow summRow_3 = sheet.CreateRow(2); summRow_3.Height = 20 * 20; ICell cell_31 = summRow_3.CreateCell(0); cell_31.SetCellValue(summ2[0]); cell_31.CellStyle = SummStyle; ICell cell_32 = summRow_3.CreateCell(1); cell_32.SetCellValue(summ2[1]); cell_32.CellStyle = SummStyle; ICell cell_33 = summRow_3.CreateCell(2); cell_33.SetCellValue(summ2[2]); cell_33.CellStyle = SummStyle; ICell cell_34 = summRow_3.CreateCell(3); cell_34.SetCellValue(summ2[3]); cell_34.CellStyle = SummStyle; IRow summRow_4 = sheet.CreateRow(3); summRow_4.Height = 20 * 20; ICell cell_41 = summRow_4.CreateCell(0); cell_41.SetCellValue(summ3[0]); cell_41.CellStyle = SummStyle; ICell cell_42 = summRow_4.CreateCell(1); cell_42.SetCellValue(summ3[1]); cell_42.CellStyle = SummStyle; if (summ3.Length > 2) { ICell cell_43 = summRow_4.CreateCell(2); cell_43.SetCellValue(summ3[2]); cell_43.CellStyle = SummStyle; ICell cell_44 = summRow_4.CreateCell(3); cell_44.SetCellValue(summ3[3]); cell_44.CellStyle = SummStyle; } if (summ4 != null) { IRow r = sheet.CreateRow(4); r.Height = 20 * 20; IRow r4 = null; for (int i = 0; i < summ4.Length; i++) { var cIndex = 1; if (i % 2 == 0) { r4 = sheet.CreateRow(5 + rIndex); r4.Height = 20 * 20; cIndex = 0; rIndex++; } ICell c = r4.CreateCell(cIndex); c.SetCellValue(summ4[i]); c.CellStyle = SummStyle; } rIndex++; } } //表格头 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //HeadercellStyle.FillBackgroundColorColor HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; var waferId = string.Empty; int tDefects = 0, badDie = 0; var yieldLoss = 0.00; if (gv1 != null) { int icolIndex1 = 0; int ridx1 = (summflag ? 4 + rIndex : 1 + rIndex); IRow r = sheet.CreateRow(ridx1); r.Height = 20 * 20; rIndex++; ridx1++; IRow headerRow1 = sheet.CreateRow(ridx1); //rIndex++; headerRow1.Height = 20 * 20; foreach (DataGridViewColumn item in gv1.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow1.CreateCell(icolIndex1); sheet.SetColumnWidth(icolIndex1, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex1++; } ICellStyle cellStyle1 = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont1 = workbook.CreateFont(); cellfont1.Boldweight = (short)FontBoldWeight.Normal; cellStyle1.SetFont(cellfont1); //建立内容行 int iRowIndex1 = (summflag ? 5 + rIndex : 2 + rIndex); int iCellIndex1 = 0; if (gv1.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv1.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex1); foreach (DataGridViewColumn Colitem in gv1.Columns) { if (Colitem.Visible) { ICell cell = DataRow.CreateCell(iCellIndex1); cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); cell.CellStyle = cellStyle1; iCellIndex1++; if (Colitem.Name == "SumYieldLoss") { yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value); } } } iCellIndex1 = 0; iRowIndex1++; rIndex++; } IRow DataRows = sheet.CreateRow(iRowIndex1); foreach (DataGridViewColumn Colitem in gv1.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex1); if (Colitem.Name == "SumYieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } cell.CellStyle = cellStyle1; iCellIndex1++; } } iCellIndex1 = 0; iRowIndex1++; rIndex++; } rIndex++; } //用column name 作为列名 int icolIndex = 0; int ridx = (summflag ? 4 + rIndex : 1 + rIndex); IRow r2 = sheet.CreateRow(ridx); r2.Height = 20 * 20; rIndex++; ridx++; IRow headerRow = sheet.CreateRow(ridx); //rIndex++; headerRow.Height = 20 * 20; foreach (DataGridViewColumn item in gv.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow.CreateCell(icolIndex); sheet.SetColumnWidth(icolIndex, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); ICellStyle sumStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 sumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); sumStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont sumfont = workbook.CreateFont(); sumfont.Boldweight = (short)FontBoldWeight.Bold; sumStyle.SetFont(sumfont); //建立内容行 int iRowIndex = (summflag ? 5 + rIndex : 2 + rIndex); int iCellIndex = 0; int rcnt = 0; yieldLoss = 0; if (gv.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv.Rows) { if (!string.IsNullOrEmpty(waferId) && waferId != Rowitem.Cells["WaferId"].FormattedValue.ToString()) { IRow DataRows = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex); if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto") { if (Colitem.Name == "TotalDefects") { cell.SetCellValue(tDefects); } else if (Colitem.Name == "DieQuantity") { cell.SetCellValue(badDie); } else if (Colitem.Name == "YieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } else if (Colitem.Name == "WaferId") { cell.SetCellValue(waferId); } else { cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); } } cell.CellStyle = sumStyle; iCellIndex++; } } tDefects = 0; badDie = 0; yieldLoss = 0; iCellIndex = 0; iRowIndex++; rIndex++; sheet.CreateRow(iRowIndex); iRowIndex++; rIndex++; } IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); cell.CellStyle = cellStyle; iCellIndex++; if (Colitem.Name == "TotalDefects") { tDefects += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value); } else if (Colitem.Name == "DieQuantity") { badDie += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value); } else if (Colitem.Name == "YieldLoss") { yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value); } } } iCellIndex = 0; iRowIndex++; rIndex++; rcnt++; waferId = Rowitem.Cells["WaferId"].FormattedValue.ToString(); if (!string.IsNullOrEmpty(waferId) && rcnt == gv.Rows.Count) { IRow DataRows = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex); if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto") { if (Colitem.Name == "TotalDefects") { cell.SetCellValue(tDefects); } else if (Colitem.Name == "DieQuantity") { cell.SetCellValue(badDie); } else if (Colitem.Name == "YieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } else if (Colitem.Name == "WaferId") { cell.SetCellValue(waferId); } else { cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); } } cell.CellStyle = sumStyle; iCellIndex++; } } tDefects = 0; badDie = 0; yieldLoss = 0; iCellIndex = 0; iRowIndex++; rIndex++; IRow drSum = sheet.CreateRow(iRowIndex); drSum.Height = 20 * 20; iRowIndex++; rIndex++; } } ////自适应列宽度 //for (int i = 0; i < icolIndex; i++) //{ // sheet.AutoSizeColumn(i); //} } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { throw ex; } finally { workbook = null; } }
public static void DtToExcelByNPOI(DataTable dt, string strExcelFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { } }
private static void CreateSheets(HSSFWorkbook workbook, DataTable dt, string sheetName) { ISheet sheet = workbook.CreateSheet(sheetName); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null) { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } } #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); rowIndex += 1; } #endregion } #endregion #region 填充内容 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream xlsx /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">文件名</param> public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null) { XSSFWorkbook workbook = new XSSFWorkbook(); int i = 0; NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 右击文件 属性信息 { // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "文件作者信息"; //填加xlsx文件作者信息 // si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息 // si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 // si.Comments = "作者信息"; //填加xls文件作者信息 // si.Title = "标题信息"; //填加xls文件标题信息 // si.Subject = "主题信息";//填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI"); // if (!workbook.GetProperties().CustomProperties.Contains("Company")) // workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company); } #endregion foreach (DataTable dt in dataTables) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i).ToString() : dt.TableName; ISheet sheet = workbook.CreateSheet(sheetName); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } int rowIndex = 0; #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int k = 0; k < dt.Rows.Count; k++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); rowIndex += 1; } #endregion //#region 表头 //for (int j = 0; j < dt.Columns.Count; j++) //{ // string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName) // ? "Column " + j.ToString() // : dt.Columns[j].ColumnName; // headerRow.CreateCell(j).SetCellValue(columnName); //} //#endregion #region 内容 for (int a = 0; a < dt.Rows.Count; a++) { DataRow dr = dt.Rows[a]; IRow row = sheet.CreateRow(a + rowIndex); for (int b = 0; b < dt.Columns.Count; b++) { row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty); DataColumn dc = dt.Columns[b]; NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal); string drValue = dr[b].ToString(); switch (dc.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } #endregion } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); // ms.Position = 0; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
public string ExportToExcel(string Name, DataTable dt) { try { if (dt.Rows.Count > 0) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = HorizontalAlignment.Center; HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index; HeadercellStyle.FillPattern = FillPattern.SolidForeground; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; headerfont.FontHeightInPoints = 12; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn dc in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(dc.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 0; foreach (DataRow dr in dt.Rows) { int iCellIndex = 0; IRow irow = sheet.CreateRow(iRowIndex + 1); for (int i = 0; i < dt.Columns.Count; i++) { string strsj = string.Empty; if (dr[i] != null) { strsj = dr[i].ToString(); } ICell cell = irow.CreateCell(iCellIndex); cell.SetCellValue(strsj); cell.CellStyle = cellStyle; iCellIndex++; } iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); workbook = null; ms.Close(); ms.Dispose(); curContext.Response.End(); } } return(""); } catch (Exception ex) { return("导出失败!"); } }
private void ExportDataItem(DataTable dt) { DataRow dr = dt.Rows[0]; string filename = "培训讲师档案--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训讲师档案.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); for (int i = 0, length = dt.Rows.Count; i < length; i++) { IRow row = sheet0.CreateRow(i + 1); row.HeightInPoints = 20; row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号 row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString()); row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BM"].ToString()); row.CreateCell(3).SetCellValue(dt.Rows[i]["PX_BH"].ToString()); row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部"); row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString()); row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString()); row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString()); row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString()); row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString()); row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_ZJRDF"].ToString()); row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString()); NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 11; j++) { row.Cells[j].CellStyle = cells; } } NPOI.SS.UserModel.IFont font2 = wk.CreateFont(); font2.FontName = "仿宋"; //字体 font2.FontHeightInPoints = 9; //字号 ICellStyle cells2 = wk.CreateCellStyle(); cells2.SetFont(font2); cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cells2.VerticalAlignment = VerticalAlignment.CENTER; //CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16); //sheet0.AddMergedRegion(range1); //CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17); //sheet0.AddMergedRegion(range2); MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
// /** // * Drawing context to measure text // */ //private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true); /** * Compute width of a column and return the result * * @param sheet the sheet to calculate * @param column 0-based index of the column * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells) { //AttributedString str; //TextLayout layout; IWorkbook wb = sheet.Workbook; DataFormatter formatter = new DataFormatter(); IFont defaultFont = wb.GetFontAt((short)0); //str = new AttributedString((defaultChar)); //copyAttributes(defaultFont, str, 0, 1); //layout = new TextLayout(str.Iterator, fontRenderContext); //int defaultCharWidth = (int)layout.Advance; Font font = IFont2Font(defaultFont); int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; DummyEvaluator dummyEvaluator = new DummyEvaluator(); double width = -1; using (Bitmap bmp = new Bitmap(2048, 100)) { Graphics g = Graphics.FromImage(bmp); //rows: bool skipthisrow = false; for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();) { IRow row = (IRow)it.Current; ICell cell = row.GetCell(column); if (cell == null) { continue; } int colspan = 1; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and Move on to the next. //continue rows; skipthisrow = true; } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } if (skipthisrow) { continue; } ICellStyle style = cell.CellStyle; NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex); CellType cellType = cell.CellType; // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.FORMULA) { cellType = cell.CachedFormulaResultType; } if (cellType == CellType.STRING) { IRichTextString rt = cell.RichStringCellValue; String[] lines = rt.String.Split("\n".ToCharArray()); for (int i = 0; i < lines.Length; i++) { String txt = lines[i] + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.Length); font = IFont2Font(font1); if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } //layout = new TextLayout(str.Iterator, fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is Added by the standard Excel autosize. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); SizeF sf = g.MeasureString(txt, font); double x1 = Math.Abs(sf.Height * Math.Sin(angle)); double x2 = Math.Abs(sf.Width * Math.Cos(angle)); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); //width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); } else { //width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } else { String sval = null; if (cellType == CellType.NUMERIC) { // Try to Get it formatted to look the same as excel try { sval = formatter.FormatCellValue(cell, dummyEvaluator); } catch (Exception) { sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture); } } else if (cellType == CellType.BOOLEAN) { sval = cell.BooleanCellValue.ToString().ToUpper(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.Length); //layout = new TextLayout(str.Iterator, fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is Added by the standard Excel autosize. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); //width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); SizeF sf = g.MeasureString(txt, font); double x1 = sf.Height * Math.Sin(angle); double x2 = sf.Width * Math.Cos(angle); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } else { //width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } } } return(width); }
/// <summary> /// 将DataTable导出 /// </summary> /// <param name="dt">DataTable</param> /// <param name="saveFilePath">保存位置</param> private void ExportByNPOI(DataTable dt, string saveFilePath) { //NPOI到处excel IWorkbook workbook; string fileExt = Path.GetExtension(saveFilePath); if (fileExt.Equals(".xls")) { workbook = new HSSFWorkbook(); } else if (fileExt.Equals(".xlsx")) { workbook = new XSSFWorkbook(); } else { return; } ISheet sheet = workbook.CreateSheet("Sheet1"); try { //设置格式 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用Caption 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); //配置列名 foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.Caption); cell.CellStyle = HeadercellStyle; icolIndex++; } //匹配格式 ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //字体 IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell newCell = DataRow.CreateCell(iCellIndex); string drValue = Rowitem[Colitem].ToString(); switch (Colitem.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); if (dateV.Year == 1900) { newCell.SetCellValue(""); break; } newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } //cell.SetCellValue(rowObject[columnIds[columnIndex]].ToString()); //cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (sheet.GetRow(rowNum) == null) { currentRow = sheet.CreateRow(rowNum); } else { currentRow = sheet.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } sheet.SetColumnWidth(columnNum, columnWidth * 256); } //写Excel if (File.Exists(saveFilePath)) { if ( DigiwinMessageBox.Show(Properties.Resources.String12, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.Cancel) { return; } } using (FileStream file = new FileStream(saveFilePath, FileMode.Create, FileAccess.Write)) { workbook.Write(file); } //导出后打开文件 //DialogResult dialogResult = DigiwinMessageBox.Show(Properties.Resources.String3, // MessageBoxButtons.OKCancel, MessageBoxIcon.Information); //if (dialogResult == DialogResult.OK) //{ // //打开文件 // System.Diagnostics.Process process = new System.Diagnostics.Process(); // process.StartInfo.FileName = saveFilePath; // try // { // process.Start(); // } // catch (System.ComponentModel.Win32Exception we) // { // MessageBox.Show(we.Message); // return; // } //} } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { workbook = null; } }
/// <summary> /// Excel 导出 /// </summary> /// <param name="SourceTable">来源DataTable数据集</param> /// <param name="strFileName">输出文件名.xls</param> /// <param name="strTemplateUrl">模板路径../sheet.xls</param> /// <param name="strSheetName">填充的工作表名</param> /// <param name="intTitleRowCount">标题行数</param> /// <param name="HasColumns">是否自动生成列名,针对动态生成列名数据导出</param> /// <param name="arrTitle">标题数组string[3]{"存放位置","内容","是否正标题(true/false)"}</param> /// <param name="arrRow">列数组string[2]{"列号","列名"}</param> /// <param name="arrEnd">列数组string[2]{"排放顺序","内容"}</param> /// <returns></returns> public void RenderDataTableToExcel(System.Data.DataTable SourceTable, string strFileName, string strTemplateUrl, string strSheetName, int intTitleRowCount, bool HasColumns, ArrayList arrTitle, ArrayList arrRow, ArrayList arrEnd) { if (!File.Exists(HttpContext.Current.Server.MapPath(strTemplateUrl))) { FileStream stream = File.Create(HttpContext.Current.Server.MapPath(strTemplateUrl)); stream.Close();//立即关闭创建文件进程,以便下面读取文件 } FileStream file = new FileStream(HttpContext.Current.Server.MapPath(strTemplateUrl), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheet(strSheetName); if (sheet == null) { sheet = hssfworkbook.GetSheetAt(0);//取第一个工作表 } try { //列标题数据填充 for (int i = 0; i < arrTitle.Count; i++) { string[] arr = arrTitle[i] as string[]; //获取行 IRow row = sheet.CreateRow(Int32.Parse(arr[0].ToString()) - 1); ICell cell = row.CreateCell(0); cell.SetCellValue(arr[1].ToString()); //合并列 sheet.AddMergedRegion(new CellRangeAddress(i, i, 0, SourceTable.Columns.Count - 1)); //格式设置 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.LEFT; //字体设置 NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont(); //font.Boldweight = (short)FontBoldWeight.BOLD; if (arr[2].ToString() == "true")//正标题 字体放大 { font.FontHeight = 400; cellStyle.Alignment = HorizontalAlignment.CENTER; } cellStyle.SetFont(font); cell.CellStyle = cellStyle; } //构建表头 if (HasColumns) { IRow row = sheet.CreateRow(intTitleRowCount - 1); //表头所在行号 即intTitleRowCount标题行数 for (int i = 0; i < arrRow.Count; i++) { string[] arr = arrRow[i] as string[]; //获取行 ICell cell = row.CreateCell(Int32.Parse(arr[0].ToString()) - 1); cell.SetCellValue(arr[1].ToString()); //格式设置 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.CENTER; //字体设置 NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont(); //font.Boldweight = (short)FontBoldWeight.BOLD; cellStyle.SetFont(font); //边框设置 cellStyle.BorderBottom = BorderStyle.THIN; cellStyle.BorderTop = BorderStyle.THIN; cellStyle.BorderLeft = BorderStyle.THIN; cellStyle.BorderRight = BorderStyle.THIN; cell.CellStyle = cellStyle; //宽度设置 sheet.SetColumnWidth(Int32.Parse(arr[0].ToString()) - 1, (arr[1].ToString().Length) * 420); } } //表尾填充 for (int i = 0; i < arrEnd.Count; i++) { string[] arr = arrEnd[i] as string[]; int intEnd = Int32.Parse(arr[0].ToString()) + SourceTable.Rows.Count + intTitleRowCount - 1;//插入表尾位置 //获取行 IRow row = sheet.CreateRow(intEnd); ICell cell = row.CreateCell(0); cell.SetCellValue(arr[1].ToString()); //构建一个合并区域 sheet.AddMergedRegion(new CellRangeAddress(intEnd, intEnd, 0, arrRow.Count - 1)); //格式设置 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.RIGHT; //字体设置 NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont(); //font.Boldweight = (short)FontBoldWeight.BOLD; cellStyle.SetFont(font); cell.CellStyle = cellStyle; } //列主体数据填充 for (int i = 0; i < SourceTable.Rows.Count; i++) { int intTopWidth = 0; int intMainWidth = 0; //创建新行 IRow row = sheet.CreateRow(i + intTitleRowCount); for (int j = 0; j < arrRow.Count; j++) { string[] arr = arrRow[j] as string[]; //创建新列 ICell cell = row.CreateCell(Int32.Parse(arr[0].ToString()) - 1); cell.SetCellValue(SourceTable.Rows[i][arr[1].ToString()].ToString()); //列宽度设置 intTopWidth = sheet.GetColumnWidth(Int32.Parse(arr[0].ToString()) - 1); //表头宽度 intMainWidth = intMainWidth > (SourceTable.Rows[i][arr[1].ToString()].ToString().Length) * 420 ? intMainWidth : (SourceTable.Rows[i][arr[1].ToString()].ToString().Length) * 420; //表体宽度 if (intTopWidth < intMainWidth) //如果数据宽度比表头宽度大,则以数据宽度重新设置 { sheet.SetColumnWidth(Int32.Parse(arr[0].ToString()) - 1, intMainWidth); } //边框设置 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.LEFT; cellStyle.BorderBottom = BorderStyle.THIN; cellStyle.BorderTop = BorderStyle.THIN; cellStyle.BorderLeft = BorderStyle.THIN; cellStyle.BorderRight = BorderStyle.THIN; cell.CellStyle = cellStyle; } //固定列 //sheet.CreateFreezePane(4, 0, 4, 0); } using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(stream.GetBuffer()); curContext.Response.End(); } } catch (Exception e) { //AppendToFile(e.ToString()); } }
public static void ToExcelByNPOI(System.Collections.IEnumerable list, string strExcelFileName, List <KeyValuePair <string, string> > fields = null) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index; HeadercellStyle.VerticalAlignment = VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 30; Type type = null; foreach (var item in list) { type = item.GetType(); break; } PropertyInfo[] Properties = type.GetProperties(); if (fields == null) { foreach (PropertyInfo objProperty in Properties) //遍历T的属性 { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(objProperty.Name); cell.CellStyle = HeadercellStyle; icolIndex++; } } else { foreach (var field in fields) //遍历T的属性 { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(field.Value); cell.CellStyle = HeadercellStyle; icolIndex++; } } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.Alignment = HorizontalAlignment.Center; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (var item in list) { IRow DataRow = sheet.CreateRow(iRowIndex); DataRow.HeightInPoints = 25; //PropertyInfo[] PI = typeof(T).GetProperties(); if (fields == null) { //循环获取该对象的所有属性和值 foreach (PropertyInfo info in Properties) { ICell cell = DataRow.CreateCell(iCellIndex); var value = info.GetValue(item); if (value != null) { cell.SetCellValue(value.ToString()); } cell.CellStyle = cellStyle; iCellIndex++; } } else { foreach (var field in fields) { ICell cell = DataRow.CreateCell(iCellIndex); var p = Properties.FirstOrDefault(a => a.Name == field.Key); if (p != null) { var value = p.GetValue(item); if (value != null) { cell.SetCellValue(value.ToString()); } } cell.CellStyle = cellStyle; iCellIndex++; } } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //string excelPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\" + strExcelFileName + ".xls"; //写Excel FileStream file = new System.IO.FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch { throw; } }
//Datatable导出Excel /// <summary> /// 从DataTable导出Excel /// </summary> /// <param name="dt">DataTable</param> /// <param name="strExcelFileName">excel文件路径</param> /// <param name="fields">对应要导出的字段</param> public static void ToExcelByNPOI(DataTable dt, string strExcelFileName, List <KeyValuePair <string, string> > fields = null) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 30; if (fields == null) { foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } } else { foreach (var item in fields) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.Value); cell.CellStyle = HeadercellStyle; icolIndex++; } } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.Alignment = HorizontalAlignment.Center; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); DataRow.HeightInPoints = 25; if (fields != null) { foreach (var Colitem in fields) { ICell cell = DataRow.CreateCell(iCellIndex); var value = Rowitem[Colitem.Key]; if (value != null) { cell.SetCellValue(value.ToString()); } cell.CellStyle = cellStyle; iCellIndex++; } } else { foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); var value = Rowitem[Colitem]; if (value != null) { cell.SetCellValue(value.ToString()); } cell.CellStyle = cellStyle; iCellIndex++; } } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //string excelPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\" + strExcelFileName + ".xls"; //写Excel FileStream file = new System.IO.FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch { throw; } }
private void ExportDataItem(DataTable dt) { DataRow dr = dt.Rows[0]; string sql = ""; string[] ddbh = dr["HT_DDBH"].ToString().Split('|'); sql = "select orderno,zdrnm,zdtime,PO_ZJE,marid,marnm,suppliernm,"; sql += "PO_TUHAO= stuff((select '|'+PO_TUHAO from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as t where t.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and t.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,''),"; sql += "margg,marcz,margb,PO_MASHAPE,sum(convert(float,zxnum)) as zxnum,marunit,sum(convert(float,fznum))as fznum,PO_TECUNIT,"; sql += "stuff((select '|'+convert(varchar(50),length) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as a where a.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and a.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as length,"; sql += "stuff((select '|'+convert(varchar(50),width) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as b where b.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and b.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as width,"; sql += "stuff((select '|'+convert(varchar(50),PO_PZ) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as c where c.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and c.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as PO_PZ,"; sql += "ctprice,sum(convert(float,ctamount))as ctamount,cgtimerq,"; sql += "detailnote =stuff((select '|'+detailnote from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as d where d.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and d.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,''),"; sql += "ptcode=stuff((select ' | '+ptcode from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as e where e.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and e.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'')"; sql += "from View_TBPC_PURORDERDETAIL_PLAN_TOTAL where orderno in ("; //sql = "select * from View_TBPC_PURORDERDETAIL_PLAN_TOTAL where orderno in ("; for (int i = 0, length = ddbh.Length; i < length; i++) { sql += "'" + ddbh[i] + "',"; } sql = sql.Trim(','); //sql += ")"; sql += ") group by orderno,zdrnm,zdtime,suppliernm,PO_ZJE,marid,marnm,margg,marcz,margb,PO_MASHAPE,marunit,PO_TECUNIT,ctprice,cgtimerq order by ptcode"; DataTable dt1 = DBCallCommon.GetDTUsingSqlText(sql); //string filename = "采购合同" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; string filename = "采购合同--" + dr["HT_GF"].ToString() + dr["HT_QDSJ"].ToString() + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购合同.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); #region //*********************导出到采购合同条款***************************// ISheet sheet0 = wk.GetSheetAt(0); IRow row1 = sheet0.GetRow(1); //买方合同号编号 row1.GetCell(3).SetCellValue(dr["HT_XFHTBH"].ToString()); IRow row2 = sheet0.GetRow(2);//卖方合同号编号 row2.GetCell(3).SetCellValue(dr["HT_GFHTBH"].ToString()); IRow row3 = sheet0.GetRow(3);//签订时间 row3.GetCell(3).SetCellValue(dr["HT_QDSJ"].ToString().Substring(0, 4) + "年" + dr["HT_QDSJ"].ToString().Substring(5, 2) + "月" + dr["HT_QDSJ"].ToString().Substring(8, 2) + "日"); IRow row4 = sheet0.GetRow(4); row4.GetCell(1).SetCellValue(dr["HT_GF"].ToString());//卖方(供方) IRow row7 = sheet0.GetRow(7); row7.GetCell(1).SetCellValue(dr["HT_HTZJ"].ToString());//合同总价 IRow row22 = sheet0.GetRow(22); IRow row23 = sheet0.GetRow(23); IRow row24 = sheet0.GetRow(24); IRow row25 = sheet0.GetRow(25); IRow row26 = sheet0.GetRow(26); IRow row27 = sheet0.GetRow(27); if (dr["HT_JSFS"].ToString() == "1")//付款方式 { row22.GetCell(0).SetCellValue("货到付款型"); row23.GetCell(0).SetCellValue("备注:" + dr["HT_JSFSBZ"].ToString()); } if (dr["HT_JSFS"].ToString() == "2") { row22.GetCell(0).SetCellValue("款到发货型"); row23.GetCell(0).SetCellValue("备注:" + dr["HT_JSFSBZ"].ToString()); } if (dr["HT_JSFS1BZ"].ToString() != "") { row24.GetCell(0).SetCellValue(dr["HT_JSFS1BZ"].ToString()); } else { row24.HeightInPoints = 0; } if (dr["HT_JSFS2BZ"].ToString() != "") { row25.GetCell(0).SetCellValue(dr["HT_JSFS2BZ"].ToString()); } else { row25.HeightInPoints = 0; } if (dr["HT_JSFS3"].ToString() != "") { row26.GetCell(0).SetCellValue(dr["HT_JSFS3"].ToString()); } else { row26.HeightInPoints = 0; } row27.HeightInPoints = 0; IRow row52 = sheet0.GetRow(52); row52.GetCell(3).SetCellValue(dr["HT_GF"].ToString());//单位名称 IRow row53 = sheet0.GetRow(53); row53.GetCell(3).SetCellValue(dr["HT_DZ"].ToString());//单位地址 IRow row54 = sheet0.GetRow(54); row54.GetCell(3).SetCellValue(dr["HT_FDDBR"].ToString());//法定代表人 IRow row55 = sheet0.GetRow(55); row55.GetCell(3).SetCellValue(dr["HT_WTDLR"].ToString());//委托代理人 IRow row56 = sheet0.GetRow(56); row56.GetCell(3).SetCellValue(dr["HT_DH"].ToString());//电话 IRow row57 = sheet0.GetRow(57); row57.GetCell(3).SetCellValue(dr["HT_CZ"].ToString());//传真 IRow row58 = sheet0.GetRow(58); row58.GetCell(3).SetCellValue(dr["HT_KHYH"].ToString());//开户银行 IRow row59 = sheet0.GetRow(59); row59.GetCell(3).SetCellValue(dr["HT_ZH"].ToString());//账号 IRow row60 = sheet0.GetRow(60); row60.GetCell(3).SetCellValue(dr["HT_SH"].ToString());//税号 IRow row61 = sheet0.GetRow(61); row61.GetCell(3).SetCellValue(dr["HT_YB"].ToString());//邮编 sheet0.ForceFormulaRecalculation = true; #endregion #region //******************导出到采购合同附件***********************// //int[] a = new int[dt1.Rows.Count]; //for (int i = 1,length=dt1.Rows.Count; i < length; i++) //{ // if (dt1.Rows[i]["orderno"].ToString()!=dt1.Rows[i-1]["orderno"].ToString()) // { // a[i] = i; // } //} int leixing = 0; for (int i = 0, length = dt1.Rows.Count; i < length; i++) { if (dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("板") || dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("圆") || dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("型")) { leixing = 1; } } if (leixing == 0)//非钢材类 { ISheet sheet1 = wk.GetSheetAt(1); IRow rowddbh = sheet1.GetRow(1); rowddbh.GetCell(0).SetCellValue("订单编号:" + dr["HT_DDBH"].ToString()); for (int i = 0, length = dt1.Rows.Count; i < length; i++) { IRow row = sheet1.CreateRow(i + 3); row.HeightInPoints = 14; row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号 //row.CreateCell(1).SetCellValue(dt1.Rows[i]["orderno"].ToString());//计划单号 row.CreateCell(1).SetCellValue(dt1.Rows[i]["marid"].ToString()); //物料编码 row.CreateCell(2).SetCellValue(dt1.Rows[i]["marnm"].ToString()); //物料名称 row.CreateCell(3).SetCellValue(dt1.Rows[i]["PO_TUHAO"].ToString()); //图号 row.CreateCell(4).SetCellValue(dt1.Rows[i]["margg"].ToString()); //规格 row.CreateCell(5).SetCellValue(dt1.Rows[i]["marcz"].ToString()); //材质 row.CreateCell(6).SetCellValue(dt1.Rows[i]["margb"].ToString()); //国标 row.CreateCell(7).SetCellValue(dt1.Rows[i]["PO_MASHAPE"].ToString()); //类型 row.CreateCell(8).SetCellValue(dt1.Rows[i]["zxnum"].ToString()); //数(重)量 row.CreateCell(9).SetCellValue(dt1.Rows[i]["marunit"].ToString()); //单位 row.CreateCell(10).SetCellValue(dt1.Rows[i]["fznum"].ToString()); //辅助数量 row.CreateCell(11).SetCellValue(dt1.Rows[i]["PO_TECUNIT"].ToString()); //辅助单位 //row.CreateCell(13).SetCellValue(dt1.Rows[i]["length"].ToString());//长度 //row.CreateCell(14).SetCellValue(dt1.Rows[i]["width"].ToString());//宽度 //row.CreateCell(15).SetCellValue(dt1.Rows[i]["PO_PZ"].ToString());//片支 row.CreateCell(12).SetCellValue(dt1.Rows[i]["ctprice"].ToString()); //含税单价 row.CreateCell(13).SetCellValue(dt1.Rows[i]["ctamount"].ToString()); //含税金额 row.CreateCell(14).SetCellValue(dt1.Rows[i]["cgtimerq"].ToString()); //交货日期 row.CreateCell(15).SetCellValue(dt1.Rows[i]["detailnote"].ToString()); //备注 row.CreateCell(16).SetCellValue(dt1.Rows[i]["ptcode"].ToString()); //计划跟踪号 NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 16; j++) { row.Cells[j].CellStyle = cells; } } double number = 0; double money = 0; for (int i = 0, length = dt1.Rows.Count; i < length; i++) { number += Convert.ToDouble(dt1.Rows[i]["zxnum"].ToString()); money += Convert.ToDouble(dt1.Rows[i]["ctamount"].ToString()); } IRow rowhz = sheet1.CreateRow(dt1.Rows.Count + 3); for (int i = 0; i <= 16; i++) { rowhz.CreateCell(i); } rowhz.GetCell(0).SetCellValue("合计"); rowhz.GetCell(8).SetCellValue(number); rowhz.GetCell(13).SetCellValue(money); NPOI.SS.UserModel.IFont font2 = wk.CreateFont(); font2.FontName = "仿宋"; //字体 font2.FontHeightInPoints = 9; //字号 ICellStyle cells2 = wk.CreateCellStyle(); cells2.SetFont(font2); cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cells2.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i <= 16; i++) { rowhz.Cells[i].CellStyle = cells2; } IRow rowbzts = sheet1.CreateRow(dt1.Rows.Count + 4); rowbzts.CreateCell(0).SetCellValue("备注:"); rowbzts.Cells[0].CellStyle = cells2; IRow rowbz = sheet1.CreateRow(dt1.Rows.Count + 5); for (int i = 0; i <= 16; i++) { rowbz.CreateCell(i); } rowbz.GetCell(0).SetCellValue(dt.Rows[0]["HT_DDBZ"].ToString()); for (int i = 0; i <= 16; i++) { rowbz.Cells[i].CellStyle = cells2; } //ICellStyle cellstyle = wk.CreateCellStyle(); //cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //cellstyle.VerticalAlignment = VerticalAlignment.CENTER; //for (int i = 0; i <= 16; i++) //{ // rowhz.Cells[i].CellStyle = cellstyle; //} CellRangeAddress range1 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 0, 7); sheet1.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 9, 12); sheet1.AddMergedRegion(range2); CellRangeAddress range3 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 14, 16); sheet1.AddMergedRegion(range3); CellRangeAddress range4 = new CellRangeAddress(dt1.Rows.Count + 5, dt1.Rows.Count + 9, 0, 16); sheet1.AddMergedRegion(range4); for (int i = 0; i <= 16; i++) { sheet1.AutoSizeColumn(i); } sheet1.ForceFormulaRecalculation = true; } if (leixing == 1) { ISheet sheet2 = wk.GetSheetAt(2); IRow rowddbh1 = sheet2.GetRow(1); rowddbh1.GetCell(0).SetCellValue("订单编号:" + dr["HT_DDBH"].ToString()); for (int i = 0, length = dt1.Rows.Count; i < length; i++) { IRow row = sheet2.CreateRow(i + 3); row.HeightInPoints = 14; row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号 //row.CreateCell(1).SetCellValue(dt1.Rows[i]["orderno"].ToString());//计划单号 row.CreateCell(1).SetCellValue(dt1.Rows[i]["marid"].ToString()); //物料编码 row.CreateCell(2).SetCellValue(dt1.Rows[i]["marnm"].ToString()); //物料名称 row.CreateCell(3).SetCellValue(dt1.Rows[i]["PO_TUHAO"].ToString()); //图号 row.CreateCell(4).SetCellValue(dt1.Rows[i]["margg"].ToString()); //规格 row.CreateCell(5).SetCellValue(dt1.Rows[i]["marcz"].ToString()); //材质 row.CreateCell(6).SetCellValue(dt1.Rows[i]["margb"].ToString()); //国标 row.CreateCell(7).SetCellValue(dt1.Rows[i]["PO_MASHAPE"].ToString()); //类型 row.CreateCell(8).SetCellValue(dt1.Rows[i]["zxnum"].ToString()); //数(重)量 row.CreateCell(9).SetCellValue(dt1.Rows[i]["marunit"].ToString()); //单位 //row.CreateCell(11).SetCellValue(dt1.Rows[i]["fznum"].ToString());//辅助数量 //row.CreateCell(12).SetCellValue(dt1.Rows[i]["marfzunit"].ToString());//辅助单位 row.CreateCell(10).SetCellValue(dt1.Rows[i]["length"].ToString()); //长度 row.CreateCell(11).SetCellValue(dt1.Rows[i]["width"].ToString()); //宽度 row.CreateCell(12).SetCellValue(dt1.Rows[i]["PO_PZ"].ToString()); //片支 row.CreateCell(13).SetCellValue(dt1.Rows[i]["ctprice"].ToString()); //含税单价 row.CreateCell(14).SetCellValue(dt1.Rows[i]["ctamount"].ToString()); //含税金额 row.CreateCell(15).SetCellValue(dt1.Rows[i]["cgtimerq"].ToString()); //交货日期 row.CreateCell(16).SetCellValue(dt1.Rows[i]["detailnote"].ToString()); //备注 row.CreateCell(17).SetCellValue(dt1.Rows[i]["ptcode"].ToString()); //计划跟踪号 NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 17; j++) { row.Cells[j].CellStyle = cells; } } double number = 0; double money = 0; for (int i = 0, length = dt1.Rows.Count; i < length; i++) { number += Convert.ToDouble(dt1.Rows[i]["zxnum"].ToString()); money += Convert.ToDouble(dt1.Rows[i]["ctamount"].ToString()); } IRow rowhz = sheet2.CreateRow(dt1.Rows.Count + 3); NPOI.SS.UserModel.IFont font2 = wk.CreateFont(); font2.FontName = "仿宋"; //字体 font2.FontHeightInPoints = 9; //字号 ICellStyle cells2 = wk.CreateCellStyle(); cells2.SetFont(font2); cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cells2.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i < 18; i++) { rowhz.CreateCell(i); } rowhz.GetCell(0).SetCellValue("合计"); rowhz.GetCell(8).SetCellValue(number); rowhz.GetCell(14).SetCellValue(money); for (int i = 0; i < 18; i++) { rowhz.Cells[i].CellStyle = cells2; } IRow rowbzts = sheet2.CreateRow(dt1.Rows.Count + 4); rowbzts.CreateCell(0).SetCellValue("备注:"); rowbzts.Cells[0].CellStyle = cells2; IRow rowbz = sheet2.CreateRow(dt1.Rows.Count + 5); for (int i = 0; i < 18; i++) { rowbz.CreateCell(i); } rowbz.GetCell(0).SetCellValue(dt.Rows[0]["HT_DDBZ"].ToString()); for (int i = 0; i < 18; i++) { rowbz.Cells[i].CellStyle = cells2; } //ICellStyle cellstyle = wk.CreateCellStyle(); //cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //cellstyle.VerticalAlignment = VerticalAlignment.CENTER; //for (int i = 0; i < 18; i++) //{ // rowhz.Cells[i].CellStyle = cellstyle; //} CellRangeAddress range1 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 0, 7); sheet2.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 9, 13); sheet2.AddMergedRegion(range2); CellRangeAddress range3 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 15, 17); sheet2.AddMergedRegion(range3); CellRangeAddress range4 = new CellRangeAddress(dt1.Rows.Count + 5, dt1.Rows.Count + 9, 0, 17); sheet2.AddMergedRegion(range4); for (int i = 0; i < 18; i++) { sheet2.AutoSizeColumn(i); } sheet2.ForceFormulaRecalculation = true; } #endregion //sheet0.ProtectSheet("123456"); //sheet1.ProtectSheet("123456"); MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
private void ExportDataItem(DataTable dt) { DataRow dr = dt.Rows[0]; string filename = "招聘计划--" + dr["JH_HZBH"].ToString() + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("招聘计划导出.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); for (int i = 0, length = dt.Rows.Count; i < length; i++) { IRow row = sheet0.CreateRow(i + 4); row.HeightInPoints = 20; row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号 row.CreateCell(1).SetCellValue(dt.Rows[i]["JH_HZBH"].ToString()); row.CreateCell(2).SetCellValue(dt.Rows[i]["JH_ZPBM"].ToString()); row.CreateCell(3).SetCellValue(dt.Rows[i]["JH_GWMC"].ToString()); row.CreateCell(4).SetCellValue(dt.Rows[i]["JH_XQLY"].ToString()); row.CreateCell(5).SetCellValue(dt.Rows[i]["JH_ZPFS"].ToString()); row.CreateCell(6).SetCellValue(dt.Rows[i]["JH_ZPRS"].ToString()); row.CreateCell(7).SetCellValue(dt.Rows[i]["JH_ZPGW"].ToString()); row.CreateCell(8).SetCellValue(dt.Rows[i]["JH_ZPZY"].ToString()); row.CreateCell(9).SetCellValue(dt.Rows[i]["JH_ZPYX"].ToString()); row.CreateCell(10).SetCellValue(dt.Rows[i]["JH_ZPXL"].ToString()); row.CreateCell(11).SetCellValue(dt.Rows[i]["JH_ZPXB"].ToString()); row.CreateCell(12).SetCellValue(dt.Rows[i]["JH_ZPNL"].ToString()); row.CreateCell(13).SetCellValue(dt.Rows[i]["JH_ZPYQ"].ToString()); row.CreateCell(14).SetCellValue(dt.Rows[i]["JH_QTYQ"].ToString()); row.CreateCell(15).SetCellValue(dt.Rows[i]["JH_XWDGSJ"].ToString()); row.CreateCell(16).SetCellValue(dt.Rows[i]["JH_NGZDD"].ToString()); row.CreateCell(17).SetCellValue(dt.Rows[i]["JH_QT"].ToString()); NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 17; j++) { row.Cells[j].CellStyle = cells; } } int num = 0; for (int i = 0, length = dt.Rows.Count; i < length; i++) { num += Convert.ToInt32(dt.Rows[i]["JH_ZPRS"].ToString()); } IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 4); for (int i = 0; i <= 17; i++) { rowhz.CreateCell(i); } rowhz.GetCell(0).SetCellValue("人数合计"); rowhz.GetCell(6).SetCellValue(num); rowhz.HeightInPoints = 20; NPOI.SS.UserModel.IFont font2 = wk.CreateFont(); font2.FontName = "仿宋"; //字体 font2.FontHeightInPoints = 9; //字号 ICellStyle cells2 = wk.CreateCellStyle(); cells2.SetFont(font2); cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cells2.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i <= 17; i++) { rowhz.Cells[i].CellStyle = cells2; } CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 0, 5); sheet0.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17); sheet0.AddMergedRegion(range2); IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6); for (int i = 0; i <= 17; i++) { rowqz1.CreateCell(i); } rowqz1.GetCell(0).SetCellValue("编制负责人:"); rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString()); rowqz1.GetCell(5).SetCellValue("申报单位/部门主管:"); rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString()); rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : ""); rowqz1.GetCell(12).SetCellValue("集团公司主管领导:"); rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString()); rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : ""); for (int i = 0; i <= 17; i++) { rowqz1.Cells[i].CellStyle = cells2; } CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1); sheet0.AddMergedRegion(range3); //CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4); //sheet0.AddMergedRegion(range4); CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6); sheet0.AddMergedRegion(range5); //CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11); //sheet0.AddMergedRegion(range6); CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13); sheet0.AddMergedRegion(range7); IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7); for (int i = 0; i <= 17; i++) { rowqz2.CreateCell(i); } rowqz2.GetCell(1).SetCellValue("日期:"); rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString()); rowqz2.GetCell(6).SetCellValue("日期:"); rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString()); rowqz2.GetCell(13).SetCellValue("日期:"); rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString()); for (int i = 0; i <= 17; i++) { rowqz2.Cells[i].CellStyle = cells2; } MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
/// <summary> /// Datatable导出Excel /// </summary> /// <param name="dt">datatable</param> /// <param name="strExcelFileName">Excel文件目录地址</param> /// <param name="title">Excel文件标题</param> public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //标题头 int icolIndex = 0; CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0); sheet.AddMergedRegion(region); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 20; ICell celltitle = headerRow.CreateCell(0); celltitle.SetCellValue(title); celltitle.CellStyle = HeadercellStyle; //用column name 作为列名 IRow headerRow1 = sheet.CreateRow(1); foreach (DataColumn item in dt.Columns) { ICell cell1 = headerRow1.CreateCell(icolIndex); cell1.SetCellValue(item.ColumnName); cell1.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 2; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { throw ex; } finally { workbook = null; } }
private void CreateSheet(HSSFWorkbook workbook, DataTable dtExcel, IDictionary htFields, int sheetIndex, int nextRowIndex) { string sheetName = string.Format("sheet{0}", sheetIndex); // 构建工作台中的一张表,对应到excel文档中的sheet ISheet sheet = workbook.CreateSheet(sheetName); #region 构建Sheet的头部 // 新建一行,对应到excel文件中的一行 IRow row = sheet.CreateRow(0); // 把值写入单元格中,此处生成表头 // 这个为列索引,从0开始 int cellIndex = 0; if (_isWholeTable) { for (int i = 0; i < dtExcel.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dtExcel.Columns[i].ColumnName); } } else { foreach (DictionaryEntry dictionaryEntry in htFields) { int columnIndex = dtExcel.Columns.IndexOf(dictionaryEntry.Key.ToString()); if (columnIndex > -1) { string fieldText = htFields[dictionaryEntry.Key].ToString(); ICell cell = row.CreateCell(cellIndex++); cell.SetCellValue(fieldText); // 头部样式设置 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderBottom = BorderStyle.Thick; cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BrightGreen.Index; NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; cellStyle.SetFont(headerfont); cell.CellStyle = cellStyle; } } } #endregion #region 单元格中填写数据 int totalCount = dtExcel.Rows.Count; bool isEndSheet = sheetIndex * DefaultMaxRow >= totalCount; int broundRowsCount = sheetIndex * DefaultMaxRow >= totalCount ? totalCount : sheetIndex * DefaultMaxRow; for (int i = nextRowIndex; i < broundRowsCount; i++) { // 列索引置0 cellIndex = 0; // 生成一行,对应到excel文件中的一条数据行 IRow row1 = sheet.CreateRow(i - nextRowIndex + 1); // 把值写入单元格中,此处生成表身 foreach (DictionaryEntry dictionaryEntry in htFields) { row1.CreateCell(cellIndex++).SetCellValue(dtExcel.Rows[i][dictionaryEntry.Key.ToString()].ToString()); } } #endregion #region 设置Sheet中列自适应 // 列宽度设置为自适应 for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); } #endregion if (!isEndSheet) { CreateSheet(workbook, dtExcel, htFields, sheetIndex + 1, nextRowIndex + broundRowsCount); } }
/// <summary> /// 采购比价单 /// </summary> public static void ExportMSData(System.Data.DataTable dt) { //Object Opt = System.Type.Missing; //Application m_xlApp = new Application(); //Workbooks workbooks = m_xlApp.Workbooks; //Workbook workbook;// = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //Worksheet wksheet; //workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购比价单明细") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ; //m_xlApp.Visible = false; // Excel不显示 //m_xlApp.DisplayAlerts = false; // 关闭提示,采用默认的方案执行(合并单元格的时候,如果两个单元格都有数据,会出现一个确认提示) //wksheet = (Worksheet)workbook.Sheets.get_Item(1); ////根据批号查询数据 ////设置工作薄名称 //////填充数据 //int rowCount = dt.Rows.Count; //int colCount = dt.Columns.Count; //object[,] dataArray = new object[rowCount, colCount+1]; //for (int i = 0; i < rowCount; i++) //{ // dataArray[i, 0] = i + 1; // for (int j = 0; j < colCount; j++) // { // dataArray[i, j + 1] = dt.Rows[i][j]; // } //} //wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount+1]).Value2 = dataArray; //wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount+1]).Borders.LineStyle = 1; //////设置列宽 //wksheet.Columns.EntireColumn.AutoFit();//列宽自适应 //string filename = System.Web.HttpContext.Current.Server.MapPath("采购比价单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"); //ExportExcel_Exit(filename, workbook, m_xlApp, wksheet); string filename = "采购比价单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购比价单明细.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); #region 写入数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet0.CreateRow(i + 3); row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号 row.CreateCell(1).SetCellValue(dt.Rows[i]["picno"].ToString()); //比价单号 row.CreateCell(2).SetCellValue(dt.Rows[i]["ptcode"].ToString()); //计划跟踪号 row.CreateCell(3).SetCellValue(dt.Rows[i]["PIC_CHILDENGNAME"].ToString()); //部件名称 row.CreateCell(4).SetCellValue(dt.Rows[i]["PIC_MAP"].ToString()); //部件图号 row.CreateCell(5).SetCellValue(dt.Rows[i]["margb"].ToString()); //国标 row.CreateCell(6).SetCellValue(dt.Rows[i]["PIC_TUHAO"].ToString()); //图号 row.CreateCell(7).SetCellValue(dt.Rows[i]["marnm"].ToString()); //名称 row.CreateCell(8).SetCellValue(dt.Rows[i]["margg"].ToString()); //规格 row.CreateCell(9).SetCellValue(dt.Rows[i]["marcz"].ToString()); //材质 row.CreateCell(10).SetCellValue(dt.Rows[i]["length"].ToString()); //长度 row.CreateCell(11).SetCellValue(dt.Rows[i]["width"].ToString()); //宽度 row.CreateCell(12).SetCellValue(dt.Rows[i]["marnum"].ToString()); //数量 row.CreateCell(13).SetCellValue(dt.Rows[i]["marunit"].ToString()); //单位 row.CreateCell(14).SetCellValue(dt.Rows[i]["marfznum"].ToString()); //辅助数量 row.CreateCell(15).SetCellValue(dt.Rows[i]["marfzunit"].ToString()); //辅助单位 row.CreateCell(16).SetCellValue(dt.Rows[i]["supplierresnm"].ToString()); //供应商 //row.CreateCell(4).SetCellValue( dt.Rows[i]["pjnm"].ToString());//项目名称 //row.CreateCell(5).SetCellValue( dt.Rows[i]["engnm"].ToString());//工程名称 //row.CreateCell(7).SetCellValue( dt.Rows[i]["marid"].ToString());//物料编码 row.CreateCell(17).SetCellValue(dt.Rows[i]["price"].ToString()); //单价 row.CreateCell(18).SetCellValue(dt.Rows[i]["detamount"].ToString()); //金额 row.CreateCell(19).SetCellValue(dt.Rows[i]["detailnote"].ToString()); //备注 row.CreateCell(20).SetCellValue(dt.Rows[i]["supplieranm"].ToString()); //供应商1 row.CreateCell(21).SetCellValue(dt.Rows[i]["qoutefstsa"].ToString()); //供应商1 row.CreateCell(22).SetCellValue(dt.Rows[i]["qoutescdsa"].ToString()); //供应商1 row.CreateCell(23).SetCellValue(dt.Rows[i]["qoutelstsa"].ToString()); //供应商1 row.CreateCell(24).SetCellValue(dt.Rows[i]["supplierbnm"].ToString()); //供应商2 row.CreateCell(25).SetCellValue(dt.Rows[i]["qoutefstsb"].ToString()); //供应商2 row.CreateCell(26).SetCellValue(dt.Rows[i]["qoutescdsb"].ToString()); //供应商2 row.CreateCell(27).SetCellValue(dt.Rows[i]["qoutelstsb"].ToString()); //供应商2 row.CreateCell(28).SetCellValue(dt.Rows[i]["suppliercnm"].ToString()); //供应商3 row.CreateCell(29).SetCellValue(dt.Rows[i]["qoutefstsc"].ToString()); //供应商3 row.CreateCell(30).SetCellValue(dt.Rows[i]["qoutescdsc"].ToString()); //供应商3 row.CreateCell(31).SetCellValue(dt.Rows[i]["qoutelstsc"].ToString()); //供应商3 row.CreateCell(32).SetCellValue(dt.Rows[i]["supplierdnm"].ToString()); //供应商4 row.CreateCell(33).SetCellValue(dt.Rows[i]["qoutefstsd"].ToString()); //供应商4 row.CreateCell(34).SetCellValue(dt.Rows[i]["qoutescdsd"].ToString()); //供应商4 row.CreateCell(35).SetCellValue(dt.Rows[i]["qoutelstsd"].ToString()); //供应商4 row.CreateCell(36).SetCellValue(dt.Rows[i]["supplierenm"].ToString()); //供应商5 row.CreateCell(37).SetCellValue(dt.Rows[i]["qoutefstse"].ToString()); //供应商5 row.CreateCell(38).SetCellValue(dt.Rows[i]["qoutescdse"].ToString()); //供应商5 row.CreateCell(39).SetCellValue(dt.Rows[i]["qoutelstse"].ToString()); //供应商5 row.CreateCell(40).SetCellValue(dt.Rows[i]["supplierfnm"].ToString()); //供应商6 row.CreateCell(41).SetCellValue(dt.Rows[i]["qoutefstsf"].ToString()); //供应商6 row.CreateCell(42).SetCellValue(dt.Rows[i]["qoutescdsf"].ToString()); //供应商6 row.CreateCell(43).SetCellValue(dt.Rows[i]["qoutelstsf"].ToString()); //供应商6 NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 11; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); for (int j = 0; j < 44; j++) { row.Cells[j].CellStyle = cells; } } #endregion for (int i = 0; i <= dt.Columns.Count; i++) { sheet0.AutoSizeColumn(i); } sheet0.ForceFormulaRecalculation = true; MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
/// <summary> /// 采购订单 /// </summary> public static void ExportMSData(System.Data.DataTable dt) { //Object Opt = System.Type.Missing; //Application m_xlApp = new Application(); //Workbooks workbooks = m_xlApp.Workbooks; //Workbook workbook;// = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //Worksheet wksheet; //workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购订单明细表模版") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ; //m_xlApp.Visible = false; // Excel不显示 //m_xlApp.DisplayAlerts = false; // 关闭提示,采用默认的方案执行(合并单元格的时候,如果两个单元格都有数据,会出现一个确认提示) //wksheet = (Worksheet)workbook.Sheets.get_Item(1); ////根据批号查询数据 ////设置工作薄名称 //////填充数据 //int rowCount = dt.Rows.Count; //int colCount = dt.Columns.Count; //object[,] dataArray = new object[rowCount, colCount]; //for (int i = 0; i < rowCount; i++) //{ // dataArray[i, 0] = i + 1; // for (int j = 0; j < colCount-2; j++) // { // dataArray[i, j+1] = dt.Rows[i][j]; // } //} //wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount-1]).Value2 = dataArray; //wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount-1]).Borders.LineStyle = 1; //////设置列宽 //wksheet.Columns.EntireColumn.AutoFit();//列宽自适应 //string filename = System.Web.HttpContext.Current.Server.MapPath("采购订单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"); //ExportExcel_Exit(filename, workbook, m_xlApp, wksheet); string filename = "采购订单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购订单明细表模版.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); #region 写入数据 IRow row1 = sheet0.GetRow(1); row1.GetCell(2).SetCellValue(dt.Rows[0]["orderno"].ToString()); //订单编号 row1.GetCell(7).SetCellValue(dt.Rows[0]["suppliernm"].ToString()); //供应商 row1.GetCell(13).SetCellValue(dt.Rows[0]["cgtimerq"].ToString()); //交货日期 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet0.CreateRow(i + 3); #region MyRegion //row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号 //row.CreateCell(1).SetCellValue("'" + dt.Rows[i]["orderno"].ToString());//订单编号 //row.CreateCell(2).SetCellValue("'" + dt.Rows[i]["suppliernm"].ToString());//供应商 //row.CreateCell(3).SetCellValue("'" + dt.Rows[i]["zdtime"].ToString());//制单日期 //row.CreateCell(4).SetCellValue("'" + dt.Rows[i]["ptcode"].ToString());//计划跟踪号 //row.CreateCell(5).SetCellValue("'" + dt.Rows[i]["pjnm"].ToString());//项目名称 //row.CreateCell(6).SetCellValue("'" + dt.Rows[i]["engnm"].ToString());//工程名称 //row.CreateCell(7).SetCellValue("'" + dt.Rows[i]["PO_TUHAO"].ToString());//图号/标识号 //row.CreateCell(8).SetCellValue("'" + dt.Rows[i]["marid"].ToString());//物料编码 //row.CreateCell(9).SetCellValue("'" + dt.Rows[i]["marnm"].ToString());//名称 //row.CreateCell(10).SetCellValue("'" + dt.Rows[i]["margg"].ToString());//规格 //row.CreateCell(11).SetCellValue("'" + dt.Rows[i]["marcz"].ToString());//材质 //row.CreateCell(12).SetCellValue("'" + dt.Rows[i]["margb"].ToString());//国标 //row.CreateCell(13).SetCellValue(dt.Rows[i]["price"].ToString());//单价 //row.CreateCell(14).SetCellValue(dt.Rows[i]["amount"].ToString());//金额 //row.CreateCell(15).SetCellValue(dt.Rows[i]["ctprice"].ToString());//含税单价 //row.CreateCell(16).SetCellValue(dt.Rows[i]["ctamount"].ToString());//价税合计 //row.CreateCell(17).SetCellValue(dt.Rows[i]["zxnum"].ToString());//采购数量 //row.CreateCell(18).SetCellValue("'" + dt.Rows[i]["marunit"].ToString());//单位 //row.CreateCell(19).SetCellValue(dt.Rows[i]["zxfznum"].ToString());//辅助数量 //row.CreateCell(20).SetCellValue("'" + dt.Rows[i]["marfzunit"].ToString());//辅助单位 //row.CreateCell(21).SetCellValue(dt.Rows[i]["recgdnum"].ToString());//已到货数量 //row.CreateCell(22).SetCellValue("'" + dt.Rows[i]["recdate"].ToString());//到货日期 //row.CreateCell(23).SetCellValue("'" + dt.Rows[i]["cgtimerq"].ToString());//交货日期 //row.CreateCell(24).SetCellValue("'" + dt.Rows[i]["length"].ToString());//长度 //row.CreateCell(25).SetCellValue("'" + dt.Rows[i]["width"].ToString());//宽度 //row.CreateCell(26).SetCellValue("'" + dt.Rows[i]["detailnote"].ToString());//备注 #endregion row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号 row.CreateCell(1).SetCellValue(dt.Rows[i]["marid"].ToString()); //物料编码 row.CreateCell(2).SetCellValue(dt.Rows[i]["marnm"].ToString()); //名称 row.CreateCell(3).SetCellValue(dt.Rows[i]["PO_CHILDENGNAME"].ToString()); //部件名称 row.CreateCell(4).SetCellValue(dt.Rows[i]["PO_MAP"].ToString()); //部件图号 row.CreateCell(5).SetCellValue(dt.Rows[i]["margb"].ToString()); //国标 row.CreateCell(6).SetCellValue(dt.Rows[i]["PO_TUHAO"].ToString()); //图号/标识号 row.CreateCell(7).SetCellValue(dt.Rows[i]["margg"].ToString()); //规格 row.CreateCell(8).SetCellValue(dt.Rows[i]["marcz"].ToString()); //材质 row.CreateCell(9).SetCellValue(dt.Rows[i]["length"].ToString()); //长度 row.CreateCell(10).SetCellValue(dt.Rows[i]["width"].ToString()); //宽度 row.CreateCell(11).SetCellValue(dt.Rows[i]["marunit"].ToString()); //单位 row.CreateCell(12).SetCellValue(dt.Rows[i]["zxnum"].ToString()); //采购数量 row.CreateCell(13).SetCellValue(dt.Rows[i]["PO_TECUNIT"].ToString()); //辅助单位 row.CreateCell(14).SetCellValue(dt.Rows[i]["zxfznum"].ToString()); //辅助数量 row.CreateCell(15).SetCellValue(dt.Rows[i]["ctprice"].ToString()); //含税单价 row.CreateCell(16).SetCellValue(dt.Rows[i]["ctamount"].ToString()); //加税合计 //row.CreateCell(19).SetCellValue(dt.Rows[i]["price"].ToString());//单价(不含税) //row.CreateCell(20).SetCellValue(dt.Rows[i]["amount"].ToString());//金额(不含税) //row.CreateCell(22).SetCellValue(dt.Rows[i]["recdate"].ToString());//到货日期 row.CreateCell(17).SetCellValue(dt.Rows[i]["PO_MASHAPE"].ToString()); //类型 row.CreateCell(18).SetCellValue(dt.Rows[i]["detailnote"].ToString()); //备注 //row.CreateCell(25).SetCellValue(dt.Rows[i]["taxrate"].ToString());//税率 //row.CreateCell(26).SetCellValue(dt.Rows[i]["recgdnum"].ToString());//已到货数量 row.CreateCell(19).SetCellValue(dt.Rows[i]["ptcode"].ToString());//计划跟踪号 NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 11; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); for (int j = 0; j < 20; j++) { row.Cells[j].CellStyle = cells; } } #endregion for (int i = 0; i <= dt.Columns.Count; i++) { sheet0.AutoSizeColumn(i); } sheet0.ForceFormulaRecalculation = true; MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
public static bool DataTableToExcel(DataTable dt, string relativeFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; sheet.AutoSizeColumn(icolIndex); icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); ICellStyle datetimeCellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 IDataFormat datastyle = workbook.CreateDataFormat(); datetimeCellStyle.DataFormat = datastyle.GetFormat("yyyy/MM/dd"); datetimeCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; datetimeCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; datetimeCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; datetimeCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; datetimeCellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); if (Colitem.DataType == typeof(DateTime)) { var value = Rowitem[Colitem]; if (value.GetType() == typeof(DateTime)) { cell.SetCellValue(Convert.ToDateTime(value)); } else { cell.SetCellValue(value.ToString()); } cell.CellStyle = datetimeCellStyle; } else { cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; } iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel var absolutePath = Path.Combine(SystemInfo.BaseDirectory, relativeFileName); var dir = Path.GetDirectoryName(absolutePath); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } FileStream file = new FileStream(absolutePath, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); return(true); } catch (Exception ex) { return(false); //ILog log = LogManager.GetLogger("Exception Log"); //log.Error(ex.Message + Environment.NewLine + ex.StackTrace); } finally { //workbook = null; } }
private void ExportDataItem(DataTable dt) { DataRow dr = dt.Rows[0]; string filename = "培训实施记录--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训实施记录.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); for (int i = 0, length = dt.Rows.Count; i < length; i++) { IRow row = sheet0.CreateRow(i + 2); row.HeightInPoints = 20; row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号 row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_BM"].ToString()); row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BH"].ToString()); row.CreateCell(3).SetCellValue(dt.Rows[i]["SPLX"].ToString() == "NDPXJH" ? "年度" : "临时"); row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部"); row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString()); row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJ"].ToString() == "1" ? "第一季度" : dt.Rows[i]["PX_SJ"].ToString() == "2" ? "第二季度" : dt.Rows[i]["PX_SJ"].ToString() == "3" ? "第三季度" : "第四季度"); row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_DD"].ToString()); row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString()); row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_DX"].ToString()); row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_RS"].ToString()); row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_XS"].ToString()); row.CreateCell(12).SetCellValue(dt.Rows[i]["PX_FYYS"].ToString()); row.CreateCell(13).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString()); row.CreateCell(14).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString()); row.CreateCell(15).SetCellValue(dt.Rows[i]["PX_SJRY"].ToString()); row.CreateCell(16).SetCellValue(dt.Rows[i]["PX_SJRS"].ToString()); row.CreateCell(17).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString()); row.CreateCell(18).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString()); NPOI.SS.UserModel.IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 9; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.SetFont(font1); cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; for (int j = 0; j <= 18; j++) { row.Cells[j].CellStyle = cells; } } double num = 0; for (int i = 0, length = dt.Rows.Count; i < length; i++) { num += CommonFun.ComTryDouble(dt.Rows[i]["PX_SJXS"].ToString()); } IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 2); for (int i = 0; i <= 18; i++) { rowhz.CreateCell(i); } rowhz.GetCell(0).SetCellValue("人数合计"); rowhz.GetCell(17).SetCellValue(num); rowhz.HeightInPoints = 20; NPOI.SS.UserModel.IFont font2 = wk.CreateFont(); font2.FontName = "仿宋"; //字体 font2.FontHeightInPoints = 9; //字号 ICellStyle cells2 = wk.CreateCellStyle(); cells2.SetFont(font2); cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cells2.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i <= 18; i++) { rowhz.Cells[i].CellStyle = cells2; } CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16); sheet0.AddMergedRegion(range1); //CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17); //sheet0.AddMergedRegion(range2); //IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6); //for (int i = 0; i <= 17; i++) //{ // rowqz1.CreateCell(i); //} //rowqz1.GetCell(0).SetCellValue("编制负责人:"); //rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString()); //rowqz1.GetCell(5).SetCellValue("申报单位/部门主管:"); //rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString()); //rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : ""); //rowqz1.GetCell(12).SetCellValue("集团公司主管领导:"); //rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString()); //rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : ""); //for (int i = 0; i <= 17; i++) //{ // rowqz1.Cells[i].CellStyle = cells2; //} //CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1); //sheet0.AddMergedRegion(range3); ////CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4); ////sheet0.AddMergedRegion(range4); //CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6); //sheet0.AddMergedRegion(range5); ////CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11); ////sheet0.AddMergedRegion(range6); //CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13); //sheet0.AddMergedRegion(range7); //IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7); //for (int i = 0; i <= 17; i++) //{ // rowqz2.CreateCell(i); //} //rowqz2.GetCell(1).SetCellValue("日期:"); //rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString()); //rowqz2.GetCell(6).SetCellValue("日期:"); //rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString()); //rowqz2.GetCell(13).SetCellValue("日期:"); //rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString()); //for (int i = 0; i <= 17; i++) //{ // rowqz2.Cells[i].CellStyle = cells2; //} MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }