用Fastjson SXSSFWorkbook 实现Excel单sheet与多sheet导出

     鉴于工作需要大数据量(一般10w左右，或者更多数据量)的excel导出功能，由于现有项目中的 XSSFWorkbook包陈旧、以及jackson封装的数据转化不够高效，所以做了一些修改，优化sql查询和数据处理，基本满足导出秒级实现。

直接上代码了（demo）

1.HTML demo

var data = {
    "sfzh" : $("#jsycx_sfzh").val(),
    "cph" : $("#jsycx_cph option:selected").html()||"全部",
    "xm" : $("#jsycx_xm").val(),
    "gsm" : $("#jsycx_gsm option:selected").html()||"全部",
    "fwzh" : $("#jsycx_fwzh").val(),
    "jyxkz" : $("#jsycx_jyxkz").val(),
    "city" : $("#jsycx_city option:selected").html()};
url = "../../tjfx/jsycxxlsx?data=" + JSON.stringify(data) , window.open(url)

HTML页面的处理比较简单，直接就是打开新的页面就行，请求带上需要的参数。

2.Controller demo

/**
	 * 驾驶员导出
	 * @param request
	 * @param data
	 * @param response
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/jsycxxlsx")
	@ResponseBody
	public String jsycxxlsx(HttpServletRequest request,
			@RequestParam("data") String data, HttpServletResponse response) throws IOException {
		Map<String, Object> paramMap = FastJsonUtil.stringToMap(data);
		String sfzh = String.valueOf(paramMap.get("sfzh"));
		String cph = String.valueOf(paramMap.get("cph"));
		String xm = String.valueOf(paramMap.get("xm"));
		String gsm = String.valueOf(paramMap.get("gsm"));
		String fwzh = String.valueOf(paramMap.get("fwzh"));
		String jyxkz = String.valueOf(paramMap.get("jyxkz"));
		String city = String.valueOf(paramMap.get("city"));
		String a[] = { "身份证号", "姓名", "业户名称","经营许可证号", "服务证号", "车牌号", "资格证有效期止","分值","证照状态" };// 导出列明
		String b[] = { "ID_NUMBER", "NAME", "COMPANY_NAME","COMPANY_LICENSE_NUMBER", "VEHICLE_ID", "PLATE_NUMBER", "VALID_PERIOD_END","ASSESS_SCORE","STATUS_NAME" };// 导出map中的key
		String gzb = "驾驶员信息";// 导出sheet名和导出的文件名
		String msg = tjfxService.jsycxxlsx(sfzh, cph, xm, gsm,fwzh,jyxkz,city);
		List<Map<String, Object>> list = DownloadAct.strlist(msg);// 导出的数据
		downloadAct.download(request, response, a, b, gzb, list);
		return null;
	}

Controller主要是设置表名 列名 列参数 以及FastJsonUtil解析request参数进行sql查询，具体的sql执行方法就不贴了这个，最后返回的是封装好的FastJsonUtil的Json字符串msg。

3.DownloadAct.strlist方法 demo

/**
     * json字符串转换成List<Map>集合  
     * json字符串格式 [{a:null,b:null}]
     * @return
     * @author erxi
     * @date : 2018年12月3日 下午2:22:40
     */
    public static List<Map<String, Object>> strlist(String jsonString) {
       List<Map<String, Object>> list = null;
        try {
            list = JSON.parseObject(jsonString, new TypeReference<List<Map<String, Object>>>() {});
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

这是FastJsonUtil封装类中的其中一个json字符串转成List<Map>集合的格式，后面会贴出自己封装的FastJsonUtil类。

4.downloadAct.download方法  demo

/**
     * demo返回http响应
     * @param request
     * @param response
     * @param a
     * @param b
     * @param gzb
     * @param list
     * @return
     * @throws IOException
     * @author erxi
     * @date : 2018年12月3日 下午2:22:40
     */
    public String download(HttpServletRequest request,
              HttpServletResponse response,
              String[] a,String[] b,String gzb,List<Map<String,Object>> list) throws IOException{
        String fileName = gzb;//excle文件名
        //填充projects数据
        String columnNames[] = a;//列名
        String keys[] = b;//map中的key
        ByteArrayOutputStream os = new ByteArrayOutputStream();//内存缓冲区的数据，转换成字节数组
        try {
            ExcelUtil.createWorkBook(list,keys,columnNames,gzb).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[1024];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
        return null;
    }

通过response把文件输出到页面进行导出。

---

4.ExcelUtil.createWorkBook方法 demo

4.1.单sheet

/**
 * demo ExcelUtil封装
 * @author erxi
 * @date : 2018年12月3日 下午2:22:40
 */
public class ExcelUtil {
        public static Workbook createWorkBook(List<Map<String, Object>> list,
                     String []keys,String columnNames[],String gzb) throws IOException {
               //缓存的值
               int rowaccess = 100;
               // 创建excel工作簿
               SXSSFWorkbook wb = new SXSSFWorkbook(rowaccess);
               //数据库数据量
               int listSize = list.size();
               // 创建第一个sheet（页），并命名
               Sheet sheet = wb.createSheet(gzb);
               // 创建第一行
               Row row = sheet.createRow((short) 0);
               //设置列名
               for(int i=0;i<columnNames.length;i++){
                   Cell cell = row.createCell(i);
                   cell.setCellValue(columnNames[i]);
               }
               //设置每行每列的值
               int rowss=1;
              for (int i = 0; i < listSize; i++) {
                   // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                   // 创建一行，在页sheet上
                   Row row1 = sheet.createRow((short) rowss+i);
                   // 在row行上创建一个方格
                   for(short j=0;j<keys.length;j++){
                       Cell cell = row1.createCell(j);
                       cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                   }
                   //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                   if(i % rowaccess==0){
                      ((SXSSFSheet) sheet).flushRows();
                     }
               }
               return wb;
           }
}

4.2.多sheet  该demo加入了对行列表头样式的设定

/**
 * demo ExcelUtil封装
 * @author erxi
 * @date : 2018年12月3日 下午2:22:40
 */
public class ExcelUtil {
        public static Workbook createWorkBook(List<Map<String, Object>> list,
                     String []keys,String columnNames[],String gzb) throws IOException {
               //缓存的值
               int rowaccess = 100;
               // 创建excel工作簿
               SXSSFWorkbook wb = new SXSSFWorkbook(rowaccess);
               //数据库数据量
               int listSize = list.size();
               //最大记录数
               int maxCache = 5000;
               //sheet 页数
               int sheet_num = 0;
               if(0 == listSize%maxCache){
                     sheet_num = listSize/maxCache;
               }else{
                     sheet_num = listSize/maxCache + 1;
               }
               //每个sheet
               for(int k=0; k<sheet_num; k++){
                      // 创建第一个sheet（页），并命名
                     Sheet sheet = wb.createSheet(gzb+k);
                     // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
                     for(int i=0;i<keys.length;i++){
                         sheet.setColumnWidth((short) i, (short) (35.7 * 150));
                     }
                     // 创建第一行
                     Row row = sheet.createRow((short) 0);
                     // 创建两种单元格格式
                     CellStyle cs = wb.createCellStyle();
                     CellStyle cs2 = wb.createCellStyle();
                     // 创建两种字体
                     Font f = wb.createFont();
                     Font f2 = wb.createFont();
                     // 创建第一种字体样式（用于列名）
                     f.setFontHeightInPoints((short) 10);
                     f.setColor(IndexedColors.BLACK.getIndex());
                     f.setBoldweight(Font.BOLDWEIGHT_BOLD);
                     // 创建第二种字体样式（用于值）
                     f2.setFontHeightInPoints((short) 10);
                     f2.setColor(IndexedColors.BLACK.getIndex());
                     Font f3=wb.createFont();
                     f3.setFontHeightInPoints((short) 10);
                     f3.setColor(IndexedColors.RED.getIndex());
                     // 设置第一种单元格的样式（用于列名）
                     cs.setFont(f);
                     cs.setBorderLeft(CellStyle.BORDER_THIN);
                     cs.setBorderRight(CellStyle.BORDER_THIN);
                     cs.setBorderTop(CellStyle.BORDER_THIN);
                     cs.setBorderBottom(CellStyle.BORDER_THIN);
                     cs.setAlignment(CellStyle.ALIGN_CENTER);
                     // 设置第二种单元格的样式（用于值）
                     cs2.setFont(f2);
                     cs2.setBorderLeft(CellStyle.BORDER_THIN);
                     cs2.setBorderRight(CellStyle.BORDER_THIN);
                     cs2.setBorderTop(CellStyle.BORDER_THIN);
                     cs2.setBorderBottom(CellStyle.BORDER_THIN);
                     cs2.setAlignment(CellStyle.ALIGN_CENTER);
                     //设置列名
                     for(int i=0;i<columnNames.length;i++){
                         Cell cell = row.createCell(i);
                         cell.setCellValue(columnNames[i]);
                         cell.setCellStyle(cs);
                     }
                     //设置每行每列的值
                     int rowss=1;
                     if(k == sheet_num - 1){
                           for (int i = k*maxCache; i < listSize; i++) {
                                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                                // 创建一行，在页sheet上
                                Row row1 = sheet.createRow((short) rowss+i-k*maxCache);
                                // 在row行上创建一个方格
                                for(short j=0;j<keys.length;j++){
                                    Cell cell = row1.createCell(j);
                                    cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
//                                  cell.setCellStyle(cs2);
                                }
                                //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                                if(i % rowaccess==0){
                                   ((SXSSFSheet) sheet).flushRows();
                                   }
                            }
                     }else{
                           for (int i = k*maxCache; i < (k+1)*maxCache; i++) {
                                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                                // 创建一行，在页sheet上
                                Row row1 = sheet.createRow((short) rowss+i-k*maxCache);
                                // 在row行上创建一个方格
                                for(short j=0;j<keys.length;j++){
                                    Cell cell = row1.createCell(j);
                                    cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                                    cell.setCellStyle(cs2);
                                }
                                //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                                if(i % rowaccess==0){
                                   ((SXSSFSheet) sheet).flushRows();
                                   }
                            }
                     }
               }
               return wb;
           }
}

