一、环境搭建
IDE：idea 2022.2
构建工具：maven 3.6.3
2、准备工作
①引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
二、创建Excel工具类
1、导出工具类代码分析
exportExcel方法为导出的核心方法，在内容的设置时先创建行，在创建每一列。cell.setCellValue 是为每一个单元格赋值。
/**
* 用户信息导出类
*
* @param datas 内容
* @param counts 统计需要合并的列
* @param response 响应
* @param fileName 文件名
* @param columnList 第一行的标题名
* @param Arr 第二行的标题名
*/
public static void exportExcel (HttpServletResponse response , String fileName, List<String>columnList, List<String>Arr, List<Map> counts, List<Map> datas){
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
os = response.getOutputStream();
SXSSFWorkbook workbook = new SXSSFWorkbook();
//获取该工作区的第一个sheet
Sheet sheet1 = workbook.createSheet("sheet1");
//调用合并方法
rowColMerge(sheet1,Arr,columnList,counts,0,1);
int excelRow = 0;
//创建标题行1
Row titleRow = sheet1.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
cell.setCellStyle( setStyle(workbook));
}
//创建标题行2
Row titleRow2 = sheet1.createRow(excelRow++);
for(int i = 0;i<Arr.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow2.createCell(i);
cell.setCellValue(Arr.get(i));
cell.setCellStyle(setStyle(workbook));
}
//设置行高
titleRow.setHeight((short) (50*20));
//设置列宽
setColSize(sheet1,columnList);
//设置内容行
if (datas!=null && datas.size()>0){
for (int i = 0; i < datas.size(); i++) {
Row dataRow = sheet1.createRow(excelRow++);
for (int j = 0; j <datas.get(i).size() ; j++) {
Cell cell = dataRow.createCell(j);
if (String.valueOf(datas.get(i).get("data"+j)).equals("null")){
cell.setCellValue(" ");
}else {
cell.setCellValue(String.valueOf(datas.get(i).get("data"+j)));
}
}
}
}
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
//关闭输出流
if (os != null){
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}setStyle为头部样式，可以在次更改头部样式，该方法是为了代码不冗余拆分出来的，也可以写进核心方法里面（不推荐）。 同理，你也可以在命名一个内容样式的方法设置内容的样式。
//头部样式
private static CellStyle setStyle(SXSSFWorkbook workbook){
//标题
CellStyle titleStyle = workbook.createCellStyle();
//居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setWrapText(true);
return titleStyle;
}
setColSize设置列宽,这里我简单的设置了一下列宽，根据你的具体需求，可以自行修改一个处理逻辑。
//根据标题长度设置宽度
private static void setColSize(Sheet sheet,List<String>oneTitles){
for (int i = 0; i < oneTitles.size(); i++) {
sheet.setColumnWidth(i,(int)((50 + 0.72) * (oneTitles.get(i).length()*10+30)));
}
}rowColMerge该方法动态合并行列，根据具体传入的参数，动态合并行和列。第一个for循环，合并行。第二个for循环，合并列。
/**
*
* @param sheet
* @param list 第二行
* @param arrList 第一行
* @param counts 统计合并的列数
* @param start 开始行
* @param end 结束行
*/
private static void rowColMerge(Sheet sheet, List<String>list, List<String>arrList , List<Map> counts, int start, int end){
for (int i = 0; i < list.size(); i++) {
if (list.get(i).equals(" ")){
sheet.addMergedRegion(new CellRangeAddress(start,end,i,i));
}
}
for (Map count : counts) {
for (int i = 0; i < arrList.size(); i++) {
if (count.get("groupName").equals(arrList.get(i))){
int endCol = Integer.parseInt(String.valueOf(count.get("count"))) + i -1;
sheet.addMergedRegion(new CellRangeAddress(start,start,i,endCol));
}
}
}
}setResponseHeader设置下载的响应头，解决下载的乱码的 问题。
/*
设置浏览器下载响应头
*/
private static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//添加响应头的跨域信息--开始
response.addHeader("Access-Control-Allow-Origin", "*");
response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
response.addHeader("Access-Control-Allow-Headers", "Content-Type");
//添加响应头的跨域信息--结束 URLEncoder.encode(fileName + ".xlsx", "UTF-8"))
response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(fileName+".xlsx")));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//
} catch (Exception ex) {
ex.printStackTrace();
}
}2、完整工具类
public class ExcelUtil {
/**
* 用户信息导出类
*
* @param datas 内容
* @param counts 统计需要合并的列
* @param response 响应
* @param fileName 文件名
* @param columnList 第一行的标题名
* @param Arr 第二行的标题名
*/
public static void exportExcel (HttpServletResponse response , String fileName, List<String>columnList, List<String>Arr, List<Map> counts, List<Map> datas){
OutputStream os = null;
//设置响应头
setResponseHeader(response,fileName);
try {
os = response.getOutputStream();
SXSSFWorkbook workbook = new SXSSFWorkbook();
//获取该工作区的第一个sheet
Sheet sheet1 = workbook.createSheet("sheet1");
//调用合并方法
rowColMerge(sheet1,Arr,columnList,counts,0,1);
int excelRow = 0;
//创建标题行1
Row titleRow = sheet1.createRow(excelRow++);
for(int i = 0;i<columnList.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
cell.setCellStyle( setStyle(workbook));
}
//创建标题行2
Row titleRow2 = sheet1.createRow(excelRow++);
for(int i = 0;i<Arr.size();i++){
//创建该行下的每一列，并写入标题数据
Cell cell = titleRow2.createCell(i);
cell.setCellValue(Arr.get(i));
cell.setCellStyle(setStyle(workbook));
}
//设置行高
titleRow.setHeight((short) (50*20));
//设置列宽
setColSize(sheet1,columnList);
//设置内容行
if (datas!=null && datas.size()>0){
for (int i = 0; i < datas.size(); i++) {
Row dataRow = sheet1.createRow(excelRow++);
for (int j = 0; j <datas.get(i).size() ; j++) {
Cell cell = dataRow.createCell(j);
if (String.valueOf(datas.get(i).get("data"+j)).equals("null")){
cell.setCellValue(" ");
}else {
cell.setCellValue(String.valueOf(datas.get(i).get("data"+j)));
}
}
}
}
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
//关闭输出流
if (os != null){
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
//头部样式
private static CellStyle setStyle(SXSSFWorkbook workbook){
//标题
CellStyle titleStyle = workbook.createCellStyle();
//居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setWrapText(true);
return titleStyle;
}
//根据标题长度设置宽度
private static void setColSize(Sheet sheet,List<String>oneTitles){
for (int i = 0; i < oneTitles.size(); i++) {
sheet.setColumnWidth(i,(int)((50 + 0.72) * (oneTitles.get(i).length()*10+30)));
}
}
//动态合并行列
/**
*
* @param sheet
* @param list 第二行
* @param arrList 第一行
* @param counts 统计合并的列数
* @param start 开始行
* @param end 结束行
*/
private static void rowColMerge(Sheet sheet, List<String>list, List<String>arrList , List<Map> counts, int start, int end){
for (int i = 0; i < list.size(); i++) {
if (list.get(i).equals(" ")){
sheet.addMergedRegion(new CellRangeAddress(start,end,i,i));
}
}
for (Map count : counts) {
for (int i = 0; i < arrList.size(); i++) {
if (count.get("groupName").equals(arrList.get(i))){
int endCol = Integer.parseInt(String.valueOf(count.get("count"))) + i -1;
sheet.addMergedRegion(new CellRangeAddress(start,start,i,endCol));
}
}
}
}
/*
设置浏览器下载响应头
*/
private static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//添加响应头的跨域信息--开始
response.addHeader("Access-Control-Allow-Origin", "*");
response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
response.addHeader("Access-Control-Allow-Headers", "Content-Type");
//添加响应头的跨域信息--结束 URLEncoder.encode(fileName + ".xlsx", "UTF-8"))
response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(fileName+".xlsx")));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
三、实例测试
1、测试代码
这里没用数据库，手工生成的一些数据，list 和list2 为头部标题，data为内容，counts为统计的合并的列数。
@GetMapping("/excel")
public void testExport(HttpServletResponse response){
String [] list = {"日期","来源","投诉订单","投诉号码","关联重复订单","原始申诉时间","投诉内容","投诉类型"," ","投诉类型备注","处理意见","处理班组信息"," "," ",};
String [] list2 = {" "," "," "," "," "," "," ","类型1","类型2"," "," ","处理人1","处理人2","上级领导"};
List<Map> data = new ArrayList<>();
for (int i = 0; i < 2; i++) {
Map map2 = new HashMap();
for (int j = 0; j < 14; j++) {
map2.put("data"+j,"8月1日"+j);
}
data.add(map2);
}
data.get(1).put("data9",null);
List<Map> counts = new ArrayList<>();
Map map = new HashMap();
map.put("groupName","投诉类型");
map.put("count",2);
counts.add(map);
Map map1 = new HashMap();
map1.put("groupName","处理班组信息");
map1.put("count",3);
counts.add(map1);
System.out.println(Arrays.toString(list));
System.out.println(Arrays.toString(list2));
System.out.println(counts);
System.out.println(Arrays.toString(list));
System.out.println(Arrays.toString(list2));
List<String> listTest = new ArrayList<>(Arrays.asList(list));
List<String> list2Test = new ArrayList<>(Arrays.asList(list2));
ExcelUtil.exportExcel(response,"导出测试",listTest,list2Test,counts,data);
}2、实例展示
4642
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
