Java 上传Excel 并解析Excel

布满荆棘的人生 2022-05-15 03:57 678阅读 0赞

这个没什么好说的直接上代码了

调用方法

  1. FileUploadDto fileUploadDto = TransferExcelDateToFileUploadDtoUtil.TransferExcelDateToFileUploadDto(
  2.         request, 2, 8, 0);
  3. // 解析后返回List集合
  4. List<FileUploadBaseDto> baseDtos = fileUploadDto.getList();

Dto 上传解析的列

  1. package com.hand.utils.excel;
  4. public class FileUploadBaseDto {
  5.     private int rowNum; //行号
  7.     private String attribute1;
  9.     private String attribute2;
  11.     private String attribute3;
  13.     private String attribute4;
  15.     private String attribute5;
  17.     private String attribute6;
  19.     private String attribute7;
  21.     private String attribute8;
  23.     private String attribute9;
  25.     private String attribute10;
  27.     private String attribute11;
  29.     private String attribute12;
  31.     private String attribute13;
  33.     private String attribute14;
  35.     private String attribute15;
  37.     private String attribute16;
  39.     private String attribute17;
  41.     private String attribute18;
  43.     private String attribute19;
  45.     private String attribute20;
  46.     private String attribute21;
  47.     private String attribute22;
  48.     private String attribute23;
  49.     private String attribute24;
  50.     private String attribute25;
  51.     private String attribute26;
  52.     private String attribute27;
  53.     private String attribute28;
  54.     private String attribute29;
  55.     private String attribute30;
  56.     private String attribute31;
  57.     private String attribute32;
  58.     private String attribute33;
  59.     private String attribute34;
  60.     private String attribute35;
  61.     private String attribute36;
  62.     private String attribute37;
  63.     private String attribute38;
  64.     private String attribute39;
  65.     private String attribute40;
  67.     public int getRowNum() {
  68.         return rowNum;
  69.     }
  71.     public void setRowNum(int rowNum) {
  72.         this.rowNum = rowNum;
  73.     }
  75.     public String getAttribute1() {
  76.         return attribute1;
  77.     }
  79.     public void setAttribute1(String attribute1) {
  80.         this.attribute1 = attribute1;
  81.     }
  83.     public String getAttribute2() {
  84.         return attribute2;
  85.     }
  87.     public void setAttribute2(String attribute2) {
  88.         this.attribute2 = attribute2;
  89.     }
  91.     public String getAttribute3() {
  92.         return attribute3;
  93.     }
  95.     public void setAttribute3(String attribute3) {
  96.         this.attribute3 = attribute3;
  97.     }
  99.     public String getAttribute4() {
  100.         return attribute4;
  101.     }
  103.     public void setAttribute4(String attribute4) {
  104.         this.attribute4 = attribute4;
  105.     }
  107.     public String getAttribute5() {
  108.         return attribute5;
  109.     }
  111.     public void setAttribute5(String attribute5) {
  112.         this.attribute5 = attribute5;
  113.     }
  115.     public String getAttribute6() {
  116.         return attribute6;
  117.     }
  119.     public void setAttribute6(String attribute6) {
  120.         this.attribute6 = attribute6;
  121.     }
  123.     public String getAttribute7() {
  124.         return attribute7;
  125.     }
  127.     public void setAttribute7(String attribute7) {
  128.         this.attribute7 = attribute7;
  129.     }
  131.     public String getAttribute8() {
  132.         return attribute8;
  133.     }
  135.     public void setAttribute8(String attribute8) {
  136.         this.attribute8 = attribute8;
  137.     }
  139.     public String getAttribute9() {
  140.         return attribute9;
  141.     }
  143.     public void setAttribute9(String attribute9) {
  144.         this.attribute9 = attribute9;
  145.     }
  147.     public String getAttribute10() {
  148.         return attribute10;
  149.     }
  151.     public void setAttribute10(String attribute10) {
  152.         this.attribute10 = attribute10;
  153.     }
  155.     public String getAttribute11() {
  156.         return attribute11;
  157.     }
  159.     public void setAttribute11(String attribute11) {
  160.         this.attribute11 = attribute11;
  161.     }
  163.     public String getAttribute12() {
  164.         return attribute12;
  165.     }
  167.     public void setAttribute12(String attribute12) {
  168.         this.attribute12 = attribute12;
  169.     }
  171.     public String getAttribute13() {
  172.         return attribute13;
  173.     }
  175.     public void setAttribute13(String attribute13) {
  176.         this.attribute13 = attribute13;
  177.     }
  179.     public String getAttribute14() {
  180.         return attribute14;
  181.     }
  183.     public void setAttribute14(String attribute14) {
  184.         this.attribute14 = attribute14;
  185.     }
  187.     public String getAttribute15() {
  188.         return attribute15;
  189.     }
  191.     public void setAttribute15(String attribute15) {
  192.         this.attribute15 = attribute15;
  193.     }
  195.     public String getAttribute16() {
  196.         return attribute16;
  197.     }
  199.     public void setAttribute16(String attribute16) {
  200.         this.attribute16 = attribute16;
  201.     }
  203.     public String getAttribute17() {
  204.         return attribute17;
  205.     }
  207.     public void setAttribute17(String attribute17) {
  208.         this.attribute17 = attribute17;
  209.     }
  211.     public String getAttribute18() {
  212.         return attribute18;
  213.     }
  215.     public void setAttribute18(String attribute18) {
  216.         this.attribute18 = attribute18;
  217.     }
  219.     public String getAttribute19() {
  220.         return attribute19;
  221.     }
  223.     public void setAttribute19(String attribute19) {
  224.         this.attribute19 = attribute19;
  225.     }
  227.     public String getAttribute20() {
  228.         return attribute20;
  229.     }
  231.     public void setAttribute20(String attribute20) {
  232.         this.attribute20 = attribute20;
  233.     }
  235.     public String getAttribute21() {
  236.         return attribute21;
  237.     }
  239.     public void setAttribute21(String attribute21) {
  240.         this.attribute21 = attribute21;
  241.     }
  243.     public String getAttribute22() {
  244.         return attribute22;
  245.     }
  247.     public void setAttribute22(String attribute22) {
  248.         this.attribute22 = attribute22;
  249.     }
  251.     public String getAttribute23() {
  252.         return attribute23;
  253.     }
  255.     public void setAttribute23(String attribute23) {
  256.         this.attribute23 = attribute23;
  257.     }
  259.     public String getAttribute24() {
  260.         return attribute24;
  261.     }
  263.     public void setAttribute24(String attribute24) {
  264.         this.attribute24 = attribute24;
  265.     }
  267.     public String getAttribute25() {
  268.         return attribute25;
  269.     }
  271.     public void setAttribute25(String attribute25) {
  272.         this.attribute25 = attribute25;
  273.     }
  275.     public String getAttribute26() {
  276.         return attribute26;
  277.     }
  279.     public void setAttribute26(String attribute26) {
  280.         this.attribute26 = attribute26;
  281.     }
  283.     public String getAttribute27() {
  284.         return attribute27;
  285.     }
  287.     public void setAttribute27(String attribute27) {
  288.         this.attribute27 = attribute27;
  289.     }
  291.     public String getAttribute28() {
  292.         return attribute28;
  293.     }
  295.     public void setAttribute28(String attribute28) {
  296.         this.attribute28 = attribute28;
  297.     }
  299.     public String getAttribute29() {
  300.         return attribute29;
  301.     }
  303.     public void setAttribute29(String attribute29) {
  304.         this.attribute29 = attribute29;
  305.     }
  307.     public String getAttribute30() {
  308.         return attribute30;
  309.     }
  311.     public void setAttribute30(String attribute30) {
  312.         this.attribute30 = attribute30;
  313.     }
  315.     public String getAttribute31() {
  316.         return attribute31;
  317.     }
  319.     public void setAttribute31(String attribute31) {
  320.         this.attribute31 = attribute31;
  321.     }
  323.     public String getAttribute32() {
  324.         return attribute32;
  325.     }
  327.     public void setAttribute32(String attribute32) {
  328.         this.attribute32 = attribute32;
  329.     }
  331.     public String getAttribute33() {
  332.         return attribute33;
  333.     }
  335.     public void setAttribute33(String attribute33) {
  336.         this.attribute33 = attribute33;
  337.     }
  339.     public String getAttribute34() {
  340.         return attribute34;
  341.     }
  343.     public void setAttribute34(String attribute34) {
  344.         this.attribute34 = attribute34;
  345.     }
  347.     public String getAttribute35() {
  348.         return attribute35;
  349.     }
  351.     public void setAttribute35(String attribute35) {
  352.         this.attribute35 = attribute35;
  353.     }
  355.     public String getAttribute36() {
  356.         return attribute36;
  357.     }
  359.     public void setAttribute36(String attribute36) {
  360.         this.attribute36 = attribute36;
  361.     }
  363.     public String getAttribute37() {
  364.         return attribute37;
  365.     }
  367.     public void setAttribute37(String attribute37) {
  368.         this.attribute37 = attribute37;
  369.     }
  371.     public String getAttribute38() {
  372.         return attribute38;
  373.     }
  375.     public void setAttribute38(String attribute38) {
  376.         this.attribute38 = attribute38;
  377.     }
  379.     public String getAttribute39() {
  380.         return attribute39;
  381.     }
  383.     public void setAttribute39(String attribute39) {
  384.         this.attribute39 = attribute39;
  385.     }
  387.     public String getAttribute40() {
  388.         return attribute40;
  389.     }
  391.     public void setAttribute40(String attribute40) {
  392.         this.attribute40 = attribute40;
  393.     }
  394. }

