Java Excel导出为Excel文件工具类(xlsx)版本

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;

public class ExportManager {
    private static final Logger logger = LoggerFactory.getLogger(ExportManager.class);

    //自定义所有列宽(优先级低于columnWidths)，>0：列宽度，0：自适应列宽(根据数据的最大长度决定)
    public static final String COLUMN_WIDTH="COLUMN_WIDTH";
    //0:第一页显示标题，1：每页都显示标题
    public static final String TITLE_PER_SHEET="TITLE_PER_SHEET";
    //每页输出行数,默认DEFAULT_ROWS_PER_SHEET
    public static final String ROWS_PER_SHEET="ROWS_PER_SHEET";
    //标题在第几行输出，默认第一行
    public static final String TITLE_AT_ROW="TITLE_AT_ROW";
    //数据从第几行开始,默认标题行之后
    public static final String DATA_AT_ROW="DATA_AT_ROW";

    //默认每页数据行数（纯数据，不包括标题等）
    public static final int DEFAULT_ROWS_PER_SHEET=30000;
    //编码转换名称输出
    private HashMap<String, HashMap<String,String>> codeToNames = new HashMap();

    private XSSFWorkbook wb = null;

    public ExportManager(){
        wb = new XSSFWorkbook();
    }

    public void exportExcel(OutputStream os, JSONArray data, String[] titleNames,String[] columnNames) throws Exception {
        exportExcel(os,data,titleNames,columnNames,null,-1,null,null,null);
    }

    public void exportExcel(OutputStream os, JSONArray data, String[] titleNames,String[] columnNames,HSSFCellStyle titleStyle,HSSFCellStyle dataStyle) throws Exception {
        exportExcel(os,data,titleNames,columnNames,null,-1,null,titleStyle,dataStyle);
    }

    /**
     *
     * @param os 输出流，必填
     * @param datas 数据列表，必填
     * @param titleNames 标题列表，不填则不输出标题
     * @param columnNames 数据中的列名，不填则输出所有值
     * @param style 输出设置，不填按默认输出
     * @param titleStyle 标题输出风格，不填按默认输出
     * @param dataStyle 数据输出风格，不填按默认输出
     * @throws Exception
     */
    public void exportExcel(OutputStream os, JSONArray datas, String[] titleNames,String[] columnNames,int[] columnWidths,
                            int dataHeigth,JSONObject style,HSSFCellStyle titleStyle,HSSFCellStyle dataStyle) throws Exception{
        int columnWidth=0;
        boolean titlePerSheet=true;
        int titleAtRow = 0;
        int dataAtRow = -1;
        int rowsPerSheet = 50000;

        if(style!=null){
            if(style.containsKey(COLUMN_WIDTH)){
                columnWidth = style.getInteger(COLUMN_WIDTH);
            }
            if(style.containsKey(TITLE_PER_SHEET)){
                titlePerSheet = style.getInteger(TITLE_PER_SHEET)==1;
            }
            if(style.containsKey(ROWS_PER_SHEET)){
                rowsPerSheet = style.getInteger(ROWS_PER_SHEET);
            }
            if(style.containsKey(TITLE_AT_ROW)){
                titleAtRow = style.getInteger(TITLE_AT_ROW);
            }
            if(style.containsKey(DATA_AT_ROW)){
                dataAtRow = style.getInteger(DATA_AT_ROW);
            }
        }
        titleAtRow = (titleNames==null)?-1:(titleAtRow>=0?titleAtRow:0);
        dataAtRow = (dataAtRow>=0)?dataAtRow:(titleAtRow>=0?titleAtRow+1:0);

        int dataSize = datas.size();

        if(dataSize==0){
            Sheet sheet0 = wb.createSheet("Sheet0");
            writeTitle(sheet0,0,titleNames,true,titleAtRow,titleStyle);
        }else{
            int sheetNums = dataSize / rowsPerSheet;
            if(dataSize%rowsPerSheet!=0) {
                sheetNums ++;
            }

            int currDataIdx = 0;
            for(int sheetNum=0;sheetNum<sheetNums;sheetNum++){
                String sheetName = "Sheet"+(sheetNum+1);
                XSSFSheet sheet = wb.createSheet(sheetName);

                //写每页数据
                int[] maxColumnLengths = null;
                for(int i=0,atRow = dataAtRow;i<rowsPerSheet;i++,atRow++){
                    JSONObject data = datas.getJSONObject(currDataIdx);

                    if(currDataIdx==0 && (columnNames==null || columnNames.length==0)){
                        columnNames = (String[]) data.keySet().toArray();
                    }

                    if(i==0){
                        maxColumnLengths = new int[columnNames.length];
                    }

                    XSSFRow rowData = sheet.createRow(atRow);
                    if(dataHeigth>0) {
                        rowData.setHeight((short)dataHeigth);
                    }
                    int j=0;
                    for(String columnName:columnNames){
                        XSSFCell cell = rowData.createCell(j);
                        if(dataStyle!=null) {
                            cell.setCellStyle(dataStyle);
                        }
                        if(data.containsKey(columnName)) {
                            String v = data.getString(columnName);
                            v = (v == null ? "" : getNameForCode(columnName,v));
                            cell.setCellValue(v);
                            maxColumnLengths[j] = Math.max(maxColumnLengths[j], v == null ? 0 : v.getBytes().length);
                        }

                        j++;
                    }

                    currDataIdx ++;
                    if(currDataIdx>=dataSize){
                        break;
                    }
                }

                //写每页标题
                writeTitle(sheet,sheetNum,titleNames,titlePerSheet,titleAtRow,titleStyle);

                //设置列宽
                if(maxColumnLengths==null){
                    maxColumnLengths = new int[titleNames.length];
                }
                for(int k=0;k<maxColumnLengths.length;k++){
                    if(k<titleNames.length){
                        maxColumnLengths[k] = Math.max(maxColumnLengths[k],titleNames[k].getBytes().length);
                    }
                }
                setColumnWidth(sheet,columnWidths,columnWidth,maxColumnLengths);
            }
        }

        wb.write(os);
    }

