/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="strHeaderText">Excel表头文本(例如:车辆列表)</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "lixun"; //填加xls文件作者信息 si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息 si.LastAuthor = "lixun"; //填加xls文件最后保存者信息 si.Comments = ""; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = ""; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion CellStyle dateStyle = workbook.CreateCellStyle(); DataFormat 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(); } #region 表头及样式 { if (strHeaderText.Length > 0) { Row headerRow = sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------ Font 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++; } } #endregion #region 列头及样式 { Row headerRow = sheet.CreateRow(rowIndex); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; // ------------------ headStyle.BorderTop = CellBorderType.THIN; headStyle.BorderBottom = CellBorderType.THIN; headStyle.BorderLeft = CellBorderType.THIN; headStyle.BorderRight = CellBorderType.THIN; Font font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { string sName = column.ColumnName; if (sName.Contains("剥离")) { sName = "剥离"; } else if (sName.Contains("样品1")) { sName = "热合样品1"; } else if (sName.Contains("样品2")) { sName = "热合样品2"; } else if (sName.Contains("泡水")) { sName = "泡水"; } headerRow.CreateCell(column.Ordinal).SetCellValue(sName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 350); } sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 6, 7)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 8, 10)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 11, 13)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 18, 19)); rowIndex++; } #endregion } #endregion #region 填充内容 Row dataRow = sheet.CreateRow(rowIndex); CellStyle mainStyle = workbook.CreateCellStyle(); mainStyle.BorderTop = CellBorderType.THIN; mainStyle.BorderBottom = CellBorderType.THIN; mainStyle.BorderLeft = CellBorderType.THIN; mainStyle.BorderRight = CellBorderType.THIN; foreach (DataColumn column in dtSource.Columns) { Cell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = mainStyle; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.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(); return(ms); } }
public void MergeRegion(int firstRow, int lastRow, int firstCol, int lastCol) { var range = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); Sheet.AddMergedRegion(range); }
protected override void SetDataHeader(Sheet worksheet, DataFeedTemplate data) { int colindex = 0; Row headerNameRow = worksheet.CreateRow(HeaderNameRowIndex); headerNameRow.HeightInPoints = 30; Row headerSymbolRow = worksheet.CreateRow(MustInputSymbolRowIndex); //headerSymbolRow.HeightInPoints = 30; Row headerMergeRow = worksheet.CreateRow(MergeHeaderRowIndex); headerMergeRow.HeightInPoints = 30; //int mustInputColIndex = 0; //分组字典 Dictionary <string, List <int> > dicGroupList = new Dictionary <string, List <int> >(); foreach (var item in data.HeaderGroups) { dicGroupList.Add(item.Key, new List <int>()); } //基本列 foreach (var item in data.BasicColumns) { HeaderGroup currentHeaderGroup = data.HeaderGroups.SingleOrDefault(p => p.Index == item.HeaderGroupIndex); #region [处理列必填标志] Cell cellSymbol = headerSymbolRow.CreateCell(colindex); float cellHeight = cellSymbol.Row.HeightInPoints; if (item.IsMustInput) { cellSymbol.SetCellValue(MustInputSymbol); //mustInputColIndex++; } CellStyle style = cellSymbol.GetCommonCellStyle(); Font font = cellSymbol.Sheet.Workbook.CreateFont(); font.FontHeightInPoints = 18; font.Boldweight = (short)FontBoldWeight.BOLD; font.Color = HSSFColor.RED.index; style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; if (item.Name == NotInputHeaderName_Item) { cellSymbol.SetCellValue(SystemFillSymbol); font.FontHeightInPoints = 10; font.Color = HSSFColor.BLACK.index; } style.SetFont(font); cellSymbol.CellStyle = style; cellSymbol.Row.HeightInPoints = cellHeight; #endregion #region [写列名称] Cell cellHeaderName = headerNameRow.CreateCell(colindex); cellHeaderName.SetCellType(CellType.STRING); style = cellHeaderName.GetCommonCellStyle(); style.FillPattern = FillPatternType.SOLID_FOREGROUND; style.FillForegroundColor = short.Parse(currentHeaderGroup.Color); if (item.Name == NotInputHeaderName_Item) { Font fontHeaderName = cellHeaderName.Sheet.Workbook.CreateFont(); style.FillForegroundColor = NotInputCellColor; fontHeaderName.Color = HSSFColor.WHITE.index; fontHeaderName.Boldweight = (short)FontBoldWeight.BOLD; style.SetFont(fontHeaderName); } style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; cellHeaderName.CellStyle = style; cellHeaderName.SetCellValue(item.Name); #endregion //填充分组字典 if (currentHeaderGroup != null) { dicGroupList[currentHeaderGroup.Key].Add(colindex); } colindex = colindex + 1; } //获取颜色 HeaderGroup currentDynamicHeader = data.HeaderGroups.SingleOrDefault(p => p.Name == HeaderGroupName); if (data.Properties != null && data.Properties.Count > 0) { Font font = headerNameRow.Sheet.Workbook.CreateFont(); foreach (var item in data.Properties) { Cell cellHeaderNameDynamic = headerNameRow.CreateCell(colindex); Cell cellSymbol = headerSymbolRow.CreateCell(colindex); cellHeaderNameDynamic.SetCellValue(item.Name); float cellHeight = cellSymbol.Row.HeightInPoints; //if (item.IsMustInput) //{ // font.Color = HSSFColor.RED.index; // cellSymbol.SetCellValue(MustInputSymbol); //} if (item.IsInAdvSearch) { cellSymbol.SetCellValue(AdvSearchSymbol); } CellStyle styleHeaderNameDynamic = cellHeaderNameDynamic.GetCommonCellStyle(); CellStyle styleSymbol = cellSymbol.GetCommonCellStyle(); font.FontHeightInPoints = 18; font.Boldweight = (short)FontBoldWeight.BOLD; font.Color = HSSFColor.GREEN.index; styleSymbol.SetFont(font); cellSymbol.Row.HeightInPoints = cellHeight; styleHeaderNameDynamic.BorderLeft = CellBorderType.THIN; styleHeaderNameDynamic.BorderTop = CellBorderType.THIN; styleHeaderNameDynamic.BorderRight = CellBorderType.THIN; styleSymbol.BorderLeft = CellBorderType.THIN; styleSymbol.BorderRight = CellBorderType.THIN; //背景色 styleHeaderNameDynamic.FillPattern = FillPatternType.SOLID_FOREGROUND; styleHeaderNameDynamic.FillForegroundColor = short.Parse(currentDynamicHeader.Color); cellHeaderNameDynamic.CellStyle = styleHeaderNameDynamic; cellSymbol.CellStyle = styleSymbol; dicGroupList[HeaderGroupName].Add(colindex); colindex = colindex + 1; } } #region [处理列分类信息] foreach (var item in dicGroupList) { if (item.Value != null && item.Value.Count > 0) { CellRangeAddress mergeArea = new CellRangeAddress(MergeHeaderRowIndex, MergeHeaderRowIndex, item.Value.First(), item.Value.Last()); worksheet.AddMergedRegion(mergeArea); ((HSSFSheet)worksheet).SetEnclosedBorderOfRegion(mergeArea, CellBorderType.MEDIUM, NPOI.HSSF.Util.HSSFColor.BLACK.index); headerMergeRow.HeightInPoints = 30; Cell currentGroupCell = headerMergeRow.GetCell(item.Value.First()); if (currentGroupCell == null) { currentGroupCell = headerMergeRow.CreateCell(item.Value.First()); } if (item.Key == HeaderGroupName) { currentGroupCell.SetCellValue(C3Name + item.Key); } else { currentGroupCell.SetCellValue(item.Key); } CellStyle style = currentGroupCell.GetCommonCellStyle(); currentGroupCell.CellStyle = style; } } #endregion }
private static MemoryStream Export2Delivery(DeliveryNote note) { HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "ChuTian"; //填加xls文件作者信息 si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息 si.LastAuthor = "ChuTian"; //填加xls文件最后保存者信息 si.Comments = ""; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = ""; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion //CellStyle dateStyle = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 1; sheet.SetColumnWidth(0, (int)((9.5 + 0.72) * 256)); sheet.SetColumnWidth(1, (int)((18.5 + 0.72) * 256)); sheet.SetColumnWidth(2, (int)((9.5 + 0.72) * 256)); sheet.SetColumnWidth(3, (int)((12.5 + 0.72) * 256)); sheet.SetColumnWidth(4, (int)((12.5 + 0.72) * 256)); sheet.SetColumnWidth(5, (int)((15.5 + 0.72) * 256)); sheet.SetColumnWidth(6, (int)((20 + 0.72) * 256)); //所有的字体 Font font18 = workbook.CreateFont(); font18.FontName = "华文彩云"; font18.FontHeightInPoints = 18; font18.IsItalic = true; Font font11Bold = workbook.CreateFont(); font11Bold.FontHeightInPoints = 11; font11Bold.Boldweight = 700; Font font11Normal = workbook.CreateFont(); font11Normal.FontHeightInPoints = 11; Font font20Bold = workbook.CreateFont(); font20Bold.FontHeightInPoints = 20; font20Bold.Boldweight = 700; Font foot10Bold = workbook.CreateFont(); foot10Bold.FontHeightInPoints = 10; foot10Bold.Boldweight = 700; CellStyle companyStyle = workbook.CreateCellStyle(); companyStyle.Alignment = HorizontalAlignment.CENTER; companyStyle.SetFont(font18); Row companyRow = sheet.CreateRow(rowIndex); companyRow.HeightInPoints = 30; companyRow.CreateCell(0).SetCellValue(" 湖北楚天通讯材料有限公司"); companyRow.GetCell(0).CellStyle = companyStyle; rowIndex++; //创建表头 CellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.CENTER; titleStyle.SetFont(font20Bold); Row titleRow = sheet.CreateRow(rowIndex); titleRow.HeightInPoints = 29; titleRow.CreateCell(0).SetCellValue(" 送 货 单"); titleRow.GetCell(0).CellStyle = titleStyle; //创建Logo string strLogoPath = AppDomain.CurrentDomain.BaseDirectory + "ctlogo.png"; byte[] bLogo = System.IO.File.ReadAllBytes(strLogoPath); int pictureIdx = workbook.AddPicture(bLogo, PictureType.PNG); var patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 82, 39, 0, 2, 0, 2); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex++; rowIndex++; //创建父表内容 CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.LEFT; headStyle.SetFont(font11Bold); CellStyle headDescStyle = workbook.CreateCellStyle(); headDescStyle.Alignment = HorizontalAlignment.RIGHT; headDescStyle.SetFont(font11Normal); Row parentRow1 = sheet.CreateRow(rowIndex); parentRow1.CreateCell(0).SetCellValue("客户:"); parentRow1.CreateCell(1).SetCellValue(note.customer); parentRow1.CreateCell(4).SetCellValue("送货单号:"); parentRow1.CreateCell(5).SetCellValue(note.deliverid.ToString().PadLeft(3, '0')); parentRow1.CreateCell(6).SetCellValue(note.description); parentRow1.GetCell(0).CellStyle = headStyle; parentRow1.GetCell(1).CellStyle = headStyle; parentRow1.GetCell(4).CellStyle = headStyle; parentRow1.GetCell(5).CellStyle = headStyle; parentRow1.GetCell(6).CellStyle = headDescStyle; rowIndex++; Row parentRow2 = sheet.CreateRow(rowIndex); parentRow2.CreateCell(0).SetCellValue("型号:"); parentRow2.CreateCell(1).SetCellValue(note.model); parentRow2.CreateCell(4).SetCellValue("发货时间:"); parentRow2.CreateCell(5).SetCellValue(note.deliverdate.ToString("yyyy.MM.dd")); parentRow2.GetCell(0).CellStyle = headStyle; parentRow2.GetCell(1).CellStyle = headStyle; parentRow2.GetCell(4).CellStyle = headStyle; parentRow2.GetCell(5).CellStyle = headStyle; rowIndex++; Row parentRow3 = sheet.CreateRow(rowIndex); parentRow3.CreateCell(0).SetCellValue("货物名称:"); parentRow3.CreateCell(1).SetCellValue(note.goodname); parentRow3.CreateCell(4).SetCellValue("出厂批号:"); parentRow3.CreateCell(5).SetCellValue(note.batch); parentRow3.GetCell(0).CellStyle = headStyle; parentRow3.GetCell(1).CellStyle = headStyle; parentRow3.GetCell(4).CellStyle = headStyle; parentRow3.GetCell(5).CellStyle = headStyle; rowIndex++; //表格样式 CellStyle tableStyle = workbook.CreateCellStyle(); CellStyle tableLeftStyle = workbook.CreateCellStyle(); tableStyle.Alignment = HorizontalAlignment.CENTER; tableLeftStyle.Alignment = HorizontalAlignment.LEFT; tableStyle.SetFont(font11Normal); tableLeftStyle.SetFont(font11Normal); tableStyle.BorderTop = CellBorderType.THIN; tableStyle.BorderBottom = CellBorderType.THIN; tableStyle.BorderLeft = CellBorderType.THIN; tableStyle.BorderRight = CellBorderType.THIN; tableLeftStyle.BorderTop = CellBorderType.THIN; tableLeftStyle.BorderBottom = CellBorderType.THIN; tableLeftStyle.BorderLeft = CellBorderType.THIN; tableLeftStyle.BorderRight = CellBorderType.THIN; //小计和合计样式 CellStyle tableSumStyle = workbook.CreateCellStyle(); CellStyle tableSumLeftStyle = workbook.CreateCellStyle(); tableSumStyle.Alignment = HorizontalAlignment.CENTER; tableSumLeftStyle.Alignment = HorizontalAlignment.LEFT; tableSumStyle.SetFont(font11Bold); tableSumLeftStyle.SetFont(font11Bold); tableSumStyle.BorderTop = CellBorderType.THIN; tableSumStyle.BorderBottom = CellBorderType.THIN; tableSumStyle.BorderLeft = CellBorderType.THIN; tableSumStyle.BorderRight = CellBorderType.THIN; tableSumLeftStyle.BorderTop = CellBorderType.THIN; tableSumLeftStyle.BorderBottom = CellBorderType.THIN; tableSumLeftStyle.BorderLeft = CellBorderType.THIN; tableSumLeftStyle.BorderRight = CellBorderType.THIN; Row colHeaderRow = sheet.CreateRow(rowIndex); colHeaderRow.CreateCell(0).SetCellValue("件号"); colHeaderRow.CreateCell(1).SetCellValue("规格"); colHeaderRow.CreateCell(2).SetCellValue("盘数"); colHeaderRow.CreateCell(3).SetCellValue("净重(KG)"); colHeaderRow.CreateCell(4).SetCellValue("单价"); colHeaderRow.CreateCell(5).SetCellValue("金额"); colHeaderRow.CreateCell(6).SetCellValue("合同号"); colHeaderRow.CreateCell(7).SetCellValue("毛重"); colHeaderRow.CreateCell(8).SetCellValue("管芯重量"); colHeaderRow.GetCell(0).CellStyle = tableLeftStyle; colHeaderRow.GetCell(1).CellStyle = tableStyle; colHeaderRow.GetCell(2).CellStyle = tableStyle; colHeaderRow.GetCell(3).CellStyle = tableStyle; colHeaderRow.GetCell(4).CellStyle = tableStyle; colHeaderRow.GetCell(5).CellStyle = tableStyle; colHeaderRow.GetCell(6).CellStyle = tableStyle; colHeaderRow.GetCell(7).CellStyle = tableStyle; colHeaderRow.GetCell(8).CellStyle = tableStyle; rowIndex++; Dictionary <string, List <DeliveryItem> > dicDelivery = DeliveryItemGroup(note.items); int iDisoTotal = 0; double dWeightTotal = 0; double dPriceTotal = 0; foreach (string delivertyId in dicDelivery.Keys) { int iDisoSum = 0; double dWeightSum = 0; double dPriceSum = 0; List <DeliveryItem> noteItems = dicDelivery[delivertyId]; foreach (DeliveryItem item in noteItems) { iDisoSum = iDisoSum + item.discnum; dWeightSum = dWeightSum + item.weight; dPriceSum = dPriceSum + item.totalprice; iDisoTotal = iDisoTotal + item.discnum; dWeightTotal = dWeightTotal + item.weight; dPriceTotal = dPriceTotal + item.totalprice; Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue(item.jiannum); colItemRow.GetCell(0).CellStyle = tableLeftStyle; colItemRow.CreateCell(1).SetCellValue(item.specifications + "*" + item.lenght.ToString()); colItemRow.GetCell(1).CellStyle = tableStyle; colItemRow.CreateCell(2).SetCellValue(item.discnum); colItemRow.GetCell(2).CellStyle = tableStyle; colItemRow.CreateCell(3).SetCellValue(item.weight.ToString("f2")); colItemRow.GetCell(3).CellStyle = tableStyle; colItemRow.CreateCell(4).SetCellValue(item.price.ToString("f5")); colItemRow.GetCell(4).CellStyle = tableStyle; colItemRow.CreateCell(5).SetCellValue(item.totalprice.ToString("f5")); colItemRow.GetCell(5).CellStyle = tableStyle; colItemRow.CreateCell(6).SetCellValue(item.contractno); colItemRow.GetCell(6).CellStyle = tableStyle; colItemRow.CreateCell(7).SetCellValue(item.netweight); colItemRow.GetCell(7).CellStyle = tableStyle; colItemRow.CreateCell(8).SetCellValue(item.coreweight); colItemRow.GetCell(8).CellStyle = tableStyle; rowIndex++; } if (noteItems.Count > 1 && dicDelivery.Count > 1) { Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue("小计"); colItemRow.GetCell(0).CellStyle = tableSumLeftStyle; colItemRow.CreateCell(1).SetCellValue(""); colItemRow.GetCell(1).CellStyle = tableSumStyle; colItemRow.CreateCell(2).SetCellValue(iDisoSum.ToString()); colItemRow.GetCell(2).CellStyle = tableSumStyle; colItemRow.CreateCell(3).SetCellValue(dWeightSum.ToString("f2")); colItemRow.GetCell(3).CellStyle = tableSumStyle; colItemRow.CreateCell(4).SetCellValue(""); colItemRow.GetCell(4).CellStyle = tableSumStyle; colItemRow.CreateCell(5).SetCellValue(dPriceSum.ToString("f2")); colItemRow.GetCell(5).CellStyle = tableSumStyle; colItemRow.CreateCell(6).SetCellValue(""); colItemRow.GetCell(6).CellStyle = tableSumStyle; colItemRow.CreateCell(7).SetCellValue(""); colItemRow.GetCell(7).CellStyle = tableSumStyle; colItemRow.CreateCell(8).SetCellValue(""); colItemRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; } else { Row colItemRow = sheet.CreateRow(rowIndex); colItemRow.CreateCell(0).SetCellValue(""); colItemRow.GetCell(0).CellStyle = tableSumLeftStyle; colItemRow.CreateCell(1).SetCellValue(""); colItemRow.GetCell(1).CellStyle = tableSumStyle; colItemRow.CreateCell(2).SetCellValue(""); colItemRow.GetCell(2).CellStyle = tableSumStyle; colItemRow.CreateCell(3).SetCellValue(""); colItemRow.GetCell(3).CellStyle = tableSumStyle; colItemRow.CreateCell(4).SetCellValue(""); colItemRow.GetCell(4).CellStyle = tableSumStyle; colItemRow.CreateCell(5).SetCellValue(""); colItemRow.GetCell(5).CellStyle = tableSumStyle; colItemRow.CreateCell(6).SetCellValue(""); colItemRow.GetCell(6).CellStyle = tableSumStyle; colItemRow.CreateCell(7).SetCellValue(""); colItemRow.GetCell(7).CellStyle = tableSumStyle; colItemRow.CreateCell(8).SetCellValue(""); colItemRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; } } //空行 Row colBlankRow = sheet.CreateRow(rowIndex); colBlankRow.CreateCell(0).SetCellValue(""); colBlankRow.GetCell(0).CellStyle = tableLeftStyle; colBlankRow.CreateCell(1).SetCellValue(""); colBlankRow.GetCell(1).CellStyle = tableStyle; colBlankRow.CreateCell(2).SetCellValue(""); colBlankRow.GetCell(2).CellStyle = tableStyle; colBlankRow.CreateCell(3).SetCellValue(""); colBlankRow.GetCell(3).CellStyle = tableStyle; colBlankRow.CreateCell(4).SetCellValue(""); colBlankRow.GetCell(4).CellStyle = tableStyle; colBlankRow.CreateCell(5).SetCellValue(""); colBlankRow.GetCell(5).CellStyle = tableStyle; colBlankRow.CreateCell(6).SetCellValue(""); colBlankRow.GetCell(6).CellStyle = tableStyle; colBlankRow.CreateCell(7).SetCellValue(""); colBlankRow.GetCell(7).CellStyle = tableStyle; colBlankRow.CreateCell(8).SetCellValue(""); colBlankRow.GetCell(8).CellStyle = tableStyle; rowIndex++; //合计 Row colTotalRow = sheet.CreateRow(rowIndex); colTotalRow.CreateCell(0).SetCellValue("合计"); colTotalRow.GetCell(0).CellStyle = tableSumLeftStyle; colTotalRow.CreateCell(1).SetCellValue(""); colTotalRow.GetCell(1).CellStyle = tableSumStyle; colTotalRow.CreateCell(2).SetCellValue(iDisoTotal.ToString()); colTotalRow.GetCell(2).CellStyle = tableSumStyle; colTotalRow.CreateCell(3).SetCellValue(dWeightTotal.ToString("f2")); colTotalRow.GetCell(3).CellStyle = tableSumStyle; colTotalRow.CreateCell(4).SetCellValue(""); colTotalRow.GetCell(4).CellStyle = tableSumStyle; colTotalRow.CreateCell(5).SetCellValue(dPriceTotal.ToString("f2")); colTotalRow.GetCell(5).CellStyle = tableSumStyle; colTotalRow.CreateCell(6).SetCellValue(""); colTotalRow.GetCell(6).CellStyle = tableSumStyle; colTotalRow.CreateCell(7).SetCellValue(""); colTotalRow.GetCell(7).CellStyle = tableSumStyle; colTotalRow.CreateCell(8).SetCellValue(""); colTotalRow.GetCell(8).CellStyle = tableSumStyle; rowIndex++; CellStyle footStyle = workbook.CreateCellStyle(); footStyle.Alignment = HorizontalAlignment.LEFT; footStyle.SetFont(font11Normal); CellStyle footBoldStyle = workbook.CreateCellStyle(); footBoldStyle.Alignment = HorizontalAlignment.LEFT; footBoldStyle.SetFont(font11Bold); CellStyle footRightStyle = workbook.CreateCellStyle(); footRightStyle.Alignment = HorizontalAlignment.RIGHT; footRightStyle.SetFont(font11Normal); Row descRow = sheet.CreateRow(rowIndex); descRow.CreateCell(0).SetCellValue("备注:"); descRow.GetCell(0).CellStyle = footBoldStyle; descRow.CreateCell(1).SetCellValue(note.description1); descRow.GetCell(1).CellStyle = footBoldStyle; rowIndex++; Row footerRow = sheet.CreateRow(rowIndex); footerRow.CreateCell(0).SetCellValue("请按上列货验收"); footerRow.GetCell(0).CellStyle = footStyle; rowIndex++; Row footerRow1 = sheet.CreateRow(rowIndex); footerRow1.CreateCell(0).SetCellValue("收货人:"); footerRow1.GetCell(0).CellStyle = footStyle; footerRow1.CreateCell(1).SetCellValue(""); footerRow1.GetCell(1).CellStyle = footStyle; footerRow1.CreateCell(2).SetCellValue("送货人:"); footerRow1.GetCell(2).CellStyle = footStyle; footerRow1.CreateCell(3).SetCellValue(""); footerRow1.GetCell(3).CellStyle = footRightStyle; footerRow1.CreateCell(4).SetCellValue("制单:"); footerRow1.GetCell(4).CellStyle = footRightStyle; footerRow1.CreateCell(5).SetCellValue(note.loginid); footerRow1.GetCell(5).CellStyle = footStyle; footerRow1.CreateCell(6).SetCellValue("审核:"); footerRow1.GetCell(6).CellStyle = footStyle; rowIndex++; CellStyle footStyle1 = workbook.CreateCellStyle(); footStyle1.Alignment = HorizontalAlignment.LEFT; footStyle1.SetFont(foot10Bold); Row footerRow2 = sheet.CreateRow(rowIndex); footerRow2.CreateCell(0).SetCellValue("地址:湖北省汉川市马口工业园区楚天路"); footerRow2.GetCell(0).CellStyle = footStyle1; footerRow2.CreateCell(1).SetCellValue(""); footerRow2.GetCell(1).CellStyle = footStyle1; footerRow2.CreateCell(2).SetCellValue(""); footerRow2.GetCell(2).CellStyle = footStyle1; footerRow2.CreateCell(3).SetCellValue(" 电话:0712-8521088 传真:0712-8512311"); footerRow2.GetCell(3).CellStyle = footStyle1; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2)); using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); workbook.Dispose(); return(ms); } }
/// <summary> /// 普通单表导出npoi(集合数据+样式) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button2_Click(object sender, EventArgs e) { string filename = "test.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.Clear(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称"); CellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.FillBackgroundColor = HSSFColor.PINK.index; var row0 = sheet1.CreateRow(0).CreateCell(0); row0.SetCellValue("This is a Sample");//sheet标题 row0.CellStyle = style; var j = 17; #region 居中/自动换行 CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式 styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式 styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式 styleCenter.WrapText = true; //自动换行 sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter; var cell17 = sheet1.CreateRow(j).CreateCell(j); var cell172 = sheet1.CreateRow(j).CreateCell(j + 1); cell17.CellStyle = styleCenter; cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); //cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); j++; #endregion #region 设置宽高度 sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。 var rowwh = sheet1.CreateRow(j); rowwh.HeightInPoints = 50; //高度 rowwh.CreateCell(j).SetCellValue("宽高度"); j++; #endregion #region 自适应宽度(对中文不友好)+自动换行 /*场景: * 12林学1班 * 12林学1班 */ CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式 autoAndWrap.WrapText = true; //自动换行 var rowwhauto = sheet1.CreateRow(j); var cellauto = rowwhauto.CreateCell(j); cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度"); sheet1.AutoSizeColumn(j); cellauto.CellStyle = autoAndWrap; j++; #endregion #region 设置背景色 CellStyle style1 = hssfworkbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; sheet1.CreateRow(j).CreateCell(j).CellStyle = style1; j++; #endregion #region 自定义背景色 HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228); HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228); CellStyle style2 = hssfworkbook.CreateCellStyle(); style2.FillPattern = FillPatternType.SOLID_FOREGROUND; style2.FillForegroundColor = hssFColor.GetIndex(); sheet1.CreateRow(j).CreateCell(j).CellStyle = style2; j++; #endregion #region 设置字体颜色 CellStyle style3 = hssfworkbook.CreateCellStyle(); Font font1 = hssfworkbook.CreateFont(); font1.Color = hssFColor.GetIndex();//颜色 style3.SetFont(font1); var cell20 = sheet1.CreateRow(j).CreateCell(j); cell20.CellStyle = style3; cell20.SetCellValue("666666666"); j++; #endregion List <ModelStu> data = StuDaTa.GetData(); string[] arrthead = { "ID", "name", "age", "pc" }; sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1)); Row row1 = sheet1.CreateRow(1); for (int i = 0; i < arrthead.Length; i++) { row1.CreateCell(i).SetCellValue(arrthead[i]); } for (int i = 0; i < data.Count; i++) { Row row = sheet1.CreateRow(i + 2); var colIndex = 0; row.CreateCell(colIndex++).SetCellValue(data[i].id); row.CreateCell(colIndex++).SetCellValue(data[i].name); row.CreateCell(colIndex++).SetCellValue(data[i].age); row.CreateCell(colIndex).SetCellValue(data[i].pc); } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); Response.BinaryWrite(file.GetBuffer()); Response.End(); hssfworkbook = null; file.Close(); file.Dispose(); }
public object getExcel(TransportationBill tb, IList <TransportationBillDetail> detailList, bool isExportExcel) { string path = Server.MapPath(".") + @"\Reports\Templates\YFKExcelTemplates\TransportationBill.xls"; if (File.Exists(path)) { string filename = @"/Reports/Templates/TempFiles/temp_" + DateTime.Now.ToString("yyyyMMddhhmmss") + tb.BillNo + ".xls"; string _wpath = Server.MapPath(".") + filename; File.Copy(path, _wpath); FileStream file = new FileStream(_wpath, FileMode.Open, FileAccess.ReadWrite, FileShare.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); Sheet sheet = hssfworkbook.GetSheet("sheet1"); NPOI.SS.UserModel.CellStyle normalStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center" }); NPOI.SS.UserModel.CellStyle dateStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center", "DateTime" }); Cell cell = sheet.GetRow(8).GetCell(2); cell.SetCellValue(tb.BillAddress.Party.Name); cell = sheet.GetRow(8).GetCell(8); cell.SetCellValue(tb.BillNo); int i = 10; decimal cnt = 0; foreach (TransportationBillDetail tbd in detailList) { Row row = sheet.CreateRow(i); TransportationOrder tord = TheTransportationOrderMgr.LoadTransportationOrder(tbd.ActBill.OrderNo); row.CreateCell(0).SetCellValue(tord.CreateDate); //运输日期 row.CreateCell(1).SetCellValue(tord.TransportationRoute != null ? tord.TransportationRoute.Description : ""); //运输路线 row.CreateCell(2).SetCellValue(tbd.ActBill.PricingMethod != null ? tbd.ActBill.PricingMethod : ""); //运输形式 row.CreateCell(3).SetCellValue(tord.OrderNo); //运单号码 row.CreateCell(4).SetCellValue(tbd.ActBill.EffectiveDate); //生效日期 row.CreateCell(5).SetCellValue(tbd.ActBill.UnitPrice.ToString("F2")); //单价 row.CreateCell(6).SetCellValue(tbd.ActBill.Currency.Name); //币种 row.CreateCell(7).SetCellValue(tbd.ActBill.BillQty.ToString("F0")); //开票数 row.CreateCell(8).SetCellValue(tbd.ActBill.BillAmount.ToString("F2")); //金额 cnt = Convert.ToInt32(tbd.ActBill.BillAmount) + cnt; for (int y = 0; y < 9; y++) { row.GetCell(y).CellStyle = normalStyle; } row.GetCell(0).CellStyle = dateStyle; row.GetCell(4).CellStyle = dateStyle; i++; } if (i <= 20) { for (int j = i; j < 21; j++) { Row row = sheet.CreateRow(j); for (int y = 0; y < 9; y++) { row.CreateCell(y).CellStyle = normalStyle; } } i = 20; } Row _row = sheet.CreateRow(i + 1); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 1, i + 1, 6, 7)); _row.CreateCell(6).SetCellValue("合计发票金额:"); _row.GetCell(6).CellStyle.Alignment = HorizontalAlignment.RIGHT; _row.CreateCell(8).SetCellValue(cnt.ToString("F2")); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); if (!isExportExcel) { FileStream f = new FileStream(_wpath, FileMode.Open, FileAccess.Write); byte[] data = ms.ToArray(); f.Write(data, 0, data.Length); f.Close(); f.Dispose(); hssfworkbook = null; ms.Close(); ms.Dispose(); return("http://" + Request.Url.Authority + filename); } else { Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TBillResult.xls")); Response.BinaryWrite(ms.ToArray()); hssfworkbook = null; ms.Close(); ms.Dispose(); return(null); } } else { return(null); } }
/// <summary> /// /// </summary> /// <param name="sheet"></param> private void LoadPremiumInfo(Sheet sheet,Sheet contentSheet) { Row titleOne = CreateCell(sheet,contentSheet,0,0); contentSheet.AddMergedRegion(new CellRangeAddress(0,0,0,9)); }
private void SetHead(Sheet sheet) { //sheet = hssWorkBook.CreateSheet("CustomerInfo"); Row rowHead = sheet.CreateRow(0); Cell cell = rowHead.CreateCell(0); cell.SetCellValue("新客户注册统计表"); Cell cellhead1 = rowHead.CreateCell(1); Cell cellhead2 = rowHead.CreateCell(2); Cell cellhead3 = rowHead.CreateCell(3); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3)); NPOI.SS.UserModel.CellStyle style = hssWorkBook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; Font font = hssWorkBook.CreateFont(); font.FontName = "黑体"; font.Boldweight = 700; font.FontHeightInPoints = 14; style.SetFont(font); //style.BorderBottom = CellBorderType.THIN; //style.BorderLeft = CellBorderType.THIN; //style.BorderRight = CellBorderType.THIN; //style.BorderTop = CellBorderType.THIN; cell.CellStyle = style; cellhead1.CellStyle = style; cellhead2.CellStyle = style; cellhead3.CellStyle = style; rowHead.Height = 2 * 256; NPOI.SS.UserModel.CellStyle stylefirst = hssWorkBook.CreateCellStyle(); stylefirst.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; Font fontfirst = hssWorkBook.CreateFont(); fontfirst.FontName = "宋体"; fontfirst.FontHeightInPoints = 10; fontfirst.Boldweight = 700; stylefirst.SetFont(fontfirst); //stylefirst.BorderBottom = CellBorderType.THIN; //stylefirst.BorderLeft = CellBorderType.THIN; //stylefirst.BorderRight = CellBorderType.THIN; //stylefirst.BorderTop = CellBorderType.THIN; // rowHead.Height = 1 * 256; Row rowfirst = sheet.CreateRow(1); rowfirst.CreateCell(0).SetCellValue(""); Cell celfirst = rowfirst.CreateCell(1); DateTime dateTiem = DateTime.Now.Date.AddDays(-7); string dateBegin = dateTiem.Year.ToString() + "-" + dateTiem.Month.ToString() + "-" + dateTiem.Day.ToString(); DateTime dateEnd = DateTime.Now.AddDays(-1); string date = dateEnd.Year.ToString() + "-" + dateEnd.Month.ToString() + "-" + dateEnd.Day.ToString(); celfirst.SetCellValue(dateBegin + " 00:00——" + date + " 24:00"); celfirst.CellStyle = stylefirst; rowfirst.CreateCell(2).SetCellValue(""); Cell cellTitle3 = rowfirst.CreateCell(3); sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2)); NPOI.SS.UserModel.CellStyle styleTitle = hssWorkBook.CreateCellStyle(); styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; Font fontTitle = hssWorkBook.CreateFont(); fontTitle.Boldweight = 700; styleTitle.SetFont(fontTitle); styleTitle.BorderBottom = CellBorderType.THIN; styleTitle.BorderLeft = CellBorderType.THIN; styleTitle.BorderRight = CellBorderType.THIN; styleTitle.BorderTop = CellBorderType.THIN; // cellTitle3.CellStyle = styleTitle; Row row = sheet.CreateRow(2); Cell cell0 = row.CreateCell(0); cell0.SetCellValue("序号"); cell0.CellStyle = styleTitle; Cell cell1 = row.CreateCell(1); cell1.SetCellValue("顾客ID"); cell1.CellStyle = styleTitle; Cell cell2 = row.CreateCell(2); cell2.SetCellValue("电子邮件"); cell2.CellStyle = styleTitle; Cell cell3 = row.CreateCell(3); cell3.SetCellValue("状态"); cell3.CellStyle = styleTitle; sheet.SetColumnWidth(0, 5 * 256); sheet.SetColumnWidth(1, 20 * 256); sheet.SetColumnWidth(2, 50 * 256); sheet.SetColumnWidth(3, 5 * 256); }
/*private void DrawBorders(TableCellBorder borders) * { * var style = Style; * style.Borders = borders; * SetStyle(style); * }*/ public void AddCell(int colSpan = 0, int rowSpan = 0) { if (CurrentRow == null) { AddRow(); //CurrentRow = Sheet.CreateRow(CurrentRowIndex); } CurrentColIndex = NextColIndex++; CurrentCell = CurrentRow.GetCell(CurrentColIndex) ?? CurrentRow.CreateCell(CurrentColIndex); var regionColIndex = CurrentColIndex + (colSpan > 1 ? colSpan - 1 : 0); var regionRowIndex = CurrentRowIndex + (rowSpan > 1 ? rowSpan - 1 : 0); CellRangeAddress region = null; if (colSpan > 1 || rowSpan > 1) { region = new CellRangeAddress(CurrentRowIndex, regionRowIndex, CurrentColIndex, regionColIndex); Sheet.AddMergedRegion(region); if (colSpan > 1) { NextColIndex += colSpan - 1; } if (rowSpan > 1) { NextRowIndex = Math.Max(NextRowIndex, regionRowIndex + 1); } } // Style the cell with borders all around. // if (Style.HasValues()/* || ((int)Borders) != 0*/) // (BorderTop ?? false) || (BorderLeft ?? false) || (BorderRight ?? false) || (BorderBottom ?? false)) { //var style = Workbook.CreateCellStyle(); var style = GetStyle(Style); if (style == null) { style = region == null?CreateCellStyle(CurrentCell) : CreateCellStyle(region); SetStyle(style, Style); } if (region == null) { SetCellStyle(CurrentCell, style); } else { SetCellStyle(region, style); } /* * if (Borders.HasFlag(TableCellBorder.Top)/* ?? false♥1♥) * { * style.BorderTop = CellBorderType.THIN; * style.TopBorderColor = HSSFColor.BLACK.index; * } * if (Borders.HasFlag(TableCellBorder.Left)) * { * style.BorderLeft = CellBorderType.THIN; * style.LeftBorderColor = HSSFColor.BLACK.index; * } * if (Borders.HasFlag(TableCellBorder.Right)) * { * style.BorderRight = CellBorderType.THIN; * style.RightBorderColor = HSSFColor.BLACK.index; * } * if (Borders.HasFlag(TableCellBorder.Bottom)) * { * style.BorderBottom = CellBorderType.THIN; * style.BottomBorderColor = HSSFColor.BLACK.index; * } */ // style.WrapText = WrapText; // style.ShrinkToFit = ShrinkToFit; /*if (region == null) * CurrentCell.CellStyle = style; * else * { * for (int iRow = region.FirstRow; iRow <= region.LastRow; iRow++) * { * var row = Sheet.GetRow(iRow) ?? Sheet.CreateRow(iRow); * for (int iCol = region.FirstColumn; iCol <= region.LastColumn; iCol++) * { * var cell = row.GetCell(iCol) ?? row.CreateCell(iCol); * if (cell != null) cell.CellStyle = style; * } * } * }*/ } }
/// <summary> /// 放映表导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void 放映表ToolStripMenuItem_Click(object sender, EventArgs e) { //创建排片信息集合对象,用以保存信息 List <IMovieShowList.MovieShow> listMovie = new List <IMovieShowList.MovieShow>(); try { if (newExcel) { ExcelSource ex = new ExcelSource(); listMovie = ex.GetList4Excel(newFileName); if (!ex.isOk) { MessageBox.Show(ex.Msg); return; } } else { //排片信息读取 listMovie = movieList; } if (listMovie.Count == 0) { MessageBox.Show("没有排片信息"); return; } } catch { MessageBox.Show("未知错误,请重试"); return; } MovieEndTime end = new MovieEndTime(); listMovie = end.GetMovieEndTimeList(listMovie); string headeValue = string.Empty; //是否Excel源或Api源 bool ok = IsExcelOrApi(out headeValue, listMovie); if (!ok) { return; } //让排片信息以厅号来排序 var iList = listMovie.OrderBy(i => i.Room[0]); //提取所有厅号 var a = from room in iList select room.Room; //去重之后放映厅的集合 List <string> RoomInfo = a.Distinct().ToList(); //创建放映表 hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("放映表"); sheet.PrintSetup.Landscape = true;//设置为横向 //设定列宽 for (int i = 0; i < 12; i++) { if ((i + 1) % 3 == 0) { //将包含电影名称的列设置列宽 sheet.SetColumnWidth(i, 23 * 256 + 200); } else { //其它的列设置列宽 sheet.SetColumnWidth(i, 6 * 256 + 200); } } //创建第一行 CreateR1(sheet, 0, 0, 0, 11, headeValue); //行标识从第2行开始 int roomCout = 1; Dictionary <string, int> roomIndex = new Dictionary <string, int>(); //创建当前行 Row row = sheet.CreateRow(roomCout); row.HeightInPoints = 30; int j = -1; //保存厅号的位置信息 List <Cell> listCell = new List <Cell>(); //创建其它字段 for (int i = 0; i < RoomInfo.Count; i++) { //创建第四个厅的时候转折 if ((i + 1) % 5 == 0) { //并且前面空余8格 roomCout += 9; row = sheet.CreateRow(roomCout); row.HeightInPoints = 30; j = -1; } j++; //创建单元格 //创建字体样式 NPOI.SS.UserModel.Font fontHeader = hssfworkbook.CreateFont(); fontHeader.FontHeightInPoints = 12; fontHeader.Boldweight = 700; int count = j * 3; Cell cell = row.CreateCell(count); cell.SetCellValue(RoomInfo[i]); cell.CellStyle = SetCellAllCenterBorder(); cell.CellStyle.SetFont(fontHeader); //将后面的两个单元格设置出来 SetCell(row, count + 1, string.Empty); SetCell(row, count + 2, string.Empty); //进行单元格合并 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(roomCout, roomCout, count, count + 2)); listCell.Add(cell); } //将影片信息按厅排列 List <IMovieShowList.MovieShow> li = listMovie.OrderBy(x => x.Room[0]).ToList <IMovieShowList.MovieShow>(); int rowIndex = 0; int indexCol; foreach (var item in listCell) { //当前厅名 string room = item.StringCellValue; //找到当前厅的所有子集并且进行排序 List <IMovieShowList.MovieShow> s = (from i in li where i.Room == room select i).OrderBy(x => x.BeginTime).ToList <IMovieShowList.MovieShow>(); //如果rowIndex与上次一致,则不创建行,直接在原有的行上创建单元格 //保存之前的rowIndex; rowIndex = item.RowIndex + 1; //3 indexCol = item.ColumnIndex; //0 //循环子集添加 foreach (var movie in s) { //获得下一行 Row rowCell = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); //设置行高 rowCell.HeightInPoints = 20; //在行下面创建三个单元格填充值 SetCell(rowCell, indexCol, movie.BeginTime); SetCell(rowCell, indexCol + 1, movie.EndTime); //单独处理电影名称,让其左对齐 //SetCell(rowCell, indexCol+2, movie.Name); Cell cellName = rowCell.CreateCell(indexCol + 2); cellName.CellStyle = cellLeftAllBorder(); cellName.SetCellValue(movie.Name); rowIndex++; } } //打开一个保存对话框,让用户保存数据 SaveFileDialog save = new SaveFileDialog(); save.Filter = "excel|*.xls"; save.FileName = headeValue + "--放映 "; DialogResult res = save.ShowDialog(); if (res == DialogResult.OK) { string path = save.FileName; try { FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write); hssfworkbook.Write(file); file.Close(); MessageBox.Show("保存成功"); } catch { MessageBox.Show("文件已打开,请先关闭文件"); return; } } else { return; } }
/// <summary> /// Método genérico para imprimir la tabla multiescenario /// </summary> /// <param name="sheet">Hoja donde se escribirá la tabla</param> /// <param name="row_ini">Fila inicial</param> /// <param name="col_ini">Columna inicial</param> /// <param name="nombreTabla">Título de la tabla</param> private void ImprimirTabla(Sheet sheet, int row_ini, int col_ini, string nombreTabla) { Cell cell = sheet.CreateRow(row_ini + 1).CreateCell(col_ini + 2); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Bueno"); cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 3); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Normal"); cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 4); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Malo"); cell = sheet.CreateRow(row_ini + 2).CreateCell(col_ini + 1); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Bueno"); cell = sheet.CreateRow(row_ini + 3).CreateCell(col_ini + 1); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Normal"); cell = sheet.CreateRow(row_ini + 4).CreateCell(col_ini + 1); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Malo"); cell = sheet.CreateRow(row_ini).CreateCell(col_ini + 2); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Escenarios Mantto"); cell = sheet.GetRow(row_ini).CreateCell(col_ini + 3); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell = sheet.GetRow(row_ini).CreateCell(col_ini + 4); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); sheet.AddMergedRegion(new CellRangeAddress(row_ini, row_ini, col_ini + 2, col_ini + 4)); cell = sheet.GetRow(row_ini + 2).CreateCell(col_ini); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue("Escenarios WXS"); cell = sheet.GetRow(row_ini + 3).CreateCell(col_ini); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell = sheet.GetRow(row_ini + 4).CreateCell(col_ini); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); sheet.AddMergedRegion(new CellRangeAddress(row_ini + 2, row_ini + 4, col_ini, col_ini)); cell = sheet.GetRow(row_ini).CreateCell(col_ini); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell.SetCellType(CellType.STRING); cell.SetCellValue(nombreTabla); cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell = sheet.GetRow(row_ini + 1).CreateCell(col_ini + 1); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); cell = sheet.GetRow(row_ini).CreateCell(col_ini + 1); cell.CellStyle = GetEstilo(EstilosTexto.EncabezadoColumnas); sheet.AddMergedRegion(new CellRangeAddress(row_ini, row_ini + 1, col_ini, col_ini + 1)); }
/** * Copy the cells from srcRow to this row * If this row is not a blank row, this will merge the two rows, overwriting * the cells in this row with the cells in srcRow * If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy * srcRow may be from a different sheet in the same workbook * @param srcRow the rows to copy from * @param policy the policy to determine what gets copied */ public void CopyRowFrom(IRow srcRow, CellCopyPolicy policy) { if (srcRow == null) { // srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy foreach (ICell destCell in this) { XSSFCell srcCell = null; // FIXME: remove type casting when copyCellFrom(Cell, CellCopyPolicy) is added to Cell interface ((XSSFCell)destCell).CopyCellFrom(srcCell, policy); } if (policy.IsCopyMergedRegions) { // Remove MergedRegions in dest row int destRowNum = RowNum; int index = 0; HashSet <int> indices = new HashSet <int>(); foreach (CellRangeAddress destRegion in Sheet.MergedRegions) { if (destRowNum == destRegion.FirstRow && destRowNum == destRegion.LastRow) { indices.Add(index); } index++; } (Sheet as XSSFSheet).RemoveMergedRegions(indices.ToList()); } if (policy.IsCopyRowHeight) { // clear row height Height = ((short)-1); } } else { foreach (ICell c in srcRow) { XSSFCell srcCell = (XSSFCell)c; XSSFCell destCell = CreateCell(srcCell.ColumnIndex, srcCell.CellType) as XSSFCell; destCell.CopyCellFrom(srcCell, policy); } XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet); int sheetIndex = _sheet.Workbook.GetSheetIndex(_sheet); String sheetName = _sheet.Workbook.GetSheetName(sheetIndex); int srcRowNum = srcRow.RowNum; int destRowNum = RowNum; int rowDifference = destRowNum - srcRowNum; FormulaShifter shifter = FormulaShifter.CreateForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007); rowShifter.UpdateRowFormulas(this, shifter); // Copy merged regions that are fully contained on the row // FIXME: is this something that rowShifter could be doing? if (policy.IsCopyMergedRegions) { foreach (CellRangeAddress srcRegion in srcRow.Sheet.MergedRegions) { if (srcRowNum == srcRegion.FirstRow && srcRowNum == srcRegion.LastRow) { CellRangeAddress destRegion = srcRegion.Copy(); destRegion.FirstRow = (destRowNum); destRegion.LastRow = (destRowNum); Sheet.AddMergedRegion(destRegion); } } } if (policy.IsCopyRowHeight) { Height = (srcRow.Height); } } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion CellStyle dateStyle = workbook.CreateCellStyle(); DataFormat 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(); } #region 表头及样式 { Row headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); CellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; Font 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)); //headerRow.Dispose(); } #endregion #region 列头及样式 { Row headerRow = sheet.CreateRow(1); CellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; Font 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(); } #endregion rowIndex = 2; } #endregion #region 填充内容 Row dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { Cell 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(); return(ms); } }