Java代码生成并下载Excel文件

水深无声 2022-05-24 14:05 301阅读 0赞

/**
    	 * 销售榜单
    	 * @param year
    	 * @param month
    	 * @param response
    	 * @throws IOException
    	 */
    	@RequestMapping("/download.do")
    	public void downloadProduct(String year,String month,
    			HttpServletResponse response,HttpServletRequest request) throws IOException {
    		//根据年份、月份查询出销售榜单
    		List<Dow> result = null;
    		if(year != "" && month != "") {
    			String time = year + "-" + month;
    			 result = adminService.selectProductByTime(time);	
    		}
    		if(year == "") {
    			result = adminService.selectProductByTime(month);
    		}
    		if(month == "") {
    			result = adminService.selectProductByTime(year);
    		}
    		System.out.println(result.size());
    		//定义文件名
    		String fileName = year + "年" + month + "月销售榜单.xls";
    		if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {  
    			fileName = URLEncoder.encode(fileName, "UTF-8");  
    		} else {  
    			fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");  
    		} 
    		//定义表名
    		String sheetName = month + "月销售榜单";
    		//定义表标题
    		String titleName = year + "年" + month + "月销售榜单";
    		//定义列名，即表头
    		String[] columnName = {"商品名称","销售数量"};
    		//创建二维数组，向其中写数据
    		int columnNum = 2;
    		String[][] dataList = new String[result.size()][columnNum];
    		for (int i = 0; i < result.size(); i++) {
    			Dow dow = result.get(i);
    			dataList[i][0] = dow.getName();
    			dataList[i][1] = dow.getSum();
    		}
    		//创建HSSFWorkbook对象，其对应一个Excel文件
    		HSSFWorkbook wb = new HSSFWorkbook();
    		//在wb中创建一个表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++) {
    			HSSFCell cell = row.createCell(i);
    			cell.setCellValue(columnName[i]);
    			System.out.println(columnName[i]);
    		}
    		//创建数据行，并写入数据
    		for (int i = 0; i < dataList.length; i++) {
    			HSSFRow row3 = sheet.createRow(i+2);
    			for (int j = 0; j < columnNum; j++) {
    				row3.createCell(j).setCellValue(dataList[i][j]);
    			}
    		}
    		//设置响应头
    		response.setContentType("application/ms-excel;charset=utf-8");
    		response.setHeader("content-Disposition", "attachment;filename="+fileName);
    		//向流中写入文件
    		ServletOutputStream out = response.getOutputStream();
    		wb.write(out);
    		//更新缓冲区
    		out.flush();
    		//关闭流
    		out.close();
    	}