导出Excel工具类

1、工具类源码

package com.mkaq.core.util;

import org.apache.poi.hssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * Created by z on 2018/12/12.
 */

public class ExcelExport {
    /**
     * 导出Excel
     *
     * @param fileName  Excel表格名称
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    数据内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static void getHSSFWorkbook(String fileName, String sheetName, String[] title, String[][] values, HSSFWorkbook wb, HttpServletResponse res) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建表头标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }

/**----------------------------------以上已经将所有数据放入 HSSFWorkbook 中--------------------------**/

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            res.setHeader("Content-Disposition", "attachment;filename="
                    + new String((fileName).getBytes(), "iso-8859-1"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        ServletOutputStream out = null;
        try {
            out = res.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;

            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

 

2、使用（准备好工具类中所需要的参数之后，直接调用工具类方法）

package org.platform4j.controller;

import com.mkaq.core.util.ExcelExport;
import org.platform4j.model.griddata.GridDataBase_Org;
import org.platform4j.model.griddata.GridDataEmp;
import org.platform4j.model.griddata.GridDataUserinfo;
import org.platform4j.model.kingcenum.UserType;
import org.platform4j.service.DepartmentService;
import org.platform4j.service.EmpService;
import org.platform4j.service.UserinfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

@RestController
public class ControllerExcelExport {

    //查询部门的服务
    @Autowired
    private DepartmentService departmentService;

    //导出部门的Excel
    @RequestMapping(value = "/exportDepartment")
    public void exportDepartment(HttpServletResponse res, String fOrgID) {
        //获取部门数据
        GridDataBase_Org chilidOrgAll = departmentService.getChilidOrgAll(fOrgID, null, false, 10000, 1);

        //Excel标题
        String[] title = {"部门名称", "上级部门", "负责人"};

        //excel文件名
        String fileName = "部门信息表" + System.currentTimeMillis() + ".xls";

        //sheet名
        String sheetName = "部门信息表";

        String[][] values = new String[chilidOrgAll.getRows().size()][3];
        for (int i = 0; i < chilidOrgAll.getRows().size(); i++) {
            if (null != chilidOrgAll.getRows().get(i).getFOrg_Name()) {
                values[i][0] = chilidOrgAll.getRows().get(i).getFOrg_Name().toString();//部门名称
            } else {
                values[i][0] = "";
            }

            if (null != chilidOrgAll.getRows().get(i).getfParent_Name()) {
                values[i][1] = chilidOrgAll.getRows().get(i).getfParent_Name().toString();//上级部门
            } else {
                values[i][1] = "";
            }

            if (null != chilidOrgAll.getRows().get(i).getFOrg_Leader_Name()) {
                values[i][2] = chilidOrgAll.getRows().get(i).getFOrg_Leader_Name().toString();//负责人
            } else {
                values[i][2] = "";
            }
        }

        //导出Excel表格方法
        ExcelExport.getHSSFWorkbook(fileName, sheetName, title, values, null, res);

    }
}