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