---

5.总结

1. FastJson的高效数据算法是基本实现得到数据到页面导出是秒级实现。
2. SXSSFWorkbook适用于大数据量下的批量导出，合理的使用内存和磁盘，提升效率，避免了传统导出数据量过大导致内存溢出。

 

最后贴上FastJsonUtil类

/**
* FastJsonUtil 工具类
*
* @author erxi
* @date : 2018年12月3日 下午4:09:27
*/
public class FastJsonUtil {

    private static final SerializeConfig config;

    static {
        config = new SerializeConfig();
        config.put(java.util.Date.class, new JSONLibDataFormatSerializer()); // 使用和json-lib兼容的日期输出格式
        config.put(java.sql.Date.class, new JSONLibDataFormatSerializer()); // 使用和json-lib兼容的日期输出格式
    }

    private static final SerializerFeature[] features = {
            SerializerFeature.WriteMapNullValue, // 输出空置字段
            SerializerFeature.WriteNullListAsEmpty, // list字段如果为null，输出为[]，而不是null
            SerializerFeature.WriteNullNumberAsZero, // 数值字段如果为null，输出为0，而不是null
            SerializerFeature.WriteNullBooleanAsFalse, // Boolean字段如果为null，输出为false，而不是null
            SerializerFeature.WriteNullStringAsEmpty // 字符类型字段如果为null，输出为""，而不是null
    };

