Aspose Cells Copying the Empty rows while rplacing the data to Word

Aspose.Cells Product Family
1

Hello Team,
input_outputdoc.zip (133.0 KB)

Excel_Word_Doc.zip (763.2 KB)
I am replacing excel sheet data in word at that time Aspose cells are considering few rows at the end of the table which doesn’t have the data, Attached sample code and doc. How to remove those empty rows

2

@bkalightaspose1125

To remove empty rows from your Excel sheet before replacing the data in a Word document, you can utilize the DeleteBlankRows method provided by Aspose.Cells. This method will help you eliminate any blank rows in your worksheet, ensuring that only rows with data are processed.

Here’s a sample code snippet demonstrating how to achieve this:

using System;
using Aspose.Cells;

namespace Aspose.Cells.Examples
{
    public class RemoveEmptyRows
    {
        public static void Main()
        {
            // Load your Excel file
            Workbook workbook = new Workbook("path_to_your_excel_file.xlsx");

            // Access the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Delete blank rows from the worksheet
            worksheet.Cells.DeleteBlankRows();

            // Save the modified Excel file
            workbook.Save("path_to_save_modified_file.xlsx");

            Console.WriteLine("Empty rows removed successfully.");
        }
    }
}

In this code:

  • Replace "path_to_your_excel_file.xlsx" with the path to your input Excel file.
  • The DeleteBlankRows method is called on the Cells collection of the worksheet, which removes all empty rows.
  • Finally, save the modified workbook to a new file.

By using this approach, you should be able to prevent empty rows from being included when you replace the data in your Word document.

If you have any further questions or need additional assistance, feel free to ask!

1 Like
3

@bkalightaspose1125
Replace TrimEmptyRowsAndColumns(sheet) with sheet.Cells.DeleteBlankRows()

    sheet.Cells.DeleteBlankRows();
    //TrimEmptyRowsAndColumns(sheet);

Hope helps a bit!
result.7z (37.2 KB)

4

@bkalightaspose1125
There are some empty cells after data row in the worksheet “4) Net Position”, but they are formatted, so we still have to export them to html though they looks empty.

If you only want to export data rows, you can assign HtmlSaveOptions.ExportArea which should be exported:

 Workbook workbook = new Workbook(dir + "Section 4 Reports1.xlsx");
 Cells cells = workbook.Worksheets[2].Cells;

 workbook.Worksheets.ActiveSheetIndex = 2;
 HtmlSaveOptions htmlOptions = new HtmlSaveOptions(Aspose.Cells.SaveFormat.Html)
 {
     ExportGridLines = true,
     ExportActiveWorksheetOnly = true,
     HiddenColDisplayType = HtmlHiddenColDisplayType.Remove,
     HiddenRowDisplayType = HtmlHiddenRowDisplayType.Remove,
     ExportArea = CellArea.CreateCellArea(cells.MinDataRow, cells.MinDataColumn, cells.MaxDataRow,cells.MaxDataColumn)

 };
         
         
 workbook.Save(dir +"dest.html", htmlOptions);

I think it’s better that you can find which range should be exported in the source file.