Excel Styles


#1

Hopefully, I can sum this up coherently!

I just upgraged to the 3.2.0.0 version, uninstalling the previous version and running the new msi file. I have also installed the new license file.

In my coding logic, which I don’t use templates, I discovered during my initial developement with the previous version that if I changed the precision formatting on a style, it effected all of the cells using the style…which I expected.

So I have created C# methods for handling cell formatting of numbers and formulas, using a parameter to set the decimal percision. In the old version, I created a default style, with the color and text formatting I wanted to use, and called the following method:

public void CellFormula(string formula, int decimals, int row, int column, string style) {

Aspose.Excel.Style _style;

//…generate the fromatting string

string _sFormat = “#,##0”;

if (decimals != 0) {

_sFormat += “.”;

for (int _iIndex = 1; _iIndex <= decimals; _iIndex++) {_sFormat += “0”;}

}

//…add the negative formatting

_sFormat += “;[Red]-” + _sFormat;

//…create a new style (if needed), based on the requested style, to keep the formatting from updating ALL cells using the style

try {

_style = _moExcel.Styles[style + “Formula” + decimals.ToString()];

} catch (Exception) {

_miStyleIndex = _moExcel.Styles.Add();

_style = _moExcel.Styles[_miStyleIndex];

_style.Font.Name = _moExcel.Styles[style].Font.Name;

_style.Font.Size = _moExcel.Styles[style].Font.Size;

_style.Font.Color = _moExcel.Styles[style].Font.Color;

_style.Font.IsBold = _moExcel.Styles[style].Font.IsBold;

_style.ForegroundColor = _moExcel.Styles[style].ForegroundColor;

_style.HorizontalAlignment = _moExcel.Styles[style].HorizontalAlignment;

_style.VerticalAlignment = _moExcel.Styles[style].VerticalAlignment;

_style.Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.LeftBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.TopBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.TopBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.RightBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.RightBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.BottomBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.BottomBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Custom = _sFormat;

_style.Name = style + “Formula” + decimals.ToString();

}

//…get the cell, set the style, and add the formula

Aspose.Excel.Cell _oCell = _moWorksheet.Cells[row, Convert.ToByte(column)];

_oCell.Style = _style;

_oCell.Formula = formula;

_oCell = null;

}

You’ll notice the TRY {} CATCH(){}…in the old version, this would fail with an error and would thus create me a new style, based on the orginal style, only the formatting would be specific to the decimal precision I requested. If I called this method a second time, then the style would already have been created and it would use it instead of creating a new style.

In the new version, however, the TRY {} CATCH(){} is not failing when I set the style object to a “non-existent” style. I simply end up with a NULL style object, resulting in no formatting taking place.

Any suggestions would be very helpful.
Thanks!
DTRWRH (what a user name…eh?!?)


#2

Yes. We change the routine if a style is not found with its name. Using try-catch in normal program work flow is not recommended and will slow down the program.

In your code, you can change it to:

_style = _moExcel.Styles[style + “Formula” + decimals.ToString()];

if(_style == null)
{

_miStyleIndex = _moExcel.Styles.Add();

_style = _moExcel.Styles[_miStyleIndex];

_style.Font.Name = _moExcel.Styles[style].Font.Name;

_style.Font.Size = _moExcel.Styles[style].Font.Size;

_style.Font.Color = _moExcel.Styles[style].Font.Color;

_style.Font.IsBold = _moExcel.Styles[style].Font.IsBold;

_style.ForegroundColor = _moExcel.Styles[style].ForegroundColor;

_style.HorizontalAlignment = _moExcel.Styles[style].HorizontalAlignment;

_style.VerticalAlignment = _moExcel.Styles[style].VerticalAlignment;

_style.Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.LeftBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.TopBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.TopBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.RightBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.RightBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Borders[Aspose.Excel.BorderType.BottomBorder].LineStyle = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.LeftBorder].LineStyle;

_style.Borders[Aspose.Excel.BorderType.BottomBorder].Color = _moExcel.Styles[style].Borders[Aspose.Excel.BorderType.BottomBorder].Color;

_style.Custom = _sFormat;

_style.Name = style + “Formula” + decimals.ToString();

}


#3

DUH! Don’t know why I didn’t think of that! Thanks Alot!!!