项目中如何下载销售榜单到指定excel表单中
功能展示：
选择指定的年份和月份：
点击下载之后：
如何实现：
第一步pom.xml中导入对应的excel相关依赖：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
第二步需要查找到某年某月份的订单：这里创建了ProductList对象
因为本项目设计到product对象，order对象，orderitem对象，各自对应的数据库：
product对象：
order对象：
orderitem对象：
SQL语句查询，返回封装成ProductList
<select id="findProductSalList" resultType="ProductList">
SELECT products.`name`,SUM(orderitem.buynum) salnum
FROM products,orders,orderitem
where orders.paystate=1
AND orders.id = orderitem.order_id
AND orderitem.product_id=products.id
AND YEAR(ordertime) = #{year}
AND MONTH(ordertime) = #{month}
GROUP BY products.id
ORDER BY salnum DESC
</select>
对应的结果放在
List<ProductList> plists =adminProductService.findProductSalList(year,month);
第三步把查到的结果封装到excel中（关键所在）
//销售榜单
@RequestMapping("/download")
public void download(String year, String month, HttpServletResponse response,HttpServletRequest request) throws IOException {
List<ProductList> plists =adminProductService.findProductSalList(year,month);
for (ProductList plist : plists) {
System.out.println(plist);
}
String filename = year + "年" +month + "月销售榜单";
String sheetName = month + "月销售榜单";
String titleName = year + "年" + month +"月销售榜单";
String[] columnName = {"商品名称","商品销量"};
String[][] dataList = new String[plists.size()][2];
for (int i=0;i<plists.size();i++){
dataList[i][0] = plists.get(i).getName();
dataList[i][1] = plists.get(i).getSalnum();
}
//创建excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//创建excel中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
//创建sheet的第一行
HSSFRow row1 = sheet.createRow(0);
//创建第一行的第一个单元格
HSSFCell cell =row1.createCell(0);
//合并第一行的两个单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
//给第一行的第一个合并后的单元格赋值
cell.setCellValue(titleName);
//创建第二行
HSSFRow row = sheet.createRow(1);
for (int i = 0; i < 2 ; i++){
row.createCell(i).setCellValue(columnName[i]);
}
//创建数据行
for (int i=0;i<dataList.length;i++){
row = sheet.createRow(i+2);
for(int j=0;j<2;j++){
row.createCell(j).setCellValue(dataList[i][j]);
}
}
filename = filename +".xls";
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("content-Disposition","attachment;filename="+getFilename(request,filename));
OutputStream out = response.getOutputStream();
wb.write(out);
}
对下载是编码格式的设置：
private String getFilename(HttpServletRequest request, String filename) throws UnsupportedEncodingException {
//IE
String[] IEBrowserKeyWords={"MSIE","Trident","Edge"};
String userAgent=request.getHeader("User-Agent");
for (String keyWord:IEBrowserKeyWords){
if(userAgent.contains(keyWord)){
return URLEncoder.encode(filename,"UTF-8");
}
}
//其他
return new String(filename.getBytes("UTF-8"),"ISO-8859-1");
}
