导入表格数据时，重复数据sheet表格形式输出

最新推荐文章于 2024-07-08 16:31:10 发布

wqr111

最新推荐文章于 2024-07-08 16:31:10 发布

阅读量371

点赞数

分类专栏： java基础文章标签： java servlet 开发语言

本文链接：https://blog.youkuaiyun.com/wqr111/article/details/129011105

版权

java基础专栏收录该内容

32 篇文章

订阅专栏

该代码段展示了如何从MultipartFile对象读取Excel文件，创建XSSFWorkbook对象，处理Sheet数据，并将SupplyStudentModel对象写入Excel。最后，它使用FileUtils.exportExcelMulSheet方法将处理后的Excel导出到响应流中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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();
      }

   }