Keep Leading Zeros when using ImportFromDataTable

Aspose.Cells Product Family
1

Hi,


I am using the ImportFromDataTable method to create a workbook with only one worksheet. I have a column which is a string (not a number) where it is possible that this column could have data which looks like “0001234”. When I look at the resulting excel workbook, the leading zeroes are removed, I would like to keep them. I have read a similar post which says to use the TxtLoadOptions class and passed in the LoadFormat.CSV ENUM. Here is what I have -

Dim DT As DataTable = CustomMethodToReturnDTFromSQL

’Get a reference to an excel worksheet
Dim exportFile As New Aspose.Cells.Workbook
Dim worksheet As Aspose.Cells.Worksheet = exportFile.Worksheets(0)

'The memory stream to be saved as the export file
Dim ms As New MemoryStream

’Add the export file datatable to the excel worksheet
worksheet.Cells.ImportDataTable(budgetExportDT, True, “A1”)
'Save the file (workbook) as a memorystream object
exportFile.Save(ms, Aspose.Cells.SaveFormat.CSV)

'Now I would like to use the TxtLoadOptions class like this =
Dim txtLoadOptions As New Aspose.Cells.TxtLoadOptions(Aspose.Cells.LoadFormat.CSV)
txtLoadOptions.ConvertNumericData = False

’But it seems like the only way to use this is =
Dim file as new Aspose.Cells.Workbook(path, txtLoadOptions)

'But as you can see I do not have a “path” created already, I create one on the fly.

My question is - is there a way to add the txtLoadoptions to my workbook object without already
having a filepath in existence

*Note: I am using the newest .dll and do not have Workbook.Settings.ConvertNumericData


Any help would be appreciated

Thanks
2

Hi,


Well, it is MS Excel’s behavior to eliminate the leading zeros in the value when entering data into the cells, you may confirm this behavior in MS Excel too. I think for your needs, you may try to import data and specify that the data should not be converted to numbers, you may pick some overloads for ImportDataTable() method e.g. you may use the following overload and put “false” for the last boolean parameter.

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
int firstColumn,
bool insertRows,
bool convertStringToNumber
);



Also, for your other part of your query, you may try to open a file from streams, it is supported see the following sample code:
//…


exportFile.Save(ms, Aspose.Cells.SaveFormat.CSV)

'Now I would like to use the TxtLoadOptions class like this =
Dim txtLoadOptions As New Aspose.Cells.TxtLoadOptions(Aspose.Cells.LoadFormat.CSV) txtLoadOptions.ConvertNumericData = False
ms.Position = 0

Dim file as new Aspose.Cells.Workbook(ms, txtLoadOptions)