poi导出大数据excel

2017-11-20 19:13:47 zjx4321 阅读数 743
  1.  @RequestMapping("exportUserToExcel.do")  
  2.     public void exportUserToExcel(UserBean userBean,HttpServletRequest request,HttpServletResponse response) throws Exception{  
  3.         SXSSFWorkbook wb = new SXSSFWorkbook(5000);//内存中保留 10000 条数据,以免内存溢出,其余写入 硬盘       
  4.         int count = userService.getUserExcelCount(userBean);  
  5.         int page = (int) Math.ceil(count / 500000)+1;  
  6.         for (int i = 1; i <= page; i++) {  
  7.             System.out.println(i);  
  8.             userBean.setPage(i);  
  9.             userBean.setRows(500000);  
  10.             userBean.calculate();  
  11.             List<UserBean> userList = userService.getUserExcelList(userBean);  
  12.             Sheet sheet = wb.createSheet("sheet"+i);   
  13.             Row row = sheet.createRow(0);  
  14.             Cell cell = row.createCell(0);  
  15.             cell.setCellValue("id");  
  16.             cell = row.createCell(1);  
  17.             cell.setCellValue("员工姓名");  
  18.             cell = row.createCell(2);  
  19.             cell.setCellValue("员工性别");  
  20.             cell = row.createCell(3);  
  21.             cell.setCellValue("开号日期");  
  22.             cell = row.createCell(4);  
  23.             cell.setCellValue("账号");  
  24.             cell = row.createCell(5);  
  25.             cell.setCellValue("密码");  
  26.             cell = row.createCell(6);  
  27.             cell.setCellValue("头像");  
  28.             cell = row.createCell(7);  
  29.             cell.setCellValue("状态");  
  30.             cell = row.createCell(8);  
  31.             cell.setCellValue("备注");  
  32.             for (int j = 0; j < userList.size(); j++) {  
  33.                 row = sheet.createRow(j+1);  
  34.                 row.createCell(0).setCellValue(userList.get(i).getId());  
  35.                 row.createCell(1).setCellValue(userList.get(j).getName());  
  36.                 row.createCell(2).setCellValue(userList.get(j).getSex() == 1 ? "男":"女");  
  37.                 row.createCell(3).setCellValue(userList.get(j).getRegisTime());  
  38.                 row.createCell(4).setCellValue(userList.get(j).getLoginNumer());  
  39.                 row.createCell(5).setCellValue(userList.get(j).getPassword());  
  40.                 row.createCell(6).setCellValue(userList.get(j).getHeadImg());  
  41.                 row.createCell(7).setCellValue(userList.get(j).getStatusName());  
  42.                 row.createCell(8).setCellValue(userList.get(j).getRemark());  
  43.             }  
  44.         }  
  45.         FileOutputStream fout = new FileOutputStream("G:\\students.xlsx");    
  46.         wb.write(fout);  
  47.         System.out.println("完成");  
  48.         fout.flush();  
  49.         fout.close();  
  50.         wb.dispose();  
  51.   
  52.         FileUtil.downloadFile(request, response,"G:\\students.xlsx","测试.xlsx");  
  53.         File file = new File("G:\\students.xlsx");  
  54.         file.delete();  
  55.     }  
2018-09-25 20:18:05 qq_35206261 阅读数 30671

一. 简介

          excel导出,如果数据量在百万级,会出现俩点内存溢出的问题:

          1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决;

          2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的会自动缓存在硬盘的临时目录上,同时,并不会存在页面卡顿的情况;

          3. 为了能够使用不同的mapper并分批写数据, 采用了外观模式和模板方法模式,大体分三步:

              a. 根据总数量生成excel,确定sheet的数量和写标题;

              b. 写数据,在可变的匿名内部类中实现写入逻辑;
              c. 转换输出流进行下载;

          4. 使用案例在3.3 ServiceImpl中,可自行书写。

          5. 最近太忙,写的太仓促,性能我感觉还有一倍的优化空间,比如循环次数,现在存在无意义空循环(这个耗时比较多)的情况,缓冲流,mybatis的浮标等,待优化........   

          6. 优化空间很大  刚试了下 把空循环去掉  4个字段 90W条数据  40s  180W  75s  300W 122s

          7. 转战阿里开源的EasyExcel了, 那个内存控制在kb级别,绝对不会内存溢出。使用说明请参见博主的另一篇文章:

阿里开源(EasyExcel)---导出EXCEL

          8. 导入的也有,请参见另一篇文章: 阿里开源(EasyExcel)---导入EXCEL

二. 工具代码

2.1 配置

2.1.1 pom.xml

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

注: 如果是springboot2.0,则不需要poi依赖,如果是1.0,则需要poi依赖,并且poi和poi-ooxml的版本要保持一致。

          别的依赖我就不加了。

2.1.2 application.yml

# pagehelper
pagehelper:
    helperDialect: mysql
    reasonable: false # 如果没有数据  返回空 而非最后一页的数据
    supportMethodsArguments: true
    params: count=countSql
    returnPageInfo: check

注:  reasonable一定要为false, 其他的我就不粘了。

2.2 ExcelConstant

package com.yzx.caasscs.constant;

/**
 * @author qjwyss
 * @date 2018/9/19
 * @description EXCEL常量类
 */
public class ExcelConstant {

    /**
     * 每个sheet存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;


    /**
     * 每个sheet的写入次数 5
     */
    public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;


}

注: xlsx模式的excel每个sheet最多存储104W,此处我就每个sheet存储了 100W数据;每次查询20W数据; 自己根据内存来调合适的大小,写入次数待优化。

2.3 写数据委托类

package com.yzx.caasscs.util;

import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description EXCEL写数据委托类
 */
public interface WriteExcelDataDelegated {

    /**
     * EXCEL写数据委托类  针对不同的情况自行实现
     *
     * @param eachSheet     指定SHEET
     * @param startRowCount 开始行
     * @param endRowCount   结束行
     * @param currentPage   分批查询开始页
     * @param pageSize      分批查询数据量
     * @throws Exception
     */
    public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;


}

2.4 DateUtil工具类(非必须)

package com.yzx.caasscs.util;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description 日期工具类
 */
public class DateUtil {

    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";


    /**
     * 将日期转换为字符串
     *
     * @param date   DATE日期
     * @param format 转换格式
     * @return 字符串日期
     */
    public static String formatDate(Date date, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        return simpleDateFormat.format(date);
    }


}

2.5 POI工具类

package com.yzx.caasscs.util;


import com.yzx.caasscs.constant.ExcelConstant;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/18
 * @description POI导出工具类
 */
public class PoiUtil {

    private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);

    /**
     * 初始化EXCEL(sheet个数和标题)
     *
     * @param totalRowCount 总记录数
     * @param titles        标题集合
     * @return XSSFWorkbook对象
     */
    public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {

        // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
                (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));

        // 根据总记录数创建sheet并分配标题
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
            SXSSFRow headRow = sheet.createRow(0);

            for (int j = 0; j < titles.length; j++) {
                SXSSFCell headRowCell = headRow.createCell(j);
                headRowCell.setCellValue(titles[j]);
            }
        }

        return wb;
    }


    /**
     * 下载EXCEL到本地指定的文件夹
     *
     * @param wb         EXCEL对象SXSSFWorkbook
     * @param exportPath 导出路径
     */
    public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        try {
            fops = new FileOutputStream(exportPath);
            wb.write(fops);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 下载EXCEL到浏览器
     *
     * @param wb       EXCEL对象XSSFWorkbook
     * @param response
     * @param fileName 文件名称
     * @throws IOException
     */
    public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {

        response.setHeader("Content-disposition", "attachment; filename="
                + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出Excel到本地指定路径
     *
     * @param totalRowCount           总记录数
     * @param titles                  标题
     * @param exportPath              导出路径
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);

        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;


                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToLocalPath(wb, exportPath);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


    /**
     * 导出Excel到浏览器
     *
     * @param response
     * @param totalRowCount           总记录数
     * @param fileName                文件名称
     * @param titles                  标题
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);


        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;

                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToWebsite(wb, response, fileName);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


}

三. 使用DEMO

3.1 Controller

package com.yzx.caasscs.controller.organiza;

import com.yzx.caasscs.controller.BaseController;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import springfox.documentation.annotations.ApiIgnore;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户控制类
 */
@Api(tags = {"UserController"}, description = "用户Controller")
@RestController
@RequestMapping(value = "/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;


    @ApiOperation("导出用户EXCEL")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "loginUid", value = "登录用户UID", required = true),
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "uid", value = "用户UID", required = true)
    })
    @GetMapping("/export")
    public ResultVO<Void> exportUser(@ApiIgnore UserVO userVO, HttpServletResponse response) throws Exception {
        return this.userService.export(userVO, response);
    }

}

