Java 上传Excel 并解析Excel

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

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

调用方法

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

Dto 上传解析的列

package com.hand.utils.excel;
    
    
    public class FileUploadBaseDto {
        private int rowNum; //行号
    
        private String attribute1;
    
        private String attribute2;
    
        private String attribute3;
    
        private String attribute4;
    
        private String attribute5;
    
        private String attribute6;
    
        private String attribute7;
    
        private String attribute8;
    
        private String attribute9;
    
        private String attribute10;
    
        private String attribute11;
    
        private String attribute12;
    
        private String attribute13;
    
        private String attribute14;
    
        private String attribute15;
    
        private String attribute16;
    
        private String attribute17;
    
        private String attribute18;
    
        private String attribute19;
    
        private String attribute20;
        private String attribute21;
        private String attribute22;
        private String attribute23;
        private String attribute24;
        private String attribute25;
        private String attribute26;
        private String attribute27;
        private String attribute28;
        private String attribute29;
        private String attribute30;
        private String attribute31;
        private String attribute32;
        private String attribute33;
        private String attribute34;
        private String attribute35;
        private String attribute36;
        private String attribute37;
        private String attribute38;
        private String attribute39;
        private String attribute40;
    
        public int getRowNum() {
            return rowNum;
        }
    
        public void setRowNum(int rowNum) {
            this.rowNum = rowNum;
        }
    
        public String getAttribute1() {
            return attribute1;
        }
    
        public void setAttribute1(String attribute1) {
            this.attribute1 = attribute1;
        }
    
        public String getAttribute2() {
            return attribute2;
        }
    
        public void setAttribute2(String attribute2) {
            this.attribute2 = attribute2;
        }
    
        public String getAttribute3() {
            return attribute3;
        }
    
        public void setAttribute3(String attribute3) {
            this.attribute3 = attribute3;
        }
    
        public String getAttribute4() {
            return attribute4;
        }
    
        public void setAttribute4(String attribute4) {
            this.attribute4 = attribute4;
        }
    
        public String getAttribute5() {
            return attribute5;
        }
    
        public void setAttribute5(String attribute5) {
            this.attribute5 = attribute5;
        }
    
        public String getAttribute6() {
            return attribute6;
        }
    
        public void setAttribute6(String attribute6) {
            this.attribute6 = attribute6;
        }
    
        public String getAttribute7() {
            return attribute7;
        }
    
        public void setAttribute7(String attribute7) {
            this.attribute7 = attribute7;
        }
    
        public String getAttribute8() {
            return attribute8;
        }
    
        public void setAttribute8(String attribute8) {
            this.attribute8 = attribute8;
        }
    
        public String getAttribute9() {
            return attribute9;
        }
    
        public void setAttribute9(String attribute9) {
            this.attribute9 = attribute9;
        }
    
        public String getAttribute10() {
            return attribute10;
        }
    
        public void setAttribute10(String attribute10) {
            this.attribute10 = attribute10;
        }
    
        public String getAttribute11() {
            return attribute11;
        }
    
        public void setAttribute11(String attribute11) {
            this.attribute11 = attribute11;
        }
    
        public String getAttribute12() {
            return attribute12;
        }
    
        public void setAttribute12(String attribute12) {
            this.attribute12 = attribute12;
        }
    
        public String getAttribute13() {
            return attribute13;
        }
    
        public void setAttribute13(String attribute13) {
            this.attribute13 = attribute13;
        }
    
        public String getAttribute14() {
            return attribute14;
        }
    
        public void setAttribute14(String attribute14) {
            this.attribute14 = attribute14;
        }
    
        public String getAttribute15() {
            return attribute15;
        }
    
        public void setAttribute15(String attribute15) {
            this.attribute15 = attribute15;
        }
    
        public String getAttribute16() {
            return attribute16;
        }
    
        public void setAttribute16(String attribute16) {
            this.attribute16 = attribute16;
        }
    
        public String getAttribute17() {
            return attribute17;
        }
    
        public void setAttribute17(String attribute17) {
            this.attribute17 = attribute17;
        }
    
        public String getAttribute18() {
            return attribute18;
        }
    
        public void setAttribute18(String attribute18) {
            this.attribute18 = attribute18;
        }
    
        public String getAttribute19() {
            return attribute19;
        }
    
        public void setAttribute19(String attribute19) {
            this.attribute19 = attribute19;
        }
    
        public String getAttribute20() {
            return attribute20;
        }
    
        public void setAttribute20(String attribute20) {
            this.attribute20 = attribute20;
        }
    
        public String getAttribute21() {
            return attribute21;
        }
    
        public void setAttribute21(String attribute21) {
            this.attribute21 = attribute21;
        }
    
        public String getAttribute22() {
            return attribute22;
        }
    
        public void setAttribute22(String attribute22) {
            this.attribute22 = attribute22;
        }
    
        public String getAttribute23() {
            return attribute23;
        }
    
        public void setAttribute23(String attribute23) {
            this.attribute23 = attribute23;
        }
    
        public String getAttribute24() {
            return attribute24;
        }
    
        public void setAttribute24(String attribute24) {
            this.attribute24 = attribute24;
        }
    
        public String getAttribute25() {
            return attribute25;
        }
    
        public void setAttribute25(String attribute25) {
            this.attribute25 = attribute25;
        }
    
        public String getAttribute26() {
            return attribute26;
        }
    
        public void setAttribute26(String attribute26) {
            this.attribute26 = attribute26;
        }
    
        public String getAttribute27() {
            return attribute27;
        }
    
        public void setAttribute27(String attribute27) {
            this.attribute27 = attribute27;
        }
    
        public String getAttribute28() {
            return attribute28;
        }
    
        public void setAttribute28(String attribute28) {
            this.attribute28 = attribute28;
        }
    
        public String getAttribute29() {
            return attribute29;
        }
    
        public void setAttribute29(String attribute29) {
            this.attribute29 = attribute29;
        }
    
        public String getAttribute30() {
            return attribute30;
        }
    
        public void setAttribute30(String attribute30) {
            this.attribute30 = attribute30;
        }
    
        public String getAttribute31() {
            return attribute31;
        }
    
        public void setAttribute31(String attribute31) {
            this.attribute31 = attribute31;
        }
    
        public String getAttribute32() {
            return attribute32;
        }
    
        public void setAttribute32(String attribute32) {
            this.attribute32 = attribute32;
        }
    
        public String getAttribute33() {
            return attribute33;
        }
    
        public void setAttribute33(String attribute33) {
            this.attribute33 = attribute33;
        }
    
        public String getAttribute34() {
            return attribute34;
        }
    
        public void setAttribute34(String attribute34) {
            this.attribute34 = attribute34;
        }
    
        public String getAttribute35() {
            return attribute35;
        }
    
        public void setAttribute35(String attribute35) {
            this.attribute35 = attribute35;
        }
    
        public String getAttribute36() {
            return attribute36;
        }
    
        public void setAttribute36(String attribute36) {
            this.attribute36 = attribute36;
        }
    
        public String getAttribute37() {
            return attribute37;
        }
    
        public void setAttribute37(String attribute37) {
            this.attribute37 = attribute37;
        }
    
        public String getAttribute38() {
            return attribute38;
        }
    
        public void setAttribute38(String attribute38) {
            this.attribute38 = attribute38;
        }
    
        public String getAttribute39() {
            return attribute39;
        }
    
        public void setAttribute39(String attribute39) {
            this.attribute39 = attribute39;
        }
    
        public String getAttribute40() {
            return attribute40;
        }
    
        public void setAttribute40(String attribute40) {
            this.attribute40 = attribute40;
        }
    }

