I need to set default date and number format at the workbook level.
I found few threads where I see apply styles for a specific cell or column but I am not looking for that.
In my case I have a work book with 5 date fields and 5 number fields and I cannot find the column names because its not fixed. in this case I need to apply ‘mm-dd-YYYY’ format for all the date fields and 0.00 format for all the number fields.
How can I do that?
Hi,
Thanks for your posting and using Aspose.Cells.
Please use the Workbook.DefaultStyle property for your needs. However, you will have to either set date format or number format. You cannot set both.
Please see the following code for your reference. I have also attached the output excel file generated by it. Please open it and add some date in any cell and it will automatically be formatted to mm-dd-YYYY.
C#
Workbook workbook = new Workbook();
//You can not set Number and Custom together
Style st = workbook.DefaultStyle;
// st.Number = 5;
st.Custom = “mm-dd-YYYY”;
workbook.DefaultStyle = st;
workbook.Save(“output.xlsx”);
Thank you for your quick response!
Is there any way I can find all Date columns/ Numeric columns with in a work sheet? So that I can apply style only to those columns?
Hi Krish,
Thank you for writing back.
Please note, the
Cell class has a member by name
Type which corresponds to the
CellValueType enumeration and can be used to detect the value type of a cell. If you do not have mixed data types in a particular column then you can use the aforementioned property to detect the type of all cells in any row. The
Cell object also contains the reference to column index in which the cell resides so you can use the following piece of code to apply column styles based on type of the cell value. Check the detailed article on
how to set the formatting for numeric & date values.
Please note, you may need to amend the code if the first row in your Worksheet contains the column headers.
C#
var book =
new Workbook(dir + “book1.xlsx”);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
var dateStyle = book.CreateStyle();
dateStyle.Custom = “dd-mmm-yyyy”;
var numberStyle = book.CreateStyle();
numberStyle.Custom = “#,#
#0.00”;
for (
int col = 0; col < cells.MaxDataColumn+1; col++)
{
var cell = cells[0, col];
if (cell.Type == CellValueType.IsDateTime)
{
cells.Columns[cell.Column].ApplyStyle(dateStyle,
new StyleFlag() { NumberFormat =
true });
}
else if (cell.Type == CellValueType.IsNumeric)
{
cells.Columns[cell.Column].ApplyStyle(numberStyle,
new StyleFlag() { NumberFormat =
true });
}
}
book.Save(dir + “output.xlsx”);
else if (cell.Type == CellValueType.IsNumeric)
{
cells.Columns[cell.Column].ApplyStyle(numberStyle, new StyleFlag() { NumberFormat = true });
}
Is there any way I can distinguish Decimal and Integer?
Hi Krish,
I am afraid, you have to implement your own custom logic to detect if a number is an Integer or Decimal because Aspose.Cells APIs do not provide any such mechanism. However, if you search internet, you will find a number ways to accomplish this task. For instance, please check the method
isNumeric acquired from
this article that can be helpful in distinguishing between whole & decimal values.
Note: The code has been shared here for demonstration purposes, and may require tweaking/amendments based on your application requirements.
C#
public static bool isNumeric(string val, System.Globalization.NumberStyles NumberStyle)
{
Double result;
return Double.TryParse(val, NumberStyle,
System.Globalization.CultureInfo.CurrentCulture, out result);
}
var book = new Workbook(dir + "book1 (2).xlsx");
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
var dateStyle = book.CreateStyle();
dateStyle.Custom = "dd-mmm-yyyy";
var numberStyle = book.CreateStyle();
for (int col = 0; col < cells.MaxDataColumn + 1; col++)
{
var cell = cells[0, col];
if (cell.Type == CellValueType.IsDateTime)
{
cells.Columns[cell.Column].ApplyStyle(dateStyle, new StyleFlag() { NumberFormat = true });
}
else if (cell.Type == CellValueType.IsNumeric)
{
if (isNumeric(cell.Value.ToString(), System.Globalization.NumberStyles.Integer))
{
numberStyle.Custom = "#,##0";
cells.Columns[cell.Column].ApplyStyle(numberStyle, new StyleFlag() { NumberFormat = true });
}
else if (isNumeric(cell.StringValueWithoutFormat, System.Globalization.NumberStyles.Float))
{
numberStyle.Custom = "#,##0.000";
cells.Columns[cell.Column].ApplyStyle(numberStyle, new StyleFlag() { NumberFormat = true });
}
}
}
book.Save(dir + "output.xlsx");
Hello,
I dont see the property "StringValueWithoutFormat" for the cells do we have an alternative?
Hi Krish,
Please note that the
Cell.StringValueWithoutFormat was added with the release of Aspose.Cells for .NET 8.1.0. Please check the
public API changes for the said release. Please check if you have an overload version of
Cell.GetStringValue method that could accept a parameter from enumeration
CellValueFormatStrategy. If yes, please review
this article for details on how you can extract the un-formatted string value from a cell.
If your current version of the API does not allow you to retrieve the un-formatted value using any of the above mentioned approached then I am afraid, there are no other alternatives.
I am tryingout cell.Value.ToString() will it work? I am not in a position to upgrade the version of Aspos.Cells
Hi,
Well, you may try Cell.Value.ToString() but it may not work accurately and all the time. Also, as Babar told you and since both suggested APIs (i.e., Cell.StringValueWithoutFormat and Cell.GetStringValue() with CellValueFormatStrategy enum etc.) are added into recent releases, so I am afraid, there is no alternative but to upgrade to latest APIs set of Aspose.Cells.
Thank you.
we already have the lisense.
Can you share the lastest version download link?
Thank you! just one issue left now.. if the cell value is "0.00", cell.StringValueWithoutFormat returns "0" and hence it considered as integer value. I need it return with decimal places "0.00"
similarly if the value is "123,322.00" it returns 123322. How to mitigate that?
Hi Krish,
Thank you for writing back.
I believe you wish to get the formatted values so you may use Cell.Value property instead. Please note, the zeros at the right side of the decimal point appears due to the number format applied to the cells. If you reset the format to Text or General, the said zeros will disappear. However, if you insert a value with certain decimal points while cell format is Text, you can retrieve them using Cell.Value property.
Furthermore, please note that when user input value to a cell, Excel will convert the input string to proper value and type automatically. Such as when you input a numeric value in Excel, in the saved excel file, the data for this cell is saved as numeric value, not the original string you input. You may confirm this by creating a new spreadsheet in Excel, without changing the format of the cell, input 1.00 in A1, save the file in XLSX format. Next, rename the file to .zip and inspect the sheet1.xml, you will notice that Excel has stored the value as 1 rather 1.00.
I am getting the below error now. Can you please let me know?
The subscription included in this license allows free upgrades until 09 Apr 2016, but this version of the product was released on 20 Apr 2016. Please renew the subscription or use a previous version of the product.