欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI合并单元格的使用

程序员文章站 2022-03-06 23:02:39
...
 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);