java后台html 转excel

package xls; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Arrays; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.util.CellRangeAddress; import org.jsoup.Jsoup; import org.jsoup.nodes.Element; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import xls.resource.CssApplier; import xls.resource.support.AlignApplier; import xls.resource.support.BackgroundApplier; import xls.resource.support.BorderApplier; import xls.resource.support.HeightApplier; import xls.resource.support.TextApplier; import xls.resource.support.WidthApplier; /** * @version 0.0.1 * @since 0.0.1 * @author Shaun Chyxion <br> * chyxion@163.com <br> * Oct 24, 2014 2:09:02 PM */ public class TableToXls { private static final Logger log = LoggerFactory.getLogger(TableToXls.class); private static final List<CssApplier> STYLE_APPLIERS = new LinkedList<CssApplier>(); // static init static { STYLE_APPLIERS.add(new AlignApplier()); STYLE_APPLIERS.add(new BackgroundApplier()); STYLE_APPLIERS.add(new WidthApplier()); STYLE_APPLIERS.add(new HeightApplier()); STYLE_APPLIERS.add(new BorderApplier()); STYLE_APPLIERS.add(new TextApplier()); } private HSSFWorkbook workBook = new HSSFWorkbook(); private HSSFSheet sheet; private Map<String, Object> cellsOccupied = new HashMap<String, Object>(); private Map<String, HSSFCellStyle> cellStyles = new HashMap<String, HSSFCellStyle>(); private HSSFCellStyle defaultCellStyle; private int maxRow = 0; // init { sheet = workBook.createSheet(); defaultCellStyle = workBook.createCellStyle(); defaultCellStyle.setWrapText(true); defaultCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // border short black = new HSSFColor.BLACK().getIndex(); short thin = CellStyle.BORDER_THIN; // top defaultCellStyle.setBorderTop(thin); defaultCellStyle.setTopBorderColor(black); // right defaultCellStyle.setBorderRight(thin); defaultCellStyle.setRightBorderColor(black); // bottom defaultCellStyle.setBorderBottom(thin); defaultCellStyle.setBottomBorderColor(black); // left defaultCellStyle.setBorderLeft(thin); defaultCellStyle.setLeftBorderColor(black); } /** * process html to xls * @param html html char sequence * @return xls bytes */ public static byte[] process(CharSequence html) { ByteArrayOutputStream baos = null; try { baos = new ByteArrayOutputStream(); process(html, baos); return baos.toByteArray(); } finally { if (baos != null) { try { baos.close(); } catch (IOException e) { log.warn("Close Byte Array Inpout Stream Error Caused.", e); } } } } /** * process html to output stream * @param html html char sequence * @param output output stream */ public static void process(CharSequence html, OutputStream output) { new TableToXls().doProcess( html instanceof String ? (String) html : html.toString(), output); } // -- // private methods private void processTable(Element table) { int rowIndex = 0; if (maxRow > 0) { // blank row maxRow += 2; rowIndex = maxRow; } log.info("Interate Table Rows."); for (Element row : table.select("tr")) { log.info("Parse Table Row [{}]. Row Index [{}].", row, rowIndex); int colIndex = 0; log.info("Interate Cols."); for (Element td : row.select("td, th")) { // skip occupied cell while (cellsOccupied.get(rowIndex + "_" + colIndex) != null) { log.info("Cell [{}][{}] Has Been Occupied, Skip.", rowIndex, colIndex); ++colIndex; } log.info("Parse Col [{}], Col Index [{}].", td, colIndex); int rowSpan = 0; String strRowSpan = td.attr("rowspan"); if (StringUtils.isNotBlank(strRowSpan) && StringUtils.isNumeric(strRowSpan)) { log.info("Found Row Span [{}].", strRowSpan); rowSpan = Integer.parseInt(strRowSpan); } int colSpan = 0; String strColSpan = td.attr("colspan"); if (StringUtils.isNotBlank(strColSpan) && StringUtils.isNumeric(strColSpan)) { log.info("Found Col Span [{}].", strColSpan); colSpan = Integer.parseInt(strColSpan); } // col span & row span if (colSpan > 1 && rowSpan > 1) { spanRowAndCol(td, rowIndex, colIndex, rowSpan, colSpan); colIndex += colSpan; } // col span only else if (colSpan > 1) { spanCol(td, rowIndex, colIndex, colSpan); colIndex += colSpan; } // row span only else if (rowSpan > 1) { spanRow(td, rowIndex, colIndex, rowSpan); ++colIndex; } // no span else { createCell(td, getOrCreateRow(rowIndex), colIndex).setCellValue(td.text()); ++colIndex; } } ++rowIndex; } } private void doProcess(String html, OutputStream output) { for (Element table : Jsoup.parseBodyFragment(html).select("table")) { processTable(table); } try { workBook.write(output); } catch (IOException e) { throw new IllegalStateException("Table To XLS, IO ERROR.", e); } } private void spanRow(Element td, int rowIndex, int colIndex, int rowSpan) { log.info("Span Row , From Row [{}], Span [{}].", rowIndex, rowSpan); mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex); for (int i = 0; i < rowSpan; ++i) { HSSFRow row = getOrCreateRow(rowIndex + i); createCell(td, row, colIndex); cellsOccupied.put((rowIndex + i) + "_" + colIndex, true); } getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text()); } private void spanCol(Element td, int rowIndex, int colIndex, int colSpan) { log.info("Span Col, From Col [{}], Span [{}].", colIndex, colSpan); mergeRegion(rowIndex, rowIndex, colIndex, colIndex + colSpan - 1); HSSFRow row = getOrCreateRow(rowIndex); for (int i = 0; i < colSpan; ++i) { createCell(td, row, colIndex + i); } row.getCell(colIndex).setCellValue(td.text()); } private void spanRowAndCol(Element td, int rowIndex, int colIndex, int rowSpan, int colSpan) { log.info("Span Row And Col, From Row [{}], Span [{}].", rowIndex, rowSpan); log.info("From Col [{}], Span [{}].", colIndex, colSpan); mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1); for (int i = 0; i < rowSpan; ++i) { HSSFRow row = getOrCreateRow(rowIndex + i); for (int j = 0; j < colSpan; ++j) { createCell(td, row, colIndex + j); cellsOccupied.put((rowIndex + i) + "_" + (colIndex + j), true); } } getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text()); } private HSSFCell createCell(Element td, HSSFRow row, int colIndex) { HSSFCell cell = row.getCell(colIndex); if (cell == null) { log.debug("Create Cell [{}][{}].", row.getRowNum(), colIndex); cell = row.createCell(colIndex); } return applyStyle(td, cell); } private HSSFCell applyStyle(Element td, HSSFCell cell) { String style = td.attr(CssApplier.STYLE); HSSFCellStyle cellStyle = null; if (StringUtils.isNotBlank(style)) { if (cellStyles.size() < 4000) { Map<String, String> mapStyle = parseStyle(style.trim()); Map<String, String> mapStyleParsed = new HashMap<String, String>(); for (CssApplier applier : STYLE_APPLIERS) { mapStyleParsed.putAll(applier.parse(mapStyle)); } cellStyle = cellStyles.get(styleStr(mapStyleParsed)); if (cellStyle == null) { log.debug("No Cell Style Found In Cache, Parse New S