代码功能:
在前端jsp页面上有一个链接或按钮,单击之后进入Controller层的接口,然后弹出弹框下载导出的Excel,最后将这个excel表格下载到客户端指定路径!
工具:IDEA
pom.xml添加依赖:
<dependency> <groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId> <version>1.2.54</version>
</dependency> <dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId> <version>3.4</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId> <artifactId>poi</artifactId>
<version>3.16</version> </dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId> <version>3.16</version>
</dependency> <dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId> <version>1.9.3</version>
</dependency>
Controller层接口代码:
@RequestMapping("/download") public void downstudents(HttpServletRequest
request, HttpServletResponse response,@RequestParam String startTime,
@RequestParam String endTime)throws IOException {
//我这是根据前端传来的起始时间来查询数据库里的数据,如果没有输入变量要求,保留前两个就行 String[] headers = { "ID", "主题",
"姓名", "手机","创建时间","开始时间","结束时间"};//导出的Excel头部,这个要根据自己项目改一下 List dataset =
videoMeetInfoMapper.selectByTime(startTime,endTime);//查询出来的数据,根据自己项目改一下
//下面的完全不动就行了(Excel数据中不包含图片) // 声明一个工作薄 HSSFWorkbook workbook = new
HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(); //
设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 18); HSSFRow row =
sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell
= row.createCell(i); HSSFRichTextString text = new
HSSFRichTextString(headers[i]); cell.setCellValue(text); } //遍历集合数据,产生数据行
Iterator it = dataset.iterator(); int index = 0; while (it.hasNext()) {
index++; row = sheet.createRow(index); demo t = (demo) it.next();
//利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields =
t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i); Field field = fields[i]; String fieldName =
field.getName(); String getMethodName = "get" + fieldName.substring(0,
1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{}); Object value =
getMethod.invoke(t, new Object[]{}); String textValue = null; if (value
instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new
SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); textValue = sdf.format(date); } else {
//其它数据类型都当作字符串简单处理 textValue = value.toString(); } HSSFRichTextString
richString = new HSSFRichTextString(textValue); HSSFFont font3 =
workbook.createFont(); font3.setColor(HSSFColor.BLUE.index);//定义Excel数据颜色
richString.applyFont(font3); cell.setCellValue(richString); } catch
(SecurityException e) { // TODO Auto-generated catch block e.printStackTrace();
} catch (NoSuchMethodException e) { // TODO Auto-generated catch block
e.printStackTrace(); } catch (IllegalArgumentException e) { // TODO
Auto-generated catch block e.printStackTrace(); } catch (IllegalAccessException
e) { // TODO Auto-generated catch block e.printStackTrace(); } catch
(InvocationTargetException e) { // TODO Auto-generated catch block
e.printStackTrace(); } } } response.setContentType("application/octet-stream");
response.setHeader("Content-disposition",
"attachment;filename=createList.xls");//默认Excel名称 response.flushBuffer();
workbook.write(response.getOutputStream()); } dataset =
videoMeetInfoMapper.selectByTime(startTime,endTime);//查询出来的数据,根据自己项目改一下
//下面的完全不动就行了(Excel数据中不包含图片) // 声明一个工作薄 HSSFWorkbook workbook = new
HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(); //
设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 18); HSSFRow row =
sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell
= row.createCell(i); HSSFRichTextString text = new
HSSFRichTextString(headers[i]); cell.setCellValue(text); } //遍历集合数据,产生数据行
Iterator it = dataset.iterator(); int index = 0; while (it.hasNext()) {
index++; row = sheet.createRow(index); demo t = (demo) it.next();
//利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields =
t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i); Field field = fields[i]; String fieldName =
field.getName(); String getMethodName = "get" + fieldName.substring(0,
1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{}); Object value =
getMethod.invoke(t, new Object[]{}); String textValue = null; if (value
instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new
SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); textValue = sdf.format(date); } else {
//其它数据类型都当作字符串简单处理 textValue = value.toString(); } HSSFRichTextString
richString = new HSSFRichTextString(textValue); HSSFFont font3 =
workbook.createFont(); font3.setColor(HSSFColor.BLUE.index);//定义Excel数据颜色
richString.applyFont(font3); cell.setCellValue(richString); } catch
(SecurityException e) { // TODO Auto-generated catch block e.printStackTrace();
} catch (NoSuchMethodException e) { // TODO Auto-generated catch block
e.printStackTrace(); } catch (IllegalArgumentException e) { // TODO
Auto-generated catch block e.printStackTrace(); } catch (IllegalAccessException
e) { // TODO Auto-generated catch block e.printStackTrace(); } catch
(InvocationTargetException e) { // TODO Auto-generated catch block
e.printStackTrace(); } } } response.setContentType("application/octet-stream");
response.setHeader("Content-disposition",
"attachment;filename=createList.xls");//默认Excel名称 response.flushBuffer();
workbook.write(response.getOutputStream()); }
浏览器输入链接测试一下:
localhost:8080/download?startTime=2017-07-26
8:57:59&endTime=2018-08-28 18:57:59(有无参数按项目情况而定)
显示截图如下,即可下载导出的Excel到客户端:
今日推荐