java导出excel封装

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.Map;


@Component
public class ExcelComponent {

    
    public HSSFWorkbook generateExcel(ExcelFormatInput input) throws Exception{
        try {
            // step1: 参数校验
            this.checkParams(input);

            // step2: 构建excel表头及大体样式
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFSheet sheet = wb.createSheet(input.getSheetName());
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) (26.25 * 15));
            for (int i = 0,length = input.getCellNameArray().length; i < length; i++) {
                row.createCell(i).setCellValue(input.getCellNameArray()[i]);
                sheet.setColumnWidth(i,(short) (26.25 * 200));
            }

            // step3: 构建数据行
            for (int i = 0,size = input.getDataList().size(); i  map = input.getDataList().get(i);
                HSSFRow rows = sheet.createRow(i + 1);
                for (int j = 0,length = input.getDataCloumnNameArray().length; j < length; j++) {
                    rows.setHeight((short) (26.25 * 15));
                    rows.createCell(j).setCellValue(String.valueOf(map.get(input.getDataCloumnNameArray()[j])));
                }
            }
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("生成Excel失败"+e.getMessage());
        }
    }

    
    private void checkParams(ExcelFormatInput input) throws Exception{
        if (StringUtils.isEmpty(input.getSheetName()))
            throw new Exception("sheetName不能为空");

        if (CollectionUtils.isEmpty(input.getDataList()))
            throw new Exception("数据列表dataList不能为空");

        if (input.getCellNameArray() == null || input.getCellNameArray().length == 0)
            throw new Exception("excel列名列表cellNameArray不能为空");

        if (input.getDataCloumnNameArray() == null || input.getDataCloumnNameArray().length == 0)
            throw new Exception("数据字段列表dataCloumnNameArray不能为空");

        if (input.getDataCloumnNameArray().length != input.getCellNameArray().length)
            throw new Exception("数据字段列表dataCloumnNameArray与excel列名列表cellNameArray不匹配");
    }
}

import java.io.Serializable;
import java.util.List;
import java.util.Map;

public class ExcelFormatInput implements Serializable {

    private static final long serialVersionUID = 8249936897348760813L;

    private String[] CellNameArray;

    private String[] dataCloumnNameArray;

    private String sheetName;

    private List> dataList;

    public String[] getCellNameArray() {
        return CellNameArray;
    }

    public void setCellNameArray(String[] cellNameArray) {
        CellNameArray = cellNameArray;
    }

    public String[] getDataCloumnNameArray() {
        return dataCloumnNameArray;
    }

    public void setDataCloumnNameArray(String[] dataCloumnNameArray) {
        this.dataCloumnNameArray = dataCloumnNameArray;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List> getDataList() {
        return dataList;
    }

    public void setDataList(List> dataList) {
        this.dataList = dataList;
    }

    public ExcelFormatInput() {
    }

    public ExcelFormatInput(String[] cellNameArray, String[] dataCloumnNameArray, String sheetName, List> dataList) {
        CellNameArray = cellNameArray;
        this.dataCloumnNameArray = dataCloumnNameArray;
        this.sheetName = sheetName;
        this.dataList = dataList;
    }
}

主方法:

    @RequestMapping("/acctAdjustProcessLogExport")
    public void acctAdjustProcessLogExport(AcctAdjustQuery acctAdjustQuery, HttpServletResponse response, HttpServletRequest request) throws Exception{
        OutputStream os = null;
        try {
            String sheetName = "调账记录";
            String[] cellNameArray = {"调账时间", "订单号", "商户号", "商户名称", "实付金额", "FTP支付结算金额", "代付金额", "FTP代付结算金额", "支付结果", "代付结果", "原支付结果", "原代付结果", "操作人"};
            String[] dataColumnNameArray = {"createTime", "orderNum", "merId", "shopName", "payAmt", "ftpPayCalcAmt", "paidAmt", "ftpPaidCalcAmt", "paySts", "paidSts", "oldPaySts", "oldPaidSts", "createUser"};

            List> dataList = acctAdjustService.queryProcessLogExprotData(acctAdjustQuery);
            ExcelFormatInput input = new ExcelFormatInput(
                    cellNameArray,
                    dataColumnNameArray,
                    sheetName,
                    dataList
            );
            HSSFWorkbook wb = excelComponent.generateExcel(input);
            os = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + DownChineseEncode.setFileDownHeader(request, response, "调账记录.xls"));
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw  new Exception("调账记录导出失败"+e.getMessage());
        } finally {
            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }