0.pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.6</version> </dependency>
1.Excel工具类
public class Excel { public static HSSFWorkbook getExcel(String sheetName, String []title, List<String[]> values, HSSFWorkbook wb){ //创建HSSFWorkbook if(wb == null){ wb = new HSSFWorkbook(); } // 居中格式 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); //在sheet里创建第一行大标题, HSSFRow row=sheet.createRow(0); HSSFCell cell=row.createCell(0); cell.setCellValue(sheetName); cell.setCellStyle(style); //设置合并单元格内容 sheet.addMergedRegion(new CellRangeAddress(0,0,0,title.length-1)); //创建标题 row=sheet.createRow(1); for(int i=0;i<title.length;i++){ cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } //输入内容 for(int i=0;i<values.size();i++){ row = sheet.createRow(i+2); for(int j=0;j<title.length;j++){ row.createCell(j).setCellValue(values.get(i)[j]); } } return wb; } }
2.Controller
@GetMapping("DownloadWarehouseExcel") public ResponseEntity<byte[]> DownloadWarehouseExcel(HttpServletResponse response) throws IOException { GetTime getTime=new GetTime();//时间工具类 //excel表格数据 List<Warehouse> warehouses = this.warehouseService.selWarehouseByTime(getTime.getYear() + "年"); //excel标题 String[] title = {"姓名","时间","原因","金额"}; //excel文件名 String fileName = "仓库损耗表-"+getTime.getYMD()+".xls";//getTime.getYMD()为获取当前年月日 //sheet名 String sheetName = "仓库损耗表"; ArrayList<String[]> list= new ArrayList<String[]>(); for (int i = 0; i <warehouses.size(); i++) { String [] strings=new String[title.length]; strings[0] = warehouses.get(i).getD_name(); strings[1] = warehouses.get(i).getTime(); strings[2] = warehouses.get(i).getSituation(); strings[3] = warehouses.get(i).getMoney().toString(); list.add(strings); } //创建HSSFWorkbook HSSFWorkbook wb = Excel.getExcel(sheetName, title, list, null); ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); byte[] bytes=bos.toByteArray(); HttpHeaders httpHeaders = new HttpHeaders(); httpHeaders.setAccessControlExposeHeaders(Collections.singletonList("Content-Disposition")); httpHeaders.add("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8")); ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(bytes,httpHeaders, HttpStatus.OK); return responseEntity; }
3.注意
获取文件名中文乱码问题(转自:https://blog.csdn.net/qq_38098125/article/details/87165351)
后 filename:URLEncoder.encode(fileName, "UTF-8")
前 decodeURI(resp.headers['content-disposition'].split(";")[1].split("filename=")[1]);
可以解决中文乱码问题