0

I have an Excel sheet that has multiple sheets and is filled with formulas. There are some blank cells in this sheet that are to be filled by a user and saved to get different results with different inputs.

I take the inputs from the users and write these values to blank cells using EPPlus for c#. After this, I call the calculate function on this excel sheet and save the sheet. When I read this saved sheet using ExcelDataReader, the value that I am getting is 0 for some cells and proper values for some cells. But when I open this sheet manually, all the cells have properly calculated values.

Kindly help me so that I can get properly calculated values from that sheet using ExcelDataReader

 FileStream stream = System.IO.File.Open(path, FileMode.Open, FileAccess.ReadWrite);
    IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);
    var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
    {
    ConfigureDataTable = _ => new ExcelDataTableConfiguration
    {
     UseHeaderRow = true,
    }
    });
    var workbook = reader.AsDataSet();
    var sheet =  dataSet.Tables[index];

I am using this code to read from the excel.

Note - When I write to the excel file (say abc.xlsx) using c# and try to read from the file the values that I get are invalid but when I open the file (abc.xlsx) manually and click on save option in MS Excel or WPS writer and after that if I read from this file I get proper values.

Following is the code that I use to write to excel using EPPlus

FileInfo fileInfo = new FileInfo(filePath);

ExcelPackage excelPackage = new ExcelPackage(fileInfo);
excelPackage.Workbook.CalcMode = ExcelCalcMode.Manual;

var inputDataWorksheet = excelPackage.Workbook.Worksheets.Where(x => x.Name.Contains("Input Data")).FirstOrDefault();
var resultSheet1 = excelPackage.Workbook.Worksheets.Where(x => x.Name.Contains("SMH code wise-A")).FirstOrDefault();
var resultSheet2 = excelPackage.Workbook.Worksheets.Where(x => x.Name.Contains("SMH code wise-B")).FirstOrDefault();
var resultSheet3 = excelPackage.Workbook.Worksheets.Where(x => x.Name.Contains("SMH Process wise")).FirstOrDefaul

//dummy code
foreach (var input in allAnswers))
{
    inputDataWorksheet.Cells[input.Item2, input.Item3].Value = input.Item1;
}

var calcOptions = new ExcelCalculationOption();
calcOptions.AllowCircularReferences = true;
resultSheet1.Cells[1, 1, lastRow, lastCol].Calculate(calcOptions);
resultSheet2.Cells[1, 1, lastRow, lastCol].Calculate(calcOptions);
resultSheet3.Cells[1, 1, lastRow, lastCol].Calculate(calcOptions);
excelPackage.Workbook.CalcMode = ExcelCalcMode.Automatic;

excelPackage.SaveAs(new FileInfo(exportPath));
excelPackage.Dispose();

After writing to excel, I try to read the value from resultSheet1, resultSheet2 and resultSheet3.

icamn
  • 1
  • 1
  • 1
  • 1
    It's hard to help without seeing the relevant code. – LarsTech Jan 07 '22 at 00:21
  • Could you share how you are reading the value and calculating that? Seems requires to debug the value if they are coming accordingly. – Md Farid Uddin Kiron Jan 07 '22 at 01:24
  • The problem is in the writing/calculating step. ExcelDataReader makes no attempts to recalculate formulas, but returns raw values physically stored in the file. Thats why it works after saving the file in "Excel proper". Maybe try and identify specific formulas/functions that are not implemented in EPPlus and workaround those – user8728340 Jan 07 '22 at 08:52
  • If you're using EPPlus then what's stopping you from reading the excel using it too...? You can use `workSheet.Cells[row, col].Value` to get the value – Bumba Jan 09 '22 at 14:06
  • @user8728340 I have added the code which I use to write to excel – icamn Jan 14 '22 at 07:47
  • @Bumba I've tried reading from excel using EPPlus and the result is same as ExcelDataReader. I am using ExcelDataReader coz I think it's faster in reading excels than EPPlus – icamn Jan 14 '22 at 07:48
  • @icamn I assume you have a template spreadsheet in `filePath` with predefined formulas etc for which you are populating the data. You may want to check if all the formulas are supported by EPPlus: https://epplussoftware.com/en/Developers/FormulaCalc . If they are, next tip is to double-check if `.Calculate` is called on the correct cell(s?). Hard to tell without looking at the template XLSX. – user8728340 Jan 15 '22 at 09:29

0 Answers0