后台数据download成excel的方法（controller/action）
2024-08-22 12:13:32
jsp页面端
<a href="/portal/server/importExec" title="Data Download">
<img src="${pageContext.request.contextPath}/style/images/excel6.jpg" width=20px height=20px style="padding-top:15px"/>
</a>
controller端处理
@RequestMapping(value = "importExec", method = RequestMethod.GET)
@ResponseBody
public void importExec(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
String fname = "serverlist";
response.reset();// 清空输出流
response.setHeader("Content-disposition","attachment; filename=" + fname + ".xls");// 设定输出文件头
response.setContentType("application/msexcel");//EXCEL格式 Microsoft excel
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFFont f = workbook.createFont();
// f.setColor(HSSFColor.RED.index);
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建sheet页
HSSFSheet sheet = workbook.createSheet("Server Info.");
//创建单元格
HSSFRow row = sheet.createRow(0);
HSSFCell c0 = row.createCell(0);
c0.setCellValue(new HSSFRichTextString("No"));
c0.setCellStyle(style); HSSFCell c1 = row.createCell(1);
c1.setCellValue(new HSSFRichTextString("IP"));
c1.setCellStyle(style); HSSFCell c2 = row.createCell(2);
c2.setCellValue(new HSSFRichTextString("Server Name"));
c2.setCellStyle(style); HSSFCell c3 = row.createCell(3);
c3.setCellValue(new HSSFRichTextString("Server Usage"));
c3.setCellStyle(style); HSSFCell c4 = row.createCell(4);
c4.setCellValue(new HSSFRichTextString("CPU"));
c4.setCellStyle(style); HSSFCell c5 = row.createCell(6);
c5.setCellValue(new HSSFRichTextString("Memory"));
c5.setCellStyle(style); HSSFCell c6 = row.createCell(8);
c6.setCellValue(new HSSFRichTextString("HDD"));
c6.setCellStyle(style); HSSFCell c7 = row.createCell(11);
c7.setCellValue(new HSSFRichTextString("OS Version"));
c7.setCellStyle(style); HSSFCell c8 = row.createCell(12);
c8.setCellValue(new HSSFRichTextString("Manager"));
c8.setCellStyle(style); HSSFRow row1 = sheet.createRow(1);
HSSFCell c9 = row1.createCell(4);
c9.setCellValue(new HSSFRichTextString("Count"));
c9.setCellStyle(style);
HSSFCell c10 = row1.createCell(5);
c10.setCellValue(new HSSFRichTextString("Core Num."));
c10.setCellStyle(style);
HSSFCell c11 = row1.createCell(6);
c11.setCellValue(new HSSFRichTextString("Count"));
c11.setCellStyle(style);
HSSFCell c12 = row1.createCell(7);
c12.setCellValue(new HSSFRichTextString("Size (GB)"));
c12.setCellStyle(style);
HSSFCell c13 = row1.createCell(8);
c13.setCellValue(new HSSFRichTextString("Count"));
c13.setCellStyle(style);
HSSFCell c14 = row1.createCell(9);
c14.setCellValue(new HSSFRichTextString("Type"));
c14.setCellStyle(style);
HSSFCell c15 = row1.createCell(10);
c15.setCellValue(new HSSFRichTextString("Size (GB)"));
c15.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)0, (short)0));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)1, (short)1));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)2, (short)2));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)3, (short)3));
sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)4, (short)5));
sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)6, (short)7));
sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)8, (short)10));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)11, (short)11));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)12, (short)12)); List<Server> serverList = serverService.findServerList("");
for(int i=0;i<serverList.size();i++){
row=sheet.createRow((int)i+2);
Server server = (Server)serverList.get(i);
row.createCell((short)0).setCellValue(new HSSFRichTextString(i+1+""));
row.createCell((short)1).setCellValue(new HSSFRichTextString(server.getIp()));
row.createCell((short)2).setCellValue(new HSSFRichTextString(server.getName()));
row.createCell((short)3).setCellValue(new HSSFRichTextString(server.getUseFor()));
row.createCell((short)4).setCellValue(new HSSFRichTextString(String.valueOf(server.getCpuCount())));
row.createCell((short)5).setCellValue(new HSSFRichTextString(server.getCpuNumber()+""));
row.createCell((short)6).setCellValue(new HSSFRichTextString(server.getMemCount()+""));
row.createCell((short)7).setCellValue(new HSSFRichTextString(server.getMemSize()));
row.createCell((short)8).setCellValue(new HSSFRichTextString(server.getHddCount()+""));
row.createCell((short)9).setCellValue(new HSSFRichTextString(server.getHddType()));
row.createCell((short)10).setCellValue(new HSSFRichTextString(server.getHddSize()));
row.createCell((short)11).setCellValue(new HSSFRichTextString(server.getOsVersion()));
row.createCell((short)12).setCellValue(new HSSFRichTextString(server.getManager()));
} try{
workbook.write(response.getOutputStream());
}
catch (Exception e){
e.printStackTrace();
}
}
action的处理方法：
jsp端
<a href="DownDefectServlet?projectname=<%=request.getParameter("projectname")%>&item=<%=request.getParameter("item")%>" title="Data Download">
<img src="${pageContext.request.contextPath}/images/excel6.jpg" width=20px height=20px style="padding-top:15px"/>
</a>
web.xml加入
<servlet>
<servlet-name>DownDefectServlet</servlet-name>
<servlet-class>net.nw.servlet.DownDefectServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DownDefectServlet</servlet-name>
<url-pattern>/DownDefectServlet</url-pattern>
</servlet-mapping>
后台servlet处理
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { //int currpage = Integer.parseInt(request.getParameter("currpage")==null?"1":request.getParameter("currpage"));
String projectname=request.getParameter("projectname");
String item=request.getParameter("item");
String where=null;
int total=0;
if ("".equals(item)){
where="where PROJECTNAME like '%"+projectname+"%' AND STATUS != 'PLM_Deleted' and STATUS != 'Not_Related' and PLMFLAG='Y'" ;
}
else { //Opened
where="where PROJECTNAME like '%"+projectname+"%' AND STATUS != 'PLM_Deleted' AND STATUS != 'Closed' AND STATUS != 'Resolved' and STATUS != 'Not_Related' and PLMFLAG='Y'" ;
} response = ServletActionContext.getResponse();
String fname = "defectlist";
response.reset();// 清空输出流
response.setHeader("Content-disposition","attachment; filename=" + fname + ".xls");// 设定输出文件头
response.setContentType("application/msexcel");//EXCEL格式 Microsoft excel
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFFont f = workbook.createFont();
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //创建sheet页
HSSFSheet sheet = workbook.createSheet("Defect List");
//创建单元格
HSSFRow row = sheet.createRow(0);
HSSFCell c0 = row.createCell(0);
c0.setCellValue(new HSSFRichTextString("ID"));
c0.setCellStyle(style);
HSSFCell c1 = row.createCell(1);
c1.setCellValue(new HSSFRichTextString("Headline"));
c1.setCellStyle(style);
HSSFCell c2 = row.createCell(2);
c2.setCellValue(new HSSFRichTextString("Priority"));
c2.setCellStyle(style);
HSSFCell c3 = row.createCell(3);
c3.setCellValue(new HSSFRichTextString("ModelCode"));
c3.setCellStyle(style);
HSSFCell c4 = row.createCell(4);
c4.setCellValue(new HSSFRichTextString("Sub Component Name"));
c4.setCellStyle(style);
HSSFCell c5 = row.createCell(5);
c5.setCellValue(new HSSFRichTextString("Plat. Dev."));
c5.setCellStyle(style);
HSSFCell c6 = row.createCell(6);
c6.setCellValue(new HSSFRichTextString("Prod. Dev."));
c6.setCellStyle(style);
HSSFCell c7 = row.createCell(7);
c7.setCellValue(new HSSFRichTextString("Defect Solved Ver."));
c7.setCellStyle(style);
HSSFCell c8 = row.createCell(8);
c8.setCellValue(new HSSFRichTextString("Requester"));
c8.setCellStyle(style);
HSSFCell c9 = row.createCell(9);
c9.setCellValue(new HSSFRichTextString("Status"));
c9.setCellStyle(style);
HSSFCell c10 = row.createCell(10);
c10.setCellValue(new HSSFRichTextString("State Owner"));
c10.setCellStyle(style); ResultSet rs=null; total=this.getResultCount_1(where);
rs = this.getResultSet_1(where);
int i = 0;
try {
while (rs.next()) {
row=sheet.createRow((int)++i);
row.createCell((short)0).setCellValue(new HSSFRichTextString(rs.getString("ID")));
row.createCell((short)1).setCellValue(new HSSFRichTextString(rs.getString("HEADLINE").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)2).setCellValue(new HSSFRichTextString(rs.getString("SERIOUSNESS").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)3).setCellValue(new HSSFRichTextString(rs.getString("MODELCODE").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)4).setCellValue(new HSSFRichTextString(rs.getString("SUBCOMPONENTNAME").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)5).setCellValue(new HSSFRichTextString(rs.getString("PLATFORMDEVELOPER").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)6).setCellValue(new HSSFRichTextString(rs.getString("PRODUCTDEVELOPER").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)7).setCellValue(new HSSFRichTextString(rs.getString("DEFECTSOLVEDVERSION").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)8).setCellValue(new HSSFRichTextString(rs.getString("REQUESTER").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)9).setCellValue(new HSSFRichTextString(rs.getString("STATUS").replaceAll("<", " <").replaceAll(">", " >")));
row.createCell((short)10).setCellValue(new HSSFRichTextString(rs.getString("STATEOWNER").replaceAll("<", " <").replaceAll(">", " >"))); }
rs.close();
}catch (SQLException e) {
System.out.println(e.getMessage());
} try {
workbook.write(response.getOutputStream());
}
catch (Exception e){
e.printStackTrace();
}
}
最新文章
- Nfs+Drdb+Heartbeat 数据存储高可用服务架构方案
- Css 知识点（不要删）
- css absolute和float，relative，z-index的同异
- dhtmlx相关
- Microsoft Azure News(3) Azure新的基本实例上线 (Basic Virtual Machine)
- AJAX-----07XMLHttpRequest对象的处理返回的JSON类型数据
- KSImageNamed-Xcode-master 对项目中图片提供自动提示功能的插件
- DeflateStream类
- 将Oracle数据库导出为txt格式
- iOS开发之自定义导航栏返回按钮右滑返回手势失效的解决
- [Shell]随机数
- eclipse建立一个jsp项目遇到的问题及解决
- c++中使用xercesc对xml进行schema校验
- [bzoj4866] [Ynoi2017]由乃的商场之旅
- equals方法和==的区别--用实例简单说明
- EF code first出现错误：列名 Discriminator 无效
- SVN客户端使用
- HDU 4813 Hard Code（水题，2013年长春现场赛A题）
- System.Web.Mvc.AJAX缺少程序集引用
- springmvc如何访问到静态的文件，如jpg,js,css
热门文章
- gulp4个基础API
- Lua入门基础
- Entity Framework Code First -- Migrations 迁移
- satisfy_dependencies_for: Cannot satisfy the following dependencies for kmod-usb-storage:
- setsockopt()用法（参数详细说明）（转）
- WildMagic 简单图元(一)
- digitalocean教程：你应该知道的10件事
- 大数据阶乘（The factorial of large data）
- Xcode中AutoLayOut的简单使用
- 高性能MySQL第2，3章性能相关 回顾笔记