none
Spreadsheet Light: Fail to read formula's value from excel file RRS feed

  • Question

  • I have excel file which has string and numeric value. also few cell has formula. when i open that excel file i saw excel is showing value for those formula. when i try to read the same file by Spreadsheet Light then it can read string and numeric data but fail to read formula's value. so looking for guide line that how to read formula's value.

    i have tried the below code

        sheet = new SLDocument(@"d:\SpreadsheetLight_formula.xlsx");
        SLWorksheetStatistics stats = sheet.GetWorksheetStatistics();
    
    
        for (int row = 1; row <= stats.EndRowIndex; row++)
        {
            for (int col = 1; col <= stats.EndColumnIndex; col++)
            {
                // Get the first column of the row (SLS is a 1-based index)
                var value = sheet.GetCellValueAsString(row, col);
                //if (row == 22)
                //{
                    //MessageBox.Show(value);
                //}
            }
        }
    Sunday, December 30, 2018 4:15 PM

Answers

  • Okay, sorry to say the author has not exposed SLCellFormula to the point of obtaining a formula. See his response here which he describes why.

    Working with my reply from the other thread, in this case I pass the following where the last row has a value of 12.

    var dt = new DataTable();
    dt.Columns.Add(new DataColumn() {ColumnName = "Value1", DataType = typeof(int) });
    
    dt.Rows.Add(2);
    dt.Rows.Add(2);
    dt.Rows.Add(2);
    dt.Rows.Add(2);
    dt.Rows.Add(2);
    dt.Rows.Add(2);
    dt.Rows.Add(12);
    
    var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo3.xlsx");
    ImportDataTable(fileName,"Sheet1","A1", dt,false);

    Modified call with some console write lines.

    public void ImportDataTable(string pFileName, string pSheetName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
    {
        using (var doc = new SLDocument(pFileName, pSheetName))
        {
            var style = new SLStyle {FormatCode = "#,##0.00"};
    
            doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
    
            // next two lines have hard coded values which can be corrected
            // at runtime but I'm addressing formatting and summing only.
            doc.SetCellValue(8, 1, "=Sum(A1:A7)");
            doc.SetCellStyle("A1", "A7", style);
            SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
    
            Console.WriteLine($"Sum: {doc.GetCellValueAsString(stats.EndRowIndex, 1)}");
            Console.WriteLine(doc.GetCellFormula(stats.EndRowIndex, 1));
            Console.WriteLine(doc.GetCellFormula($"A{stats.EndRowIndex}"));
            Console.WriteLine(doc.HasCellValue("A8", true));
            var test = doc.GetCells();
    
            doc.Save();
    
        }
    }

    Output

    Sum: 
    Sum(A1:A7)
    Sum(A1:A7)
    True
    

    I then peeked at GetGells to confirm that SpreadSheetLight does not evaluate a formula. Note it has the formula and indicates the cell is not empty. Now note the cell above, it has a value of 12 as expected.

    So the bottom line is you can't get the SUM as expected but instead must iterate the cells from the formula. It's really not an issue when considering the formula is for the benefit of the viewer rather than the developer where for what you want (and I sympathize) is more for database reporting and not Excel.  


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, December 30, 2018 5:07 PM
    Moderator