本文已参与「新人创作礼」活动，一起开启掘金创作之路。
实现思路
- Excle的生成与下载 首先说一下那个在那个生成excle并给前台下载文件这块遇到的问题当时用的时异步请求文件后台已经生成了但是前台死活不能弹出下载文件最后发现是不能用异步请求用同步请求 或者是 window.location.href = 或者 超链接都是可以的
- 动态从数据库获取数据写入excle文件中导出动态模板 方法 首先设计导出模板 导出时去遍历模板设置excle的表头 查询模板中的字段设置别名
第一步
导入POI的maven
<!-- poi导出测试 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
第二步
写页面
<div class="layui-row" style="margin-left: 5%;margin-top: 5%" id="form">
<div class="layui-col-md10" style="padding: 30px;">
<form class="layui-form" action="${pageContext.request.contextPath}/user/exprotexcle">
<input type="text" name="type" value="1">
<div class="layui-form-item">
<div class="layui-input-block">
<button id="register" type="submit"style="margin-left: 22%" >提交</button>
</div>
</div>
</form>
</div>
第三步
后台接收处理
@RequestMapping("exprotexcle")
public void exprotexcle(String type,HttpServletResponse response) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException {
List<Map<Object,Object>> list =new ArrayList<Map<Object,Object>>();
//表头模板
String str="Class,Cron,Description,Status";
String[] split = str.split(",");
String sql ="cron_key as Class,cron_expression as Cron,task_explain as Description ,status as Status";
SpringScheduledCron sp =new SpringScheduledCron();
sp.setTaskexplain(sql);
//获取数据
list=springScheduledDao.seledate(sp);
//这里是一个临时字段将这个sql存到SpringScheduledCron实体对象 去后面赋值
/** xml的语句
<select id="finbyziduan" resultType="map"
parameterType="实体类">
select ${taskexplain} from
表
</select>
**/
Map<String, String> daoChu = Excle.daoChu(split, list);
Workbook work = null;
InputStream in = new FileInputStream(daoChu.get("path"));
OutputStream out = null;
try {
work=new HSSFWorkbook(in);
out = response.getOutputStream();
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(daoChu.get("name"), "UTF-8"))));
work.write(out);
//返回之后删除掉本地文件
File file2 = new File(daoChu.get("path"));
file2.delete();
} catch (IOException e) {
System.out.println("输入流错误");
e.printStackTrace();
} finally {
out.close();
}
}
效果图
导出工具类
package com.xk.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* excel动态导出数据
* @author Mr_xk
*
*/
public class Excle {
public static Map<String,String> daoChu(String[] fieldname,List<Map<Object,Object>> list) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
// 创建excel
Map<String,String> map = new HashedMap<String, String>();
OutputStream fOut = null;
HSSFWorkbook wk = new HSSFWorkbook();
// 创建一张工作表
HSSFSheet sheet = wk.createSheet();
// 写入表头
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
for (int i = 0; i < fieldname.length; i++) {
sheet.setColumnWidth(i,4000);
cell.setCellValue(fieldname[i]);
cell = row.createCell((short)i+1);
}
// 写入数据
for(int j=0;j<list.size();j++) {
row = sheet.createRow(j+1);
for(int i= 0;i<fieldname.length;i++) {
Object object=list.get(j).get(fieldname[i]);
row.createCell(i).setCellValue(object+"");
}
}
//判断是否存在目录. 不存在则创建
try {
String relativelyPath=System.getProperty("user.dir");
String timeName = TimeName();
String filename =timeName+".xls";
//输出Excel文件1
FileOutputStream output=new FileOutputStream(relativelyPath+"\\"+filename);
wk.write(output);//写入磁盘
output.close();
map.put("path", relativelyPath+"\\"+filename);
map.put("name", filename);
return map;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return map;
}
//文件名转换成时间戳
public static String TimeName() {
Calendar cal = Calendar.getInstance();
int year = cal.get(Calendar.YEAR);//获取年份
int month=cal.get(Calendar.MONTH)+1;//获取月份
int day=cal.get(Calendar.DATE);//获取日
int hour=cal.get(Calendar.HOUR);//小时
int minute=cal.get(Calendar.MINUTE);//分
int second=cal.get(Calendar.SECOND);//秒
String Nameto=year+""+month+""+day+""+hour+""+minute+""+second;
return Nameto;
}
}
