Nuxtstop

For all things nuxt.js

Java convert Excel to Word table

#excel #word #table #java
0 0

This guide will show you how to convert Excel with font, styles and background color to word table in Java with the help of Spire.Office.

Installing Spire.office jar

If you create a Maven project, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the Office jar file and add it as a dependency in your application.

<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>4.6.6</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

Firstly, view the effective screenshot of the Excel worksheet:

Excel Sample

With Spire.XLS, we will get all the data and styles on the Excel worksheet and then use Spire.Doc to copy them to the word and generate a new word table. Here comes to the codes.

import com.spire.doc.Document;
import com.spire.doc.Table;
import com.spire.doc.TableCell;
import com.spire.doc.documents.HorizontalAlignment;
import com.spire.doc.documents.VerticalAlignment;
import com.spire.doc.fields.TextRange;
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ExcelToWordTable {
    public static void main(String[] args) throws Exception {
        //Load the Sample workbook
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample00.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Copy to Word 
        copyToWord(sheet.getAllocatedRange(), "output.docx");

    }

    public static void copyToWord(CellRange cell, String fPath) {
        //Add a table
        Document doc = new Document();
        Table table = doc.addSection().addTable(true);
        table.resetCells(cell.getRowCount(), cell.getColumnCount());
        //Copy the table contents
        for (int r = 1; r <= cell.getRowCount(); r++) {
            for (int c = 1; c <= cell.getColumnCount(); c++) {
                CellRange xCell = cell.get(r, c);
                CellRange mergeArea = xCell.getMergeArea();
                //merge cells
                if (mergeArea != null && mergeArea.getRow() == r && mergeArea.getColumn() == c) {
                    int rowIndex = mergeArea.getRow();
                    int columnIndex = mergeArea.getColumn();
                    int rowCount = mergeArea.getRowCount();
                    int columnCount = mergeArea.getColumnCount();

                    for (int m = 0; m < rowCount; m++) {
                        table.applyHorizontalMerge(rowIndex - 1 + m, columnIndex - 1, columnIndex + columnCount - 2);
                    }
                    table.applyVerticalMerge(columnIndex - 1, rowIndex - 1, rowIndex + rowCount - 2);
                }
                //Copy the contents
                TableCell wCell = table.getRows().get(r - 1).getCells().get(c - 1);
                if (!xCell.getDisplayedText().isEmpty()) {
                    TextRange textRange = wCell.addParagraph().appendText(xCell.getDisplayedText());
                    copyStyle(textRange, xCell, wCell);
                } else {
                    wCell.getCellFormat().setBackColor(xCell.getStyle().getColor());
                }
            }
        }
        doc.saveToFile(fPath,com.spire.doc.FileFormat.Docx);
}
        private static void copyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell) {
            //Copy the 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 the background
            wCell.getCellFormat().setBackColor(xCell.getStyle().getColor());
            //Copy the alignment style
            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;
                default:
                    break;
            }
            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;
                default:
                    break;
            }
        }
    }
Enter fullscreen mode Exit fullscreen mode

Output of the Word Table：

Excel to Word table

Add comment