import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
public class ExportExcel {
public static void main(String[] args) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth(10);
HSSFFont headerFont = wb.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
CellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setAlignment(CellStyle.ALIGN_CENTER);
bodyStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
int rowCount = 0;
Row header;
Document doc = Jsoup.connect(
"http://www.w3school.com.cn/tags/tag_table.asp")
.get();
Cell cell;
for (Element table : doc.select("table[class=dataintable]")) {
Elements trs = table.select("tr");
int rowAmount = trs.size();
int colAmount = table.select("tbody tr:eq(0)").select("td").size();
List<List<Boolean>> lists = initLists(rowAmount, colAmount);
for (Element row : trs) {
header = sheet.createRow(rowCount);
Elements ths = row.select("th");
int colCount = 0;
if (ths.size() != 0) {
List<Boolean> iList = lists.get(rowCount);
int index = 0;
for (int colIndex = 0; colIndex < colAmount; ++colIndex) {
if (iList.get(colIndex)) {
cell = header.createCell(colCount);
cell.setCellValue("");
cell.setCellStyle(headerStyle);
} else {
Element element = ths.get(index++);
cell = header.createCell(colCount);
cell.setCellValue(element.text());
cell.setCellStyle(headerStyle);
String rowspan = element.attr("rowspan").trim();
String colspan = element.attr("colspan").trim();
if (rowspan.length() != 0) {
Integer rowspanInt = Integer.parseInt(rowspan);
Integer colspanInt = Integer.parseInt(colspan);
int firstRow = rowCount;
int lastRow = firstRow + rowspanInt - 1;
int firstCol = colCount;
int lastCol = colCount + colspanInt - 1;
sheet.addMergedRegion(new CellRangeAddress(
firstRow, lastRow, firstCol, lastCol));
for (int i = firstRow; i <= lastRow; ++i) {
for (int j = firstCol; j <= lastCol; ++j) {
lists.get(i).set(j, true);
}
}
} else {
lists.get(rowCount).set(colCount, true);
}
}
colCount++;
}
}
Elements tds = row.select("td:not([rowspan])");
colCount = 0;
for (Element element : tds) {
cell = header.createCell(colCount);
cell.setCellValue(element.text());
cell.setCellStyle(bodyStyle);
colCount++;
}
rowCount++;
}
}
String filepath = "g:/export.xls";
wb.write(new FileOutputStream(new File(filepath)));
System.out.println("成功导出。" + filepath);
}
private static List<List<Boolean>> initLists(int rowAmount, int colAmount) {
List<List<Boolean>> lists = new ArrayList<List<Boolean>>();
for (int i = 0; i < rowAmount; ++i) {
List<Boolean> list = new ArrayList<Boolean>();
for (int j = 0; j < colAmount; ++j) {
list.add(false);
}
lists.add(list);
}
return lists;
}
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10.1</version>
</dependency>