导包:
<properties>
<poi-ooxml.version>4.0.0</poi-ooxml.version>
</properties>
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi-ooxml.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi-ooxml.version}</version>
</dependency>
代码:
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* 基于POI的excel导出工具类
*
* @author Vick C on 2018/11/8.
*/
@Slf4j
public class ExportExcel {
/**
* 显示的导出表的标题
*/
private String fileName;
/**
* 导出表的列名
*/
private String[] rowName;
/**
* 单表数据集合
*/
private List<String[]> dataList = new ArrayList<>();
/**
* sheet名称
*/
private String[] sheetTitle;
/**
* sheet个数
*/
private int sheetNum;
/**
* 多表数据集合
*/
private List<List<String[]>> dataLists;
/**
* 单表构造方法,传入要导出的数据
*
* @param fileName The file name
* @param rowName the row name array
* @param dataList the data list
*/
public ExportExcel(String fileName, String[] rowName, List<String[]> dataList) {
this.dataList = dataList;
this.rowName = rowName;
this.fileName = fileName;
}
/**
* 多表构造方法
*
* @param fileName the file name
* @param sheetTitle the sheet title array
* @param sheetNum the sheet num
* @param rowName the row name array
* @param dataLists the data list
*/
public ExportExcel(String fileName, String[] sheetTitle, int sheetNum, String[] rowName, List<List<String[]>> dataLists) {
this.dataLists = dataLists;
this.rowName = rowName;
this.fileName = fileName;
this.sheetTitle = sheetTitle;
this.sheetNum = sheetNum;
}
/**
* 导出数据-单表
*
* @param request the http servlet request
* @param response the http servlet response
*/
public void export(HttpServletRequest request, HttpServletResponse response) {
try {
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(20);
// 获取列头样式对象
XSSFCellStyle columnTopStyle = getCellStyle(workbook, ExcelEnum.TOP);
// 单元格样式对象
XSSFCellStyle style = getCellStyle(workbook, ExcelEnum.COLUMN);
// 定义所需列数
int columnNum = rowName.length;
// 在索引2的位置创建行(最顶端的行开始的第二行)
XSSFRow rowRowName = sheet.createRow(0);
rowRowName.setHeightInPoints(30);
// 将列头设置到sheet的单元格中\
setHearderToSheet(columnTopStyle, columnNum, rowRowName);
// 将查询出的数据设置到sheet对应的单元格中
fillDataToSheet(sheet, style, this.dataList);
// 让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
sheet.autoSizeColumn((short) colNum);
}
exportFileStream(fileName, workbook, request, response);
} catch (Exception e) {
log.error("Excel单表导出失败,失败原因:", e);
}
}
/**
* 导出数据-多表
*
* @param request the http servlet request
* @param response the http servlet response
*/
public void exportSheets(HttpServletRequest request, HttpServletResponse response) {
try {
XSSFWorkbook workbook = new XSSFWorkbook();
for (int z = 0; z <= sheetNum; z++) {
XSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(z, sheetTitle[z]);
sheet.setDefaultColumnWidth(20);
// sheet样式定义
XSSFCellStyle columnTopStyle = getCellStyle(workbook, ExcelEnum.TOP);
// 单元格样式对象
XSSFCellStyle style = getCellStyle(workbook, ExcelEnum.COLUMN);
// 定义所需列数
int columnNum = rowName.length;
// 在索引2的位置创建行(最顶端的行开始的第二行)
XSSFRow rowRowName = sheet.createRow(0);
rowRowName.setHeightInPoints(30);
// 将列头设置到sheet的单元格中
setHearderToSheet(columnTopStyle, columnNum, rowRowName);
List<String[]> dataList = dataLists.get(z);
// 将查询出的数据设置到sheet对应的单元格中
fillDataToSheet(sheet, style, dataList);
}
exportFileStream(fileName, workbook, request, response);
} catch (Exception e) {
log.error("Excel多表导出失败,失败原因:", e);
}
}
/**
* 列头单元格样式
*
* @param workbook the work book
* @param type the resource type enum
* @return the cell style
*/
private XSSFCellStyle getCellStyle(XSSFWorkbook workbook, ExcelEnum type) {
// 设置字体
XSSFFont font = workbook.createFont();
if (ExcelEnum.TOP.equals(type)) {
// 设置字体大小
font.setFontHeightInPoints((short) 12);
// 字体加粗
font.setBold(true);
}
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
XSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 返回
return style;
}
/**
* 填充数据到sheet
*
* @param sheet the target sheet
* @param style the cell style
* @param dataList the data list
*/
private void fillDataToSheet(XSSFSheet sheet, XSSFCellStyle style, List<String[]> dataList) {
for (int i = 0; i < dataList.size(); i++) {
// 遍历每个对象
String[] obj = dataList.get(i);
// 创建所需的行数
XSSFRow row = sheet.createRow(i + 1);
row.setHeightInPoints(22);
for (int j = 0; j < obj.length; j++) {
// 设置单元格的数据类型
XSSFCell cell = null;
cell = row.createCell(j, CellType.STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
// 设置单元格的值
cell.setCellValue(obj[j]);
} else {
cell.setCellValue("");
}
// 设置单元格样式
cell.setCellStyle(style);
}
}
}
/**
* 设置表头
*
* @param columnTopStyle top style
* @param columnNum the column num
* @param rowRowName the row name
*/
private void setHearderToSheet(XSSFCellStyle columnTopStyle, int columnNum, XSSFRow rowRowName) {
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
XSSFCell cellRowName = rowRowName.createCell(n);
// 设置列头单元格的数据类型
cellRowName.setCellType(CellType.STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
}
/**
* 导出文件
*
* @param fileName the file name
* @param workbook the work book
* @param request the http servlet request
* @param response the http servlet response
*/
public static void exportFileStream(String fileName, Workbook workbook, HttpServletRequest request, HttpServletResponse response) {
ServletOutputStream out = null;
final String userAgent = request.getHeader("USER-AGENT");
try {
out = response.getOutputStream();
//清空输出流
response.reset();
String finalFileName = null;
if (StringUtils.contains(userAgent, "MSIE")) {
// IE浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {
// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
//其他浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
}
response.setHeader("content-disposition", "attachment;filename=" + finalFileName);
workbook.write(out);
} catch (IOException var12) {
throw new Exception("excel导出失败");
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException var11) {
var11.printStackTrace();
}
}
}
}
定义了一个枚举类
import java.util.EnumSet;
import java.util.Map;
import java.util.stream.Collectors;
/**
* <p>excel枚举类</p>
*
* @author Vick C on 2018/11/8
*/
public enum ExcelEnum {
/**
* 顶部
*/
TOP("top"),
/**
* 行
*/
ROW("row"),
/**
* 列
*/
COLUMN("column");
private String value;
private static Map<String, ExcelEnum> map;
ExcelEnum(String value) {
this.value = value;
}
static {
map = EnumSet.allOf(ExcelEnum.class).stream().collect(Collectors.toMap(ExcelEnum::getValue, value -> value));
}
public String getValue() {
return this.value;
}
public static ExcelEnum fromValue(String value) {
return map.get(value);
}
}