How to add same column name in excel sheet in EPPLUS library while creating excel

Question

I am using EPPLUS library for creating excel file with a data table. I have such column in my stored procedure: emp_name, sales, INR, marketing, INR But in excel file it generates column name like with column name like emp_name, sales, INR, marketing, INR1 etc. How do I prevent that to print whatever column name in the data table or any other configurations that I am missing?

Code:

        string BasePath = ConfigurationManager.AppSettings["BasePath"];
        string fileName = Guid.NewGuid().ToString() + ".xlsx";
        string FilePath = BasePath + fileName;

        using (ExcelPackage excel = new ExcelPackage())
        {
            ExcelWorksheet ws = 
            excel.Workbook.Worksheets.Add("WorkingReport");

            ws.Cells["A1"].LoadFromDataTable(data,false);
            ws.Row(1).Style.Font.Bold = true;

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

            FileInfo excelFile = new FileInfo(FilePath);
            ws.Protection.IsProtected = false;
            excel.SaveAs(excelFile);
         }

Sanjay · Accepted Answer · 2018-05-29T06:29:54.793

2

INR and INR1 is printed because your datatable has those columns. When same column is selected more than one time in a select list, SQL automatically aliases the column name by adding number at its end to distinguish between two columns. A datatable cannot have duplicate column names. In your case, second INR is being renamed to INR1. You could try

ws.Cells["A1"].LoadFromDataTable(data,true); //"PrintHeaders" true instead of false

but I think you will get the same result.

If you can somehow manage to get cell address of header after data is loaded you can over write the value as ws.Cells["A5"].Value="INR" will overwrite the "INR1". There are other ways also https://github.com/JanKallman/EPPlus/wiki/Addressing-a-worksheet

edited May 29 '18 at 06:29

answered May 29 '18 at 06:02

Sanjay

515
3
8

Thanks for the suggestion but I need the excel file in such format, not a single chance to change column names! – Prashant Pimpale May 29 '18 at 06:10
column name + count is added by datatable or that modification is doing while creating excel? – Prashant Pimpale May 29 '18 at 06:11
1

If the postion of header in excel file is fixed, or some how you can find the address of header cell, then you can rename the text after the data is being loaded. sheet.Cells[CellAddress].Value = "INR"; – Sanjay May 29 '18 at 06:14
Columnname+count is added by c# while creating datatable – Sanjay May 29 '18 at 06:16
Ok let me try above on because the no of column in the sheet is fixed – Prashant Pimpale May 29 '18 at 06:18
If it is fixed that second "INR" will always be fifth col the ws.Cells["A5"].Value="INR" will overwrite the "INR1". There are other ways also https://github.com/JanKallman/EPPlus/wiki/Addressing-a-worksheet – Sanjay May 29 '18 at 06:21
Above one is working please update your answer as per comment code. For the temporary purpose, its working and the change impact is also less in my case. – Prashant Pimpale May 29 '18 at 06:26

How to add same column name in excel sheet in EPPLUS library while creating excel

1 Answers1