1、需要引入的依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
2、代码实现
package com.my.projects.utils.office;
import com.alibaba.fastjson.JSON;
import com.my.projects.utils.constant.ErrorCode;
import com.my.projects.utils.use.Response;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
/**
* @author: fengchangxin
* @created: 2018/9/3 21:28
* @decription:生成Excel表格
*/
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
/**
* @param sheetName 表格名
* @param fileName 文件名
* @param headers 表格第一行头名称
* @param content 表格内容
* @param c 内容对应的class
* @return
*/
public static void createExcel(String sheetName, String fileName, List<String> headers,
List<?> content, Class<?> c, HttpServletResponse response) {
OutputStream outputStream = null;
try {
response.setHeader("Content-type", MediaType.APPLICATION_JSON_UTF8_VALUE);
response.setCharacterEncoding("UTF-8");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
if (!CollectionUtils.isEmpty(headers)) {
HSSFCell cell;
for (int i = 0; i < headers.size(); i++) {
cell = row.createCell((short) i);
cell.setCellValue(headers.get(i));
}
}
if (CollectionUtils.isEmpty(content)) {
response.getWriter().println(JSON.toJSONString(Response.newFail(ErrorCode.ERROR_101.getCode(), ErrorCode.ERROR_101.getMsg())));
return;
}
if (StringUtils.isBlank(fileName)) {
fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMddHHmmss");
}
//获取class属性
Field[] fields = c.getDeclaredFields();
int contentSize = content.size();
int fieldsLength = fields.length;
for (int j = 0; j < contentSize; j++) {
Object o = content.get(j);
//创建行
row = sheet.createRow(j + 1);
for (int m = 0; m < fieldsLength; m++) {
Field field = fields[m];
field.setAccessible(true);
try {
//创建单元格
row.createCell((short) m).setCellValue(String.valueOf(field.get(o)));
} catch (Exception e) {
LOGGER.error("{}", e.getMessage());
response.getWriter().println(JSON.toJSONString(Response.newFail(ErrorCode.ERROR_102.getCode(), ErrorCode.ERROR_102.getMsg())));
return;
}
}
}
response.setHeader("Content-disposition", new String("attachment; filename=".concat(fileName).concat(".xls").getBytes("gbk"), "ISO-8859-1"));
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (IOException e) {
LOGGER.error("{}", e.getMessage());
return;
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (IOException ee) {
LOGGER.error("fail to close IO,{}", ee.getMessage());
}
}
}
}
2.1 对第一种方法修改
/**
* @param sheetName 表格名
* @param fileName 文件名
* @param headers 表格第一行头名称
* @param content 表格内容
* @return
*/
public static void createExcel(String sheetName, String fileName, String[] headers,
String[][] content, HttpServletResponse response) {
OutputStream outputStream = null;
try {
response.setHeader("Content-type", MediaType.APPLICATION_JSON_UTF8_VALUE);
response.setCharacterEncoding("UTF-8");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
//生成表格导航栏
if (!ArrayUtils.isEmpty(headers)) {
HSSFCell cell;
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headers[i]);
}
}
if (ArrayUtils.isEmpty(content)) {
return;
}
if (StringUtils.isBlank(fileName)) {
fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMddHHmmss");
}
int valueLength = content.length;
for (int i = 0; i < valueLength; i++) {
//创建行
row = sheet.createRow(i + 1);
int length = content[i].length;
for (int j = 0; j < length; j++) {
//创建单元格
row.createCell((short) j).setCellValue(content[i][j]);
}
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.
concat(".xls").getBytes("UTF-8"), "ISO-8859-1"));
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (IOException e) {
LOGGER.error("{}", e.getMessage());
return;
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (IOException ee) {
LOGGER.error("fail to close IO,{}", ee.getMessage());
}
}
}
注意：接口返回要用void类型。
2.2使用阿里巴巴的easyExcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
简单例子：
@Data
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class DataDTO {
@ExcelProperty(value = "名称", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 2)
@ColumnWidth(50)
private Integer age;
@ExcelProperty(value = "时间", index = 1)
private Date time;
}
public class ExcelUtils {
public static void main(String[] args) {
String fileName = "E://test.xlsx";
EasyExcel.write(fileName, DataDTO.class).sheet("模板").doWrite(getData());
}
private static List<DataDTO> getData() {
List<DataDTO> list = new ArrayList<>();
for (int i = 0; i < 100; i++) {
DataDTO dataDTO = new DataDTO();
dataDTO.setAge(i);
dataDTO.setName("name" + i);
dataDTO.setTime(new Date());
list.add(dataDTO);
}
return list;
}
}
easyExcel对poi做了很多的优化，更简单使用，更推荐这种方式，不用像前面写那么复杂，对Date和图片格式都支持，更多功能去官网了解，有详细的demo，不过不支持LocalDateTime时间类型，因为easyExcel是基于jdk1.7的。在对应的dto使用注解就可读取字段信息。官方文档：https://github.com/alibaba/easyexcel
