List<SupplyStudentModel> studentModelList = new ArrayList<>();File fileNew = MultipartFileToFile(file); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream( fileNew)); XSSFSheet sheet = wb.getSheetAt(0); List<SupplyStudentModel> rowList = studentModelList.stream().collect(Collectors.toList()); List<List<SupplyStudentModel>> strList = new ArrayList<>(); for (SupplyStudentModel studentModel : rowList) { strList.add(new ArrayList() {{ this.add(studentModel); }}); } //2、写入数据 for (int i = 0; i < rowList.size(); i++) { List<SupplyStudentModel> stringList = strList.get(i); myWriteSheet(sheet, i + 1, stringList, 3, i); }// response传入参数
FileUtils.exportExcelMulSheet(response, "供应链传奇用户导入模板.xlsx", wb);
private void myWriteSheet(Sheet sh, int k, List<SupplyStudentModel> ps, int start, int c) { try { Map<String, CellStyle> smap = new HashMap(); //获取列表行 Row row1 = sh.getRow(start); //获取行高 short col1width = row1.getHeight(); Row row = sh.createRow(k); row.setHeight(col1width); for (int i = 0; i < ps.size(); i++) { // cell.setCellStyle(smap.get("normal"+i)); Cell cell1 = row.createCell(0); Cell cell2 = row.createCell(1); Cell cell3 = row.createCell(2); Cell cell4 = row.createCell(3); Cell cell5 = row.createCell(4); cell1.setCellValue(ps.get(i).getLoginName()); cell2.setCellValue(ps.get(i).getName()); cell3.setCellValue(ps.get(i).getPassword()); cell4.setCellValue(ps.get(i).getKssj()); cell5.setCellValue(ps.get(i).getJssj()); } } catch (Exception e) { e.printStackTrace(); } }
/** * MultipartFile 转 File * * @param multipartFile * @throws Exception */ private File MultipartFileToFile(MultipartFile multipartFile) { File file = null; //判断是否为null if (multipartFile.equals("") || multipartFile.getSize() <= 0) { return file; } //MultipartFile转换为File InputStream ins = null; OutputStream os = null; try { ins = multipartFile.getInputStream(); file = new File(multipartFile.getOriginalFilename()); os = new FileOutputStream(file); int bytesRead = 0; byte[] buffer = new byte[8192]; while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) { os.write(buffer, 0, bytesRead); } } catch (IOException e) { e.printStackTrace(); }finally { if(os != null){ try { os.close(); } catch (IOException e) { e.printStackTrace(); } } if(ins != null){ try { ins.close(); } catch (IOException e) { e.printStackTrace(); } } } return file; }public static void exportExcelMulSheet(HttpServletResponse response, String fileName, XSSFWorkbook workbook) throws Exception { ServletOutputStream out = null ; try { if(org.apache.commons.lang3.StringUtils.isBlank(fileName)){ //当前日期 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); fileName = sdf.format(new Date()); } response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); if (fileName.matches("^.+\\.(?i)(xls)$") || fileName.matches("^.+\\.(?i)(xlsx)$")) { fileName = fileName.substring( 0 ,fileName.lastIndexOf(".")); } response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")+".xlsx" ); out = response.getOutputStream(); workbook.write(out); out.flush(); } finally { out.close(); // workbook.close(); } }
导入表格数据时,重复数据sheet表格形式输出
![](https://img-home.csdnimg.cn/images/20240711042549.png)