This project has moved. For the latest updates, please go here.

Formula not working when the formulas are in a table

Sep 12, 2016 at 3:38 PM
Hi,


I want to create a copy of a excel file [p.xlsx]. It has two spreadsheets - sheet1 and sheet2. I create another file perror.xlsx by using p.xlsx as a template file.

Sheet1 data is as follows (second column has a formula):
COL1, COl2
MICK1,=A2
MICk2,=A3

The data in the Sheet1 is defined as a table - Table1 =$A:$B

Sheet2 is "not defined" as a table, the data again is same

COL1, COl2
MICK1,=A2
MICk2,=A3

Executed the code as follows:
        string ErrFileName = @"C:\\TEMP\\pErrors.xlsx";  //Destination file
        FileInfo errorFile = new FileInfo(ErrFileName);

        string templatefile = @"C:\\TEMP\\p.xlsx";   //Source file
        FileInfo template = new FileInfo(templatefile);

        ExcelPackage errorExcelFile = new ExcelPackage(errorFile, template);

        errorExcelFile.Save();
        errorExcelFile.Dispose();

When I open the perror.xlsx file, I see the data in the sheet1 as
COL1, COl2
MICK1,MICK1
MICk2,MICK1

Analyzed the formula so it was found as:
COL1, COl2
MICK1,=A2
MICk2,=A2

But the sheet2 was populated properly.
COL1, COl2
MICK1,MICK1
MICk2,MICK2

So does that mean the EPPLUS does not bring the formula correctly when the data is in the table?

I could bring the data correctly in the sheet1 only when in the source file I opened it in excel clicked on sheet1, visited Design menu, convert to range, saved.

Let me know please how to bring the data correctly when the formula is in a table?

Thanks,

Vijay
Sep 13, 2016 at 9:20 AM
When the data was in a table, the formula need to be changed in the COL2 in the Excel file

COL1, COl2

MICK1,=[COL1]

MICk2,=[COL1]

It brought the correct result.