Bug: Excel.Worksheets.AddCopy() when Range.Style is set

If I set a new style on a range on a worksheet, then copy that worksheet using Excel.Worksheets.AddCopy(0) I get an “Object reference not set…” error. The code to replicate this is below. Following that is the full (and fully obfuscated) call stack.

Template.xls is a blank Workbook created with Excel 2002 SP2. Code is run against Aspose.Excel 1.8.5.0.


excel.Open(templateFile);

// Next line results in error thrown in AddCopy
excel.Worksheets[0].Cells.CreateRange(1,1,true).Style=excel.Styles[excel.Styles.Add()];

// Next line works fine with AddCopy
excel.Worksheets[0].Cells[0,0].Style = excel.Styles[excel.Styles.Add()];

excel.Worksheets.AddCopy(0);


[NullReferenceException: Object reference not set to an instance of an object.]
Aspose.Excel.Range.a(Range A_0)
Aspose.Excel.Cells.a(Cells A_0, Boolean A_1)
Aspose.Excel.Worksheet.a(Worksheet A_0)
Aspose.Excel.Worksheets.AddCopy(Int32 sheetIndex)
WebApplication8.WebForm1.Page_Load(Object sender, EventArgs e)
____________________________________________________________________

Regards,

– Josh

Hi Josh,

Sorry for this bug. Please download the fix 1.8.5.1 and have a try.


Thanks for your feedback.

By the way, please don’t use CreateRange function to create a columm range and set style to it. That will increase the file size too much.
I will optimize this by adding a new API to enable you setting style of a column.

Now please specifically set a range of cells. For example,

excel.Worksheets[0].Cells.CreateRange(1, 1, 1, 100).Style=excel.Styles[excel.Styles.Add()];

That will decrease file size and improve performance.

Thanks for the crash-fix and the performance tip, Laurence. In my ‘real’ code I hadn’t been setting styles for columns in that way, but it’s useful to know that it results in large file sizes.

I’m trying out the fix now and although it no longer crashes, it also does not copy any aspect of the style over to the new sheet. If I name the style, select a cell in the first sheet then choose Format->Style in Excel I can see that it’s using my new style. Choosing a cell in the new sheet and then Format->Style shows that it’s using the “Normal” style.

Using Aspose.Excel 1.8.5.1

Example code below:


// Assign borders colours and font attributes to the top left cells.
Aspose.Excel.Style s = excel.Styles[excel.Styles.Add()];
s.Name = “Some New Style”;

s.Borders[BorderType.TopBorder].LineStyle =
s.Borders[BorderType.BottomBorder].LineStyle =
s.Borders[BorderType.LeftBorder].LineStyle =
s.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Double;

excel.ChangePalette(Color.BlueViolet, 33);
s.ForegroundColor = Color.BlueViolet;
s.Font.Color = Color.Red;
s.Font.IsBold = true;
excel.Worksheets[0].Cells.CreateRange(0,0,3,3).Style = s;

// Put a value in the middle.
excel.Worksheets[0].Cells[1,1].PutValue(“Hello World”);

// Add Copy. “Hello World” gets copied over, but no aspect of the style does.
excel.Worksheets.AddCopy(0);



Hi Josh,

Please download fix 1.8.5.2. It fixed above problem.

We would like to inform that advanced version of Aspose.Excel is released named Aspose.Cells. The previous product Aspose.Excel is discontinued now and no more upgradations are done. This new product Aspose.Cells is quite ahead in features and performance when compared with Aspose.Excel. All the features are incorporated in this new product which are supported by different versions of MS Excel. Copying and moving worksheets feature is available in this new product also as shown in the following sample code:

// Open an existing Excel file.
Workbook wb = new Workbook(InputPath);

// Create a Worksheets object with reference to
// the sheets of the Workbook.
WorksheetCollection sheets = wb.Worksheets;

// Copy data to a new sheet from an existing
// sheet within the Workbook.
sheets.AddCopy("Sheet1");

// Save the Excel file.
wb.Save(dataDir + "CopyWithinWorkbook_out.xls");

For moreinformation on this topic, have a look at the following article:
Copying and moving sorksheets

The latest version is this product is available here:
Aspose.Cells for .NET (Latest Version)

A working solution with variety of examples can be ddownloaded here.