3.2 Service

package com.yzx.caasscs.service.organiza;

import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICE
 */
public interface UserService {


    /**
     * 导出用户EXCEL
     *
     * @param userVO
     * @return VOID
     * @throws Exception
     */
    ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception;


}

3.3 ServiceImpl

package com.yzx.caasscs.service.impl.organiza;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.yzx.caasscs.constant.middleware.RedisConstant;
import com.yzx.caasscs.constant.organiza.UserApartmentConstant;
import com.yzx.caasscs.constant.organiza.UserConstant;
import com.yzx.caasscs.constant.organiza.UserRoleConstant;
import com.yzx.caasscs.constant.sys.SysLogConstant;
import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserApartment;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserRole;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserApartmentMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserRoleMapper;
import com.yzx.caasscs.mapper.dscaasscs.sys.RoleMenuMapper;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.service.sys.SysLogService;
import com.yzx.caasscs.util.CommonUtil;
import com.yzx.caasscs.util.DateUtil;
import com.yzx.caasscs.util.PoiUtil;
import com.yzx.caasscs.util.WriteExcelDataDelegated;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserApartmentVO;
import com.yzx.caasscs.vo.organiza.UserRoleVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import com.yzx.caasscs.vo.sys.MenuVO;
import com.yzx.caasscs.vo.sys.RoleMenuVO;
import com.yzx.caasscs.vo.sys.SysLogVO;
import net.sf.json.JSONObject;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICEIMPL
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private HttpServletRequest request;


    @Override
    public ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception {

        // 总记录数
        Integer totalRowCount = this.userMapper.selectUserVOCount(userVO);

        // 导出EXCEL文件名称
        String filaName = "用户EXCEL";

        // 标题
        String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};

        // 开始导入
        PoiUtil.exportExcelToWebsite(response, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
            @Override
            public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {

                PageHelper.startPage(currentPage, pageSize);
                List<UserVO> userVOList = userMapper.selectUserVOList(userVO);

                if (!CollectionUtils.isEmpty(userVOList)) {

                    // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
                    for (int i = startRowCount; i <= endRowCount; i++) {
                        SXSSFRow eachDataRow = eachSheet.createRow(i);
                        if ((i - startRowCount) < userVOList.size()) {

                            UserVO eachUserVO = userVOList.get(i - startRowCount);
                            // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------

                            eachDataRow.createCell(0).setCellValue(eachUserVO.getUsername() == null ? "" : eachUserVO.getUsername());
                            eachDataRow.createCell(1).setCellValue(eachUserVO.getPassword() == null ? "" : eachUserVO.getPassword());
                            eachDataRow.createCell(2).setCellValue(eachUserVO.getUserState() == null ? "" : (eachUserVO.getUserState() == 1 ? "启用" : "停用"));
                            eachDataRow.createCell(3).setCellValue(eachUserVO.getNickname() == null ? "" : eachUserVO.getNickname());
                            eachDataRow.createCell(4).setCellValue(eachUserVO.getPosition() == null ? "" : eachUserVO.getPosition());
                            eachDataRow.createCell(5).setCellValue(eachUserVO.getMobile() == null ? "" : eachUserVO.getMobile());
                            eachDataRow.createCell(6).setCellValue(eachUserVO.getEmail() == null ? "" : eachUserVO.getEmail());
                            if (null != eachUserVO.getCreateUid()) {
                                eachDataRow.createCell(7).setCellValue(eachUserVO.getCreateUid());
                            }
                            if (null != eachUserVO.getCreateTime()) {
                                eachDataRow.createCell(8).setCellValue(DateUtil.formatDate(eachUserVO.getCreateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                            if (null != eachUserVO.getUpdateUid()) {
                                eachDataRow.createCell(9).setCellValue(eachUserVO.getUpdateUid());
                            }
                            if (null != eachUserVO.getUpdateTime()) {
                                eachDataRow.createCell(10).setCellValue(DateUtil.formatDate(eachUserVO.getUpdateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                        }
                    }
                }

            }
        });

        return ResultVO.getSuccess("导出用户EXCEL成功");
    }


}

3.4 mapper

package com.yzx.caasscs.mapper.dscaasscs.organiza;

import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.vo.organiza.UserVO;

import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/29
 * @description 用户MAPPER
 */
public interface UserMapper {


    /**
     * 查询用户列表
     *
     * @param userVO
     * @return UserVO集合
     * @throws Exception
     */
    List<UserVO> selectUserVOList(UserVO userVO) throws Exception;


    /**
     * 查询用户数量
     *
     * @param userVO
     * @return 用户数量
     * @throws Exception
     */
    Integer selectUserVOCount(UserVO userVO) throws Exception;

}

3.5 mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper" >

  <resultMap id="BaseResultMap" type="com.yzx.caasscs.entity.dscaasscs.organiza.User" >
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="state" property="state" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>


  <!-- 查询用户分页列表返回MAP -->
  <resultMap id="SelectUserVOListMap" type="com.yzx.caasscs.vo.organiza.UserVO">
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="user_state" property="userState" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="apartment_name" property="apartmentName" jdbcType="VARCHAR" />
    <result column="role_names" property="roleNames" jdbcType="VARCHAR" />
  </resultMap>


  <sql id="Base_Column_List" >
    uid, username, password, state, nickname, position, mobile, email, create_uid, create_time,
    update_uid, update_time
  </sql>


  <!-- 查询用户列表 -->
  <select id="selectUserVOList" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultMap="SelectUserVOListMap">
    SELECT
		U.uid, U.username, U.state AS user_state, U.nickname, U.position, U.mobile, U.email, U.create_uid, U.create_time,
		U.update_uid, U.update_time,
		A.apartment_name,
		(
			SELECT
				GROUP_CONCAT( R.role_name ) AS role_name_list
			FROM
				user_role AS UR
				LEFT JOIN role AS R ON R.rid = UR.rid
			WHERE
				UR.state > 0
				AND UR.uid = U.uid
			GROUP BY UR.uid
		) role_names
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
    ORDER BY U.create_time DESC

  </select>


  <!-- 查询用户列表数量 -->
  <select id="selectUserVOCount" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultType="java.lang.Integer" >
    SELECT
        COUNT(1)
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
  </select>
  

</mapper>

4. 测试

每个sheet存100W,每次查询写20W,每条记录10个字段,时间如下:

数据量 时间
100W 3.5分钟
150W 7分钟

 

 

2018-03-06 10:54:10 cyy_ln1001 阅读数 385
java中导出excel
第一步  创建一个个workbook对象
创建一个工作空间
设置表格样式
第二部 设置标题  设置表列sheet.createRow(rownum++); 创建一行
第三部  设置表列sheet.createRow(rownum++);创建一行
第四部 创建行对象
创建一个单元格设置 添加的行列  添加的列 还有值


 第五步,将excel文件存到指定位置


====================工具类




/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导出Excel文件(导出“XLSX”格式,支持大数据量导出   @see org.apache.poi.ss.SpreadsheetVersion)
 * @author ThinkGem
 * @version 2013-04-21
 */
public class ExportExcel {
   
   private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
         
   /**
    * 工作薄对象
    */
   private SXSSFWorkbook wb;
   
   /**
    * 工作表对象
    */
   private Sheet sheet;
   
   /**
    * 样式列表
    */
   private Map<String, CellStyle> styles;
   
   /**
    * 当前行号
    */
   private int rownum;
   
   /**
    * 注解列表(Object[]{ ExcelField, Field/Method })
    */
   List<Object[]> annotationList = Lists.newArrayList();
   
   /**
    * 构造函数
    * @param title 表格标题,传“空值”,表示无标题
    * @param cls 实体对象,通过annotation.ExportField获取标题
    */
   public ExportExcel(String title, Class<?> cls){
      this(title, cls, 1);
   }
   
   /**
    * 构造函数
    * @param title 表格标题,传“空值”,表示无标题
    * @param cls 实体对象,通过annotation.ExportField获取标题
    * @param type 导出类型(1:导出数据;2:导出模板)
    * @param groups 导入分组
    */
   public ExportExcel(String title, Class<?> cls, int type, int... groups){
      // Get annotation field 
      Field[] fs = cls.getDeclaredFields();
      for (Field f : fs){
         ExcelField ef = f.getAnnotation(ExcelField.class);
         if (ef != null && (ef.type()==0 || ef.type()==type)){
            if (groups!=null && groups.length>0){
               boolean inGroup = false;
               for (int g : groups){
                  if (inGroup){
                     break;
                  }
                  for (int efg : ef.groups()){
                     if (g == efg){
                        inGroup = true;
                        annotationList.add(new Object[]{ef, f});
                        break;
                     }
                  }
               }
            }else{
               annotationList.add(new Object[]{ef, f});
            }
         }
      }
      // Get annotation method
      Method[] ms = cls.getDeclaredMethods();
      for (Method m : ms){
         ExcelField ef = m.getAnnotation(ExcelField.class);
         if (ef != null && (ef.type()==0 || ef.type()==type)){
            if (groups!=null && groups.length>0){
               boolean inGroup = false;
               for (int g : groups){
                  if (inGroup){
                     break;
                  }
                  for (int efg : ef.groups()){
                     if (g == efg){
                        inGroup = true;
                        annotationList.add(new Object[]{ef, m});
                        break;
                     }
                  }
               }
            }else{
               annotationList.add(new Object[]{ef, m});
            }
         }
      }
      // Field sorting
      Collections.sort(annotationList, new Comparator<Object[]>() {
         public int compare(Object[] o1, Object[] o2) {
            return new Integer(((ExcelField)o1[0]).sort()).compareTo(
                  new Integer(((ExcelField)o2[0]).sort()));
         };
      });
      // Initialize
      List<String> headerList = Lists.newArrayList();
      for (Object[] os : annotationList){
         String t = ((ExcelField)os[0]).title();
         // 如果是导出,则去掉注释
         if (type==1){
            String[] ss = StringUtils.split(t, "**", 2);
            if (ss.length==2){
               t = ss[0];
            }
         }
         headerList.add(t);
      }
      initialize(title, headerList);
   }
   
   /**
    * 构造函数
    * @param title 表格标题,传“空值”,表示无标题
    * @param headers 表头数组
    */
   public ExportExcel(String title, String[] headers) {
      initialize(title, Lists.newArrayList(headers));
   }
   
   /**
    * 构造函数
    * @param title 表格标题,传“空值”,表示无标题
    * @param headerList 表头列表
    */
   public ExportExcel(String title, List<String> headerList) {
      initialize(title, headerList);
   }
   
   /**
    * 初始化函数
    * @param title 表格标题,传“空值”,表示无标题
    * @param headerList 表头列表
    */
   private void initialize(String title, List<String> headerList) {
      this.wb = new SXSSFWorkbook(500);
      this.sheet = wb.createSheet("Export");
      this.styles = createStyles(wb);
      // Create title
      if (StringUtils.isNotBlank(title)){
         Row titleRow = sheet.createRow(rownum++);
         titleRow.setHeightInPoints(30);
         Cell titleCell = titleRow.createCell(0);
         titleCell.setCellStyle(styles.get("title"));
         titleCell.setCellValue(title);
         sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
               titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
      }
      // Create header
      if (headerList == null){
         throw new RuntimeException("headerList not null!");
      }
      Row headerRow = sheet.createRow(rownum++);
      headerRow.setHeightInPoints(16);
      for (int i = 0; i < headerList.size(); i++) {
         Cell cell = headerRow.createCell(i);
         cell.setCellStyle(styles.get("header"));
         String[] ss = StringUtils.split(headerList.get(i), "**", 2);
         if (ss.length==2){
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
                  new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
         }else{
            cell.setCellValue(headerList.get(i));
         }
         sheet.autoSizeColumn(i);
      }
      for (int i = 0; i < headerList.size(); i++) {  
         int colWidth = sheet.getColumnWidth(i)*2;
           sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
      }
      log.debug("Initialize success.");
   }
   
   /**
    * 创建表格样式
    * @param wb 工作薄对象
    * @return 样式列表
    */
   private Map<String, CellStyle> createStyles(Workbook wb) {
      Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
      
      CellStyle style = wb.createCellStyle();
      style.setAlignment(CellStyle.ALIGN_CENTER);
      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      Font titleFont = wb.createFont();
      titleFont.setFontName("Arial");
      titleFont.setFontHeightInPoints((short) 16);
      titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      style.setFont(titleFont);
      styles.put("title", style);

      style = wb.createCellStyle();
      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      style.setBorderRight(CellStyle.BORDER_THIN);
      style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderLeft(CellStyle.BORDER_THIN);
      style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderTop(CellStyle.BORDER_THIN);
      style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setBorderBottom(CellStyle.BORDER_THIN);
      style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
      Font dataFont = wb.createFont();
      dataFont.setFontName("Arial");
      dataFont.setFontHeightInPoints((short) 10);
      style.setFont(dataFont);
      styles.put("data", style);
      
      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_LEFT);
      styles.put("data1", style);

      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_CENTER);
      styles.put("data2", style);

      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
      style.setAlignment(CellStyle.ALIGN_RIGHT);
      styles.put("data3", style);
      
      style = wb.createCellStyle();
      style.cloneStyleFrom(styles.get("data"));
//    style.setWrapText(true);
      style.setAlignment(CellStyle.ALIGN_CENTER);
      style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
      style.setFillPattern(CellStyle.SOLID_FOREGROUND);
      Font headerFont = wb.createFont();
      headerFont.setFontName("Arial");
      headerFont.setFontHeightInPoints((short) 10);
      headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
      headerFont.setColor(IndexedColors.WHITE.getIndex());
      style.setFont(headerFont);
      styles.put("header", style);
      
      return styles;
   }

   /**
    * 添加一行
    * @return 行对象
    */
   public Row addRow(){
      return sheet.createRow(rownum++);
   }
   

   /**
    * 添加一个单元格
    * @param row 添加的行
    * @param column 添加列号
    * @param val 添加值
    * @return 单元格对象
    */
   public Cell addCell(Row row, int column, Object val){
      return this.addCell(row, column, val, 0, Class.class);
   }
   
   /**
    * 添加一个单元格
    * @param row 添加的行
    * @param column 添加列号
    * @param val 添加值
    * @param align 对齐方式(1:靠左;2:居中;3:靠右)
    * @return 单元格对象
    */
   public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
      Cell cell = row.createCell(column);
      String cellFormatString = "@";
      try {
         if(val == null){
            cell.setCellValue("");
         }else if(fieldType != Class.class){
            cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
         }else{
            if(val instanceof String) {
               cell.setCellValue((String) val);
            }else if(val instanceof Integer) {
               cell.setCellValue((Integer) val);
               cellFormatString = "0";
            }else if(val instanceof Long) {
               cell.setCellValue((Long) val);
               cellFormatString = "0";
            }else if(val instanceof Double) {
               cell.setCellValue((Double) val);
               cellFormatString = "0.00";
            }else if(val instanceof Float) {
               cell.setCellValue((Float) val);
               cellFormatString = "0.00";
            }else if(val instanceof Date) {
               cell.setCellValue((Date) val);
               cellFormatString = "yyyy-MM-dd HH:mm";
            }else {
               cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
                  "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
            }
         }
         if (val != null){
            CellStyle style = styles.get("data_column_"+column);
            if (style == null){
               style = wb.createCellStyle();
               style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
                 style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
               styles.put("data_column_" + column, style);
            }
            cell.setCellStyle(style);
         }
      } catch (Exception ex) {
         log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
         cell.setCellValue(val.toString());
      }
      return cell;
   }

   /**
    * 添加数据(通过annotation.ExportField添加数据)
    * @return list 数据列表
    */
   public <E> ExportExcel setDataList(List<E> list){
      for (E e : list){
         int colunm = 0;
         Row row = this.addRow();
         StringBuilder sb = new StringBuilder();
         for (Object[] os : annotationList){
            ExcelField ef = (ExcelField)os[0];
            Object val = null;
            // Get entity value
            try{
               if (StringUtils.isNotBlank(ef.value())){
                  val = Reflections.invokeGetter(e, ef.value());
               }else{
                  if (os[1] instanceof Field){
                     val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
                  }else if (os[1] instanceof Method){
                     val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
                  }
               }
               // If is dict, get dict label
               if (StringUtils.isNotBlank(ef.dictType())){
                  val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
               }
            }catch(Exception ex) {
               // Failure to ignore
               log.info(ex.toString());
               val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
         }
         log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
      }
      return this;
   }
   
   /**
    * 输出数据流
    * @param os 输出数据流
    */
   public ExportExcel write(OutputStream os) throws IOException{
      wb.write(os);
      return this;
   }
   
   /**
    * 输出到客户端
    * @param fileName 输出文件名
    */
   public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
      response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
      write(response.getOutputStream());
      return this;
   }
   
   /**
    * 输出到文件
    * @param fileName 输出文件名
    */
   public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
      FileOutputStream os = new FileOutputStream(name);
      this.write(os);
      return this;
   }
   
   /**
    * 清理临时文件
    */
   public ExportExcel dispose(){
      wb.dispose();
      return this;
   }
   