解析工具

package com.hand.utils.excel;
    
    import oracle.core.lmx.CoreException;
    import org.apache.commons.fileupload.FileItem;
    import org.apache.commons.fileupload.disk.DiskFileItemFactory;
    import org.apache.commons.fileupload.servlet.ServletFileUpload;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    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.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.http.HttpServletRequest;
    import java.io.IOException;
    import java.io.InputStream;
    import java.lang.reflect.Method;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    
    public class TransferExcelDateToFileUploadDtoUtil {
        
        public static FileUploadDto TransferExcelDateToFileUploadDto(HttpServletRequest request, int startRowId, int colCount, int sheetIndex) throws Exception {
            /**
             *
             * 功能描述: 
             * 
             * @auther:lkj
             * @date:2018/8/24 上午10:37
             * @param:[request 请求, startRowId 从第几行开始解析, colCount 一共几列, sheetIndex 解析的sheet页]
             * @return:com.hand.utils.excel.FileUploadDto
             *
             */
            //将excel文件流解析为InputStream流
            DiskFileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            List items = upload.parseRequest(request);
            InputStream inputStream = null;
    
            String excelType = "";
            if(!items.isEmpty()) {
                FileItem item = (FileItem)items.get(0);
                if(!item.isFormField()) {
                    inputStream = item.getInputStream();
    
                    //获得当前Excel格式
                    String fileName = item.getName();
                    excelType = fileName.substring(fileName.lastIndexOf(".") + 1);
                }
            }
    
            //将该fileInputStream解析为list对象
            FileUploadDto fileUploadDto = TransferExcelDateToFileUploadDtoUtil.transferExcelDateToArrayList(inputStream,excelType,startRowId,colCount,sheetIndex);
    
            return fileUploadDto;
        }
    
        /*
           将excel中的数据转换为java对象数组
           传参fileInputStream：excel的文件输入流
           返回：FileUploadDto
        */
        public static  FileUploadDto transferExcelDateToArrayList(InputStream inputStream, String excelType, int startRowId, int colCount, int sheetIndex) throws Exception {
            Sheet sheet;
            Workbook workBook = null;
            List<FileUploadBaseDto> list = new ArrayList<>();
            FileUploadDto fileUploadDto = new FileUploadDto();
    
            /*
             * 得到sheet
             */
            try {
                if (excelType.equals("xls")){
                    workBook = new HSSFWorkbook(inputStream);
                } else {
                    workBook = new XSSFWorkbook(inputStream);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
    
            /*
             * 验证：导入模板数据时，如果有多个sheet如何选择？
             */
            int count = workBook.getNumberOfSheets();
            if(count>1){
                throw new Exception("导入模板中存在多页!");
            }
    
            //获得第一个sheet
            sheet = workBook.getSheetAt(sheetIndex);
    
            /*
            遍历所有行
             */
            int rownum = sheet.getLastRowNum() + 1;
            for (int i = startRowId-1; i < rownum; i++) {
                Row row = sheet.getRow(i);
                FileUploadBaseDto model = new FileUploadBaseDto();
                model.setRowNum(i+1);
    
                /*
                构造sheet中单行数据的dto
                 */
                for(int j = 0;j < colCount; j++){
                    model = TransferExcelDateToFileUploadDtoUtil.setAttribute(model,row,j);
                }
                list.add(model);
            }
    
            //返回
            fileUploadDto.setList(list);
            fileUploadDto.setN(colCount);
            return fileUploadDto;
    
        }
    
        /*
         * 将Row中第colNum列的信息放入FileUploadBaseDto中
         */
        public static FileUploadBaseDto setAttribute(FileUploadBaseDto fileUploadBaseDto,Row row,int colNum ){
            Cell cell = row.getCell(colNum);
    
            if(cell == null)
                return fileUploadBaseDto;
    
            if(cell.toString().equals("")){
                return fileUploadBaseDto;
            }
    
            Class clazz = FileUploadBaseDto.class;
            int count = colNum+1;
            try {
                String val = "";
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        val = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        Boolean val1 = cell.getBooleanCellValue();
                        val = val1.toString();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                            Date theDate = cell.getDateCellValue();
                            SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            val = dff.format(theDate);
                        }else{
                            DecimalFormat df = new DecimalFormat("#.##");
                            val = df.format(cell.getNumericCellValue());
                        }
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    default:
                        throw new CoreException("数据类型配置不正确");
                }
                Method method = clazz.getDeclaredMethod("setAttribute" + count,String.class);
                method.invoke(fileUploadBaseDto,val);
            } catch (Exception e) {
                e.printStackTrace();
            }
    
            return fileUploadBaseDto;
        }
    
    }

返回List 集合

package com.hand.utils.excel;
    
    import java.util.List;
    
    public class FileUploadDto {
        private List<FileUploadBaseDto> list; //将file解析为List集合
    
        private int n; //列数
    
    
        public List<FileUploadBaseDto> getList() {
            return list;
        }
    
        public void setList(List<FileUploadBaseDto> list) {
            this.list = list;
        }
    
        public int getN() {
            return n;
        }
    
        public void setN(int n) {
            this.n = n;
        }
    }