Conversion to csv without apply formatting on numbers

[igorla72]

I would like to convert .xslx into .csv. Excel file has a column with numbers which are formatted (like "#,###.##"). As a result, output .csv file also has numbers in a formatted way (e.g. "1,186.75").

Is there a way to make conversion without applying any formatting? I.e. to have plain number like 1186.75 .

Thank you,
Igor

[Abel.He]

Hi,

Thanks for your inquiry.
For your scenario, you can set format to “00.0” for the specify column before converting. I put the C# code below for your reference.

Code: Select all

// Create a new Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file from the specified path
            workbook.LoadFromFile(@"../../data/modifyFormat.xlsx");

            // Get the first worksheet from the workbook
            Worksheet worksheet = workbook.Worksheets[0];

            // Set the number format of the first column to two decimal places
            worksheet.Columns[0].NumberFormat = "0.00";

            // Save the worksheet as a CSV file with comma as the delimiter and UTF-8 encoding
            worksheet.SaveToFile(@"../../output/rest.csv", ",", Encoding.UTF8);

Sincerely
Abel
E-iceblue support team

[igorla72]

Thank you for your prompt response. The problem, that I don't always know which column has numbers. And applying this format to column with dates will be disaster.
Is any flag to remove any formatting on cells on each column, please?

[Abel.He]

Hi,

Thanks for your feedback.
If the format of every cell in one column is same, you can check the format of column, if the format is "#,##0.00", you can set new format("0.00") to column. I put complete code below for your reference.

Code: Select all

// Create a Workbook object
            Workbook workbook = new Workbook();

            // Load the workbook from a file
            workbook.LoadFromFile(@"../../data/modifyFormat.xlsx");

            // Get the first worksheet from the workbook
            Worksheet worksheet = workbook.Worksheets[0];

            // Print the current number format of the first column
            Console.WriteLine(worksheet.Columns[0].NumberFormat);

            // Check if the number format of the first column is "#,##0.00"
            if (worksheet.Columns[0].NumberFormat == "#,##0.00")
            {
                // If yes, change it to "0.00"
                worksheet.Columns[0].NumberFormat = "0.00";
            }

            // Save the worksheet as a CSV file with comma as the delimiter and using UTF-8 encoding
            worksheet.SaveToFile(@"../../output/rest.csv", ",", Encoding.UTF8);

Sincerely
Abel
E-iceblue support team

[igorla72]

It didn't work on the column level, however, worked on cell level.

Code: Select all

       Worksheet sheet = workbook.getWorksheets().get(0);
           for(CellRange column: sheet.getColumns()){
              for(CellRange cell: column.getCellList()){
                 if(cell.hasNumber()){
                    column.setNumberFormat("0.00");
                 }
              }
           }

Thank you for your help.

[Abel.He]

Hi,

Thanks for your feedback.
I'm glad to hear you have found the solution. If you have any other questions, just feel free to contact us.

Sincerely
Abel
E-iceblue support team