Adding a new style to a existing cell removes Date format

I create a row of data as follows. I look at each element and see if its a datetime format and then set the style to Number=14. The 1st part works fine until I try to add more styles to it.

string[] AryLines = aItem.ToString().Split('\t');

for (int Cellpos = 0; Cellpos < AryLines.GetUpperBound(0); Cellpos++)
{
wrksheet.Cells[importcounter, Cellpos].PutValue(AryLines[Cellpos], true);
// now test the data to see if its a Datetime. Because we need to change cell to display it.
DateTime dtTest;
if (Regex.IsMatch(AryLines[Cellpos], DateStringMatch))
if (DateTime.TryParse(AryLines[Cellpos], out dtTest))
wrksheet.Cells[importcounter, Cellpos].Style.Number = 14;
}

DrawGrid(wrkBook, wrksheet, 1, importcounter, intLastCol);

----

private static void DrawGrid(Workbook wrkBook, Worksheet wrkSheet, int startRow, int endRow, int lastCol)
{

//Create the style object
Style style = wrkBook.Styles[wrkBook.Styles.Add()];
//Sets font attributes
style.Font.IsBold = false;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Name = "Arial";
style.Font.Size = 10;

//Sets borders
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

DateTime dtTest;
for (int row = startRow; row <= endRow; row++)
{
for (int y = 0; y <= lastCol; y++)
{
wrkSheet.Cells[row, y].Style = style;
// addtional Note:****
}
}

}

------

Now it changes the Style number to General so now instead of 4/13/2010 I get 40281.

I even tried to replace the "addition Note. ****" with the following code. and I get the same result.

if (wrkSheet.Cells[row, y].Value != null)
if (DateTime.TryParse(wrkSheet.Cells[row, y].Value.ToString(), out dtTest))
{
Style aCellStyle = wrkSheet.Cells[row, y].GetStyle();
aCellStyle.Number = 14;
wrkSheet.Cells[row, y].SetStyle(aCellStyle);
}

How do I fix this? I need all the data in a grid.

Ed.

Hi,

Please create a sample console application, zip it and post it here with all the template excel files. We will check it soon.

And make sure that your code is correct, don’t use Style property of the Cell class to format data in as we are this attribute is going to be obsoleted in future versions. Aspose.Cells provides GetStyle and SetStyle methods of the Cell class that are used to get/set the formatting style of a cell. Aspose.Cells also provides a Style class that should be used to serve your needs for formatting. You can apply different kinds of formatting styles on the cells to set number formats upon data, set the background or foreground colors, set borders, fonts, horizontal and vertical alignments etc.

See the following documents for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-display-formats-of-numbers-dates.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/approaches-to-format-data-in-cells.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/dealing-with-font-settings.html


Thank you.

If I understand right.

wrksheet.Cells[importcounter, Cellpos].Style.Number = 14;

is no longer going to be used. I will have to do

Style aStyle = wrksheet.Cells[importcounter, Celpos].GetStyle();
aStyle.Number = 14;
wrksheet.Cells[importcounter, Celpos].SetStyle(aStyle);


Ok.. I solved it by doing this.. Thanks for the other links. Sometimes you just need to bounce it off someone.

private static void DrawGrid(Workbook wrkBook, Worksheet wrkSheet, int startRow, int endRow, int lastCol)
{
for (int row = startRow; row <= endRow; row++)
{
for (int y = 0; y <= lastCol; y++)
{
Style aCellStyle = wrkSheet.Cells[row, y].GetStyle();
aCellStyle = SetAddtionalStyles(aCellStyle);
wrkSheet.Cells[row, y].SetStyle(aCellStyle);
}
}
}

private static Style SetAddtionalStyles(Style style)
{
//Sets font attributes
style.Font.IsBold = false;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Name = "Arial";
style.Font.Size = 10;
//Sets borders
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

return style;
}