I apologize about my first file, the first one was more of a general concept I was trying to understand how to work with, but since the actual file I’m using is much more complicated we can stick with that file instead (the TEST_FILE.zip I have attached). This test file is exactly what my current file looks like, and it’s what I need to merge rows for.
For the time being you can ignore the above headers and focus only on how I can merge the rows of the file, the desired output I’d like would be: Documents.zip (16.1 KB)
This zip will contain a file to use to test, as well as a file that shows the desired output I’d want when it comes to merging rows
@AsposeC95,
There are two special factors in your template file which prevent you from getting the expected result. First is the merged cells, for merged cells, you cannot set cell values other than the cell at the top-left corner. Second is many cells contains empty string “”, which make the criteria fail when checking empty cells. According to your new template file(TEST_FILE.xlsx), please see below code:
private static bool IsNull(Cell c)
{
CellValueType t = c.Type;
return t == CellValueType.IsNull || t == CellValueType.IsString && c.StringValue.Length < 1;
}
int rowHead = 2; //from your template file found it is still 2 for data header
Cells cells = wb.Worksheets[0].Cells;
cells.ClearMergedCells();
int rowEnd = cells.MaxDataRow;
for (int i = rowEnd - 1; i > rowHead; i--)
{
Cell cell = cells.CheckCell(i + 1, 0);
if (cell != null && !IsNull(cell))
{
continue;
}
cell = cells.CheckCell(i, 0);
if (cell == null || IsNull(cell))
{
continue;
}
IEnumerator en = cells.Rows[i + 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Column > 7)
{
break;
}
if (!IsNull(cell1))
{
cell = cells[i, cell1.Column];
if (IsNull(cell))
{
cell.Copy(cell1);
}
}
}
cells.DeleteRow(i + 1);
i--;
}
Thanks! This helps a lot, Editing comment because I figured out most of my issue aside from this final issue:
The ClearMergedCells portion gives me error:
‘Cells’ does not contain a definition for ‘ClearMergedCells’ and no accessible extension method ‘ClearMergedCells’ accepting a first argument of type ‘Cells’ could be found (are you missing a using directive or an assembly reference?)
I double checked that I am using aspose.cells so it should be working, is there a different library I should be using to allow my code to use ClearMergedCells properly?
I checked Cells.ClearMergedCells method is there in the Aspose.Cells APIs. Which Aspose.Cells version you are using? Please make sure you are using latest version, e.g., Aspose.Cells for .NET v22.5.
Just in case it’s an older version, is there a way to do this without the ClearMergedCells option? This is a company license so I don’t believe I have access to upgrade it unless is there a way to upgrade to latest version locally on my vscode?
Checking the lic file, we have licenseversion 3.0 which I assume is the version
Thanks!
If it’s an older version is there a way I can use ClearMergedCells without this command? I don’t know if I have access to update the current version of the library.
Also do you have the code for ClearMergedCells method so I can declare it myself in my script to use since I don’t have the correct version of aspose.cells?
Edit:
Confirmed we are unable to upgrade to the newest version, is there a workaround I can use?