    private String getNameForCode(String columnName,String code){
        String v = code;
        if(codeToNames!=null && codeToNames.containsKey(columnName)){
            HashMap<String,String> codeToName = codeToNames.get(columnName);
            if(codeToName.containsKey(code)){
                v = codeToName.get(code);
            }
        }
        return v;
    }

    private void setColumnWidth(XSSFSheet sheet,int[] columnWidths,int columnWidth,int[] maxColumnLengths){
        if(columnWidths!=null){
            for(int k=0;k<columnWidths.length;k++){
                sheet.setColumnWidth(k,columnWidths[k] * 256);
            }
        }else{
            for(int k=0;k<maxColumnLengths.length;k++){
                if(columnWidth>0) {
                    sheet.setColumnWidth(k, columnWidth * 256);
                }else{
                    sheet.setColumnWidth(k, maxColumnLengths[k] * 256);
                }
            }
        }
    }

    private void writeTitle(Sheet sheet,int sheetNum,String[] titleNames,boolean titlePerSheet,int titleAtRow,HSSFCellStyle titleStyle){
        if(titleNames!=null && titleNames.length>0 && (sheetNum==0 || titlePerSheet)){
            //写标题
            Row rowTitle = sheet.createRow(titleAtRow);
            for(int i=0;i<titleNames.length;i++){
                Cell cell = rowTitle.createCell(i);
                if(titleStyle!=null) {
                    cell.setCellStyle(titleStyle);
                }
                cell.setCellValue(titleNames[i]);
            }
        }
    }

    public XSSFCellStyle getCellStyle(String fontName, int fontSize, boolean bold, HorizontalAlignment align,
                                      boolean border,boolean hidden,boolean wraped){
        XSSFFont font = wb.createFont();
        font.setBold(bold);
        font.setFontName(fontName);
        font.setFontHeightInPoints((short)fontSize);

        XSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(align);
        if(border){
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
        }
        style.setHidden(hidden);
        style.setWrapText(wraped);

        return style;
    }

    public static void setResponseHeader(HttpServletRequest requests, HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes("ISO8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {
                logger.error(e.getMessage());
            }
            response.setContentType("application/xls;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Set-Cookie", "fileDownload=true; path=/");

            String allowOrigin = requests.getHeader("Origin");
            String allowHeader = requests.getHeader("Access-Control-Request-Headers");
            response.setHeader("Access-Control-Allow-Origin",allowOrigin);
            response.setHeader("Access-Control-Allow-Credentials", "true");
            response.setHeader("Access-Control-Allow-Methods", "OPTIONS, POST, PUT, GET, OPTIONS, DELETE");
            response.setHeader("Access-Control-Allow-Headers", allowHeader);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static String getDisplay(String v,String[] names){
        return getDisplay(Integer.parseInt(v),names,v);
    }

    public static String getDisplay(int v,String[] names,String defaultValue){
        if(v>=0 && v< names.length){
            return names[v];
        }else{
            return defaultValue;
        }
    }

    public void setCodeToNames(HashMap codeToNames){
        this.codeToNames = codeToNames;
    }

    public void close(){
        if(wb!=null){
            try {
                wb.close();
                wb = null;
            }catch (Exception e){
                logger.error(e.getMessage());
            }
        }
    }
}

导出功能使用示例:

@RequestMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {

        Object object = redisTemplate.opsForValue().get("PAYBACK_NEW_ADD_DATA");
        JSONArray data = JSONArray.parseArray(object.toString());

        String[] titleName={"是否承兑汇票","汇款方名称","收款银行","收款银行账号","汇款金额","汇款日期","票据号码","汇票到期日","承兑人名称","备注","校验结果"};
        String[] valueName={"reserved_3","remittance_party_name","bank_name","bank_account_num","receipt_amount","receipt_date","reserved_4","reserved_5","reserved_6","comments","check_Result"};

        Date date = new Date();
        SimpleDateFormat format0 = new SimpleDateFormat("yyyy-MM-dd");
        String time = format0.format(date);
        String fileName = "你指定的文件名字";

        ExportManager.setResponseHeader(request,response, fileName);
        OutputStream os = response.getOutputStream();

        ExportManager excel = new ExportManager();
        excel.exportExcel(os,data,titleName,valueName);

        os.flush();
        os.close();
        excel.close();
    }