当我们需要实时制作一个带有下拉框的Excel上传模板时,比如我们要上传员工信息,而上传那些信息又是有格式要求,此时就需要制作一个上传的Excel模板供用户下载,其中,岗位和部门又是实时更新的,故下拉框的内容又不固定,需要实时从数据库更新,我们的思路就是,每次下载时,都将数据更新到一个新的文档里面,然后再下载这个文档,用的io包括,先是制作表格存到本地服务器,然后就是根据路径将文档读取出来,用到了FileInputStream()流,具体看如下代码:
/**
* 模板下载
*
* @param request
*/
@RequestMapping(value = "/downloadTemplate")
public void download(HttpServletResponse response) {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder
.getRequestAttributes();
HttpServletRequest request = requestAttributes.getRequest();
// 文件名
String filename = "员工导入模板.xlsx";
try {
// 写到服务器上
String path = request.getSession().getServletContext().getRealPath("") + "/" + filename;
File name = new File(path);
// 创建写工作簿对象
WritableWorkbook workbook = Workbook.createWorkbook(name);
// 工作表
WritableSheet sheet = workbook.createSheet("员工导入模板", 0);
// 设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat = new WritableCellFormat(font);
// 设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
// 设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
// 设置文字居中对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 分别给1,2,3,4列设置不同的宽度;
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 15);
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 15);
// 给sheet电子版中所有的列设置默认的列的宽度;
sheet.getSettings().setDefaultColumnWidth(20);
// 给sheet电子版中所有的行设置默认的高度,高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了
// 设置自动换行;
cellFormat.setWrap(true);
// 给第二行设置背景、字体颜色、对齐方式等等;
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.RED);
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
// 设置文字居中对齐方式;
cellFormat2.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat2.setBackground(Colour.WHITE);
cellFormat1.setBackground(Colour.WHITE);
cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
cellFormat2.setWrap(true);
// 记录行数
int n = 1;
// 获取当前的合作机构ID
Long partnerId = (Long) request.getSession().getAttribute("partnerId");
// 获取得到所有部门信息
List<Department> deptList = departmentService.selectAll(partnerId);
// 获取得到所有岗位信息
List<Post> postList = postService.selectAll(partnerId);
List<String> deptName = new ArrayList<String>();
List<String> postName = new ArrayList<String>();
WritableCellFeatures wcf1 = new WritableCellFeatures();// 待选择集合对象,这是jxl的对象
WritableCellFeatures wcf2 = new WritableCellFeatures();// 待选择集合对象,这是jxl的对象
Label lt1 = new Label(0, 0, "工号", cellFormat2);
Label lt2 = new Label(1, 0, "姓名", cellFormat2);
Label lt3 = new Label(2, 0, "性别", cellFormat2);
Label lt4 = new Label(3, 0, "部门", cellFormat2);
Label lt5 = new Label(4, 0, "岗位", cellFormat2);
Label lt6 = new Label(5, 0, "1、添加员工部门、岗位的时候,按照员工数量的多少,将下拉框样式往下复制,然后点击选择下拉框中的信息,最后导入的时候将该句话删除即可",
cellFormat1);
Label lblColumn1 = new Label(0, 1, "xxx", cellFormat2);
Label lblColumn2 = new Label(1, 1, "xxx", cellFormat2);
Label lblColumn3 = new Label(2, 1, "xxx", cellFormat2);
Label lblColumn4 = new Label(3, 1, "请选择部门", cellFormat2);// 生成一个待选择的标签
Label lblColumn5 = new Label(4, 1, "请选择岗位", cellFormat2);// 生成一个待选择的标签
// 设置单元格
for (int i = 0; i < postList.size(); i++) {
postName.add(postList.get(i).getPostName());
}
// 设置单元格
for (int i = 0; i < deptList.size(); i++) {
deptName.add(deptList.get(i).getDepartmentName());
}
// 将部门集合导入到表格中去
wcf1.setDataValidationList(deptName);// 设置jxl对象要选择的集合
lblColumn4.setCellFeatures(wcf1);// 设置到单元格里面去
// 将岗位集合导入到表格中去
wcf2.setDataValidationList(postName);// 设置jxl对象要选择的集合
lblColumn5.setCellFeatures(wcf2);// 设置到单元格里面去
//以下是将我们上面设置的单元格加入到我们的表中去
sheet.addCell(lt1);
sheet.addCell(lt2);
sheet.addCell(lt3);
sheet.addCell(lt4);
sheet.addCell(lt5);
sheet.addCell(lt6);
sheet.addCell(lblColumn1);
sheet.addCell(lblColumn2);
sheet.addCell(lblColumn3);
sheet.addCell(lblColumn4);
sheet.addCell(lblColumn5);
// 开始执行写入操作,将该模板写入到具体的路径下面
workbook.write();
// 关闭流
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
OutputStream out = null;
try {
response.addHeader("content-disposition",
"attachment;filename=" + java.net.URLEncoder.encode(filename, "utf-8"));
// 2.下载
out = response.getOutputStream();
String path3 = request.getSession().getServletContext().getRealPath("") + "/" + filename;
// inputStream:读文件,前提是这个文件必须存在,要不就会报错
InputStream is = new FileInputStream(path3);
byte[] b = new byte[4096];
int size = is.read(b);
while (size > 0) {
out.write(b, 0, size);
size = is.read(b);
}
out.close();
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}