JAVA Poi 导出代码书写

废话少说

先上样式，这个是整体样式的代码，可以直接使用。

package cn.***.**.**.excelUtils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * excle样式工具类
 */
public class ExcelFormatUtil {

    // 标题第一行样式
    public static HSSFCellStyle titleStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = publicStyle(wb);
        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 15); // 设置字体大小
        style.setFont(headerFont); // 为标题样式设置字体样式
        return style;
    }

    // 第二行样式
    public static HSSFCellStyle style(HSSFWorkbook wb) {
        HSSFCellStyle style = publicStyle(wb);
        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        style.setFont(headerFont); // 为标题样式设置字体样式
        return style;
    }

    // 数据行样式

    /**
     * 因为office对excel的样式数量有要求，故而删除一些样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle style1(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
        style.setFont(headerFont1); // 为标题样式设置字体样式
        return style;
    }

    public static HSSFCellStyle publicStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);// 设置自动换行
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBottomBorderColor((short) 8);
        style.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        return style;
    }
}

下面是具体的导出代码

package cn.dxrj.lims.kangroo.excelUtils;

import cn.dxrj.boot.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.*;

import static cn.dxrj.lims.kangroo.excelUtils.ExcelFormatUtil.style;
import static cn.dxrj.lims.kangroo.excelUtils.ExcelFormatUtil.titleStyle;


/**
 * Excel工具类
 */
public class ExcelExportUtil {

    /**
     * 参数校验
     * @param title
     * @param list
     * @param lines
     * @param bean
     * @return
     */
    public boolean excelExportPreCheck(String title, List<String> list, List<String> lines, List<Map<String, Object>> bean){
        // 标题不能为空
        if (StringUtil.isBlank(title)){
            return false;
        }
        //导出数据不能为空
        if (null == bean){
            return false;
        }
        //传入值有误
        if (list == null && lines == null) {
            return false;
        }
        // 参数与值数不对应
        if (list.size() != lines.size() && lines.size() == 0 && list.size() == 0) {
            return false;
        }
        return true;
    }

    /**
     * 参数校验
     * @param title
     * @param list
     * @param lines
     * @param bean
     * @return
     */
    public boolean excelExportCheck(String title, List<String> list, List<String> lines, List bean){
        // 标题不能为空
        if (StringUtil.isBlank(title)){
            return false;
        }
        //导出数据不能为空
        if (null == bean){
            return false;
        }
        //传入值有误
        if (list == null && lines == null) {
            return false;
        }
        // 参数与值数不对应
        if (list.size() != lines.size() && lines.size() == 0 && list.size() == 0) {
            return false;
        }
        return true;
    }

    /**
     *  @param title 导出标题
     * @param list  导出列名
     * @param lines 导出字段
     * @param bean  导出值
     */
    public <T> void excelExportToMadel (String title, List<String> list, List<String> lines, List<Map<String, Object>> bean, String fileType, HttpServletResponse response) throws UnsupportedEncodingException {
        boolean b = excelExportPreCheck(title,list,lines,bean);
        if(b){
            response.setCharacterEncoding("utf-8");
            if(fileType.equals(".xls")){
                // 指定文件的保存类型。
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            }else{
                // 指定文件的保存类型。
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            }
            String fileTitle = URLDecoder.decode(title,"UTF-8");
            String fileNameEncode = fileTitle+fileType;
            response.setHeader("Content-Disposition", "attachment;filename=" + fileNameEncode);
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Expires", "0");
            response.setHeader("Filename", URLEncoder.encode(fileNameEncode,"utf-8"));
            //然后导出
            //拥有一个excel的文档对象 输出xls
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 创建一个工作表sheet 默认是表名是sheet0
            HSSFSheet sheet = workbook.createSheet(fileTitle);
            // 创建表的第一行
            HSSFRow row = sheet.createRow(0);
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            // 创建第0行 也就是标题
            // 设备标题的高度
            row.setHeightInPoints(30);
            //合并单元格
            if(list.size() > 1){
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, list.size() - 1));
            }
            // 创建单元格
            HSSFCell cell = null;
            cell = row.createCell(0);
            cell.setCellStyle(titleStyle(workbook));
            cell.setCellValue(fileTitle);

            //循环为第一行插入标题
            HSSFRow row1 = sheet.createRow(1);
            for (int i = 0; i < list.size() ; i++) {
                cell = row1.createCell(i);
             //   cell.setCellStyle(style(workbook));
                cell.setCellValue(list.get(i));
            }