// /**
//  * 导出测试
//  */
// public static void main(String[] args) throws Throwable {
//    
//    List<String> headerList = Lists.newArrayList();
//    for (int i = 1; i <= 10; i++) {
//       headerList.add("表头"+i);
//    }
//    
//    List<String> dataRowList = Lists.newArrayList();
//    for (int i = 1; i <= headerList.size(); i++) {
//       dataRowList.add("数据"+i);
//    }
//    
//    List<List<String>> dataList = Lists.newArrayList();
//    for (int i = 1; i <=1000000; i++) {
//       dataList.add(dataRowList);
//    }
//
//    ExportExcel ee = new ExportExcel("表格标题", headerList);
//    
//    for (int i = 0; i < dataList.size(); i++) {
//       Row row = ee.addRow();
//       for (int j = 0; j < dataList.get(i).size(); j++) {
//          ee.addCell(row, j, dataList.get(i).get(j));
//       }
//    }
//    
//    ee.writeFile("target/export.xlsx");
//
//    ee.dispose();
//    
//    log.debug("Export success.");
//    
// }

}

事例

String fileName = null;
List<WorkerCashDeposit> list = super.findList(workerCashDeposit);
if (!CollectionUtils.isEmpty(list)) {
    List<String> excelTitle = Arrays.asList("姓名", "手机号", "缴纳金额", "缴纳时间", "缴纳方式", "签约方式");
    ExportExcel exportExcel = new ExportExcel("师傅保证金管理", excelTitle);
    Row row = null;
    for (int i = 0; i < list.size(); i++) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (list.get(i) != null) {
            WorkerCashDeposit ci = list.get(i);
            row = exportExcel.addRow();
            exportExcel.addCell(row, 0, ci.getByWorker().getWorkerName());
            exportExcel.addCell(row, 1, ci.getByWorker().getTelephone());
            exportExcel.addCell(row, 2, ci.getAmount());
            if (ci.getCreateTime() != null) {
                exportExcel.addCell(row, 3, simpleDateFormat.format(ci.getCreateTime()));
            }
            String str = "";
            if ("1".equals(ci.getThirdPayType())) {
                str = "支付宝";
            }
            if ("2".equals(ci.getThirdPayType())) {
                str = "微信";
            }
            exportExcel.addCell(row, 4, str);
            String str1 = "";
            if ("0".equals(ci.getSignStatus())) {
                str1 = "未签约";
            }
            if ("1".equals(ci.getSignStatus())) {
                str1 = "签约成功";
            }
            exportExcel.addCell(row, 5, str1);


        }
    }
    fileName = "downList" + new Date().getTime() + ".xlsx";
    HttpServletRequest request = Servlets.getRequest();
    String absolutePath = request.getSession().getServletContext().getRealPath(Global.USERFILES_BASE_URL);
    File file = new File(absolutePath);
    if (!file.exists()) {
        file.mkdir();
    }
    exportExcel.writeFile(absolutePath + "/" + fileName);
}










