将Excel数据导出到Java中的单词表
#api #java #excel #word

当您需要根据Excel工作表中的数据中创建Word文档中的表时,通常可以通过复制和粘贴来完成任务。但是在此过程中,表格的格式可能会丢失,然后您需要在Word文档中重新格式化。本文将分享如何以编程方式将Excel数据转换为单词表并保留其格式在Java 应用中。

安装免费的尖峰。

要将Excel数据转换为Java中的单词表,您需要安装 free spire.office for Java 库。该图书馆完全免费用于商业和个人用途。以下是安装它的两种方法。
方法1:下载free library并解压缩,然后将spire.office.jar文件添加到您的项目中。

方法2:通过将以下配置添加到pom.xml。

<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.free</artifactId>
        <version>5.3.1</version>
    </dependency>
</dependencies>

示例代码

要将excel日期转换为单词表,您需要使用 cellrange.getValue()方法来获取特定的Excel单元格的值 tablecell.addparagraph()。appendText()方法。为了保留表格格式,您可以使用自定义方法 copystyle()将字体样式和单元格样式从excel复制到单词表。完整的示例代码如下所示。

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("test.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.Portrait);

        //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("ExportTableToWord.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;
        }
    }
}

ExceltoWord