Hi,
On exporting data to excel do not format date as per the settings.
Could you please send me the code?
thanks,
Shreyash
Hi,
Thank you for considering Aspose.
Well, if you are using Aspose.Cells for Reporting Services, please share your RDL file and issue details. We will check it soon.
If you are using Aspose.Cells for .NET, you may check the following sample codes for your reference and check if they fix your issue.
Sample Code (1):
// Instantiating a "Products" DataTable object;
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Stock In Date", typeof(DateTime));
//Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
//Creating another empty row in the DataTable object
dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
//You can use @this overloaded method to convert to numeric value and apply formatting
//formatting at the same time, see the last two parameter.
ws.Cells.ImportDataTable(dataTable, true, 0, 0, 2, 1, true, "mm/dd/yyyy", true);
ws.AutoFitColumn(0);
wb.Save("c:\\output.xls");
Sample Code (2):
//Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Stock In Date", typeof(DateTime));
//Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
//Creating another empty row in the DataTable object
dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
ws.Cells.ImportDataTable(dataTable, true, "A1");
Aspose.Cells.Style style = null;
StyleFlag flag = null;
flag = new StyleFlag();
style = wb.Styles[wb.Styles.Add()];
style.Custom = "mm/dd/yyyy";
flag.NumberFormat = true;
ws.Cells.ApplyColumnStyle(0, style, flag);
ws.AutoFitColumn(0);
wb.Save("c:\\output.xls");
Thank You & Best Regards,
Hi I am having problem with formatting date field, I want to have in the form of MM/dd/yyyy, even while sending datatable i m formatting to "MM/dd/yyyy" ie,"05/12/2010" but in the excel sheet i am not getting the leading zero("5/12/2010" instead of "05/12/2010" ). below is the code snippet currently i am using to export
private static void ExportData(DataTable dataTable, string exportFile, FileFormatType fileFormat, string dateFormat)
{
Workbook workbook = new Workbook();
//load the Aspose.Total license into memory
byte[] a = System.Text.Encoding.GetEncoding("iso-8859-1").GetBytes(SystemMessage.Resources.EnumResources.AsposeTotalLicense);
MemoryStream ms = new MemoryStream(a);
License license = new License();
license.SetLicense(ms);
ms.Dispose();
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells.ImportDataTable(dataTable, true, 0, 0, 65536, 256, true, "mm/dd/yyyy", false);
sheet.Name = SystemMessage.Resources.EnumResources.ExportedDataSheetName;
workbook.Save(exportFile, fileFormat);
}
Please advise me and appreciate your quick reply.
Amar
Hi,
Thank you for considering Aspose.
Please try the attached latest version of Aspose.Cells. I checked the following sample code with it and it works fine.
// Instantiating a "Products" DataTable object;
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Stock In Date", typeof(DateTime));
//Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
//Creating another empty row in the DataTable object
dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
ws.Cells.ImportDataTable(dataTable, true, 0, 0, 2, 1, true, "mm/dd/yyyy", false);
ws.AutoFitColumn(0);
wb.Save("D:\\output.xls");
If you still face any problem, please create a sample application with your data and post it here. We will check it soon.
Thank You & Best Regards,
Hi,
Thanks for the quick response.
I am sorry i forgot to mention that I am trying to export to CSV file. If i do it for xls then this works like chram but CSV no luck.
private void ExportToExcel()
{
// Instantiating a "Products" DataTable object;
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Stock In Date", typeof(DateTime));
//Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
//Creating another empty row in the DataTable object
dr = dataTable.NewRow();
//Adding data to the row
dr[0] = DateTime.Now;
//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
ws.Cells.ImportDataTable(dataTable, true, 0, 0, 2, 1, true, "mm/dd/yyyy", false);
ws.AutoFitColumn(0);
wb.Save("D:\\output.csv");
}
Appreciate your quick respone.
Amar
Hi Amar,
Thank you for the feedback.
Well, I think this is MS Excel’s functionality while saving the file as CSV. You may create a simple CSV file and then open it with MS Excel. It will not show the leading zero. But if you open the file in Notepad the leading zero will be there.
Thank You & Best Regards,
Hi Alsam,
You are right. Thanks for the advise.
Amar