操作Excel的3种方式
2021-04-20 本文已影响0人
felixfeijs
操作Excel的3种方式
- 相同点：
- Easypoi 和 Easyexcel 都是基于Apache poi进行二次开发的
- 不同点：
- Easypoi 在读写数据的时候，优先是先将数据写入内存，优点是读写性能非常高，但是当数据量很大的时候，会出现oom，当然它也提供了 sax 模式的读写方式，需要调用特定的方法实现
- Easyexcel 基于sax模式进行读写数据，不会出现oom情况，程序有过高并发场景的验证，因此程序运行比较稳定，相对于 Easypoi 来说，读写性能稍慢
Apache poi
基础model
@Data
public class Bsheet {
/**
* sheet的名称
*/
private String sheetName = Constants.SHEET_NAME;
/**
* 文件名称
*/
private String fileName = LocalDateTime.now().getMinute() + "";
/**
* 是否需要合并第一行
*/
private Boolean isMerge = false;
/**
* 合并的第一行的名称
*/
private String firstMergeName = Constants.FIRST_MERGE_NAME;
/**
* 合并第一行的截至列
*/
private int endCell;
/**
* 表格的头部
*/
private String[] header;
/**
* 表格数据
*/
private List<String[]> data;
}
相关操作代码
添加依赖
<!--Apache poi操作Excel start-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--时间格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.6</version>
</dependency>
<!--Apache poi操作Excel end-->
编写util
public class ExcelUtil {
// HSSFWorkbook导出 .xls方式
private Workbook wb = null;
// XSSF方式导出.xlsx方式
private XSSFWorkbook xwb = null;
// SXSSF方式导出.xlsx方式
private SXSSFWorkbook swb = null;
private Sheet sheet = null;
private Row row = null;
private Cell cell = null;
// 样式的设置
private CellStyle style = null;
private Font font = null;
private InputStream inputStream = null;
// excel的格式 默认xlsx
public static String type = Constants.EXCEL_TYPE_XLSX;
public ExcelUtil(Bsheet bsheet) {
switch (type) {
case Constants.EXCEL_TYPE_XLS:
// 最多只支持65536条数据导出，超过这个条数会报错
wb = new HSSFWorkbook();
sheet = wb.createSheet();
style = wb.createCellStyle();
font = wb.createFont();
break;
case Constants.EXCEL_TYPE_XLSX:
// XSSF方式支持大批量数据导出，所有的数据先写入内存再导出，容易出现内存溢出
xwb = new XSSFWorkbook();
sheet = xwb.createSheet();
style = xwb.createCellStyle();
font = xwb.createFont();
break;
case Constants.EXCEL_TYPE_SXLSX:
// XSSF方式支持大批量数据导出，所有的数据先写入内存再导出，容易出现内存溢出
swb = new SXSSFWorkbook();
sheet = swb.createSheet();
style = swb.createCellStyle();
font = swb.createFont();
break;
default:
xwb = new XSSFWorkbook();
sheet = xwb.createSheet();
style = xwb.createCellStyle();
font = xwb.createFont();
}
}
/*-----------------------------------------------------------导出excel-----------------------------------------------------------*/
/**
* 创建Excel
*
* @param request
* @param response
* @param bsheet
*/
public void createExcel(HttpServletRequest request, HttpServletResponse response, Bsheet bsheet) {
String fileName = getFileName(request, bsheet.getFileName());
// 是否合并第一行
if (bsheet.getIsMerge()) {
row = sheet.createRow(0);
cell = row.createCell(0);
if (StringUtils.isNotBlank(bsheet.getFirstMergeName())) {
cell.setCellValue(bsheet.getFirstMergeName());
}
// 起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, bsheet.getEndCell()));
}
// 设置头数据
setHeader(bsheet.getHeader(), bsheet.getIsMerge());
// 设置表数据(默认设置header)
addRow(bsheet.getData(), bsheet.getEndCell());
try {
response.reset();
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
ServletOutputStream sos = response.getOutputStream();
switch (type) {
case Constants.EXCEL_TYPE_XLS:
wb.write(sos);
break;
case Constants.EXCEL_TYPE_XLSX:
xwb.write(sos);
break;
case Constants.EXCEL_TYPE_SXLSX:
swb.write(sos);
break;
default:
xwb.write(sos);
}
sos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取文件名称
*
* @param request
* @param fileName
* @return
*/
private String getFileName(HttpServletRequest request, String fileName) {
fileName = fileName.replace(" ", "");
String userAgent = request.getHeader("user-agent").toLowerCase();
if (StringUtils.isBlank(fileName)) {
fileName = System.currentTimeMillis() + "";
}
fileName = fileName + (type.equals("xls") ? ".xls" : ".xlsx");
if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
} else {
try {
fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
return fileName;
}
/**
* 设置头部数据
*
* @param headers
* @param isMerge
*/
private void setHeader(String[] headers, Boolean isMerge) {
row = sheet.createRow(0);
// 获取样式
CellStyle style = getStyle();
if (isMerge) {
row = sheet.createRow(1);
}
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
}
/**
* 添加数据
*
* @param dataList
* @param endCell
*/
private void addRow(List<String[]> dataList, int endCell) {
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < endCell; j++) {
cell = row.createCell(j);
cell.setCellValue(dataList.get(i)[j]);
}
}
}
/**
* 获取行样式
*
* @return
*/
private CellStyle getStyle() {
// 设置样式
style.setAlignment(HorizontalAlignment.CENTER); // 居中
// 自动换行
style.setWrapText(true);
// 设置字体
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
style.setFont(font);
return style;
}
/*-----------------------------------------------------------读取excel-----------------------------------------------------------*/
public ExcelUtil(InputStream inputStream) {
this.inputStream = inputStream;
// 实例化操作
initWorkBook();
}
/**
* 获取workBook实例
*/
public void initWorkBook() {
try {
switch (type) {
// HSSF
case Constants.EXCEL_TYPE_XLS:
wb = new HSSFWorkbook(inputStream);
sheet = wb.getSheetAt(0);
break;
case Constants.EXCEL_TYPE_XLSX:
// XSSF
wb = new XSSFWorkbook(inputStream);
sheet = wb.getSheetAt(0);
break;
case Constants.EXCEL_TYPE_SXLSX:
wb = new SXSSFWorkbook();
sheet = wb.getSheetAt(0);
break;
default:
wb = new XSSFWorkbook(inputStream);
sheet = wb.getSheetAt(0);
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 根据流读取excel数据
*/
public String readExcel(int rowNum, int cellNum) {
if (null == inputStream) {
throw new RuntimeException("inputStream is not null");
}
row = sheet.getRow(rowNum);
cell = row.getCell(cellNum);
String value = getValue(cell);
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
return value;
}
/**
* 获取总行数
*
* @return
*/
public int readRowCountNum() {
return sheet.getLastRowNum();
}
/**
* 获取总列数
*
* @param rowNum
* @return
*/
public int readCellCount(int rowNum) {
row = sheet.getRow(rowNum);
return row.getLastCellNum();
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
public static String getValue(Cell cell) {
String cellValue = "";
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
switch (cellType) {
case STRING: //字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空
break;
case NUMERIC: //数字（日期、普通数字）
if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式，防止数字过长
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
case ERROR:
break;
default:
}
}
return cellValue;
}
}
编写测试方法
/**
* 导出excel
*
* @param fileName
* @param request
* @param response
*/
@GetMapping("excel/export")
public void exportTest(
@RequestParam String fileName,
HttpServletRequest request,
HttpServletResponse response
) {
String[] headers = {"姓名", "年龄", "手机号", "余额"};
ArrayList<String[]> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
dataList.add(new String[]{
"f1" + i,
i + 1 + "",
"1566495158" + i,
"1585869" + i
});
}
Bsheet bsheet = new Bsheet();
bsheet.setFileName(fileName);
bsheet.setHeader(headers);
bsheet.setEndCell(headers.length);
bsheet.setData(dataList);
// 设置导出方式
ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
ExcelUtil excelUtil = new ExcelUtil(bsheet);
excelUtil.createExcel(request, response, bsheet);
}
/**
* 以路径方式读取
*
* @throws FileNotFoundException
*/
@GetMapping(value = "excel/read")
public void readExcelForPathTest() throws FileNotFoundException {
// 设置读取方式
ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
String path = "D:\\file\\excel\\22222.xlsx";
FileInputStream fileInputStream = new FileInputStream(path);
ExcelUtil excelUtil = new ExcelUtil(fileInputStream);
int row = excelUtil.readRowCountNum();
int cell = excelUtil.readCellCount(row);
for (int j = 0; j < row; j++) {
for (int k = 0; k < cell; k++) {
String s = excelUtil.readExcel(j, k);
System.out.println("读取到第" + (j + 1) + "行," + (k + 1) + "列的值=" + s);
}
}
}
/**
* 以文件方式读取
*
* @param file
* @throws IOException
*/
@PostMapping(value = "excel/read", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public void readExcelForStreamTest(@RequestParam("file") MultipartFile file) throws IOException {
InputStream inputStream = file.getInputStream();
String filename = file.getOriginalFilename();
// 设置读取方式
if (filename.contains(".xlsx")) {
ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
} else if (filename.contains(".xls")) {
ExcelUtil.type = Constants.EXCEL_TYPE_XLS;
} else {
ExcelUtil.type = Constants.EXCEL_TYPE_XLSX;
}
ExcelUtil excelUtil = new ExcelUtil(inputStream);
int row = excelUtil.readRowCountNum();
int cell = excelUtil.readCellCount(row);
for (int j = 0; j < row; j++) {
for (int k = 0; k < cell; k++) {
String s = excelUtil.readExcel(j, k);
System.out.println("读取到第" + (j + 1) + "行," + (k + 1) + "列的值=" + s);
}
}
}
EasyPoi
- 简介： easypoi底层也是基于 apache poi 进行深度开发的，它主要的特点就是将更多重复的工作，全部简单化，避免编写重复的代码！
添加依赖
<!--Easy poi操作Excel start-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>com.googlecode.json-simple</groupId>
<artifactId>json-simple</artifactId>
<version>1.1.1</version>
</dependency>
<!--Easy poi操作Excel end-->
相关操作代码
基础model
public class UserExcel {
@Excel(name = "昵称")
private String nickName;
@Excel(name = "真实姓名")
private String realName;
@Excel(name = "头像")
private String avatar;
@Excel(name = "性别")
private Integer sex;
@Excel(name = "类型")
private Integer type;
@Excel(name = "手机号")
private String phone;
@Excel(name = "创建时间")
private Date createTime;
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getAvatar() {
return avatar;
}
public void setAvatar(String avatar) {
this.avatar = avatar;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
编写测试方法
public class EasyPoiExcelUtil {
/**
* excel导出操作
*/
@Test
public void exportExcel() throws IOException {
List<UserExcel> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserExcel userEntity = new UserExcel();
userEntity.setNickName("张三" + i);
userEntity.setAvatar("头像" + i);
userEntity.setSex(i % 2 == 0 ? 1 : i % 2);
userEntity.setAvatar("头像" + i);
userEntity.setRealName("真实姓名" + i);
userEntity.setPhone("1599485698" + i);
userEntity.setCreateTime(new Date(System.currentTimeMillis() + i));
dataList.add(userEntity);
}
//生成excel文档
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户", "用户信息"),
UserExcel.class, dataList);
FileOutputStream fos = new FileOutputStream("D:\\file\\excel\\easypoi-user.xls");
workbook.write(fos);
fos.close();
}
@Test
public void exportDefinitionExcel() throws IOException {
//封装表头
List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
entityList.add(new ExcelExportEntity("姓名", "name"));
entityList.add(new ExcelExportEntity("年龄", "age"));
ExcelExportEntity entityTime = new ExcelExportEntity("操作时间", "time");
entityTime.setFormat("yyyy-MM-dd HH:mm:ss");
entityTime.setWidth(20.0);
entityList.add(entityTime);
//封装数据体
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> userEntityMap = new HashMap<>();
userEntityMap.put("name", "张三" + i);
userEntityMap.put("age", 20 + i);
userEntityMap.put("time", new Date(System.currentTimeMillis() + i));
dataList.add(userEntityMap);
}
//生成excel文档
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("学生","用户信息"), entityList, dataList);
FileOutputStream fos = new FileOutputStream("D:\\file\\excel\\easypoi-definition.xls");
workbook.write(fos);
fos.close();
}
/**
* 导入excel操作
*/
@Test
public void importExcel() {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = System.currentTimeMillis();
List<UserExcel> list = ExcelImportUtil.importExcel(new File("D:\\file\\excel\\easypoi-user1.xls"), UserExcel.class, params);
System.out.println("耗时" + (System.currentTimeMillis() - start) + "秒");
System.out.println(JSONArray.toJSONString(list));
}
}
EasyExcel
- 简介：easyexcel 是阿里巴巴开源的一款 excel 解析工具，底层逻辑也是基于 apache poi 进行二次开发的。不同的是，再读写数据的时候，采用 sax 模式一行一行解析，在并发量很大的情况下，依然能稳定运行
相关依赖
<!-- EasyExcel 操作Excel start -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!--常用工具库-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>29.0-jre</version>
</dependency>
<!-- EasyExcel 操作Excel end -->
相关操作代码
相关model
public class UserEasyExcel {
@ExcelProperty(value = "昵称")
private String nickName;
@ExcelProperty(value = "真实姓名")
private String realName;
@ExcelProperty(value = "头像")
private String avatar;
@ExcelProperty(value = "性别")
private Integer sex;
@ExcelProperty(value = "类型")
private Integer type;
@ExcelProperty(value = "手机号")
private String phone;
@ExcelProperty(value = "创建时间")
private Date createTime;
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getAvatar() {
return avatar;
}
public void setAvatar(String avatar) {
this.avatar = avatar;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
编写测试方法
public class EasyExcelUtil {
@Test
public void exportExcel(){
List<UserEasyExcel> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserEasyExcel userEntity = new UserEasyExcel();
userEntity.setNickName("张三" + i);
userEntity.setAvatar("头像" + i);
userEntity.setSex(i % 2 == 0 ? 1 : i % 2);
userEntity.setAvatar("头像" + i);
userEntity.setRealName("真实姓名" + i);
userEntity.setPhone("1599485698" + i);
userEntity.setCreateTime(new Date(System.currentTimeMillis() + i));
dataList.add(userEntity);
}
EasyExcel.write("D:\\file\\excel\\easyexcel-user1.xls", UserEasyExcel.class).sheet("用户信息").doWrite(dataList);
}
@Test
public void exportDefinitionExcel(){
//表头
List<List<String>> headList = new ArrayList<>();
headList.add(Lists.newArrayList("姓名"));
headList.add(Lists.newArrayList("年龄"));
headList.add(Lists.newArrayList("操作时间"));
//数据体
List<List<Object>> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<>();
data.add("张三" + i);
data.add(20 + i);
data.add(new Date(System.currentTimeMillis() + i));
dataList.add(data);
}
EasyExcel.write("D:\\file\\excel\\easyexcel-user2.xls").head(headList).sheet("用户信息").doWrite(dataList);
}
@Test
public void importExcel(){
String filePath = "D:\\file\\excel\\easyexcel-user1.xls";
List<UserEasyExcel> list = EasyExcel.read(filePath).head(UserEasyExcel.class).sheet().doReadSync();
System.out.println(JSONArray.toJSONString(list));
}
}