2019-02-19 18:10:54 zfy1355 阅读数 1509

导出数据是一个应用常用的功能,而使用java语言时常用的工具类莫过于POI。不过当数据量很大时,会经常遇到OOM的问题。通过两天尝试,终于解决了OOM的问题,以下分享一下解决过程。

优化1:首先,我们对导出文件的格式做了一定优化,使用csv的方式做导出,发现仍然会出现oom。通过梳理逻辑,发现导出的过程是将先将所有结果集一次性读入内存再进行文件的转化导出。于是对结果进行分页转换输出,不过对于改造的文件格式,业务方却不是很认可,要求再次将导出格式设置为excel,于是再次将导出格式定义为excel,进行了优化2的改造,所以对系统的改造如果涉及到业务,一定要先沟通。

优化2:方案是这样,对大文件进行拆分成小文件进行保存,最后对所有临时文件进行打包导出,询问业务,不同意,于是又增加了对所有小文件进行merge。不过merge文件的过程注定是一个耗费时间的过程,因为需要的打开多个小文件再进行合并,虽然导出文件不再oom,但是超时了,于是进行优化3。

优化3:经过调研在POI中除了HSSFWorkbook和XSSFWorkbook之外,还有一个SXSSFWorkbook,该类通过设置rowAccessWindowSize
参数,可以调整保存在内存中的对象数量,大于rowAccessWindowSize的数据会缓存到磁盘上。通常缓存的文件大小会比结果大,如果磁盘空间有限,可以通过setCompressTempFiles参数进行文件压缩(不过压缩会消耗部分性能,实际情况综合取舍)。通过分页加载数据到SXSSFWorkbook中的方式避免了全量数据加载内存导致的溢出情况,但是当数据量巨大时,还是推荐可以将文件进行拆分,否则还是会遇到请求超时的情况。

优化4:将优化3的方案改为异步下载,通过邮件的方式发送,可以解决超大数据导出超时的问题。

已上是大文件导出的解决过程,本文仅分享方法,代码google吧。

2018-11-13 20:40:46 qq_16855077 阅读数 765

前提:

由于本人采取的是利用反射的方式,这样的好处,可以兼容,导出所有类型的数据,例如,订单,充值记录等等。

既然是公有的接口,肯定有一定的约束规范。

(1)导出的表头需要自己设置一个动态数组

(2) 头部的宽度需要自己设置,也可以自己写一个方法,根据字体的长度,形成一个头部的动态数组长度。

(3)返回的实体类,一定要遵循,跟头部一一对应的原则,例如:

导出的实体类,name要显示在excel的第一列,名字就得放在number这个字段的前面

