Java-POI-sxssfWorkbook实例_inputstream转sxssfworkbook-优快云博客

该博客介绍了如何使用Java-POI的SXSSFWorkbook处理大量Excel数据，包括创建样式、设置数据格式、写入数据到工作簿以及输出到OutputStream的方法。示例展示了如何创建和管理样式，特别是针对数值格式的处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.herdsric.excel.create;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.common.type.NumberUtil;

public class ExcelCreateBigRecord {
        private static Logger logger = LogManager.getLogger(ExcelCreateBigRecord.class.getName());
        private SXSSFWorkbook sxssfWorkbook;
        private CellStyle noramlStyle;
        private CellStyle formatStyle1;
        private CellStyle formatStyle2;
        public ExcelCreateBigRecord(InputStream excelIs){
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(excelIs);
            this.sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
            this.noramlStyle = this.sxssfWorkbook.createCellStyle();
            this.formatStyle1 = this.sxssfWorkbook.createCellStyle();
            this.formatStyle2 = this.sxssfWorkbook.createCellStyle();
            this.setNormalStyle();
            this.setFormatStyle1();
            this.setFormatStyle2();
        } catch (IOException e) {
            logger.error("创建sxssfWorkbook错误:"+e.getMessage());
        }
    }

    private void setFormatStyle1(){
        DataFormat df = this.sxssfWorkbook.createDataFormat();
        this.formatStyle1.setDataFormat(df.getFormat("#,#0.00"));
    }
    private void setFormatStyle2(){
        DataFormat df = this.sxssfWorkbook.createDataFormat();
        this.formatStyle2.setDataFormat(df.getFormat("@"));
    }
    private void setNormalStyle(){
        Font font = this.sxssfWorkbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);
        this.noramlStyle.setFont(font);
        this.formatStyle1.setFont(font);
        this.formatStyle2.setFont(font);
    }

    public void createWorkBook(List<Map<String,Object>> recordList,int sheetNum,int firstRow) throws IOException{
        Sheet sheet = this.sxssfWorkbook.getSheetAt(sheetNum);
        for(int i = 0; i < recordList.size(); i++){
            Row row = sheet.createRow(i+firstRow);
            Map<String,Object> rowMap = recordList.get(i);
            for(int j = 0; j < rowMap.size(); j++){
            Cell cell = row.createCell(j);
            Object celVal = rowMap.get("e"+(j+1));
            this.setCellValue(cell, celVal);
        }
    }
}

    public void removeSheet(int sheetNum){
        this.sxssfWorkbook.removeSheetAt(sheetNum);
    }

    public void outWrite(OutputStream out){
        try {
            this.sxssfWorkbook.write(out);
        } catch (IOException e) {
            logger.error("sxssfWorkbook输出错误:"+e.getMessage());
        }
    }

    private void setCellValue(Cell cell,Object celVal){
    if(celVal == null){
        cell.setCellValue("");
        cell.setCellStyle(this.noramlStyle);
    }else if(celVal.toString().indexOf("mil") != -1){
        String cel = celVal.toString().replaceAll("mil", "").trim();
        if(NumberUtil.isNumeric(cel)){
            cell.setCellValue(new BigDecimal(cel).doubleValue());
            cell.setCellStyle(this.formatStyle1);
        }else{
            cell.setCellValue(cel);
            cell.setCellStyle(this.noramlStyle);
        }
}else if(NumberUtil.isNumeric(celVal.toString().trim())){
    cell.setCellValue(new BigDecimal(celVal.toString().trim()).doubleValue());
    cell.setCellStyle(this.formatStyle1);
   }else{
    cell.setCellValue(celVal.toString().replaceAll("cus", ""));
    cell.setCellStyle(this.formatStyle2);
}
}
}