BOS运行时 - 引出 - Excel合并单元格
excel支持合并单元格效果,比如存在2个列头,可以合并列或者合并行,如果引出excel想要实现类似效果,可以使用第三方NPOI引出,实现效果如下:
设置参考代码:
//设置合并行列 CellRangeAddress region1 = new CellRangeAddress(0, 1,0, 0); sheet.AddMergedRegion(region1);
完整示例代码如下:
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Metadata.ControlElement;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Excel;
using Kingdee.BOS.Log;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Resource;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.ServiceHelper.FileServer;
using Kingdee.BOS.Util;
using Kingdee.BOS.Web;
using Kingdee.BOS.WebApi.Client;
using Kingdee.BOS.WebApi.FormService;
using NPOI.HSSF.Util;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
namespace Cloud.BOS.Support.Bill
{
[HotUpdate]
public class NpoiExportPlugin : AbstractBillPlugIn
{
public override void BarItemClick(BarItemClickEventArgs e)
{
if (e.BarItemKey.EqualsIgnoreCase("tbExportByNpoiMerge"))
{
ExportMerge();
}
}
private void ExportMerge()
{
var tb = BuildMergeData();
var showParameter = new DynamicFormShowParameter();
showParameter.FormId = "BOS_FileDownLoad";
showParameter.OpenStyle.ShowType = ShowType.Modal;
showParameter.CustomParams.Add("url", NPOIMerge(tb));
this.View.ShowForm(showParameter);
}
private string NPOIMerge(DataTable tb)
{
var wb = new SXSSFWorkbook();
SXSSFSheet sheet = (SXSSFSheet)wb.CreateSheet("demo");
var tempXmlFile = sheet._writer.TemporaryFilePath();
string fileName = string.Format("NPOI-demo-{0}.xlsx", DateTime.Now.ToString("yyyyMMddhhmmss"));
var filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
var fileUrl = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, PathUtils.UrlEncode(fileName));
try
{
for (int i = 0; i < tb.Rows.Count; i++)
{
var dataIndex = i;
IRow row = sheet.CreateRow(dataIndex);
for (int colIndex = 0; colIndex < tb.Columns.Count; colIndex++)
{
ICell cell = row.CreateCell(colIndex);
ICellStyle textCellStyle = wb.CreateCellStyle();
textCellStyle.DataFormat = wb.CreateDataFormat().GetFormat("@");
cell.SetCellValue(tb.Rows[dataIndex][colIndex].ToString());
cell.CellStyle = textCellStyle;
}
}
var cell1 = sheet.GetRow(0).GetCell(0);
//设置合并行列
CellRangeAddress region1 = new CellRangeAddress(0, 1,0, 0);
sheet.AddMergedRegion(region1);
SetCenter(wb, cell1);
CellRangeAddress region2 = new CellRangeAddress(0, 1, 1, 1);
sheet.AddMergedRegion(region2);
SetCenter(wb, cell1);
CellRangeAddress region3 = new CellRangeAddress(0, 1, 2, 2);
sheet.AddMergedRegion(region3);
SetCenter(wb, cell1);
var cell2 = sheet.GetRow(0).GetCell(3);
CellRangeAddress region4 = new CellRangeAddress(0,0, 3, 5);
sheet.AddMergedRegion(region4);
SetCenter(wb, cell2);
//写入Excel
using (FileStream fs = File.OpenWrite(filePath))
{
wb.Write(fs);
}
}
finally
{
if (wb != null)
{
wb.Close();
}
//清理中间临时xml文件
if (File.Exists(tempXmlFile))
{
File.Delete(tempXmlFile);
}
}
return fileUrl;
}
private DataTable BuildMergeData()
{
var sheetName = "NPOI Demo";
var tb = new DataTable(sheetName);
tb.Columns.Add("fbillno");
tb.Columns.Add("fdate");
tb.Columns.Add("FDOCUMENTSTATUS");
tb.Columns.Add("FQTY");
tb.Columns.Add("FPRICE");
tb.Columns.Add("FAMOUNT");
//构造列表头
var newRow = tb.NewRow();
newRow["fbillno"] = "单据编号";
newRow["FDate"] = "日期";
newRow["FDOCUMENTSTATUS"] = "状态";
newRow["FQTY"] = "合并多列";
newRow["FPRICE"] = "";
newRow["FAMOUNT"] = "";
tb.Rows.Add(newRow);
var newRow2 = tb.NewRow();
newRow2["fbillno"] = "";
newRow2["FDate"] = "";
newRow2["FDOCUMENTSTATUS"] = "";
newRow2["FQTY"] = "数量";
newRow2["FPRICE"] = "单价";
newRow2["FAMOUNT"] = "金额";
tb.Rows.Add(newRow2);
for (int i = 0; i < 10; i++)
{
var row = tb.NewRow();
row["fbillno"] = string.Format("BillNo-{0}", i.ToString("000"));
row["FDate"] = DateTime.Now.ToLongDateString();
row["FDOCUMENTSTATUS"] = "C";
row["FQTY"] = i + 1;
row["FPRICE"] = i * 10 + 5;
row["FAMOUNT"] = 0;
tb.Rows.Add(row);
}
return tb;
}
private void SetCenter(SXSSFWorkbook wb, ICell cell)
{
ICellStyle cellstyle = wb.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellstyle;
}
}
}
BOS运行时 - 引出 - Excel合并单元格
本文2024-09-23 04:17:07发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-164318.html