转载自
一、必须的maven依赖(可在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>
二、设置Excel模板(新建一个Excel文档,按如下步骤设置),将新建的文档放到项目中(本案例是放到项目的resource目录下,其他目录不知道可以不)
三、代码编写:
@RequestMapping(value = “exportDeviceModelMsg”,method = RequestMethod.GET)
@ResponseBody
public void exportDeviceModelMsg(HttpServletRequest request, HttpServletResponse response) {
try {
List<MyTest> myTestList = myTestService.findAll(); //获取列表数据
InputStream in = this.getClass().getClassLoader().getResourceAsStream("excel/test.xls"); //得到文档的路径
//列表数据将存储到指定的excel文件路径,这个路径是在项目编译之后的target目录下
FileOutputStream out = new FileOutputStream("target/classes/excel/aaaa.xls");
//这里的context是jxls框架上的context内容
org.jxls.common.Context context = new org.jxls.common.Context();
//将列表参数放入context中
context.putVar("myTestList", myTestList);
//将List<Exam>列表数据按照模板文件中的格式生成到scoreOutput.xls文件中
JxlsHelper.getInstance().processTemplate(in, out, context);
//下面步骤为浏览器下载部分
//指定数据生成后的文件输入流(将上述out的路径作为文件的输入流)
FileInputStream fileInputStream = new FileInputStream("target/classes/excel/aaaa.xls");
//导出excel文件,设置文件名
String filename = URLEncoder.encode("test信息.xls", "UTF-8");
//设置下载头
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
ServletOutputStream outputStream = response.getOutputStream();
//将文件写入浏览器
byte[] bys = new byte[fileInputStream.available()];
fileInputStream.read(bys);
outputStream.write(bys);
outputStream.flush();
outputStream.close();
fileInputStream.close();
if (file.exists()) {
file.delete();
}
} catch (Exception e) {
e.printStackTrace();
}
}
转载自添加链接描述
导入excel数据功能的话
第一步:先确定好Excel导入的格式以及各表格字段值的含义
第二步:定义好解析的XML–videoConfig.xml;
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="Sheet1">
<section startRow="0" endRow="0"/>
<loop startRow="1" endRow="1" items="videoInfoList" var="videoInfo" varType="com.iflytek.weike.job.bo.VideoInfo">
<section startRow="1" endRow="1">
<mapping row="1" col="0">
videoInfo.index</mapping>
<mapping row="1" col="1">videoInfo.videoName</mapping>
<mapping row="1" col="2">videoInfo.resourceId</mapping>
<mapping row="1" col="3">videoInfo.upload</mapping>
<mapping row="1" col="4">videoInfo.content</mapping>
<mapping row="1" col="5">videoInfo.schoolName</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
第三步:生成一下解析的实体类PipSysAccount(这个需要根据excel文件的列去手工写一个)
第四步:Controller 接受一个excel文件,然后返回excel的数据
public List<PipSysAccount> setExcel(MultipartFile file) {
List<PipSysAccount> videoInfoList = new ArrayList<PipSysAccount>();
try {
InputStream inputXML = new BufferedInputStream(getClass().getClassLoader().getResourceAsStream("videoConfig.xml"));
XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
InputStream inputXLS = new BufferedInputStream(file.getInputStream());
PipSysAccount videoInfo = new PipSysAccount();
Map<String, Object> beans = new HashMap<String, Object>();
beans.put("videoInfo", videoInfo);
beans.put("videoInfoList", videoInfoList);
XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
if (readStatus.isStatusOK()) {
System.err.println("jxls读取Excel成功!");
}
} catch (Exception e) {
System.err.println("格式错误");
}
return videoInfoList;
}
xml读取excel有可能会涉及的一些问题
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="基础基213">
<section startRow="0" endRow="5"/> 标题等有6行
<loop startRow="1" endRow="2" items="videoInfoList" var="videoInfo" varType="com.lmbx.edu.data.common.domain.PipSysAccount"> 两行数据对应一个对象时将endRow改成2`在这里插入代码片`
<section startRow="1" endRow="2">
<mapping row="1" col="0">videoInfo.id</mapping>
<mapping row="1" col="1">videoInfo.loginName</mapping>
<mapping row="1" col="2">videoInfo.status</mapping>
<mapping row="1" col="3">videoInfo.createDatetime</mapping>
<mapping row="1" col="5">videoInfo.loginName</mapping>
<mapping row="2" col="5">videoInfo.pwd</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>