How to detect and remove the aspose inbuilt style set on cell without loosing existing custom formatting(ie DataTime)

Hi,


We are using aspose.cells version 7.4.2.0 to implement export and import of the data from excel.

We have a requirement where we need to mark the row in ListObject as BAD. and while importing again detect if row is marked as BAD style and reset the style back to normal row. We could set the style to BAD but existing datetime format(custom) is getting lost and being converted to double.

Any idea, how can preserve the datetime formatting while applying/removing the style?

Below is the code:
MarkRowBAD(rowPos)
{
Style style = this.ExcelWorkbook.Styles.CreateBuiltinStyle(BuiltinStyleType.Bad);
//set the style to all the cells of ListObject
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
if (tableObject.DataRange[rowPos, i].Type == CellValueType.IsDateTime)
{
Style currStyle = tableObject.DataRange[rowPos, i].GetStyle();
style.Custom = currStyle.Custom;
}
tableObject.DataRange[rowPos, i].SetStyle(style);
style.Custom = “”;
}
}

Unmark(rowPos)
{
//detect:when style is not set on first and last cell of row return
if ((tableObject.DataRange[rowPos, 0].GetStyle().ParentStyle == null ||
tableObject.DataRange[rowPos, 0].GetStyle().ParentStyle.Name.ToLower() != AposeBuiltInBadStyle.ToLower()) &&
(tableObject.DataRange[rowPos, tableObject.EndColumn-1].GetStyle().ParentStyle == null ||
tableObject.DataRange[rowPos, tableObject.EndColumn - 1].GetStyle().ParentStyle.Name.ToLower() != AposeBuiltInBadStyle.ToLower()))
return;

//get normal Style
Style style = this.ExcelWorkbook.Styles.CreateBuiltinStyle(BuiltinStyleType.Normal);

//remove: set the style to all the cells of table object to normal
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
if (tableObject.DataRange[rowPos, i].Type == CellValueType.IsDateTime)
{
Style currStyle = tableObject.DataRange[rowPos, i].GetStyle();
style.Custom = currStyle.Custom;
}
tableObject.DataRange[rowPos, i].SetStyle(style);
style.Custom = “”;
}
}

Grey: I have put this code to handle the formatting of datetime. Is it correct way of doing it?
Also, please correct us we are doing is wrong?

We really appreciate your help.

Thanks,
Nikhil

Hi Nikhil,


Thank you for contacting Aspose support.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for .NET 8.3.2.3 and the following piece of code.

C#

Workbook book = new Workbook(“D:/temp/tablestyle.xlsx”);
var tableObject = book.Worksheets[0].ListObjects[0];
Style style = book.Styles.CreateBuiltinStyle(BuiltinStyleType.Bad);
//set the style to all the cells of ListObject
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
for (int j = 0; j <= tableObject.EndRow - 1; j++)
{
if (tableObject.DataRange[j, i].Type == CellValueType.IsDateTime)
{
Style currStyle = tableObject.DataRange[j, i].GetStyle();
style.Custom = currStyle.Custom;
}
tableObject.DataRange[j, i].SetStyle(style);
style.Custom = “”;
}
}
book.Save(“D:/output.xlsx”);

book = new Workbook(“D:/output.xlsx”);
tableObject = book.Worksheets[0].ListObjects[0];
//get normal Style
style = book.Styles.CreateBuiltinStyle(BuiltinStyleType.Normal);

//remove: set the style to all the cells of table object to normal
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
for (int j = 0; j <= tableObject.EndRow - 1; j++)
{
if (tableObject.DataRange[j, i].Type == CellValueType.IsDateTime)
{
Style currStyle = tableObject.DataRange[j, i].GetStyle();
style.Custom = currStyle.Custom;
}
tableObject.DataRange[j, i].SetStyle(style);
style.Custom = “”;
}
}
book.Save(“D:/output2.xlsx”);

Unfortunately, we are unable to replicate the said problem on our end so we think the problem is either sample specific or it could be a bug in your current version of the API. Therefore we would request you to please give a try to the latest version of Aspose.Cells for .NET 8.3.2.3 on your side. In case the problem persists, please provide us the problematic sample for further investigation.

Yes, it works for me also with special handling for datatime formatting.
I have commented the special handling.

Can you please try with below code:

Workbook book = new Workbook("D:/temp/tablestyle.xlsx");
var tableObject = book.Worksheets[0].ListObjects[0];
Style style = book.Styles.CreateBuiltinStyle(BuiltinStyleType.Bad);
//set the style to all the cells of ListObject
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
for (int j = 0; j <= tableObject.EndRow - 1; j++)
{
//if (tableObject.DataRange[j, i].Type == CellValueType.IsDateTime)
//{
//Style currStyle = tableObject.DataRange[j, i].GetStyle();
//style.Custom = currStyle.Custom;
//}
tableObject.DataRange[j, i].SetStyle(style);
//style.Custom = "";
}
}
book.Save("D:/output.xlsx");

book = new Workbook("D:/output.xlsx");
tableObject = book.Worksheets[0].ListObjects[0];
//get normal Style
style = book.Styles.CreateBuiltinStyle(BuiltinStyleType.Normal);

//remove: set the style to all the cells of table object to normal
for (int i = 0; i <= (tableObject.EndColumn - tableObject.StartColumn); i++)
{
for (int j = 0; j <= tableObject.EndRow - 1; j++)
{
//if (tableObject.DataRange[j, i].Type == CellValueType.IsDateTime)
//{
// Style currStyle = tableObject.DataRange[j, i].GetStyle();
//style.Custom = currStyle.Custom;
//}
tableObject.DataRange[j, i].SetStyle(style);
//style.Custom = "";
}
}
book.Save("D:/output2.xlsx");

Thanks,
Nikhil

Hi Nikhil,


Thank you for writing back.

I have tested the modified code (after commenting the statement style.Custom = currStyle.Custom) and I believe the results are expected. This is because when you apply a style to a cell or a range of cell, the existing style is completely overwritten by the new style. Moreover, when you create a new style, you have to set the appropriate properties to format the data accordingly. If you do not set the aforesaid properties then the formatting will be changed according to the default values.

For instance, when you create new styles such as BuiltinStyleType.Bad & BuiltinStyleType.Normal, these styles give you the predefined colors for cell foreground and background. Unless you specify the number or custom format for the data, the associated properties are set to their default values and thus format the data accordingly. It is suggested that you should use the statement style.Custom = currStyle.Custom to paste the current data formatting to the new style.

Thank you for your suggestion and support…!



Thank you for your understanding. Please feel free to contact us back in case you need our further assistance with Aspose APIs.