1、Easyexcel
在接到任务后,首先选择的是相对代码量比较少的Easyexcel工具,但是由于部分需求无法实现,所以后续选择了更换成其他工具包,以下展示的是Easyexcel作者在Github上写的几个小demo。(加技术交流群后,作者说后续应该会更新类似于jxls的模板导出功能)以下demo来自于--https://github.com/alibaba/easyexcel--
首先,我们需要创建一个data类,用于存储需要导出至excel的数据
当然,在这之前先将pom依赖添加到项目中,个人使用的时springboot,就不贴出所有pom了
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.0-beta5</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
此处的data注解来自于lombok插件以及maven依赖,具体如何配置在很多博客中都有,此处就不再贴出
/**
* 基础数据类
*
* @author Jiaju Zhuang
**/
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
}
/**
* 基础数据类
*
* @author Jiaju Zhuang
**/
@Data
public class IndexData {
@ExcelProperty(value = "字符串标题", index = 0)
private String string;
@ExcelProperty(value = "日期标题", index = 1)
private Date date;
/**
* 这里设置3 会导致第二列空的
*/
@ExcelProperty(value = "数字标题", index = 3)
private Double doubleData;
}
data类写入excel时常用的注解有以下几个
@ExcelProperty(value=这里写的是在excel中的列名,index=这里写的是在excel中列的索引,即第几列)
@ColumnWidth//该注解用于设置列宽,可以用在属性上也可以用在类上全局设置
@HeadRowHeight//该注解用于设置列的行高
@ContentRowHeight//该注解用于设置内容行的行高
接下来就是具体实现导出操作的代码了
/**
* 最简单的写
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 直接写即可
*/
@Test
public void simpleWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
// 写法2
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读
ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(data(), writeSheet);
/// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
}
作者在demo中提供了两种写法,其中写法二对所有版本支持(应该),但是写法一似乎只支持新版本,以上的demo是最简单的excel导出,在demo中存在的TestFileUtil工具类代码如下
import java.io.File;
import java.io.InputStream;
public class TestFileUtil {
public static InputStream getResourcesFileInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath() {
return TestFileUtil.class.getResource("/").getPath();
}
public static File createNewFile(String pathName) {
File file = new File(getPath() + pathName);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName) {
return new File(getPath() + pathName);
}
}
以下的demo使用的是写入到指定的列索引中
/**
* 指定写入的列
* <p>
* 1. 创建excel对应的实体对象 参照{@link IndexData}
* <p>
* 2. 使用{@link ExcelProperty}注解指定写入的列
* <p>
* 3. 直接写即可
*/
@Test
public void indexWrite() {
String fileName = TestFileUtil.getPath() + "indexWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data());
}
以上便是对Easyexcel的简单介绍,由于使用时无法满足需求,未找到如何复制sheet的功能,所以更换为jxls
2、Jxls
jxls是一个通过在excel中创建模板从而实现将数据写入到excle中,由于demo涉及到公司项目内容,此处不贴出完整的demo
jxls主要是通过模板来完成excel导出功能,此处需要先创建相应的模板,如下图所示
在添加模板时,需要注意几个要点,必须在excel头列处添加该批注,该批注的作用是让jxls判断该模板文件与你需要写入到表单中的哪个对象对应,items就是在程序中存储到map中的key值,var时遍历该map的时候使用的声明变量,lastcell代表的是模板的范围,此处需要注意,lastcell需要将模板全部包含起来,而不是模板结束的cell
jx:each(items="catalog" var="p" lastCell="J2")
以下这句用于在遍历行的时候确定行的结束位置,lastcell代表的是写入的行末尾
jx:each(items="catalog" var="p" lastCell="J2")
按照以上操作后,模板初始化便算是完成了,接下来便是在java中调用jxls类将需要写入到excel中的数据传入便可以实现excel导出了
首先添加jxls依赖
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.6</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.15</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.7</version>
</dependency>
然后创建一个类,调用jxls内置方法,JxlsUtils需要的参数包括一个模板路径,一个excel导出的输出流,外加一个map,map中的key值要和模板中的item名称对应,记住不要忘了关闭流,这样要给简单的模板导出便完成了
public void getExcel() {
try {
OutputStream os = new FileOutputStream(path);
List info = MapInfoInit.info;
Map<String, Object> model = new HashMap<String, Object>();
model.put("catalog", info);
JxlsUtils.exportExcel(new ClassPathResource(templatePath).getFile().getAbsolutePath(), os, model);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
JxlsUtils来源于某大佬根据klguang大佬写的工具类改编而来。。来源找不到了。。
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
/**
* @author klguang
*/
public class JxlsUtils{
public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{
Context context = PoiTransformer.createInitialContext();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
//设置静默模式,不报警告
//evaluator.getJexlEngine().setSilent(true);
//函数强制,自定义功能
Map<String, Object> funcs = new HashMap<String, Object>();
funcs.put("utils", new JxlsUtils()); //添加自定义功能
evaluator.getJexlEngine().setFunctions(funcs);
//必须要这个,否者表格函数统计会错乱
jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
}
public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
}
public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
File template = getTemplate(templatePath);
if(template != null){
exportExcel(new FileInputStream(template), os, model);
} else {
throw new Exception("Excel 模板未找到。");
}
}
//获取jxls模版文件
public static File getTemplate(String path){
File template = new File(path);
if(template.exists()){
return template;
}
return null;
}
// 日期格式化
public String dateFmt(Date date, String fmt) {
if (date == null) {
return "";
}
try {
SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
return dateFmt.format(date);
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
// if判断
public Object ifelse(boolean b, Object o1, Object o2) {
return b ? o1 : o2;
}
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
接下来是稍微复杂一些的模板导出,因为当时我需要的是多sheet导出,搜寻了大量资料并未发现如何操作,后来才发现只需要在需要导出内容的sheet中将模板格式正确输入,便可以实现多sheet导出,以下是模板示例
如上图所示为一个excel中的两个sheet ,模板内容只是在头列处多了如下代码,该行的作用是复制当前模板sheet,主要用于多个内容需要实现sheet分页,这个时候就需要在起始列加上这行代码
jx:each(items="drillInfos", var="info", lastCell="G7" multisheet="sheetNames")
要实现对以上示例模板的sheet复制,首先需要添加一个pojo类(仅适用于该模板,因为该sheet需要一个对象加一个list集合),用于存储sheet页的内容
import lombok.Data;
import java.util.List;
@Data
public class DrillInfo {
private String sheetName;
private List<DrillLayer> data;
private DrillBase drillBase;
}
接下来便是在代码中调用jxls方法,将处理好的数据导入到excel文件中,此处需要注意几个问题,sheetName存储到该数据对象中的用途是jxls获取sheetName是按照一个list集合获取,所以如果sheetName的顺序和需要导入该页sheet的数据内容不匹配便会导致内容错页,记住,一定要将sheetName存入到一个集合中并key一定要和模板中的multisheet后的值对应,否则不会复制sheet
public void getExcels() {
OutputStream os = null;
try {
os = new FileOutputStream(path);
Map<String, Object> model = new HashMap<String, Object>();
List<DrillInfo> drillInfos = MapInfoInit.drillInfos;
model.put("drillInfos", drillInfos);
model.put("sheetNames", MapInfoInit.sheetNames);
model.put("drillPaper", MapInfoInit.drillPaper);
JxlsUtils.exportExcel(new ClassPathResource(templatePaths).getFile().getAbsolutePath(), os, model);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
3、JXL
当然,jxls依然不满足我们的需求,主要原因是模板后期可能会频繁修改,所以jxls最终也被pas掉了,剩下的便是poi和jxl或者其他easy家族的工具,最终我还是选择了项目中已存在的jxl,以下便是jxl的使用心得
当然,其实我在使用jxl时也遇到不少问题,也许是我的使用姿势有问题,以下我会把遇到的问题已经我自己的解决办法列举出来,仅供参考
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
首先是导入maven依赖,最新版本时2.6.12,在使用这个版本的时候会出现一个空指针问题,原因是在判断对象是否为空后使用了&判断下一个条件。。所以若第一个判断对象为空依旧会去做第二个判断,导致空指针,所以选用了2.6.10版本
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
以下最简单的写入操作和复制sheet操作,jxl不需要对模板进行任何操作,所有的写入都是从java中操作模板的坐标实现的,jxl写入到行的时候使用的时LLable对象,该对象常用的构造器三个参数分别为列坐标,行坐标以及需要写入到单元格的内容
复制sheet时需要注意一个问题:sheet复制会把sheet中的所有内容复制到目标sheet,所以复制sheet操作我是在写入sheet之前进行操作的
public void getExcelTest() {
try {
// 创建只读的 Excel 工作薄的对象
Workbook rw = Workbook.getWorkbook(new File(templatePath));
// 创建可写入的 Excel 工作薄对象
WritableWorkbook wwb = Workbook.createWorkbook(new File(path), rw);
// 读取第一张工作表
WritableSheet ws = wwb.getSheet(0);
Label label = new Label(0, 1, "hhh");
ws.addCell(label);
// 复制一个新的sheet
wwb.copySheet(0, "newSheet", 1);
// 写入 Excel 对象
wwb.write();
// 关闭可写入的 Excel 对象
wwb.close();
// 关闭只读的 Excel 对象
rw.close();
} catch (IOException | BiffException | WriteException e) {
e.printStackTrace();
}
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
以上便是比较简单的写入操作,接下来介绍一下稍微复杂的写入,先上模板 ,这是我需要导出的excel模板中的三个sheet,jxl在操作多个sheet时,需要根据sheet的索引切换sheet
切换sheet用如下方法,参数是sheet的索引
// 读取第一张工作表
WritableSheet ws = wwb.getSheet(0);
切换sheet后,便是获取当前sheet的工作表坐标,一个一个向单元格写入内容,原本一切顺利却遇到了第一个问题
模板的样式会在new Label写入后,冲掉原有样式,我的解决办法是首先获取一下源单元格样式,但后在new Label时同时将样式作为参数穿给jxl,示例如下
// 获取原单元格样式
CellFormat cellFormat = ws.getCell(1, 0).getCellFormat();
Label pno = new Label(1, 0, drillPaper.getPno(), cellFormat);
解决了这个问题后,第二个问题接踵而至
以上是我的模板在执行sheet复制前的样式和执行sheet复制后的样式,很明显,右边多出来一部分边框样式,后来经过排查,发现原因是全选列设置样式造成的,具体应该是在设置的sheet样式时行数超出了excel最大行数,导致下面的行数样式挤到了上面
解决办法是只设置一行边框样式,在写入时如上所示,将样式与label一起写入到单元格中
接下来是第三个问题,由于存在一个需要根据数据容量判断多少列就在excel中写入多少列的需求,需要将整列的样式复制至下一列,此问题的解决办法如下
我是使用for循环先判断存在多少列,然后将列在写入前创建好,这个时候再写入内容不会冲掉使用方法设置好的样式
// 首先根据钻孔数量创建对应的数据列
for (int i = 0; i < baseSize; i++) {
DrillBase drillBase = drillBases.get(i);
// 目标单元格列坐标
int columnIndex = i + 1;
// 设置列样式
ws.setColumnView(columnIndex, ws.getColumnView(sourceColumnIndex));
// 获取单元格样式---getCell参数 列坐标,行坐标---
CellFormat cellFormat = ws.getCell(sourceColumnIndex, sourceRowIndex).getCellFormat();
// 设置列名和表头颜色
ws.addCell(new Label(columnIndex, rowIndex, drillBase.getInnerNo(), cellFormat));
}
然后便是比较常见的单元格合并,代码如下
// 合并单元格 参数--合并起始列坐标 起始行坐标 合并至列坐标 合并至行坐标
ws.mergeCells(0, startCellIndex, 0, endCellIndex);
然后便是另一个比较奇怪的问题,列样式可以复制但是行样式复制会不起任何效果,所以我最终只能一个一个将原有样式在写入内容时赋值给Label,方法和上面写入样式相同
接下来的这个问题貌似比较常见,excel中
如这种斜线分割的单元格无法将模板样式复制过来,解决办法比较蠢。。是用一张png矢量图盖在单元格上,实现代码如下
// 获取斜线图片
WritableImage image = new WritableImage(4, 0, 1, 1, new File(new ClassPathResource("templates/line.png").getFile().getAbsolutePath()));
// 将图片插入到表格中
ws.addImage(image);
接下来的这个问题也是样式问题,如这样用excel设置的上标表示平方的文本无法作为模板复制过来,最终我的解决办法是使用unicode码将列名写入到表格中,unicode码是在这个网站查询的--https://unicode-table.com/cn/--
ws.addCell(new Label(5, 0, "5*10\u207b\u2076", triaxialColumnFormat));