一,概述
好几个项目都需要与Excel打交道,每次都要特地去解析、导出,费时费力!所以参考了两位作者的文章,整理了一下POI导入导出的通用工具类。虽说是通用工具类,也只能对那些比较规则的Excel表进行操作,对于那些不规则的Excel表,还是要撸起袖子加油干!(下面这张图是POI的单元格数据类型)
二,实现
1,主要依赖
POI的一些依赖以及阿里的fastjson
<!-- POI Excel表 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.51</version>
</dependency>
2,解析Excel表工具类
1)解析Excel表工具类,我参考了一位作者的博文(非常感谢!!!),并做了一些优化——POI读取excel文件通用代码(增强版--支持返回javaBean集合)---2003,2007
2)完整代码
package com.statement.utils.excel;
import com.alibaba.fastjson.JSONObject;
import com.statement.pojo.YlswBranch;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
/**
* Execel(2003->xls,2007以上->xlsx)解析工具类
*/
public class ParseExcelUtil {
//2003版本 最大支持65536 行
private static final String EXCEL_XLS = "xls";
//2007 版本以上 最大支持1048576行
private static final String EXCEL_XLSX = "xlsx";
private static List<String> columns;//要解析excel中的列名
private static int sheetNum = 0;//要解析的sheet下标
public static List<String> getColumns() {
return ParseExcelUtil.columns;
}
public static void setColumns(List<String> columns) {
ParseExcelUtil.columns = columns;
}
public static int getSheetNum() {
return sheetNum;
}
public static void setSheetNum(int sheetNum) {
ParseExcelUtil.sheetNum = sheetNum;
}
/**
* 判断Excel的版本,获得对应的Workbook
*
* @param file
* @return
* @throws Exception
*/
public static Workbook getWorkbook(File file) {
Workbook wb = null;
try {
checkExcelValid(file);
InputStream in = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) {//Excel2003及以下
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) {//Excel2007及以上
wb = new XSSFWorkbook(in);
}
} catch (Exception e) {
e.printStackTrace();
wb = null;
}
return wb;
}
/**
* 直接传入输入流和文件名(xxx.xls或xxx.xlsx)获取对应的Workbook
*
* @param ins
* @param fileName
* @return
*/
public static Workbook getWorkbook(InputStream ins, String fileName) {
Workbook wb = null;
try {
if (!fileName.endsWith(EXCEL_XLS) && !fileName.endsWith(EXCEL_XLSX)) {
throw new Exception("不是标准的Excel文件");
}
if (fileName.endsWith(EXCEL_XLS)) {//Excel2003及以下
wb = new HSSFWorkbook(ins);
} else if (fileName.endsWith(EXCEL_XLSX)) {//Excel2007及以上
wb = new XSSFWorkbook(ins);
}
} catch (Exception e) {
e.printStackTrace();
wb = null;
}
return wb;
}
/**
* 判断Excel文件是否有效,无效时抛出异常
*
* @param file
* @throws Exception
*/
public static void checkExcelValid(File file) throws Exception {
//System.out.println("filename:" + file.getName());
if (!file.exists()) {
//文件不存在
throw new Exception("文件不存在");
} else if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new Exception("不是标准的Excel文件");
}
}
/**
* 获取Excel文件中,从startIndex到(总行数-endIndex)之间的数据,返回json数组
*
* @param file
* @param startIndex
* @return
*/
public static String readExcel(File file, int startIndex, int endIndex) {
StringBuilder retJson = new StringBuilder();
Workbook workbook = getWorkbook(file);
Sheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum();//最后一行
System.out.println("lastRowNum:" + lastRowNum);
retJson.append("[");
//for (int i = 0; i < lastRowNum; i++) {
for (int i = startIndex; i < lastRowNum - endIndex; i++) {
Row row = sheet.getRow(i);//获得行
String rowJson = readExcelRow(row);
retJson.append(rowJson);
if (i < lastRowNum - 1)
retJson.append(",");
}
retJson.append("]");
try {
//关闭资源
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
return retJson.toString();
}
}
/**
* 网页上传文件时,通过流的方式更为便捷,从startIndex到(总行数-endIndex)之间的数据,返回json数组
*
* @param ins
* @param fileName
* @param startIndex
* @param endIndex
* @return
*/
public static String readExcel(InputStream ins, String fileName, int startIndex, int endIndex) {
StringBuilder retJson = new StringBuilder();
Workbook workbook = getWorkbook(ins, fileName);
Sheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum();//最后一行
System.out.println("lastRowNum:" + lastRowNum);
retJson.append("[");
//for (int i = 0; i < lastRowNum; i++) {
for (int i = startIndex; i < lastRowNum - endIndex; i++) {
Row row = sheet.getRow(i);//获得行
String rowJson = readExcelRow(row);
retJson.append(rowJson);
if (i < lastRowNum - 1)
retJson.append(",");
}
retJson.append("]");
try {
//关闭资源
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
return retJson.toString();
}
}
/**
* 将json转换为集合,使用阿里的fastjson框架非常便捷
*
* @param json
* @param clazz
* @param <E>
* @return
*/
public static <E> Collection<E> readExcel(String json, Class<E> clazz) {
return JSONObject.parseArray(json, clazz);
}
/**
* 读取每行Row的数据,每行数据对应一个javabean
*
* @param row
* @return 返回一个json对象
*/
private static String readExcelRow(Row row) {
StringBuilder rowJson = new StringBuilder();
int lastCellNum = ParseExcelUtil.columns.size();//最后一个单元格
rowJson.append("{");
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
String cellVal = readCellValue(cell);
rowJson.append(toJsonItem(columns.get(i), cellVal));
if (i < lastCellNum - 1)
rowJson.append(",");
}
rowJson.append("}");
return rowJson.toString();
}
/**
* 读取每个单元格Cell的value,对NUMERIC类型的Cell需要做特别处理
*
* @param cell
* @return 返回Cell的value
*/
@SuppressWarnings("static-access")
private static String readCellValue(Cell cell) {
if (cell == null) {
return null;
}
CellType type = cell.getCellTypeEnum();
String cellValue;
switch (type) {
case BLANK:
cellValue = "";
break;
case _NONE:
cellValue = "";
break;
case ERROR:
cellValue = "";
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
//cellValue = String.valueOf(cell.getNumericCellValue());
//当Cell为日期类型(如2018-11-19)时,需要做特殊处理,否则解析出来的将会是一个距离1900年1月1日的天数(此时为43423)
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(date);
} else {//货币类型 等等
//如果Cell是科学计数法类型的数据或者货币类型时,获取不到想要的字符串,此时通过NumberToTextConverter工具类
// 的toText(...)方法可以解决该问题,获取字符串
cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case FORMULA:
cellValue = cell.getCellFormula();
break;
default:
cellValue = "";
break;
}
return cellValue;
}
/**
* 转换为json对
*
* @return
*/
private static String toJsonItem(String name, String val) {
return "\"" + name + "\":\"" + val + "\"";
}
public static void main(String[] args) {
File file = new File("C:\\Users\\Administrator\\IdeaProjects\\statement\\src\\main\\resources\\10050003400000_20181120_1_test.xlsx");
List<String> list = new ArrayList<>();
list.add("mrchNo");
list.add("transDate");
list.add("transTime");
list.add("terminalNo");
list.add("transType");
list.add("cardNo");
list.add("transAmount");
list.add("clearAmount");
list.add("poundage");
list.add("flowNo");
list.add("sysTracking");
list.add("cardType");
list.add("transReferNo");
list.add("bank");
// list.add("orderNo");
ParseExcelUtil.setColumns(list);
String json = ParseExcelUtil.readExcel(file, 3, 1);
Collection<YlswBranch> ylswBranches = ParseExcelUtil.readExcel(json, YlswBranch.class);
System.out.println("*************************");
System.out.println("ylswBranches1.size():" + ylswBranches.size());
for (YlswBranch yl : ylswBranches) {
System.out.println(yl);
}
}
}
主要做法就是通过readExcelRow(Row row)将Excel表中每行的数据做成json对象,在readExcel(File file, int startIndex, int endIndex)或readExcel(InputStream ins,String fileName, int startIndex, int endIndex)方法中将全部的json对象组成一个json数组。有了一个json数组,通过阿里的fastjson框架,可轻松转换成一个集合,对应为readExcel(String json, Class<E> clazz)方法。
3)测试(测试代码在工具类的main方法中)
a)解析10050003400000_20181120_1_test.xlsx
b)对应实体属性如下
private String mrchNo;
private String transDate;
private String transTime;
private String terminalNo;
private String transType;
private String cardNo;
private String transAmount;
private String clearAmount;
private String poundage;
private String flowNo;
private String sysTracking;
private String cardType;
private String transReferNo;
private String bank;
private String orderNo;
c)获取文件,ParseExcelUtil.setColumns(list)设置Excel文件中每一列对应的名称,执行解析方法ParseExcelUtil.readExcel(file, 3, 1)获得json数组,最后通过ParseExcelUtil.readExcel(json, YlswBranch.class)得到对应的集合。
ParseExcelUtil.setColumns(list);
//从第4行开始解析,直到最后一行前一行
String json = ParseExcelUtil.readExcel(file, 3, 1);
Collection<YlswBranch> ylswBranches = ParseExcelUtil.readExcel(json, YlswBranch.class);
System.out.println("*************************");
System.out.println("ylswBranches1.size():" + ylswBranches.size());
for (YlswBranch yl : ylswBranches) {
System.out.println(yl);
}
集合打印如下:
3 ,导出Excel工具类
1)导出工具类参考了一位作者的博文,精简了一些代码,原文为Java之——导出Excel通用工具类
2)工具类有两个:ExportExcelUtil、ExportExcelWrapper,后者继承前者,后置主要提供网页点击按钮生成Excel文件的快捷操作。
3)ExportExcelUtil代码:
package com.statement.utils.excel;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcelUtil<T> {
// 2007 版本以上 最大支持1048576行
public final static String EXCEl_FILE_2007 = "2007";
// 2003 版本 最大支持65536 行
public final static String EXCEL_FILE_2003 = "2003";
/**
* 通过版本类判断是
*
* @param sheetName 表格标题名
* @param headers 表格头部标题集合
* @param dataset 数据集合
* @param out 输出流
* @param version 指定生成Excel文件的版本
*/
public void exportExcel(String sheetName, String[] headers, Collection<T> dataset, OutputStream out, String version) {
Workbook workbook = null;
if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
exportExcel(workbook, sheetName, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
/**
* 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中 <br>
* 此版本生成2007以上版本的文件 (文件后缀:xlsx)
*
* @param sheetName 表格标题名
* @param headers 表格头部标题集合
* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
* JavaBean属性的数据类型有基本数据类型及String,Date
* @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
*/
public void exportExcel(Workbook workbook, String sheetName, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
// 生成一个表格
Sheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
//设置宽度
//sheet.setDefaultColumnWidth(20);
sheet.setDefaultColumnWidth(18);
//sheet.trackAllColumnsForAutoSizing();
// sheet.autoSizeColumn(0);
// 生成一个样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//内容居中
// 生成一个字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBold(true);//加粗
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
CellStyle style2 = workbook.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
// 生成另一个字体
Font font2 = workbook.createFont();
// font2.setBold(true);//加粗
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
Row row = sheet.createRow(0);
Cell cellHeader;
for (int i = 0; i < headers.length; i++) {
cellHeader = row.createCell(i);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(headers[i]);
}
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
T t;
Field[] fields;
Field field;
//HSSFRichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
String getMethodName;
Cell cell;
Class tCls;
Method getMethod;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
t = (T) it.next();
// 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值
fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style2);
field = fields[i];
fieldName = field.getName();
getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
tCls = t.getClass();
getMethod = tCls.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
// richString = new HSSFRichTextString(textValue);
cell.setCellValue(textValue);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
//关闭资源
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
4)ExportExcelWrapper代码:
package com.statement.utils.excel;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Collection;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 供网页端点击按钮,自动生成并下载Excel表
*
* @param <T>
*/
public class ExportExcelWrapper<T> extends ExportExcelUtil {
/**
* 导出带有头部标题行的Excel
* 时间格式默认:yyyy-MM-dd hh:mm:ss
*
* @param fileName 生成的Excel文件名字(不要加后缀)
* @param title 表格标题
* @param headers 头部标题集合
* @param dataset 数据集合
* @param response
* @param version 2003 或者 2007,不传时默认生成2003版本
*/
public void exportExcel(String fileName, String title, String[] headers, Collection<T> dataset, HttpServletResponse response, String version) {
try {
response.setContentType("application/vnd.ms-excel");
String suffix = null;
if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
suffix = ".xls";
} else {
suffix = ".xlsx";
}
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + suffix);
Workbook workbook = null;
if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
exportExcel(workbook, title, headers, dataset, response.getOutputStream(), "yyyy-MM-dd HH:mm:ss");
} catch (Exception e) {
e.printStackTrace();
}
}
}
思路:
a)在ExportExcelUtil工具类中,通过方法中传入的version来生成对应的Workbook,然后再调用生成Excel文件的核心方法生成目标文件,核心逻辑来自参考文章(再次感谢那位大佬!)。 exportExcel(Workbook workbook, String sheetName, String[] headers, Collection<T> dataset, OutputStream out, String pattern) b)ExportExcelWrapper<T>继承ExportExcelUtil,重载了exportExcel方法,加入了一个response,主要实现逻辑也是调用父类的方法。
exportExcel(String fileName, String title, String[] headers, Collection<T> dataset, HttpServletResponse response, String version)
5)测试
a)解析和生成一起测试
public static void main(String[] args) {
File file = new File("C:\\Users\\Administrator\\IdeaProjects\\statement\\src\\main\\resources\\10050003400000_20181120_1_test.xlsx");
List<String> list = new ArrayList<>();
list.add("mrchNo");
list.add("transDate");
list.add("transTime");
list.add("terminalNo");
list.add("transType");
list.add("cardNo");
list.add("transAmount");
list.add("clearAmount");
list.add("poundage");
list.add("flowNo");
list.add("sysTracking");
list.add("cardType");
list.add("transReferNo");
list.add("bank");
// list.add("orderNo");
ParseExcelUtil.setColumns(list);
String json = ParseExcelUtil.readExcel(file, 3, 1);
Collection<YlswBranch> ylswBranches = ParseExcelUtil.readExcel(json, YlswBranch.class);
System.out.println("*****************11111111111111111111111**************************");
System.out.println("ylswBranches1.size():" + ylswBranches.size());
for (YlswBranch yl : ylswBranches) {
System.out.println(yl);
}
System.out.println("*****************11111111111111111111111**************************");
ExportExcelUtil<YlswBranch> util = new ExportExcelUtil<YlswBranch>();
// 准备数据
String[] columnNames = {"mrchNo", "transDate", "transTime", "terminalNo",
"transType", "cardNo", "transAmount", "clearAmount",
"poundage", "flowNo", "sysTracking", "cardType",
"transReferNo", "bank", "orderNo"};
try {
// util.exportExcel("用户导出", columnNames, ylswBranches, new FileOutputStream("E:/testExcel.xlsx"), ExportExcelUtil.EXCEl_FILE_2007);
util.exportExcel("用户导出", columnNames, ylswBranches, new FileOutputStream("E:/testExcel33.xls"), ExportExcelUtil.EXCEL_FILE_2003);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
System.out.println("程序执行成功!!!!!!!!!!");
}
b)测试ExportExcelWrapper
@Controller
@RequestMapping("/test")
public class TestController {
@RequestMapping("/excel")
public void getExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
ExportExcelWrapper<AccountBill> util = new ExportExcelWrapper<AccountBill>();
// 准备数据
List<AccountBill> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
//没有给实体的所有属性赋值
AccountBill accountBill = new AccountBill();
accountBill.setClearDate("20180923");
accountBill.setTransAmount("23424234234");
accountBill.setTransDate("2018083209");
accountBill.setTransType("支付宝");
accountBill.setTransAmount("10000000");
accountBill.setOriginalPayReferNo("dfsdfsdffasdfas");
accountBill.setCardNo("234234*************324234");
list.add(accountBill);
}
String[] columnNames = {"清算日期", "交易日期", "交易时间", "商户号",
"终端号", "交易类型", "卡号", "总交易金额",
"总清算金额", "总手续费", "参考号", "流水号",
"卡类型", "发卡行", "订单号", "原缴费参考号",
"备注"};
String fileName = "testAccountBill";
//网页导出Excel表
util.exportExcel(fileName, "用户导出", columnNames, list, response, ExportExcelUtil.EXCEl_FILE_2007);
// util.exportExcel("用户导出", columnNames, list, new FileOutputStream("E:/testAccountBill111.xlsx"), ExportExcelUtil.EXCEl_FILE_2007);
System.out.println("程序执行了!!!!!!!!!!!");
}
}
在浏览器上输入:http://127.0.0.1:6225/test/get/excel ,浏览器就可直接下载文件了。部分效果图如下:
三,总结
1,在开发中,经常遇到解析Excel表获取数据和导出数据到Excel表中的需求,还是要准备工具类,提高开发效率!
2,本文中的工具类也只能对那些比较规则的导入导出起作用,对于那些比较复杂的Excel表,无论是解析还是导出,均需要作出特别处理,还是偷不了懒!
3,参考了两位作者的文章,再次感谢!