            List<List<String>> stringList = new ArrayList<>();

            String val = null;
            for (Map<String, Object> data : bean) {
                List<String> dataList = new ArrayList<>();
                for (String st : lines) {
                    for (String key : data.keySet()) {
                        if (st.equals(key)){
                            Object value = data.get(key);
                            if(value != null){
                                val  =  value.toString();
                            }
                            dataList.add(val);
                            continue;
                        }
                    }
                }
                stringList.add(dataList);
            }
            int com = 2;
            try {
                for (List<String> list1 : stringList) {
                    HSSFRow row2 = sheet.createRow(com++);
                    for (int k = 0; k < list1.size(); k++){
                        cell = row2.createCell(k);
//                        cell.setCellStyle(style1(workbook));
                        cell.setCellValue(list1.get(k));
                    }
                }
                workbook.write(response.getOutputStream());
                workbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

    }


    /**
     *  @param title 导出标题
     * @param list  导出列名
     * @param lines 导出字段
     * @param bean  导出值
     */
    public <T> String excelExport (String title, List<String> list, List<String> lines, List<T> bean,String fileType, HttpServletResponse response) throws UnsupportedEncodingException {
        boolean b = excelExportCheck(title, list, lines, bean);
        if(b){
            response.setCharacterEncoding("utf-8");
            if(fileType.equals(".xls")){
                // 指定文件的保存类型。
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            }else{
                // 指定文件的保存类型。
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");

            }
            String fileTitle = URLDecoder.decode(title,"UTF-8");
            String fileNameEncode = fileTitle+fileType;
            response.setHeader("Content-Disposition", "attachment;filename=" + fileNameEncode);
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Expires", "0");
            response.setHeader("Filename", URLEncoder.encode(fileNameEncode,"utf-8"));
            //然后导出
            //拥有一个excel的文档对象 输出xls
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 创建一个工作表sheet 默认是表名是sheet0
            HSSFSheet sheet = workbook.createSheet(fileTitle);
            // 创建表的第一行
            HSSFRow row = sheet.createRow(0);
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            // 创建第0行 也就是标题
            // 设备标题的高度
            row.setHeightInPoints(30);
            if(list.size() > 1){
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, list.size() - 1));
            }
            // 创建单元格
            HSSFCell cell = null;
            cell = row.createCell(0);
            cell.setCellStyle(titleStyle(workbook));
            cell.setCellValue(fileTitle);

            //循环为第一行插入标题
            HSSFRow row1 = sheet.createRow(1);
            for (int i = 0; i < list.size() ; i++) {
                cell = row1.createCell(i);
                cell.setCellStyle(style(workbook));
                cell.setCellValue(list.get(i));
            }
            List<String> stringList = new ArrayList<>();
            Set<String> set = new HashSet<String>();
            for (T data:bean) {
                Field[] fields = data.getClass().getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    String fieldName = fields[i].getName();
                    for (String st : lines) {
                        if(st.equals(fieldName)) {
                            String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);
                            set.add(getMethodName);
                        }
                    }
                }
            }
            stringList.addAll(set);

            List<String> fieldList = new ArrayList<>();

            for(int i = 0;i < lines.size(); i++){
                for(int j = 0;j < stringList.size(); j++){
                    String string = stringList.get(j).substring(3);
                    String str  = string.substring(0,1).toLowerCase() + string.substring(1);
                    if(lines.get(i).equals(str)){
                        fieldList.add(stringList.get(j));
                    }
                }
            }
            int com = 2;
            try {
//                    HSSFRow row2 = sheet.createRow(2);
                Iterator<Object> iterator = (Iterator<Object>) bean.iterator();
                while (iterator.hasNext()) {
                    HSSFRow row2 = sheet.createRow(com++);
                    Object next = iterator.next();
                    for (int i = 0; i < fieldList.size(); i++){
                        Method getMethod = next.getClass().getMethod(fieldList.get(i));
                        cell = row2.createCell(i);
//                        cell.setCellStyle(style1(workbook));
                        if(null != getMethod.invoke(next)){
                            cell.setCellValue(getMethod.invoke(next).toString());
                        }else {
                            cell.setCellValue("");
                        }
                    }
                }
                workbook.write(response.getOutputStream());
                workbook.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return "";
    }
}

我写了两个方法，一个是针对常规模型的，使用了泛型T，另外一个针对Map结构。
才疏学浅，写的不好以后有时间再精简修改。