1.pom.xml配置

<!-- poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.8</version>
		</dependency>

2.前端代码

2.1jsp页面代码

 <div class="float-r">
		<a class="red_btn_big addOrEditFoodCategory" type="add" onclick="list.exportReCharge()">+ 导出充值记录</a>
</div>

2.2 js代码

	/**
		 * 导出充值交易信息
		 */
		exportReCharge : function() {
			var methodOfPayment = $('#methodOfPayment option:selected').val();
			var paymentStatus = $('#paymentStatus option:selected').val();
			var serviceType = $('#serviceType option:selected').val();
			var startTime = $('#date0').val();
			var endTime = $('#date1').val();
			var keywords = $('#keywords').val();
			layer.confirm('确定导出充值数据吗?', {
				icon : 3,
				title : '提示'
			}, function(index) {
				window.location.href = "export?search=" + keywords
						+ "&channels=" + methodOfPayment + "&state="
						+ paymentStatus + "&serviceType=" + serviceType
						+ "&startTime=" + startTime + "&endTime=" + endTime;
				layer.close(index);
			});
		}

2.3后台controller代码

@RequestMapping("export")
	public void exportCharge(HttpServletRequest request, HttpServletResponse response, OrderExportVo exportVo) {
		Principal principal = getLoginAdminInfo();
		if (null == principal) {
			logger.info("--------------获取登录用户身份信息为空!");
			return;
		}
		HashMap<String, Object> conditionMap = new HashMap<>();

		try {
			boolean superAdmin = false; // 是否为超级管理员
			if(principal.getOperatorId() != null && StringUtils.isBlank(principal.getTreeIds())){// 非超级管理员
				superAdmin = true;
			} 

			PayOrderInfo payOrderInfo = new PayOrderInfo();
			payOrderInfo.setState(exportVo.getState());
			payOrderInfo.setServiceType(exportVo.getServiceType());
			payOrderInfo.setChannels(exportVo.getChannels());
			
			conditionMap.put("payOrderInfo", payOrderInfo);
			conditionMap.put("superAdmin", superAdmin);
			conditionMap.put("operatorId", principal.getOperatorId());
			conditionMap.put("adminId", principal.getTreeIds());
			conditionMap.put("search", exportVo.getSearch());
			conditionMap.put("startTime", exportVo.getStartTime());
			conditionMap.put("endTime", exportVo.getEndTime());
			Collection<OrderVo> dataset = payOrderInfoMapper.getPayOrderList(conditionMap);
			
			
			for (OrderVo orderVo : dataset) {
				String channelsName = "";
				if(orderVo.getChannels() != null){
					channelsName = ChannelsType.getType(orderVo.getChannels()).getDesc();
				}
				orderVo.setChannelsName(channelsName);
				orderVo.setStateName(OrderState.getType(orderVo.getState()).getDesc());
				String serviceTypeName="";
				if(orderVo.getServiceType() != null){
					serviceTypeName = ServiceType.getType(orderVo.getServiceType()).getDesc();
				}
				orderVo.setServiceTypeName(serviceTypeName);
			}
			
			ExportExcelUtils<OrderVo> excelUtils = new ExportExcelUtils<>();
			SXSSFWorkbook exportExcel = null;
			String[] headers = { "用户名","手机号","充值金额","退款金额", "余额", "充值平台", "订单号", "支付时间", "支付状态", "支付类型", "操作者" };
			int[] widths = {68,90,80,80,80,85,189,140,68,68,68};
			exportExcel = excelUtils.exportExcel("充值", headers,widths, dataset, response.getOutputStream());
			String currentDate = TimeUtils.getCurrentTimeStr("yyyyMMddHHmmss");
			excelUtils.createSXSSFWorkbook(exportExcel, response, "充值记录" + currentDate + ".xlsx");
		} catch (IOException e) {
			logger.info("导出充值交易记录失败!", e.getMessage());
		}
	}

2.4 excel辅助类

package com.parwa.web.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 * 导出Excel工具 
 * @author wude
 *
 * @param <T>
 */
