目录
(1)导入依赖如上,注意只有高一点版本的poi-tl才有模板策略,即LoopRowTableRenderPolicy
3、使用easypoi按模板生成一个word(包含easypoi生成word的合并)
4、使用jxls 2.10.0 按模板生成excel(有条件还是使用新版好,生成excel合并格式也都完整,老版的合并单元格还是有问题需要自己调整)
一、前言
最近项目遇到生成word,excel的问题,但由于jar包冲突,版本不符合要求等原因,小白使用了许多方法来完成目的,基本都是不断度娘,今天来汇总一下,代码注释中加了些个人的粗浅理解,望误喷。
先说一下,本文中,生成word描述了使用Apache poi,easypoi工具的方法,生成excel描述了使用Apache poi,easyexcel,jxls工具的方法,可选择观看
二、生成word
1、使用Apache poi手动生成一个word
(1)导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.10.0</version>
</dependency>
(2)手动生成一个包含表格的word
package cn.itcast.mp;
import cn.itcast.mp.pojo.User;
import org.apache.poi.xwpf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class WordUtil {
public static void main(String[] args) throws IOException {//普通生成word文件及其中的word表格
List<User> users = new ArrayList<>();//造点数据,此处创建了User对象,属性id,userName,password,name,age,mail
users.add(new User(1L, "zhangsan", "99999", "张三", 25, "test1@itcast.cn"));
users.add(new User(2L, "zhangsan1", "88888", "张三1", 20, "test1@itcast.cn1"));
users.add(new User(3L, "zhangsan2", "77777", "张三2", 23, "test1@itcast.cn2"));
users.add(new User(4L, "zhangsan3", "66666", "张三3", 24, "test1@itcast.cn3"));
users.add(new User(5L, "zhangsan4", "55555", "张三4", 28, "test1@itcast.cn4"));
users.add(new User(6L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
XWPFDocument doc = new XWPFDocument();//Apache poi的一个文档对象,暂理解为创建了一个虚拟的word文档
XWPFParagraph p = doc.createParagraph();//在这个word里新建一个段落
p.setAlignment(ParagraphAlignment.CENTER);//设置段落的对齐方式
p.setBorderBottom(Borders.DOUBLE);//设置下边框
p.setBorderTop(Borders.DOUBLE);//设置上边框
p.setBorderRight(Borders.DOUBLE);//设置右边框
p.setBorderLeft(Borders.DOUBLE);//设置左边框
XWPFRun r = p.createRun();//创建段落文本,即段落里要放入的内容
r.setText("创建段落文本里的内容,set相当于放值");//内容赋值
r.setBold(true);//内容设置为粗体
r.setColor("FF0000");//内容设置颜色;此处不带#
p = doc.createParagraph();//再创建一个段落
r= p.createRun();//第二个段落中的文本
r.setText("这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!");
// XWPFParagraph p1 = doc.createParagraph();//这三句与上三句效果相同
// XWPFRun r1 = p1.createRun();
// r1.setText("这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!这里是段落内容!");
XWPFTable table = doc.createTable(users.size() + 1, 6);//创建一个表格,括号中(几行,几列)
table.getRow(0).getCell(0).setText("id");//设置表头
table.getRow(0).getCell(1).setText("userName");//getRow获取第几行,getCell获取第几列,setText单元格赋值
table.getRow(0).getCell(2).setText("password");
table.getRow(0).getCell(3).setText("name");
table.getRow(0).getCell(4).setText("age");
table.getRow(0).getCell(5).setText("mail");
for (int i = 0; i < users.size(); i++) {//使用双层循环,并用以上单元格赋值方法,给单元格一个一个赋值
for (int j = 0; j < 6; j++) {
if (j == 0) {
table.getRow(i+1).getCell(j).setText(users.get(i).getId().toString());
} else if (j == 1) {
table.getRow(i + 1).getCell(j).setText(users.get(i).getUserName());
} else if (j == 2) {
table.getRow(i + 1).getCell(j).setText(users.get(i).getPassword());
} else if (j == 3) {
table.getRow(i + 1).getCell(j).setText(users.get(i).getName());
} else if (j == 4) {
table.getRow(i+1).getCell(j).setText(users.get(i).getAge().toString());
} else if (j == 5) {
table.getRow(i + 1).getCell(j).setText(users.get(i).getMail());
}
}
}
table.setTableAlignment(TableRowAlign.CENTER);//表格居中
String savePath = "D:\\poi";//生成word后要保存到的位置
String fileName = "PoiWord";//生成的word的名字
createDoc(doc, savePath, fileName);//调用下面poi生成word的方法
}
public static void createDoc(XWPFDocument document, String savePath, String fileName) throws IOException {
File file = new File(savePath);//按照路径先生成一个文件,用于判断是否存在目录
if (!file.exists()) {
// 判断生成目录是否存在,不存在时创建目录。
file.mkdirs();
}
// 保存
fileName += ".docx";//后缀
FileOutputStream out = new FileOutputStream(new File(savePath + File.separator + fileName));//生成word文件以输出流的方式,输出用于文件的写入
document.write(out);//poi的write方法,把虚拟word中的数据写入刚创建的word文件里
// 关闭资源
out.flush();
out.close();
document.close();
}
}
2、使用Apache poi 按模板生成一个简单的word
(1)导入依赖如上,注意只有高一点版本的poi-tl才有模板策略,即LoopRowTableRenderPolicy
(2)模板样式
(3)代码示例
import cn.itcast.mp.pojo.User;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.config.Configure;
import com.deepoove.poi.data.TextRenderData;
import com.deepoove.poi.data.Texts;
import com.deepoove.poi.data.style.Style;
import com.deepoove.poi.plugin.table.LoopRowTableRenderPolicy;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class WordTest {//模板生成word表格
public static void main(String[] args) {
try {
List<User> users = new ArrayList<>();//造点数据
users.add(new User(1L, "zhangsan", "99999", "张三", 25, "test1@itcast.cn"));
users.add(new User(2L, "zhangsan1", "88888", "张三1", 20, "test1@itcast.cn1"));
users.add(new User(3L, "zhangsan2", "77777", "张三2", 23, "test1@itcast.cn2"));
users.add(new User(4L, "zhangsan3", "66666", "张三3", 24, "test1@itcast.cn3"));
users.add(new User(5L, "zhangsan4", "55555", "张三4", 28, "test1@itcast.cn4"));
users.add(new User(6L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
List<Map<String,Object>> empList = new ArrayList<>();//必须是List<Map>型几个的数据才可以显示出来
for (int i = 0; i < users.size(); i++) {
Map<String, Object> map = new HashMap<>();
map.put("id",users.get(i).getId());
map.put("userName",users.get(i).getUserName());
map.put("password",users.get(i).getPassword());
map.put("name",users.get(i).getName());
map.put("age",users.get(i).getAge());
map.put("mail",users.get(i).getMail());
empList.add(map);
}
Map<String,Object> empList1 = new HashMap<>();
empList1.put("empList",empList);
empList1.put("empList1",empList);
empList1.put("title",new TextRenderData("F04B09","POI创建的Word段落文本"));
String content = "这是测试!这是测试!这是测试!这是测试!这是测试!这是测试!这是测试!这是测试!这是测试!";
Style style = Style.builder().buildColor("18282B").buildUnderlineColor("F04B09").build();//设置样式
empList1.put("content", Texts.of(content).style(style).create());//加入样式
// 模板文件地址
String filePath = "D:\\poi\\WordTem.docx";
// 读取模板后保存生成word的地址
String outPath = "D:\\poi\\wordTest.docx";
// 为表格的显示绑定循环政策
LoopRowTableRenderPolicy policy = new LoopRowTableRenderPolicy();
// 将empList设置为行循环绑定的数据源的key,即key是empList的value会在模板中的{{empList}}处进行解析,可查看模板
Configure configure = Configure.builder().bind("empList", policy).bind("empList1",policy).build();
// 读取模板、数据并渲染,render中放入整体的模板map
XWPFTemplate template = XWPFTemplate.compile(new File(filePath), configure).render(empList1);
// 文件是否已存在,则删除
File file = new File(outPath);
if (file.exists()){
file.delete();
}
// 生成word保存在指定目录(把生成好的模板word数据写入刚生成的文件中)
template.writeToFile(outPath);
template.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
(4)模板说明
-
{{xxx}}表示普通占位符
-
Configure configure = Configure.builder().bind("empList", policy).bind("empList1",policy).build();
-
模板表格中id项{{xxx}}相当于一个标识,起名字就为以上代码bind中“ ”中的内容,想要几个表就bind几次
-
表格中的各个字段用 [ ]
3、使用easypoi按模板生成一个word(包含easypoi生成word的合并)
(1)导入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.3.0</version>
</dependency>
<!--注意:word中要使用循环等标签必须单独导入以下依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
(2)模板样式
(3)模板说明
-
{{xxx}}普通占位符
-
{{$fe:遍历集合的键名}} t就用t就行 此处注意循环标签括起了一整行
-
还有,如果出现表格与数据堆在一起的情况,不行就多空几行,如图
(4)代码示例
import cn.afterturn.easypoi.word.WordExportUtil;
import cn.itcast.mp.pojo.User;
import org.apache.poi.xwpf.usermodel.Document;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.xwpf.usermodel.XWPFPictureData;
import org.apache.xmlbeans.XmlOptions;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTBody;
import org.springframework.util.CollectionUtils;
public class CreateWordEasy {
public static void main(String[] args) {
List<User> users = new ArrayList<>();//造点数据
users.add(new User(1L, "zhangsan", "99999", "张三", 25, "test1@itcast.cn"));
users.add(new User(2L, "zhangsan1", "88888", "张三1", 20, "test1@itcast.cn1"));
users.add(new User(3L, "zhangsan2", "77777", "张三2", 23, "test1@itcast.cn2"));
users.add(new User(4L, "zhangsan3", "66666", "张三3", 24, "test1@itcast.cn3"));
users.add(new User(5L, "zhangsan4", "55555", "张三4", 28, "test1@itcast.cn4"));
users.add(new User(7L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(8L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(7L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(10L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(11L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(12L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(13L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(14L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
users.add(new User(15L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
List<Map<String,Object>> empList = new ArrayList<>();//必须是List<Map>型几个的数据才可以显示出来
for (int i = 0; i < users.size(); i++) {
Map<String, Object> map = new HashMap<>();
map.put("id",users.get(i).getId());
map.put("userName",users.get(i).getUserName());
map.put("password",users.get(i).getPassword());
map.put("name",users.get(i).getName());
map.put("age",users.get(i).getAge());
map.put("mail",users.get(i).getMail());
empList.add(map);
}
Map<String,Object> empList1 = new HashMap<>();
empList1.put("empList",empList);//放入word表格中需要遍历的集合数据,键名“empList”
String content = "测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!测试!!!\n ";
empList1.put("content",content);
// 模板文件地址
String filePath = "D:\\poi\\wordEasyTem.docx";
// 读取模板后保存生成word的地址
String outPath = "D:\\poi\\easyWordTest.docx";
try {
XWPFDocument doc = WordExportUtil.exportWord07(filePath,empList1);//easypoi自带类与方法,(模板地址,模板所需map)
XWPFDocument doc1 = WordExportUtil.exportWord07(filePath,empList1);//合成所需的另一个虚拟word
List<XWPFDocument> wordList = new ArrayList<>();//需合并时创建的集合
wordList.add(doc);//把所有虚拟word加入集合,以便调用合并方法
wordList.add(doc1);
XWPFDocument xwpfDocument = mergeWord(wordList);//调用word合并方法
File file = new File(outPath);
if (file.exists()) {
file.delete();
}
FileOutputStream out = new FileOutputStream(file);
xwpfDocument.write(out);//将生成的模板数据写入word文件,如果不用合并此处直接doc.即可
out.flush();
out.close();
xwpfDocument.close();//如果不用合并此处直接doc.即可
} catch (Exception e) {
e.printStackTrace();
}
}
word合并工具方法
/**
* word文件合并
* @param wordList
* @return
* @throws Exception
*/
public static XWPFDocument mergeWord(List<XWPFDocument> wordList) throws Exception{
if (CollectionUtils.isEmpty(wordList)) {
throw new RuntimeException("待合并的word文档list为空");
}
XWPFDocument doc = wordList.get(0);
int size = wordList.size();
if (size > 1) {
doc.createParagraph().setPageBreak(true);
for (int i = 1; i < size; i++) {
// 从第二个word开始合并
XWPFDocument nextPageDoc = wordList.get(i);
// 最后一页不需要设置分页符
if (i != (size-1)) {
nextPageDoc.createParagraph().setPageBreak(true);
}
appendBody(doc, nextPageDoc);
}
}
return doc;
}
private static void appendBody(XWPFDocument src, XWPFDocument append) throws Exception {
CTBody src1Body = src.getDocument().getBody();
CTBody src2Body = append.getDocument().getBody();
List<XWPFPictureData> allPictures = append.getAllPictures();
// 记录图片合并前及合并后的ID
Map<String,String> map = new HashMap<>();
for (XWPFPictureData picture : allPictures) {
String before = append.getRelationId(picture);
//将原文档中的图片加入到目标文档中
String after = src.addPictureData(picture.getData(), Document.PICTURE_TYPE_PNG);
map.put(before, after);
}
appendBody(src1Body, src2Body,map);
}
private static void appendBody(CTBody src, CTBody append,Map<String,String> map) throws Exception {
XmlOptions optionsOuter = new XmlOptions();
optionsOuter.setSaveOuter();
String appendString = append.xmlText(optionsOuter);
String srcString = src.xmlText();
String prefix = srcString.substring(0,srcString.indexOf(">")+1);
String mainPart = srcString.substring(srcString.indexOf(">")+1,srcString.lastIndexOf("<"));
String sufix = srcString.substring( srcString.lastIndexOf("<") );
String addPart = appendString.substring(appendString.indexOf(">") + 1, appendString.lastIndexOf("<"));
if (map != null && !map.isEmpty()) {
//对xml字符串中图片ID进行替换
for (Map.Entry<String, String> set : map.entrySet()) {
addPart = addPart.replace(set.getKey(), set.getValue());
}
}
//将两个文档的xml内容进行拼接
CTBody makeBody = CTBody.Factory.parse(prefix+mainPart+addPart+sufix);
src.set(makeBody);
}
注意:方法来源于网络,该合并方法有时会使生成的word报数据恢复,我之后多合并了几个就没有出现这种情况
三、生成excel
1、使用Apache poi 手动生成一个简单的excel
(1)导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.10.0</version>
</dependency>
(2)代码示例
import cn.itcast.mp.pojo.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public static void createExcel(){
List<User> users = new ArrayList<>();//先造点数据
users.add(new User(1L, "zhangsan", "99999", "张三", 25, "test1@itcast.cn"));
users.add(new User(2L, "zhangsan1", "88888", "张三1", 20, "test1@itcast.cn1"));
users.add(new User(3L, "zhangsan2", "77777", "张三2", 23, "test1@itcast.cn2"));
users.add(new User(4L, "zhangsan3", "66666", "张三3", 24, "test1@itcast.cn3"));
users.add(new User(5L, "zhangsan4", "55555", "张三4", 28, "test1@itcast.cn4"));
users.add(new User(6L, "zhangsan5", "44444", "张三5", 30, "test1@itcast.cn5"));
XSSFWorkbook workbook = new XSSFWorkbook();//Apache poi创建excel对象,相当于创建一个虚拟的excel
XSSFSheet sheet = workbook.createSheet("sheet1");//创建一个sheet页,括号内为sheet页名字
sheet.addMergedRegion(new CellRangeAddress(0,0,5,6));//合并单元格 范围:(firstRow,lastRow,firstCol,lastCol),注意行列计算均从0开始
CellStyle cellStyle = workbook.createCellStyle();//设置单元格样式
cellStyle.setBorderBottom(BorderStyle.THIN);//设置单元格样式为黑色实线
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
Font font = workbook.createFont();//设置字体样式
font.setFontName("宋体");//字体样式
font.setBold(true);//字体加粗
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
Row row = sheet.createRow(0);//设置表头,获取第一行
row.createCell(0).setCellValue("id");//createRow获取行,createCell获取列,setCellValue,放值
row.createCell(1).setCellValue("userName");
row.createCell(2).setCellValue("password");
row.createCell(3).setCellValue("name");
row.createCell(4).setCellValue("age");
row.createCell(5).setCellValue("mail");
for (int i = 0; i < users.size(); i++) {
Row row1 = sheet.createRow(i+1);
row1.createCell(0).setCellValue(users.get(i).getId());
row1.createCell(1).setCellValue(users.get(i).getUserName());
row1.createCell(2).setCellValue(users.get(i).getPassword());
row1.createCell(3).setCellValue(users.get(i).getName());
row1.createCell(4).setCellValue(users.get(i).getAge());
row1.createCell(5).setCellValue(users.get(i).getMail());
sheet.addMergedRegion(new CellRangeAddress(i+1,i+1,5,6));//合并数据中的单元格
}
String path = "D:\\poi\\excel\\";//生成文件
//判断路径是否存在,否则生成
File file = new File(path);
if (!file.exists()){
file.mkdirs();
}
String fileName = new SimpleDateFormat("yyyy年MM月dd日hhmmssSS").format(new Date() ) + "测试Excel"+".xlsx";
try {
FileOutputStream fileOutputStream = new FileOutputStream(path + fileName);
workbook.write(fileOutputStream);//excel写进文件里
fileOutputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
2、使用easyexcel按模板生成excel
(1)导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
(2)模板样式
(3)代码示例
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CreateExcel01 {
public static void main(String[] args) {
List<Map<String,Object>> events = new ArrayList<>();
for (int i = 0; i < 3; i++) {//造点数据
Map<String,Object> map = new HashMap<>();
map.put("index",i+1);
map.put("sys","windows");
map.put("content","测试!测试!测试!测试!测试!测试!测试!测试!");
map.put("time","2022-9-5");
events.add(map);
}
String filePath = "D:\\poi\\zhyw\\excelTest.xlsx";//生成Excel路径
String templatePath = "D:\\poi\\zhyw\\weekTem.xlsx";//模板路径
ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templatePath).build();//easyexcel创建一个虚拟的excel
WriteSheet writeSheet = EasyExcel.writerSheet().build(); // 每次都会重新生成新的一行,而不是使用下面的空行
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.TRUE).build();//占位符替换
Map<String, Object> map = new HashMap<>();// 填充数据
map.put("name", "张三");
excelWriter.fill(map, writeSheet);
excelWriter.fill(new FillWrapper("data01", events), fillConfig, writeSheet);//要遍历的数据设置
excelWriter.fill(new FillWrapper("data02", events), fillConfig, writeSheet);//同一个sheet页的不同表中要遍历的数据
excelWriter.finish();// 关闭流
}
}
3、使用jxls 1.0.6 按模板生成excel
(1)导入依赖
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<!-- <version>4.1.0</version>此处用4.1.0我会报冲突-->
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<!-- <version>4.1.0</version>-->
<version>3.16</version>
</dependency>
(2)模板样式及说明
本来循环显示数据表格在设置模板时需要加<foreach>标签,但是我在之前带有合并的表格中加了<foreach>会报无法合并的错误,试了一下,不使用<foreach>也可以循环生成数据
(3)代码示例
import com.jxdinfo.mobile.report.service.IReportService;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;
import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class CreateRunInfoWeekExcel {
@Autowired
private IReportService reportService;
public void createRunInfoWeekExcel() {//我所用的数据处理
List<Map<String, Object>> weekRunInfo = reportService.getWeekRunInfo();
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日");
String currentTime = LocalDateTime.now().format(dateTimeFormatter);
Map<String,Object> map = new HashMap<>();
for (int i = 0; i < weekRunInfo.size(); i++) {
String sysName = (String) weekRunInfo.get(i).get("sysName");
map = weekRunInfo.get(i);
String fileName = currentTime + sysName + "周运行情况";
createWeekExcelUtil(fileName,map);//调用生成excel方法
}
}
public void createWeekExcelUtil(String fileName,Map<String,Object> map) {//生成excel主要在这里
try {
String filePath = "D:\\poi\\zhyw\\Test01\\"+fileName+".xlsx";// 生成Excel路径
Resource resource = new ClassPathResource("templates/RunInfoWeek01.xlsx");//获取resource文件下的模板路径
String templatePath = resource.getFile().getPath();
//String templatePath = "D:\\poi\\zhyw\\Test01\\RunInfoWeek01.xlsx";//本地模板路径
File file = new File(templatePath);
InputStream inputStream = new FileInputStream(file);//使用输入流读取模板文件
XLSTransformer xlsTransformer = new XLSTransformer();//创建jxls的XLSTransformer对象
Workbook workbook = xlsTransformer.transformXLS(inputStream,map);//生成一个按模板生成的虚拟excel,(模板输入流,占位替换所需map)
OutputStream os = new BufferedOutputStream(new FileOutputStream(filePath));//输出流输出一个备用excel
workbook.write(os);//把之前生成好的模板文件写入流输出的备用excel
inputStream.close();//关闭资源
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
4、使用jxls 2.10.0 按模板生成excel(有条件还是使用新版好,生成excel合并格式也都完整,老版的合并单元格还是有问题需要自己调整)
(1)导入依赖
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.10.0</version>
</dependency>
(2)模板样式
红色三角就是在excel里加批注,右键单元格就有
(3)模板说明
-
普通占位符 ${xxx}
-
循环标签 jx:each(items="循环数据的键" var="遍历时用的对象" lastCell="循环标签这一行 最后一列最后一行")
-
在最开始标题头那里添加注释 jx:area(lastCell="整个表格的范围")
-
对于lastCell均只考虑模板先有的行列,并不考虑循环有数据后的行列
(4)代码示例
import org.jxls.common.Context;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JxlsCreateExcel {
public static void main(String[] args) {
List<Map<String, Object>> events = new ArrayList<>();
for (int i = 0; i < 3; i++) {//造点数据
Map<String, Object> map = new HashMap<>();
map.put("index", i + 1);
map.put("sys", "windows");
map.put("content", "测试!测试!测试!测试!测试!测试!测试!测试!");
map.put("time", "2022-9-5");
events.add(map);
}
String filePath = "D:\\poi\\zhyw\\jxlsExcelTest.xlsx";//生成Excel路径
String templatePath = "D:\\poi\\zhyw\\weekTem04.xlsx";//模板路径
File file = new File(templatePath);
try (InputStream is = new FileInputStream(file)) {
try (OutputStream os = new FileOutputStream(filePath)) {
Context context = new Context();//jxls 2.10.0自带的类很像一个map
context.putVar("name","李四");
context.putVar("data01",events);
context.putVar("data02",events);
context.putVar("data03",events);
context.putVar("data04",events);
JxlsHelper.getInstance().processTemplate(is, os, context);//jxls 2.10.0自带的辅助类(模板流,生成文件流,占位符map)
}
} catch (Exception e) {
e.printStackTrace();
}
}
}