自定义POI的excel工具类-xls-xlsx

使用jdk8（java8）实现Excel导出，随意切换 xls和xlsx。自己只是封装了比较常用方法，07版生成还有bug。ε=(´ο｀*)))唉

jdk版本

java version "1.8.0_151"
Java(TM) SE Runtime Environment (build 1.8.0_151-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.151-b12, mixed mode)

maven版本

Apache Maven 3.6.3 (cecedd343002696d0abb50b32b541b8a6ba2883f)
Maven home: D:_frameapache-maven-3.6.3bin..
Java version: 1.8.0_151, vendor: Oracle Corporation, runtime: D:_languagejdk1.8.0_151_64jre
Default locale: zh_CN, platform encoding: GBK
OS name: "windows 10", version: "10.0", arch: "amd64", family: "windows"

pom

2.0.9.RELEASE
5.0.13.RELEASE
3.17

     org.springframework
     spring-web
     ${springframework.version}


    org.apache.commons
    commons-lang3
    3.8.1


    commons-io
    commons-io
    2.4


    org.apache.commons
    commons-collections4
    4.1


    com.alibaba
    fastjson
    1.2.60


    org.apache.poi
    poi
    ${poi.varsion}


    org.apache.poi
    poi-ooxml
    ${poi.varsion}


    com.alibaba
    QLExpress
    3.2.3

工具代码

package ***.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Consumer;


public class ExcelKit {
    private static final Logger log = LoggerFactory.getLogger(ExcelKit.class);

