导出excel工具类
1、excel工具类代码如下
package com.spring.excel.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import javax.servlet.ServletOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.Collection;
import java.util.List;
import java.util.Map;
/**
* 导出excel工具类
* @author 福小林
*/
public class ExcelUtils {
/**
* 日志框架对象
*/
private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private ExcelUtils() {
}
/**
*
* @param titleMap excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
* @param out 输出流对象
* @param list 需要打印在excel上面的数据集合
* @param <T> 泛型标识
*/
public static <T> void exportExcel(Map<String,String> titleMap,ServletOutputStream out, List<T> list) {
try{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = getRows(titleMap, workbook);
setExcelData(titleMap, list, hssfSheet);
closeServletOutputStream(out, workbook);
}catch(Exception e){
logger.error("导出excel信息失败！"+e.getMessage(),e);
}
}
/**
* 关闭excel导出的各种流
* @param out 输出流对象
* @param workbook HSSFWorkbook对象
* @throws IOException 可能抛出的IO异常
*/
private static void closeServletOutputStream(ServletOutputStream out, HSSFWorkbook workbook) throws IOException {
workbook.write(out);
out.flush();
out.close();
}
/**
* 把数据放入excel表格中
* @param titleNames excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
* @param list 需要打印在excel上面的数据集合
* @param hssfSheet HSSFSheet对象
* @param <T> 泛型标识
* @throws Exception 异常
*/
private static <T> void setExcelData(Map<String,String> titleNames,List<T> list, HSSFSheet hssfSheet) throws Exception {
HSSFRow row;
//循环数据，打印到excel
for (int i = 0; i < list.size(); i++) {
row = hssfSheet.createRow(i+1);
//获取list里面的对象
T t = list.get(i);
//如果list集合的类型是Map<String,Object>
if (t instanceof Map){
Map map=(Map) t;
int k=0;
for(Map.Entry<String,String> entry :titleNames.entrySet()){
//标题英文字段
String key = entry.getKey();
Object obj = map.get(key);
//excel每个单元格插入的数据
String excelStr=getObjStr(obj);
row.createCell(k).setCellValue(excelStr);
k++;
}
//如果list集合的类型是实体类对象
}else {
int k=0;
for(Map.Entry<String,String> entry :titleNames.entrySet()){
String key = entry.getKey();
//获取对应实体类Class对象
Class < ?>cls = t.getClass();
//把t的数据赋值给obj
Object obj = cls.cast(t);
//获取标题对应的哪一个字段对象
Field field = cls.getDeclaredField(key);
//可以访问私有字段数据
field.setAccessible(true);
//获取对应的字段值
Object fieldValue = field.get(obj);
//excel每个单元格插入的数据
String excelStr = getObjStr(fieldValue);
row.createCell(k).setCellValue(excelStr);
k++;
}
}
}
}
/**
* 获取对象的字符串
* @param obj Object对象
* @return String
*/
private static String getObjStr(Object obj) {
if (StringUtils.isEmpty(obj)){
return "";
}else {
return obj.toString();
}
}
/**
*
* @param titleMap excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
* @param workbook HSSFWorkbook对象
* @return HSSFSheet已经把标题和样式设置和打印完成的对象
*/
private static HSSFSheet getRows(Map<String,String> titleMap, HSSFWorkbook workbook) {
Collection<String> valueCollection = titleMap.values();
final int size = valueCollection.size();
String[] titles = new String[size];
titleMap.values().toArray(titles);
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
HSSFRow row = hssfSheet.createRow(0);
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFCell hssfCell ;
for (int i = 0; i < titles.length; i++) {
hssfCell = row.createCell(i);
hssfCell.setCellValue(titles[i]);
hssfCell.setCellStyle(hssfCellStyle);
}
return hssfSheet;
}
}
2、调用导出excel示例
package com.spring.excel.controller;
import com.spring.excel.entity.User;
import com.spring.excel.utils.ExcelUtils;
import org.apache.commons.collections4.map.LinkedMap;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* 导出excel数据
* @author 福小林
*/
@RestController
public class ExcelController {
/**
*
* @param response 浏览器相应
* @param request 获取前端传入的值 测试暂时未用
* @return String
*/
@RequestMapping("/exportExcel")
@ResponseBody
public String exportExcel(HttpServletResponse response, HttpServletRequest request){
try{
response.setContentType("application/binary;charset=UTF-8");
ServletOutputStream out=response.getOutputStream();
//设置文件头：最后一个参数是设置下载文件名(这里我们叫：张三.pdf)
String excelName="导出的数据表格";
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(excelName+".xls", "UTF-8"));
//用作传入的参数不正确时，返回给浏览器的提示信息
if (StringUtils.isEmpty(excelName)){
response.getWriter().print("传入参数不全!");
return "传入参数不全";
}
//拼装测试数据
LinkedMap<String,String> titleMap=new LinkedMap<>();
titleMap.put("name","姓名");
titleMap.put("age","年龄");
titleMap.put("address","居住地址");
titleMap.put("email","邮箱");
titleMap.put("telephone","电话号码");
List<User> list = new ArrayList<>();
list.add(new User("张三","28","成都市","zhangsan@126.com","13312345678"));
list.add(new User("李四","36","中江县","lisi@163.com","18998765432"));
list.add(new User("王五","75","北京市","wangwu@126.com","15676543456"));
list.add(new User("赵六","47","上海市","zhaoliu@126.com","1387656789"));
ExcelUtils.exportExcel(titleMap, out,list);
return "success";
} catch(Exception e){
return "导出信息失败";
}
}
}
3、需要导入的java包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
4、下载表格
在浏览器上输入http://localhost:8080/exportExcel下载并打开
代码下载地址：https://github.com/hello-google/excel
参考文档 https://blog.csdn.net/yudiandemingzi/article/details/80204794
