EPPlus - 如何使用模板

Question

我最近发现了EPPlus (http://epplus.codeplex.com/) 这个库。

项目中有一个带有所有样式列标题的Excel .xlsx文件。在他们的网站上，我看到可以使用模板。

请问有人知道如何使用我的template.xlsx文件与EPPlus，或者能否提供代码示例？ 我想能够简单地将数据加载到行中，而不会弄乱标题。

- Baxter

Answer 1

我最终采用的解决方案是：

using System.IO;
using System.Reflection;
using OfficeOpenXml;

//Create a stream of .xlsx file contained within my project using reflection
Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("EPPlusTest.templates.VendorTemplate.xlsx");            

//EPPlusTest = Namespace/Project
//templates = folder
//VendorTemplate.xlsx = file

//ExcelPackage has a constructor that only requires a stream.
ExcelPackage pck = new OfficeOpenXml.ExcelPackage(stream);

之后，您可以在从模板加载的 .xlsx 文件上使用 ExcelPackage 的所有方法。

- Baxter

Answer 2

要创建新的程序包，您可以提供流模板：

    // templateName = the name of .xlsx file
    // result = stream to write the resulting xlsx to
    using (var source = System.IO.File.OpenRead(templateName))
    using (var excel = new OfficeOpenXml.ExcelPackage(result, source)) {

        // Fill cells here
        // Leave headers etc as is

        excel.Save();
    }

- GvS

---

我该如何在我的项目中引用模板文件？ 项目名称 = EPPTest 路径 = \template\myTemplate.xlsx - Baxter

取决于您的模板存储位置。您可以询问正在执行程序集的文件夹，然后从那里开始。 - GvS

当我从现有文件创建文件时，这个方法对我有用。在我这个情况下，我正在更新XLSX文件中的单元格，例如日期和数字。 - Dave Stuart

这对我有用。但是我困惑为什么在使用模板文件时需要输入一个空流。 - Sue Su

Answer 3

//这是我使用EPPlus的实现方法，希望能对你有所帮助。

class EPPlus
{
    FileInfo newFile;
    FileInfo templateFile;
    DataSet _ds;
    ExcelPackage xlPackage;
    public string _ErrorMessage;

public EPPlus(string filePath, string templateFilePath)
    {
        newFile = new FileInfo(@filePath);
        templateFile = new FileInfo(@templateFilePath);

        _ds = GetDataTables(); /* DataTables */

        _ErrorMessage = string.Empty;

        CreateFileWithTemplate();

    }

private bool CreateFileWithTemplate()
    {
        try
        {
            _ErrorMessage = string.Empty;

            using (xlPackage = new ExcelPackage(newFile, templateFile))
            {
                int i = 1;
                foreach (DataTable dt in _ds.Tables)
                {
                    AddSheetWithTemplate(xlPackage, dt, i);
                    i++;
                }



                ///* Set title, Author.. */
                //xlPackage.Workbook.Properties.Title = "Title: Office Open XML Sample";
                //xlPackage.Workbook.Properties.Author = "Author: Muhammad Mubashir.";
                ////xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
                //xlPackage.Workbook.Properties.Comments = "Sample Record Details";
                //xlPackage.Workbook.Properties.Company = "TRG Tech.";



                ///* Save */
                xlPackage.Save();

            }
            return true;
        }
        catch (Exception ex)
        {
            _ErrorMessage = ex.Message.ToString();
            return false;
        }
    }

/// <summary>
    /// This AddSheet method generates a .xlsx Sheet with your provided Template file, //DataTable and SheetIndex.
    /// </summary>
    public static void AddSheetWithTemplate(ExcelPackage xlApp, DataTable dt, int SheetIndex)
    {
        string _SheetName = string.Format("Sheet{0}", SheetIndex.ToString());
        ExcelWorksheet worksheet;
        /* WorkSheet */
        if (SheetIndex == 0)
        {
            worksheet = xlApp.Workbook.Worksheets[SheetIndex + 1]; // add a new worksheet to the empty workbook
        }
        else
        {
            worksheet = xlApp.Workbook.Worksheets[SheetIndex]; // add a new worksheet to the empty workbook
        }


        if (worksheet == null)
        {
            worksheet = xlApp.Workbook.Worksheets.Add(_SheetName); // add a new worksheet to the empty workbook    
        }
        else
        {

        }

        /* Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 */
        worksheet.Cells["A1"].LoadFromDataTable(dt, true);


    }


private static void AddSheet(ExcelPackage xlApp, DataTable dt, int Index, string sheetName)
    {
        string _SheetName = string.Empty;

        if (string.IsNullOrEmpty(sheetName) == true)
        {
            _SheetName = string.Format("Sheet{0}", Index.ToString());
        }
        else
        {
            _SheetName = sheetName;
        }

        /* WorkSheet */
        ExcelWorksheet worksheet = xlApp.Workbook.Worksheets[_SheetName]; // add a new worksheet to the empty workbook
        if (worksheet == null)
        {
            worksheet = xlApp.Workbook.Worksheets.Add(_SheetName); // add a new worksheet to the empty workbook    
        }
        else
        {

        }



        /* Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 */
        worksheet.Cells["A1"].LoadFromDataTable(dt, true);



        int rowCount = dt.Rows.Count;
        int colCount = dt.Columns.Count;





        #region Set Column Type to Date using LINQ.
        /*
            IEnumerable<int> dateColumns = from DataColumn d in dt.Columns
                                           where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date")
                                           select d.Ordinal + 1;

            foreach (int dc in dateColumns)
            {
                xlSheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format = "dd/MM/yyyy";
            }
            */

        #endregion
        #region Set Column Type to Date using LOOP.

        /* Set Column Type to Date. */
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if ((dt.Columns[i].DataType).FullName == "System.DateTime" && (dt.Columns[i].DataType).Name == "DateTime")
            {
                //worksheet.Cells[2,4] .Style.Numberformat.Format = "yyyy-mm-dd h:mm"; //OR "yyyy-mm-dd h:mm" if you want to include the time!
                worksheet.Column(i + 1).Style.Numberformat.Format = "dd/MM/yyyy h:mm"; //OR "yyyy-mm-dd h:mm" if you want to include the time!
                worksheet.Column(i + 1).Width = 25;
            }
        }

        #endregion

        //(from DataColumn d in dt.Columns select d.Ordinal + 1).ToList().ForEach(dc =>
        //{
        //    //background color
        //    worksheet.Cells[1, 1, 1, dc].Style.Fill.PatternType = ExcelFillStyle.Solid;
        //    worksheet.Cells[1, 1, 1, dc].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);

        //    //border
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Top.Color.SetColor(System.Drawing.Color.LightGray);
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Right.Color.SetColor(System.Drawing.Color.LightGray);
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Bottom.Color.SetColor(System.Drawing.Color.LightGray);
        //    worksheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Left.Color.SetColor(System.Drawing.Color.LightGray);
        //});

        /* Format the header: Prepare the range for the column headers */
        string cellRange = "A1:" + Convert.ToChar('A' + colCount - 1) + 1;
        using (ExcelRange rng = worksheet.Cells[cellRange])
        {
            rng.Style.Font.Bold = true;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
            rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));  //Set color to dark blue
            rng.Style.Font.Color.SetColor(Color.White);
        }



        /* Header Footer */
        worksheet.HeaderFooter.OddHeader.CenteredText = "Header: Tinned Goods Sales";
        worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Footer: Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); // add the page number to the footer plus the total number of pages
    }


}// class End.

