Unable to apply custom format/style

Hello,

I’m trying to apply a custom number format to a column. Can you help take a look to at the code below and let me know if I’m missing something?



string ColHeader = “MyColHeader”;

Cell cell = worksheet.Cells.Find(ColHeader, null, findOptions);

Style style = workbook.Styles[workbook.Styles.Add()];

                StyleFlag flag = new StyleFlag();<br/>
                flag.NumberFormat = true;<br/>
                style.Custom = "0.00";<br/>
                worksheet.Cells.Columns[0].ApplyStyle(style, flag);

Hi Paam,


Thank you for contacting Aspose support.

Your provided code snippet seems to apply the custom format on the first column regardless of the searched term. Please amend the code snippet as follow to make it work. Attached are the input & output spreadsheets for your reference.

C#

var workbook = new Workbook(“D:/temp/book1.xlsx”);
var worksheet = workbook.Worksheets[0];
string ColHeader = “MyColHeader”;
Cell cell = worksheet.Cells.Find(ColHeader, null, new FindOptions() { LookAtType = LookAtType.Contains });
Style style = workbook.Styles[workbook.Styles.Add()];
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
style.Custom = “0.00”;

//Get Column Index
int column = cell.Column;
//Apply style to complete column
worksheet.Cells.Columns[column].ApplyStyle(style, flag);

workbook.Save(“D:/temp/output.xlsx”);

In case the problem persists, please provide us your sample spreadsheet for our review.

Hi Babar,





I actually had the FindOptions set to LookAtType.EntireContent to ensure the exact column I wanted to change the format. In this case for demonstration purposes, it is the first column.





Does it make a different if the file is csv instead of xls? The piece of code I have used to work for xls until now I am trying to work with a csv file. Please see the attached screenshot for the actual change indeed applied the custom styling code.





Please note the screenshot reflect my actual worksheet which has over 100 columns so in this case the index is 12 instead of 0.







I have also attached a sample csv file here.

Babar,

I figured it out. This is now making sense to me why the same piece of code works before but not now.

The issue is that the column I’m trying to convert the datatype to numberic value starts with a blank cell. When I attempt export this to a datatable, aspose automatically assumes that it’s a string column no matter how I tried to apply the style. However, if the column I try to work with starts with a numeric value, it will convert correctly when I export the worksheet to a datatable.

You can try it out with the test.csv I attached earlier by adding/removing a numeric value in the first cell, which is blank.

Does Aspose has a suggestion for this issue or any plan to fix this?

Thanks!

Hi Paam,


Thank you for elaborating your scenario further.

We have been able to notice the behavior presented in your recent post. When a CSV column has first blank cell, the Cells.ExportDataTable assigns the string data type to that particular column. We have logged this behavior in our bug tracking system for further investigation under the ticket CELLSNET-42950.

Please note, you can always control what data type should be assigned to a particular columns using the ExportTableOptions class. Please check the below provided code snippet that forces the API to consider the first column of your provided CSV as double.

C#

//Create an instance of LoadOptions to tell the API that you are going to load a CSV
var loadOptions = new LoadOptions(LoadFormat.CSV);

//Load a CSV in an instance of Workbook
var book = new Workbook(“D:/temp/test.csv”, loadOptions);

//Get the first worksheet containing the data
var sheet = book.Worksheets[0];

//Create an instance of ExportTableOptions
var options = new ExportTableOptions();

//Set ExportColumnName property to false, as only data needs to be exported
options.ExportColumnName = false;

//Initialize the DataTable property of ExportTableOptions class to a new DataTable
options.DataTable = new DataTable();

//Set data types for different columns
options.DataTable.Columns.Add(“Quantity”, typeof(double));

//Export data to an instance of DataTable by passing the instance of ExportTableOptions
var table = sheet.Cells.ExportDataTable(1, 0, sheet.Cells.MaxDataRow, sheet.Cells.MaxDataColumn + 1, options);

Console.WriteLine(table.Columns[0].DataType);

Thank you. Let me when you found a resolution for CELLSNET-42950.

Thanks again!

Hi Paam,


We have analyzed the ticket logged earlier as CELLSNET-42950. I am afraid, the presented behavior is expected, and we cannot change it. In order to determine the column’s data type, we check the first cell’s value type, and not its custom number format. If the first cell in the column is blank, the data type for that particular column is string.

You may have to opt the approach elaborated in my previous response. However, it will involve setting the data type for each and every column in the CSV.