写在前面：
导出excel，这个是很常见的，刚好最近项目也用到了，就简单记录一下，方便有需要的小伙伴，也算是一个备份。
直接贴代码了那就。
生成excel文档：
package com.justdoit.util;
import com.justdoit.domain.Dto;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author pavel
* @date 2018/12/11 0011
*/
public class ExportExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExportExcelUtil.class);
private static final String DEFDATEFORMAT = "yyyy-MM-dd HH:mm:ss";
/**
* @param title 表格标题名
* @param headersName 表格属性列名数组
* @param headersId 表格属性列名对应的字段
* @param dtoList 需要显示的数据集合
*/
public static HSSFWorkbook exportExcel(String title, String[] headersName,
String[] headersId, List dtoList) {
// 声明一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title);
sheet.setDefaultColumnWidth((short)15);
// 生成一个样式
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell;
//根据选择的字段生成表头
for (int i = 0; i < headersName.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headersName[i].toString());
cell.setCellStyle(style);
}
// 导出数据
Iterator labIt = dtoList.iterator();
int zdRow = 1;
while (labIt.hasNext()) {
int zdCell = 0;
row = sheet.createRow(zdRow);
zdRow++;
Object object = labIt.next();
Class<? extends Object> tCls = object.getClass();
for (int i = 0; i < headersId.length; i++) {
try {
Field fieid = tCls.getDeclaredField(headersId[i].toString());
// 字段名
String fieldName = fieid.getName();
// 字段类型
String filedclass = fieid.getType().toString();
// 获取方法名
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
Object val = getMethod.invoke(object, new Object[] {});
cell = row.createCell((short) zdCell);
if (val != null) {
if ("class java.util.Date".equals(filedclass)) {
cell.setCellValue(new SimpleDateFormat(DEFDATEFORMAT).format(val));
}else if ("class java.lang.Long".equals(filedclass)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(val.toString()));
}else if ("class java.lang.Double".equals(filedclass)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(val.toString()));
}else if ("class java.lang.Integer".equals(filedclass)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Integer.parseInt(val.toString()));
}else{
cell.setCellStyle(null);
cell.setCellValue(String.valueOf(val));
}
}else {
if ("class java.lang.Double".equals(filedclass)
|| "class java.lang.Long".equals(filedclass)
|| "class java.lang.Integer".equals(filedclass)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(0);
}else{
cell.setCellStyle(null);
cell.setCellValue("");
}
}
zdCell++;
} catch(Exception e) {
LOGGER.info("[ExportExcelUtil] exportExcel occur exception ", e);
}
}
}
return wb;
}
/**
* @param title
* @param headersName
* @param headersId
* @param dtoList
* @return
*/
public static <T> XSSFWorkbook exportExcel2007(String title, String[] headersName, String[] headersId, List<T> dtoList) {
XSSFWorkbook book = null;
try {
book = new XSSFWorkbook();
//设置居中格式
XSSFCellStyle cellStyle = book.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//创建sheet
XSSFSheet sheet = book.createSheet(title);
//单元格
XSSFCell cell;
//表格标题行
XSSFRow row = sheet.createRow(0);
//Cache
String[] getMethodNames = new String[50];
String[] types = new String[50];
for (int i = 0; i < headersName.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headersId[i]);
cell.setCellStyle(cellStyle);
if (!dtoList.isEmpty()) {
T t = dtoList.get(0);
Class<?> aClass = t.getClass();
Field declaredField = aClass.getDeclaredField(headersId[i]);
getMethodNames[i] = "get" + declaredField.getName().substring(0, 1).toUpperCase() + declaredField.getName().substring(1);
types[i] = declaredField.getType().toString();
}
}
int rowDateNum = 1;
//塞入数据行
for (int i = 0; i < dtoList.size(); i++) {
System.out.println(i);
row = sheet.createRow(rowDateNum);
rowDateNum++;
int cellColumn = 0;
T t = dtoList.get(i);
Class<?> aClass = t.getClass();
for (int i1 = 0; i1 < headersId.length; i1++) {
Method method = aClass.getMethod(getMethodNames[i1], new Class[]{});
Object cellValue = method.invoke(t, new Object[]{});
String type = types[i1];
cell = row.createCell(cellColumn);
if (cellValue != null) {
if ("class java.util.Date".equals(type)) {
cell.setCellValue(new SimpleDateFormat(DEFDATEFORMAT).format(cellValue));
} else if ("class java.lang.Long".equals(type)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(cellValue.toString()));
} else if ("class java.lang.Double".equals(type)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(cellValue.toString()));
} else if ("class java.lang.Integer".equals(type)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Integer.parseInt(cellValue.toString()));
} else {
cell.setCellStyle(null);
cell.setCellValue(String.valueOf(cellValue));
}
} else {
if ("class java.lang.Double".equals(type)
|| "class java.lang.Long".equals(type)
|| "class java.lang.Integer".equals(type)) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(0);
} else {
cell.setCellStyle(null);
cell.setCellValue("");
}
}
cellColumn++;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return book;
}
public static void main(String[] args) {
String[] col = {"用户ID","姓名"};
String[] colList = {"id","name"};
List<Dto> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Dto model = new Dto();
model.setId("100");
model.setName("测试");
list.add(model);
}
ExportExcelUtil.exportExcel2007("2018", col, colList, list);
}
}
导出excel文件到本地：
package com.justdoit.controller;
import com.justdoit.util.ExportExcelUtil;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author pavel
* @date 2018/12/11 0011
*/
public class BaseController {
private static Logger logger = LoggerFactory.getLogger(BaseController.class);
public static final String CONTENTTYPE_UTF_8 = "text/html;charset=utf-8";
/**
* Excel文件导出
* @param response
* @param feilname 导出文件名称
* @param headersName 表头名称
* @param headersId 表头对应的实体类字段名称
* @param tranlistlist 需要导出的数据列表
* @throws IOException
*/
public void responseDownload(HttpServletResponse response, String feilname,
String[] headersName, String[] headersId, List tranlistlist) throws IOException{
ByteArrayOutputStream os = new ByteArrayOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
int max_rows = 0x10000;
boolean use2007 = (tranlistlist.size() >= max_rows);
if(use2007) {
ExportExcelUtil.exportExcel2007(feilname, headersName, headersId, tranlistlist).write(os);
} else {
ExportExcelUtil.exportExcel(feilname, headersName, headersId, tranlistlist).write(os);
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// 下载
if(use2007) {
response.setHeader("Content-disposition",
"attachment;filename=" + new String((feilname + ".xlsx").getBytes("gbk"), "iso-8859-1"));
} else {
response.setHeader("Content-disposition",
"attachment;filename=" + new String((feilname + ".xls").getBytes("gbk"), "iso-8859-1"));
}
ServletOutputStream out = response.getOutputStream();
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 (IOException e) {
logger.info("[responseDownload] responseDownload occur exception",e);
}finally {
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) { }
}
}
}
在用到导出的controller中调用，该controller继承BaseController：
package com.justdoit.controller;
import com.justdoit.domain.Dto;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* @author pavel
* @date 2018/12/11 0011
*/
@Controller
@RequestMapping("/dto")
public class DtoController extends BaseController {
@RequestMapping("/export")
@ResponseBody
public void export(HttpServletResponse response) throws IOException {
List<Dto> dtoList = new ArrayList<>();
for (int i = 0; i <= 5; i++) {
Dto dto = new Dto();
dto.setId(i + "");
dto.setName(i + "号");
dtoList.add(dto);
}
String[] col = {"用户ID", "姓名"};
String[] dataId = {"id", "name"};
String fileName = "用户信息" + "_" + System.currentTimeMillis();
// 导出excel，调用父类BaseController的方法
super.responseDownload(response, fileName, col, dataId, dtoList);
}
}
相关pom文件：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>好，就是这样
547
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
