这篇文章主要介绍了使用jxl简化poi代码,实现导出Excel,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。以下代码就是一个小demo,大家可以直接自己创建maven项目然后跟着步骤来学习,步骤相对来说是比较全面的。
1、添加依赖,引入相关jar包
<dependencies>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-jexl</artifactId>
<version>2.1.1</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
2、创建导出工具类
这个了解即可,没必要记的,只需要记住怎么使用即可。
package com.gzl.util;
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.util.List;
import java.util.Map;
import java.util.Objects;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelUtil {
public static Workbook createExcel(Map<String, Object> dataParam, String templateFilePath) throws InvalidFormatException, FileNotFoundException {
InputStream is = new FileInputStream(templateFilePath);
XLSTransformer transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(is, dataParam);
if (dataParam.get("sheetName") != null)
workbook.setSheetName(0, dataParam.get("sheetName").toString());
return workbook;
}
public static void writeExcel(Workbook workBook, String targetFilePath) throws IOException {
OutputStream os = new FileOutputStream(targetFilePath);
workBook.write(os);
os.flush();
os.close();
}
public static void createExcel(Map<String, Object> dataParam, String templateFilePath, String targetFilePath) throws InvalidFormatException, IOException {
checkFileExtension(templateFilePath, targetFilePath);
Workbook workbook = createExcel(dataParam, templateFilePath);
writeExcel(workbook, targetFilePath);
}
private static void checkFileExtension(String templateFilePath, String targetFilePath) {
if (!Objects.equals(FilenameUtils.getExtension(templateFilePath),
FilenameUtils.getExtension(templateFilePath)))
throw new IllegalArgumentException("模板后缀跟要保存的Excel文件后缀不一样");
}
}
3、创建导出方法
这个测试方法当中我们是直接导出doctorVillage模板,在现实项目当中,有时候会多处存在导出表格功能,一般模板名称让前台来传,然后根据传的参数来控制他需要执行的方法,以及所对应的模板。
package com.gzl.util;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
public class Test1 {
public static void main(String[] args) throws InvalidFormatException, IOException {
String path = Test1.class.getResource("/template/").getPath();
// getResource对中文进行了编码,需要调用decode解码
path = java.net.URLDecoder.decode(path, "utf-8");
String fileNames = "";
//模板全路径
String templateFilePath = path + "doctorVillage" + ".xls";
//判断是否是windows
boolean flag = System.getProperty("os.name").contains("Window");
String fileName = UUID.randomUUID().toString();
String targetFilePath = "";
if(flag) {
//获取系统临时目录
targetFilePath = System.getProperty("java.io.tmpdir") + fileName + ".xls";
} else {
//如果是linux需要添加File.separator把/改为\
targetFilePath = System.getProperty("java.io.tmpdir")+ File.separator + fileName + ".xls";
}
//模拟数据,一般项目当中都会以实体类来作为对象,这里我们用map为例,这个是用什么都可以的。
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Map<String, Object> map1 = new HashMap<String, Object>();
map1.put("rownums", Integer.valueOf(1));
map1.put("cityName", "湘潭市");
map1.put("countyName", "湘潭县");
map1.put("townName", "中路铺镇");
Map<String, Object> map2 = new HashMap<String, Object>();
map2.put("rownums", Integer.valueOf(2));
map2.put("cityName", "湘潭市");
map2.put("countyName", "雨湖区");
map2.put("townName", "基建营");
list.add(map1);
list.add(map2);
//将数据存入map当中
Map<String, Object> excelParam = new HashMap<>();
excelParam.put("result", list);
//excelParam存放数据的map,templateFilePath模板全路径,targetFilePath存放临时路径
ExcelUtil.createExcel(excelParam, templateFilePath, targetFilePath);
System.out.println(targetFilePath);
}
}
4、创建template文件夹专门用来存放导出模板
5、创建doctorVillage.xls导出模板
6、执行方法
执行之后返回路径,这个路径就是生成的文件路径。下载的话直接就去这个路径下载即可。