JAVA导出EXCEL表格

工作时遇到了一个excel 导出的问题 , 原本是想直接用前端的table直接做excel 导出,但是又发现有点不满足需求

是 同是将三个小表格放入到一个excel表格中去,于是就上网找资料

这篇贴子就写的比较清楚详细 于是快乐的抄袭就开始了,但是发现一些不足之处,于是就稍微改动了一下,觉得还是满足使用的

#贴上代码

import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.*;
import java.util.*;

public class ExcelUtil {

    static HSSFCellStyle style;

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, List<Map<String, Object>> values, HSSFWorkbook wb) {
        // 第一步，创建一个HSSFWorkbook
        if (wb == null) {
            wb = new HSSFWorkbook();
            style = getStyle(wb);
            /*创建一个sheet*/
            HSSFSheet sheet = wb.createSheet(sheetName);
            /*设置标题*/
            setTitle(sheet, title);
            /*将map里的数据设置到sheet 中去*/
            setData(sheet, values);
        } else {
            style = getStyle(wb);
            HSSFSheet sheet = wb.getSheet(sheetName);
            setTitle(sheet, title);
            setData(sheet, values);
        }
        return wb;
    }

    private static void setTitle(HSSFSheet sheet, String[] title) {
        int lastRowNum = sheet.getLastRowNum();
        /*获取 最后一行的行号*/
        HSSFRow row = sheet.createRow(lastRowNum + 1);
        for (int i = 0; i < title.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

    }

    /**
     * 构建样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle getStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return style;
    }

    /**
     * 填充数据的方法
     *
     * @param sheet
     * @param mapList
     */
    private static void setData(HSSFSheet sheet, List<Map<String, Object>> mapList) {
        int i = sheet.getLastRowNum();
        for (Map<String, Object> value : mapList) {
            HSSFRow row = sheet.createRow(i + 1);
            int j = 0;
            for (Map.Entry<String, Object> entry : value.entrySet()) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue((String) entry.getValue());
                j++;
            }
            i++;
        }
    }

    public static void main(String[] args) {
        String sheetName = "学生信息表";//Sheet名称
        /*说明: 如果是用map的话 必须使用linkHashMap 这样的数据才是有序的,否则就是无序的*/
        Map<String, Object> map = new LinkedHashMap<>();
        List<Map<String, Object>> mapList = new ArrayList<>();
        map.put("name", "小明");
        map.put("sex", "男");
        map.put("age", "10");
        map.put("class", "广东小学");
        map.put("school", "三年二班");
        mapList.add(map);
        String[] title = {"名称", "性别", "年龄", "学校", "班级"};
        //excel文件名
        String fileName = "D://学生信息表" + System.currentTimeMillis() + ".xls";
        //第一次加载数据
        HSSFWorkbook workbook = getHSSFWorkbook(sheetName, title, mapList, null);
        //同一个sheet 中添加其他信息
        Map<String, Object> map1 = new LinkedHashMap<>();
        List<Map<String, Object>> mapList1 = new ArrayList<>();
        map1.put("hight", "165cm");
        map1.put("weight", "80kg");
        map1.put("like", "打篮球");
        mapList1.add(map1);
        String[] titles = {"身高", "体重", "爱好"};
        workbook = getHSSFWorkbook(sheetName, titles, mapList1, workbook);
        OutputStream os = null;
        try {
            os = new FileOutputStream(new File(fileName));
            workbook.write(os);
            os.flush();
            os.close();
            System.out.println("successful");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}

#测试结果是

刚刚好满足我的要求

补充: 由于写日志的时候快要下班了,但是启动项目的时候发现一个异常, 异常就是springBoot 不兼容poi 3.6的版本,所以重新换了4.0.0的版本才得以生效

#贴出前端的代码

/**
     * 导出excel表格
     *
     * @param request
     * @param response
     * @return
     * @author Lengff
     * @time 2018-11-1 18:43:06
     */
    @RequestMapping("export")
    @ResponseBody
    public void export(HttpServletRequest request, HttpServletResponse response) {
        String sheetName = "全站概况统计";
        String fileName = "全站概况统计" + System.currentTimeMillis() + ".xls";
        String[] title = {"统计", "新增激活设备", "新增注册数", "新增注册转化率", "新增注册数", "启动量", "活跃启动量", "平均启动次数", "登录人数", "登录转化率"};
        String[] titlB = {"统计", "累计激活设备", "累计注册数", "注册转化率"};
        List<Map<String, Object>> todayCount = siteCountService.getTodayCount(1);
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, todayCount, null);
        List<Map<String, Object>> countYesterday = siteCountService.getTodayCount(2);
        wb = ExcelUtil.getHSSFWorkbook(sheetName, title, countYesterday, wb);
        List<Map<String, Object>> countAll = siteCountService.countAll();
        wb = ExcelUtil.getHSSFWorkbook(sheetName, titlB, countAll, wb);
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //发送响应流方法
    private void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

成功的导出excel文件

#最后的结果

 

总结:excel 导出其实是用的比较多的功能 , 实现的方式也有很多种, 之前也做过很多种 , 但是觉得都挺麻烦的, 唯独这次的方式比较方便, 因为是把bean 转成了map , 所以就不需要考虑 对应关系, 但是要注意的是必须是用linkHashMap 要不然就会乱序,一样会让人头疼不已的!