Hi there!
I’m trying to use the method ImportDataGrid to import an existing DataGrid.
The problem is that numbers and datetime are exported in bad ways… For example the text ‘29/12/2006’ is rendered as ‘39080’ in the xls created.
I don’t know how to control or format it!
Anyone could help?
Tnx!
Stefano
PS: The localization of the web application is ‘it-IT’
| 39080 |
Hi,
Thanks for considering Aspose.
Well, when you import data grid to your excel worksheet, all the date values are extracted in general format. You have to convert it to your required data format. E.g, The column in Excel worksheet is 6th, where the dates are going to be stored from the data grid, you may try the following lines of code:
workbook.Worksheets[0].Cells.Columns[5].Style.Custom = "dd/mm/yyyy";
workbook.Worksheets[0].AutoFitColumn(5);
Thank you.
First of all, thanks for the reply, but I have one more question about number cells.
I use this function to import a datagrid:
Public Sub ScriviDg(ByVal oDg As DataGrid, ByVal sNomeFoglio As String)
oDg.AllowPaging = False
Dim oWorksheet As Worksheet = AggiugiFoglio(sNomeFoglio)
Dim nRighe As Integer = oWorksheet.Cells.ImportDataGrid(oDg, 0, 0, True)
For i As Integer = 0 To oDg.Columns.Count
For j As Integer = 0 To nRighe - 1
If IsDate(oWorksheet.Cells(j, i).Value) Then
oWorksheet.Cells(j, i).Style.Custom = “dd/mm/yyyy”
Else
oWorksheet.Cells(j, i).Style.Custom = “@”
End If
Next
oWorksheet.AutoFitColumn(i)
Next
End Sub
The first problem now is that I have a TemplateColumn with an anchor that is not displayed in the produced excel.
The other problem is that in a column there is the text “0000033” that is rendered to “33” and not to “0000033” as expected for the “@” style.
Thank you very much for your support.
Best regards
Stefano
Hi,
1. The first problem now is that I have a TemplateColumn with an anchor that is not displayed in the produced excel.
Do you mean that you have a column in the grid cotaining the url etc. and it is not exported to the excel worksheet. Well, I don't find the problem, which version of Aspose.Cells you are using. Please make sure that you are using the latest version. If the problem still persists, could you create a sample test project that could produce an excel file without hyperlinks extracted. Then, Zip the project to one file and post us here so that we may check and resolve it soon.
2. The other problem is that in a column there is the text "0000033" that is rendered to "33" and not to "0000033" as expected for the "@" style.
Well, you may try:
oWorksheet.Cells(j, i).Style.Custom = "0000000"
Thank you.
For the first problem, i attached a zip file with a web project.
I noticed that if I insert a static anchor to ItemTemplate, it is rendered fine, but if I put a asp:HyperLink it disappears.
If you want other informations, ask me!
For the second problem, is there anyway to format any cell with text format?
Thanks
Stefano
Hi,
1. Well, are not supported when you use ImportDataGrid() method. I think you may try to create an array or a datatable to store those urls in the column and then in the sheet you can extract them using ImportArray() or ImportDataColumn() method etc. And we may consider it to support it soon.
2. To format a cell's data with text format, you can use:
Cells(row, column).Style.Custom = "@"
Thank you.
Hi Stefano,
When using ImportDataGrid method, date string value like '29/12/2006' is converted to Excel date value. It's a number and you can use number format to display it as your wished date format.
I will add a new ImportDataGridAsString method to preserve all text in your datagrid and don't try to convert them to numeric values. Will it serve your need?
Laurence:
I will add a new ImportDataGridAsString method to preserve all text in your datagrid and don't try to convert them to numeric values. Will it serve your need?
Hi Stefano,
Please try this attached version. It includes this new method.
Please try this attached version. It includes this new method.
Yes, it will be included in the next public release.