最近项目用到 excel导入导出功能,找了很多插件,最后选择了jxls2 。相比jxls1有很大改变
官方文档:http://jxls.sourceforge.net/getting_started.html
maven依赖:
注意其poi版本为4.0
<!--excel处理相关 start-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.7</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.16</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.7</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.5</version>
</dependency>
<!--excel处理相关 end-->
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.jxls.common.Context;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReader;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.SAXException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class JxlsUtil {
private static final Logger logger = LoggerFactory.getLogger(JxlsUtil.class);
public static void exportExcel(OutputStream outputStream,
String templateFilePath,
Map<String, Object> model) {
Context context = new Context();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
} else {
//你的处理方式
}
try {
ClassPathResource classPathResource = new ClassPathResource(templateFilePath);
try (InputStream is = classPathResource.getInputStream()) {
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
jxlsHelper.setUseFastFormulaProcessor(false);
jxlsHelper.processTemplate(is, outputStream, context);
}
} catch (Exception e) {
throw new CustomizeException(e.getMessage());
}
}
/**
* excel读取
*
* @param file 文件流
* @param beans 需处理对象
* @param xmlPath xml映射文件路径
* @return 返回传入的bean
*/
public static void importExcel(MultipartFile file,
Map<String, Object> beans,
String xmlPath) {
try {
ClassPathResource classPathResource = new ClassPathResource(xmlPath);
try (InputStream xmlInputStream = classPathResource.getInputStream()) {
XLSReader reader = ReaderBuilder.buildFromXML(xmlInputStream);
try (InputStream xlsInputStream = file.getInputStream()) {
reader.read(xlsInputStream, beans);
}
}
} catch (IOException | SAXException | InvalidFormatException e) {
throw new CustomizeException("处理失败,请检查上传文件格式");
}
}
/**
* list分批
* @param batchCount 分批数
* @param list 传入list
* @param isNewList 是否返回新的list子对象(不使用引用)
* @param <T> 类型
* @return 子list集合
*/
public static <T> List<List<T>> listUtil(Integer batchCount, List<T> list, Boolean isNewList) {
List<List<T>> lists = new ArrayList<>();
if (list.size() < batchCount) {
logger.info("===> 设定分批量大于集合数据量,设定分批量:{},集合数量:{}",batchCount,list.size());
lists.add(list);
} else {
int size = list.size();
int count = (size + batchCount - 1) / batchCount;
logger.info("===>指定分批大小:{}, 集合大小:{},分批数:{}",batchCount,list.size(),count);
for (int i = 0; i < count; i++) {
List<T> subList = list.subList(i * batchCount, ((i + 1) * batchCount > size ? size : batchCount * (i + 1)));
if (isNewList) {
lists.add(new ArrayList<T>(subList));
} else {
lists.add(subList);
}
}
}
return lists;
}
}