【工具类】ExcelUtils
这是笔者自己发现的比较好的Excel操作类。分享给大家。
在使用这个类之前，你需要导入以下依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
导出
package tool.excel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 导出Excel工具类
*
* @param <T>
* @author xxx
*/
public class ExportExcelUtils<T> {
/**
* 导出excel表格
* 导出list的所有内容，使用属性名称作为列名称
*
* @param outputStream 文件输出流
* @param list 内容
*/
public boolean exportExcel(OutputStream outputStream,
List<T> list) {
if (list == null || list.size() == 0) {
System.out.println("您所查找的数据不存在！");
return false;
}
T obj = list.get(0);
Field[] fields = obj.getClass().getDeclaredFields();
List<String> propertyList = new ArrayList<>();
for (Field field : fields) {
if ("serialVersionUID".equals(field.getName())) {
continue;
}
propertyList.add(field.getName());
}
String[] propertys = new String[propertyList.size()];
for (int i = 0; i < propertyList.size(); i++) {
propertys[i] = propertyList.get(i);
}
return exportExcel(outputStream, "", propertys, propertys, list, "");
}
/**
* 导出excel表格
* 默认描述说明行高为11*256，标题行高为500，带有序号
*
* @param outputStream 文件输出流
* @param sheetname 表名
* @param titles 列名称
* @param propertys 属性名称
* @param list 内容
* @param description 描述说明
*/
public boolean exportExcel(OutputStream outputStream,
String sheetname,
String[] titles,
String[] propertys,
List<T> list,
String description) {
return exportExcel(outputStream, sheetname, titles, propertys, list, description, (short) (11 * 256), (short) 500, true);
}
/**
* 填写内容
*
* @param outputStream 文件输出流
* @param sheetname 表名
* @param titles 列名称
* @param propertys 属性名称
* @param list 内容
* @param description 描述说明
* @param descriptionRowHeight 描述说明行高
* @param titleRowHeight 标题行高
* @param serialFlag 是否添加序号
*/
public boolean exportExcel(OutputStream outputStream,
String sheetname,
String[] titles,
String[] propertys,
List<T> list,
String description,
short descriptionRowHeight,
short titleRowHeight,
boolean serialFlag) {
try {
if (StringUtils.isBlank(sheetname)) {
sheetname = "sheet1";
}
if (titles == null || titles.length == 0) {
System.out.println("请设置列名");
return false;
}
if (propertys == null || propertys.length == 0) {
System.out.println("请设置属性名");
return false;
}
if (titles.length != propertys.length) {
System.out.println("列名数量与属性名数量不一致");
return false;
}
if (list == null || list.size() == 0) {
System.out.println("您所查找的数据不存在！");
return false;
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetname);
//设置默认行宽
sheet.setDefaultColumnWidth(20);
//列数
int columnLength = titles.length;
//行数
int rowNum = 0;
// 设置表描述
if (StringUtils.isNotBlank(description)) {
fillDescript(workbook, sheet, columnLength, rowNum++, description, descriptionRowHeight);
}
// 设置表头
fillTitle(workbook, sheet, columnLength, rowNum++, titles, titleRowHeight, serialFlag);
// 填充数据
fillBody(workbook, sheet, columnLength, rowNum, propertys, list, serialFlag);
// 将文件输出到客户端浏览器
try {
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
return true;
} catch (Exception e) {
System.out.println(sheetname + ":导出表格异常2");
e.printStackTrace();
return false;
}
} catch (Exception e) {
System.out.println(sheetname + ":导出表格异常2");
e.printStackTrace();
return false;
}
}
/**
* 填写描述说明
*
* @param workbook 文件
* @param sheet 表
* @param columnLength 总列数
* @param rownum 起始行
* @param description 描述说明
* @param rowHeight 行高
*/
private void fillDescript(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String description, short rowHeight) {
XSSFCellStyle style = getDescriptStyle(workbook);
//新增一行
XSSFRow row = sheet.createRow(rownum);
//行高,默认11*256
row.setHeight(rowHeight);
for (int i = 0; i < columnLength; i++) {
//列索引从0开始
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString richTextString = new XSSFRichTextString("");
if (i == 0) {
//首行说明文字
richTextString = new XSSFRichTextString(description);
}
cell.setCellValue(richTextString);
}
// 合并单元格
// 起始行, 终止行, 起始列, 终止列
CellRangeAddress cellRangeAddress = new CellRangeAddress(rownum, rownum, 0, columnLength);
sheet.addMergedRegion(cellRangeAddress);
}
/**
* 填写标题
*
* @param workbook 文件
* @param sheet 表
* @param columnLength 总列数
* @param rownum 起始行
* @param titles 列名称
* @param rowHeight 行高
* @param serialFlag 是否添加序号
*/
private void fillTitle(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String[] titles, short rowHeight, boolean serialFlag) {
XSSFCellStyle style = getTitleStyle(workbook);
//新增一行
XSSFRow row = sheet.createRow(rownum);
//行高,默认500
row.setHeight(rowHeight);
XSSFCell cell;
if (serialFlag) {
//列索引从0开始
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(new XSSFRichTextString("序号"));
}
for (int i = 0; i < columnLength; i++) {
if (serialFlag) {
//列索引从1开始
cell = row.createCell(i + 1);
} else {
//列索引从1开始
cell = row.createCell(i);
}
cell.setCellStyle(style);
String textValue = "";
if (StringUtils.isNotBlank(titles[i])) {
textValue = titles[i];
}
cell.setCellValue(new XSSFRichTextString(textValue));
}
//冻结窗口
sheet.createFreezePane(0, rownum + 1);
}
/**
* 填写内容
*
* @param workbook 文件
* @param sheet 表
* @param columnLength 总列数
* @param rownum 起始行
* @param propertys 属性名称
* @param list 内容
* @param serialFlag 是否添加序号
*/
private void fillBody(XSSFWorkbook workbook, XSSFSheet sheet, int columnLength, int rownum, String[] propertys, List<T> list, boolean serialFlag) {
try {
XSSFCellStyle style = getBodyStyle(workbook);
for (int i = 0; i < list.size(); i++) {
//新增一行
XSSFRow row = sheet.createRow(rownum + i);
XSSFCell cell;
if (serialFlag) {
cell = row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(i + 1);
}
T obj = list.get(i);
for (int j = 0; j < propertys.length; j++) {
if (serialFlag) {
//列索引从1开始
cell = row.createCell(j + 1);
} else {
//列索引从1开始
cell = row.createCell(j);
}
cell.setAsActiveCell();
cell.setCellStyle(style);
//属性
String propertyName = propertys[j];
if (StringUtils.isNotBlank(propertyName)) {
// 拼接get方法获取值
Object value = getFieldValueByName(propertyName, obj);
if (null != value) {
// 属性类型
String typeName = value.getClass().getTypeName();
switch (typeName) {
case "java.lang.String":
cell.setCellValue(new XSSFRichTextString(value.toString()));
break;
case "java.util.Date":
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
break;
case "java.lang.Integer":
cell.setCellValue((Integer) value);
break;
case "java.lang.Float":
cell.setCellValue((Float) value);
break;
case "java.lang.Double":
cell.setCellValue((Double) value);
break;
case "java.util.List":
cell.setCellValue("");
break;
default:
cell.setCellValue(value.toString());
break;
}
} else {
cell.setCellValue("");
}
} else {
cell.setCellValue("");
}
}
}
} catch (Exception ex) {
System.out.println("设置表内容异常");
ex.printStackTrace();
}
}
/**
* 根据属性名获取属性值
*/
public Object getFieldValueByName(String fieldName, Object o) throws Exception {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter, new Class[]{});
return method.invoke(o, new Object[]{});
}
/**
* 设置描述说明样式
*
* @param workbook workbook
* @return XSSFCellStyle
*/
private XSSFCellStyle getDescriptStyle(XSSFWorkbook workbook) {
//标题样式
XSSFCellStyle style = workbook.createCellStyle();
//边框
setBorderStyle(style);
//style.setAlignment(HorizontalAlignment.CENTER);//水平居中
//style.setVerticalAlignment(VerticalAlignment.BOTTOM);//垂直居中
//自动换行
style.setWrapText(true);
//字体
Font font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 9);
style.setFont(font);
return style;
}
/**
* 设置标题样式
*
* @param workbook workbook
* @return XSSFCellStyle
*/
private XSSFCellStyle getTitleStyle(XSSFWorkbook workbook) {
//标题样式
XSSFCellStyle style = workbook.createCellStyle();
//居中
setCenterStyle(style);
//边框
setBorderStyle(style);
//背景颜色为灰色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(150, 150, 150)));
//字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 9);
style.setFont(font);
return style;
}
/**
* 设置内容样式
*
* @param workbook workbook
* @return XSSFCellStyle
*/
private XSSFCellStyle getBodyStyle(XSSFWorkbook workbook) {
//标题样式
XSSFCellStyle style = workbook.createCellStyle();
//居中
setCenterStyle(style);
//边框
setBorderStyle(style);
//字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 9);
style.setFont(font);
return style;
}
/**
* 设置边框样式
*
* @param style 风格
*/
private void setBorderStyle(XSSFCellStyle style) {
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
}
/**
* 设置居中样式
*
* @param style 风格
*/
private void setCenterStyle(XSSFCellStyle style) {
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
}
}
导入
package tool.excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
/**
* 导入Excel工具类
*
* @param
*/
@Slf4j
public class ImportExcelUtils<T> {
/**
* HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
* XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx；
* 当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook
*
* @param filename 文件名
* @param inputStream 输入流
* @param titles 列名称
* @param descriptionFlag 首行是否为描述
* @return boolean
*/
public String[][] ImportExcel(String filename, InputStream inputStream,
String[] titles,
boolean descriptionFlag) {
try {
// 验证文件名是否合格
if (!validateExcel(filename)) {
System.out.println("文件名不是excel格式");
return null;
}
Workbook workbook = null;
// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(filename)) {
workbook = new XSSFWorkbook(inputStream);
} else {
workbook = new HSSFWorkbook(inputStream);
}
if (workbook.getNumberOfSheets() == 0) {
System.out.println("这是一个空文件，请编辑后重新上传");
return null;
}
Sheet sheet = workbook.getSheetAt(0);
//总行数
int totalRows = sheet.getLastRowNum();
if (totalRows == 0) {
System.out.println("第一个sheet为空，请编辑后重新上传");
return null;
}
//总列数
int totalColumn = sheet.getRow(0).getPhysicalNumberOfCells();
if (totalColumn == 0) {
System.out.println("第一个sheet为空，请编辑后重新上传");
return null;
}
if (totalColumn != titles.length) {
System.out.println("第一个sheet列名数量与要求的不一致");
return null;
}
return ImportExcel(sheet, totalRows, totalColumn, titles, descriptionFlag);
} catch (Exception ex) {
ex.printStackTrace();
System.out.println("导入Excel发送异常");
return null;
}
}
/**
* @param sheet 第一个表
* @param totalRows 总行数
* @param totalColumn 总列数
* @param titles 列名称
* @param descriptionFlag 首行是否为描述
* @return
*/
public String[][] ImportExcel(Sheet sheet, int totalRows, int totalColumn,
String[] titles, boolean descriptionFlag) {
//起始行
int initeRow = descriptionFlag ? 1 : 0;
String[][] tableData = new String[totalRows - 1][totalColumn];
for (int r = initeRow; r <= totalRows; r++) {
Row row = sheet.getRow(r);
String[] rowData = new String[totalColumn];
for (int c = 0; c < totalColumn; c++) {
Cell cell = row.getCell(c);
CellType type = cell.getCellTypeEnum();
String cellVal;
switch (type) {
case STRING:
cellVal = cell.getStringCellValue().trim();
break;
case NUMERIC:
double val = cell.getNumericCellValue();
cellVal = String.valueOf(val);
double valDiv = val % 1.0;
if (valDiv == 0) {
cellVal = String.valueOf((long) val);
}
break;
case BOOLEAN:
cellVal = String.valueOf(cell.getBooleanCellValue());
break;
default:
cellVal = "";
break;
}
if (r == initeRow) {
String titleName = titles[c].trim();
if (!cellVal.equals(titleName)) {
System.out.printf("第%s列不是%s，请下载最新的用户模板！%n", c, titleName);
return null;
}
} else {
rowData[c] = cellVal;
}
}
if (r != initeRow) {
tableData[r - initeRow - 1] = rowData;
}
}
return tableData;
}
/**
*
*
* @param filePath 文件路径
* @return 验证EXCEL文件结果
*/
private boolean validateExcel(String filePath) {
return isExcel2003(filePath) || isExcel2007(filePath);
}
/**
* @param filePath 文件路径
* @return 是否是2003的excel，返回true是2003
*/
private static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* @param filePath 文件路径
* @return 是否是2007的excel，返回true是2007
*/
private static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
private Map<String, Object> getExcelInfo(MultipartFile mFile) {
Map<String, Object> map = new HashMap<String, Object>();
// 获取文件名
String fileName = mFile.getOriginalFilename();
try {
// 验证文件名是否合格
if (!validateExcel(fileName)) {
return null;
}
// 根据文件名判断文件是2003版本还是2007版本
boolean isExcel2003 = true;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
map = createExcel(mFile.getInputStream(), isExcel2003);
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
private Map<String, Object> createExcel(InputStream is, boolean isExcel2003) {
Map<String, Object> map = new HashMap<String, Object>();
try {
Workbook wb = null;
if (isExcel2003) {
// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {
// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
map.put("wb", wb);
} catch (IOException e) {
e.printStackTrace();
}
return map;
}
}
