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);
}
}