背景
新公司会员分销报表管理系统有导出excel表的场景，通过查找，项目中并没有excel表相关工具类，故手写了工具类给大家分享一下，以备不时之需。
方式一（POI方式）
1.该工具类包含导出和导入解析功能，其中导入解析功能需读者根据自身业务情况做进一步封装。
2.工具类中有笔者自己的CommonException异常类，需读者根据自身业务做相应调整和替换。
import com.zzx.commons.exceptions.CommonException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;
public class ExcelUtil {
private static Logger logger = LoggerFactory.getLogger("info");
/**
* 下载execel表模板
*
* @param response HttpServletResponse
* @param fileName 导出文件名
* @param list 目标表单
* @param title 表头数组
* @param properties 导出属性（与表头对应）
*/
public static void download(HttpServletResponse response, String fileName, List list, String[] title, String[] properties) {
HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
HSSFSheet sheet = workbook.createSheet("sheet1");//创建工作表(Sheet)
//设置第一列宽（3766）
HSSFRow row = sheet.createRow(0);// 创建行,从0开始
for (int i = 0; i < title.length; i++) {
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("@"));
sheet.setDefaultColumnStyle(i,style);
HSSFCell cells = row.createCell(i);// 设置单元格内容,重载
styleOne(workbook, cells).setCellValue(title[i]);
}
for(int i = 0; i < list.size() ;i++){
HSSFRow row_one = sheet.createRow(i+1);
//反射得到所有属性
Class cls = list.get(i).getClass();
Field[] fields = cls.getDeclaredFields();
styleTwo(workbook, row_one);
for(int k = 0 ; k < properties.length ; k++) {
for (Field field : fields) {
//得到属性
//打开私有访问
field.setAccessible(true);
//获取属性
Object value = null;
try {
value = field.get(list.get(i));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
if (properties[k].equals(field.getName())) {
row_one.createCell(k).setCellValue(String.valueOf(value));
}
}
}
}
OutputStream outputStream = null;
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);//保存Excel文件
if (outputStream != null) {
outputStream.close();//关闭文件流
}
} catch (Exception e) {
logger.info("execel流输出时错误,错误详情：{}", e.getMessage());
throw new CommonException("execel流输出时错误");
}
System.out.println("OK!");
}
/**
* 上传并解析execel表
*
* @param file
* @return
* @throws IOException
* @throws FileUploadException
*/
public static String upLoadExecel(MultipartFile file) {
byte[] b = new byte[0];
try {
b = file.getBytes();
} catch (IOException e) {
logger.info("上传文件出错,错误详情：{}", e.getMessage());
throw new CommonException("上传文件出错");
}
InputStream is = new ByteArrayInputStream(b);
HSSFWorkbook hssfWorkbook = null;
try {
hssfWorkbook = new HSSFWorkbook(is);
} catch (IOException e) {
logger.info("该文件为非execel文件,错误详情：{}", e.getMessage());
throw new CommonException("该文件为非execel文件");
}
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环列Cell
for (int j = 0; j <= hssfRow.getLastCellNum(); j++) {
HSSFCell cell = hssfRow.getCell(j);
if (cell == null) {
continue;
}
System.out.println(getCellDate(cell));
}
}
}
return "数据上传成功！";
}
/**
* 遍历获取相应类型值静态工具类
*
* @param cell
* @return
*/
private static String getCellDate(Cell cell) {
String return_string = null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
return_string = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat format = new DecimalFormat("#,##0.000");
return_string = String.valueOf(format.format(cell.getNumericCellValue()));
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
return_string = String.valueOf(cell.getBooleanCellValue());
default:
return_string = "";
break;
}
return return_string;
}
/**
* 样式1
*
* @param workbook
* @param cell
* @return
*/
private static HSSFCell styleOne(HSSFWorkbook workbook, HSSFCell cell) {
//创建CellStyle或HSSFCellStyle对象
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置单元格字体位置水平方向
style.setAlignment(HorizontalAlignment.LEFT);
//设置单元格字体位置垂直方向
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
style.setBorderBottom(BorderStyle.THIN); //底部边框样式
//通过颜色索引设置底部颜色
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //底部边框颜色
//同理，设置左边样式
style.setBorderLeft(BorderStyle.THIN); //左边边框样式
style.setLeftBorderColor(IndexedColors.BLUE.getIndex()); //左边边框颜色
//同理，设置右边样式
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREEN.getIndex());
//最后，设置顶部样式
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BROWN.getIndex());
//设置字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12); // 字体高度
font.setFontName(" 宋体 "); // 字体
style.setFont(font);
cell.setCellStyle(style);
return cell;
}
/**
* 样式2
*
* @param workbook
* @param cell
* @return
*/
private static HSSFRow styleTwo(HSSFWorkbook workbook, HSSFRow cell) {
//设置单元格数据格式
HSSFCellStyle textStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("#,##0.000"));
cell.setRowStyle(textStyle);
return cell;
}
}
方式二（阿里巴巴EasyExcel）
- pom.xml导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
- 实体类
import com.alibaba.excel.annotation.ExcelProperty;
public class UserEntity {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年龄")
private Integer age;
@ExcelProperty("职业")
private String occupation;
@ExcelProperty("账户")
private String account;
@ExcelProperty("地址")
private String address;
//gettrt和setter省略。。。。
}
- EasyExcel工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
public static void process(String fileName, List target, Class clazs , HttpServletResponse response) throws IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
EasyExcel.write(response.getOutputStream(), clazs).excelType(ExcelTypeEnum.XLS).autoCloseStream(Boolean.TRUE)
.sheet("用户报表").doWrite(target);
}
}
- EasyExcelListener类(用于导入)
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.List;
/**
* @Author: zhuzixin
* @CreateDate: 2021-07-04 15:09
*/
public class EasyExcelListener<T> extends AnalysisEventListener<T> {
private List<T> scoringPlayersBeanList;
public EasyExcelListener() {
}
public EasyExcelListener(List<T> scoringPlayersBeanList) {
this.scoringPlayersBeanList = scoringPlayersBeanList;
}
@Override
public void invoke(T t, AnalysisContext analysisContext) {
System.out.println(t);
scoringPlayersBeanList.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
- Controller类[导出]
import com.husky.tinelion.entity.UserEntity;
import com.husky.tinelion.utils.ExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@RestController
public class UserReportFormController {
@GetMapping("/load")
public void downloadReportData(HttpServletResponse response){
String fileName = "用户详情报表数据.xls";
List<UserEntity> userEntities = new ArrayList<>();
UserEntity userEntity = new UserEntity();
userEntity.setName("自鑫");
userEntity.setAccount("9999999999999");
userEntity.setAddress("成都");
userEntity.setAge(18);
userEntity.setOccupation("码农");
userEntities.add(userEntity);
try {
ExcelUtil.process(fileName,userEntities,UserEntity.class,response);
} catch (IOException e) {
e.printStackTrace();
}
}
}
- 导出结果
- Controller类[导入]
@RestController
public class RegionExcelController {
@Autowired
private DicLocationProvinceMapper provinceMapper;
@Autowired
private DicLocationCityMapper cityMapper;
@Autowired
private DicLocationRegionMapper regionMapper;
@GetMapping("/execute")
public void executeUploadRegion() throws FileNotFoundException {
FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\Procedural.life\\Desktop\\tianjing\\regions.xlsx"));
//此处为easyexcel解析List<Region>实例
List<Region> regions = new ArrayList<>();
EasyExcelListener<Region> easyExcelListener = new EasyExcelListener<>(regions);
EasyExcel.read(inputStream, Region.class, easyExcelListener).sheet().doRead();
Map<String, List<Region>> p = regions.stream().collect(Collectors.groupingBy(Region::getProvince));
Counter counter = new Counter();
p.forEach((k, v) -> {
counter.setA(counter.getA() + 1);
DicLocationProvince province = new DicLocationProvince();
province.setDicLocationCountryId(1);
province.setDicLocationProvinceId(counter.getA());
province.setProvinceName(k);
provinceMapper.insertSelective(province);
System.out.println(k + ":" + counter.getA());
Map<String, List<Region>> c = v.stream().collect(Collectors.groupingBy(Region::getCity));
c.forEach((m, n) -> {
counter.setB(counter.getB() + 1);
DicLocationCity city = new DicLocationCity();
city.setDicLocationProvinceId(counter.getA());
city.setDicLocationCountryId(1);
city.setDicLocationCityId(counter.getB());
city.setCityName(m);
cityMapper.insertSelective(city);
System.out.println(m + ":" + counter.getA()+ ":" + counter.getB());
n.forEach(co -> {
counter.setC(counter.getC() + 1);
DicLocationRegion region = new DicLocationRegion();
region.setDicLocationCityId(counter.getB());
region.setDicLocationRegionId(counter.getC());
region.setRegionName(co.getCounty());
regionMapper.insertSelective(region);
System.out.println(co.getCounty()+ ":" + counter.getA()+ ":" + counter.getB()+ ":" + counter.getC());
});
});
});
}
}
上面的实例是将一个省市县的excel表解析保存到自己的省市县表，源文件如下：
- EasyExcel动态导出数据及合并表头示例
/**
* 动态导出excel表
* @param response
*/
public static void generateExcel(HttpServletResponse response) {
//完整表格头
ArrayList<List<String>> head = new ArrayList<>();
head.add(Stream.of("序号").collect(Collectors.toList()));
head.add(Stream.of("姓名").collect(Collectors.toList()));
head.add(Stream.of("年龄").collect(Collectors.toList()));
head.add(Stream.of("地址").collect(Collectors.toList()));
head.add(Stream.of("时间").collect(Collectors.toList()));
//表格头1
ArrayList<List<String>> head1 = new ArrayList<>();
head1.add(Stream.of("序号").collect(Collectors.toList()));
//表格头2
ArrayList<List<String>> head2 = new ArrayList<>();
head2.add(Stream.of("序号").collect(Collectors.toList()));
head2.add(Stream.of("").collect(Collectors.toList()));
head2.add(Stream.of("001").collect(Collectors.toList()));
//表格头3
ArrayList<List<String>> head3 = new ArrayList<>();
head3.add(Stream.of("序号").collect(Collectors.toList()));
head3.add(Stream.of("002").collect(Collectors.toList()));
//导出数据
List<List<String>> bodyList = new ArrayList<>();
bodyList.add(Stream.of("1", "张三", "15", null, "2022-09-02").collect(Collectors.toList()));
bodyList.add(Stream.of("2", "李四", null, "天津", "2022-09-03").collect(Collectors.toList()));
//创建EXCEL对象
ExcelWriterBuilder builder = null;
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("个人信息.xlsx", "utf-8"));
builder = EasyExcel.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
//设置处理器，合并单元格，列宽处理器等
builder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
//获取writer对象
ExcelWriter writer = builder.build();
WriteSheet sheet = EasyExcel.writerSheet(0, "个人信息").build();
//创建表格并写入表头
WriteTable table1 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
table1.setHead(head);
WriteTable table2 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
table2.setHead(head1);
WriteTable table3 = EasyExcel.writerTable(2).needHead(Boolean.TRUE).build();
table3.setHead(head2);
WriteTable table4 = EasyExcel.writerTable(3).needHead(Boolean.TRUE).build();
table4.setHead(head3);
//写入4次数据，每次会自动生成一个表头
writer.write(new ArrayList(), sheet, table2);
writer.write(new ArrayList(), sheet, table3);
writer.write(new ArrayList(), sheet, table4);
writer.write(bodyList, sheet, table1);
writer.finish();
}
导出效果如下：
扫一扫
7625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
