BookStore销售榜单文档下载

先导入jar包

1.Download.jsp页面

把表单提交到download.do处理器方法

<form id=“Form1” name=“Form1”action=${pageContext.request.contextPath}/admin/products/download.do”method=“post”>

2.handler处理器方法

@RequestMapping(“/download.do”)public void download(String year,String month,

HttpServletResponse response,

HttpSession session,

HttpServletRequest request) throws IOException

{

List<ProductList> plist = adminProductService.findProductList(year,month);

String fileName= year+”年” +month +”月销售榜单.xls”;

String sheetName=month+”月销售榜单”;

String titleName= year+”年” +month +”月销售榜单”;

String[]columnName={“名称”,”数量”};

int columnNum=2;

String[][]dataList = new String [plist.size()][columnNum];

for(int i=0;i<plist.size();i++){

ProductList pl = plist.get(i);

dataList[i][0]= pl.getName();

dataList[i][1]=pl.getSalnum();

}

//对应一个Excel文件

HSSFWorkbook wbnew HSSFWorkbook();

//创建一个sheet

HSSFSheet  sheet = wb.createSheet(sheetName);

//创建第一行

HSSFRow row1 =sheet.createRow(0);

//创建单元格

HSSFCell cell1 =  row1.createCell(0);

//合并单元格

sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));

cell1.setCellValue(titleName);

//创建第二行

HSSFRow row =sheet.createRow(1);

for(int i=0;i<columnNum;i++){

row.createCell(i);

HSSFCell cell= row.createCell(i);

cell.setCellValue(columnName[i]);

}

//创建数据行

for(int i=0;i<dataList.length;i++){

row = sheet.createRow(i+2);

HSSFCell datacell=null;

for(int j=0;j<columnNum;j++)

{

datacell=row.createCell(j);

datacell.setCellValue(dataList[i][j]);

}

}

//文件下载

response.setContentType(“application/ms-excel;charset=utf-8”);

response.setHeader(“content-Disposition”, “attachment;filename=”+processFileName(request,fileName));

OutputStream out = response.getOutputStream();

wb.write(out);

}

3.service接口

    List<ProductList> findProductList(String year, String month);

4.servic实现

@Overridepublic List<ProductList> findProductList(String year, String month) {

Map map = new HashMap<>();

map.put(“year”year);

map.put(“month”month);

return adminProductDao.selectProductList(year,month);

}

5.dao层接口

List<ProductList> selectProductList(String year, String month);

6.sql语句

<select id=“selectProductList” resultType=“ProductList”>select products.name,SUM(orderitem.buynum) salnum

from products,orders,orderitem

where orders.paystate=1

and orderitem.order_id=orders.id

and products.id=orderitem.product_id

group by products.id

order by salnum desc

</select>

7.ProductList实体类

public class ProductList {private String name;

private String salnum;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getSalnum() {

return salnum;

}

public void setSalnum(String salnum) {

this.salnum = salnum;

}

}

8.解决IE Chrome FireFox中文乱码问题

   private String processFileName(HttpServletRequest request, String fileNames) {String codedfilename = null;

try {

String agent = request.getHeader(“USER-AGENT”);

if (null != agent && -1 != agent.indexOf(“MSIE”) || null != agent

&& -1 != agent.indexOf(“Trident”)) {// ie

String name = java.net.URLEncoder.encode(fileNames, “UTF8”);

codedfilename = name;

else if (null != agent && -1 != agent.indexOf(“Mozilla”)) {// 火狐,chrome

codedfilename = new String(fileNames.getBytes(“UTF-8”), “iso-8859-1”);

}

catch (Exception e) {

e.printStackTrace();

}

return codedfilename;

}

发表评论