Aspose work sheet Import csv loses leading zero foramt

Aspose.Cells Product Family
1
Hi, We are using aspose excel worksheet for exporting to CSV format.

I am using importdatatable method to export the datatable to csv format. in the datatable I have the value 00009666 after using importdatatable as csv leading zero has truncated and it is display as integer number. Please let me know the alternate solutions for this…

Here is the code.
xlWorksheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0, ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count, false, “dd/MM/yyyy”, false);

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please try the following overload and see if it works fine. Set the last parameter as false.

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

If your problem still occurs, please provide us your runnable sample project and source files. We will look into it and update you asap.
3

Hi,I have used the mentioned method Still it is not working.I have pasted my code here. can you please check and let me know.

private void ExportToCSV()

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

con.Open();

SqlDataAdapter da = new SqlDataAdapter("select '00000' + CONVERT(varchar, 0095555)", con);

DataSet ds = new DataSet();

da.Fill(ds);

string Filename = "test";

string sFilePath = HttpRuntime.AppDomainAppPath + Filename;

FileStream fstream = new FileStream(sFilePath, FileMode.Create);

Workbook workBook = new Workbook(fstream);

xlWorksheet = workBook.Worksheets[0];

// xlWorksheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0, ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count, false, "dd/MM/yyyy", false);

xlWorksheet.Cells.ImportDataTable(ds.Tables[0],

true, 0, 0, false, false);

xlWorksheet.AutoFitRows();

xlWorksheet.AutoFitColumns();

workBook.Save(

this.Response, Filename + ".csv", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.CSV));

fstream.Close();

}

4

Hi,


Well, this is MS Excel’s behavior that if the data/values has leading zeros, it will eliminate the zeros, so you will see the numbers without leading zeros. I have tested your scenario using the following sample runnable code and it works fine. I am using v7.5.2.

Sample code:
Workbook excel = new Workbook();
DataTable dt = new DataTable(“Products”);
DataRow dr = null;

dt.Columns.Add(“ColA”, typeof(string));


for (int x = 0; x < 10; x++)
{
dr = dt.NewRow();
dr[0] = “0009” + x.ToString();
dt.Rows.Add(dr);
}

Worksheet sheet = excel.Worksheets[0];
ImportTableOptions tableOptions = new ImportTableOptions();
tableOptions.ConvertNumericData = false;
sheet.Cells.ImportData(dt, 0, 0, tableOptions);

excel.Save(“e:\test2\outfile_1.csv”, SaveFormat.CSV);

Note: Please open the output file into Notepad and you will see those leading zeros fine.

Thank you.
5
Hi,

gunasekaran:
Hi, I am using aspose.cells 7.1.1.0 in this there no ImportTableoption. Regards Gunasekaran

Well, ImportTableOptions is newer APIs, so kindly use our latest version as we recommend.

Thank you.
6

Hi,

When i open in the notepad i don't have any problem in aspose version 7.1.1.0

but when i open in the excel only i have the problem. is there any styles for this.

Regards

Gunasekaran

7

In ms excel we have format 'Text' it will display as text if enter number.

In the excel format cell option is available. in the format cell several options available

text,number..

Can you please check how we can apply the style for this.

8

Hi,


I have evaluated your scenario in MS Excel manually. I think you cannot do it in CSV file format, numbers formattings applied in a CSV file is not saved as this is only simple text format. you may try to create a simple CSV file. Right-click on a cell (e.g A1) and click “Format Cells…” options, now select “Text” from the category. Enter a value i.e. “00009343”. Now, save/resave the CSV file, you got the error, anyways kindly save it. Now close the file. Again reopen the file into MS Excel and you will see that the leading zeros are gone. So, this is not possible in MS Excel either. You have to choose other file formats e.g XLS/XLSX etc.

If you still think that this is possible in MS Excel, please create such a CSV file (having values with leading zeros) in MS Excel manually and post it here, we will check it soon.

Thank you.

9
Hi,

gunasekaran:

Hi, I think what you are said is correct. Thank you for your analysis.. Regards gunasekaran


Good to know that your have sorted it out and confirmed it by yourself.


Have a good day!