1.前言
现在提供了很多方便的框架操作excel进行导入导出。
框架的底层还是对poi框架进行了封装。
2.项目环境
前端使用的是layui框架,后台是springboot框架,部署在阿里云linux服务器上。
3.maven依赖
导入maven 依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-math3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.6.1</version>
</dependency>
4. 代码演示
1.首先创建工作簿,相当于整体excel文件,是整个文件的骨架
private HSSFWorkbook workbook = new HSSFWorkbook();
2.再通过workbook 对象 创建sheet对象
private HSSFSheet sheet = workbook.createSheet();
对应于excel表格的工作空间
3.接下来就到数据单元的绘制
可以分为两个部分:
表头:数据是固定的
列数据:是动态数据
每个数据都由一个单元格组成,一行可以有多个单元格
3.1.创建表头
private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};
.....省略
//===========表头操作=====================
for (int i = 0; i < HEAD_NAME.length; i++) {
//设置宽度
sheet.setColumnWidth(i,4000);
if (i == 0) {
//创建一行表头
headRow = sheet.createRow(i);
headRow.setHeight((short) 800);
}
//创建单元格数据
HSSFCell cell = headRow.createCell(i);
//给单元格赋值
cell.setCellValue(HEAD_NAME[i]);
}
3.2 绘制数据
//===========数据操作=====================
for (int i = 0; i < appointment.size(); i++) {
//从i+1行开始绘制
HSSFRow row = sheet.createRow(i+1);
//jackson反序列化
Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);
//为每个数据创建一个单元格
HSSFCell cell = row.createCell(0);
cell.setCellValue(value.getAId());
HSSFCell cell2 = row.createCell(1);
cell2.setCellValue(value.getName());
HSSFCell cell3 = row.createCell(2);
cell3.setCellValue(value.getCompany());
HSSFCell cell4 = row.createCell(3);
cell4.setCellValue(value.getPost());
HSSFCell cell5 = row.createCell(4);
cell5.setCellValue(value.getPhone());
HSSFCell cell6 = row.createCell(5);
cell6.setCellValue(value.getEmail());
HSSFCell cell7 = row.createCell(6);
cell7.setCellValue(value.getMessage());
}
5.整体代码
controller层代码
@PostMapping("/export")
public Object export(@RequestParam String appointment){
HashMap result = new HashMap();
try {
ObjectMapper mapper = new ObjectMapper();
List list = mapper.readValue(appointment,List.class);
String fileName = new ExcelUtil().export(list, mapper);
if (!fileName.isEmpty()) {
//必须将路径返回给前端,才能配合前端下载到本地
result.put("msg","excel/"+fileName);
result.put("code", 200);
result.put("desc", "导出成功");
return result;
}
} catch (Exception e) {
logger.error(e.toString());
}
result.put("code", 500);
result.put("desc", "导出失败");
return result;
}
业务层代码
package com.hniu.zs_manage.utils;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.hniu.zs_manage.entity.appointment.Appointment;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
/**
* @Description: excel 导出工具类
* @Author: songbiao
*/
@Component
public class ExcelUtil {
private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};
private static FileOutputStream FILE_OUTPUT_STREAM = null;
private static Logger logger = LogUtil.getLOG(ExcelUtil.class);
private static final File FILE_URL = new File("/cbim/ManagerSystem/excel");
private static String fileName = "";
private HSSFWorkbook workbook = new HSSFWorkbook();
private HSSFSheet sheet = workbook.createSheet();
private static HSSFFont headFont = null;
private static HSSFCellStyle headStyle = null;
private static HSSFFont dataFont = null;
private static HSSFCellStyle dataStyle = null;
private static HSSFRow headRow = null;
public ExcelUtil() {
//头字体样式
HSSFFont headFont = workbook.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 18);
//列头
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setFont(headFont);
// headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
//新版本FillPatternType
headStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//数字字体样式
HSSFFont dataFont = workbook.createFont();
dataFont.setFontName("宋体");
dataFont.setFontHeightInPoints((short) 16);
//数据样式
HSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setFont(dataFont);
dataStyle.setAlignment(HorizontalAlignment.LEFT);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//===========表头操作=====================
for (int i = 0; i < HEAD_NAME.length; i++) {
//设置宽度
sheet.setColumnWidth(i,4000);
if (i == 0) {
//创建一行表头
headRow = sheet.createRow(i);
headRow.setHeight((short) 800);
}
//创建单元格数据
HSSFCell cell = headRow.createCell(i);
//给单元格赋值
cell.setCellValue(HEAD_NAME[i]);
}
}
public String export(List appointment,ObjectMapper objectMapper) throws IOException {
FILE_OUTPUT_STREAM = new FileOutputStream(fileCheck(FILE_URL));
//===========数据操作=====================
for (int i = 0; i < appointment.size(); i++) {
HSSFRow row = sheet.createRow(i+1);
//为每个数据创建一个单元格
Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(dataStyle);
cell.setCellValue(value.getAId());
HSSFCell cell2 = row.createCell(1);
cell2.setCellStyle(dataStyle);
cell2.setCellValue(value.getName());
HSSFCell cell3 = row.createCell(2);
cell3.setCellStyle(dataStyle);
cell3.setCellValue(value.getCompany());
HSSFCell cell4 = row.createCell(3);
cell4.setCellStyle(dataStyle);
cell4.setCellValue(value.getPost());
HSSFCell cell5 = row.createCell(4);
cell5.setCellStyle(dataStyle);
cell5.setCellValue(value.getPhone());
HSSFCell cell6 = row.createCell(5);
cell6.setCellStyle(dataStyle);
cell6.setCellValue(value.getEmail());
HSSFCell cell7 = row.createCell(6);
cell7.setCellStyle(dataStyle);
cell7.setCellValue(value.getMessage());
}
workbook.write(FILE_OUTPUT_STREAM);
FILE_OUTPUT_STREAM.flush();
FILE_OUTPUT_STREAM.close();
workbook.close();
logger.debug("文件流读写关闭成功....");
return fileName;
}
public static File fileCheck(File file) throws IOException {
if (!file.getPath().endsWith(".xls")) {
if (!file.exists()) {
file.mkdirs();
}
fileName = "cbim_" + System.currentTimeMillis() + ".xls";
file = new File(file.getPath(), fileName);
file.createNewFile();
} else if (!file.exists()) {
//是文件且路径不对时直接返回异常信息
throw new IllegalStateException("路径异常");
}
return file;
}
}
6. 导出到本地配置
可以通过response流将文件返回给客户端,也可以用nginx搭建文件服务器。
将文件写在服务器上,通过nginx 搭建文件服务器来实现下载到本地
6.1nginx配置
在nginx.conf下配置(找到自己对应的nginx配置文件)
location /myfiles {
alias /export/share/test/; # 文件存放目录,注意要以 '/' 结尾;
autoindex on; # 自动列出目录下的文件;
autoindex_exact_size off; # 文件大小按 G、M 的格式显示,而不是 Bytes;
}
配置成功如下: 点击文件即可下载,或者通过http请求在myfiles后加上该文件名
6.2前端代码
发送ajax请求controller得到的文件路径,再进行http请求
success:function (res) {
if (res.code == 200) {
//下载到本地
window.location.href(res.msg);
}
alert(res.desc);
layer.close(layer.index);
}
最终结果演示: