When field contains formula, text is always empty string

Jan 22, 2013 at 10:31 PM

I have an excel workbook that has multiple worksheets. The first worksheet has vlookups that pull from the other worksheets. I'm trying to copy the values from that first worksheet into a new workbook. I can successfully copy the values in the fields that do not have formulas, but the fields with formulas always show an empty string for the text property and null for the value property. I am seeing in the code that the formula property is set, so I know I'm accessing the field in the original worksheet, it's just not giving me the value. How can I get the value from the worksheet?

Here's my code:

foreach (ExcelRangeBase sourceCell in TTL.Cells[1, 1, TTL.Dimension.End.Row, TTL.Dimension.End.Column])
    TTLNew.Cells[sourceCell.Address].Value = sourceCell.Text;

I have also tried copying over a range and that did not work either.

Jan 23, 2013 at 2:24 AM
Edited Jan 23, 2013 at 6:50 PM

Something like this will work (never mind that the formula does not correctly reference other cells but it is copied):-


      FileInfo xlsx_template = new FileInfo(@"d:\test1.xlsx");
      FileInfo new_xlsx_file = new FileInfo(@"d:\test1-formula-copied.xlsx");
      ExcelPackage excel_package = new ExcelPackage(new_xlsx_file, xlsx_template);
      ExcelWorksheet excel_worksheet4 = excel_package.Workbook.Worksheets.Add("Read one Formula From First Sheet From test1.xlsx"); // test1.xlsx has three worksheets
      string test = excel_package.Workbook.Worksheets["Sheet 1"].Cells["B78"].Formula.ToString(); // Debugger says the value of string test is =SUM(B2:B77)
      excel_worksheet4.Cells["A1"].Formula = excel_package.Workbook.Worksheets["Sheet 1"].Cells["B78"].Formula.ToString(); //Formula =SUM(B2:B77) is correctly copied to cell A1 on new worksheet

Jan 30, 2013 at 11:23 AM
Edited Jan 30, 2013 at 11:26 AM

[Replay removed: placed in the wrong thread]

Jan 30, 2013 at 3:37 PM
Edited Jan 30, 2013 at 3:37 PM

Thanks for posting, but I don't want to copy the formula, I already know how to do that. I want to copy the text or result of the formula which isn't working. The code you supplied copies the formula, not the result. 

Apr 10, 2013 at 1:30 AM
I'm having the same issue - basically, if I add the formula dynamically via C#, the resulting value will show up as empty when queried - but, if I add that same formula manually in Excel, or if I manually save the file as a new file from Excel after I've added the formula via C#, it will deliver the value...quite strange and quite annoying.