EP Plus - 템플릿 사용 방법

EP Plus - 템플릿 사용 방법

저는 최근에 EPLUS(http://epplus.codeplex.com/) 를 발견했습니다.프로젝트에 모든 스타일 열 헤더가 있는 excel .xlsx 파일이 있습니다.저는 그들의 사이트에서 당신이 템플릿을 사용할 수 있다고 읽었습니다.

내 템플릿을 사용하는 방법에 대한 코드 샘플을 제공하는 방법을 알고 있거나 제공할 수 있는 사람이 있습니까?EPLUS를 사용하는 xlsx 파일?표제를 흐트러뜨리지 않고 행에 데이터를 간단히 로드할 수 있기를 바랍니다.

결국 제가 사용하게 된 솔루션은 다음과 같습니다.

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 파일에서 원하는 모든 Excel 패키지 방법을 사용할 수 있습니다.

새 패키지를 생성하려면 스트림 템플릿을 제공할 수 있습니다.

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

//EPLus용 구현입니다. // 도움이 될 수 있습니다.

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

언급URL : https://stackoverflow.com/questions/9571581/epplus-how-to-use-a-template