POI合并单元格的使用

private void cellRange(HSSFSheet sheet, HSSFRow row, HSSFCellStyle cellStyle, String cellVal, int firstRow, int lastRow, short fistCol, short lastCol) {
        HSSFCell cell = row.createCell(fistCol);
        cell.setCellValue(cellVal);
        cell.setCellStyle(cellStyle);
        CellRangeAddress cellRange = new CellRangeAddress(firstRow, lastRow, fistCol, lastCol);
        sheet.addMergedRegion(cellRange);
        setRegionStyle(sheet, cellRange, cellStyle);
    }

    public void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
        RegionUtil.setBorderBottom(cs.getBorderBottom(), region, sheet);//下边框
        RegionUtil.setBorderLeft(cs.getBorderLeft(), region, sheet);     //左边框
        RegionUtil.setBorderRight(cs.getBorderRight(), region, sheet);    //右边框
        RegionUtil.setBorderTop(cs.getBorderTop(), region, sheet);      //上边框
    }
           

注意，合并单元格并赋予合并后的单元格的样式时，必须将所合并的单元格“全部创建”！！！！否则单元格的格式会缺失！！！

excel 转html

private String toHtml(HSSFWorkbook workbook) throws TransformerException, IOException, ParserConfigurationException {
        ExcelToHtmlConverter ethc = new ExcelToHtmlConverter(
                DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
        ethc.setOutputColumnHeaders(false);
        ethc.setOutputRowNumbers(false);
        ethc.processWorkbook(workbook);

        Document htmlDocument = (Document) ethc.getDocument();
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        DOMSource domSource = new DOMSource(htmlDocument);
        StreamResult streamResult = new StreamResult(out);

        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer serializer = tf.newTransformer();
        serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
        serializer.setOutputProperty(OutputKeys.INDENT, "yes");
        serializer.setOutputProperty(OutputKeys.METHOD, "html");
        serializer.transform(domSource, streamResult);
        out.close();

        String htmlStr = new String(out.toByteArray());

        htmlStr = htmlStr.replace("<h2>Sheet1</h2>", "")
                .replace("<h2>Sheet2</h2>", "")
                .replace("<h2>Sheet3</h2>", "")
                .replace("<h2>Sheet4</h2>", "")
                .replace("<h2>Sheet5</h2>", "");

        return htmlStr;
    }
           

excel转html预览时，单元格函数未进行计算，则需要加上如下代码，重新声明该单元格为函数

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i <  sheet.getPhysicalNumberOfRows(); i++) {
   HSSFRow row = sheet.getRow(i);
    for (int j = 0; j < row.getPhysicalNumberOfCells() ; j++) {
        HSSFCell cell = row.getCell(j);
        if(cell.getCellType()==CellType.FORMULA.getCode()){
            evaluator.evaluateFormulaCell(cell);
        }
    }
}
sheet.setForceFormulaRecalculation(true);