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))
You may use Cells.MinRow and Cells.MinColumn to get the first cell and use Cells.MaxRow and Cells.MaxColumn to get the last cell.
e.g.
CellArea.CreateCellArea(ws.Cells.MinRow, ws.Cells.MinColumn, ws.Cells.MaxRow, ws.Cells.MaxColumn);
I have modified the code for the problem that next row’s first cell is also being added, see the following code sample for your reference:
Sample code:
static 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[row, col];
if(cell == null)
{
continue;
}
if (cell.IsMerged)
{
Range range = cell.GetMergedRange();
if (row == range.FirstRow || col == range.FirstColumn)
{
if (range.ColumnCount > 1)
{
sb.Append("<td ");
sb.Append("colspan ='");
sb.Append(range.CellCount);
sb.Append("'");
col += range.CellCount;
}
if (range.RowCount > 1)
{
if (row == range.FirstRow)
{
sb.Append("<td ");
sb.Append(" rowspan ='");
sb.Append(range.RowCount);
sb.Append("' ");
}
else
{
continue;
}
}
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();
}Thank you this is working for now.
Thanks for your feedback and good to know the suggested code snippet works for your needs. Feel free to write us back if you have further queries or comments.