How to convert String to number format for particular column

I am using aspose cells to download excel file with set of both string and numeric data.

For a particular column say “Salary” we are getting vales as decimal from Database and assigning it to worksheet But in excel it is displayed as String format with a green indication on it top of each cell. Due to this i can’t able to use Formula like “SUM(M2:M10)”. How can i fix this issue.

I am using this code to assign data to excel.

protected void DownloadExcel(string psPlanNo, string psSuffix)
{
DataTable dtExcelData = GetASRPlansAsUploaded(psPlanNo, Convert.ToString(Session[“UserWindowsID”]));
dtExcelData.TableName = psPlanNo + “Template”;

var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
//Assigning datatable to worksheet
worksheet.Cells.ImportDataTable(dtExcelData, true, “A1”);
worksheet.AutoFilter.Range = worksheet.Cells.FirstCell.Name + “:” + worksheet.Cells.LastCell.Name;
Response.Clear();
Response.Buffer = true;
Response.Charset = “”;
Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
Response.AddHeader(“content-disposition”, “attachment;filename=” + psPlanNo + psSuffix + “.xlsx”);
worksheet.AutoFitColumns();

Aspose.Cells.Style style = worksheet.Cells[“A1”].GetStyle();
style.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent1, 0);
style.Font.Color = Color.White;
style.Pattern = BackgroundType.Solid;

for (int lnColumn = 0; lnColumn <= worksheet.Cells.MaxColumn; lnColumn++)
worksheet.Cells[0, lnColumn].SetStyle(style);

Cells cells = worksheet.Cells;
Aspose.Cells.Style fontStyle = new Aspose.Cells.Style();
Aspose.Cells.Style stylefont = workbook.Styles[workbook.Styles.Add()];
stylefont.Font.Name = “Calibri”;
stylefont.Font.Size = 12;
StyleFlag flag = new StyleFlag();
flag.FontName = true;
flag.FontSize = true;
cells.ApplyStyle(stylefont, flag);

// Changing format of Column from string to number

Aspose.Cells.Style NumStyle = worksheet.Cells[“M1”].GetStyle();
NumStyle.Custom = “#,##0.00” ;
StyleFlag NumFlag = new StyleFlag();
NumFlag.NumberFormat = true;
worksheet.Cells.ApplyColumnStyle(12, NumStyle, NumFlag);
using (MemoryStream memoryStream = new MemoryStream())
{
workbook.Save(memoryStream, SaveFormat.Xlsx);
memoryStream.WriteTo(Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.SuppressContent = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
}

I have also attached a screen shot of Excel for your reference. Please check the image

Hi,


Thanks for providing us sample code, screenshot and details.

Well, it looks your underlying data (in the column) is stored as string in your data source (database), so when you import data from the database to fill the worksheet via Aspose.Cells APIs, it remains as string. Furthermore, you cannot apply formatting or calculate formulas for string data, so you got to convert it to numeric data first. I think you may try to choose relevant ImportDataTable() overload, so while importing from datatable, so the string (so called numeric data) data should be converted automatically for your needs. Could you change the line of your code:
i.e.,
worksheet.Cells.ImportDataTable(dtExcelData, true, “A1”);

to:
worksheet.Cells.ImportDataTable(dtExcelData, true, 0, 0, true, true);
(Note: the last Boolean parameter “convertStringToNumber” should be set to true)

Hope, this helps a bit.

Thank you.

Thank you so much Amjad Sahi. Solution works. Thanks a lot… :slight_smile:

Hi,


Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

Is there anyway to complete this without dropping the appended dollar sign?

@jcovington91,

I am not sure about your requirements/issue. Could you elaborate your requirements/issue in details with sample code, template files and screenshots, we will check it and help you through.