package com.ds.crawler.search.service.thirdParty;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.Date;import java.util.List;import com.ds.model.CrawlerModel;import com.ds.model.CrawlerResultModel;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExportToExcel { public static Boolean exportToExcel(List products){ // 创建工作簿对象 XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作表对象 Sheet sheet = workbook.createSheet("products"); // 创建表头行 Row header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("site"); header.createCell(2).setCellValue("name"); header.createCell(3).setCellValue("price"); header.createCell(4).setCellValue("unit"); header.createCell(5).setCellValue("time"); header.createCell(6).setCellValue("source"); header.createCell(7).setCellValue("type"); header.createCell(8).setCellValue("size"); header.createCell(9).setCellValue("color"); header.createCell(10).setCellValue("img"); header.createCell(11).setCellValue("material"); header.createCell(12).setCellValue("rank"); // 填充数据 int rowNum = 1; for (CrawlerModel product : products) { Row row = sheet.createRow(rowNum); row.createCell(0).setCellValue(rowNum); row.createCell(1).setCellValue(product.getSite()==null?"":product.getSite()); row.createCell(2).setCellValue(product.getName()==null?"":product.getName()); row.createCell(3).setCellValue(((product.getPrice()==null?"":product.getPrice().toString()))); row.createCell(4).setCellValue(product.getUnit()==null?"":product.getUnit()); row.createCell(5).setCellValue(product.getTime()==null?"":product.getTime()); row.createCell(6).setCellValue(product.getSource()==null?"":product.getSource()); row.createCell(7).setCellValue(product.getType()==null?"":product.getType()); row.createCell(8).setCellValue(product.getSize()==null?"":product.getSize().toString()); row.createCell(9).setCellValue(product.getColor()==null?"":product.getColor().toString()); row.createCell(10).setCellValue(product.getImg()==null?"":product.getImg().toString()); row.createCell(11).setCellValue(product.getMaterial()==null?"":product.getMaterial()); row.createCell(12).setCellValue(product.getRank()==null?"":product.getRank().toString()); rowNum++; } // 将数据写入Excel文件 FileOutputStream outputStream = null; try { outputStream = new FileOutputStream("products.xlsx"); workbook.write(outputStream); workbook.close(); outputStream.close(); return true; } catch (IOException e) { throw new RuntimeException(e); } }}
之后就会看到到处的excel文件了:
package com.ds.crawler.search.controller;import com.ds.common.exception.BizCodeEnume;import com.ds.common.exception.RRException;import com.ds.common.result.Result;import com.ds.crawler.search.service.MallSearchService;import com.ds.crawler.search.service.thirdParty.ExportToExcel;import com.ds.model.CrawlerResultModel;import com.ds.model.SearchParam;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.CrossOrigin;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileInputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import javax.servlet.http.HttpServletResponse;import org.springframework.http.HttpHeaders;import org.springframework.http.HttpStatus;import org.springframework.http.MediaType;import org.springframework.http.ResponseEntity;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;@Slf4j@RestController@RequestMapping("/search/product")@CrossOriginpublic class ExportConroller { @Autowired MallSearchService mallSearchService; @GetMapping("/export") public ResponseEntity export(SearchParam param, HttpServletResponse response) throws IOException {// 1、 根据要求查询数据 CrawlerResultModel result = mallSearchService.search(param); if (result!=null&&result.getProduct()!=null){// 2、 将数据导入excel Boolean b = ExportToExcel.exportToExcel(result.getProduct());// 3、转为字节流返回给前端 if(b){ File file = new File("E:\\GonZuoShi\\java\\crawler\\crawler-es\\products.xlsx"); // 将Excel文件读取到字节数组中 FileInputStream fileInputStream = new FileInputStream(file); byte[] bytes = new byte[(int) file.length()]; fileInputStream.read(bytes); fileInputStream.close(); // 设置响应头 HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel")); headers.setContentDispositionFormData("attachment", "products.xlsx"); // 返回响应实体 return ResponseEntity.ok()// .headers(headers)// .contentLength(bytes.length) .body(bytes); }else{ //抛出异常 throw new RRException(BizCodeEnume.EXPORT_TO_EXCEPTION.getMsg(),BizCodeEnume.EXPORT_TO_EXCEPTION.getCode()); } }else{ //抛出异常 throw new RRException(BizCodeEnume.EXPORT_TO_EXCEPTION.getMsg(),BizCodeEnume.EXPORT_TO_EXCEPTION.getCode()); } }}
这样前端就收会收到一个二进制的文件流
使用axios 请求:
创建request:
exportExcel(data){ return request{ url:'....', port:get, data:data, responseType:'arraybuffer', }}
调用:
proxy.$api.downloadcode({site:store.state.siteName}).then(res=>{ const link=document.createElement('a'); // let blob = new Blob([res.data],{type: 'applicationnd.ms-excel'}); //如果后台返回的不是blob对象类型,先定义成blob对象格式 try { let blob = new Blob([res.data], { type: 'application/octet-stream' }) //如果后台返回的直接是blob对象类型,直接获取数据 // let _fileName = res.headers['content-disposition'].split(';')[1].split('=')[1]; //拆解获取文件名, link.style.display='none'; // 兼容不同浏览器的URL对象 const url = window.URL || window.webkitURL || window.moxURL; link.href=url.createObjectURL(blob); link.download =`${store.state.siteName}.xlsx`; //下载的文件名称 link.click(); window.URL.revokeObjectURL(url); // #URL.revokeObjectURL()方法会释放一个通过URL.createObjectURL()创建的对象URL. 当你要已经用过了这个对象URL,然后要让浏览器知道这个URL已经不再需要指向对应的文件的时候,就需要调用这个方法. }catch(e){ console.log('下载的文件出错',e) } })
来源地址:https://blog.csdn.net/qq_63946922/article/details/129969894