Convert Excel Files to HTML and add a header row/col

Aspose.Cells Product Family
1

Trying to convert to HTML and add header row and column like AsposeCells Webgrid
A B C D
1 col1 col2 col3 col4
2 aa
3
4
5

Here are the steps that were suggested in another thread, which do NOT work and mess up the formulaes as they now point to wrong cells.

  • Insert an empty row & column at index 0.
  • Insert column names such as A, B, C in the first row. Similarly, insert row numbers such as 1, 2, 3 in the first column. They will serve as the header column & row respectively.
  • Apply styles on the header column & row as they look in the MS Excel application.
  • Create a range of cells covering the original data, and apply styles to replicate the grid view.

Here is my code and attached is original excel and output html files.zip (22.0 KB)

    Workbook book = new Workbook("C:\\Simple.xlsx");
    int worksheetscount = book.Worksheets.Count;

    Worksheet sheet = book.Worksheets[0];

    sheet.Cells.InsertColumn(0,true);
    sheet.Cells.InsertRow(0);

    sheet.Cells.Rows[0].Height = 15.75;
    sheet.Cells.Columns[0].Width = 4.75;

    int maxRow = 200;
    int maxCol = 200;
    sheet.PageSetup.PrintArea = "D1:K13";
    sheet.PageSetup.PrintTitleRows = "$5:$7";
    sheet.PageSetup.PrintTitleColumns = "$A:$B";

    for (int i = 1; i <= maxRow; i++)
    {
        sheet.Cells[i, 0].PutValue(i);
    }
    for (int j = 1; j <= maxCol; j++)
    {
        sheet.Cells[0, j].PutValue(CellsHelper.ColumnIndexToName(j - 1));
    }

    Aspose.Cells.Style style = book.CreateStyle();
    style.ForegroundColor = Color.LightGray;
    style.Pattern = BackgroundType.Solid;
    style.Font.IsBold = true;
    style.VerticalAlignment = TextAlignmentType.Center;
    style.HorizontalAlignment = TextAlignmentType.Center;
    style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
    style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = Color.Black;
    style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin;
    style.Borders[Aspose.Cells.BorderType.RightBorder].Color = Color.Black;
    style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin;
    style.Borders[Aspose.Cells.BorderType.TopBorder].Color = Color.Black;
    style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
    style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = Color.Black;

    StyleFlag styleFlagHeader = new StyleFlag();
    styleFlagHeader.All = true;
    sheet.Cells.Rows[0].ApplyStyle(style, styleFlagHeader);
    sheet.Cells.Columns[0].ApplyStyle(style, styleFlagHeader);

    Aspose.Cells.Range range = sheet.Cells.CreateRange("B1", CellsHelper.ColumnIndexToName(maxCol) + (maxRow + 1));
    StyleFlag styleFlagGrid = new StyleFlag();
    styleFlagGrid.Borders = true;
    range.ApplyStyle(style, styleFlagGrid);

    book.Save("C:\\output.html", new HtmlSaveOptions());
2

@cary.arvidson,
I have tried to reproduce this scenario here but could not observe the issue. Could you please explain the issue by sharing the expected output along with the images showing issues in the current output. Also explain the following:

I have shared the comparison of the template file and output HTML file where no difference is observed and all the formulas are showing correct values in the expected cells.
Comparison.PNG (34.1 KB)

3

You are correct values of the formulaes displayed in cells is correct.
But if you look closely for cell A7, formulae is =SUM(A2:A4), but in HTML, even though result is same in A7, the formulae is now x:fmla="=SUM(B3:B5)" which is wrong.
This is happening for all formulaes in HTML files.
See attached screenshot to understand better.error.png (74.9 KB)

4

@cary.arvidson,
It does not seem to be an issue as it is expected behavior. You may save the output file into XLSX format also along with the HTML format. Now open the output XLSX file in MS Excel and analyze it as you have added one row in the start and one column in the start, so the formulas will be updated accordingly. You will see that even if you perform these steps in MS Excel and add one row and one column, the formulas will be updated as =SUM(B3:B5). Hence it is not a bug in Aspose.Cells.

XLSX-Output.PNG (24.5 KB)

5

I think you are missing the point. The formulaes are updating, but now they appear wrong after adding a header/col as was shared by support team.
So is there any other way to add a header/col while saving as html???

6

@cary.arvidson,
Could you please share an expected output HTML generated by MS Excel after performing same steps i.e. adding one row and one column in the beginning? We will compare the MS Excel generated HTML with the HTML file generated by Aspose.Cells and share our feedback.

7

I am still stuck on this issue on how to add a header row/col into HTML. The approach here is not working as HTML gets wrong formulaes inside it. My intention is not to open generated html back into excel, but display html and that html has wrong formulae.

I have tried to explain this several different times, but I am not getting any help/answer.

8

@cary.arvidson,
Please spare us little time to analyze your issue. We will write back here soon to share our feedback.

9

@cary.arvidson,
We have tried different ways to achieve it but it is not possible to keep the formula same whereas the values have changed their location. We can only provide you assistance if you can achieve this requirement using MS Excel only. Open your template file in MS Excel and add one row and one column and then save it as HTML. If you get desired result by any means using MS Excel, please share the steps with us. We will provide you assistance immediately to achieve the same using Aspose.Cells otherwise I am afraid that it is not possible.

10

Can you add a different feature where when we convert to html, you can add header rows and cols in html?

11

@cary.arvidson,
Aspose.Cells resembles the behavior of MS Excel which does not provide any such feature, hence it is not possible to achieve it using this library. However we have logged it in our database for further investigation in this regard. You will be notified here once any update is available for sharing.

This requirement is logged as:
CELLSNET-47601 - Render extra row and column in HTML without disturbing formulas/references to resemble the output with MS Excel

12

@cary.arvidson,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

13

@cary.arvidson,

Please try our latest version/fix: Aspose.Cells for .NET v20.9.7 (attached)
Aspose.Cells20.9.7 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.9.7 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.9.7 For .NetStandard20.Zip (5.4 MB)

Your issue should be fixed in it. See the sample code for your reference:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "Simple.xlsx");
HtmlSaveOptions options = new HtmlSaveOptions();
options.ExportHeadings = true;
wb.Save(filePath + "out.html", options);

Let us know your feedback.

14

The issues you have found earlier (filed as CELLSNET-47601) have been fixed in Aspose.Cells for .NET v20.10. This message was posted using Bugs notification tool by Amjad_Sahi. You may also get the version @ Nuget repos. (NuGet Gallery | Aspose.Cells 20.10.0).