I want to get styles, formatting of table in excel file and then return that table in html string

Amrinder_Singh · June 6, 2023, 12:59pm

@eduardo.canal
I have this:

Table in Excel I want to get this table and convert to html string with html tags or anyway I can get styling, formatting of that excel table?

eduardo.canal · June 6, 2023, 2:06pm

@Amrinder_Singh please note that Aspose.Words API does not provide features to manage Excel files (xlsx, xls, etc.). I recommend considering the use of Aspose.Cells API, which is specifically designed for managing such files. It will be easier to achieve your desired functionality using Aspose.Cells API.

Amrinder_Singh · June 6, 2023, 2:11pm

Sorry for adding Aspose.Words but im using Aspose.Cells only. Right now im getting styling in the cells which i extracted from excel sheetimage.png (4.0 KB)
Like shown in image but how can i convert those cells to table in the form of html string

amjad.sahi · June 6, 2023, 2:28pm

@Amrinder_Singh,

Using Cell.HtmlString attribute, you may get the formatting and data in HTML (tags) string individually for each cell. To get/render complete HTML file with tags, you may convert the Excel workbook to HTML via Aspose.Cells, see the document for your reference.

Hope, this helps a bit.

Amrinder_Singh · June 7, 2023, 7:24am

@eduardo.canal @amjad.sahi
I got the styling, can you tell me if i have merged cell in my uploaded excel then i want to return that as html string alsoimage.png (1.8 KB)

amjad.sahi · June 7, 2023, 9:40am

@Amrinder_Singh,

Thanks for the screenshot.

Could you please zip and attach your template Excel file and your current sample code. We will check it soon. I think you may unmerge the cells and then try getting HTML string for individual cells accordingly.

Amrinder_Singh · June 7, 2023, 10:01am

@eduardo.canal @amjad.sahi
My requirement is I have this Excel fileimage.png (2.9 KB)

