EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
需求前提
业务需要做一个导入导出功能，可以实现数据的导入导出功能，且不能固定导入导出模板，所以采用了不创建对象的读、写方式。
数据库表的字段和字段名称都有关系表做存储。能知道每个表格导出时需要查询的字段和列名，以及每次导入时需要拼接的sql 添加语句。
通过以下四张表就能动态获取数据库中所有字段的数据关系，以及系统中所有列表页面显示的字段，而根据调整列表页面显示字段来实现一个可以灵活调整的导出功能。
pom.xml
在pom.xml中加入 com.alibaba.easyexcel 的依赖
<!-- alibaba easyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.3</version>
</dependency>
<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>
<!-- alibaba easyExcel -->
导出示例：
1.获取需要导出的列表和列表数据
2.解析列头数据和列表数据
目前需要导出的列表数据。
参考官方示例 不创建对象的写
/**
* 不创建对象的写
*/
@Test
public void noModelWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "noModelWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
}
private List<List<String>> head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("字符串" + System.currentTimeMillis());
List<String> head1 = new ArrayList<String>();
head1.add("数字" + System.currentTimeMillis());
List<String> head2 = new ArrayList<String>();
head2.add("日期" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
list.add(head2);
return list;
}
private List<List<Object>> dataList() {
List<List<Object>> list = new ArrayList<List<Object>>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<Object>();
data.add("字符串" + i);
data.add(new Date());
data.add(0.56);
list.add(data);
}
return list;
}
通过逻辑处理获得这个列表的数据，以及显示的字段集合。
ExportController .java
public class ExportController {
EasyExcelUtils.downloadFailedUsingJson(response,listLayoutFieldList,list,listLayout.getName());
}
EasyExcelUtils.java
package com.ac.hdx.base.custom.importexport;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import com.ac.hdx.base.custom.layout.ListLayoutField;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
public class EasyExcelUtils {
/**
*
* @param response
* @param listLayoutFieldList 显示的字段名,excel的列头
* @param list 数据内容
* @param fileName 导出的文件名
* @throws IOException
*/
public static void downloadFailedUsingJson(HttpServletResponse response,
List<ListLayoutField> listLayoutFieldList,List<Map<String, Object>> list,String fileName) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream()).head(head(listLayoutFieldList))
.autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(dataList(list));
}
/**
* 通过解析显示字段集合得到列头
* @param listLayoutFieldList
* @return
*/
private static List<List<String>> head(List<ListLayoutField> listLayoutFieldList) {
List<List<String>> list = new ArrayList<List<String>>();
List<String> id = new ArrayList<String>();
id.add("ID");
list.add(id);
//上面两行是为了导出数据ID，不需要的可以不写
//下面是解析字段集合获取列头
for (ListLayoutField layoutField : listLayoutFieldList) {
List<String> head = new ArrayList<String>();
head.add(layoutField.getInchinese());
list.add(head);
}
return list;
}
/**
* 通过解析所有数据将数据写入excel中，有特殊业务处理也可在这里进行
* @param listLayoutFieldList
* @return
*/
private static List<List<Object>> dataList(List<Map<String, Object>> list) {
List<List<Object>> excellist = new ArrayList<List<Object>>();
for (Map<String, Object> map : list) {
List<Object> data = new ArrayList<Object>();
for (String e:map.keySet()) {
if(!("isdelete").equals(e)) {
data.add(map.get(e));
}
}
excellist.add(data);
}
return excellist;
}
}
以上就实现了不创建对象写功能，实现了通过的数据导出功能。
导入示例：
- 实体表进行导入，将表中所有的数据库字段显示在。
- 选择对应的excel文件，使用前端js工具类获取excel的列头。(我使用的是 sheetjs)
- 让用户来将列头与数据库字段关系绑定
- 在上传文件到后台执行导入方法时，将列头与数据库字段的对应对应关系一起提交，用做数据解析。
import_choose_file.html
示例只能获取第一个sheet页面的内容
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>导入通用页面</title>
<link th:href="@{/static/styles/import.css}" rel="stylesheet" >
<script th:src="@{/static/script/xlsx.full.min.js}"></script>
</head>
<body class="gray-bg">
<div class="ibox-content" style="height:800px;">
<div class="import-w" style="width:100%;">
<div class="import-header">选择导入的数据源文件</div>
<div class="import-con">
<div class="import-warning">
<p class="import-txt">1.你可以把excel(*.xls),excel(*.xlsx)格式的文件导入系统 </p>
<p class="import-txt">2.请确认文件格式,所选文件格式与文件的格式要相符 </p>
<p class="import-txt">3.选择数据列名与系统字段名的匹配关系</p>
<p class="import-txt">4.如果需要导入数据量过于庞大且数据自身关系复杂时,请拆分多表格分批导入(建议超过3W数据时分批导入)</p>
</div>
<form th:action="@{/import/importData}" id="exportform"
method="post" enctype="multipart/form-data">
<input name="propertyMap" type="hidden" value=""/>
<input th:value="${entityId}" name="entityId" type="hidden"/>
<table class="table-base" style="width:90%;margin:0 auto;">
<tr>
<td class="table-label-title" style="width:20%;">指定导入文件的格式：</td>
<td class="table-label-edit" style="width:80%;">
<select name="fileType" class="tb-select">
<option value="xls">Excel(*.xls)</option>
<option value="xlsx">Exce(*.xlsx)</option>
</select>
</td>
</tr>
<tr>
<td class="table-label-title"></td>
<td class="table-label-edit">
<input name="file" type="file" class="tb-input"
onchange="importFile(this)" accept=".xls" />
</td>
</tr>
</table>
</form>
</div>
</div>
<div class="import-w" style="width:100%;">
<div class="import-header">配置属性对应关系</div>
<table class="table-report">
<tr>
<th>系统属性</th>
<th>导入属性</th>
<th>系统属性</th>
<th>导入属性</th>
</tr>
<tr th:each="m : ${list}">
<td th:text="${m.name}"></td>
<td>
<select th:fieldid="${m.id}" th:dbname="${m.ename}" name="sheetSelect"></select>
</td>
</tr>
</table>
</div>
<div class="nav-button-wrap">
<a class="nav-button-body">
<span class="nav-button">
<span class="btn btn-w-m btn-primary" id="importData" title="确认数据关系匹配正确！" onclick="importData();">提交</span>
</span>
</a>
</div>
</div>
<script th:inline="javascript">
var jsonArr = "";
//使用sheetjs导入文件，获取文件列头，示例只能获取第一个sheet页面的内容
function importFile(obj) {//导入
if(validateMethod()){
if(!obj.files) {
return;
}
var f = obj.files[0];
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var wb = XLSX.read(data, {
type: 'binary' //以二进制的方式读取
});
var sheet0=wb.Sheets[wb.SheetNames[0]];//sheet0代表excel表格中的第一页
jsonArr=XLSX.utils.sheet_to_json(sheet0,{header:1,blankrows:false});//利用接口实现转换。
if(jsonArr.length > 30000){
alert('单表数据超过30000条，请分多次导入！');
return;
}
//获取表头 jsonArr[0] 拼接到下拉框中
$("[name=sheetSelect]").empty();
var optionNull = '<option value="空">空</option>'
$("[name=sheetSelect]").append(optionNull);
var colIndex = 0;
for (var col in jsonArr[0]) {
var option = '<option value='+col+'>'+jsonArr[0][col]+'</option>';
colIndex++;
$("[name=sheetSelect]").append(option);
}
}
reader.readAsBinaryString(f);
}
}
//提交数据
function importData(){
if(validateMethod()){
getSelectAttribute();
}
}
//获取数据属性绑定,传递至后台进行解析
function getSelectAttribute(){
var propertyMap = '{';
$("[name='sheetSelect']").each(function(){
if(this.value != '空'){
propertyMap+= '"'+$(this).attr('dbname')+'":"'+this.value+'",';
}
});
propertyMap = propertyMap.substring(0,propertyMap.length-1)+'}';
$("[name='propertyMap']").val(propertyMap);
$("#importData").hide();
$("#exportform").ajaxForm(function(data){
processJsonResponse(data);
}).submit();
}
//验证文件类型
function validateMethod(){
var fileType = $("select[name='fileType']").val();
var path = $("input[name='file']").val();
var attr = path.substring(path.lastIndexOf(".")+1);
//判断文件选择类型是否相同
if(path==''){
alert("文件不能为空，请选择文件!","warning");
return false;
}else{
if(attr != fileType){
alert("文件类型错误，请重新选择!","warning");
return false;
}
}
return true;
}
//选择文件类型时，过滤文件选择框的默认文件格式
$("select[name='fileType']").change(function(){
var fileType = $("select[name='fileType']").val();
$("input[name='file']").attr("accept","."+fileType);
});
</script>
</body>
</html>
参考官方的 不创建对象的读
/**
* 直接用map接收数据
*
* @author Jiaju Zhuang
*/
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(NoModelDataListener.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
LOGGER.info("存储数据库成功！");
}
}
/**
* 不创建对象的读
*/
@Test
public void noModelRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里 只要，然后读取第一个sheet 同步读取会自动finish
EasyExcel.read(fileName, new NoModelDataListener()).sheet().doRead();
}
ImportController.java
package com.ac.hdx.base.custom.importexport;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import com.ac.hdx.base.custom.role.AuthorizeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.ac.hdx.base.custom.entity.FieldRecordService;
import com.ac.hdx.base.custom.layout.ListLayout;
import com.ac.hdx.base.custom.layout.ListLayoutService;
import com.ac.hdx.base.custom.universal.UniversalService;
import com.ac.hdx.base.framework.utils.JsonResponse;
import com.alibaba.excel.EasyExcel;
/**
* 导入控制器
*
* @author liushao
* @date 2020-02-12
*/
@Controller
@RequestMapping(value = "/import")
public class ImportController {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
ListLayoutService listLayoutService;
@Autowired
private FieldRecordService fieldRecordService;
@Autowired
private ImportService importService;
@Autowired
private UniversalService universalService;
@Value("${base.txt.uri}")
private String txturi;
@Autowired
private AuthorizeService authorizeService;
/**
* 导入数据
*
* @throws Exception
*/
@RequestMapping(value = "importData", method = RequestMethod.POST)
@ResponseBody
public JsonResponse importData(HttpServletRequest request,String entityId, String propertyMap) throws Exception {
MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartHttpServletRequest.getFile("file");
EasyExcel.read(file.getInputStream(),new NoModleDataListener(entityId,propertyMap,jdbcTemplate,txturi,universalService)).sheet().doRead();
return JsonResponse.reload("导入成功");
}
}
NoModleDataListener.java
类中无法注入所以很多类都是实例化NoModleDataListener时传入的
package com.ac.hdx.base.custom.importexport;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import com.ac.hdx.base.custom.universal.UniversalService;
import com.ac.hdx.base.dylan.base.core.DBRule;
import com.ac.hdx.base.dylan.crud.create.IdGenerator;
import com.ac.hdx.base.dylan.crud.retrieve.sqlparse.DicMap;
import com.ac.hdx.base.framework.utils.FileUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
public class NoModleDataListener extends AnalysisEventListener<Map<Integer, String>> {
private JdbcTemplate jdbcTemplate;
private UniversalService universalService;
private String txturi;
//字段映射关系
public String propertyStr;
//主表
public String entityId;
//字段映射Map
public Map<String,String> propertyMap = null;
//字段IDMap
public Map<String,String> fieldIdMap = new HashMap<>();
//pk字段缓存
public Map<String,String> pkMap = new HashMap<String, String>();
public String importSql = "insert into t_";
public NoModleDataListener(String entityId,String propertyStr,
JdbcTemplate jdbcTemplate,String txturi,UniversalService universalService) {
this.propertyStr = propertyStr;
this.entityId = entityId;
this.jdbcTemplate = jdbcTemplate;
this.universalService = universalService;
this.txturi = txturi;
}
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<Map<Integer, String>> excelList = new ArrayList<Map<Integer, String>>();
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
excelList.add(data);
if (excelList.size() >= BATCH_COUNT) {
saveData();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
pkMap.clear();
}
/**
* 加上存储数据库
*/
private void saveData() {
try {
//如果字段映射Map为空，则获取映射关系，遍历字段映射拼接批处理语句
if( propertyMap == null) {
propertyMap = JSONObject.parseObject(propertyStr,Map.class);
importSql += entityId +" (";
for (Entry<String, String> a : propertyMap.entrySet()) {
importSql += a.getKey() +",";
}
importSql = importSql.substring(0,importSql.length()-1)+",id,create_datetime) VALUES (";
for (Entry<String, String> a : propertyMap.entrySet()) {
importSql += "?,";
}
importSql = importSql.substring(0,importSql.length()-1)+",?,now())";
}
jdbcTemplate.batchUpdate(importSql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
// TODO Auto-generated method stub
int sqlIndex = 1;
//生成Id
String id = IdGenerator.generator(Integer.parseInt(entityId));
/**
* 遍历字段，处理字段类型，依照每个类型的不同进行单独的业务处理
* 如果是pk引用字段则获取列中文字在数据库中对应的ID值，并缓存对应关系
* 如果是下拉字段，则从数据字段缓存中获取对应的ID值
* 如果是文本域字段，则将数据内容写入到文件中,将文件名写入数据库
*/
for (Entry<String, String> a : propertyMap.entrySet()) {
String fieldName = excelList.get(i).get(Integer.parseInt(a.getValue()));
if(fieldName != null) {
if(a.getKey().startsWith("pk_t")) {//PK字段
String tableName = DBRule.getTableNameByPkField(a.getKey());
String pkMapKey = tableName+a.getKey()+fieldName;
if(pkMap.get(pkMapKey) ==null){
try {
String sql = "select id,name name from " + tableName +" where name = ?";
Map<String,Object> resertMap = jdbcTemplate.queryForMap(sql,fieldName);
pkMap.put(pkMapKey, resertMap.get("id").toString());
}catch (Exception e) {
// TODO: handle exception
pkMap.put(pkMapKey, "0");
}
}
if(!"0".equals(pkMap.get(pkMapKey))) {
ps.setObject(sqlIndex, pkMap.get(pkMapKey));
}else {
ps.setObject(sqlIndex, null);
}
}else if(a.getKey().endsWith("_select")) {//下拉字段
if(DicMap.getId(fieldName)!=null) {
ps.setObject(sqlIndex, DicMap.getId(fieldName));
}else {
ps.setObject(sqlIndex, null);
}
}else if(a.getKey().endsWith("_text")) {//文本域
String code = UUID.randomUUID().toString();
FileUtil.writerString2Uri(fieldName, txturi + "/" + code + ".txt");
Integer fieldId = 0;
if(fieldIdMap.get("t_"+entityId+"_"+a.getKey()+"_fieldName") !=null) {
fieldId = Integer.parseInt(fieldIdMap.get("t_"+entityId+"_"+a.getKey()+"_fieldName"));
}else {
fieldId = selectFiledId(entityId, a.getKey(), jdbcTemplate);
fieldIdMap.put("t_"+entityId+"_"+a.getKey()+"_fieldName", fieldId+"");
}
//插入富文本记录
universalService.textareaSave(Integer.parseInt(entityId), id, fieldId, code);
ps.setObject(sqlIndex, code);
}else {
ps.setObject(sqlIndex, fieldName);
}
}else {
ps.setObject(sqlIndex, null);
}
sqlIndex ++;
}
ps.setObject(sqlIndex, id);
}
@Override
public int getBatchSize() {
// TODO Auto-generated method stub
return excelList.size();
}
});
} catch (Exception e) {
System.out.println(e);
}finally {
//清空集合，回收内存
excelList.clear();
}
}
public Integer selectFiledId (String entityId,String fieldName,JdbcTemplate jdbcTemplate) {
String sql = "select id from s_field where name = ? and pk_s_entityrecord_1 = ?";
return jdbcTemplate.queryForObject(sql, Integer.class,fieldName,entityId);
}
}
NoModleDataListener.java中 saveData()方法主要是系统的具体业务逻辑处理，可根据自身不同做调整。
1834
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
