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.
@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);
}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)
Yes, But can i somehow only get the first value and remove the empty ones? for only the merged cells(horizontally or veritically?)
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.
@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
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
@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?
@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)
@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
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).
Please zip and share template Excel file with your motioned merged area. Also, give a sample HTML file/string as per your expectations.
@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.
@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)
When this version will be released? Any documentation to get the results you attached?
@John.He
And i dont want to return html i want to return html string means(excel table in table tags and added rowspan or colspan if merged cell is there)
@Amrinder_Singh
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-53504
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Please try the following code for your reference:
e.g.
Sample code:
string ToHtmlTable(Cells cells, CellArea area)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table>");
for (int row = area.StartRow; row <= area.EndRow; row++)
{
sb.Append("<tr>");
for (int col = area.StartColumn; col <= area.EndColumn; col++)
{
Cell cell = cells.GetCell(row, col);
if (cell.IsMerged)
{
Range range = cell.GetMergedRange();
if (row == range.FirstRow || col == range.FirstColumn)
{
sb.Append("<td ");
if (range.ColumnCount > 1)
{
sb.Append("colspan ='");
sb.Append(range.CellCount);
sb.Append("'");
col += range.CellCount;
}
if (range.RowCount > 1)
{
if (row == range.FirstRow)
{
sb.Append(" rowspan ='");
sb.Append(range.RowCount);
sb.Append("' ");
}
}
sb.Append(cell.HtmlString.Replace("<Font ", "").Replace("</Font>", ""));
sb.Append("</td>");
}
}
else
{
sb.Append("<td ");
sb.Append(cell.HtmlString.Replace("<Font ", "").Replace("</Font>", ""));
sb.Append("</td>");
}
}
sb.Append("</tr>");
}
sb.Append("</table>");
return sb.ToString();
}
and call it as following code:
Workbook workbook = new Workbook("g:\\test2\\Bk_merged1.xlsx");
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
string table= ToHtmlTable(worksheet .Cells, CellArea.CreateCellArea("A1", "C7"));
Hope, this helps a bit.
@Eric.wang
I got the header row, while calling createCellArea, How can i get the firstcell and last cell based on the Excel sheet?Table.docx (14.5 KB)
And there is one problem with this code. when the merge is rowwise and we use e.g rowspan = ‘3’
Then the next row’s first cell is also being added as separate cell
and giving this output (see the file attached(table.docx))