java excel导出工具类(大量数据)

java excel导出工具类(大量数据)

主要代码：

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 导入到EXCEL
 * 类名称：ObjectExcelView.java
 *
 * @author LYY
 * @version 1.0
 */
public class ObjectExcelView extends AbstractExcelView {

    @Override
    protected void buildExcelDocument(Map<String, Object> model,
                                      HSSFWorkbook workbook, HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {
        // TODO Auto-generated method stub
        Date date = new Date();
        String filename = DateUtil.dateToStr(date, "yyyyMMddHHmmss");
        HSSFSheet sheet;
        HSSFCell cell;
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        List<String> titles = (List<String>) model.get("titles");
        int len = titles.size();
        HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont headerFont = workbook.createFont();    //标题字体
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        List<Map<String, Object>> varList = (List<Map<String, Object>>) model.get("varList");
        int varCount = varList.size();
        Integer lineIndex = (varCount + 9999) / 10000;
        List<HSSFSheet> sheetList = new ArrayList<>();
        for (Integer i = 0; i < lineIndex; i++) {
            sheet = workbook.createSheet((i + 1) + "页");
            sheetTtile(titles, sheet, headerStyle);
            sheetList.add(sheet);
        }

        HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        int num = -1;
        HSSFSheet hssfSheet = null;
        /**
         * 设置excel数据写入开始行数
         */
        int row = 1;
        try {
            for (int i = 0; i < varCount; i++) {
                Map<String, Object> vpd = varList.get(i);
                /**
                 * 当数据到达10000行时写入新sheet页,并将写入数据开始行数设置为1
                 */
                if (i % 10000 == 0) {
                    hssfSheet = sheetList.get(++num);
                    row = 1;
                }
                for (int j = 0; j < len; j++) {
                    String varstr = vpd.get("var" + (j + 1)) != null ? vpd.get("var" + (j + 1)) + "" : "";
                    cell = getCell(hssfSheet, row, j);
                    cell.setCellStyle(contentStyle);
                    setText(cell, varstr);
                }
                /**
                 * 每写入一行数据,写入行加1
                 */
                row++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void sheetTtile(List<String> titles, HSSFSheet sheet, HSSFCellStyle headerStyle) {
        short width = 20, height = 25 * 20;
        HSSFCell cell;
        sheet.setDefaultColumnWidth(width);
        int len = titles.size();
        for (int i = 0; i < len; i++) { //设置标题
            String title = titles.get(i);
            cell = getCell(sheet, 0, i);
            cell.setCellStyle(headerStyle);
            setText(cell, title);
        }
        sheet.getRow(0).setHeight(height);
    }
}

调用方式：

@RequestMapping("/xxx")
    public ModelAndView getLotterySchemeListInfoExcel(LotterySchemeDTO dto) {
        ModelAndView mv = null;
        try {
            Map<String, Object> dataMap = new HashMap<>(16);
            if (null == dto.getIds() || !(dto.getIds().length > 0)) {
                dto.setIds(null);
            }
            if (dto.getOrder() == null || StringUtils.isEmpty(dto.getOrder().trim())) {
                dto.setOrder("createTime desc");
            } else {
                if (dto.getOrder().trim().length() > 15 || (!dto.getOrder().toLowerCase().contains("desc")) && !dto.getOrder().toLowerCase().contains("asc")) {
                    log.error("!");
                    return null;
                }
            }
            List<LotterySchemeVO> list = financeMapper.getLotterySchemeListInfo(dto);
            for (LotterySchemeVO statement : list) {
                if (statement.getLotteryType() != null) {
                    statement.setLotteryTypeName(statement.getLotteryType().getName());
                }
                if (statement.getSchemePrintState() != null) {
                    statement.setSchemePrintStateName(statement.getSchemePrintState().getStateName());
                }
            }
            Map<String, Object> map = financeMapper.getLotterySchemeCount(dto);
            if (map == null) {
                map = new HashMap<>(3);
                map.put("SuccessMoney", 0);
                map.put("FailedMoney", 0);
            }
            //标题
            List<String> titles = new ArrayList<>();
            titles.add("xx");
            titles.add("xxx");
            dataMap.put("titles", titles);
            //内容
            List<Map<String, Object>> varList = new ArrayList<>();
            for (LotterySchemeVO vo : list) {
                Map<String, Object> vpd = new HashMap<>(9);
                vpd.put("var1", vo.getId());
                vpd.put("var2", vo.getUserName());
                varList.add(vpd);
            }
            Map<String, Object> vpd = new HashMap<>(0);
            Map<String, Object> vpd1 = new HashMap<>(3);
            vpd1.put("var1", "汇总:");
            vpd1.put("var2", "xx:");
            vpd1.put("var3", map.get("SuccessMoney"));
            Map<String, Object> vpd2 = new HashMap<>(3);
            vpd2.put("var1", "");
            vpd2.put("var2", "xx:");
            vpd2.put("var3", map.get("FailedMoney"));
            varList.add(vpd);
            varList.add(vpd1);
            varList.add(vpd2);
            varList.add(vpd3);
            dataMap.put("varList", varList);
            ObjectExcelView erv = new ObjectExcelView();
            mv = new ModelAndView(erv, dataMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mv;
    }