1、maven依赖
<!-- 导出Excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>2、Java实现通过浏览器下载
导出逻辑同下载逻辑相同，只需要在表格内插入导出数据。
（1）使用XSSFWorkbook创建表格
@ResponseBody
@RequestMapping(value = "/downloadTemplate", method = RequestMethod.GET)
public String downloadTemplate(HttpServletRequest request, HttpServletResponse response, String manufactureId) {
String info = "";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();// xls格式用HSSFWorkbook
// HSSFSheet
try {
if (!CommonUtil.isNotEmpty(manufactureId)) {
throw new Exception("param is null !");
}
//设置表头格式
XSSFCellStyle styleTitle = xssfWorkbook.createCellStyle();
styleTitle.setBorderBottom(BorderStyle.THIN); //下边框
styleTitle.setBorderLeft(BorderStyle.THIN);//左边框
styleTitle.setBorderTop(BorderStyle.THIN);//上边框
styleTitle.setBorderRight(BorderStyle.THIN);//右边框
XSSFFont font = xssfWorkbook.createFont();
font.setFontHeightInPoints((short) 12);// 字号
font.setColor(HSSFColor.RED.index);
styleTitle.setFont(font);
styleTitle.setWrapText(true);//自动换行
//设置单元格格式
XSSFCellStyle textStyle = xssfWorkbook.createCellStyle();
DataFormat format = xssfWorkbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));// 设置单元格格式为"文本"
textStyle.setBorderBottom(BorderStyle.THIN); //下边框
textStyle.setBorderLeft(BorderStyle.THIN);//左边框
textStyle.setBorderTop(BorderStyle.THIN);//上边框
textStyle.setBorderRight(BorderStyle.THIN);//右边框
textStyle.setVerticalAlignment(VerticalAlignment.CENTER);//居中
//第一个页签
XSSFSheet sheet1 = xssfWorkbook.createSheet();
sheet1.setDefaultColumnWidth(20);// 设置默认列宽，width为字符个数
//设置表格名称
xssfWorkbook.setSheetName(0, "离场申请模板");
// 创建表格标题行 第一行
XSSFRow titleRow = sheet1.createRow(0);
String fileName = "离场申请模板.xlsx";
String[] titles = {"编号\r\n（必填，从页签“已入场人员”中获取）",
"姓名\r\n（必填，从页签“已入场人员”中获取）",
"性别\r\n（必填，从页签“已入场人员”中获取）",
"离场时间\r\n（必填，格式：YYYY-MM-DD）",
"是否离职\r\n（必填）",
"离职原因\r\n（当是否离职为“是”时，必填）"};
for (int i = 0; i < titles.length; i++) {
titleRow.createCell(i).setCellValue(titles[i]);
titleRow.getCell(i).setCellStyle(styleTitle);
sheet1.setDefaultColumnStyle(i, textStyle);
}
//第二个页签含人员记录导出该厂商所有已入场状态的人员
XSSFSheet sheet2= xssfWorkbook.createSheet();
sheet2.setDefaultColumnWidth(20);// 设置默认列宽，width为字符个数
//设置表格名称
xssfWorkbook.setSheetName(1, "已入场人员");
// 创建表格标题行 第一行
XSSFRow titleRow2 = sheet2.createRow(0);
String[] titles2 = {"编号", "姓名", "性别"};
for (int i = 0; i < titles2.length; i++) {
titleRow2.createCell(i).setCellValue(titles2[i]);
titleRow2.getCell(i).setCellStyle(styleTitle);
sheet2.setDefaultColumnStyle(i, textStyle);
}
//获取该厂商下所有已入场的人员
List<Map<String, Object>> listMap = this.leaveApplicationService.getManufactureStaffByManufactureId(Long.parseLong(manufactureId));
for (int i = 0; i < listMap.size(); i++) {
XSSFRow row = sheet2.createRow(i+1);
Map<String, Object> order = listMap.get(i);
row.createCell(0).setCellValue(order.get("ID")+"");
row.getCell(0).setCellStyle(textStyle);
row.createCell(1).setCellValue(order.get("NAME")+"");
row.getCell(1).setCellStyle(textStyle);
String sex = order.get("SEX") + "";
if ("1".equals(sex)) {
row.createCell(2).setCellValue("男");
row.getCell(2).setCellStyle(textStyle);
} else if ("2".equals(sex)) {
row.createCell(2).setCellValue("女");
row.getCell(2).setCellStyle(textStyle);
}
}
// 浏览器下载
response.reset();//重置浏览器,清空输出流
//不同浏览器的编码设置
String downloadFileName = URLEncoder.encode(fileName, "UTF-8");
String agent = request.getHeader("User-Agent").toUpperCase();
if (agent.indexOf("MSIE") > 0 || agent.indexOf("EDGE") > 0
|| (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0)) {
response.setHeader("Content-disposition", "attachment; filename=" + downloadFileName);
} else {
response.setHeader("Content-disposition", "attachment; filename*=UTF-8''" + downloadFileName);
}
response.setCharacterEncoding("UTF-8");
// response.setContentType("application/vnd.ms-excel;charset=GBK");
xssfWorkbook.write(response.getOutputStream());
info = "success";
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage(), e);
request.setAttribute("error", "系统错误");
info = "系统错误!";
} finally {
if (xssfWorkbook != null) {
try {
xssfWorkbook.close();
xssfWorkbook = null;
} catch (IOException e) {
e.printStackTrace();
}
}
// 添加用户日志
systemUtilService.addLog("WorkReportAction.downloadTemplate", info);
}
return info;
}下面是从浏览器上下载后的效果图：
sheet1：
sheet2：
（2）从服务器上下载
首先要把模板文件放在服务器相应的目录下，然后在配置文件中配置该路径：
获取配置文件：
/**
* 获取配置文件
* @param path
* @return Properties
*/
public static Properties getProps(String path) {
InputStream is = CmUtil.class.getResourceAsStream(path);
Properties props = new Properties();
try {
props.load(is);
} catch (IOException e) {
e.printStackTrace();
log.error(e.getMessage(),e);
//throw new CommonException("加载配置文件错误");
}finally{
if(is!=null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
log.error("不能关闭输入文件",e);
//throw new CommonException("关闭配置文件错误");
}
}
}
return props;
}从服务器上获取模板，然后通过浏览器下载：
@Override
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
OutputStream ops = null;
FileInputStream fis = null;
XSSFWorkbook xwb = null;
try {
String fileName = "离场申请模板.xlsx";
//获取模板在服务器上的地址
String downloadPath = CmUtil.getProps("/conf.properties").getProperty("LEAVEFILE_TEMPLATE_PATH").trim();
response.reset(); //重置浏览器，清空输出流
// 设置RESPONSE
String downloadFileName = URLEncoder.encode(fileName, "UTF-8");
String agent = request.getHeader("User-Agent").toUpperCase();
if (agent.indexOf("MSIE") > 0 || agent.indexOf("EDGE") > 0
|| (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0)) {
response.setHeader("Content-Disposition", "attachment; filename=" + downloadFileName);
} else {
response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + downloadFileName);
}
response.setCharacterEncoding("UTF-8");
// response.setContentType("application/vnd.ms-excel;charset=GBK");
String filePath = downloadPath + fileName;
fis = new FileInputStream(filePath);
xwb = new XSSFWorkbook(fis);
fis.close();
// 输出流
ops = response.getOutputStream();
xwb.write(ops);
} catch (Exception e) {
e.printStackTrace();
response.reset();
try {
OutputStreamWriter writer = new OutputStreamWriter(response.getOutputStream(), "UTF-8");
String data = "<script language='javascript'>alert(\"\\u64cd\\u4f5c\\u5f02\\u5e38\\uff01\");</script>";
writer.write(data);
writer.close();
} catch (IOException e1) {
e1.printStackTrace();
}
} finally {
if (null != fis) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != xwb) {
try {
xwb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != ops) {
try {
ops.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
