CODEWITHSUNDEEP
c#excel-formulacharexport-to-excelepplus
Tippi
Tippi

Reputation: 25

How to set correctly the header range in C# (use EPPlus)?

    using System.IO;   
    using System.Collections.Generic;
    using OfficeOpenXml;
    


namespace Project
{
public class CreateExcel
{

    public static void GenerateExcel(List<string> headerList, List<string> dataList, FileInfo filePath)
    {
        using (ExcelPackage excel = new ExcelPackage())
        {
            excel.Workbook.Worksheets.Add("Worksheet1");

            // Determine the header range (e.g. A1:D1)
            string headerRange = "A1:" + Char.ConvertFromUtf32(headerList.Count + 64) + "1";

            // Target a worksheet
            var worksheet = excel.Workbook.Worksheets["Worksheet1"];

            // Popular header row data
            worksheet.Cells[headerRange].LoadFromCollection(headerList);

            worksheet.Cells[2, 1].LoadFromCollection(dataList, false);

            excel.SaveAs(filePath);
        }
    }
}

I would like to create .xlsx file with this function, but the headerRange get "A1:^1" value (when I use my headerList, which has 30 elements), and of course I get this error: System.Exception: 'Invalid Address format ^1' . How to set correctly the headerRange?

Upvotes: 1

Views: 662

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131483

Use LoadFromArrays instead :

var values=new List<object[]> {
    headerList.ToArray(),
    dataList.ToArray()
}; 
worksheet.Cells["A1"].LoadFromArrays(values);

LoadFromCollection loads data from a strongly typed collection using reflection to create a different column for each property

Upvotes: 1

Related Questions