Hello, does anyone know how to merge and delete rows in excel using C#?
For example, I have an excel, attached an example pic of what it would look like. I need to do the following with it:
loop through all rows and merge any row with a blank Column B to the row above it
loop through all rows and delete any row that contains ‘N/A’ in column B image.png (14.4 KB)
I tried looking up how to run a loop and an update/delete at the same time, but I couldn’t find an example query to use
This is the query I’m attempting to use to remove certain rows, but it’s deleting all rows for some reason
for (int row=0;row<=worksheet.Cells.MaxDataRow;row++)
{
for (int col =0;col<= worksheet.Cells.MaxDataColumn; col++ )
{
//Your code goes here…
if (worksheet.Cells[row,col].StringValue == “Total”)
{
worksheet.Cells.DeleteRow(row);
}
1). See the document on how to merge the cells for your reference.
2). Generally, N/A value in the cell is due to formula error. Please note the following errors due to formulas: #DIV/0!, #N/A, #REF!, #VALUE!, #NUM!, etc…
In this case, you may easily check cells by code like:
IEnumerator en = cells.GetEnumerator();
while (en.MoveNext())
{
cell = (Cell)en.Current;
if (cell.IsFormula && cell.IsErrorValue)
{
//your code goes here
}
}
Also, for your code segment, it can be refined as following:
....
workbook.CalculateFormula();
int rowCount = worksheet.Cells.MaxDataRow;
int colCount = worksheet.Cells.MaxDataColumn;
for (int i = rowCount; i >= 0; i--)
{
for (int j = colCount; j >= 0; j--)
{
if (worksheet.Cells[i, j].StringValue == "N/A")
{
worksheet.Cells.DeleteRow(i);
}
}
}
Hello, thank you for the response! I was able to resolve the second quesiton, but I am still stuck on the first question. I reviewed the document but is there an easy way to set a merge o the above row only if column 1 is blank? I just want to merge to the above row that way but I’m having a bit of trouble finding out how to do this
Using the image I attached to my post, I want to merge it so that the records for certain columns, placed on a separate row, will merge with the above row so all fields can be populated
Could you please zip and share your sample input Excel file and your expected output Excel file for reference. We will check and help you on how to do it via Aspose.Cells APIs.
PS. please create your desired Excel file via MS Excel manually.
AsposeExcelTest.zip (8.4 KB)
I’ve attached a zip with an excel file I manually created, this will include everything I need to do for the file using aspose (so not just the 2 steps above, but also 1 more step where I have to add locations into it’s own column). I’ve shown both the input and output in the same sheet, and the output only shows for the first records but it has to be done for all records, but I color coded and showed the concept I need to follow, let me know if you have any questions!
Seeing your input data formation and your output(expected) data formation plus foreseeing your notes/conditions, apparently there may not be a better/reliable way to accomplish your task except you have to do it in manually way for your custom needs. Anyways, we will further check if there is any better way to do your task. By the way, I do not find any option or automatic way in MS Excel either for your desired task. If you find any available option to accomplish your task in MS Excel manually, let us know with steps details, we can check it further.
Please do look into it, ideally I’d like to get the merge cell part down, where it will merge lines together using aspose. If you can answerr this issue for me, I can work on the other parts myself
(Please answer the blue highlighted related question, I want to figure out how to merge rows usiong aspose)
Your data is pasted horizontally and you want to get it pasted vertically with your desired conditions plus rules accordingly. This is where it is difficult to build reliable logic. Anyways, we have to look into it further and we will get back to you soon.
No not the vertical to horizonal issue, focus on the blue highlighted records where I just want to merge records to the above rows so they are all in one line (ignore the vertical to horizonal example, I want to focus on solving the merge column issue first).
If the vertical to horizonal cannot be done, I will find another way, but for now I’d like to resolve the merge row issue first, as part of my original request:
loop through all rows and merge any row with a blank Column B to the row above it
Workbook wb = new Workbook("AsposeExcelTest.xlsx");
Cells cells = wb.Worksheets[0].Cells;
//From your template file I assume the data table starts from the third row
int rowHead = 2;
//because in the template file there are data of OUTPUT,
// so I set static value here for the end row. For your case maybe it
// should be cells.MaxDataRow or others
int rowEnd = 11;
for (int i = rowEnd - 1; i > rowHead; i--)
{
//I use ID as the criteria to check whether it is the row where
// the next row needs to be merged into
Cell cell = cells.CheckCell(i, 0);
if (cell == null || cell.Type == CellValueType.IsNull)
{
continue;
}
IEnumerator en = cells.Rows[i + 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Type != CellValueType.IsNull)
{
cells[i, cell1.Column].Copy(cell1);
}
}
cells.DeleteRow(i + 1);
}
wb.Save("res.xlsx");
This is just an example to show how to loop all rows and merge some data. You may modify it according to your business requirements to get your expected result.
for (int i=0;i<=worksheet.Cells.MaxDataRow;i++)
{
//I use ID as the criteria to check whether it is the row where
// the next row needs to be merged into
Cell cell = cells.CheckCell(i, 0);
if (cell == null || cell.Type == CellValueType.IsNull)
{
continue;
}
IEnumerator en = cells.Rows[i - 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Type != CellValueType.IsNull)
{
cells[i, cell1.Column].Copy(cell1);
}
}
cells.DeleteRow(i - 1);
}
But I’m getting an error at the IEnumerator part:
Using the generic type ‘IEnumerator’ requires 1 type arguments
Do you see an issue with the code I made? It doesn’t seem to be working from my end
I think you should not use MaxDataRow in the for statement. The reason is as rows are being deleted, so MaxDataRow would be changed in every iteration. You may get its value in some variable and use that variable in the for statement. int endRow = worksheet.Cells.MaxDataRow;
Moreover, as rows are being removed in interactions, so it is better you process the loop from end row to first row, see the sample code segment shared by @johnson.shi.
I do not find any issue, the suggested code segment shared by @johnson.shi works fine as I tested.
int endRow = worksheet.Cells.MaxDataRow;
for (int i=0;i<=endRow;i++)
{
//I use ID as the criteria to check whether it is the row where
// the next row needs to be merged into
Cell cell = cells.CheckCell(i, 2);
if (cell == null || cell.Type == CellValueType.IsNull)
{
continue;
}
IEnumerator en = cells.Rows[i - 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Type != CellValueType.IsNull)
{
cells[i, cell1.Column].Copy(cell1);
}
}
cells.DeleteRow(i - 1);
}
But it looks like it still isn’t merging the rows, is there something I’m doing wrong? It removes the rows but the values aren’t combining with the above rows
I’ve tried the code above and it deletes everything in my excel, I had to change around some of the stuff from my end:
Cells cellsmerge = workbook.Worksheets[0].Cells;
//From your template file I assume the data table starts from the third row
int rowHead = 2;
//because in the template file there are data of OUTPUT,
// so I set static value here for the end row. For your case maybe it
// should be cells.MaxDataRow or others
int rowEnd = worksheet.Cells.MaxDataRow;
for (int i = rowEnd - 1; i > rowHead; i--)
{
//I use ID as the criteria to check whether it is the row where
// the next row needs to be merged into
Cell cell = cellsmerge.CheckCell(i, 0);
if (cell == null || cell.Type == CellValueType.IsNull)
{
continue;
}
IEnumerator en = cellsmerge.Rows[i + 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Type != CellValueType.IsNull)
{
cells[i, cell1.Column].Copy(cell1);
}
}
cellsmerge.DeleteRow(i + 1);
}
I’m thinking maybe the Cells cellsmerge = workbook.Worksheets[0].Cells; would also need to be changed,
Also here’s what I have already declared in the code prior to adding the above code:
Workbook workbook = Excel.BuildWorkbook(new MemoryStream(batchContent));
workbook.Worksheets.ActiveSheetIndex = workbook.Worksheets[sheetName].Index;
Worksheet worksheet = workbook.Worksheets[sheetName];
Cells cells = worksheet.Cells;
If this changes anything.
I also run some column deletes right before I’m merging rows, I assume that shouldn’t make too much of a difference but in case it does feel free to let me know:
worksheet.Cells.DeleteColumn(27);
worksheet.Cells.DeleteColumn(26);
worksheet.Cells.DeleteColumn(23);
worksheet.Cells.DeleteColumn(22);
worksheet.Cells.DeleteColumn(21);
worksheet.Cells.DeleteColumn(20);
worksheet.Cells.DeleteColumn(19);
worksheet.Cells.DeleteColumn(18);
worksheet.Cells.DeleteColumn(15);
worksheet.Cells.DeleteColumn(14);
worksheet.Cells.DeleteColumn(3);
worksheet.Cells.DeleteColumn(2);
worksheet.Cells.DeleteColumn(1);
worksheet.Cells.DeleteColumn(0);
Keep in mind the excel I provided is not the excel I’m actually using, it’s a mock up of something similar I need done
I guess you did not find time to check the comments attached to the code as following:
We used your provided file as template file. Johnson used 11 as rowEnd variable (static) value because you have also pasted your desired output below your actual input data/table in your provided file. Either remove your complete desired output data range to re-save the Excel file and then use the suggested code. Alternatively, use static value at the moment.
Please note, the suggested code segment is only to give you hints and is for your reference purpose based on your provided Excel file. You have to write your own logic and code by yourselves to accommodate your custom needs accordingly for your actual template Excel file.
I had read the comments which is why my above queries have been altered to use maxdatarow along with other updates that I have made accordingly based on above comments and further changes you have suggested. Certain things such as cells were already declared in my query so I’ve updated those accordingly as well. Here’s an exact test file that mimics exactly how my file looks, if this file works in your test then it must be something on my end, please use this file as a test to see if rows merge properly:
(I removed this zip file to prevent confusion, please reference documents.zip down below)
But on my end, no matter how I update it to fit my script, it is removing all rows instead.
One thing to note, in the actual excel that I am using (which is also shown in the above test file that is an exact replica of what I am using) the first column (id) is 2 rows of column 1 combined each time, so I assume this is causing an issue too, which is why in my query I have also updated it to check for column 2 instead.
This is my current query after all updates:
int rowHead = 5; // I changed this to 5 so I could get the sample data for the test file, since otherwise it just deletes all my data, originally this is set to 2
//because in the template file there are data of OUTPUT,
// so I set static value here for the end row. For your case maybe it
// should be cells.MaxDataRow or others
int rowEnd = worksheet.Cells.MaxDataRow;
for (int i = rowEnd - 1; i > rowHead; i--)
{
//I use ID as the criteria to check whether it is the row where
// the next row needs to be merged into
Cell cell = cells.CheckCell(i, 2);
if (cell == null || cell.Type == CellValueType.IsNull)
{
continue;
}
IEnumerator en = cells.Rows[i + 1].GetEnumerator();
while (en.MoveNext())
{
Cell cell1 = (Cell)en.Current;
if (cell1.Type != CellValueType.IsNull)
{
cells[i, cell1.Column].Copy(cell1);
}
}
cells.DeleteRow(i + 1);
}
Your newly shared file is completely different from your previously provided file. Some cells (especially in the first field) are merged. Moreover, data formation plus fields are different. The file is a mixed kind of file. So, the suggested code segment may not work for this file. But the suggested code segment will work with your previously shared file (you may test it). Could you also provide your desired output. This may help to evaluate if we could build some logic or you have to do it in manual way only.