spring boot execl 下载

一、使用POI做execl下载，封装列为String[]数组做为列数组,封装List<String[]>集合数组做为execl内容集合数组，最后数据库查询集合对象类封装为集合数组，不说废话直接上代码。

二、代码

1、加入pom.xml坐标

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>RELEASE</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>RELEASE</version>
</dependency>

2、Controller 中实现

 

    excelData.setFileName(CommunalUtils.userFileName);
    String[] strHead = new String[]{"用户ID","姓名","性别","年龄","手机","邮箱","角色","注册时间"};
    excelData.setHead(strHead);
    List<SystemUserDownload> listuser = systemUserMapper.sysUserDownload();
    List<String[]> list = new ArrayList<String[]>();
    for (int i = 0; i < listuser.size(); i++) {
        String[] str = ExcelUtil.getFiledValues(listuser.get(i));
        list.add(str);
    }
    excelData.setData(list);
    ExcelUtil.exportExcel(response,excelData,CommunalUtils.userExeclName);
}

3、对象集合转数组集合 有利于统一转化 不需要对POI进行再生成。

 

 

 

 

 

@Data
public class SystemUserDownload{

    private Integer sysId;
    private String  username;
    private Integer sysSex;
    private Integer sysAge;
    private String  sysMobile;
    private String  sysEamil;
    private String roleName;
    private Date sysDate;

}

@Data
public class ExcelData {
    private String fileName;
    private String[] head;
    private List<String[]> data;

}

**
 * 获取对象的所有属性值，返回一个对象数组
 * */
public static String[] getFiledValues(Object o){
    String[] fieldNames=getFiledName(o);
    String[] value=new String[fieldNames.length];
    for(int i=0;i<fieldNames.length;i++){
        value[i]=getFieldValueByName(fieldNames[i], o);
    }
    return value;
}

/**
 * 获取属性名数组
 * */
private static String[] getFiledName(Object o){
    Field[] fields=o.getClass().getDeclaredFields();
    String[] fieldNames=new String[fields.length];
    for(int i=0;i<fields.length;i++){
        fieldNames[i]=fields[i].getName();
    }
    return fieldNames;
}

/**
 * 根据属性名获取属性值
 * */
private static String getFieldValueByName(String fieldName, Object o) {
    try {
        String firstLetter = fieldName.substring(0, 1).toUpperCase();
        String getter = "get" + firstLetter + fieldName.substring(1);
        Method method = o.getClass().getMethod(getter, new Class[] {});
        Object value = method.invoke(o, new Object[] {});
        if (fieldName.contains("Date")){
            return DateUtils.getDateString(value,"yyyy-MM-dd");
        }
        return String.valueOf(value);
    } catch (Exception e) {
        log.error(e.getMessage(),e);
        return null;
    }
}

4、进行execl进行文件生成下载

/**
 * 方法名：exportExcel
 * 功能：导出Excel
 */
public static void exportExcel(HttpServletResponse response, ExcelData data,String execlName) {
    try {
        HSSFWorkbook workbook = new HSSFWorkbook();//实例化HSSFWorkbook
        HSSFSheet sheet = workbook.createSheet(execlName);//创建一个Excel表单，参数为sheet的名字
        setTitle(workbook, sheet, data.getHead());//设置表头
        setData(sheet, data.getData());//设置单元格并赋值
        setBrowser(response, workbook, data.getFileName()); //设置浏览器下载
        log.info("=============导出解析成功!==============");
    } catch (Exception e) {
        log.info("===========导出解析失败!===============");
        e.printStackTrace();
    }
}

/**
 * 方法名：setTitle
 * 功能：设置表头
 */
private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
    try {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        for (int i = 0; i <= str.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
        }
        HSSFCellStyle style = workbook.createCellStyle(); //设置为居中加粗,格式化时间格式
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);//字号
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        HSSFCell cell;//创建表头名称
        for (int j = 0; j < str.length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(str[j]);
            cell.setCellStyle(style);
        }
    } catch (Exception e) {
        log.info("=================导出时设置表头失败！==================");
        e.printStackTrace();
    }
}

/**
 * 方法名：setData
 * 功能：表格赋值
 */
private static void setData(HSSFSheet sheet, List<String[]> data) {
    try{
        int rowNum = 1;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(rowNum);
            for (int j = 0; j < data.get(i).length; j++) {
                row.createCell(j).setCellValue(data.get(i)[j]);
            }
            rowNum++;
        }
        log.info("==============表格赋值成功！================");
    }catch (Exception e){
        log.info("================表格赋值失败！==============");
        e.printStackTrace();
    }
}

/**
 * 方法名：setBrowser
 * 功能：使用浏览器下载
 */
private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
    try {
        response.reset();//清空response
        //设置response的Header
        response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        OutputStream os = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        workbook.write(os);//将excel写入到输出流中
        os.flush();
        os.close();
        log.info("==============设置浏览器下载成功！====================");
    } catch (Exception e) {
        log.info("===============设置浏览器下载失败！===================");
        e.printStackTrace();
    }

}

 

 

5、注意事项

     js中ajax请求不支持execl返回，故可生成一个from表单提交