public void TestWriteModifySheetMerged() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); for (int rownum = 0; rownum < 100; rownum++) { IRow r = s.CreateRow(rownum); for (int cellnum = 0; cellnum < 50; cellnum += 2) { ICell c = r.CreateCell(cellnum); c.SetCellValue(rownum * 10000 + cellnum + (((double)rownum / 1000) + ((double)cellnum / 10000))); c = r.CreateCell(cellnum + 1); c.SetCellValue(new HSSFRichTextString("TEST")); } } s.AddMergedRegion(new CellRangeAddress(0, 10, 0, 10)); s.AddMergedRegion(new CellRangeAddress(30, 40, 5, 15)); sanityChecker.CheckHSSFWorkbook(wb); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); s = wb.GetSheetAt(0); CellRangeAddress r1 = s.GetMergedRegion(0); CellRangeAddress r2 = s.GetMergedRegion(1); ConfirmRegion(new CellRangeAddress(0, 10, 0, 10), r1); ConfirmRegion(new CellRangeAddress(30, 40, 5, 15), r2); }
/// <summary> /// 合并单元格 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="sheet">工作表</param> /// <param name="columnIndex">列索引</param> /// <param name="options">导出选项配置</param> private void MergeCells <T>(NPOI.SS.UserModel.ISheet sheet, int columnIndex, IExportOptions <T> options) where T : class, new() { string currentCellValue; var startRowIndex = options.DataRowStartIndex; NPOI.SS.Util.CellRangeAddress mergeRangeAddress; var startRow = sheet.GetRow(startRowIndex); if (startRow == null) { return; } var startCell = startRow.GetCell(columnIndex); if (startCell == null) { return; } string startCellValue = startCell.StringCellValue; if (string.IsNullOrWhiteSpace(startCellValue)) { return; } for (var rowIndex = options.DataRowStartIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++) { var cell = sheet.GetRow(rowIndex)?.GetCell(columnIndex); currentCellValue = cell == null ? string.Empty : cell.StringCellValue; if (currentCellValue.Trim() != startCellValue.Trim()) { mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, columnIndex, columnIndex); if (mergeRangeAddress.NumberOfCells > 1) { sheet.AddMergedRegion(mergeRangeAddress); startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center; } startRowIndex = rowIndex; startCellValue = currentCellValue; } if (rowIndex == sheet.PhysicalNumberOfRows - 1 && startRowIndex != rowIndex) { mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex, columnIndex, columnIndex); sheet.AddMergedRegion(mergeRangeAddress); startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center; } } }
protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "客户编号", CellType.String, cellStyle); CreateCell(row, 1, "客户名称", CellType.String, cellStyle); CreateCell(row, 2, "类别", CellType.String, cellStyle); CreateCell(row, 3, "综合等级", CellType.String, cellStyle); CreateCell(row, 4, "需求量等级", CellType.String, cellStyle); CreateCell(row, 5, "活跃程度", CellType.String, cellStyle); CreateCell(row, 6, "忠诚度", CellType.String, cellStyle); CreateCell(row, 7, "主要产品", CellType.String, cellStyle); CreateCell(row, 8, "业务员", CellType.String, cellStyle); CreateCell(row, 9, "联系人", CellType.String, cellStyle); CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle); CreateCell(row, 11, "最近周预估", CellType.String, cellStyle); CreateCell(row, 12, "最近月预估", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.CompanyEntity model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.fullname); CreateCell(row, 2, model.type); CreateCell(row, 3, model.generallevel); CreateCell(row, 4, model.requiredlevel); CreateCell(row, 5, model.managestandard); CreateCell(row, 6, model.activelevel); CreateCell(row, 7, model.products); CreateCell(row, 8, model.salesman); CreateCell(row, 9, model.linkman); CreateCell(row, 10, model.currentlink); CreateCell(row, 11, model.currentweekestimate); CreateCell(row, 12, model.currentmonthestimate); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "通话记录表", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "记录单号", CellType.String, cellStyle); CreateCell(row, 1, "客户名称", CellType.String, cellStyle); CreateCell(row, 2, "联系人", CellType.String, cellStyle); CreateCell(row, 3, "移动电话", CellType.String, cellStyle); CreateCell(row, 4, "固定电话", CellType.String, cellStyle); CreateCell(row, 5, "客户等级", CellType.String, cellStyle); CreateCell(row, 6, "日期", CellType.String, cellStyle); CreateCell(row, 7, "沟通内容", CellType.String, cellStyle); CreateCell(row, 8, "品质要求", CellType.String, cellStyle); CreateCell(row, 9, "主要产品", CellType.String, cellStyle); CreateCell(row, 10, "估计周用量", CellType.String, cellStyle); CreateCell(row, 11, "估计月用量", CellType.String, cellStyle); CreateCell(row, 12, "地址", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.CallRecordsEntity model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.customer); CreateCell(row, 2, model.linkman); CreateCell(row, 3, model.mobile); CreateCell(row, 4, model.telephone); CreateCell(row, 5, model.customerlevel); CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd")); CreateCell(row, 7, model.communicatecontent); CreateCell(row, 8, model.requiredquantity); CreateCell(row, 9, model.products); CreateCell(row, 10, model.weekestimate); CreateCell(row, 11, model.monthestimate); CreateCell(row, 12, model.address); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
public FileResult OmitCheckCountExportExcel() { PatrolRouteStat_SW sw = new PatrolRouteStat_SW(); sw.orgNo = Request.Params["BYORGNO"]; sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUReportCls.getPatrolRouteStatModel(sw); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 10 * 256); IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("未巡统计表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位/姓名"); row.CreateCell(1).SetCellValue("应巡"); row.CreateCell(2).SetCellValue("已巡"); row.CreateCell(3).SetCellValue("未巡"); row.CreateCell(4).SetCellValue("完成率"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row.GetCell(3).CellStyle = getCellStyleHead(book); row.GetCell(4).CellStyle = getCellStyleHead(book); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4)); int rowI = 0; foreach (var v in list) { row = sheet1.CreateRow(rowI + 2); row.CreateCell(0).SetCellValue(v.ORGName); row.CreateCell(1).SetCellValue(v.PointCount); row.CreateCell(2).SetCellValue(v.PointCount0); row.CreateCell(3).SetCellValue(v.PointCount1); row.CreateCell(4).SetCellValue(v.PointCount2); rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow) { //int datastartrow = 0; if (!string.IsNullOrEmpty(datadesc)) { NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow); for (int i = 0; i < 10; i++) { desc.CreateCell(i); } NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9); sheet1.AddMergedRegion(address); sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc); datastartrow++; } //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow); int tick = 0; foreach (string value in columnInfo.Values) { row1.CreateCell(tick).SetCellValue(value); tick++; } //将数据逐步写入sheet1各个行 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1); int index = 0; foreach (string key in columnInfo.Keys) { bool isnumberic = false; if (key.ToLower() == "rowid") { rowtemp.CreateCell(index).SetCellValue(i + 1); } else { string data = GetValue(dt.Rows[i], key, out isnumberic); if (isnumberic && data != "--") { rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data)); } else { rowtemp.CreateCell(index).SetCellValue(data); } } index++; } } }
/// <summary> /// 合并单元格 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sheet"></param> /// <param name="options"></param> private void CellRangeAddress <T>(NPOI.SS.UserModel.ISheet sheet, IExportOptions <T> options) where T : class, new() { //if (options.HeaderRow.All(x => x.Cells.All(m => m.ColumnSpan == 1))) // return; //var rows = options.HeaderRow.Where(x => x.Cells.Any(m => m.ColumnSpan > 1)).ToList(); var rows = options.HeaderRow.Where(x => x.Cells.Any(t => t.RowSpan > 1 || t.ColumnSpan > 1)).ToList(); if (rows.Any()) { rows.OrderBy(x => x.RowIndex).ForEach(row => { row.Cells.OrderBy(x => x.ColumnIndex).ForEach(x => { if (x.RowSpan <= 1 && x.ColumnSpan <= 1) { return; } var region = new CellRangeAddress(x.RowIndex, x.EndRowIndex, x.ColumnIndex, x.EndColumnIndex); sheet.AddMergedRegion(region); }); }); } }
protected static MemoryStream GetMS(List <FishEntity.ProductEntity> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); #region head row NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICellStyle style = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); row.HeightInPoints = 26; CreateCell(row, 0, "报盘", CellType.String, style); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 20)); row = sheet.CreateRow(1); style = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Normal); CreateCell(row, 0, "供应商", CellType.String, style); CreateCell(row, 1, "联系人", CellType.String, style); CreateCell(row, 2, "品质", CellType.String, style); CreateCell(row, 3, "鱼粉ID", CellType.String, style); CreateCell(row, 4, "状态", CellType.String, style); CreateCell(row, 5, "年份", CellType.String, style); CreateCell(row, 6, "装货时间", CellType.String, style); CreateCell(row, 7, "蛋白", CellType.String, style); CreateCell(row, 8, "TVN", CellType.String, style); CreateCell(row, 9, "灰份", CellType.String, style); CreateCell(row, 10, "沙盐", CellType.String, style); CreateCell(row, 11, "组胺", CellType.String, style); CreateCell(row, 12, "FFA", CellType.String, style); CreateCell(row, 13, "脂肪", CellType.String, style); CreateCell(row, 14, "水份", CellType.String, style); CreateCell(row, 15, "沙", CellType.String, style); CreateCell(row, 16, "数量/吨", CellType.String, style); CreateCell(row, 17, "备注", CellType.String, style); CreateCell(row, 18, "品牌", CellType.String, style); CreateCell(row, 19, "最近报价", CellType.String, style); #endregion if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.ProductEntity model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.supplier); CreateCell(row, 1, model.linkman); CreateCell(row, 2, model.quality); CreateCell(row, 3, model.code); CreateCell(row, 4, model.statename); CreateCell(row, 5, ""); CreateCell(row, 6, ""); CreateCell(row, 7, model.quote_protein.ToString()); CreateCell(row, 8, model.quote_tvn.ToString()); CreateCell(row, 9, model.quote_graypart.ToString()); CreateCell(row, 10, model.quote_sandsalt.ToString()); CreateCell(row, 11, model.quote_amine.ToString()); CreateCell(row, 12, model.quote_ffa.ToString()); CreateCell(row, 13, model.quote_fat.ToString()); CreateCell(row, 14, model.quote_water.ToString()); CreateCell(row, 15, model.quote_sand.ToString()); CreateCell(row, 16, ""); CreateCell(row, 17, model.remark); CreateCell(row, 18, model.brand); CreateCell(row, 19, model.latestquote.ToString()); #endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList) { var first = orderItemList.FirstOrDefault(); //创建表格 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(); #region 定义样式 //创建字体 var cellStyleFont = (HSSFFont)book.CreateFont(); cellStyleFont.IsBold = true; cellStyleFont.FontName = "宋体"; cellStyleFont.FontHeightInPoints = 10; //RGB自定义背景色 HSSFPalette palette = book.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220); // titleStyle HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle(); //(自定义背景色)单元格背景颜色 和FillPattern必须一起设置 titleStyle.FillForegroundColor = HSSFColor.Pink.Index; titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 titleStyle.BorderBottom = BorderStyle.Thin; titleStyle.BorderLeft = BorderStyle.Thin; titleStyle.BorderRight = BorderStyle.Thin; titleStyle.BorderTop = BorderStyle.Thin; titleStyle.SetFont(cellStyleFont);//设置字体 //headStyle HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; //水平居中 headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; headStyle.SetFont(cellStyleFont); //borderStyle HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle(); borderStyle.BorderBottom = BorderStyle.Thin; borderStyle.BorderLeft = BorderStyle.Thin; borderStyle.BorderRight = BorderStyle.Thin; borderStyle.BorderTop = BorderStyle.Thin; #endregion #region 表头 //设置单元格宽度 sheet.SetColumnWidth(0, 5000); //采购日期 sheet.SetColumnWidth(1, 5000); //门店名称 sheet.SetColumnWidth(2, 14000); //门店地址 sheet.SetColumnWidth(3, 4500); //采购数量 sheet.SetColumnWidth(4, 4500); //操作人 //表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(4); row.Height = 350; row.CreateCell(0).SetCellValue("采购日期"); row.CreateCell(1).SetCellValue("门店名称"); row.CreateCell(2).SetCellValue("门店地址"); row.CreateCell(3).SetCellValue("采购数量"); row.CreateCell(4).SetCellValue("操作人"); for (int i = 0; i < 5; i++) { row.Cells[i].CellStyle = titleStyle; } //合并单元格 CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4); sheet.AddMergedRegion(region0); IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("冷链食品处理详情"); row0.Cells[0].CellStyle = headStyle; var row0Cell4 = row0.CreateCell(4); row0Cell4.CellStyle = headStyle; IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue("处理商品批次码"); row1.CreateCell(1).SetCellValue(first.BatchNo.ToString()); row1.Cells[0].CellStyle = titleStyle; IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue("处理日期"); row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd")); row2.Cells[0].CellStyle = titleStyle; IRow row3 = sheet.CreateRow(3); row3.CreateCell(0).SetCellValue("处理人"); row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString()); row3.Cells[0].CellStyle = titleStyle; //设置黑色边框 for (int i = 1; i < 4; i++) { var iRow = sheet.GetRow(i); for (int j = 1; j < 5; j++) { var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j); jCell.CellStyle = borderStyle; } } #endregion #region 循环数据 int index = 5; foreach (var item in orderItemList) { IRow itemRow = sheet.CreateRow(index); var rowCell0 = itemRow.CreateCell(0); rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd")); rowCell0.CellStyle = borderStyle; var rowCell1 = itemRow.CreateCell(1); rowCell1.SetCellValue(item.TargetStoreName.ToString()); rowCell1.CellStyle = borderStyle; var rowCell2 = itemRow.CreateCell(2); rowCell2.SetCellValue(item.TargetStoreAddress.ToString()); rowCell2.CellStyle = borderStyle; var rowCell3 = itemRow.CreateCell(3); rowCell3.SetCellValue(item.Number?.ToString()); rowCell3.CellStyle = borderStyle; var rowCell4 = itemRow.CreateCell(4); rowCell4.SetCellValue(item.ImportUserNames.ToString()); rowCell4.CellStyle = borderStyle; index++; } #endregion MemoryStream ms = new MemoryStream(); book.Write(ms); return(ms); }
/// <summary> /// 合并单元格 /// </summary> /// <param name="r1">左上角单元格行标(从0开始,下同)</param> /// <param name="c1">左上角单元格列标</param> /// <param name="r2">右下角单元格行标</param> /// <param name="c2">右下角单元格列标</param> public void Merge(int r1, int c1, int r2, int c2) { osheet.AddMergedRegion(new CellRangeAddress(r1, c1, r2, c2)); }
protected static MemoryStream GetMS(List <FishEntity.SelfStorageFlowingReportVo> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "自营自制库存流水账", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 18)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "鱼粉ID", CellType.String, cellStyle); CreateCell(row, 1, "品名", CellType.String, cellStyle); CreateCell(row, 2, "状态", CellType.String, cellStyle); CreateCell(row, 3, "单据类型", CellType.String, cellStyle); CreateCell(row, 4, "单据号码", CellType.String, cellStyle); CreateCell(row, 5, "日期", CellType.String, cellStyle); CreateCell(row, 6, "出/入库存", CellType.String, cellStyle); CreateCell(row, 7, "重量(吨)", CellType.String, cellStyle); CreateCell(row, 8, "数量(包)", CellType.String, cellStyle); CreateCell(row, 9, "国别", CellType.String, cellStyle); CreateCell(row, 10, "工艺分类", CellType.String, cellStyle); CreateCell(row, 11, "品质规格", CellType.String, cellStyle); CreateCell(row, 12, "所属货主", CellType.String, cellStyle); CreateCell(row, 13, "到港时间", CellType.String, cellStyle); CreateCell(row, 14, "代理开证公司", CellType.String, cellStyle); CreateCell(row, 15, "货代报关公司", CellType.String, cellStyle); CreateCell(row, 16, "SGS(蛋白)", CellType.String, cellStyle); CreateCell(row, 17, "SGS(TVN)", CellType.String, cellStyle); CreateCell(row, 18, "组胺", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.SelfStorageFlowingReportVo model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.productcode); CreateCell(row, 1, model.productname); CreateCell(row, 2, model.statename); CreateCell(row, 3, model.billtype); CreateCell(row, 4, model.billcode); CreateCell(row, 5, model.date.ToString("yyyy-MM-dd")); CreateCell(row, 6, model.storagetype); CreateCell(row, 7, model.weight.ToString()); CreateCell(row, 8, model.package.ToString()); CreateCell(row, 9, model.nature); CreateCell(row, 10, model.techtype); CreateCell(row, 11, model.specification); CreateCell(row, 12, model.ownername); CreateCell(row, 13, model.arriveportdate); CreateCell(row, 14, model.agentifcompany); CreateCell(row, 15, model.customsofcompany); CreateCell(row, 16, model.sgs_protein.ToString()); CreateCell(row, 17, model.sgs_tvn.ToString()); CreateCell(row, 18, model.sgs_amine.ToString()); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
protected static MemoryStream GetMS(List <FishEntity.ProductConfirmVo> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); #region head row NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICellStyle style = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); row.HeightInPoints = 26; CreateCell(row, 0, "确盘", CellType.String, style); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 28)); row = sheet.CreateRow(1); style = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Normal); CreateCell(row, 0, "鱼粉ID", CellType.String, style); CreateCell(row, 1, "报盘单位", CellType.String, style); CreateCell(row, 2, "联系人", CellType.String, style); CreateCell(row, 3, "国别", CellType.String, style); CreateCell(row, 4, "品质", CellType.String, style); CreateCell(row, 5, "最新报盘日期", CellType.String, style); CreateCell(row, 6, "船期最快", CellType.String, style); CreateCell(row, 7, "船期最晚", CellType.String, style); CreateCell(row, 8, "蛋白", CellType.String, style); CreateCell(row, 9, "TVN", CellType.String, style); CreateCell(row, 10, "组胺", CellType.String, style); CreateCell(row, 11, "FFA", CellType.String, style); CreateCell(row, 12, "盐和砂", CellType.String, style); CreateCell(row, 13, "灰份", CellType.String, style); CreateCell(row, 14, "备注", CellType.String, style); CreateCell(row, 15, "开证单位", CellType.String, style); CreateCell(row, 16, "联系人", CellType.String, style); CreateCell(row, 17, "SGS重量", CellType.String, style); CreateCell(row, 18, "品牌", CellType.String, style); CreateCell(row, 19, "船期", CellType.String, style); CreateCell(row, 20, "蛋白", CellType.String, style); CreateCell(row, 21, "TVN", CellType.String, style); CreateCell(row, 22, "组胺", CellType.String, style); CreateCell(row, 23, "FFA", CellType.String, style); CreateCell(row, 24, "盐和砂", CellType.String, style); CreateCell(row, 25, "灰份", CellType.String, style); CreateCell(row, 26, "日期", CellType.String, style); CreateCell(row, 27, "美元价", CellType.String, style); CreateCell(row, 28, "人民币价", CellType.String, style); #endregion if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.ProductConfirmVo model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.quotesupplier); CreateCell(row, 2, model.quotelinkman); CreateCell(row, 3, model.nature); CreateCell(row, 4, model.specification); CreateCell(row, 5, model.quotedate); CreateCell(row, 6, model.quotesaildatefast); CreateCell(row, 7, model.quotesaildatelate); CreateCell(row, 8, model.quote_protein.ToString()); CreateCell(row, 9, model.quote_tvn.ToString()); CreateCell(row, 10, model.quote_amine.ToString()); CreateCell(row, 11, model.quote_ffa.ToString()); CreateCell(row, 12, model.quote_sandsalt.ToString()); CreateCell(row, 13, model.quote_graypart.ToString()); CreateCell(row, 14, model.remark); CreateCell(row, 15, model.confirmagent); CreateCell(row, 16, model.confirmlinkman); CreateCell(row, 17, model.confirmsgsweight.ToString("f2")); CreateCell(row, 18, model.brand); CreateCell(row, 19, model.confirmsaildate); CreateCell(row, 20, model.sgs_protein.ToString()); CreateCell(row, 21, model.sgs_tvn.ToString()); CreateCell(row, 22, model.sgs_amine.ToString()); CreateCell(row, 23, model.sgs_ffa.ToString()); CreateCell(row, 24, model.sgs_sandsalt.ToString()); CreateCell(row, 25, model.sgs_graypart.ToString()); CreateCell(row, 26, model.confirmdate); CreateCell(row, 27, model.confirmdollars.ToString("f2")); CreateCell(row, 28, model.confirmrmb.ToString("f2")); #endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
/// <summary> /// 将DataTable导出到Excel /// </summary> /// <param name="dt">DataTable</param> /// <param name="fileName">仅文件名(非路径)</param> /// <returns>返回Excel文件绝对路径</returns> public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response) { #region 表头 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName); hssfSheet.DefaultColumnWidth = 10; hssfSheet.SetColumnWidth(0, 10 * 256); hssfSheet.SetColumnWidth(3, 10 * 256); // 表头 NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0); tagRow0.Height = 40 * 40; ICell cell0 = tagRow0.CreateCell(0); //设置单元格内容 cell0.SetCellValue("力诺瑞特制造工厂"); hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13)); NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1); tagRow1.Height = 30 * 20; ICell cell1 = tagRow1.CreateCell(0); //设置单元格内容 cell1.SetCellValue("反冲材料补料单"); hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 13)); NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle(); tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle.VerticalAlignment = VerticalAlignment.Center; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 16; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "宋体"; tagStyle.SetFont(font);//HEAD 样式 cell0.CellStyle = tagStyle; NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle(); tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle1.VerticalAlignment = VerticalAlignment.Center; cell1.CellStyle = tagStyle1; // 标题样式 NPOI.SS.UserModel.ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle(); cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle1.VerticalAlignment = VerticalAlignment.Center; cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.None; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.None; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.None; cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.None; #endregion //数据样式 NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; #region 表数据 // 表数据 for (int k = 0; k < 2; k++) { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < 2; i += 2) { row.CreateCell(i).SetCellValue(dr[0].ToString()); row.GetCell(i).CellStyle = cellStyle1; } for (int i = 2; i < 5; i += 3) { row.CreateCell(i).SetCellValue(dr[1].ToString()); row.GetCell(i).CellStyle = cellStyle1; } for (int i = 5; i < 7; i += 2) { row.CreateCell(i).SetCellValue(dr[2].ToString()); row.GetCell(i).CellStyle = cellStyle1; } for (int i = 7; i < 9; i += 2) { row.CreateCell(i).SetCellValue(dr[3].ToString()); row.GetCell(i).CellStyle = cellStyle1; } for (int i = 9; i < 11; i += 2) { row.CreateCell(i).SetCellValue(dr[4].ToString()); row.GetCell(i).CellStyle = cellStyle1; } for (int i = 11; i < 14; i += 3) { row.CreateCell(i).SetCellValue(dr[5].ToString()); row.GetCell(i).CellStyle = cellStyle1; } row.CreateCell(1).SetCellValue(""); row.GetCell(1).CellStyle = cellStyle1; row.CreateCell(3).SetCellValue(""); row.GetCell(3).CellStyle = cellStyle1; row.CreateCell(4).SetCellValue(""); row.GetCell(4).CellStyle = cellStyle1; row.CreateCell(6).SetCellValue(""); row.GetCell(6).CellStyle = cellStyle1; row.CreateCell(8).SetCellValue(""); row.GetCell(8).CellStyle = cellStyle1; row.CreateCell(10).SetCellValue(""); row.GetCell(10).CellStyle = cellStyle1; row.CreateCell(12).SetCellValue(""); row.GetCell(12).CellStyle = cellStyle1; row.CreateCell(13).SetCellValue(""); row.GetCell(13).CellStyle = cellStyle1; hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 4)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 5, 6)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 7, 8)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 9, 10)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 11, 13)); } // 表数据 for (int k = 2; k < dt.Rows.Count; k++) { if (k == dt.Rows.Count - 1) { DataRow drlast = dt.Rows[k]; NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2); rowlast.Height = 30 * 20; for (int i = 0; i < 2; i++) { rowlast.CreateCell(i).SetCellValue(drlast[0].ToString()); rowlast.GetCell(i).CellStyle = cellStyle1; } for (int i = 2; i < 4; i++) { rowlast.CreateCell(i).SetCellValue(""); rowlast.GetCell(i).CellStyle = cellStyle1; } for (int i = 4; i < 6; i++) { rowlast.CreateCell(i).SetCellValue(drlast[2].ToString()); rowlast.GetCell(i).CellStyle = cellStyle1; } for (int i = 6; i < 8; i++) { rowlast.CreateCell(i).SetCellValue(""); rowlast.GetCell(i).CellStyle = cellStyle1; } for (int i = 8; i < 10; i++) { rowlast.CreateCell(i).SetCellValue(drlast[4].ToString()); rowlast.GetCell(i).CellStyle = cellStyle1; } for (int i = 10; i < 14; i++) { rowlast.CreateCell(i).SetCellValue(""); rowlast.GetCell(i).CellStyle = cellStyle1; } //hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 13)); } else { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < 1; i++) { row.CreateCell(i).SetCellValue(dr[i].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 1; i < 3; i++) { row.CreateCell(i).SetCellValue(dr[1].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 3; i < 6; i++) { row.CreateCell(i).SetCellValue(dr[2].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 6; i < 8; i++) { row.CreateCell(i).SetCellValue(dr[3].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 8; i < 10; i++) { row.CreateCell(i).SetCellValue(dr[4].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 10; i < 12; i++) { row.CreateCell(i).SetCellValue(dr[5].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 12; i < 14; i++) { row.CreateCell(i).SetCellValue(dr[6].ToString()); row.GetCell(i).CellStyle = cellStyle; } row.CreateCell(2).SetCellValue(""); row.GetCell(2).CellStyle = cellStyle; row.CreateCell(4).SetCellValue(""); row.GetCell(4).CellStyle = cellStyle; row.CreateCell(5).SetCellValue(""); row.GetCell(5).CellStyle = cellStyle; row.CreateCell(7).SetCellValue(""); row.GetCell(7).CellStyle = cellStyle; row.CreateCell(9).SetCellValue(""); row.GetCell(9).CellStyle = cellStyle; row.CreateCell(11).SetCellValue(""); row.GetCell(11).CellStyle = cellStyle; row.CreateCell(13).SetCellValue(""); row.GetCell(13).CellStyle = cellStyle; #region 合并单元格 hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 2)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 3, 5)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 11)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 12, 13)); #endregion } } #endregion hssfSheet.PrintSetup.NoColor = true; hssfSheet.PrintSetup.Landscape = true; hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4; //是否自适应界面 hssfSheet.FitToPage = true; string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/"; if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } try { FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath); //return (basePath + "Temp/" + fileName + ".xls"); string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径,可用相对路径 FileInfo fileInfo = new FileInfo(fileURL); Response.Clear(); using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ToString())) { SqlCommand cmd = new SqlCommand(); SqlTransaction transaction = null; try { conn.Open(); transaction = conn.BeginTransaction(); cmd.Transaction = transaction; cmd.Connection = conn; cmd.CommandType = CommandType.Text; string str1 = string.Empty; for (int i = 0; i < materialid.Length; i++) { str1 = "update MFG_WIP_BKF_MTL_Record set Status='3',ConfirmTime=GETDATE(),ConfirmUser='******',PrintTime=GETDATE() where ID=" + materialid[i]; cmd.CommandText = str1; cmd.ExecuteNonQuery(); } Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名 Response.AppendHeader("content-type", "application/x-msexcel"); Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小 Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.Default; Response.WriteFile(fileURL); Response.Flush(); Response.Close(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); Response.Write(ex.Message); } } } catch (Exception ex) { Response.Write(ex.Message); } }
/// <summary> /// 导出 /// </summary> /// <returns></returns> public FileResult OutRaiLExportExcel() { OutRaiLCount_SW sw = new OutRaiLCount_SW(); sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUCheckCls.getOutRaiLCountModel(sw); DateTime dt1 = Convert.ToDateTime(sw.DateBegin); DateTime dt2 = Convert.ToDateTime(sw.DateEnd); int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数 //vMenu.MENUNAME 页面/菜单名称 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); for (int i = 0; i < days; i++) { sheet1.SetColumnWidth(i + 2, 10 * 256); } IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("出围统计表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位/姓名"); row.CreateCell(1).SetCellValue("出围合计"); row.CreateCell(2).SetCellValue("日期(日)"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row = sheet1.CreateRow(2); for (int i = 0; i < days; i++) { DateTime tm = dt1.AddDays(i); row.CreateCell(i + 2).SetCellValue(tm.ToString("dd")); row.GetCell(i + 2).CellStyle = getCellStyleHead(book); } sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, days + 1)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, days - 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1)); int rowI = 0; foreach (var v in list) { row = sheet1.CreateRow(rowI + 3); row.CreateCell(0).SetCellValue(v.ORGName); row.CreateCell(1).SetCellValue(v.Count); string[] arr = v.DayCountList.Split(','); for (int i = 0; i < days; i++) { row.CreateCell(i + 2).SetCellValue(arr[i]); } rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "出围统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
/// <summary> /// 将DataTable导出到Excel /// </summary> /// <param name="dt">DataTable</param> /// <param name="fileName">仅文件名(非路径)</param> /// <returns>返回Excel文件绝对路径</returns> public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response) { int dtcolunmnum = dt.Columns.Count; #region 表头 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName); hssfSheet.DefaultColumnWidth = 13; //hssfSheet.SetColumnWidth(0, 20 * 256); //hssfSheet.SetColumnWidth(3, 20 * 256); // 表头 NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0); tagRow0.Height = 40 * 40; ICell cell0 = tagRow0.CreateCell(0); //设置单元格内容 cell0.SetCellValue("力诺瑞特制造工厂"); hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtcolunmnum - 1)); NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1); tagRow1.Height = 30 * 20; ICell cell1 = tagRow1.CreateCell(0); //设置单元格内容 cell1.SetCellValue(fileName); hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtcolunmnum - 1)); // 标题样式 NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle(); tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 16; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "宋体"; tagStyle.SetFont(font);//HEAD 样式 cell0.CellStyle = tagStyle; NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle(); tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle1.VerticalAlignment = VerticalAlignment.Center; cell1.CellStyle = tagStyle1; NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; #endregion // 表数据 for (int k = 0; k < dt.Rows.Count; k++) { DataRow drlast = dt.Rows[k]; NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2); rowlast.Height = 30 * 20; for (int i = 0; i < dt.Columns.Count; i++) { rowlast.CreateCell(i).SetCellValue(drlast[i].ToString()); rowlast.GetCell(i).CellStyle = cellStyle; } } // 表数据 //for (int k = 0; k < dt.Rows.Count; k++) //{ // hssfSheet.AutoSizeColumn(k); //} //获取当前列的宽度,然后对比本列的长度,取最大值 for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++) { int columnWidth = hssfSheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= hssfSheet.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (hssfSheet.GetRow(rowNum) == null) { currentRow = hssfSheet.CreateRow(rowNum); } else { currentRow = hssfSheet.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; } } } hssfSheet.SetColumnWidth(columnNum, columnWidth * 256); } hssfSheet.PrintSetup.NoColor = true; hssfSheet.PrintSetup.Landscape = true; hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4; //是否自适应界面 hssfSheet.FitToPage = true; //保存excel文件 string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Report/Temp/"; if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath); //return (basePath + "Temp/" + fileName + ".xls"); string fileURL = HttpContext.Current.Server.MapPath((basePath + "Report/Temp/" + fileName + ".xls"));//文件路径,可用相对路径 FileInfo fileInfo = new FileInfo(fileURL); Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名 Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小 Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.Default; Response.WriteFile(fileURL); }
public static void ExportExcel(string stuID) { List <Course> Courses = CourseService.GetCourses(stuID); //创建Excel工作薄 HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作表1和工作表2并命名 NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("列表模式"); NPOI.SS.UserModel.ISheet sheet2 = excelBook.CreateSheet("周历模式"); //列表模式(表1)添加数据 //创建表头行 CreateRow(0) NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("课头号"); row1.CreateCell(1).SetCellValue("课程名"); row1.CreateCell(2).SetCellValue("课程类型"); row1.CreateCell(3).SetCellValue("学习类型"); row1.CreateCell(4).SetCellValue("授课学院"); row1.CreateCell(5).SetCellValue("授课教师"); row1.CreateCell(6).SetCellValue("专业"); row1.CreateCell(7).SetCellValue("学分"); row1.CreateCell(8).SetCellValue("学时"); row1.CreateCell(9).SetCellValue("上课时间"); row1.CreateCell(10).SetCellValue("备注"); for (int i = 0; i < Courses.Count; i++) { NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i + 1); row.CreateCell(0).SetCellValue(Courses[i].LessonNum); row.CreateCell(1).SetCellValue(Courses[i].LessonName); row.CreateCell(2).SetCellValue(Courses[i].LessonType); row.CreateCell(3).SetCellValue(Courses[i].LearninType); row.CreateCell(4).SetCellValue(Courses[i].TeachingCollege); row.CreateCell(5).SetCellValue(Courses[i].Teacher); row.CreateCell(6).SetCellValue(Courses[i].Specialty); row.CreateCell(7).SetCellValue(Courses[i].Credit); row.CreateCell(8).SetCellValue(Courses[i].LessonHours); row.CreateCell(9).SetCellValue(Courses[i].Time); row.CreateCell(10).SetCellValue(Courses[i].Note); } //周历模式(表2)添加数据 NPOI.SS.UserModel.IRow firstRow = sheet2.CreateRow(0); firstRow.Height = 20 * 20; firstRow.CreateCell(0).SetCellValue("节次"); firstRow.CreateCell(1).SetCellValue("日"); firstRow.CreateCell(2).SetCellValue("一"); firstRow.CreateCell(3).SetCellValue("二"); firstRow.CreateCell(4).SetCellValue("三"); firstRow.CreateCell(5).SetCellValue("四"); firstRow.CreateCell(6).SetCellValue("五"); firstRow.CreateCell(7).SetCellValue("六"); List <IRow> rowList = new List <IRow>(); rowList.Add(firstRow); for (int i = 0; i < 13; i++) { NPOI.SS.UserModel.IRow row = sheet2.CreateRow(i + 1); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(i + 1); rowList.Add(row); } for (int i = 0; i < Courses.Count; i++) { Course course = Courses[i]; List <List <Object> > temp = CourseTime.ParseClassTime(course); for (int j = 0; j < temp.Count; j++) { string courseFirstWeek = ((int)temp[j][5]).ToString(); string courseLastWeek = ((int)temp[j][6]).ToString(); int courseBegin = (int)temp[j][2]; int courseEnd = (int)temp[j][3]; string weekDayByString = (string)temp[j][4]; int weekDay = CourseTime.WeekDayTrans(weekDayByString); ICellStyle style = excelBook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.WrapText = true; ICell cell = rowList[courseBegin].CreateCell(weekDay); cell.CellStyle = style; cell.SetCellValue($"{course.LessonName}\n{courseFirstWeek}-{courseLastWeek}周"); sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(courseBegin, courseEnd, weekDay, weekDay)); } } if (!Directory.Exists(CQ.Api.AppDirectory + @"Export")) { Directory.CreateDirectory(CQ.Api.AppDirectory + @"Export"); } using (FileStream fs = File.OpenWrite(CQ.Api.AppDirectory + $@"Export\{stuID}CourseTable.xls")) { excelBook.Write(fs); } }
/// <summary> /// 导出 /// </summary> /// <returns></returns> public FileResult HUCountExportExcel() { var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004001", SYSFLAG = ConfigCls.getSystemFlag() }); //vMenu.MENUNAME 页面/菜单名称 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 50 * 256); sheet1.SetColumnWidth(1, 10 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 10 * 256); sheet1.SetColumnWidth(5, 10 * 256); IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue(vMenu.MENUNAME); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位"); row.CreateCell(1).SetCellValue("总数"); row.CreateCell(2).SetCellValue("性别"); row.CreateCell(4).SetCellValue("固/兼职"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row.GetCell(4).CellStyle = getCellStyleHead(book); row = sheet1.CreateRow(2); row.CreateCell(2).SetCellValue("男"); row.CreateCell(3).SetCellValue("女"); row.CreateCell(4).SetCellValue("固职"); row.CreateCell(5).SetCellValue("兼职"); row.GetCell(2).CellStyle = getCellStyleHead(book); row.GetCell(3).CellStyle = getCellStyleHead(book); row.GetCell(4).CellStyle = getCellStyleHead(book); row.GetCell(5).CellStyle = getCellStyleHead(book); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, 3)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 4, 5)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1)); T_IPSFR_USER_SW sw = new T_IPSFR_USER_SW(); sw.TopORGNO = Request.Params["TopORGNO"]; var list = HUReportCls.getHUCountModel(sw); int rowI = 0; foreach (var item in list) { row = sheet1.CreateRow(rowI + 3); row.CreateCell(0).SetCellValue(item.ORGName); row.CreateCell(1).SetCellValue(item.HUCount); row.CreateCell(2).SetCellValue(item.Sex0Count); row.CreateCell(3).SetCellValue(item.Sex1Count); row.CreateCell(4).SetCellValue(item.Onstate0Count); row.CreateCell(5).SetCellValue(item.Onstate1Count); for (int i = 0; i < 6; i++) { if (i == 0) { row.GetCell(0).CellStyle = getCellStyleLeft(book); } else { row.GetCell(i).CellStyle = getCellStyleCenter(book); } } rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = vMenu.MENUNAME + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult PatrolRouteStatExportExcel() { //string BYORGNO = Request.Params["BYORGNO"]; //string TIMEBegin = Request.Params["TIMEBegin"]; //string TIMEEnd = Request.Params["TIMEEnd"]; //string HNamePhone = Request.Params["HNamePhone"]; //var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004002", SYSFLAG = ConfigCls.getSystemFlag() }); //vMenu.MENUNAME 页面/菜单名称 #region 导出统计表 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 20 * 256); sheet1.SetColumnWidth(5, 10 * 256); sheet1.SetColumnWidth(6, 10 * 256); sheet1.SetColumnWidth(7, 10 * 256); sheet1.SetColumnWidth(8, 20 * 256); IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("巡检路线统计总表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位"); row.CreateCell(1).SetCellValue("巡检次数(条)"); row.CreateCell(5).SetCellValue("巡检地点(个)"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(5).CellStyle = getCellStyleHead(book); row = sheet1.CreateRow(2); row.CreateCell(1).SetCellValue("总数"); row.CreateCell(2).SetCellValue("巡检"); row.CreateCell(3).SetCellValue("未巡检"); row.CreateCell(4).SetCellValue("巡检率(%)"); row.CreateCell(5).SetCellValue("总数"); row.CreateCell(6).SetCellValue("完成"); row.CreateCell(7).SetCellValue("未完成"); row.CreateCell(8).SetCellValue("完成率(%)"); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row.GetCell(3).CellStyle = getCellStyleHead(book); row.GetCell(4).CellStyle = getCellStyleHead(book); row.GetCell(5).CellStyle = getCellStyleHead(book); row.GetCell(6).CellStyle = getCellStyleHead(book); row.GetCell(7).CellStyle = getCellStyleHead(book); row.GetCell(8).CellStyle = getCellStyleHead(book); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 4)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 5, 8)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0)); //var list = HUReportCls.getPatrolRouteStatModel(new PatrolRouteStat_SW { DateBegin = TIMEBegin, DateEnd = TIMEEnd, orgNo = BYORGNO, TopORGNO = BYORGNO }); PatrolRouteStat_SW sw = new PatrolRouteStat_SW(); sw.orgNo = Request.Params["BYORGNO"]; sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUReportCls.getPatrolRouteStatModel(sw); int rowI = 0; foreach (var item in list) { row = sheet1.CreateRow(rowI + 3); row.CreateCell(0).SetCellValue(item.ORGName); row.CreateCell(1).SetCellValue(item.LineCount); row.CreateCell(2).SetCellValue(item.LineCount0); row.CreateCell(3).SetCellValue(item.LineCount1); row.CreateCell(4).SetCellValue(item.LineCount2); row.CreateCell(5).SetCellValue(item.PointCount); row.CreateCell(6).SetCellValue(item.PointCount0); row.CreateCell(7).SetCellValue(item.PointCount1); row.CreateCell(8).SetCellValue(item.PointCount2); for (int i = 0; i < 9; i++) { if (i == 0) { row.GetCell(0).CellStyle = getCellStyleLeft(book); } else { row.GetCell(i).CellStyle = getCellStyleCenter(book); } } rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "巡检路线统计总表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); #endregion }
public /*ActionResult*/ FileContentResult About() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(); #region 定义表头 //RGB自定义背景色 HSSFPalette palette = book.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146); HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle(); ///(自定义背景色)单元格背景颜色 和FillPattern必须一起设置 cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index; cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.Alignment = HorizontalAlignment.Center; //水平居中 cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 //设置黑色边框 cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; //创建字体 var cellStyleFont = (HSSFFont)book.CreateFont(); cellStyleFont.Color = HSSFColor.White.Index; cellStyleFont.IsBold = true; cellStyleFont.FontName = "宋体"; cellStyleFont.FontHeightInPoints = 11; cellStyle.SetFont(cellStyleFont); //设置单元格宽度 sheet.SetColumnWidth(0, 4000); //核销人 sheet.SetColumnWidth(1, 4000); //账号 sheet.SetColumnWidth(2, 5000); //核销时间 sheet.SetColumnWidth(3, 3500); sheet.SetColumnWidth(4, 3500); sheet.SetColumnWidth(5, 4000); sheet.SetColumnWidth(6, 7000); sheet.SetColumnWidth(7, 3500); //全面值 sheet.SetColumnWidth(8, 4500); //使用门槛 sheet.SetColumnWidth(9, 5000); //核销门店 sheet.SetColumnWidth(10, 10000); //appid sheet.SetColumnWidth(11, 3500); //核销地址 sheet.SetColumnWidth(12, 3500); //核销id // 第一行 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.Height = 350; row.CreateCell(0).SetCellValue("核销人"); row.CreateCell(1).SetCellValue("核销人账号"); row.CreateCell(2).SetCellValue("核销时间"); row.CreateCell(3).SetCellValue("订单金额"); row.CreateCell(4).SetCellValue("核销金额"); row.CreateCell(5).SetCellValue("核销券码"); row.CreateCell(6).SetCellValue("有效期"); row.CreateCell(7).SetCellValue("券面值"); row.CreateCell(8).SetCellValue("使用门槛"); row.CreateCell(9).SetCellValue("核销门店"); row.CreateCell(10).SetCellValue("核销门店APPID"); row.CreateCell(11).SetCellValue("核销地址"); row.CreateCell(12).SetCellValue("核销ID"); for (int i = 0; i < 13; i++) { //设置高度 row.Height = 400; row.Cells[i].CellStyle = cellStyle; } #endregion //合并单元格 /** * 第一个参数:从第几行开始合并 * 第二个参数:到第几行结束合并 * 第三个参数:从第几列开始合并 * 第四个参数:到第几列结束合并 **/ CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0); sheet.AddMergedRegion(region); HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle(); cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中 cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中 HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle(); cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中 for (int i = 1; i < 100; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); row2.CreateCell(0).SetCellValue("收银员小吴"); row2.CreateCell(1).SetCellValue("13587639864"); row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm")); row2.CreateCell(3).SetCellValue("1000"); row2.CreateCell(4).SetCellValue("180"); row2.CreateCell(5).SetCellValue("DJD84K6JFU"); row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}"); row2.CreateCell(7).SetCellValue("60"); row2.CreateCell(8).SetCellValue("满100元可用"); row2.CreateCell(9).SetCellValue("世纪新园中餐厅"); row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString()); var ignoreList = new List <int> { 5, 8, 9, 10 }; for (int j = 0; j < 11; j++) { if (ignoreList.Contains(j)) { continue; } row2.Cells[j].CellStyle = cellStyleItem; } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); //Response.ContentType = "application/octet-stream"; //Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;"); //Response.BinaryWrite(ms.ToArray()); //book = null; //ms.Close(); //ms.Dispose(); //return null; return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")))); }
private HSSFWorkbook NPOIHelper(List <ExportAllScoreViewModel> models) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.Alignment = HorizontalAlignment.Center; //sheet1.SetColumnWidth(0,100); //sheet1.SetColumnWidth(1, 100); //sheet1.SetColumnWidth(2, 100); //sheet1.SetColumnWidth(3, 100); //sheet1.SetColumnWidth(4, 100); //sheet1.SetColumnWidth(5, 100); //sheet1.SetColumnWidth(6, 100); #region [头部设计] //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("序号"); row1.CreateCell(1).SetCellValue("作品名称"); row1.CreateCell(2).SetCellValue("作者姓名"); row1.CreateCell(3).SetCellValue("单位名称"); row1.CreateCell(4).SetCellValue("评审指标"); row1.CreateCell(8).SetCellValue("总分"); row1.CreateCell(9).SetCellValue("评审意见"); row1.Cells.ForEach(ce => { ce.CellStyle = cellstyle; }); //给sheet1添加第二行的头部标题 NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1); row2.CreateCell(4).SetCellValue("教学设计(25)"); row2.CreateCell(5).SetCellValue("教学行为(25)"); row2.CreateCell(6).SetCellValue("教学效果(25)"); row2.CreateCell(7).SetCellValue("创新与实用(25)"); row2.Cells.ForEach(ce => { ce.CellStyle = cellstyle; }); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0)); // 序号 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1)); // 作品名称 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 7)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 8, 8)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 9, 9)); #endregion var i = 0; foreach (var item in models) { i++; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(i); rowtemp.CreateCell(1).SetCellValue(item.ResourceName); rowtemp.CreateCell(2).SetCellValue(item.Author); rowtemp.CreateCell(3).SetCellValue(item.AuthorCompany); rowtemp.CreateCell(4).SetCellValue(item.Score1); rowtemp.CreateCell(5).SetCellValue(item.Score2); rowtemp.CreateCell(6).SetCellValue(item.Score3); rowtemp.CreateCell(7).SetCellValue(item.Score4); rowtemp.CreateCell(8).SetCellValue(item.Score); rowtemp.CreateCell(9).SetCellValue(item.Comment); } NPOI.SS.UserModel.IRow rowtemp2 = sheet1.CreateRow(i + 3); rowtemp2.CreateCell(1).SetCellValue("评审组:"); rowtemp2.CreateCell(2).SetCellValue(models.FirstOrDefault().GroupName); rowtemp2.CreateCell(4).SetCellValue("评审专家:"); rowtemp2.CreateCell(8).SetCellValue("评审时间:"); return(book); }
protected static MemoryStream GetMS(List <FishEntity.ProductQuoteVo> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "报盘", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 22)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "鱼粉ID", CellType.String, cellStyle); CreateCell(row, 1, "单位", CellType.String, cellStyle); CreateCell(row, 2, "联系人", CellType.String, cellStyle); CreateCell(row, 3, "国别", CellType.String, cellStyle); CreateCell(row, 4, "货物情况", CellType.String, cellStyle); CreateCell(row, 5, "品质", CellType.String, cellStyle); CreateCell(row, 6, "最新报盘日期", CellType.String, cellStyle); CreateCell(row, 7, "船期最快", CellType.String, cellStyle); CreateCell(row, 8, "船期最慢", CellType.String, cellStyle); CreateCell(row, 9, "重量", CellType.String, cellStyle); CreateCell(row, 10, "数量", CellType.String, cellStyle); CreateCell(row, 11, "蛋白", CellType.String, cellStyle); CreateCell(row, 12, "TVN", CellType.String, cellStyle); CreateCell(row, 13, "组胺", CellType.String, cellStyle); CreateCell(row, 14, "FFA", CellType.String, cellStyle); CreateCell(row, 15, "盐和砂", CellType.String, cellStyle); CreateCell(row, 16, "灰分", CellType.String, cellStyle); CreateCell(row, 17, "备注", CellType.String, cellStyle); CreateCell(row, 18, "品牌", CellType.String, cellStyle); CreateCell(row, 19, "汇率", CellType.String, cellStyle); CreateCell(row, 20, "美金价", CellType.String, cellStyle); CreateCell(row, 21, "人民币价", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.ProductQuoteVo model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.quotesupplier); CreateCell(row, 2, model.quotelinkman); CreateCell(row, 3, model.nature); CreateCell(row, 4, model.goodsinfo); CreateCell(row, 5, model.specification); CreateCell(row, 6, model.quotedate); CreateCell(row, 7, model.quotesaildatefast); CreateCell(row, 8, model.quotesaildatelate); CreateCell(row, 9, model.quoteweight.ToString("f2")); CreateCell(row, 10, model.quotequantity.ToString()); CreateCell(row, 11, model.quote_protein.ToString()); CreateCell(row, 12, model.quote_tvn.ToString()); CreateCell(row, 13, model.quote_amine.ToString()); CreateCell(row, 14, model.quote_ffa.ToString()); CreateCell(row, 15, model.quote_sandsalt.ToString()); CreateCell(row, 16, model.quote_graypart.ToString()); CreateCell(row, 17, model.remark == null ? "": model.remark.ToString()); CreateCell(row, 18, model.brand); CreateCell(row, 19, ""); CreateCell(row, 20, model.quotedollars.ToString()); CreateCell(row, 21, model.quotermb.ToString()); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
/// <summary> /// 将DataTable导出到Excel /// </summary> /// <param name="dt">DataTable</param> /// <param name="fileName">仅文件名(非路径)</param> /// <returns>返回Excel文件绝对路径</returns> public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response) { #region 表头 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName); hssfSheet.DefaultColumnWidth = 13; hssfSheet.SetColumnWidth(0, 25 * 256); hssfSheet.SetColumnWidth(3, 20 * 256); // 表头 NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0); tagRow0.Height = 40 * 40; ICell cell0 = tagRow0.CreateCell(0); //设置单元格内容 cell0.SetCellValue("力诺瑞特制造工厂"); hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7)); NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1); tagRow1.Height = 20 * 20; ICell cell1 = tagRow1.CreateCell(0); //设置单元格内容 cell1.SetCellValue("计划外领料单"); hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7)); NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle(); tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle.VerticalAlignment = VerticalAlignment.Center; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 16; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "宋体"; tagStyle.SetFont(font);//HEAD 样式 cell0.CellStyle = tagStyle; NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle(); tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle1.VerticalAlignment = VerticalAlignment.Center; cell1.CellStyle = tagStyle1; // 标题样式 NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; #endregion #region 表数据 // 表数据 for (int k = 0; k < 2; k++) { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < dt.Columns.Count; i += 2) { row.CreateCell(i).SetCellValue(dr[i / 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } row.CreateCell(1).SetCellValue(""); row.GetCell(1).CellStyle = cellStyle; row.CreateCell(3).SetCellValue(""); row.GetCell(3).CellStyle = cellStyle; row.CreateCell(5).SetCellValue(""); row.GetCell(5).CellStyle = cellStyle; row.CreateCell(7).SetCellValue(""); row.GetCell(7).CellStyle = cellStyle; hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7)); } // 表数据 for (int k = 2; k < dt.Rows.Count; k++) { if (k == dt.Rows.Count - 1) { DataRow drlast = dt.Rows[k]; NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2); rowlast.Height = 30 * 20; for (int i = 0; i < dt.Columns.Count; i++) { rowlast.CreateCell(i).SetCellValue(drlast[i].ToString()); rowlast.GetCell(i).CellStyle = cellStyle; } //hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7)); } else { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < 4; i += 2) { row.CreateCell(i).SetCellValue(dr[i / 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 4; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dr[i - 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } row.CreateCell(1).SetCellValue(""); row.GetCell(1).CellStyle = cellStyle; row.CreateCell(3).SetCellValue(""); row.GetCell(3).CellStyle = cellStyle; #region 合并单元格 hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3)); #endregion } } //NPOI.SS.UserModel.IRow tagRow5 = hssfSheet.CreateRow(dt.Rows.Count + 5); //tagRow5.Height = 20 * 20; //ICell cell12 = tagRow5.CreateCell(0); ////设置单元格内容报告 //cell12.SetCellValue("Rev: 1.0"); //hssfSheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count + 5, dt.Rows.Count + 5, 0, 7)); //cell12.CellStyle = TelNoStyle; #endregion hssfSheet.PrintSetup.NoColor = true; hssfSheet.PrintSetup.Landscape = true; hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4; //是否自适应界面 hssfSheet.FitToPage = true; string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/"; if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath); //return (basePath + "Temp/" + fileName + ".xls"); string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径,可用相对路径 FileInfo fileInfo = new FileInfo(fileURL); Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名 Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小 Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.Default; Response.WriteFile(fileURL); }
/// <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); } }
public FileResult CollectCountExportExcel() { string BYORGNO = Request.Params["BYORGNO"]; string TIMEBegin = Request.Params["TIMEBegin"]; string TIMEEnd = Request.Params["TIMEEnd"]; string HID = Request.Params["HID"]; string SYSTYPEVALUE = Request.Params["SYSTYPEVALUE"]; var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004004", SYSFLAG = ConfigCls.getSystemFlag() }); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue(vMenu.MENUNAME); row.GetCell(0).CellStyle = getCellStyleTitle(book); IEnumerable <T_IPSCOL_COLLECT_TypeCountModel> typeModel; var list = T_IPSCOL_COLLECTCls.getModelCount(new T_IPSCOL_COLLECT_SW { TopORGNO = BYORGNO, DateBegin = TIMEBegin, DateEnd = TIMEEnd }, out typeModel); int typeCount = 0;//计算类别有多少列 foreach (var v in typeModel) { typeCount++; } sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 20 * 256); for (int i = 0; i < typeCount; i++) { sheet1.SetColumnWidth(i + 2, 20 * 256); } row = sheet1.CreateRow(1); if (PublicCls.OrgIsZhen(BYORGNO) == false) { row.CreateCell(0).SetCellValue("单位"); } else { row.CreateCell(0).SetCellValue("姓名"); } row.CreateCell(1).SetCellValue("总数"); int indexType = 2;//从第二列开始 foreach (var v in typeModel) { row.CreateCell(indexType).SetCellValue(v.typeName); indexType++; } for (int i = 0; i < typeCount + 2; i++) { row.GetCell(i).CellStyle = getCellStyleHead(book); } sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, typeCount + 1)); int rowI = 0; //数据行 foreach (var v in list) //循环获取数据 { row = sheet1.CreateRow(rowI + 2); if (string.IsNullOrEmpty(v.ORGName) == false) { row.CreateCell(0).SetCellValue(v.ORGName); row.GetCell(0).CellStyle = getCellStyleLeft(book); } else { row.CreateCell(0).SetCellValue(v.HName); row.GetCell(0).CellStyle = getCellStyleCenter(book); } row.CreateCell(1).SetCellValue(v.CollectCount); row.GetCell(1).CellStyle = getCellStyleCenter(book); int TypeI = 2;//类型开始列 foreach (var vv in v.TypeCountModel) { row.CreateCell(TypeI).SetCellValue(vv.typeCount); row.GetCell(TypeI).CellStyle = getCellStyleCenter(book); TypeI++; } rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = vMenu.MENUNAME + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public string ExportAccountTemplate(SaveFileDialog sflg, DataTable dt) { string message = string.Empty; try { string filename = sflg.FileName; NPOI.SS.UserModel.IWorkbook book = null; if (sflg.FilterIndex == 1) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } //整体样式 ICellStyle style = book.CreateCellStyle(); style.FillPattern = FillPattern.NoFill; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; style.WrapText = true; IFont font = book.CreateFont(); font.FontHeightInPoints = 10; font.FontName = "宋体"; style.SetFont(font); //style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //一般样式 ICellStyle style_center = book.CreateCellStyle(); style_center.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style_center.VerticalAlignment = VerticalAlignment.Center; style_center.WrapText = true; //style_center.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //style_center.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //style_center.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //style_center.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //一般样式 ICellStyle style_left = book.CreateCellStyle(); style_left.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; style_left.WrapText = true; //style_left.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //style_left.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //style_left.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //style_left.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("清算信息汇总"); sheet.DefaultRowHeight = 20; for (int columnNum = 0; columnNum < 11; columnNum++) { sheet.SetDefaultColumnStyle(columnNum, style); } int rowIndex = 0; #region 添加表头 NPOI.SS.UserModel.IRow rowName = sheet.CreateRow(rowIndex); rowName.CreateCell(0, CellType.String).SetCellValue("地区"); rowName.CreateCell(1, CellType.String).SetCellValue("序号"); rowName.CreateCell(2, CellType.String).SetCellValue("车辆生产企业"); rowName.CreateCell(3, CellType.String).SetCellValue("车辆型号"); rowName.CreateCell(4, CellType.Numeric).SetCellValue("企业申报推广数"); rowName.CreateCell(5, CellType.Numeric).SetCellValue("企业申请补助标准"); rowName.CreateCell(6, CellType.Numeric).SetCellValue("企业申请清算资金"); rowName.CreateCell(7, CellType.Numeric).SetCellValue("专家组核定的推广数"); rowName.CreateCell(8, CellType.Numeric).SetCellValue("专家组核定的补助标准"); rowName.CreateCell(9, CellType.Numeric).SetCellValue("应清算补助资金"); rowName.CreateCell(10, CellType.String).SetCellValue("核减原因"); rowIndex++; #endregion #region 添加数据 int dealerNum = 0; //第一行总计 var countENTNum = (from DataRow row in dt.Rows select new { ent_num = row["ENT_NUM"] }).Sum(a => Convert.ToDecimal(a.ent_num)); var countENTMoney = (from DataRow row in dt.Rows select new { ent_money = row["SQBZBZ"] }).Sum(a => Convert.ToDecimal(a.ent_money)); var countENTCount = (from DataRow row in dt.Rows select new { ent_count = row["ENT_COUNT"] }).Sum(a => Convert.ToDecimal(a.ent_count)); var countAPPNum = (from DataRow row in dt.Rows select new { app_num = row["APP_NUM"] }).Sum(a => Convert.ToDecimal(a.app_num)); var countAPPMoney = (from DataRow row in dt.Rows select new { app_money = row["APP_MONEY"] }).Sum(a => Convert.ToDecimal(a.app_money)); var countAPPCount = (from DataRow row in dt.Rows select new { app_count = row["APP_COUNT"] }).Sum(a => Convert.ToDecimal(a.app_count)); IRow rowTotal = sheet.CreateRow(rowIndex); ICell cellTotal = rowTotal.CreateCell(0); cellTotal.SetCellValue("总计"); cellTotal.CellStyle = style_center; sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3)); rowTotal.CreateCell(4, CellType.String).SetCellValue(countENTNum.ToString()); rowTotal.CreateCell(5, CellType.String).SetCellValue(countENTMoney.ToString()); rowTotal.CreateCell(6, CellType.String).SetCellValue(countENTCount.ToString()); rowTotal.CreateCell(7, CellType.String).SetCellValue(countAPPNum.ToString()); rowTotal.CreateCell(8, CellType.String).SetCellValue(countAPPMoney.ToString()); rowTotal.CreateCell(9, CellType.String).SetCellValue(countAPPCount.ToString()); rowIndex++; #region 循环省份写入 var ProvinceList = dt.AsEnumerable().Select(d => d.Field <string>("DQ")).Distinct().ToList(); for (int i = 0; i < ProvinceList.Count(); i++) { dealerNum = 1; //地区 省份 int DealerCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().Count(); int ClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLXH")).Count(); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + ClxhCount + DealerCount, 0, 0)); IRow rowSFTotal = sheet.CreateRow(rowIndex); ICell cellSF = rowSFTotal.CreateCell(0); cellSF.SetCellValue(ProvinceList[i]); #region 省份合计 // IRow rowSFTotal = sheet.CreateRow(rowIndex); var dvShengfen = dt.DefaultView; dvShengfen.RowFilter = String.Format("DQ='{0}'", ProvinceList[i]); var dtShengfen = dvShengfen.ToTable(); var sum_ENT_NUM = Convert.ToDecimal(dtShengfen.Compute("sum(ENT_NUM)", "TRUE")); var sum_SQBZBZ = dtShengfen.Compute("sum(SQBZBZ)", "TRUE"); var sum_ENT_COUNT = dtShengfen.Compute("sum(ENT_COUNT)", "TRUE"); var sum_APP_NUM = dtShengfen.Compute("sum(APP_NUM)", "TRUE"); var sum_APP_MONEY = dtShengfen.Compute("sum(APP_MONEY)", "TRUE"); var sum_APP_COUNT = dtShengfen.Compute("sum(APP_COUNT)", "TRUE"); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3)); ICell cellSFTotal = rowSFTotal.CreateCell(1); cellSFTotal.SetCellValue("合计"); cellSFTotal.CellStyle = style_center; rowSFTotal.CreateCell(4, CellType.String).SetCellValue(sum_ENT_NUM.ToString()); rowSFTotal.CreateCell(5, CellType.String).SetCellValue(sum_SQBZBZ.ToString()); rowSFTotal.CreateCell(6, CellType.String).SetCellValue(sum_ENT_COUNT.ToString()); rowSFTotal.CreateCell(7, CellType.String).SetCellValue(sum_APP_NUM.ToString()); rowSFTotal.CreateCell(8, CellType.String).SetCellValue(sum_APP_MONEY.ToString()); rowSFTotal.CreateCell(9, CellType.String).SetCellValue(sum_APP_COUNT.ToString()); rowIndex++; #endregion #region 照汽车生产企业写入 var DealerList = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().ToList(); for (int j = 0; j < DealerList.Count(); j++) { //企业序号 int DealerClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).Select(d => d.Field <string>("CLXH")).Count(); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 1, 1)); IRow rowDealerTotal = sheet.CreateRow(rowIndex); ICell cellDealerNum = rowDealerTotal.CreateCell(1); cellDealerNum.SetCellValue(dealerNum); //企业名称 sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 2, 2)); ICell cellDealer = rowDealerTotal.CreateCell(2); cellDealer.SetCellValue(DealerList[j]); //企业小计 var dvDealer = dt.DefaultView; dvDealer.RowFilter = String.Format("CLSCQY='{0}' AND DQ = '{1}'", DealerList[j], ProvinceList[i]); var dtDealer = dvDealer.ToTable(); var dealer_ENT_NUM = dtDealer.Compute("sum(ENT_NUM)", "TRUE"); var dealer_SQBZBZ = dtDealer.Compute("sum(SQBZBZ)", "TRUE"); var dealer_ENT_COUNT = dtDealer.Compute("sum(ENT_COUNT)", "TRUE"); var dealer_APP_NUM = dtDealer.Compute("sum(APP_NUM)", "TRUE"); var dealer_APP_MONEY = dtDealer.Compute("sum(APP_MONEY)", "TRUE"); var dealer_APP_COUNT = dtDealer.Compute("sum(APP_COUNT)", "TRUE"); rowDealerTotal.CreateCell(3, CellType.String).SetCellValue("小计"); rowDealerTotal.CreateCell(4, CellType.String).SetCellValue(dealer_ENT_NUM.ToString()); rowDealerTotal.CreateCell(5, CellType.String).SetCellValue(dealer_SQBZBZ.ToString()); rowDealerTotal.CreateCell(6, CellType.String).SetCellValue(dealer_ENT_COUNT.ToString()); rowDealerTotal.CreateCell(7, CellType.String).SetCellValue(dealer_APP_NUM.ToString()); rowDealerTotal.CreateCell(8, CellType.String).SetCellValue(dealer_APP_MONEY.ToString()); rowDealerTotal.CreateCell(9, CellType.String).SetCellValue(dealer_APP_COUNT.ToString()); rowIndex++; //按照车辆型号写入 var detailData = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).CopyToDataTable(); for (int k = 0; k < detailData.Rows.Count; k++) { IRow rowDealer = sheet.CreateRow(rowIndex); rowDealer.CreateCell(3, CellType.String).SetCellValue(detailData.Rows[k]["CLXH"].ToString()); rowDealer.CreateCell(4, CellType.String).SetCellValue(detailData.Rows[k]["ENT_NUM"].ToString()); rowDealer.CreateCell(5, CellType.String).SetCellValue(detailData.Rows[k]["SQBZBZ"].ToString()); rowDealer.CreateCell(6, CellType.String).SetCellValue(detailData.Rows[k]["ENT_COUNT"].ToString()); rowDealer.CreateCell(7, CellType.String).SetCellValue(detailData.Rows[k]["APP_NUM"].ToString()); rowDealer.CreateCell(8, CellType.String).SetCellValue(detailData.Rows[k]["APP_MONEY"].ToString()); rowDealer.CreateCell(9, CellType.String).SetCellValue(detailData.Rows[k]["APP_COUNT"].ToString()); rowDealer.CreateCell(10, CellType.String).SetCellValue(detailData.Rows[k]["APP_RESULT"].ToString()); rowDealer.Cells[7].CellStyle = style_left; rowIndex++; } dealerNum++; } #endregion } for (int colNum = 0; colNum <= 10; colNum++) { int columnWidth = sheet.GetColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(colNum); if (currentCell != null) { int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length; if (columnWidth < length + 1) { columnWidth = length + 1 > 254 ? 255 : length + 1; } currentRow.HeightInPoints = 20; } } sheet.SetColumnWidth(colNum, columnWidth * 256); } sheet.ProtectSheet("1"); #endregion #endregion System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); book = null; using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); } catch (Exception ex) { message = ex.Message; } return(message); }
protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex) { if (this.ColumnHeadList.Count > 0) { // 冻结 sheet.CreateFreezePane(1, 4); // 数据从第3行开始显示 rowIndex = rowIndex + 2; // 所有列头居中 this.HeadStyle.Alignment = HorizontalAlignment.Center; for (int i = 0; i < 2; i++) { IRow row = sheet.CreateRow(rowIndex); foreach (ColumnsMapping cm in this.ColumnHeadList) { ICell cell = null; if (i == 0) { if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14) { // 合并行 sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, cm.ColumnsIndex, cm.ColumnsIndex)); cell = row.CreateCell(cm.ColumnsIndex); // 设置列宽 SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); // 设置列头样式 cell.CellStyle = this.HeadStyle; cell.SetCellValue(cm.ColumnsText); } else if (cm.ColumnsIndex == 3 || cm.ColumnsIndex == 8) { sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 4)); cell = row.CreateCell(cm.ColumnsIndex); SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); cell.CellStyle = this.HeadStyle; if (cm.ColumnsIndex == 3) { cell.SetCellValue("成本"); } else if (cm.ColumnsIndex == 8) { cell.SetCellValue("收入"); } for (int j = 3; j <= 12; j++) { if (j == 3 || j == 8) { continue; } cell = row.CreateCell(j); cell.CellStyle = this.HeadStyle; } } } else { if (cm.ColumnsIndex >= 3 && cm.ColumnsIndex <= 12) { cell = row.CreateCell(cm.ColumnsIndex); cell.CellStyle = this.HeadStyle; SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); cell.SetCellValue(cm.ColumnsText); } else if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14) { cell = row.CreateCell(cm.ColumnsIndex); cell.CellStyle = this.HeadStyle; } } } rowIndex++; } } }
/// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(NPOI.SS.UserModel.ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); }