public class ExportExcelUtils<T> {
	private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);

	public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
		exportExcel(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");
	}
	
	public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out) {
		exportExcel(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
	}
	
	/**
	 * 生成excel
	 * @param title     sheet名称
	 * @param headers   表头
	 * @param width     宽
	 * @param dataset   当前list对象
	 * @param out 
	 * @return
	 */
	public SXSSFWorkbook exportExcel(String title, String[] headers,int[] width,Collection<T> dataset, OutputStream out) {
		SXSSFWorkbook exportExcel = exportBigDataExcel(title, headers,width,dataset, out, "yyyy-MM-dd HH:mm:ss");
		return exportExcel;
	}
	
	/**
	 * 创建excel文档
	 * @param wb
	 * @param response
	 * @param fileName   文件名称
	 * @throws IOException
	 */
    public  void createSXSSFWorkbook(Workbook wb,HttpServletResponse response,String fileName) throws IOException {
        ServletOutputStream out = null;
        try {
            // 设置响应头
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("GBK"), "ISO8859_1"));
            out = response.getOutputStream();
            wb.write(out);
        } catch (Exception e) {
        	logger.info("导出excel报错!",e.getMessage());
        } finally {
            if (out != null) {
                    out.close();
            }
        }
    }
	
	
	/**
	 * 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * @param title 表格标题名
	 * @param title 列宽度
	 * @param headers 表格属性列名数组
	 * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public SXSSFWorkbook exportBigDataExcel(String title, String[] headers,int[] width,Collection<T> dataset, OutputStream out, String pattern) {
		/** 声明一个工作簿 **/
		SXSSFWorkbook workbook = new SXSSFWorkbook(100);   //100为内存缓存数据
		if (dataset == null || dataset.size() == 0) {
	            // 防止数据为空的情况下,excel无法打开
			workbook.createSheet();
	        return workbook;
	    }
		/** 声明一个表格 **/
		Sheet sheet = workbook.createSheet(title);
		/** 设置表格默认列宽度为15个字节 **/
		sheet.setDefaultColumnWidth((int) 16);
		sheet.autoSizeColumn(1);
		/** 生成样式,用于表格标题行 **/
		CellStyle style = workbook.createCellStyle();
		/** 设置样式 **/
		style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
	    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    style.setBorderBottom(CellStyle.BORDER_THIN);
	    style.setBorderLeft(CellStyle.BORDER_THIN);
	    style.setBorderRight(CellStyle.BORDER_THIN);
	    style.setBorderTop(CellStyle.BORDER_THIN);
	    style.setAlignment(CellStyle.ALIGN_CENTER);
	    /** 生成字体 **/
	    Font font = workbook.createFont();
	    font.setColor(IndexedColors.VIOLET.index);
	    font.setFontHeightInPoints((short) 13);
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    /** 将字体应用到样式中 **/
	    style.setFont(font);
	    
	    /** 样式二,用于表格内容行 **/
	    CellStyle style2 = workbook.createCellStyle();
	    style2.setFillForegroundColor(IndexedColors.WHITE.index);
	    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    style2.setBorderBottom(CellStyle.BORDER_THIN);
	    style2.setBorderLeft(CellStyle.BORDER_THIN);
	    style2.setBorderRight(CellStyle.BORDER_THIN);
	    style2.setBorderTop(CellStyle.BORDER_THIN);
	    style2.setAlignment(CellStyle.ALIGN_CENTER); //水平布局:居中;
	    style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      	/** 字体二 **/
	    Font font2 = workbook.createFont();
	    font.setFontHeightInPoints((short) 12);
	    font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
	    font2.setColor(IndexedColors.BLACK.index);
	    style2.setFont(font2);
	    /** 声明画图顶级管理器 **/
	    Drawing patriarch = sheet.createDrawingPatriarch();
	    
	    /** 设置表格标题行 **/
	    Row row = sheet.createRow(0);
	    for (int i = 0; i < headers.length; i++) {
	       Cell cell = row.createCell(i);
	       cell.setCellStyle(style);
	       XSSFRichTextString text = new XSSFRichTextString(headers[i]);
	       cell.setCellValue(text);
	       sheet.setColumnWidth(i, (int) (width[i] * 35.7));
	    }
	    
	    /** 以下是数据内容 **/
	    Iterator<T> it = dataset.iterator();
	    int index = 0;
	    while(it.hasNext() && it != null){
	    	index++;
	    	row = sheet.createRow(index);
	    	T t = (T) it.next();
	    	/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
	    	Field[] fields = t.getClass().getDeclaredFields();
	    	for(int i = 0;i < fields.length;i++){
	    		if(i == headers.length){
	    			break;
	    		}
	    		Cell cell = row.createCell(i);
	            
	            Field field = fields[i];
	            String fieldName = field.getName();
	            String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
	            
	            try {
	                Class tCls = t.getClass();
	                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
	                Object value = getMethod.invoke(t, new Object[] {});
	                /** 判断值的类型后进行强制类型转换 **/
	                String textValue = null;
	                if (value instanceof Boolean) {
	                	boolean bValue = (Boolean) value;
	                	textValue = "是";
	                	if (!bValue) {
	                		textValue ="否";
	                	}
	                } else if (value instanceof Date) {
	                	Date date = (Date) value;
	                	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                    textValue = sdf.format(date);
	                } else if (value instanceof byte[]) {
	                	/** 有图片时,设置行高为60px **/
	                	row.setHeightInPoints(60);
	                	/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
	                	sheet.setColumnWidth(i, (short) (35.7 * 80));
	                	byte[] bsValue = (byte[]) value;
	                	XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
	                 	anchor.setAnchorType(2);
	                	patriarch.createPicture(anchor, workbook.addPicture(bsValue, Workbook.PICTURE_TYPE_JPEG));
	                } else if(value instanceof Calendar){
	                	Calendar cale = Calendar.getInstance();  
	                	Date tasktime = cale.getTime();  
	                	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	                	textValue = df.format(tasktime);
	                } else{
	                	/** 其它数据类型都当作字符串简单处理 **/
	                	if(value != null){
	                		textValue = value.toString();
	                	}
	                }
	                /** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
	                if(textValue!=null){
	                	Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
	                	Matcher matcher = p.matcher(textValue);
	                	if(matcher.matches()){
	                		/** 是数字当作double处理 **/
	                		cell.setCellValue(Double.parseDouble(textValue));
	                   }else{
	                	   XSSFRichTextString richString = new XSSFRichTextString(textValue);
	                	   Font font3 = workbook.createFont();
	                	   font3.setColor(IndexedColors.BLACK.index);
	                	   richString.applyFont(font3);
	                	   cell.setCellValue(richString);
	                   }
	                }else{
	                	cell.setCellValue("");
	                }
	            } catch (Exception e) {
	            	e.printStackTrace();
	            	logger.info("导出excel报错!",e.getMessage());
	            } finally {
	                //清理资源
	            }
	            cell.setCellStyle(style2);
	    	}
	    }
		return workbook;
	}
	
	/**
	 * 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * @param title 表格标题名
	 * @param headers 表格属性列名数组
	 * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public HSSFWorkbook exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
		/** 声明一个工作簿 **/
		HSSFWorkbook workbook = new HSSFWorkbook();
		/** 声明一个表格 **/
		HSSFSheet sheet = workbook.createSheet(title);
		/** 设置表格默认列宽度为15个字节 **/
		sheet.setDefaultColumnWidth((int) 16);
		sheet.autoSizeColumn(1);
		/** 生成样式,用于表格标题行 **/
		HSSFCellStyle style = workbook.createCellStyle();
		/** 设置样式 **/
		style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
	    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    /** 生成字体 **/
	    HSSFFont font = workbook.createFont();
	    font.setColor(HSSFColor.VIOLET.index);
	    font.setFontHeightInPoints((short) 12);
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    /** 将字体应用到样式中 **/
	    style.setFont(font);
	    
	    /** 样式二,用于表格内容行 **/
	    HSSFCellStyle style2 = workbook.createCellStyle();
	    style2.setFillForegroundColor(HSSFColor.WHITE.index);
	    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      	/** 字体二 **/
	    HSSFFont font2 = workbook.createFont();
	    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	    font2.setColor(HSSFColor.BLACK.index);
	    style2.setFont(font2);
	    /** 声明画图顶级管理器 **/
	    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
	    
	    /** 设置表格标题行 **/
	    HSSFRow row = sheet.createRow(0);
	    for (int i = 0; i < headers.length; i++) {
	       HSSFCell cell = row.createCell(i);
	       cell.setCellStyle(style);
	       HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	       cell.setCellValue(text);
	    }
	    
	    /** 以下是数据内容 **/
	    Iterator<T> it = dataset.iterator();
	    int index = 0;
	    while(it.hasNext() && it != null){
	    	index++;
	    	row = sheet.createRow(index);
	    	T t = (T) it.next();
	    	/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
	    	Field[] fields = t.getClass().getDeclaredFields();
	    	for(int i = 0;i < fields.length;i++){
	    		HSSFCell cell = row.createCell(i);
	            
	            Field field = fields[i];
	            String fieldName = field.getName();
	            String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
	            
	            try {
	                Class tCls = t.getClass();
	                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
	                Object value = getMethod.invoke(t, new Object[] {});
	                /** 判断值的类型后进行强制类型转换 **/
	                String textValue = null;
	                if (value instanceof Boolean) {
	                	boolean bValue = (Boolean) value;
	                	textValue = "是";
	                	if (!bValue) {
	                		textValue ="否";
	                	}
	                } else if (value instanceof Date) {
	                	Date date = (Date) value;
	                	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                    textValue = sdf.format(date);
	                } else if (value instanceof byte[]) {
	                	/** 有图片时,设置行高为60px **/
	                	row.setHeightInPoints(60);
	                	/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
	                	sheet.setColumnWidth(i, (short) (35.7 * 80));
	                	byte[] bsValue = (byte[]) value;
	                	HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
	                 	anchor.setAnchorType(2);
	                	patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
	                } else if(value instanceof Calendar){
	                	Calendar cale = Calendar.getInstance();  
	                	Date tasktime = cale.getTime();  
	                	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	                	textValue = df.format(tasktime);
	                } else{
	                	/** 其它数据类型都当作字符串简单处理 **/
	                	if(value != null){
	                		textValue = value.toString();
	                	}
	                }
	                /** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
	                if(textValue!=null){
	                	Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
	                	Matcher matcher = p.matcher(textValue);
	                	if(matcher.matches()){
	                		/** 是数字当作double处理 **/
	                		cell.setCellValue(Double.parseDouble(textValue));
	                   }else{
	                	   HSSFRichTextString richString = new HSSFRichTextString(textValue);
	                	   HSSFFont font3 = workbook.createFont();
	                	   font3.setColor(HSSFColor.BLACK.index);
	                	   richString.applyFont(font3);
	                	   cell.setCellValue(richString);
	                   }
	                }else{
	                	cell.setCellValue("");
	                }
	            } catch (SecurityException e) {
	                e.printStackTrace();
	            } catch (NoSuchMethodException e) {
	                e.printStackTrace();
	            } catch (IllegalArgumentException e) {
	                e.printStackTrace();
	            } catch (IllegalAccessException e) {
	                e.printStackTrace();
	            } catch (InvocationTargetException e) {
	                e.printStackTrace();
	            } finally {
	                //清理资源
	            }
	    	}
	    }
		return workbook;
	}
	
	
	public static void main(String[] args) {
		Timestamp time = TimeUtils.getCurrentTime();
		if(time instanceof Date){
			System.out.println("时间类型");
		}
	}
}

可能存在问题 

