使用EPPlus和其他数据将数据表导出到excel [英] Export datatable to excel using EPPlus with additional data

问题描述

我有一个必须生成如下图所示的excel的要求

I have a requirement where i have to generate the excel as below image

我正在使用EPPlus dll，并且只能使用下面的c＃代码直接将数据表导出到excel。

i am using EPPlus dll and i am able to export only datatable to excel directly with below c# code.

     using (var package = new ExcelPackage(Template))
                    {
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Product_Reports");
            worksheet.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium1);          
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

    ... remainig code

如何添加标题产品统计信息和其他数据（如图片中的总计，售罄等）？

How to i add the heading "Product Statistics" and other data like "Total", "Sold Out" etc as shown in the image ??

推荐答案

eppplus的网站应该向您展示如何完成所有这些操作，但这只是一个开始：

The examples on eppplus's site should show you how to do all of this but here is a start:

[TestMethod]
public void Table_Export_Test()
{
    //http://stackoverflow.com/questions/31042901/export-datatable-to-excel-using-epplus-with-additional-data
    //Throw in some data
    var dtdata = new DataTable("tblData");
    dtdata.Columns.Add(new DataColumn("Order no.", typeof (string)));
    dtdata.Columns.Add(new DataColumn("Product name", typeof (int)));
    dtdata.Columns.Add(new DataColumn("Name", typeof (int)));

    for (var i = 0; i < 20; i++)
    {
        var row = dtdata.NewRow();
        row[0] = i;
        row[1] = i*10;
        row[2] = i*100;
        dtdata.Rows.Add(row);
    }


    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells["A1"].Value = "Product Statistics";
        ws.Cells[1, 1, 1, 6].Merge = true;

        ws.Cells[3, 3].Value = "Total";
        ws.Cells[3, 4].Value = "200";

        ws.Cells[4, 3].Value = "Sold out";
        ws.Cells[4, 4].Value = "50";

        ws.Cells[1, 1, 1, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        ws.Cells[3, 3, 6, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
        ws.Cells[3, 4, 6, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

        ws.Cells[6, 1].LoadFromDataTable(dtdata, true);

        ws.Column(1).Width = 13;
        ws.Column(2).Width = 13;
        ws.Column(3).Width = 13;
        ws.Column(4).Width = 13;

        package.Save();
    }

}

这篇关于使用EPPlus和其他数据将数据表导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！