Save Excel file (having merged cells) as Html throws null reference exception in .NET

Aspose.Cells Product Family
1

I’m encountering an issue that a null reference exception thrown when saving excel workbook to html (in either file or memory strea) when there are cells merged, everything is fine when there are no cells merged.

Here is the steps to reproduced it:

  1. load this worksheet test_data.zip (24.2 KB) by using Aspose.Cells

    var workbook = new Workbook(“test_data.xlsx”);

  2. call below function to merge cells covered by charts/images

     MergeCells(workbook.Worksheets[0]);

 static void MergeCells(Worksheet worksheet)
 {
     foreach (var pic in worksheet.Pictures)
     {
         var firstRow = pic.UpperLeftRow;
         var firstCol = pic.UpperLeftColumn;
         var rowCount = pic.LowerRightRow - pic.UpperLeftRow;
         var colCount = pic.LowerRightColumn - pic.UpperLeftColumn;
         if (rowCount > 0 && colCount > 0)
             worksheet.Cells.Merge(firstRow, firstCol, rowCount, colCount);
     }

     
     foreach (var chart in worksheet.Charts)
     {
         var chartObj = chart.ChartObject;
         var firstRow = chartObj.UpperLeftRow;
         var firstCol = chartObj.UpperLeftColumn;
         var rowCount = chartObj.LowerRightRow - chartObj.UpperLeftRow;
         var colCount = chartObj.LowerRightColumn - chartObj.UpperLeftColumn;
         if (rowCount > 0 && colCount > 0)
             worksheet.Cells.Merge(firstRow, firstCol, rowCount, colCount);
     }
 }

  3. tries to save it into a memory stream

         using (var ms = new MemoryStream())
     {
         var options = new Aspose.Cells.HtmlSaveOptions(SaveFormat.Html);
         options.ExportActiveWorksheetOnly = true;
         options.ExportImagesAsBase64 = true;
         workbook.Save(ms, options);
     }

And I get null reference exception inside the Save method, there is no detail explaining what happend, just a null reference.

2

@mchen11,

Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found NullReferenceException occurred when saving Excel workbook (having merged cells in it) to HTML file format.

I have logged a ticket with an id “CELLSNET-45486” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

3

Thanks for looking into this.
For now I have found a workaround which is to add empty rows/columns around the “MaxDisplayRange”. And set the empty columns to be 0 width or empty rows to 0 height.

4

@mchen11,

Good to know that you have found a workaround to cope with your issue for the time being. But, surely, we will try to sort out your issue, so you don’t have to use the workaround.

Please spare us little time to evaluate your issue precisely.

Thank you.

5

@mchen11,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-45486”) now. We will soon provide you the fix after performing QA and incorporating other enhancements and fixes.

Once the fix is available for public use, we will share the Download link here.

Thank you.

6

@mchen11,

Please try our latest version/fix: Aspose.Cells for .NET v17.6.7:

Let us know your feedback.

Thank you.

7

Thanks~ unfortunately I’m not able to access dropbox, this is blocked in China and also block by the company.

8

@mchen11

Thanks for using Aspose APIs.

Please download these two files and place them in some folder. Then remove its extensions

.remove.zip

Then these files will become

Aspose.Cells17.6.7.0 For .Net4.0-In Parts.part1.rar
Aspose.Cells17.6.7.0 For .Net4.0-In Parts.part2.rar

Now extract its contents using WinRAR.

Download Links
Aspose.Cells17.6.7.0 For .Net4.0-In Parts.part1.rar.remove.zip (2 MB)
Aspose.Cells17.6.7.0 For .Net4.0-In Parts.part2.rar.remove.zip (1.3 MB)

9

couldn’t download them…
Not sure if it was blocked by the company, I’ll try that when I’m home

10

@mchen11,

Please email me, I will then send you a fix via email.

Here is my email address.

s****@aspose.com

11

The issues you have found earlier (filed as CELLSNET-45486) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

12

The issues you have found earlier (filed as CELLSNET-45486) have been fixed in this update.

13

Thanks, we’ve used the latest assembly you provided and the issue has gone.

But I noticed another issue probably is still related to this. I updated my workbook and attached here test_data_more_data_and_charts.zip (50.9 KB)

  1. As you can see the exported email, there are a few issues in one data grid
    bug_aspose.jpg (120.3 KB)

  2. And if you scroll down the email, you can also see some text, a data table missing
    bug_aspose_2.jpg (50.3 KB)

if you change the background of the data table described in 1),say, to green, what’s interesting is the data table, the texts are back but the background of some areas goes all green after that data table
aspose_issue.png (224.5 KB)

Not sure if this should be in a separate thread, sorry if this is not supposed to be here

14

@mchen11,

Well, as you are merging cells (as per your original code) for shapes, charts and data in the sheet, so I think your own code is causing the issue regarding missing data/tables and formattings, etc. I think as there are some diverse shapes, charts and data tables on the sheet, so it would be hard to merge the cells effectively without disturbing the data and shapes. I would recommend you to kindly simply render your Excel file to HTML file format, see the sample code that I tried and got good results:
e.g
Sample code:

var workbook = new Workbook("e:\\test2\\test_data_more_data_and_charts.xlsx");
workbook.Save("e:\\test2\\out1.html");

Thank you.

15

I only merged the cells that are covered by the charts, not those data grid. I merged cells because it doesn’t show correct in outlook email message as css “position: absolute;" is not supported in outlook, merging cells is my solution to this (ref : https://forum.aspose.com/t/how-to-export-charts-in-worksheet-to-vml-openxml-in-a-html-file/468/10 ).

I removed the code merging cells and exported the html as the code you showed, and I noticed some thing that I don’t quite understand. For example,

why those cells get merged
sdfsdf.jpg (161.2 KB)

2323.jpg (130.1 KB)

16

@mchen11,

Thanks for the screenshots.

I could not quite understand your issue. I have tested by converting both Aspose.Cells APIs and MS Excel (manually), both works almost the same way. I have attached (below) the output HTMLs by Aspose.Cells and MS Excel for your reference. Could you check and give us some more screenshots what issues you found with the output HTML generated by Aspose.Cells APIs, we will check and log a ticket for it accordingly.
e.g
Sample code:

 var workbook = new Workbook("e:\\test2\\test_data_more_data_and_charts.xlsx");
            workbook.Save("e:\\test2\\out1_test_data_more_data_and_charts1_Aspose_Cells.html");

files1.zip (689.7 KB)

Thank you.

17

okay, my purpose is not to convert it to HTML but to Outlook Email, could you pls convert that to Email message?

18

@mchen11,

Well, you may try to do it by yourselves using both output HTMLs (by Aspose.Cells and MS Excel). Aspose.Cells only converts the Excel to HTML and mostly the same way as MS Excel does. The output is a kind of HTML which is MS Excel oriented, it might not be a common HTML. For HTML to Outlook email, I think you may make use of Aspose.Email for the purpose.

Thank you.