I upload it and after opening that file I have data in Cells Objects.
In Cells i have Array field called mergedCells(in which I have the merged cells values e.g [“A2:C2”]
So somehow i want to convert this cells to table(with table tags) and return in htmlString.

private static void MergeCells(Cells cells)
{
var count = 0;
string rangeAdress = “”;
Aspose.Cells.Range range = null;
foreach(var currentCell in cells)
{
var cell = cells[count];
var worksheet = cell.Worksheet;
range = cell.GetMergedRange();

            if (range != null)
            {
                range.EntireRow.Merge();
                //range.Merge();
            }
            count++;
        }
        if(range != null)
        cells.Merge(range.FirstRow, range.FirstColumn, range.RowCount, range.ColumnCount);


    }

amjad.sahi · June 7, 2023, 10:29am

@Amrinder_Singh,

I have tested your scenario/case using a simple template XLSX file (attached in the zipped archive). The worksheet has A2:C2 cells as merged having value/data “12” with font settings (Calibri, 12 with red color). I used the following sample code and it works as expected, the output HTML string for individual cells in A2:C2 range is ok.
e.g.
Sample code:

Workbook workbook = new Workbook("g:\\test2\\Bk_merged1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

Cell cell = worksheet.Cells["A2"];
Console.WriteLine(cell.HtmlString);

Cell cell1 = worksheet.Cells["B2"];
Console.WriteLine(cell1.HtmlString);

Cell cell2 = worksheet.Cells["C2"];
Console.WriteLine(cell2.HtmlString);

output:

<Font Style="FONT-FAMILY: Calibri;FONT-SIZE: 12pt;COLOR: #ff0000;TEXT-ALIGN: center;">12</Font>
<Font Style="FONT-FAMILY: Calibri;FONT-SIZE: 12pt;COLOR: #ff0000;TEXT-ALIGN: center;"></Font>
<Font Style="FONT-FAMILY: Calibri;FONT-SIZE: 12pt;COLOR: #ff0000;TEXT-ALIGN: center;"></Font>

Bk_merged1.zip (6.4 KB)

Amrinder_Singh · June 7, 2023, 10:41am

Yes, But can i somehow only get the first value and remove the empty ones? for only the merged cells(horizontally or veritically?)

amjad.sahi · June 7, 2023, 11:01am

@Amrinder_Singh,

Please note, when you merge cells (horizontally or vertically) in MS Excel manually, you will get one cell (top-left) while other cells in the range share the formatting of the merged cell. You have to devise your code for your custom needs to get top-left cell of each merged range of cells. See the following sample code that will only extract top-left cell in each merged range, other cells in the merged range is discarded:
e.g.
Sample code:

Workbook workbook = new Workbook("g:\\test2\\Bk_merged1.xlsx");
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
// Create an arraylist object
ArrayList al = new ArrayList();
// Get the merged cells list to put it into the arraylist object
al = worksheet.Cells.MergedCells;
// Define cellarea
CellArea ca;
// Define some variables
int frow, fcol, erow, ecol, trows, tcols;
// Loop through the arraylist and get top left (merged) cells to get its HTMLString
for (int i = 0; i < al.Count; i++)
{
                ca = new CellArea();
                ca = (CellArea)al[i];
                frow = ca.StartRow;
                fcol = ca.StartColumn;
                erow = ca.EndRow;
                ecol = ca.EndColumn;
                Cell cell = worksheet.Cells[frow, fcol];
                Console.WriteLine(cell.Name + ", " +cell.HtmlString);
}

Hope, this helps a bit.

Amrinder_Singh · June 7, 2023, 11:46am

@amjad.sahi @eduardo.canal
So, can we convert this to html table? with proper html tags ?
Suppose we have some more rows and columns and have horizontal and vertical columns

amjad.sahi · June 7, 2023, 1:41pm

@Amrinder_Singh,

You have to compile HTML table by yourselves using your own code while using HtmlString attribute of individual cells in the worksheet. As I told you earlier, you can also render complete HTML file with tags from Excel workbook via Aspose.Cells, see the document with example code for your reference:
https://docs.aspose.com/cells/net/convert-workbook-to-different-formats/#converting-excel-workbook-to-html

simon.zhao · June 7, 2023, 1:45pm

@Amrinder_Singh
Could you share a template file with motioned merged area and excepted html string?
Do you want to export an area to html?

Amrinder_Singh · June 7, 2023, 2:03pm

@amjad.sahi Yes that Html table tags I can add myself but problem is i want to check if cells are merged row wise or column wise so I can insert rowspan/colspan tag accordingly to my html string.
And second problem is I want to stop when i got first element of of merge (row wise or column wise)

Amrinder_Singh · June 7, 2023, 2:04pm

@simon.zhao
image.png (3.0 KB)
This is my excel data in which one is merged row wise and other columnwise.
I want to get this excel table same as it but in html tags

amjad.sahi · June 7, 2023, 5:41pm

@Amrinder_Singh,

You may use Cell.IsMerged attribute in an if condition to know if the cell is merged and then (if true) you may use Cell.GetMergedRange() method to get the Range of cells. Now you may evaluate if range is row/column wise (e.g., using Range.RowCount, evaluate if it is >1 or not to specify vertical/horizontal area).

amjad.sahi · June 7, 2023, 5:43pm

Please zip and share template Excel file with your motioned merged area. Also, give a sample HTML file/string as per your expectations.

Amrinder_Singh · June 8, 2023, 6:22am

@amjad.sahi @simon.zhao
Merging.zip (8.8 KB)

This file contains the Current input I’m getting, and required output and the excel file which I am using.

John.He · June 8, 2023, 6:43am

@Amrinder_Singh,
We are about to release 23.6. Attached is the latest version of the test results. Please check it.out.zip (7.8 KB)

Amrinder_Singh · June 8, 2023, 6:55am

When this version will be released? Any documentation to get the results you attached?