根据excel 模板导出excel,jxls 实现
导入相关jar包,maven依赖
<!-- jxls -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.10.0-rc3</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.8</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
JxlsUtils工具类:
package com.smm.testjxls;
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.Map;
import org.jxls.common.Context;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
public class JxlsUtils {
private static final String TEMPLATE_PATH ="D:\\Users\\EX-SUMEIMEI001\\Desktop\\smm\\";
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();
jxlsHelper.setUseFastFormulaProcessor(false).setDeleteTemplateSheet(true).processTemplate(is,os,context);
}
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;
}
}
相关dto类:
package com.smm.testjxls;
import java.util.List;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class YuserageDTO {
private String name;
private List<YearUsageExcelDTO> tools;
}
package com.smm.testjxls;
import io.swagger.annotations.ApiModel;
import java.io.Serializable;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel("年度用量excelDto")
public class YearUsageExcelDTO implements Serializable {
/**
* 年份
*/
private String year;
/**
* 年月
*/
private String yearMonth;
/**
* 年月日
*/
private String day;
private String lastCell;
/**
*
*/
private int liveType;
private int jgjLive;
private int classLive;
private int meetingLive;
private int remoteLive;
private int zhiniaoLive;
}
测试类:
package com.smm.testjxls;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestMain1 {
public static void main(String[] args) throws Exception {
int year = 2022;
//导出的excel名字
String fileName ="年度数据2019-"+ year+".xlsx";
//导出的excel路径
String templatePath = "D:\\Users\\MEIMEI001\\Desktop\\smm\\"+fileName;
OutputStream os = new FileOutputStream(templatePath);
//
YearUsageExcelDTO yearUsageDTO62019 = YearUsageExcelDTO.builder().year("2019").liveType(6).jgjLive(201910).classLive(201911).meetingLive(201912).remoteLive(201913).zhiniaoLive(201914).build();
YearUsageExcelDTO yearUsageDTO62020 = YearUsageExcelDTO.builder().year("2020").liveType(6).jgjLive(202010).classLive(202011).meetingLive(202012).remoteLive(202013).zhiniaoLive(202014).build();
YearUsageExcelDTO yearUsageDTO62021 = YearUsageExcelDTO.builder().year("2021").liveType(6).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
YearUsageExcelDTO yearUsageDTO62022 = YearUsageExcelDTO.builder().year("2022").liveType(6).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO72019 = YearUsageExcelDTO.builder().year("2019").liveType(7).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO72020 = YearUsageExcelDTO.builder().year("2020").liveType(7).jgjLive(202010).classLive(202011).meetingLive(202012).remoteLive(202013).zhiniaoLive(202014).build();
YearUsageExcelDTO yearUsageDTO72021 = YearUsageExcelDTO.builder().year("2021").liveType(7).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO82019 = YearUsageExcelDTO.builder().year("2019").liveType(8).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO82020 = YearUsageExcelDTO.builder().year("2020").liveType(8).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO82021 = YearUsageExcelDTO.builder().year("2021").liveType(8).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO92019 = YearUsageExcelDTO.builder().year("2019").liveType(9).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO92020 = YearUsageExcelDTO.builder().year("2020").liveType(9).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO92021 = YearUsageExcelDTO.builder().year("2021").liveType(9).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO102019 = YearUsageExcelDTO.builder().year("2019").liveType(10).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO102020 = YearUsageExcelDTO.builder().year("2020").liveType(10).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO102021 = YearUsageExcelDTO.builder().year("2021").liveType(10).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO112019 = YearUsageExcelDTO.builder().year("2019").liveType(11).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO112020 = YearUsageExcelDTO.builder().year("2020").liveType(11).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO112021 = YearUsageExcelDTO.builder().year("2021").liveType(11).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
//
YearUsageExcelDTO yearUsageDTO122019 = YearUsageExcelDTO.builder().year("2019").lastCell("G6").liveType(12).jgjLive(201912).classLive(201911).meetingLive(201912).remoteLive(201913).zhiniaoLive(201914).build();
YearUsageExcelDTO yearUsageDTO122020 = YearUsageExcelDTO.builder().year("2020").liveType(12).jgjLive(202012).classLive(202011).meetingLive(202012).remoteLive(202013).zhiniaoLive(202014).build();
YearUsageExcelDTO yearUsageDTO122021 = YearUsageExcelDTO.builder().year("2021").liveType(12).jgjLive(202112).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
YearUsageExcelDTO yearUsageDTO122022= YearUsageExcelDTO.builder().year("2022").liveType(12).jgjLive(202212).classLive(202211).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO22019 = YearUsageExcelDTO.builder().year("2019").liveType(2).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO22020 = YearUsageExcelDTO.builder().year("2020").liveType(2).jgjLive(202010).classLive(202011).meetingLive(202012).remoteLive(202013).zhiniaoLive(202014).build();
YearUsageExcelDTO yearUsageDTO22021 = YearUsageExcelDTO.builder().year("2021").liveType(2).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO32019 = YearUsageExcelDTO.builder().year("2019").liveType(3).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO32020 = YearUsageExcelDTO.builder().year("2020").liveType(3).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO32021 = YearUsageExcelDTO.builder().year("2021").liveType(3).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO42019 = YearUsageExcelDTO.builder().year("2019").liveType(4).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO42020 = YearUsageExcelDTO.builder().year("2020").liveType(4).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO42021 = YearUsageExcelDTO.builder().year("2021").liveType(4).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
//
YearUsageExcelDTO yearUsageDTO52019 = YearUsageExcelDTO.builder().year("2019").liveType(5).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO52020 = YearUsageExcelDTO.builder().year("2020").liveType(5).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO52021 = YearUsageExcelDTO.builder().year("2021").liveType(5).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
YearUsageExcelDTO yearUsageDTO52022 = YearUsageExcelDTO.builder().year("2022").liveType(5).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
YearUsageExcelDTO yearUsageDTO2019 = YearUsageExcelDTO.builder().year("2019").liveType(13).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO2020 = YearUsageExcelDTO.builder().year("2020").liveType(13).jgjLive(10).classLive(11).meetingLive(12).remoteLive(13).zhiniaoLive(14).build();
YearUsageExcelDTO yearUsageDTO2021 = YearUsageExcelDTO.builder().year("2021").liveType(13).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
YearUsageExcelDTO yearUsageDTO2022 = YearUsageExcelDTO.builder().year("2022").liveType(13).jgjLive(202110).classLive(202111).meetingLive(202112).remoteLive(202113).zhiniaoLive(202114).build();
List<YuserageDTO> list = new ArrayList<>();
//直播时长(分)
YuserageDTO doraemon1 = new YuserageDTO();
List<YearUsageExcelDTO> toolList1 = new ArrayList<>();
toolList1.add(yearUsageDTO62019);
toolList1.add(yearUsageDTO62020);
toolList1.add(yearUsageDTO62021);
toolList1.add(yearUsageDTO62022);
doraemon1.setName("1时长(分)");
doraemon1.setTools(toolList1);
//用户直播观看时长(分)
YuserageDTO doraemon2 = new YuserageDTO();
List<YearUsageExcelDTO> userLiveViews = new ArrayList<>();
userLiveViews.add(yearUsageDTO72019);
userLiveViews.add(yearUsageDTO72020);
userLiveViews.add(yearUsageDTO72021);
doraemon2.setName("2观看时长(分)");
doraemon2.setTools(userLiveViews);
YuserageDTO doraemon3 = new YuserageDTO();
List<YearUsageExcelDTO> wheatTimes = new ArrayList<>();
wheatTimes.add(yearUsageDTO82019);
wheatTimes.add(yearUsageDTO82020);
wheatTimes.add(yearUsageDTO82021);
doraemon3.setName("3时长(分)");
doraemon3.setTools(wheatTimes);
YuserageDTO doraemon4 = new YuserageDTO();
List<YearUsageExcelDTO> liveScreens = new ArrayList<>();
liveScreens.add(yearUsageDTO92019);
liveScreens.add(yearUsageDTO92020);
liveScreens.add(yearUsageDTO92021);
doraemon4.setName("场次");
doraemon4.setTools(liveScreens);
YuserageDTO doraemon5 = new YuserageDTO();
List<YearUsageExcelDTO> liveViews = new ArrayList<>();
liveViews.add(yearUsageDTO102019);
liveViews.add(yearUsageDTO102020);
liveViews.add(yearUsageDTO102021);
doraemon5.setName("人次");
doraemon5.setTools(liveViews);
YuserageDTO doraemon6 = new YuserageDTO();
List<YearUsageExcelDTO> liveViewNums = new ArrayList<>();
liveViewNums.add(yearUsageDTO112019);
liveViewNums.add(yearUsageDTO112020);
liveViewNums.add(yearUsageDTO112021);
doraemon6.setName("人数");
doraemon6.setTools(liveViewNums);
list.add(doraemon1);
list.add(doraemon2);
list.add(doraemon3);
list.add(doraemon4);
list.add(doraemon5);
list.add(doraemon6);
List<YuserageDTO> list2 = new ArrayList<>();
//回放时长(分)
YuserageDTO replay1 = new YuserageDTO();
List<YearUsageExcelDTO> replayTimes = new ArrayList<>();
replayTimes.add(yearUsageDTO122019);
replayTimes.add(yearUsageDTO122020);
replayTimes.add(yearUsageDTO122021);
replayTimes.add(yearUsageDTO122022);
replay1.setName("8时长(分)");
replay1.setTools(replayTimes);
//回放观看时长(分)
YuserageDTO replay2 = new YuserageDTO();
List<YearUsageExcelDTO> replayViewTimes = new ArrayList<>();
replayViewTimes.add(yearUsageDTO22020);
replayViewTimes.add(yearUsageDTO22019);
replayViewTimes.add(yearUsageDTO22021);
replay2.setName("9时长(分)");
replay2.setTools(replayViewTimes);
YuserageDTO replay3 = new YuserageDTO();
List<YearUsageExcelDTO> videostoreKs = new ArrayList<>();
videostoreKs.add(yearUsageDTO32019);
videostoreKs.add(yearUsageDTO32020);
videostoreKs.add(yearUsageDTO32021);
replay3.setName("视频储存(G)-1");
replay3.setTools(videostoreKs);
YuserageDTO replay4 = new YuserageDTO();
List<YearUsageExcelDTO> replayViewNums = new ArrayList<>();
replayViewNums.add(yearUsageDTO2019);
replayViewNums.add(yearUsageDTO2020);
replayViewNums.add(yearUsageDTO2021);
replayViewNums.add(yearUsageDTO2022);
replay4.setName("视频储存(G)-2");
replay4.setTools(replayViewNums);
YuserageDTO replay5 = new YuserageDTO();
List<YearUsageExcelDTO> videostoreZns = new ArrayList<>();
videostoreZns.add(yearUsageDTO42019);
videostoreZns.add(yearUsageDTO42020);
videostoreZns.add(yearUsageDTO42021);
replay5.setName("10人次");
replay5.setTools(videostoreZns);
YuserageDTO replay6 = new YuserageDTO();
List<YearUsageExcelDTO> replayEmpNums = new ArrayList<>();
replayEmpNums.add(yearUsageDTO52019);
replayEmpNums.add(yearUsageDTO52020);
replayEmpNums.add(yearUsageDTO52021);
replayEmpNums.add(yearUsageDTO52022);
replay6.setName("11人数");
replay6.setTools(replayEmpNums);
list2.add(replay1);
list2.add(replay2);
list2.add(replay3);
list2.add(replay4);
list2.add(replay5);
list2.add(replay6);
Map<String, Object> model = new HashMap<>();
//第一个sheet 数据
model.put("data", list);
//第二个sheet数据
model.put("data2", list2);
//多个sheet 且名字是动态, 可以在model新增sheetName的key;有时间写个单元测试
//model.put("sheetNames", sheetNames);
//excel 模板路径
String templatePath1 = "D:\\Users\\MEIMEI001\\Desktop\\smm\\yearTemplate.xlsx";
InputStream is = new FileInputStream(templatePath1);
JxlsUtils.exportExcel(is, os, model);
os.close();
System.out.println("完成");
}
}
excel 模板:
效果图: