设计的初衷
最近公司接了一个政府外包项目,要导出11张复杂的Excel报表,由于报表内容还要在前端展示,我就把每一行的数据属性封装成成了一个实体,这样方便数据传递,然后我就只需要把实体里的数据填充到Excel模板里就行了,所以我需要一个可以复用的工具,能完成所有报表的数据导入的工具类,此外报表中有些相同内容的单元格需要合并。由于这个项目由我个人独立负责,然后我也是初到公司不到一个月,顺便说下也是刚毕业,对公司技术栈了解不多,公司没有这一块的工具类,网上应该有,但是我没去找,就想着自己设计一个吧。
对Excel的操作工具选择
这里选择的是apache poi,个人以前写过一个工具方法,就是将数据库中所有的表名,字段名,属性,备注等所有内容导出到Excel时接触过它,发现确实很好用,也许是我没有用过更好用的吧(手动滑稽)
导入依赖:
<!-- apache POI for xls -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- apache POI for xlsx -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
设计思路介绍
数据导入思路
我的想法是,在数据model的属性中加上一个自定义注解,这个注解很简单,就一个value,用来记录该属性在Excel的第几列。
自定义注解如下:
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD, ElementType.TYPE })
public @interface ExcelCell {
int value() default 0;
}
然后工具类通过反射获取数据model的数据值以及注解的value值(也就是该属性数据的列),没有注解的熟悉直接跳过。然后使用apache poi将对应的数据填充到对应的位置。
后来发现有的数据model里的属性对象的数据也需要填充到EXCEL,于是在写入方法中加了一个判断(isBaseType () 方法,判断是否基本对象,和日期对象,String对象),如果是内置属性对象,那就递归调用此方法,将内置对象的属性值也填充到Excel。
代码如下:
private static int writeRow(Object model,XSSFRow row) throws IllegalAccessException {
//记录输出的的Excel有多少列,用来后期的合并单元格使用
int maxCellIndex=0;
Field[] fields = model.getClass().getDeclaredFields();
for(Field field : fields){
//判断该属性是否有注解,没有注解就不写入
boolean fieldHasAnno = field.isAnnotationPresent(ExcelCell.class);
field.setAccessible(true);
//获取属性值
Object value = field.get(model);
if (value==null){
continue;
}
if(fieldHasAnno&&isBaseType(value)){
ExcelCell fieldAnno = field.getAnnotation(ExcelCell.class);
//输出注解属性
int index = fieldAnno.value();
if(index>maxCellIndex){
maxCellIndex=index;
}
XSSFCell ce0 = row.createCell(index);
if(value instanceof Date) {
SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ce0.setCellValue(format.format((Date)value));
}else{
ce0.setCellValue(""+ value);
}
}else if (!isBaseType(value)){
//此处递归调用,写入内置属性对象数据
int maxIndex=writeRow(value,row);
if(maxIndex>maxCellIndex){
maxCellIndex=maxIndex;
}
}
}
return maxCellIndex;
}
单元格合并思路
我选择的是选择好需要合并的列,然后依次遍历该一列,如果有相同的单元格就记录行号,遇到不同的就把上面相同的单元格合并,然后换新值再继续寻找,遇到为空的直接跳过。
代码如下:
private static void mergeOneCell(XSSFSheet sheet, int cellLine, int startRow, int endRow){
int s=startRow;
//获取该一列的值,如果值为空,就返回 “”
String cellValue = getCellValue(sheet.getRow(s).getCell(cellLine));
for (int i=s+1;i<=endRow+1;i++){
Row row=sheet.getRow(i);
//到了最后一行
if (row==null){
if(s!=i-1) {
sheet.addMergedRegion(new CellRangeAddress(s, i-1, cellLine, cellLine));
}
break;
}
String value = getCellValue(row.getCell(cellLine));
//遇到空的单元格,或者和上一个内容不相符的单元格,往下移动
if(value.equals("")||!cellValue.equals(value)) {
//新的一行内容和之前的不一样,合并之前的单元格
if(s!=i-1) {
sheet.addMergedRegion(new CellRangeAddress(s, i-1, cellLine, cellLine));
}
cellValue = value;
s = i ;
}
}
}
工具类的全代码
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.jeesite.modules.audit.enums.ExcelReportFileModelPath;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* @description: 基于apache POI操作Excel工具类
* @author: Mr.Luke
* @create: 2019-07-16 15:20
* @Version V1.0
*/
public class ExcelUtils {
private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/***
* @Author: Mr.Luke
* @Description: 将数据导入Excel
* @Date: 16:30 2019/7/16
* @Param: [excelReportFilePath, savePath,sheetName, list]
* @return: void
*/
public static void importXlsx(ExcelReportFileModelPath excelReportFileModelPath,String savePath, String sheetName, List list,int[] cellLine){
XSSFWorkbook workbook=importXlsxFile(excelReportFileModelPath,sheetName,list,null);
//输出到磁盘中
FileOutputStream fos=null;
try {
fos = new FileOutputStream(new File(savePath));
workbook.write(fos);
} catch (FileNotFoundException e) {
logger.error("输出文件不存在",e);
} catch (IOException e) {
logger.error("写入失败",e);
}finally {
try {
if(workbook!=null){
workbook.close();
}
if(fos!=null){
fos.close();
}
} catch (IOException e) {
logger.error("关闭失败",e);
}
}
}
/***
* @Author: Mr.Luke
* @Description: 将数据导入Excel
* @Date: 16:30 2019/7/16
* @Param: [excelReportFilePath, savePath,sheetName, list]
* @return: void
*/
public static void importXlsx(ExcelReportFileModelPath excelReportFileModelPath,OutputStream outputStream, String sheetName, List list){
importXlsx(excelReportFileModelPath,outputStream,sheetName,list,null);
}
/***
* @Author: Mr.Luke
* @Description: 将数据导入Excel
* @Date: 16:30 2019/7/16
* @Param: [excelReportFilePath, savePath,sheetName, list]
* @return: void
*/
public static void importXlsx(ExcelReportFileModelPath excelReportFileModelPath,OutputStream outputStream, String sheetName, List list,int[] cellLine){
XSSFWorkbook workbook=importXlsxFile(excelReportFileModelPath,sheetName,list,cellLine);
try {
workbook.write(outputStream);
}catch (IOException e) {
logger.error("写入失败",e);
}finally {
try {
if(workbook!=null){
workbook.close();
}
} catch (IOException e) {
logger.error("关闭失败",e);
}
}
}
private static XSSFWorkbook importXlsxFile(ExcelReportFileModelPath excelReportFileModelPath , String sheetName, List list,int[] cellLine){
//获得模板
InputStream in = ExcelUtils.class.getClassLoader().getResourceAsStream("reportTemplate/"+excelReportFileModelPath.getCode());
//由输入流得到工作簿
XSSFWorkbook workbook=null;
try {
workbook = new XSSFWorkbook(in);
} catch (IOException e) {
// TODO Auto-generated catch block
logger.error("模板文件导入失败",e);
}finally {
try {
if(in!=null){
in.close();
}
} catch (IOException e) {
logger.error("关闭失败",e);
}
}
if(sheetName==null){
sheetName="Sheet1";
}
//得到工作表
XSSFSheet sheet = workbook.getSheet(sheetName);
if (sheet==null){
sheet=workbook.createSheet(sheetName);
}
//获取新增行的行数
int startRow=sheet.getLastRowNum()+1;
int indexRow=sheet.getLastRowNum();
int maxCellIndex=0;
for (Object model : list) {
int r=++indexRow;
XSSFRow row = sheet.createRow(r);
//获取新增的列数
try {
maxCellIndex=writeRow(model, row);
} catch (IllegalAccessException e) {
logger.error("新增数据失败",e);
}
}
//合并相同内容单元格
if(cellLine==null){
mergeCell(sheet,maxCellIndex,startRow,sheet.getLastRowNum());
}else{
mergeCell(sheet,cellLine,startRow,sheet.getLastRowNum());
}
return workbook;
}
private static int writeRow(Object model,XSSFRow row) throws IllegalAccessException {
int maxCellIndex=0;
Field[] fields = model.getClass().getDeclaredFields();
for(Field field : fields){
boolean fieldHasAnno = field.isAnnotationPresent(ExcelCell.class);
field.setAccessible(true);
//获取属性值
Object value = field.get(model);
if (value==null){
continue;
}
if(fieldHasAnno&&isBaseType(value)){
ExcelCell fieldAnno = field.getAnnotation(ExcelCell.class);
//输出注解属性
int index = fieldAnno.value();
if(index>maxCellIndex){
maxCellIndex=index;
}
XSSFCell ce0 = row.createCell(index);
if(value instanceof Date) {
SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ce0.setCellValue(format.format((Date)value));
}else{
ce0.setCellValue(""+ value);
}
}else if (!isBaseType(value)){
int maxIndex=writeRow(value,row);
if(maxIndex>maxCellIndex){
maxCellIndex=maxIndex;
}
}
}
return maxCellIndex;
}
/**
* 判断object是否为基本类型 和字符串类型 和日期类型
* @param object
* @return
*/
public static boolean isBaseType(Object object) {
if (object==null){
return false;
}
Class<? extends Object> className = object.getClass();
if (className.equals(java.lang.Integer.class) ||
className.equals(java.lang.Byte.class) ||
className.equals(java.lang.Long.class) ||
className.equals(java.lang.Double.class) ||
className.equals(java.lang.Float.class) ||
className.equals(java.lang.Character.class) ||
className.equals(java.lang.Short.class) ||
className.equals(java.lang.Boolean.class)||
className.equals(String.class)||
className.equals(Date.class)
) {
return true;
}
return false;
}
/***
* @Author: Mr.Luke
* @Description: 合并内容相同待的单元格
* @Date: 13:17 2019/7/17
* @Param: [sheet, cellLine, startRow, endRow]
* @return: void
*/
private static void mergeCell(XSSFSheet sheet, int cellLine, int startRow, int endRow){
for (int i=0;i<=cellLine;i++){
mergeOneCell(sheet,i,startRow,endRow);
}
}
/***
* @Author: Mr.Luke
* @Description: 合并内容相同待的单元格
* @Date: 13:17 2019/7/17
* @Param: [sheet, cellLine, startRow, endRow]
* @return: void
*/
private static void mergeCell(XSSFSheet sheet, int[] cellLine, int startRow, int endRow){
for (int i=0;i<cellLine.length;i++){
mergeOneCell(sheet,cellLine[i],startRow,endRow);
}
}
/***
* @Author: Mr.Luke
* @Description: 合并一列的相同内容的单元格
* @Date: 13:16 2019/7/17
* @Param: [sheet, cellLine, startRow, endRow]
* @return: void
*/
private static void mergeOneCell(XSSFSheet sheet, int cellLine, int startRow, int endRow){
int s=startRow;
String cellValue = getCellValue(sheet.getRow(s).getCell(cellLine));
for (int i=s+1;i<=endRow+1;i++){
Row row=sheet.getRow(i);
//到了最后一行
if (row==null){
if(s!=i-1) {
sheet.addMergedRegion(new CellRangeAddress(s, i-1, cellLine, cellLine));
}
break;
}
String value = getCellValue(row.getCell(cellLine));
//遇到空的单元格,或者和上一个内容不相符的单元格,往下移动
if(value.equals("")||!cellValue.equals(value)) {
//新的一行内容和之前的不一样,合并之前的单元格
if(s!=i-1) {
sheet.addMergedRegion(new CellRangeAddress(s, i-1, cellLine, cellLine));
}
cellValue = value;
s = i ;
}
}
}
/**
* 获取单元格的值
* @param cell
* @return
*/
private static String getCellValue(Cell cell){
if(cell == null){ return "";}
return cell.getStringCellValue();
}
}