Avoid Inserting Empty Ranges or Hidden Excel Ranges into Word Document

Aspose.Total Product Family
1

Hello Team,

I am working with Aspose.Cells to extract a range from an Excel worksheet as HTML content, which I then insert into a Word document using Aspose.Words.

However, I’ve encountered an issue:

  • If the selected range contains only empty cells or an empty table structure (with no visible content), it still gets inserted into the Word document, resulting in unnecessary blank space.
  • Similarly, if the range consists of only hidden rows or columns, it is still processed and inserted, even though there’s nothing meaningful to show.

I would like to prevent the insertion of such ranges if:

  1. The range is entirely empty (no visible or meaningful data even formatting should not be there).
  2. The range contains only hidden rows and/or columns.

Could you please advise on the best way to achieve this using Aspose.Cells and Aspose.Words?

2

@KeerthanaRamesh214

To avoid inserting empty ranges or hidden Excel ranges into a Word document using Aspose.Cells and Aspose.Words, follow these steps:

  1. Check for Empty Cells: Before converting the range to HTML, verify if the range contains any visible data by iterating through the cells.

  2. Check for Hidden Rows/Columns: Additionally, check if any rows or columns in the selected range are hidden. If they are, skip the conversion.

Here’s a sample code snippet to illustrate this:

using Aspose.Cells;
using Aspose.Words;

// Load your Excel file
Workbook workbook = new Workbook("input.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Define the range you want to check
Range range = worksheet.Cells.CreateRange("A1:C10");

// Check if the range is empty or contains only hidden rows/columns
bool isEmpty = true;
for (int row = range.FirstRow; row <= range.FirstRow + range.RowCount - 1; row++)
{
    for (int col = range.FirstColumn; col <= range.FirstColumn + range.ColumnCount - 1; col++)
    {
        if (!worksheet.Cells[row, col].IsEmpty && !worksheet.Cells[row, col].IsHidden)
        {
            isEmpty = false;
            break;
        }
    }
    if (!isEmpty) break;
}

if (!isEmpty)
{
    // Convert the range to HTML
    string htmlContent = range.ExportToHtml();

    // Load the Word document
    Document doc = new Document("output.docx");

    // Insert the HTML content into the Word document
    DocumentBuilder builder = new DocumentBuilder(doc);
    builder.InsertHtml(htmlContent);

    // Save the document
    doc.Save("output.docx");
}

In this code:

  • Each cell in the specified range is checked to determine if it is empty or hidden.
  • If the range contains meaningful data, it is converted to HTML and inserted into the Word document.

This approach ensures that only non-empty and visible content is inserted into your Word document.

4

@KeerthanaRamesh214,

Thanks for details.

When exporting a range from a worksheet to HTML, hidden rows and columns are rendered as expected, consistent with MS Excel’s behavior. You can verify this in MS Excel; when exporting a cell range (including empty or hidden rows/columns) to an HTML (Web Page) format manually, the empty or hidden rows/columns are represented using <tr><td></td></tr> tags within the table.

To address this, you can remove the empty cells in rows/columns or hidden rows/columns and adjust the range accordingly. Alternatively, you can set HiddenRowDisplayType to HtmlHiddenRowDisplayType.Remove and HiddenColDisplayType to HtmlHiddenColDisplayType.Remove under HtmlSaveOptions. By doing so, the hidden rows and columns will no longer appear in the output HTML. Refer to the provided sample code and template Excel file for further guidance…
e.g.,
Sample code:

Workbook workbook = new Workbook("e:\\test2\\Bk_datarange1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
Aspose.Cells.Range range = workbook.Worksheets.Names.First(x => x.FullText == "range1").GetRange()!;
worksheet.PageSetup.PrintArea = range.Address;
Aspose.Cells.HtmlSaveOptions options = new Aspose.Cells.HtmlSaveOptions()
{
   ExportPrintAreaOnly = true,
   HiddenRowDisplayType = HtmlHiddenRowDisplayType.Remove,
   HiddenColDisplayType = HtmlHiddenColDisplayType.Remove
};

workbook.Save("e:\\test2\\out1.html", options);

Please find attached the zipped archive that contains the input Excel file and output HTML file for your reference.
files.zip (8.1 KB)

In case, you still have any issue, kindly do provide your sample Excel file, sample (runnable) code and output file(s) to evaluate your issue on our end. We will check it soon.

1 Like
5

Also I am using code that replaces regex matches with values fetched in real-time from Excel, including ranges, named ranges, charts, and tables. However, the performance is currently poor. Could you help me improve the performance, possibly by implementing parallel replacements or other optimization techniques?

I will attach my code in a document and I am using Aspose cell and word
Code.docx (21.6 KB)

6

@KeerthanaRamesh214,

Thank you for sharing the code snippet.

It appears that you are performing replacements using regex matches via the Aspose.Words API in the Word document and encountering performance issues, correct? We would greatly appreciate it if you could provide a standalone sample project along with all the necessary resource files (such as Excel files, Word documents, etc.). This would enable us to thoroughly evaluate the issue on our end and assist you more effectively.

PS. Kindly ensure to zip the app and resource files before attaching them here.

7

AsposeExcelReplacement.7z (5.4 MB)

You can duplicate the excel files in the folder for others with different names I just added one make sure your license is added and excel files with those names are there you can rename the same files
Excel_Syntax.docx (17.4 KB)

this is the word document to be uploaded the performance is worst please help me to improve without any functional change

8

@KeerthanaRamesh214,

Thank you for providing the sample project and files.

We will review the performance issue soon. @alexey.noskov FYI.

1 Like
9

@KeerthanaRamesh214 Unfortunately, there is no way to perform parallel replace operations, because each replace operation changes the document node structure and placeholders can be represented with several nodes or one node can contain several placeholders.

Find/Replace is not the best and effective way to fill template with data. Aspose.Words provides more efficient ways such as Mail Merge or LINQ Reporting Engine.