java 导出excel

1.需要传入三个集合,标题的集合,key值对应数据的字段集合(可以不传但是数据就必须和标题一一对应),数据的集合

2.输出流

try (OutputStream out = response.getOutputStream()) {
    String fileName = "test";
    response.setContentType("application/x-download");
    response.setCharacterEncoding("UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
    export.write(out);
}


3.导出工具类

@Service
public class ExcelService {

    /**
     *
     * @param titles 表格第一行,表头
     * @param keys 实体类对象字典keys; 如果为null，则返回获取dataList所有字段;
     * @param dataList 数据集合
     * @param <T> 如果是实体bean，header是反射获取所有字段；如果是JsonObject，header是获取所有key
     * @return
     * @throws Exception
     */
    public <T> HSSFWorkbook export(List<String> titles, List<String> keys, List<T> dataList) throws Exception {

        //1.创建工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        if (dataList == null || dataList.isEmpty()) {
            return wb;
        }

        String[] excelHeader;
        if(keys == null){
            Collection<String> fields = fields(dataList.get(0));
            excelHeader = fields.toArray(new String[]{});
        }else{
            excelHeader = keys.toArray(new String[]{});
        }


        //2.创建表
        HSSFSheet sheet = wb.createSheet("sheet1");
        //2.1设置列宽(必须先设置列宽然后设置行高，不然列宽没有效果)
        sheet.setDefaultColumnWidth(15);
        //2.2设置列高
//        sheet.setDefaultRowHeight((short) 100);
        //3.创建行
        //4.生成样式
        HSSFCellStyle style = wb.createCellStyle();
        //4.1设置水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //4.2设置垂直居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //4.3设置背景颜色
//        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
        //4.4设置边框
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setBorderRight(BorderStyle.THIN);
//        style.setBorderTop(BorderStyle.THIN);
        //5 设置字体
//        HSSFFont font = wb.createFont();
//        font.setFontHeightInPoints((short) 10);
//        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//        font.setBold(true);
//        font.setFontName("宋体");
        //6.把字体给当前样式
//        style.setFont(font);
        //7.设置表头
        String[] titlesArray = titles.toArray(new String[]{});
        HSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < titlesArray.length; i++) {
            HSSFCell cell = headerRow.createCell(i);
            cell.setCellValue(titlesArray[i]);
            cell.setCellStyle(style);
        }
        //添加数据
        for (int i = 0; i < dataList.size(); i++) {
            HSSFRow dataRow = sheet.createRow(i + 1);
            T line = dataList.get(i);
            if (JSONObject.class.isInstance(line)) {
                JSONObject data = (JSONObject) line;
                for (int j = 0; j < excelHeader.length; j++) {
                    String header = excelHeader[j];
                    dataRow.createCell(j).setCellValue(data.getString(header));
                }
            } else {// java bean
                for (int j = 0; j < excelHeader.length; j++) {
                    String header = excelHeader[j];
                    String UTitle = Character.toUpperCase(header.charAt(0)) + header.substring(1, header.length());
                    String methodName = "get" + UTitle;
                    Method method = line.getClass().getDeclaredMethod(methodName);
                    String data = method.invoke(line) == null ? "" : method.invoke(line).toString();
                    dataRow.createCell(j).setCellValue(data);
                }
            }
        }
        return wb;
    }

    private Collection<String> fields(Object line) {
        if (JSONObject.class.isInstance(line)) {
            return ((JSONObject) line).keySet();
        } else {// java bean
            return Arrays.stream(line.getClass().getDeclaredFields()).map(field -> field.getName()).collect(Collectors.toList());
        }
    }
}