Saving to csv files removes leading zeros and decimal on numbers

Aspose.Cells Product Family
1

Hello
I have the following code
var workBook = new Workbook();
workBook.Worksheets.Clear();
LoadOptions loadOptions = new LoadOptions();
loadOptions.LoadDataAndFormatting = false;
loadOptions.ConvertNumericData = false;
int i = workBook.Worksheets.Add();

        //Obtaining the reference of the newly added worksheet by passing its sheet index
        Worksheet worksheet = workBook.Worksheets[i];
        //Getting the Style of the A1 Cell
        worksheet.Cells.ImportDataTable(inforceIllusSpreadSheet,  true, 0, 0, false, false);


        Cells cells = worksheet.Cells;
        FindOptions opts = new FindOptions();
        opts.LookInType = LookInType.Values;
        opts.LookAtType = LookAtType.EntireContent;

        Aspose.Cells.Cell cell = cells.Find("POLICY_NUMBER", null, opts);
        int maxrow = cells.GetLastDataRow(cell.Column);
        for (int cellIndex = 1; cellIndex <= maxrow; cellIndex++)
        {
            Cell policyNumberCell = cells[cellIndex, cell.Column];
        }

        cell = cells.Find("MODE_PREMIUM", null, opts);
        maxrow = cells.GetLastDataRow(cell.Column);
        for (int cellIndex = 1; cellIndex <= maxrow; cellIndex++)
        {
            Cell modePremium = cells[cellIndex, cell.Column];
            Aspose.Cells.Style style = modePremium.GetStyle();
            style.Custom = "0#.00";
            modePremium.SetStyle(style);

        }

        workBook.Save(csvFile, Aspose.Cells.SaveFormat.CSV);
        csvFileName = csvFile;

I put watch on the policyNumberCell and it shows leading zeroes. Also I put watch on modePremium and any number with ##.00 is formatted correctly. But when opening the saved csv files the policyNumberCell do not have any leading zeroes and modePremium are whole numbers.

The strange thing is if modePremium has numbers after the decimal point, it is formatted correctly, example 10.55 works but 10.00 is 10 in csv file.

Thanks

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please post your sample code again because your code is jumbled up. And also provide us your source excel or csv files used in your code. Please also provide us your expected excel or csv file which you can create manually using Microsoft Excel or Notepad. It will help us investigate this issue thoroughly.

3

Shakeel

I have included the code in aspose_12_02_2015.txt. read. In this case I create a datatable. I added just 3 columns:

NAME, AMOUNT, and DATE.

The NAME is string, the AMOUNT is decimal and DATE is string.

I added just 2 records:

Smith, 100.55, 12/02/2015, Jones, 100.00, 12/02/2015.

I stored them in datatable dt. I used the following code to get the datatable into a csv file

worksheet.Cells.ImportDataTable(dt, true, “A1”);
Cells cells = worksheet.Cells;
workBook.Save(csvFile, Aspose.Cells.SaveFormat.CSV);
where csvFile is any csv file with any name.

When I open the cscv file the date in the csv file was:
Smith 100.55 12/2/2015
Jones 100 12/2/2015

The problem is the 100.00 was 100 in the csv file. We need to have trailing zeroes and leading zeroes in the csv file.

Thanks

4

Hi,

Thanks for your nice simple code and explanation of the problem in detail and using Aspose.Cells.

We have looked into this issue and found that data table itself shows 100 instead of 100.00 as shown in the screenshot. So we think, you should not use decimal type and you should instead use string type to preserve such trailing zeros.

Also, we will keep an eye on this and discuss with product team if there is any possible solution or workaround that you can employ in your project to deal with this issue.

5

Hi,

Thanks for using Aspose.Cells.

We have logged this issue in our database for development team consideration and investigation. We will look into it and see if there is some workaround or other solution is available for you to deal with this problem if possible. Once there is some news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44109 - Saving to csv files removes leading zeros and decimal on numbers

6

Hi,

Thanks for using Aspose.Cells

There are two solutions:

a) Simply set string as value of “AMOUNT”.
change dt.Columns.Add(“AMOUNT”, typeof(decimal)); as dt.Columns.Add(“AMOUNT”, typeof(string));

b) Please set the number formats for the data. See following codes:

C#
DataTable dt = new DataTable();
DataRow row;
dt.Columns.Add(“NAME”, typeof(String));
dt.Columns.Add(“AMOUNT”, typeof(decimal));
dt.Columns.Add(“DATE”, typeof(DateTime));

row = dt.NewRow();
row[“NAME”] = “Smith”;
row[“AMOUNT”] = 100.55;
row[“DATE”] = DateTime.Now;
dt.Rows.Add(row);
row = dt.NewRow();
row[“NAME”] = “Jones”;
row[“AMOUNT”] = 100.00;
row[“DATE”] = DateTime.Now;
dt.Rows.Add(row);

var workBook = new Workbook();
workBook.Worksheets.Clear();
int i = workBook.Worksheets.Add();
Worksheet worksheet = workBook.Worksheets[i];
ImportTableOptions options = new ImportTableOptions();
options.NumberFormats = new string[] { null, “#.00”, “MM/dd/yyyy” };

worksheet.Cells.ImportData(dt,0,0,options);
Cells cells = worksheet.Cells;
workBook.Save(path +“dest.csv”, Aspose.Cells.SaveFormat.CSV);