Easypoi模板导出
优点
- 快速导出:使用预定义的Excel模板,可以快速导出数据,特别是在数据量大的情况下。
- 简化代码:通过模板导出,减少了编写大量POI代码的需要,使得代码更简洁易懂。
- 灵活性:模板可以根据需求进行自定义,可以包含复杂的格式和公式。
缺点
- 依赖性强:EasyPOI模板导出依赖于Apache POI库,如果没有这个库,它将无法工作。
- 模板维护:模板文件需要被维护,如果模板更改,可能需要重新生成模板文件。
- 限制性:模板导出的结果受限于模板的定义,不能完全自定义Excel文件的所有特性。
- 开源版本存在bug,且维护者解决bug巨慢或者说不解决。
模板语法
-
空格分割
-
三目运算 {{test ? obj:obj2}}
-
n: 表示 这个cell是数值类型 {{n:}}
-
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
-
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
-
fn: 格式化数字 {{fn:(obj;###.00)}}
-
fe: 遍历数据,创建row
-
!fe: 遍历数据不创建row
-
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
-
#fe: 横向遍历
-
v_fe: 横向遍历值
-
!if: 删除当前列 {{!if:(test)}}
-
单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
-
&NULL& 空格
-
]] 换行符 多行遍历导出
-
sum: 统计数据
POM依赖
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version>
</dependency>
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.4.0</version>
</dependency>
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.4.0</version></dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.1</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.1</version>
</dependency>
案例
该demo包含的模板导出基本能覆盖项目中使用的场景。
//ExcelExport20220302.java
package easypoi;import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import jfreechart.ChartExample2;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;import java.io.FileOutputStream;
import java.util.*;/*** @Author 老千* @Date 2022-03-02 14:10:32*/
@Slf4j
public class ExcelExport20220302 {public static void main(String[] args) {// TemplateExportParams构造参数是模板uriTemplateExportParams params = new TemplateExportParams("E:\\template\\template01.xlsx");params.setColForEach(true);Workbook workbook = null;FileOutputStream outputStream = null;try {// 模板导出所属数据用mapMap<String, Object> map = new HashMap<>();List<Point> points = buildData();map.put("list", points); // 列表数据,用模板语法$fe遍历map.put("processName", "xx工序"); // 单个属性,也可以用对象比如:task,属性aField ,模板填充{{task.aField}}map.put("taskStartTime", new Date()); //日期数据,可用{{fd:(obj;yyyy-MM-dd)}}格式化map.put("code", "2401314520");// 图片字节数组,这里的字节数组是通过Jfreechart根据数据生成的byte[] imageBytes = ChartExample2.generateImage();List<ImageEntity> imageEntityList = new ArrayList<>();ImageEntity imageEntity = new ImageEntity();imageEntity.setRowspan(25);imageEntity.setColspan(15);imageEntity.setData(imageBytes);imageEntityList.add(imageEntity);map.put("imageList", imageEntityList);outputStream = new FileOutputStream("E:\\export\\111.xlsx"); // 导出文件位置// 模板构造workbook = ExcelExportUtil.exportExcel(params, map);workbook.write(outputStream);} catch (Exception e) {//日志中打印错误信息堆栈 e.printStackTrace();log.error("下载失败,详细错误内容:", e);// 抛出运行时异常 BaseException.build(错误信息枚举(ErrorCode, ErrorMsg))throw new RuntimeException("Excel导出失败");} finally {// 如果没有使用java1.8 的try,需要在finally中关闭IOUtils.closeQuietly(outputStream);IOUtils.closeQuietly(workbook);}}private static List<Point> buildData() {List<Point> list = new ArrayList<>();for (int i = 0; i < 10; i++) {String name = "点位" + (i + 1);list.add(new Point(name, "2022-03-02 14:00:01", "1"));}return list;}}@Data
@AllArgsConstructor
class PointAndData {private String name;private List<Point> data;
}@Data
@AllArgsConstructor
class Point {private String name;private String time;private String value;
}// ChartExample2.javapackage jfreechart;import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.*;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardXYItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.xy.StandardXYItemRenderer;
import org.jfree.chart.renderer.xy.XYItemRenderer;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.time.*;
import org.jfree.ui.RectangleInsets;
import org.jfree.ui.TextAnchor;
import util.DateUtil;import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** 生成时间序列图*/
public class ChartExample2 {public static byte[] generateImage() throws IOException {TimeSeriesCollection ds = new TimeSeriesCollection();TimeSeries timeSeries = new TimeSeries("电压档位");List<Point> points = parseDataFromExcel();points.forEach(e -> {timeSeries.add(new Minute(new Date(e.getTime().getTime())), e.getValue());});// 添加多个timeSeries则可以在一个图片中展示多个曲线,对比展示ds.addSeries(timeSeries);
// long currentTimeMillis = System.currentTimeMillis();
// for (int j = 0; j < 5; j++) {
// long temp = currentTimeMillis;
// TimeSeries timeSeries = new TimeSeries("电压档位" + j);
// for (int i = 0; i < 360; i++) {
// temp = temp + 60000;
// Date date = new Date(temp);
// timeSeries.add(new Minute(date), RandomUtils.nextInt(1, 15));
// }
// ds.addSeries(timeSeries);
// }JFreeChart chart = createTimeSeriesChart(ds, "Chart Title", "X Axis Label", "Y Axis Label");// 处理图像数据,比如存储为文件或通过网络发送出去等ChartUtilities.saveChartAsJPEG(new File("e:/pic/test.jpg"), chart, 1207, 500);//释放资源return generateChartImage(chart);}public static List<Point> parseDataFromExcel() {ExcelImportResult<Point> result = ExcelImportUtil.importExcelMore(new File("e:pic/test.xls"), Point.class, new ImportParams());return result.getList();}public static void main(String[] args) throws IOException {TimeSeriesCollection ds = new TimeSeriesCollection();TimeSeries timeSeries = new TimeSeries("电压档位");List<Point> points = parseDataFromExcel();points.forEach(e -> {timeSeries.add(new Millisecond(new Date(e.getTime().getTime())), e.getValue());});// 添加多个timeSeries则可以在一个图片中展示多个曲线,对比展示ds.addSeries(timeSeries);
// long currentTimeMillis = System.currentTimeMillis();
// for (int j = 0; j < 5; j++) {
// long temp = currentTimeMillis;
// TimeSeries timeSeries = new TimeSeries("电压档位" + j);
// for (int i = 0; i < 360; i++) {
// temp = temp + 60000;
// Date date = new Date(temp);
// timeSeries.add(new Minute(date), RandomUtils.nextInt(1, 15));
// }
// ds.addSeries(timeSeries);
// }JFreeChart chart = createTimeSeriesChart(ds, "Chart Title", "X Axis Label", "Y Axis Label");// 处理图像数据,比如存储为文件或通过网络发送出去等ChartUtilities.saveChartAsPNG(new File("e:/pic/test.jpg"), chart, 1207, 500);
// JFreeChart lineChart = createLineChart();
// // 处理图像数据,比如存储为文件或通过网络发送出去等
// ChartUtilities.saveChartAsPNG(new File("e:/pic/test_line.jpg"), lineChart, 1207, 500);//释放资源}private static JFreeChart createTimeSeriesChart(TimeSeriesCollection dataset, String chartTitle, String xAxisLabel, String yAxisLabel) throws UnsupportedEncodingException {JFreeChart chart = ChartFactory.createTimeSeriesChart("电压档位曲线",
// new String("时间".getBytes(), StandardCharsets.UTF_8),
// new String("档位".getBytes(), StandardCharsets.UTF_8),null, null,dataset,false, // 是否显示图片底部的线条说明false,true);// 设置时间轴的格式DateAxis axis = (DateAxis) chart.getXYPlot().getDomainAxis();SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
// new CustomDateFormat("yyyy-MM-dd HH:mm");axis.setDateFormatOverride(dateFormat);axis.setAutoTickUnitSelection(true); // 由于横轴标签过多,这里设置为自动格式
// axis.setVerticalTickLabels(true); // 设为true表示横坐标旋转到垂直。// 设置刻度标记的位置axis.setTickMarkPosition(DateTickMarkPosition.START);Font labelFont = new Font("隶书", Font.BOLD, 12);chart.setTitle(new TextTitle("电压档位曲线", new Font("隶书", Font.BOLD, 15)));chart.setBackgroundPaint(Color.white);chart.setAntiAlias(true);XYPlot plot = (XYPlot) chart.getPlot();plot.setAxisOffset(new RectangleInsets(0, 0, 0, 0));//图片区与坐标轴的距离plot.setInsets(new RectangleInsets(15, 30, 15, 15));//坐标轴与最外延的距离AxisSpace as = new AxisSpace();as.setLeft(25);as.setRight(25);plot.setFixedRangeAxisSpace(as);chart.setPadding(new RectangleInsets(5, 5, 5, 5));chart.setNotify(true);// 设置曲线显示各数据点的值XYItemRenderer xyitem = plot.getRenderer();xyitem.setBasePositiveItemLabelPosition(new ItemLabelPosition(ItemLabelAnchor.INSIDE10, TextAnchor.BASELINE_LEFT));xyitem.setBaseItemLabelGenerator(new StandardXYItemLabelGenerator());xyitem.setBaseItemLabelFont(new Font("Dialog", 1, 14));xyitem.setSeriesPaint(0, Color.RED); // 设置曲线颜色xyitem.setSeriesStroke(0, new BasicStroke(1.5f));plot.setRangeGridlinesVisible(true); // 设置y轴网格可见plot.setRangeGridlinePaint(Color.lightGray);plot.setRenderer(xyitem);plot.setBackgroundPaint(Color.white); // 设置背景颜色plot.getRangeAxis().setLabelFont(labelFont); // 设置Y轴标签展示字体,不设置的话中文会乱码plot.getRangeAxis().setTickLabelFont(labelFont);plot.getDomainAxis().setLabelFont(labelFont); // 设置X轴标签展示字体,不设置的话中文会乱码plot.getDomainAxis().setTickLabelFont(labelFont);return chart;}private static JFreeChart createLineChart() {DefaultCategoryDataset ds = new DefaultCategoryDataset();List<Point> points = parseDataFromExcel();List<Point> point = new ArrayList<Point>(){{add(points.get(0));add(points.get(1));add(points.get(2));add(points.get(3));add(points.get(4));add(points.get(5));}};point.forEach(e -> {ds.addValue(e.getValue(), "s1", DateUtil.dateFormat(e.getTime()));});JFreeChart chart = ChartFactory.createLineChart("", "", "", ds);CategoryAxis domainAxis = chart.getCategoryPlot().getDomainAxis();domainAxis.setCategoryLabelPositions(CategoryLabelPositions.UP_45);
// domainAxis.setCategoryLabelPositions(CategoryLabelPositions.DOWN_90);return chart;}private static byte[] generateChartImage(JFreeChart chart) throws IOException {ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();ChartUtilities.writeChartAsPNG(byteArrayOutputStream, chart, 1600, 900);return byteArrayOutputStream.toByteArray();}static class CustomDateFormat extends SimpleDateFormat {public CustomDateFormat(String pattern) {super(pattern);}@Overridepublic StringBuffer format(Date date, StringBuffer toAppendTo, java.text.FieldPosition pos) {// Format the date as neededStringBuffer label = super.format(date, toAppendTo, pos);// Insert line breakreturn new StringBuffer("<html>" + label.toString().replaceAll("\\s", "<br>") + "</html>");}}
}
模板
{{processName}}操作记录 | |||
---|---|---|---|
任务时间 | {{fd:(taskStartTime; yyyy年MM月dd日 HH时mm分)}} | 编码 | {{code}} |
点位数据 | |||
点位 | 时间 | 值 | |
{{$fe:list t.name | t.time | t.value}} | |
点位曲线 | |||
{{$fe:imageList t}} | |||
导出
注意事项:
-
easypoi开源版遍历列表数据时,如果存在合并单元格时,会导致某些行的合并单元格失效。
需要通过POI手动合并单元格
PoiMergeCellUtil.addMergedRegion
-
关于导出图片
通过ImageEntity进行导出
代码中 e:pic/test.xls 文件的数据(点位数据)
time | value |
---|---|
2023-04-09T23:20:00Z | 6 |
2023-04-09T23:21:00Z | 6 |
2023-04-09T23:22:00Z | 6 |
2023-04-09T23:23:00Z | 9 |
2023-04-09T23:24:00Z | 9 |
2023-04-09T23:25:00Z | 13 |
2023-04-09T23:26:00Z | 13 |
2023-04-09T23:27:00Z | 13 |
2023-04-09T23:28:00Z | 13 |
2023-04-09T23:29:00Z | 13 |
2023-04-09T23:30:00Z | 13 |
2023-04-09T23:31:00Z | 13 |
2023-04-09T23:32:00Z | 13 |
2023-04-09T23:33:00Z | 13 |
2023-04-09T23:34:00Z | 13 |
2023-04-09T23:35:00Z | 13 |
2023-04-09T23:36:00Z | 13 |
2023-04-09T23:37:00Z | 13 |
2023-04-09T23:38:00Z | 13 |
2023-04-09T23:39:00Z | 13 |
2023-04-09T23:40:00Z | 13 |
2023-04-09T23:41:00Z | 7 |
2023-04-09T23:42:00Z | 6 |
2023-04-09T23:43:00Z | 6 |
2023-04-09T23:44:00Z | 6 |
2023-04-09T23:45:00Z | 6 |
2023-04-09T23:46:00Z | 6 |
2023-04-09T23:47:00Z | 6 |
2023-04-09T23:48:00Z | 6 |
2023-04-09T23:49:00Z | 6 |
2023-04-09T23:50:00Z | 6 |
2023-04-09T23:51:00Z | 6 |
2023-04-09T23:52:00Z | 6 |
2023-04-09T23:53:00Z | 6 |
2023-04-09T23:54:00Z | 6 |
2023-04-09T23:55:00Z | 6 |
2023-04-09T23:56:00Z | 6 |
2023-04-09T23:57:00Z | 6 |
2023-04-09T23:58:00Z | 6 |
2023-04-09T23:59:00Z | 6 |
2023-04-10T00:00:00Z | 6 |
2023-04-10T00:01:00Z | 6 |
2023-04-10T00:02:00Z | 6 |
2023-04-10T00:03:00Z | 6 |
2023-04-10T00:04:00Z | 6 |
2023-04-10T00:05:00Z | 6 |
2023-04-10T00:06:00Z | 6 |
2023-04-10T00:07:00Z | 6 |
2023-04-10T00:08:00Z | 6 |
2023-04-10T00:09:00Z | 6 |
2023-04-10T00:10:00Z | 6 |
2023-04-10T00:11:00Z | 6 |
2023-04-10T00:12:00Z | 6 |
2023-04-10T00:13:00Z | 6 |
2023-04-10T00:14:00Z | 6 |
2023-04-10T00:15:00Z | 6 |
2023-04-10T00:16:00Z | 6 |
2023-04-10T00:17:00Z | 6 |
2023-04-10T00:18:00Z | 6 |
2023-04-10T00:19:00Z | 6 |
2023-04-10T00:20:00Z | 6 |
2023-04-10T00:21:00Z | 6 |
2023-04-10T00:22:00Z | 6 |
2023-04-10T00:23:00Z | 6 |
2023-04-10T00:24:00Z | 6 |
2023-04-10T00:25:00Z | 6 |
2023-04-10T00:26:00Z | 6 |
2023-04-10T00:27:00Z | 6 |
2023-04-10T00:28:00Z | 6 |
2023-04-10T00:29:00Z | 6 |
2023-04-10T00:30:00Z | 6 |
2023-04-10T00:31:00Z | 6 |
2023-04-10T00:32:00Z | 6 |
2023-04-10T00:33:00Z | 6 |
2023-04-10T00:34:00Z | 6 |
2023-04-10T00:35:00Z | 6 |
2023-04-10T00:36:00Z | 6 |
2023-04-10T00:37:00Z | 6 |
2023-04-10T00:38:00Z | 6 |
2023-04-10T00:39:00Z | 6 |
2023-04-10T00:40:00Z | 6 |
2023-04-10T00:41:00Z | 6 |
2023-04-10T00:42:00Z | 6 |
2023-04-10T00:43:00Z | 6 |
2023-04-10T00:44:00Z | 6 |
2023-04-10T00:45:00Z | 6 |
2023-04-10T00:46:00Z | 6 |
2023-04-10T00:47:00Z | 6 |
2023-04-10T00:48:00Z | 6 |
2023-04-10T00:49:00Z | 6 |
2023-04-10T00:50:00Z | 6 |
2023-04-10T00:51:00Z | 6 |
2023-04-10T00:52:00Z | 6 |
2023-04-10T00:53:00Z | 6 |
2023-04-10T00:54:00Z | 6 |
2023-04-10T00:55:00Z | 6 |
2023-04-10T00:56:00Z | 6 |
2023-04-10T00:57:00Z | 6 |
2023-04-10T00:58:00Z | 6 |
2023-04-10T00:59:00Z | 6 |
2023-04-10T01:00:00Z | 6 |
2023-04-10T01:01:00Z | 6 |
2023-04-10T01:02:00Z | 6 |
2023-04-10T01:03:00Z | 6 |
2023-04-10T01:04:00Z | 6 |
2023-04-10T01:05:00Z | 6 |
2023-04-10T01:06:00Z | 6 |
2023-04-10T01:07:00Z | 6 |
2023-04-10T01:08:00Z | 6 |
2023-04-10T01:09:00Z | 6 |
2023-04-10T01:10:00Z | 6 |
2023-04-10T01:11:00Z | 6 |
2023-04-10T01:12:00Z | 6 |
2023-04-10T01:13:00Z | 6 |
2023-04-10T01:14:00Z | 6 |
2023-04-10T01:15:00Z | 6 |
2023-04-10T01:16:00Z | 6 |
2023-04-10T01:17:00Z | 6 |
2023-04-10T01:18:00Z | 6 |
2023-04-10T01:19:00Z | 6 |
2023-04-10T01:20:00Z | 6 |
2023-04-10T01:21:00Z | 6 |
2023-04-10T01:22:00Z | 6 |
2023-04-10T01:23:00Z | 6 |
2023-04-10T01:24:00Z | 6 |
2023-04-10T01:25:00Z | 6 |
2023-04-10T01:26:00Z | 6 |
2023-04-10T01:27:00Z | 6 |
2023-04-10T01:28:00Z | 6 |
2023-04-10T01:29:00Z | 6 |
2023-04-10T01:30:00Z | 6 |
2023-04-10T01:31:00Z | 6 |
2023-04-10T01:32:00Z | 6 |
2023-04-10T01:33:00Z | 6 |
2023-04-10T01:34:00Z | 6 |
2023-04-10T01:35:00Z | 6 |
2023-04-10T01:36:00Z | 6 |
2023-04-10T01:37:00Z | 6 |
2023-04-10T01:38:00Z | 6 |
2023-04-10T01:39:00Z | 6 |
2023-04-10T01:40:00Z | 6 |
2023-04-10T01:41:00Z | 6 |
2023-04-10T01:42:00Z | 6 |
2023-04-10T01:43:00Z | 6 |
2023-04-10T01:44:00Z | 6 |
2023-04-10T01:45:00Z | 6 |
2023-04-10T01:46:00Z | 6 |
2023-04-10T01:47:00Z | 6 |
2023-04-10T01:48:00Z | 6 |
2023-04-10T01:49:00Z | 6 |
2023-04-10T01:50:00Z | 6 |
2023-04-10T01:51:00Z | 6 |
2023-04-10T01:52:00Z | 6 |
2023-04-10T01:53:00Z | 6 |
2023-04-10T01:54:00Z | 6 |
2023-04-10T01:55:00Z | 6 |
2023-04-10T01:56:00Z | 6 |
2023-04-10T01:57:00Z | 6 |
2023-04-10T01:58:00Z | 6 |
2023-04-10T01:59:00Z | 6 |
2023-04-10T02:00:00Z | 6 |
2023-04-10T02:01:00Z | 6 |
2023-04-10T02:02:00Z | 6 |
2023-04-10T02:03:00Z | 9 |
2023-04-10T02:04:00Z | 11.5 |
2023-04-10T02:05:00Z | 13 |
2023-04-10T02:06:00Z | 13 |
2023-04-10T02:07:00Z | 13 |
2023-04-10T02:08:00Z | 13 |
2023-04-10T02:09:00Z | 13 |
2023-04-10T02:10:00Z | 13 |
2023-04-10T02:11:00Z | 13 |
2023-04-10T02:12:00Z | 13 |
2023-04-10T02:13:00Z | 13 |
2023-04-10T02:14:00Z | 13 |
2023-04-10T02:15:00Z | 13 |
2023-04-10T02:16:00Z | 13 |
2023-04-10T02:17:00Z | 9 |
2023-04-10T02:18:00Z | 9 |
2023-04-10T02:19:00Z | 9 |
2023-04-10T02:20:00Z | 9 |
2023-04-10T02:21:00Z | 9 |
2023-04-10T02:22:00Z | 6 |
2023-04-10T02:23:00Z | 6 |
2023-04-10T02:24:00Z | 6 |
2023-04-10T02:25:00Z | 6 |
2023-04-10T02:26:00Z | 6 |
2023-04-10T02:27:00Z | 2 |
2023-04-10T02:28:00Z | 2 |
2023-04-10T02:29:00Z | 2 |
2023-04-10T02:30:00Z | 2 |
2023-04-10T02:31:00Z | 3 |
2023-04-10T02:32:00Z | 3 |
2023-04-10T02:33:00Z | 3 |
2023-04-10T02:34:00Z | 3 |
2023-04-10T02:35:00Z | 3 |
2023-04-10T02:36:00Z | 3 |
2023-04-10T02:37:00Z | 3 |
2023-04-10T02:38:00Z | 3 |
2023-04-10T02:39:00Z | 3 |
2023-04-10T02:40:00Z | 3 |