实体类

 因为set get是遵守驼峰规则的,按道理生成的get方法应该是getHMaxTemp,但是这里却不是,就是因为h后面接的是大写字母,如果是thMaxTemp,生成的get方法,就是getThMaxTemp,所以编码的时候,保持一个良好的习惯很重要,但是,项目用了一段时间,如果只是这个大小写的问题,就大改动,这样改动量也太大勒,这里我分享另外一种方法。

找到这个工具类的String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);,如上图所示换成下面的图片效果

需要增加一个辅助反射类

ReflectUtil.java


package com.cloudtech.web.util;
 
import java.lang.reflect.Field;
import java.util.ArrayList;

import com.cloudtech.web.entity.RuleCode;
 
public class ReflectUtil {
 
    /**
     * 使用反射设置变量值
     *
     * @param target 被调用对象
     * @param fieldName 被调用对象的字段,一般是成员变量或静态变量,不可是常量!
     * @param value 值
     * @param <T> value类型,泛型
     */
    public static <T> void setValue(Object target,String fieldName,T value) {
        try {
            Class c = target.getClass();
            Field f = c.getDeclaredField(fieldName);
            f.setAccessible(true);
            f.set(target, value);
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 使用反射获取变量值
     *
     * @param target 被调用对象
     * @param fieldName 被调用对象的字段,一般是成员变量或静态变量,不可以是常量
     * @param <T> 返回类型,泛型
     * @return 值
     */
    public static <T> T getValue(Object target,String fieldName) {
        T value = null;
        try {
            Class c = target.getClass();
            Field f = c.getDeclaredField(fieldName);
            f.setAccessible(true);
            value = (T) f.get(target);
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return value;
    }
    
    public static void main(String[] args) {
		RuleCode code = new RuleCode();
		code.setId(1234);
		//Object value = ReflectUtil.getValue(code, "id");
		System.out.println(code.getId());
    /*	String s ="avgSpd gt hThreeMaxSpd";
    	String replace = s.replace("gt", "");
    	String[] split = replace.split(" ");
    	System.out.println();*/
	}
}

 3.对比

建议用后面这种,毕竟可以兼容实体类字段命名不规范的问题

4.优化后excel辅助类

注意:使用的时候,使用反射方式,去掉多余的代码,使用SXSSFWorkbook,性能更优

package com.cloudtech.web.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 导出Excel工具
 * 
 * @author wude
 *
 * @param <T>
 */
public class ExportExcelUtils<T> {
	private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);

	public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
		exportExcel(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss");
	}

	/**
	 * 生成excel
	 * 
	 * @param title
	 *            sheet名称
	 * @param headers
	 *            表头
	 * @param width
	 *            宽
	 * @param dataset
	 *            当前list对象
	 * @param out
	 * @return
	 */
	public SXSSFWorkbook exportExcel(String title, String[] headers, int[] width, Collection<T> dataset,
			OutputStream out) {
		SXSSFWorkbook exportExcel = exportBigDataExcel(title, headers, width, dataset, out, "yyyy-MM-dd HH:mm:ss");
		return exportExcel;
	}

	public SXSSFWorkbook exportMapExcel(String title, String[] headers, int[] width,
			LinkedHashMap<String, LinkedHashMap<String, Object>> dataset, OutputStream out) {
		SXSSFWorkbook exportExcel = exportBigMapDataExcel(title, headers, width, dataset, out, "yyyy-MM-dd HH:mm:ss");
		return exportExcel;
	}

	/**
	 * 创建excel文档
	 * 
	 * @param wb
	 * @param response
	 * @param fileName
	 *            文件名称
	 * @throws IOException
	 */
	public void createSXSSFWorkbook(Workbook wb, HttpServletResponse response, String fileName) throws IOException {
		ServletOutputStream out = null;
		try {
			// 设置响应头
			response.addHeader("Content-Disposition",
					"attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859_1"));
			out = response.getOutputStream();
			wb.write(out);
		} catch (Exception e) {
			logger.info("导出excel报错!", e.getMessage());
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}

	/**
	 * 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * 
	 * @param title
	 *            表格标题名
	 * @param title
	 *            列宽度
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
	 *            此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out
	 *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public SXSSFWorkbook exportBigDataExcel(String title, String[] headers, int[] width, Collection<T> dataset,
			OutputStream out, String pattern) {
		/** 声明一个工作簿 **/
		SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 100为内存缓存数据
		if (dataset == null || dataset.size() == 0) {
			// 防止数据为空的情况下,excel无法打开
			workbook.createSheet();
			return workbook;
		}
		/** 声明一个表格 **/
		Sheet sheet = workbook.createSheet(title);
		/** 设置表格默认列宽度为15个字节 **/
		sheet.setDefaultColumnWidth((int) 16);
		sheet.autoSizeColumn(1);
		/** 生成样式,用于表格标题行 **/
		CellStyle style = workbook.createCellStyle();
		/** 设置样式 **/
		style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		/** 生成字体 **/
		Font font = workbook.createFont();
		font.setColor(IndexedColors.VIOLET.index);
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		/** 将字体应用到样式中 **/
		style.setFont(font);

		/** 样式二,用于表格内容行 **/
		CellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(IndexedColors.WHITE.index);
		style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局:居中;
		style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		/** 字体二 **/
		Font font2 = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		font2.setColor(IndexedColors.BLACK.index);
		style2.setFont(font2);
		/** 声明画图顶级管理器 **/
		Drawing patriarch = sheet.createDrawingPatriarch();

		/** 设置表格标题行 **/
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text);
			sheet.setColumnWidth(i, (int) (width[i] * 35.7));
		}

