Excel Styles

Aspose.Cells Product Family
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!!!

4

@DTRWRH,
Aspose.Excel is no more available and discontinued. A new product Aspose.Cells has replaced it and contains all the features of its predecessor as well as support for different features available in different versions of MS Excel. You can set styles in a variety of ways as depicted in the following sample code.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];

// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");

// Create a style object
Style style = cell.GetStyle();

style.Font.Name = "Arial";
style.Font.IsBold = true;
style.Font.IsItalic = true;
style.Font.Color = Color.Red;

// Setting the line style of the top border
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;

// Setting the color of the top border
style.Borders[BorderType.TopBorder].Color = Color.Black;

// Setting the line style of the bottom border
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;

// Setting the color of the bottom border
style.Borders[BorderType.BottomBorder].Color = Color.Black;

// Setting the line style of the left border
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;

// Setting the color of the left border
style.Borders[BorderType.LeftBorder].Color = Color.Black;

// Setting the line style of the right border
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;

// Setting the color of the right border
style.Borders[BorderType.RightBorder].Color = Color.Black;

// Apply the border styles to the cell
cell.SetStyle(style);

worksheet.AutoFitColumns();
// Saving the Excel file
workbook.Save("book1.out.xlsx");

Refer to the following article for more information about Cells Formatting:
Cells Formatting

For testing this code, get a free trial version here:
Aspose.Cells for .NET (Latest Version)

A comprehensive project is available here which contains hundreds of ready to run examples for testing different features of this product.