EPPlus-如何使用模板

我最终采用的解决方案：

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的所有方法。

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

    // 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();
    }

//这是我对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.

我使用Vb.net，以下是我所做的：

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);
}

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);  
}