C# the number in this cell is formatted as text

[Sudip_inn]

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

Continue reading...