- Muhammad Mubashir

Answer 4

I use Vb.net, here is what I did:

VB

Imports OfficeOpenXml

Dim existingFile As New FileInfo("C:\OldFileLocation\File.xlsx")
Dim fNewFile As New FileInfo("C:\NewFileLocation\File.xlsx")

Using MyExcel As New ExcelPackage(existingFile)
  Dim MyWorksheet As ExcelWorksheet = MyExcel.Workbook.Worksheets("ExistingSheetName")
  MyWorksheet.Cells("A1").Value = "Hello"
  'Add additional info here
  MyExcel.SaveAs(fNewFile)
End Using

可能是C#语言（我没有测试过）

FileInfo existingFile = new FileInfo("C:\\OldFileLocation\\File.xlsx");
FileInfo fNewFile = new FileInfo("C:\\NewFileLocation\\File.xlsx");
using (ExcelPackage MyExcel = new ExcelPackage(existingFile)) {
ExcelWorksheet MyWorksheet = MyExcel.Workbook.Worksheets["ExistingSheetName"];
MyWorksheet.Cells["A1"].Value = "Hello";
    //Add additional info here
MyExcel.SaveAs(fNewFile);
}

- JamesCBaird

Answer 5

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Logs.xlsx", System.Text.Encoding.UTF8));

using (ExcelPackage pck = new ExcelPackage())
{
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Logs");
    ws.Cells["A1"].LoadFromDataTable(dt, true);                 
    var ms = new System.IO.MemoryStream();
    pck.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);  
}

- Taran