    public static void exportExcel03(HttpServletResponse response,Consumer consumer) throws Exception {
        log.info("poi导出工具类，生成03版excel-start");
        PoiBean poiBean = generate(new HSSFWorkbook(),consumer);
        export(response,poiBean);
        log.info("poi导出工具类，生成03版excel-end");
    }
    public static void exportExcel07(HttpServletResponse response,Consumer consumer) throws Exception {
        log.info("poi导出工具类，生成07版excel-start");
        PoiBean poiBean = generate(new XSSFWorkbook(),consumer);
        export(response,poiBean);
        log.info("poi导出工具类，生成07版excel-start");
    }
    public static void fail(HttpServletResponse response,String message) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json; charset=UTF-8");
        Map map = new HashMap<>(4);
        map.put("code",-1);
        map.put("message",message);
        PrintWriter writer = response.getWriter();
        writer.append(JSONObject.toJSONString(map));
        writer.flush();
        IOUtils.closeQuietly(writer);
    }
    private static void export(HttpServletResponse response, PoiBean poiBean) throws IOException {
        String name = URLEncoder.encode(poiBean.getFileName(),"UTF-8") + (poiBean.varIs03 ? ".xls":".xlsx");
        log.debug("生成结束，开始下载:{}",name);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename="+ name);
        log.debug("生成结束，开始写入流");
        poiBean.workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        IOUtils.closeQuietly(response.getOutputStream());
    }
    private static PoiBean generate(Workbook workbook, Consumer consumer){
        PoiBean poiBean = new PoiBean(System.currentTimeMillis());
        poiBean.workbook = workbook;
        log.debug("{},开始生成,sheet个数:{}",poiBean.time,poiBean.workbook.getNumberOfSheets());
        if(poiBean.workbook.getNumberOfSheets() <= 0){
            poiBean.sheet = poiBean.workbook.createSheet("sheet1");
            poiBean.row = poiBean.sheet.createRow(0);
            log.debug("{},开始生成,创建sheet1",poiBean.time);
        }else {
            poiBean.row = poiBean.sheet.getRow(0);
        }
        poiBean.varIs03 = workbook instanceof HSSFWorkbook;
        log.debug("{},开始生成>>>lambda,是否03版:{}",poiBean.time,poiBean.varIs03);
        consumer.accept(poiBean);
        log.debug("{},结束生成>>>lambda",poiBean.time);
        return poiBean;
    }

    public static class PoiBean{
        private final long time;
        
        String fileName;
        
        boolean varIs03;
        
        Workbook workbook;
        
        Sheet sheet;
        Row row;
        Cell cell;
        CellStyle cellStyle;
        
        List styleList;
        PoiBean(long time){ this.time = time; }
        public PoiBean setFileName(String fileName) { this.fileName = fileName;return this; }
        public String getFileName() { return StringUtils.isEmpty(fileName)?"文件导出"+System.currentTimeMillis():fileName; }

        
        public Font initNewFont(Consumer consumer){
            log.debug("{},创建新字体>>>lambda",time);
            Font font = workbook.createFont();
            FontBean bean = new FontBean(font);
            consumer.accept(bean);
            log.debug("{},创建新字体<< consumer){
            log.debug("{},创建新样式>>>lambda",time);
            CellStyle style = workbook.createCellStyle();
            StyleBean bean = new StyleBean(style,workbook);
            consumer.accept(bean);
            log.debug("{},创建新样式<< consumer){
            log.debug("{},设置当前样式>>>lambda",time);
            consumer.accept(new StyleBean(cell.getCellStyle(),workbook));
            log.debug("{},设置当前样式<< consumer){
            log.debug("{},设置新样式>>>lambda",time);
            cellStyle = workbook.createCellStyle();
            consumer.accept(new StyleBean(cellStyle,workbook));
            log.debug("{},设置新样式<< consumer){
            log.debug("{},设置 workbook sheet >>>lambda",time);
            consumer.accept(workbook, sheet);
            log.debug("{},设置 workbook sheet << 0) {
                for (int i = 0,l = widths.length; i < l; i++) {
                    if( null == widths[i]){ continue; }
                    sheet.setColumnWidth(i,Math.max(0,widths[i])*256);
                }
            }
            return this;
        }
        
        public PoiBean columnsFormat(Map formatMap, int[] columns, Consumer consumer){
            if( null == formatMap || null == columns || null == consumer ){ return this; }
            Map map = new TreeMap<>();
            formatMap.forEach((k,vs)->{ for (int v : vs) { map.put(v,k); } });
            CellStyle cs = workbook.createCellStyle();
            // 设置公共样式
            log.debug("{},批量设置 公共样式 >>>lambda,{}",time,formatMap.keySet());
            consumer.accept(new StyleBean(cs,workbook));
            log.debug("{},批量设置 格式化+公共样式 >>>,{}",time,map);
            styleList = new ArrayList<>(columns.length);
            CellStyle cellStyle;
            for (int column : columns) {
                cellStyle = workbook.createCellStyle();
                cellStyle.cloneStyleFrom(cs);
                if(map.containsKey(column)){
                    cellStyle.setDataFormat(workbook.createDataFormat().getFormat(map.get(column)));
                }
                sheet.setDefaultColumnStyle(column,cellStyle);
                styleList.add(cellStyle);
            }
            log.debug("{},批量设置 格式化+公共样式 <<<",time);
            return this;
        }
        
        public PoiBean sheetName(String name){
            log.debug("{},修改sheet名 <<< {}",time,name);
            workbook.setSheetName(workbook.getActiveSheetIndex(),name);
            return this;
        }

        
        public PoiBean merge(int rowMerge,int cellMerge){
        	if( null == cell ){ cell = row.createCell(0); }
            int r = row.getRowNum(),c = cell.getColumnIndex();
            log.debug("{},合并单元格,行:{}+{},列:{}+{}",time,r,rowMerge,c,cellMerge);
            sheet.addMergedRegion(new CellRangeAddress(r,r + rowMerge,c,c + cellMerge));
            return this;
        }
        
        public void vs(boolean ditto,String... values){
            vls(ditto,values);
        }
        
        public void vls(boolean ditto,String[] values){
            if( null == values || values.length <= 0 ){ return; }
            for (String val : values) {
                cellNext().thisVal(ditto,val);
            }
        }
        public PoiBean val(int cellIndex, String val){
            cell(cellIndex);
            return thisVal(val);
        }
        public PoiBean val(int rowIndex,int cellIndex,String val){ row(rowIndex);return val(cellIndex,val); }
        public PoiBean index(int rowIndex,int cellIndex){ row(rowIndex);cell(cellIndex);return this; }
        
        public PoiBean thisVal(String val){
        	if( null == cell ){ cell = row.createCell(0); }
            cell.setCellType(CellType.STRING);
            cell.setCellValue(( null == val || val.length() <= 0 ) ? "": val);
            return this;
        }
        public void thisVal(boolean ditto, String val){
        	if( null == cell ){ cell = row.createCell(0); }
            cell.setCellType(CellType.STRING);
            cell.setCellValue(( null == val || val.length() <= 0 ) ? "": val);
            ditto(ditto);
        }
        
        private void ditto(boolean ditto){
            if(ditto && null != cellStyle){
                cell.setCellStyle(cellStyle);
            }else if (!ditto && !varIs03 && null != styleList){
                // 解决 07版 单元格 默认格式 赋值后丢失问题
                int index = cell.getColumnIndex();
                if(styleList.size() > index){
                    cell.setCellStyle(styleList.get(index));
                }
            }
        }

        private void sheet(int index){
            log.debug("{},sheet : {}",time,index);
            if(null == (sheet = workbook.getSheetAt(index))){ sheet = workbook.createSheet(); }
        }
        private void row(int index){
            log.debug("{},row : {}",time,index);
            index = Math.max(0,index);
            if(null == (row = sheet.getRow(index))){ row = sheet.createRow(index); }
        }
        private void cell(int index){
            log.debug("{},cell : {}",time,index);
            index = Math.max(0,index);
            if(null == (cell = row.getCell(index))){ cell = row.createCell(index); }
        }
        public PoiBean sheetNext(){ sheet(workbook.getActiveSheetIndex() + 1); return this; }
        public PoiBean rowNext(){ row(sheet.getLastRowNum() + 1); return this; }
        public PoiBean rowNext(int skip){ row(sheet.getLastRowNum() + skip); return this; }
        public PoiBean cellNext(){ cell(row.getLastCellNum()); return this; }
        public PoiBean cellNext(int skip){ cell(row.getLastCellNum() + skip); return this; }
    }
    public static class FontBean{
        private final Font font;
        FontBean(Font font){ this.font = font; }
        
        public FontBean color(IndexedColors val){ font.setColor(val.getIndex()); return this; }
        
        public FontBean italic(boolean val){ font.setItalic(val); return this; }
        
        public FontBean fontName(String val){ font.setFontName(val); return this; }
        
        public FontBean underline(FontUnderline val){ font.setUnderline(val.getBytevalue()); return this; }
        
        public FontBean bold(boolean val){ font.setBold(val); return this; }
        
        public FontBean strikeout(boolean val){ font.setStrikeout(val); return this; }
        
        public FontBean fontHeight(int val){ font.setFontHeightInPoints((short)val); return this; }
        
        public FontBean typeOffset(int val){ font.setTypeOffset((short) val); return this; }
        
        public FontBean charSet(int val){ font.setCharSet(val); return this; }
    }
    public static class StyleBean{
        private final CellStyle style;
        private DataFormat format;
        private final Workbook workbook;
        StyleBean(CellStyle style,Workbook workbook){ this.style = style; this.workbook = workbook; }
        
        public StyleBean font(Font font){ style.setFont(font);return this; }
        
        public StyleBean locked(boolean val){ style.setLocked(val);return this; }
        
        public StyleBean hidden(boolean val){ style.setHidden(val);return this; }
        
        public StyleBean dataFormat(String val){
            if( null == format ){ this.format = workbook.createDataFormat(); }
            style.setDataFormat(format.getFormat(val));
            return this;
        }
        
        public StyleBean alignment(HorizontalAlignment val){ style.setAlignment(val);return this; }
        
        public StyleBean vertical(VerticalAlignment val){ style.setVerticalAlignment(val);return this; }
        
        public StyleBean border(BorderStyle val,Border... borders){
            if( null != val && null != borders ){
                for (Border border : borders) {
                    if( null == border ){ continue; }
                    switch (border){
                        case TOP: style.setBorderTop(val); break;
                        case BOTTOM: style.setBorderBottom(val); break;
                        case LEFT: style.setBorderLeft(val); break;
                        case RIGHT: style.setBorderRight(val); break;
                        default: break;
                    }
                }
            }
            return this;
        }
        
        public StyleBean borderColor(IndexedColors val,Border... borders){
            if( null != val && null != borders ){
                for (Border border : borders) {
                    if( null == border ){ continue; }
                    switch (border){
                        case TOP: style.setTopBorderColor(val.getIndex()); break;
                        case BOTTOM: style.setBottomBorderColor(val.getIndex()); break;
                        case LEFT: style.setLeftBorderColor(val.getIndex()); break;
                        case RIGHT: style.setRightBorderColor(val.getIndex()); break;
                        default: break;
                    }
                }
            }
            return this;
        }
        
        public StyleBean wrap(boolean val){ style.setWrapText(val);return this; }
        
        public StyleBean shrinkToFit(boolean val){ style.setShrinkToFit(val);return this; }
        
        public StyleBean rotation(short val){ style.setRotation(val);return this; }
        
        public StyleBean indention(short val){ style.setIndention(val);return this; }
        
        public StyleBean fillPattern(FillPatternType val){ style.setFillPattern(val);return this; }
        
        public StyleBean fillFore(short val){ style.setFillForegroundColor(val);return this; }
        
        public StyleBean fillBackground(short val){ style.setFillBackgroundColor(val);return this; }
    }
    public enum Border {
        TOP, RIGHT, BOTTOM, LEFT
    }
}

测试代码

其中使用了自己的工具类DateFormatEnum

jdk(java)8 日期格式化工具-使用枚举处理

import ***.enums.DateFormatEnum;
import ***.utils.ExcelKit;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import static ***.utils.ExcelKit.Border;
@GetMapping("testExcel")
@ApiOperation(value = "测试导出",notes = "测试导出-[jk-zyl]")
public void testExcel(HttpServletResponse response){
    try {
        // 有内容的列
        int[] columns = {0,1,2};
        // 文本内容 列，批量格式化
        final Map strFormat = Collections.singletonMap("@", new int[]{0,1});
        //ExcelKit.exportExcel07
        ExcelKit.exportExcel03(response,pb -> {
            // 默认文本 字体
            Font strFont = pb.initNewFont(c -> c.fontHeight(12));
            // 初始化 文件名、sheet名称、文本字体、边框、默认列宽
            pb.setFileName("测试下载"+System.currentTimeMillis()).sheetName("第一页").columnsWidth(14,30,22)
                    .columnsFormat(strFormat,columns,c -> c.font(strFont).border(BorderStyle.THIN, Border.BOTTOM,Border.LEFT,Border.RIGHT))
                    .init((w,s)-> s.setDefaultColumnWidth(17));
            // 标题 字体，粗体、红色、18号
            Font titleFont = pb.initNewFont(c -> c.bold(true).color(IndexedColors.RED).fontHeight(18));
            // 合并单元格，设置标题样式，标题内容
            pb.merge(1,2).newCellStyle(s -> s.font(titleFont)
                    .alignment(HorizontalAlignment.CENTER).vertical(VerticalAlignment.CENTER)
                    .border(BorderStyle.THIN, Border.TOP,Border.LEFT,Border.RIGHT)).thisVal(true,"测试标题---");
            // 列头字体，加粗，14号
            Font headFont = pb.initNewFont(c -> c.fontHeight(14).bold(true));
            // 列头，边框、字体，内容
            pb.rowNext(2).newCellStyle(s -> s.border(BorderStyle.THIN, Border.TOP,Border.BOTTOM,Border.LEFT,Border.RIGHT)
                    .font(headFont).alignment(HorizontalAlignment.CENTER)).vs(true,"序号", "名称", "日期");
            // 数据列
            String format = DateFormatEnum.y_M_d_1.format();
            for (int i = 0; i < 4; i++) {
                pb.rowNext().vs(false,i+"","测试zyl",format);
            }
        });
    } catch (Exception e) {
        logger.error("异常",e);
        try {
            ExcelKit.fail(response,e.getMessage());
        } catch (Exception ex) {
            logger.error("异常",ex);
        }
    }
}

测试

失败的情况，返回报文

成功导出

成功导出-07

成功导出-03

寄语

工具类当前只是自用，如果觉得方法不全可以自己在添加。代码比较简陋，请手下留情(⊙o⊙)…

自定义POI的excel工具类-xls-xlsx

Java相关栏目本月热门文章