java导处excel工具类

1:包

        <!--生成excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

2.工具类

public class ExportExcelUtil<T> {

    //统一导处的时间格式
    String pattern="yyyy-MM-dd hh:mm:ss";

    /**
     *
     * @param sheetTitle      sheet名称
     * @param titleOne      表格第一行名称
     * @param headers    表格第一行对应的每行列名
     * @param data       数据集合用于生成excel
     * @param workbook   一个到处的完整对象 包含了许多的sheet
     * @throws Exception
     */
    public void exportExcel(String sheetTitle,String titleOne,String[] headers,List<T> data,HSSFWorkbook workbook) throws Exception {
        //一个HSSFWorkbook对象生成一个sheet表格
        HSSFSheet sheet=workbook.createSheet(sheetTitle);
        HSSFRow row=sheet.createRow(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length-1));
        HSSFCell til=row.createCell(0);
        til.setCellStyle(style1(workbook));
        til.setCellValue( new HSSFRichTextString(titleOne));

        //设置默认列宽
        sheet.setDefaultColumnWidth(24);
        CellStyle style1=style2(workbook);
        CellStyle style2= style3(workbook);
        row=sheet.createRow(1);
        //产生表格标题行
        for (int i=0;i<headers.length;i++){
            HSSFCell cell=row.createCell(i);
            cell.setCellStyle(style1);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //遍历数据,产生数据行
        Iterator<T> iterator = data.iterator();
        int index=1;
        while (iterator.hasNext()){
            index++;
            row=sheet.createRow(index);
            T t=(T)iterator.next();
            Field[] fields=t.getClass().getDeclaredFields();
            List<Field> fieldList=new ArrayList<>();
            for (Field field:fields){
                fieldList.add(field);
            }
            for (Field field : fieldList) {
                HSSFCell cell = row.createCell(fieldList.indexOf(field));
                cell.setCellStyle(style2);
                String fieldName = field.getName();
                String getMethodName =
                        "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Class tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                Object value = getMethod.invoke(t, new Object[] {});
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value == null) {
                    cell.setCellValue("");
                }
                if (value instanceof Integer) {
                    int intValue = (Integer) value;
                    cell.setCellValue(intValue);
                } else if (value instanceof Float) {
                    float fValue = (Float) value;
                    cell.setCellValue(fValue);
                } else if (value instanceof Double) {
                    double dValue = (Double) value;
                    cell.setCellValue(dValue);
                } else if (value instanceof Long) {
                    long longValue = (Long) value;
                    cell.setCellValue(longValue);
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                    cell.setCellValue(textValue);
                } else {
                    // 其它数据类型都当作字符串简单处理
                    textValue = value == null ? "" : value.toString();
                    cell.setCellValue(textValue);
                }
            }
        }
    }


    //第一行标题样式
    public static CellStyle style1(HSSFWorkbook workbook){
        CellStyle title1Style = workbook.createCellStyle();
        title1Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        title1Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        Font titleFont = workbook.createFont();// 字体
        //设置默认字体高
        titleFont.setFontHeightInPoints((short) 22);
        titleFont.setBoldweight((short) 700);
        title1Style.setFont(titleFont);
        return title1Style;
    }

    //第二行列名样式
    public static CellStyle style2(HSSFWorkbook workbook){
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置颜色
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 18);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        return headerStyle;
    }
    // 普通单元格样式
    public static CellStyle style3(HSSFWorkbook workbook){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 14);
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        return cellStyle;
    }
}

3.controller调用

    @RequestMapping("/export")
    public String exportExcelByTime(HttpServletResponse response,OutputStream out) {
        String title ="department_info";
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + title + ".xls");
        response.setContentType("application/x-xls");
        //查询到list数据(这里就随便写点数据吧)
        Map<String,Object> map=new HashMap<>();
        List<TestDept> list=testDeptService.selectAll(map);
        try {
            // 输出Excel文件
            HSSFWorkbook workbook=new HSSFWorkbook();
            ExportExcelUtil exportExcelUtil=new ExportExcelUtil();
            String[] heraders={"部门id","父类部门id","祖级列表","部门名称","显示顺序","负责人","电话","邮箱","状态","是否删除","创建者","创建时间","修改者","修改时间"};
            exportExcelUtil.exportExcel("部门数据","部门数据明细",heraders, list,workbook);
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "导出成功";
    }

4.效果