Java: Export Excel Data to Word Tables with Formatting

When creating reports in Word, we often encounter the situation where we need to copy and paste data from Excel to Word, so that readers can browse data directly in Word without opening Excel documents. In this article, you will learn how to convert Excel data into Word tables and preserve the formatting using Spire.Office for Java.

Install Spire.Office for Java

First, you're required to add the Spire.Office.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.office</artifactId>
        <version>9.10.0</version>
    </dependency>
</dependencies>
    

Export Excel Data to a Word Table with Formatting

The following are the steps to convert Excel data to a Word table maintaining formatting using Spire.Office for Java.

  • Create a Workbook object and load a sample Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Create a Document object, and add a section to it.
  • Add a table using Section.addTable() method.
  • Detect the merged cells in the worksheet and merge the corresponding cells of the Word tale using the custom method mergeCells().
  • Get value of a specific Excel cell using CellRange.getValue() method and add it to a cell of the Word table using TableCell.addParagraph().appendText() method.
  • Copy the font style and cell style from Excel to the Word table using the custom method copyStyle().
  • Save the document to a Word file using Document.saveToFile() method.
  • Java
import com.spire.doc.*;
import com.spire.doc.FileFormat;
import com.spire.doc.documents.HorizontalAlignment;
import com.spire.doc.documents.PageOrientation;
import com.spire.doc.documents.VerticalAlignment;
import com.spire.doc.fields.TextRange;
import com.spire.xls.*;

public class ExportExcelToWord {

    public static void main(String[] args) {

        //Load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:/Users/Administrator/Desktop/sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Create a Word document
        Document doc = new Document();
        Section section = doc.addSection();
        section.getPageSetup().setOrientation(PageOrientation.Landscape);

        //Add a table
        Table table = section.addTable(true);
        table.resetCells(sheet.getLastRow(), sheet.getLastColumn());

        //Merge cells
        mergeCells(sheet, table);

        for (int r = 1; r <= sheet.getLastRow(); r++) {

            //Set row Height
            table.getRows().get(r - 1).setHeight((float) sheet.getRowHeight(r));

            for (int c = 1; c <= sheet.getLastColumn(); c++) {
                CellRange xCell = sheet.getCellRange(r, c);
                TableCell wCell = table.get(r - 1, c - 1);

                //Get value of a specific Excel cell and add it to a cell of Word table
                TextRange textRange = wCell.addParagraph().appendText(xCell.getValue());

                //Copy font and cell style from Excel to Word
                copyStyle(textRange, xCell, wCell);
            }
        }

        //Save the document to a Word file
        doc.saveToFile("ExportToWord.docx", FileFormat.Docx);
    }

    //Merge cells if any
    private static void mergeCells(Worksheet sheet, Table table) {
        if (sheet.hasMergedCells()) {

            //Get merged cell ranges from Excel
            CellRange[] ranges = sheet.getMergedCells();
            for (int i = 0; i < ranges.length; i++) {
                int startRow = ranges[i].getRow();
                int startColumn = ranges[i].getColumn();
                int rowCount = ranges[i].getRowCount();
                int columnCount = ranges[i].getColumnCount();

                //Merge corresponding cells in Word table
                if (rowCount > 1 && columnCount > 1) {
                    for (int j = startRow; j <= startRow + rowCount ; j++) {
                        table.applyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
                    }
                    table.applyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount -1);
                }
                if (rowCount > 1 && columnCount == 1 ) {
                     table.applyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount -1);
                }
                if (columnCount > 1 && rowCount == 1 ) {
                    table.applyHorizontalMerge(startRow - 1, startColumn - 1,  startColumn - 1 + columnCount-1);
                }
            }
        }
    }

    //Copy cell style of Excel to Word table
    private static void copyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell) {

        //Copy font style
        wTextRange.getCharacterFormat().setTextColor(xCell.getStyle().getFont().getColor());
        wTextRange.getCharacterFormat().setFontSize((float) xCell.getStyle().getFont().getSize());
        wTextRange.getCharacterFormat().setFontName(xCell.getStyle().getFont().getFontName());
        wTextRange.getCharacterFormat().setBold(xCell.getStyle().getFont().isBold());
        wTextRange.getCharacterFormat().setItalic(xCell.getStyle().getFont().isItalic());

        //Copy backcolor
        wCell.getCellFormat().setBackColor(xCell.getStyle().getColor());

        //Copy horizontal alignment
        switch (xCell.getHorizontalAlignment()) {
            case Left:
                wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Left);
                break;
            case Center:
                wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
                break;
            case Right:
                wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Right);
                break;
        }
        
        //Copy vertical alignment
        switch (xCell.getVerticalAlignment()) {
            case Bottom:
                wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Bottom);
                break;
            case Center:
                wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Middle);
                break;
            case Top:
                wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Top);
                break;
        }
    }
}

Java: Export Excel Data to Word Tables with Formatting

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.