我在c#桌面应用程序上工作,我无法将数据导出到具有多个选项卡(多页)的excel文件。
只有我可以用数据表模块字段上存在的仅基于数据的工作表来创建excel文件。
我使用开放的XML库
数据表数据如下:
将输出Excel文件按模块划分为多个页签
PartId Company Files Tab Module
1222 micro Abc source 1
1321 silicon Abc source 1
1444 cd2 Abc types 2
1321 cd3 Abc types 2
1541 tvs Abc types 2预期结果:
基于模块创建具有两个工作表第一个工作表名称来源和第二个工作表名称类型的文件ABC.xlsx,并根据数据表中存在的数据加载与每个工作表相关的数据。
所以,如果我有两个模块,意味着我有两张纸。
我尝试过的:
public Boolean createExcelFile(DataTable Table,String FullFilePathName)
{
Boolean IsDone = false;
try
{
FileInfo CreatedFile = new FileInfo(FullFilePathName);
Boolean ISNew = false;
if (!CreatedFile.Exists)
{
ISNew = true;
}
using (var pck = new ExcelPackage(CreatedFile))
{
ExcelWorksheet ws;
if (ISNew == true)
{
ws = pck.Workbook.Worksheets.Add("Sheet");
ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);
}
else
{
ws = pck.Workbook.Worksheets.FirstOrDefault();
ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);
}
pck.Save();
IsDone = true;
}
}
catch (Exception ex)
{
throw ex;
}
return IsDone;
}但是上面的问题代码创建了一个只有一个工作表的文件
那么如何创建基于模块的多页文件呢?
发布于 2020-06-05 05:23:44
我通过在数据集上存储多个数据表,然后对其进行循环来解决我的问题
DataSet ds = new DataSet();
var result = from rows in dt.AsEnumerable()
group rows by new { Module = rows["ModuleName"] } into grp
select grp;
foreach (var item in result)
{
ds.Tables.Add(item.CopyToDataTable());
}
Affected = new CExcel().createExcelFileForDs(ds, exportPath);
public Boolean createExcelFileForDs(DataSet ds, String FullFilePathName)
{
Boolean IsDone = false;
try
{
FileInfo CreatedFile = new FileInfo(FullFilePathName);
Boolean ISNew = false;
if (!CreatedFile.Exists)
{
ISNew = true;
}
using (var pck = new ExcelPackage(CreatedFile))
{
ExcelWorksheet ws;
foreach (DataTable Table in ds.Tables)
{
if (ISNew == true)
{
ws = pck.Workbook.Worksheets.Add(Convert.ToString(Table.Rows[0]["Tab name"]));
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft) // Right to Left for Arabic lang
{
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
wv.RightToLeft = true;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.Cells.AutoFitColumns();
}
else
{
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
wv.RightToLeft = false;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.Cells.AutoFitColumns();
}
ws.Cells.AutoFitColumns();
ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);
}
else
{
ws = pck.Workbook.Worksheets.FirstOrDefault();
ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);
}
}
pck.Save();
IsDone = true;
}
}
catch (Exception ex)
{
throw ex;
}
return IsDone;
}https://stackoverflow.com/questions/62186178
复制相似问题