实现多页签Excel引出功能
部分场景下,需要把数据导出的excel的不同页签。标准引出一般是将数据引出到同一个页签里面,一下案例演示:
【注意】本案例数据量较少,所以使用了一次性引出。如果数据量过大(一般指超过5-10万数据,与服务器性能有关)情况下,应该使用分批引出。
最终效果如下,把符合条件的采购订单数据,按照供应商拆分成多个页签引出:
列表插件代码如下:
using Kingdee.BOS.Core; using Kingdee.BOS.Core.DynamicForm; using Kingdee.BOS.Core.DynamicForm.PlugIn.Args; using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel; using Kingdee.BOS.Core.List.PlugIn; using Kingdee.BOS.Core.List.PlugIn.Args; using Kingdee.BOS.Core.Metadata; using Kingdee.BOS.Core.Metadata.FieldElement; using Kingdee.BOS.Excel; using Kingdee.BOS.Model.List; using Kingdee.BOS.Orm.DataEntity; using Kingdee.BOS.ServiceHelper; using Kingdee.BOS.Util; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using Witt.Cloud.PlugIn.Common; namespace Witt.Cloud.PlugIn.BillList { public class NpoiMulSheetExportPlugIn : AbstractListPlugIn { public override void BarItemClick(BarItemClickEventArgs e) { if (e.BarItemKey.Equals("tbExportMulSheet", StringComparison.OrdinalIgnoreCase)) { Export(); } } public void Export() { List<SulpplierInfo> supplierIdList = GetSupplierList(); if (supplierIdList.Count == 0) return; //引出当前页,如果是全部页或者其他数据,需要自己数据库取数 var exportData = this.GetExportData(); if (exportData == null || exportData.Rows.Count == 0) return; var dataSet = new DataSet(); foreach (var model in supplierIdList) { //获取供应商名称 var sheetName = string.Format("{0}({1})", model.Name, model.Id); var tb = new DataTable(sheetName); this.GenDataTable(tb); //填充数据 var curSupData = exportData.Select("FSUPPLIERID =" + model.Id); foreach (var dr in curSupData) { var newRow = tb.NewRow(); newRow["fbillno"] = dr["fbillno"]; newRow["FDate"] = dr["FDate"]; newRow["FDOCUMENTSTATUS"] = dr["FDOCUMENTSTATUS"]; tb.Rows.Add(newRow); } dataSet.Tables.Add(tb); } ExportAndDownload(dataSet); } private void ExportAndDownload(DataSet ds) { var fileName = string.Format("采购订单多页签{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss")); // 生成文件的存储路径 var filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName); var fileUrl = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, PathUtils.UrlEncode(fileName)); //注意,如果是非常大的数据量,需要分批取数 //此处利用平台提供的接口使用NPOI导出excel,可以自行使用NPOI或者其他方式引出 NPOIExcelHelper.ExportExcelFile(ds, filePath); // 打开文件下载界面(下载界面关闭后,临时文件会被删除) var showParameter = new DynamicFormShowParameter(); showParameter.FormId = "BOS_FileDownLoad"; showParameter.OpenStyle.ShowType = ShowType.Modal; showParameter.CustomParams.Add("url", fileUrl); this.View.ShowForm(showParameter); } private DataTable GetExportData() { string strSql = @" select t0.FID ,t0.FBILLNO ,t0.FDate ,t0.FDOCUMENTSTATUS ,t0.FSUPPLIERID from T_PUR_POORDER t0 where fdate >='2014-08-08 00:00:00.000' and FDATE <='2015-08-10 00:00:00.000' "; var ds = DBServiceHelper.ExecuteDataSet(this.Context, strSql); return ds.Tables[0]; } private void GenDataTable(DataTable tb) { tb.Columns.Add("fbillno"); tb.Columns.Add("fdate"); tb.Columns.Add("FDOCUMENTSTATUS"); var newRow = tb.NewRow(); newRow["fbillno"] = "单据编号"; newRow["FDate"] = "日期"; newRow["FDOCUMENTSTATUS"] = "状态"; tb.Rows.Add(newRow); } /// <summary> /// 获取供应商内码集合 /// </summary> /// <returns></returns> private List<SulpplierInfo> GetSupplierList() { //获取有多少个供应商 string strSql = @" select distinct a.FSUPPLIERID,b.fname from T_PUR_POORDER a inner join T_BD_SUPPLIER_l b on a.FSUPPLIERID =b.FSUPPLIERID and b.FLOCALEID=2052 where a.FSUPPLIERID<>0;"; List<SulpplierInfo> supplierIdList = new List<SulpplierInfo>(); using (var reader = DBServiceHelper.ExecuteReader(this.Context, strSql)) { while (reader.Read()) { var supVal = reader.GetString("FSUPPLIERID").ToString(); var supName = reader.GetString("fname"); long supId = 0; if (long.TryParse(supVal, out supId)) { SulpplierInfo model = new SulpplierInfo(); model.Id = supId; model.Name = supName; supplierIdList.Add(model); } } } return supplierIdList; } } struct SulpplierInfo { public long Id { get; set; } public string Name { get; set; } } }
实现多页签Excel引出功能
部分场景下,需要把数据导出的excel的不同页签。标准引出一般是将数据引出到同一个页签里面,一下案例演示:【注意】本案例数据量较少,所...
点击下载文档
本文2024-09-16 18:34:30发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-23032.html
您需要登录后才可以发表评论, 登录登录 或者 注册
最新文档
热门文章