How to display Number with first digit a 0 (Zero) in Excel

Aspose.Cells Product Family
1

I need to display ‘0565’ in Downloaded Excel. In datatable the value is ‘0565’ but after assigning datatable to worksheet, the downloaded excel shows value as ‘565’. Its an id i need to display it as it is ‘0565’. Below is my code



DataTable dtExcelData = GetDataTableValue();
dtExcelData.TableName = psPlanNo + “Template”;

var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportDataTable(dtExcelData, true, 0, 0, true,true);
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);
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();
}


The highlighted line is to assign data set value to worksheet and to display string to numbers in excel.
If i use this code
worksheet.Cells.ImportDataTable(dtExcelData, true, 0, 0, true,false);
i will get ‘0565’ but there are some columns which should be in number format that will also be converted into text.

Is there any other alternative for my problem???
Thanks in Advance

2

Hi,


Thanks for your posting and using Aspose.Cells.

If you do not want to set convertStringToNumber parameter to false, then you must assign ’ before your numerical string.

For example, your number

0056

will become

’0056

and in this way, your numerical string will not be converted into number.

Another Solution for your Requirement:

You may use custom formatting to make the value to be formatted with leading zeros. Such as Style.Custom=“0000”;