解析工具

  1. package com.hand.utils.excel;
  3. import oracle.core.lmx.CoreException;
  4. import org.apache.commons.fileupload.FileItem;
  5. import org.apache.commons.fileupload.disk.DiskFileItemFactory;
  6. import org.apache.commons.fileupload.servlet.ServletFileUpload;
  7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  8. import org.apache.poi.ss.usermodel.Cell;
  9. import org.apache.poi.ss.usermodel.Row;
  10. import org.apache.poi.ss.usermodel.Sheet;
  11. import org.apache.poi.ss.usermodel.Workbook;
  12. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  14. import javax.servlet.http.HttpServletRequest;
  15. import java.io.IOException;
  16. import java.io.InputStream;
  17. import java.lang.reflect.Method;
  18. import java.text.DecimalFormat;
  19. import java.text.SimpleDateFormat;
  20. import java.util.ArrayList;
  21. import java.util.Date;
  22. import java.util.List;
  25. public class TransferExcelDateToFileUploadDtoUtil {
  27.     public static FileUploadDto TransferExcelDateToFileUploadDto(HttpServletRequest request, int startRowId, int colCount, int sheetIndex) throws Exception {
  28.         /**
  29.          *
  30.          * 功能描述: 
  31.          * 
  32.          * @auther:lkj
  33.          * @date:2018/8/24 上午10:37
  34.          * @param:[request 请求, startRowId 从第几行开始解析, colCount 一共几列, sheetIndex 解析的sheet页]
  35.          * @return:com.hand.utils.excel.FileUploadDto
  36.          *
  37.          */
  38.         //将excel文件流解析为InputStream流
  39.         DiskFileItemFactory factory = new DiskFileItemFactory();
  40.         ServletFileUpload upload = new ServletFileUpload(factory);
  41.         List items = upload.parseRequest(request);
  42.         InputStream inputStream = null;
  44.         String excelType = "";
  45.         if(!items.isEmpty()) {
  46.             FileItem item = (FileItem)items.get(0);
  47.             if(!item.isFormField()) {
  48.                 inputStream = item.getInputStream();
  50.                 //获得当前Excel格式
  51.                 String fileName = item.getName();
  52.                 excelType = fileName.substring(fileName.lastIndexOf(".") + 1);
  53.             }
  54.         }
  56.         //将该fileInputStream解析为list对象
  57.         FileUploadDto fileUploadDto = TransferExcelDateToFileUploadDtoUtil.transferExcelDateToArrayList(inputStream,excelType,startRowId,colCount,sheetIndex);
  59.         return fileUploadDto;
  60.     }
  62.     /*
  63.        将excel中的数据转换为java对象数组
  64.        传参fileInputStream：excel的文件输入流
  65.        返回：FileUploadDto
  66.     */
  67.     public static  FileUploadDto transferExcelDateToArrayList(InputStream inputStream, String excelType, int startRowId, int colCount, int sheetIndex) throws Exception {
  68.         Sheet sheet;
  69.         Workbook workBook = null;
  70.         List<FileUploadBaseDto> list = new ArrayList<>();
  71.         FileUploadDto fileUploadDto = new FileUploadDto();
  73.         /*
  74.          * 得到sheet
  75.          */
  76.         try {
  77.             if (excelType.equals("xls")){
  78.                 workBook = new HSSFWorkbook(inputStream);
  79.             } else {
  80.                 workBook = new XSSFWorkbook(inputStream);
  81.             }
  82.         } catch (IOException e) {
  83.             e.printStackTrace();
  84.         }
  86.         /*
  87.          * 验证：导入模板数据时，如果有多个sheet如何选择？
  88.          */
  89.         int count = workBook.getNumberOfSheets();
  90.         if(count>1){
  91.             throw new Exception("导入模板中存在多页!");
  92.         }
  94.         //获得第一个sheet
  95.         sheet = workBook.getSheetAt(sheetIndex);
  97.         /*
  98.         遍历所有行
  99.          */
  100.         int rownum = sheet.getLastRowNum() + 1;
  101.         for (int i = startRowId-1; i < rownum; i++) {
  102.             Row row = sheet.getRow(i);
  103.             FileUploadBaseDto model = new FileUploadBaseDto();
  104.             model.setRowNum(i+1);
  106.             /*
  107.             构造sheet中单行数据的dto
  108.              */
  109.             for(int j = 0;j < colCount; j++){
  110.                 model = TransferExcelDateToFileUploadDtoUtil.setAttribute(model,row,j);
  111.             }
  112.             list.add(model);
  113.         }
  115.         //返回
  116.         fileUploadDto.setList(list);
  117.         fileUploadDto.setN(colCount);
  118.         return fileUploadDto;
  120.     }
  122.     /*
  123.      * 将Row中第colNum列的信息放入FileUploadBaseDto中
  124.      */
  125.     public static FileUploadBaseDto setAttribute(FileUploadBaseDto fileUploadBaseDto,Row row,int colNum ){
  126.         Cell cell = row.getCell(colNum);
  128.         if(cell == null)
  129.             return fileUploadBaseDto;
  131.         if(cell.toString().equals("")){
  132.             return fileUploadBaseDto;
  133.         }
  135.         Class clazz = FileUploadBaseDto.class;
  136.         int count = colNum+1;
  137.         try {
  138.             String val = "";
  139.             switch (cell.getCellType()) {
  140.                 case Cell.CELL_TYPE_STRING:
  141.                     val = cell.getStringCellValue();
  142.                     break;
  143.                 case Cell.CELL_TYPE_BOOLEAN:
  144.                     Boolean val1 = cell.getBooleanCellValue();
  145.                     val = val1.toString();
  146.                     break;
  147.                 case Cell.CELL_TYPE_NUMERIC:
  148.                     if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
  149.                         Date theDate = cell.getDateCellValue();
  150.                         SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  151.                         val = dff.format(theDate);
  152.                     }else{
  153.                         DecimalFormat df = new DecimalFormat("#.##");
  154.                         val = df.format(cell.getNumericCellValue());
  155.                     }
  156.                     break;
  157.                 case Cell.CELL_TYPE_BLANK:
  158.                     break;
  159.                 default:
  160.                     throw new CoreException("数据类型配置不正确");
  161.             }
  162.             Method method = clazz.getDeclaredMethod("setAttribute" + count,String.class);
  163.             method.invoke(fileUploadBaseDto,val);
  164.         } catch (Exception e) {
  165.             e.printStackTrace();
  166.         }
  168.         return fileUploadBaseDto;
  169.     }
  171. }

返回List 集合

  1. package com.hand.utils.excel;
  3. import java.util.List;
  5. public class FileUploadDto {
  6.     private List<FileUploadBaseDto> list; //将file解析为List集合
  8.     private int n; //列数
  11.     public List<FileUploadBaseDto> getList() {
  12.         return list;
  13.     }
  15.     public void setList(List<FileUploadBaseDto> list) {
  16.         this.list = list;
  17.     }
  19.     public int getN() {
  20.         return n;
  21.     }
  23.     public void setN(int n) {
  24.         this.n = n;
  25.     }
  26. }

发表评论

表情：
评论列表 （有 0 条评论，678人围观）

还没有评论，来说两句吧...

相关阅读

    相关 上传Excel并解析

    该篇博客废除，见解析excel工具类 兼容2007和2003两种类型的文件，举例：这里模板有两个页脚： ![20180404100358222][]           

    傷城~傷城~/ 0 赞/ 468 阅读

    相关 java excel上传--poi

    工作中很多批量上传需求不同，每个需求都要写一次批量上传代码，太烦。。决定写一个通用工具，此代码复制即用。需要改进的地方请评论区留言 方法调用传参例子： 上传文件中列的字段

    悠悠悠悠/ 0 赞/ 405 阅读