创建一个excel文件,把生成的excel文件输出到浏览器(文件下载)
1)使用java生成excel文件:apache-poi ,iText
关于办公文档插件使用的基本思想:把办公文档的所有元素封装成普通的Java类, 程序员通过操作这些类达到操作办公文档目的。
文件---------HSSFWorkbook
页-----------HSSFSheet
行-----------HSSFRow
列-----------HSSFCell
样式---------HSSFCellStyle
使用apache-poi生成excel:
a)添加依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
b)使用封装类生成excel文件:
2)文件下载:
filedownloadtest.jsp
ActivityController
|->fileDownload()
*所有文件下载的请求只能发送同步请求
filedownloadtest.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <% String basePath=request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+request.getContextPath()+"/"; %> <html> <head> <base href="<%=basePath%>"> <meta charset="UTF-8"> <script type="text/javascript" src="jquery/jquery-1.11.1-min.js"></script> <title>演示文件下载</title> <script type="text/javascript"> $(function (){ //给下载按钮添加单击事件 $("#fileDownLoadBtn").click(function (){ //发送文件下载请求 只能发同步请求 window.location.href="workbench/activity/fileDownload.do"; }); }); </script> </head> <body> <input type="button" value="下载" id="fileDownLoadBtn"> </body> </html>
Controller
@RequestMapping("/workbench/activity/fileDownload.do") public void fileDownload(HttpServletResponse response) throws IOException { //设置响应类型 response.setContentType("application/octet-stream;charset=UTF-8"); //获取输出流 OutputStream out=response.getOutputStream(); //浏览器收到响应信息之后 默认情况下 直接在显示窗口打开响应信息:即使打不开 也 //会调用应用程序 只有实在打不开 才会激活文件 //设置响应头信息 是浏览器接受到响应信息 直接激活文件下载窗口 response.addHeader("Content-Disposition","attachment;filename=studentList.xls"); // 读取excel文件(InputStream) 把输出到浏览器(Outputstream) InputStream is=new FileInputStream("C:\\Users\\12720\\Desktop\\serverDir\\studentList.xls"); byte[] buff=new byte[256]; int len=0; while((len=is.read(buff))!=-1){ out.write(buff,0,len); } //关闭资源 is.close(); out.flush(); }
/*使用apache-poi 生成excel文件*/
public class CreateExcel {
public static void main(String[] args) throws IOException {
//HSSFWorkbook对象 对应一个excel文件
HSSFWorkbook wb=new HSSFWorkbook();
//使用wb创建 HSSFSheet对象 对应wb文件中的一页
HSSFSheet sheet = wb.createSheet("学生列表");
//使用sheet创建HSSFRow对象 对应sheet中的一行
HSSFRow row=sheet.createRow(0); //行号 从0开始 ,依次增加
//使用row创建HSSFCell对象 对应row中的列
HSSFCell cell = row.createCell(0);//列编号 从0开始
cell.setCellValue("学号");
cell = row.createCell(1);
cell.setCellValue("姓名");
cell = row.createCell(2);
cell.setCellValue("年龄");
//生成HSSFStyle对象
HSSFCellStyle style=wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
//使用sheet创建10个HSSFRow对象
for(int i=1;i<11;i++){
row = sheet.createRow(i);
cell= row.createCell(0);
cell.setCellValue(100+i);
cell = row.createCell(1);
cell.setCellValue("黄"+i);
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(20+i);
}
//调用工具函数生成excel文件
OutputStream os=new FileOutputStream("C:\\Users\\12720\\Desktop\\serverDir\\studentList.xls");
wb.write(os);
//关闭资源
os.close();
wb.close();
System.out.println("=========create ok ");
}
}
批量导出市场活动
客户端点击“批量导出”
给元素添加点击事件 (同步请求)
//给批量导出按钮添加单击事件 $("#exportActivityAllBtn").click(function (){ window.location.href="workbench/activity/exportAllActivitys.do"; });
mapper层
List<Activity> selectAllActivitys();<select id="selectAllActivitys" resultMap="BaseResultMap"> select a.id,u1.name as owner,a.name,a.start_date,a.end_date,a.cost,a.description,a.create_time, u2.name as create_by,a.edit_time,u3.name as edit_by from tbl_activity a join tbl_user u1 on a.owner=u1.id join tbl_user u2 on a.create_by=u2.id left join tbl_user u3 on a.edit_by=u3.id order by a.create_time desc </select>
service层
List<Activity> selectAllActivitys(); 接口public List<Activity> selectAllActivitys() { 实现类 return activityMapper.selectAllActivitys(); }Controller层 (调用service 查询activityList 创建excel文件 数据存入文件
文件下载 把生成的excel文件下载到客户端)
@RequestMapping("/workbench/activity/exportAllActivitys.do") public void exportAllActivitys(HttpServletResponse response) throws IOException { //调用service层方法 查询所有市场活动 List<Activity> activityList = activityService.selectAllActivitys(); //创建excel文件 ,并且activityList写入到excel文件中 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("市场活动列表"); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellValue("ID"); cell = row.createCell(1); cell.setCellValue("所有者"); cell = row.createCell(2); cell.setCellValue("名称"); cell = row.createCell(3); cell.setCellValue("开始日期"); cell = row.createCell(4); cell.setCellValue("结束日期"); cell = row.createCell(5); cell.setCellValue("成本"); cell = row.createCell(6); cell.setCellValue("描述"); cell = row.createCell(7); cell.setCellValue("创建时间"); cell = row.createCell(8); cell.setCellValue("创建者"); cell = row.createCell(9); cell.setCellValue("修改时间"); cell = row.createCell(10); cell.setCellValue("修改者"); //遍历activityList,创建HSSFRow对象,生成所有的数据行 if (activityList != null && activityList.size() > 0) { Activity activity = null; for (int i = 0; i < activityList.size(); i++) { activity = activityList.get(i); //每遍历一个activity 生成一行 row = sheet.createRow(i + 1); // 每一行创建11列 每一列数据从实体类获取 cell = row.createCell(0); cell.setCellValue(activity.getId()); cell = row.createCell(1); cell.setCellValue(activity.getOwner()); cell = row.createCell(2); cell.setCellValue(activity.getName()); cell = row.createCell(3); cell.setCellValue(activity.getStartDate()); cell = row.createCell(4); cell.setCellValue(activity.getEndDate()); cell = row.createCell(5); cell.setCellValue(activity.getCost()); cell = row.createCell(6); cell.setCellValue(activity.getDescription()); cell = row.createCell(7); cell.setCellValue(activity.getCreateTime()); cell = row.createCell(8); cell.setCellValue(activity.getCreateBy()); cell = row.createCell(9); cell.setCellValue(activity.getEditTime()); cell = row.createCell(10); cell.setCellValue(activity.getEditBy()); } } /*//根据wb对象生成excel文件 FileOutputStream os = new FileOutputStream( "C:\\Users\\12720\\Desktop\\serverDir\\activityList.xls"); wb.write(os); //关闭资源 os.close(); wb.close(); 把内存中的wb数据写入磁盘 */ //文件下载 把生成的excel文件下载到客户端 response.setContentType("application/octet-stream;charset=UTF8"); response.addHeader("Content-Disposition","attachment;filename=activityList.xls"); OutputStream out = response.getOutputStream(); //获取输出流 /* //读文件 InputStream is = new FileInputStream("C:\\Users\\12720\\Desktop\\serverDir\\activityList.xls"); //写 byte[] buff=new byte[256]; int len=0; while ((len=is.read(buff))!=-1){ out.write(buff,0,len); } is.close(); 把磁盘上的数据 输入到内存 Input */ wb.write(out); wb.close(); //把内存中的数据 直接输出到内存 给浏览器 out.flush(); }
浏览器激活文件下载窗口