    /**
     * 转换成字符串
     *
     * @param object
     * @return
     */
    public static String toJSONString(Object object) {
        return JSON.toJSONString(object, config);
    }

    /**
     * 转换成字符串 ,带有过滤器
     *
     * @param object
     * @return
     */
    public static String toJSONWithFeatures(Object object) {
        return JSON.toJSONString(object, config, features);
    }

    /**
     * 转成bean对象
     *
     * @param text
     * @return
     */
    public static Object toBean(String text) {
        return JSON.parse(text);
    }

    /**
     * 转成具体的泛型bean对象
     *
     * @param text
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> T toBean(String text, Class<T> clazz) {
        return JSON.parseObject(text, clazz);
    }

    /**
     * 转换为数组Array
     *
     * @param text
     * @param <T>
     * @return
     */
    public static <T> Object[] toArray(String text) {
        return JSON.parseArray(text).toArray();
    }

    /**
     * 转换为具体的泛型数组Array
     *
     * @param text
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> Object[] toArray(String text, Class<T> clazz) {
        return JSON.parseArray(text, clazz).toArray();
    }

    /**
     * 转换为具体的泛型List
     *
     * @param text
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> toList(String text, Class<T> clazz) {
        return JSON.parseArray(text, clazz);
    }

    /**
     * 将javabean转化为序列化的json字符串
     *
     * @param keyvalue
     * @return
     */
    public static Object beanToJson(KeyValue keyvalue) {
        String textJson = JSON.toJSONString(keyvalue);
        Object objectJson = JSON.parse(textJson);
        return objectJson;
    }

    /**
     * 将string转化为序列化的json字符串
     *
     * @param text
     * @return
     */
    public static Object textToJson(String text) {
        Object objectJson = JSON.parse(text);
        return objectJson;
    }

    /**
     * json字符串转化为map
     *
     * @param s
     * @return
     */
    public static Map stringToMap(String s) {
        Map m = JSONObject.parseObject(s);
        return m;
    }

    /**
     * 将map转化为string
     *
     * @param m
     * @return
     */
    public static String mapToString(Map m) {
        String s = JSONObject.toJSONString(m);
        return s;
    }

    /**
     * 用fastjson 将jsonString 解析成 List<Map<String,Object>>
     *
     * @param jsonString
     * @return
     */
    public static List<Map<String, Object>> getListMap(String jsonString) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            // 两种写法
            // list = JSON.parseObject(jsonString, new
            // TypeReference<List<Map<String, Object>>>(){}.getType());

            list = JSON.parseObject(jsonString,
                    new TypeReference<List<Map<String, Object>>>() {
                    });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

代码基本都有注解，如有错误，谢谢指正。