使用POI java将HTML表格数据转换为Excel单元格

Question

您能告诉我如何使用java中的ApachePOI将html表数据呈现到Excel单元格中吗。我有以下要求，比如。。

<table border="1" cellpadding="1" cellspacing="1" style="width:500px;">
<tbody>
<tr>
<td>details</td>
<td>testing</td>
</tr>
<tr><td>1</td>
<td>220</td>
</tr>
<tr><td>3</td>
<td>4</td>
<td>10</td>
</tr></tbody>
</table>

但它只是作为文本字符串...在这里输入图像描述

Answer 1

这里基本上需要解析HTML并将其转换为数据行，然后最后写入excel。我将向您展示如何使用Gradle、Jsoup和ApachePOI实现这一点

在build.gradle中需要以下依赖项

    implementation 'org.apache.poi:poi:5.0.0'
    implementation 'org.apache.poi:poi-ooxml:5.0.0'
    implementation group: 'org.jsoup', name: 'jsoup', version: '1.11.3'

这是转换和写入excel的代码

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class Test {

    public static void main(String[] args) throws IOException {
        //HTML to Data
        String data = "<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" style=\"width:500px;\">\n" +
            "<tbody>\n" +
            "<tr>\n" +
            "<td>details</td>\n" +
            "<td>testing</td>\n" +
            "</tr>\n" +
            "<tr><td>1</td>\n" +
            "<td>220</td>\n" +
            "</tr>\n" +
            "<tr><td>3</td>\n" +
            "<td>4</td>\n" +
            "<td>10</td>\n" +
            "</tr></tbody>\n" +
            "</table>";
        Document doc = Jsoup.parse(data);
        Element table = doc.select("table").get(0); //select the first table.
        Elements rows = table.select("tr");
        Element headerRow = rows.get(0);
        List<String> headers = new ArrayList<>();
        List<List<String>> dataRows = new ArrayList<>();
        Elements headerColumns = headerRow.select("td");
        for (int l = 0; l < headerColumns.size(); l++) {
            headers.add(headerColumns.get(l).text());
        }
        System.out.println(headers);
        for (int i = 1; i < rows.size(); i++) {
            Element row = rows.get(i);
            Elements cols = row.select("td");
            List<String> dataRow = new ArrayList<>();
            for (int j = 0; j < cols.size(); j++) {
                dataRow.add(cols.get(j).text());
            }
            dataRows.add(dataRow);
        }
        //Data to excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("data");
        Row header = sheet.createRow(0);
        for (int k = 0; k < headers.size(); k++) {
            Cell headerCell = header.createCell(k);
            headerCell.setCellValue(headers.get(k));
        }

        for (int i = 0; i < dataRows.size(); i++) {
            Row r = sheet.createRow(i + 1);
            List<String> d = dataRows.get(i);
            for (int j = 0; j < d.size(); j++) {
                Cell dataCell = r.createCell(j);
                dataCell.setCellValue(d.get(j));
           }
        }

        File currDir = new File(".");
        String path = currDir.getAbsolutePath();
        String fileLocation = path.substring(0, path.length() - 1) + "Data.xlsx";

        FileOutputStream outputStream = new FileOutputStream(fileLocation);
        workbook.write(outputStream);
        workbook.close();
    }

}