none
C# the number in this cell is formatted as text RRS feed

  • Question

  • Please do not overlook or bypass this post because in this post i said that i have worked with spreadsheet light which is 3rd party library. this post is not about any help seeking for spreadsheet light. the main issue is why numeric data is saving as text when i use convert to int32 ?

    I am using C# & spreadsheet light to export datatable data to excel. my code is working but when i open the excel file then i found this warning is showing for most of the cell value. the warning message is the number in this cell is formatted as text

    before save data to excel i set the format for entire range this way

     SLStyle standardstyle = new SLStyle();
     standardstyle.FormatCode = "#,##0.00";
     sheet.SetCellStyle("A1", "E6", standardstyle);
     

    but still no luck. getting same warning after opening xls file.

    My data table has data like numeric data and few cell has formula. i want to evaluate formula by spreadsheet light if possible. if not possible then i want to save data to excel file by spreadsheet light as a result excel will evaluate formulas but what is my mistake is not clear for which i am getting this warning the number in this cell is formatted as text and formulas are not evaluating because numeric values in cell becomes text instead of digit.

    I have tried this way
    1) first i populate data table with numeric data and formulas and in second phase i load that data table by spreadsheet light and set format code for entire range and save that data to excel by spreadsheet light.

    when i open the excel file generated by spreadsheet light then i found warning is still there and formula is not evaluated. formula showing 0 as a result value.

    see screen shot of my data table as a result it would be clear how data is stored in data table. 

    First set of code

               string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                int NumRows = 6;
                int NumColumns = 5;
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
    
                DataTable dt = new DataTable();
    
                for (int col = 0; col < NumColumns; col++)
                {
                    strColName = GenerateColumnText(col);
                    DataColumn datacol = new DataColumn(strColName, typeof(object));
                    dt.Columns.Add(datacol);
                }
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    dt.Rows.Add();
    
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                dt.Rows[row][col] = strSum;
                            }
                            else
                            {
                                dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
     



    Second set of code

       SLThemeSettings stSettings = BuildTheme();
        SLDocument sheet = new SLDocument(stSettings);
        sheet.ImportDataTable(1, 1, dt, false);
        //standard number format
        SLStyle standardstyle = new SLStyle();
        standardstyle.FormatCode = "#,##0.00";
        sheet.SetCellStyle("A1", "E6", standardstyle);
    
        sheet.SaveAs("d:\\SpreadsheetLight_formula.xlsx");
        sheet.Dispose();

    My desired output would be formula evaluation and show right value there in formula cell. without saving data to excel can spreadsheet light can evaluate formula and show right value? if possible show me the way please.

    please see my code and tell me what i need to add or alter in my code as a result formula should be evaluated.

    here is the link ufile.io/grv9w from where you can download excel file which my program generate

    thanks


    • Edited by Sudip_inn Saturday, December 29, 2018 5:55 PM
    Saturday, December 29, 2018 5:47 PM

Answers

  • Hello,

    The following formats and does summing on column A.

    /// <summary>
    /// Import a DataTable into a .xlsx file to a specific sheet starting at a specific cell address
    /// </summary>
    /// <param name="pFileName">Path and file name</param>
    /// <param name="pSheetName">Sheet name to import data into</param>
    /// <param name="pStartReference">cell reference to start import e.g. A1</param>
    /// <param name="pDataTable">DataTabe to import from</param>
    /// <param name="pIncludeHeaders">true to include column name, false to exclude column names</param>
    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);
            doc.Save();
        }
    }
    
    private void button3_Click(object sender, EventArgs e)
    {
        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(2);
    
        var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo3.xlsx");
        ImportDataTable(fileName,"Sheet1","A1", dt,false);
    }


    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

    • Marked as answer by Sudip_inn Sunday, December 30, 2018 4:13 PM
    Saturday, December 29, 2018 11:53 PM
    Moderator

All replies

  • Hello,

    The following formats and does summing on column A.

    /// <summary>
    /// Import a DataTable into a .xlsx file to a specific sheet starting at a specific cell address
    /// </summary>
    /// <param name="pFileName">Path and file name</param>
    /// <param name="pSheetName">Sheet name to import data into</param>
    /// <param name="pStartReference">cell reference to start import e.g. A1</param>
    /// <param name="pDataTable">DataTabe to import from</param>
    /// <param name="pIncludeHeaders">true to include column name, false to exclude column names</param>
    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);
            doc.Save();
        }
    }
    
    private void button3_Click(object sender, EventArgs e)
    {
        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(2);
    
        var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo3.xlsx");
        ImportDataTable(fileName,"Sheet1","A1", dt,false);
    }


    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

    • Marked as answer by Sudip_inn Sunday, December 30, 2018 4:13 PM
    Saturday, December 29, 2018 11:53 PM
    Moderator
  • can you please see this post https://social.msdn.microsoft.com/Forums/en-US/adfd634e-f312-4281-8f99-8fbdb6ab1775/spreadsheet-light-fail-to-read-formulas-value-from-excel-file?forum=csharpgeneral

    and share your answer.

    Sunday, December 30, 2018 4:15 PM