		/** 以下是数据内容 **/
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext() && it != null) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
			Field[] fields = t.getClass().getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				if (i == headers.length) {
					break;
				}
				Cell cell = row.createCell(i);

				Field field = fields[i];
				String fieldName = field.getName();

				try {
					Object value = ReflectUtil.getValue(t, fieldName);
					/** 判断值的类型后进行强制类型转换 **/
					String textValue = null;
					if (value == null) {
						cell.setCellValue("");
					} else {
						if (value instanceof Boolean) {
							boolean bValue = (Boolean) value;
							textValue = "是";
							if (!bValue) {
								textValue = "否";
							}
							cell.setCellValue(textValue);
						} else if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat(pattern);
							textValue = sdf.format(date);
							cell.setCellValue(textValue);
						} else if (value instanceof Integer) {
							if (value != null) {
								int intValue = new BigDecimal(value.toString()).intValue();
								cell.setCellValue(intValue);
							}
						} else {
							/** 其它数据类型都当作字符串简单处理 **/
							if (value != null) {
								textValue = value.toString();
								cell.setCellValue(textValue);
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("导出excel报错!", e.getMessage());
				} finally {
					// 清理资源
				}
				cell.setCellStyle(style2);
			}
		}
		return workbook;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public SXSSFWorkbook exportBigMapDataExcel(String title, String[] headers, int[] width,
			LinkedHashMap<String, LinkedHashMap<String, Object>> dataset, OutputStream out, String pattern) {
		/** 声明一个工作簿 **/
		SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 100为内存缓存数据
		if (dataset == null || dataset.size() == 0) {
			// 防止数据为空的情况下,excel无法打开
			workbook.createSheet();
			return workbook;
		}
		/** 声明一个表格 **/
		Sheet sheet = workbook.createSheet(title);
		/** 设置表格默认列宽度为15个字节 **/
		sheet.setDefaultColumnWidth((int) 16);
		sheet.autoSizeColumn(1);
		/** 生成样式,用于表格标题行 **/
		CellStyle style = workbook.createCellStyle();
		/** 设置样式 **/
		style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		/** 生成字体 **/
		Font font = workbook.createFont();
		font.setColor(IndexedColors.VIOLET.index);
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		/** 将字体应用到样式中 **/
		style.setFont(font);

		/** 样式二,用于表格内容行 **/
		CellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(IndexedColors.WHITE.index);
		style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setAlignment(CellStyle.ALIGN_CENTER); // 水平布局:居中;
		style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		/** 字体二 **/
		Font font2 = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		font2.setColor(IndexedColors.BLACK.index);
		style2.setFont(font2);
		/** 声明画图顶级管理器 **/
		Drawing patriarch = sheet.createDrawingPatriarch();

		/** 设置表格标题行 **/
		Row row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text);
			sheet.setColumnWidth(i, (int) (width[i] * 35.7));
		}

		/** 以下是数据内容 **/
		int index = 0;
		for (Entry<String, LinkedHashMap<String, Object>> key : dataset.entrySet()) {
			index++;
			row = sheet.createRow(index);

			Cell cell = row.createCell(0);
			cell.setCellValue(key.getKey());
			cell.setCellStyle(style2);

			LinkedHashMap<String, Object> hourValues = key.getValue();
			int i = 1;
			for (Entry<String, Object> hourKey : hourValues.entrySet()) {
				cell = row.createCell(i);
				try {
					/** 判断值的类型后进行强制类型转换 **/
					String textValue = hourKey.getValue().toString();
					cell.setCellValue(textValue);
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("导出excel报错!", e.getMessage());
				} finally {
					// 清理资源
				}
				cell.setCellStyle(style2);
				i++;
			}
		}
		/*
		 * Iterator<T> it = (Iterator<T>) dataset.keySet().iterator(); int index
		 * = 0; while (it.hasNext() && it != null) { index++; row =
		 * sheet.createRow(index); T t = (T) it.next();
		 *//** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
		/*
		 * Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0; i
		 * < fields.length; i++) { if (i == headers.length) { break; } Cell cell
		 * = row.createCell(i);
		 * 
		 * Field field = fields[i]; String fieldName = field.getName(); //
		 * String getMethodName = "get" + fieldName.substring(0, //
		 * 1).toUpperCase() + fieldName.substring(1);
		 * 
		 * try { Class tCls = t.getClass(); Object value =
		 * ReflectUtil.getValue(t, fieldName); // Method getMethod =
		 * tCls.getMethod(getMethodName, new // Class[] {}); // Object value =
		 * getMethod.invoke(t, new Object[] {});
		 *//** 判断值的类型后进行强制类型转换 **/
		/*
		 * String textValue = null; if (value instanceof Boolean) { boolean
		 * bValue = (Boolean) value; textValue = "是"; if (!bValue) { textValue =
		 * "否"; } } else if (value instanceof Date) { Date date = (Date) value;
		 * SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue =
		 * sdf.format(date); } else if (value instanceof byte[]) {
		 *//** 有图片时,设置行高为60px **/
		/*
		 * row.setHeightInPoints(60);
		 *//** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
		/*
		 * sheet.setColumnWidth(i, (short) (35.7 * 80)); byte[] bsValue =
		 * (byte[]) value; XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0,
		 * 1023, 255, (short) 6, index, (short) 6, index);
		 * anchor.setAnchorType(2); patriarch.createPicture(anchor,
		 * workbook.addPicture(bsValue, Workbook.PICTURE_TYPE_JPEG)); } else if
		 * (value instanceof Calendar) { Calendar cale = Calendar.getInstance();
		 * Date tasktime = cale.getTime(); SimpleDateFormat df = new
		 * SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); textValue =
		 * df.format(tasktime); } else {
		 *//** 其它数据类型都当作字符串简单处理 **/
		/*
		 * if (value != null) { textValue = value.toString(); } }
		 *//** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
		/*
		 * if (textValue != null) { Pattern p =
		 * Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher =
		 * p.matcher(textValue); if (matcher.matches()) {
		 *//** 是数字当作double处理 **//*
								 * cell.setCellValue(Double.parseDouble(
								 * textValue)); } else { XSSFRichTextString
								 * richString = new
								 * XSSFRichTextString(textValue); Font font3 =
								 * workbook.createFont();
								 * font3.setColor(IndexedColors.BLACK.index);
								 * richString.applyFont(font3);
								 * cell.setCellValue(richString); } } else {
								 * cell.setCellValue(""); } } catch (Exception
								 * e) { e.printStackTrace();
								 * logger.info("导出excel报错!", e.getMessage()); }
								 * finally { // 清理资源 }
								 * cell.setCellStyle(style2); } }
								 */
		return workbook;
	}

	/**
	 * 利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
	 *            此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out
	 *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据,设定输出格式。默认为"yyy-MM-dd HH:mm:ss"
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public HSSFWorkbook exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out,
			String pattern) {
		/** 声明一个工作簿 **/
		HSSFWorkbook workbook = new HSSFWorkbook();
		/** 声明一个表格 **/
		HSSFSheet sheet = workbook.createSheet(title);
		/** 设置表格默认列宽度为15个字节 **/
		sheet.setDefaultColumnWidth((int) 16);
		sheet.autoSizeColumn(1);
		/** 生成样式,用于表格标题行 **/
		HSSFCellStyle style = workbook.createCellStyle();
		/** 设置样式 **/
		style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		/** 生成字体 **/
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		/** 将字体应用到样式中 **/
		style.setFont(font);

		/** 样式二,用于表格内容行 **/
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.WHITE.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		/** 字体二 **/
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font2.setColor(HSSFColor.BLACK.index);
		style2.setFont(font2);
		/** 声明画图顶级管理器 **/
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

		/** 设置表格标题行 **/
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		/** 以下是数据内容 **/
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext() && it != null) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			/** 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 **/
			Field[] fields = t.getClass().getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				HSSFCell cell = row.createCell(i);

				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

				try {
					Class tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					/** 判断值的类型后进行强制类型转换 **/
					String textValue = null;
					if (value instanceof Boolean) {
						boolean bValue = (Boolean) value;
						textValue = "是";
						if (!bValue) {
							textValue = "否";
						}
					} else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (value instanceof byte[]) {
						/** 有图片时,设置行高为60px **/
						row.setHeightInPoints(60);
						/** 设置图片所在列宽度为80px,注意这里单位的一个换算 **/
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						byte[] bsValue = (byte[]) value;
						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,
								index);
						anchor.setAnchorType(2);
						patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
					} else if (value instanceof Calendar) {
						Calendar cale = Calendar.getInstance();
						Date tasktime = cale.getTime();
						SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						textValue = df.format(tasktime);
					} else {
						/** 其它数据类型都当作字符串简单处理 **/
						if (value != null) {
							textValue = value.toString();
						}
					}
					/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							/** 是数字当作double处理 **/
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(textValue);
							HSSFFont font3 = workbook.createFont();
							font3.setColor(HSSFColor.BLACK.index);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					} else {
						cell.setCellValue("");
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} finally {
					// 清理资源
				}
			}
		}
		return workbook;
	}

	public static void main(String[] args) {
		Timestamp time = TimeUtils.getCurrentTime();
		if (time instanceof Date) {
			System.out.println("时间类型");
		}
	}

}

4.性能分析

列有69列,导出excel分析

优化前:

通过图片,我们可以发现sql的时间很长,当然跟我的列有关,69列

5w数据需要31.42s,而1w数据是6.5s,我们是不是可以做一些优化。列入拿1w作为切割,5w数据分为5次查询出来,再组装数据,是不是可以提升查询数据。所以,这里需要使用多线程。这样整个导出的时长就短了不少。

 

优化后:

通过数据分析,发现,性能方面有质的提高,而且缓存数大小,性能没有什么明显的提示,所以建议缓存数还是设置为100把 

 

缓存数:

设置为100后,超过100的部分会写入硬盘,而不是所有的都存放在内存中

性能优化部分: 

/** 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 **/
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							/** 是数字当作double处理 **/
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							XSSFRichTextString richString = new XSSFRichTextString(textValue);
							Font font3 = workbook.createFont();
							font3.setColor(IndexedColors.BLACK.index);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					} else {
						cell.setCellValue("");
					}

主要是正则方面的判断,消耗勒大量的数据,导致时间很长