导入excel数据到数据库

痛定思痛。 2021-11-27 05:38 535阅读 0赞

controller：

/**
    	 * 上传Excel逾期催收数据
    	 * 
    	 * @param request
    	 * @return
    	 * @throws Exception
    	 */
    	@PostMapping("/uploadExcelCom")
    	public Result uploadExcelCom(HttpServletRequest request) throws Exception {
    		Map<String, Object> paramMap = getParamMap(request);
    		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    
    		MultipartFile fileData = (MultipartFile) multipartRequest.getFileMap().values().toArray()[0];
    		InputStream iStream = fileData.getInputStream();
    
    		discountDetailService.uploadExcelCom(iStream, paramMap);
    
    		return Result.getSuccessResult();
    	}

service:

/**
    	 * 按格式导入文件
    	 * 
    	 * @param file
    	 * @param paramMap
    	 * @throws Exception
    	 */
    	public void uploadExcelCom(InputStream file, Map<String, Object> paramMap) throws Exception {
    		ByteArrayOutputStream baos = new ByteArrayOutputStream();  
    		byte[] buffer = new byte[1024];  
    		int len1;  
    		while ((len1 = file.read(buffer)) > -1 ) {  
    		    baos.write(buffer, 0, len1);  
    		}  
    		baos.flush();
    		file.close();
    		
    		InputStream inputStream1 = new ByteArrayInputStream(baos.toByteArray());
    		List<List<Cell[]>> allSheetList = ExcelUtils.getExcleAllSheet(inputStream1); 
    		inputStream1.close();
    		//创建表头  表头是唯一的
    		ReportFormHead reportFormHead = new ReportFormHead();
    		String rfhUid = UUID.randomUUID().toString().replaceAll("-", "");
    		reportFormHead.setRfhUid(rfhUid);
    		reportFormHead.setBeginDate(String.valueOf(paramMap.get("beginDate")));
    		reportFormHead.setEndDate(String.valueOf(paramMap.get("endDate")));
    		reportFormHead.setBankBranchId(String.valueOf(paramMap.get("bankBranchId")));
    		reportFormHead.setBankBranchName(String.valueOf(paramMap.get("bankBranchName")));
    		reportFormHead.setBankId(String.valueOf(paramMap.get("bankId")));
    		reportFormHead.setBankName(String.valueOf(paramMap.get("bankName")));
    		reportFormHead.setCompanyId(String.valueOf(paramMap.get("companyId")));
    		reportFormHead.setOpPersonCode(String.valueOf(paramMap.get("opPersonCode")));
    		reportFormHead.setOpPersonName(String.valueOf(paramMap.get("opPersonName")));
    		reportFormHead.setStatus(String.valueOf(paramMap.get("status")));
    		reportFormHead.setTypeId(String.valueOf(paramMap.get("typeId")));
    		
    		List<ComDiscountDetail> comDiscountDetails = new ArrayList<>();
    		for(int i=0; i<allSheetList.size(); i++) {//循环sheet集合
    			
    		    List<Cell[]> sheet = allSheetList.get(i);
    		    for(int j=0 ; j <sheet.size();j++) {
    		    	String uuid = UUID.randomUUID().toString().replaceAll("-", "");
    		    	ComDiscountDetail comDiscountDetail = new ComDiscountDetail();//创建表体
    			    comDiscountDetail.setRfhUid(rfhUid);
    			    comDiscountDetail.setComDiscountDetailUuid(uuid);
    			    comDiscountDetail.setComDiscountDetailFlag(String.valueOf(paramMap.get("comDiscountDetailFlag")));
    			    comDiscountDetail.setStatisticsDateS(String.valueOf(paramMap.get("beginDate")));
    			    comDiscountDetail.setStatisticsDateE(String.valueOf(paramMap.get("endDate")));
    				Cell[] cells = sheet.get(j);
    				if(j==1) {
    					//获取标题
    					continue;
    				}
    				if(j==sheet.size()-10) {
    					for(int k=0;k<cells.length ; k++) {//列数据
    						String value = cells[k].getContents();
    						value = value.replace(String.valueOf((char)160)," ");//将ascii码为160转化为ascii码为32的空格
    						value = value.trim();//去掉前后空格，trim()只能清除ascii码为32的空格
    						//获取填表人  审核人  部门负责人
    						value = value==""?"":value.split("：")[1];
    						switch (k) {
    						case 0:
    					    	reportFormHead.setFillerPersonName(value);
    						    break;
    						case 4:
    							reportFormHead.setExamPersonName(value);
    							break;
    						case 14:
    							reportFormHead.setHeadPersonName(value);
    							break;
    						}
    					}
    				}
    				
    				if(j>=6 && j<sheet.size()-11) {//行数据
    					for(int k=0;k<cells.length ; k++) {//列数据
    						String value = cells[k].getContents();
    						value = value.replace(String.valueOf((char)160)," ");//将ascii码为160转化为ascii码为32的空格
    						value = value.trim();//去掉前后空格，trim()只能清除ascii码为32的空格
    						//如果是时间格式的
    						Cell cell =cells[k];
    						if(cell.getType() == CellType.DATE){
    							DateCell dc = (DateCell)cell;
    							Date date = dc.getDate();
    							SimpleDateFormat ds = new SimpleDateFormat("yyyy-MM-dd HH:ss:mm");
    							value = ds.format(date);
    						}
    						//表体数据
    						switch (k) {
    						 case 0:
    							    comDiscountDetail.setComDiscountDetailA(value);
    							    break;
    							case 1:
    								comDiscountDetail.setComDiscountDetailB(value);
    								break;
    							case 2:
    								comDiscountDetail.setComDiscountDetailD(value);
    								break;
    							case 3:
    								comDiscountDetail.setComDiscountDetailE(value);
    								break;
    							case 4:
    								comDiscountDetail.setComDiscountDetailF(value);
    								break;
    							case 5:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailG(value);
    								break;
    							case 6:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailH(value);
    								break;
    							case 7:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailI(value);
    								break;	
    							case 8:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailJ(value);
    							    break;
    							case 9:
    								comDiscountDetail.setComDiscountDetailK(value);
    								break;
    							case 10:
    								comDiscountDetail.setComDiscountDetailL(value);
    								break;
    							case 11:
    								comDiscountDetail.setComDiscountDetailM(value);
    								break;
    							case 12:
    								comDiscountDetail.setComDiscountDetailN(value);
    								break;
    							case 13:
    								comDiscountDetail.setComDiscountDetailO(value);
    								break;
    							case 14:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailP(value);
    								break;
    							case 15:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailQ(value);
    								break;
    							case 16:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailR(value);
    								break;
    							case 17:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailS(value);
    								break;
    							case 18:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailT(value);
    								break;
    							case 19:
    								value = value.replaceAll(",","");
    								comDiscountDetail.setComDiscountDetailU(value);
    								break;
    						}
    					}
    					comDiscountDetails.add(comDiscountDetail);//加入集合
    				}
    			}
    		  
    		}
    		System.out.println(comDiscountDetails);
    		//插入数据库
    		discountDetailDao.addReportFormHead(reportFormHead);
    		discountDetailDao.addComDiscountDetail(comDiscountDetails);
    		
    	}

ExcelUtils:

package com.hsit.utils;
    
    import java.io.File;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.jeecgframework.poi.excel.ExcelExportUtil;
    import org.jeecgframework.poi.excel.entity.ExportParams;
    import org.jeecgframework.poi.excel.entity.TemplateExportParams;
    import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;
    import org.springframework.util.ResourceUtils;
    
    import jxl.Cell;
    import jxl.Sheet;
    
    /**
     * @description Excel操作（导入|导出）
     * @author jiangxy
     * @date 2019年01月03日
     *
     */
    public class ExcelUtils {
    
    	/**
    	 * excel模板导出
    	 * @param request
    	 * @param response
    	 * @param templateName   exportTemplates这个目录下面的模板名称
    	 * @param fileName       最终下载的文件名称
    	 * @param map            数据内容
    	 * @throws Exception
    	 */
    	public static void exportExcelByTemplate(HttpServletRequest request,HttpServletResponse response,String templateName,String fileName,Map<String,Object> map) throws Exception{
    		if(StringUtils.isBlank(templateName)) {
    			throw new Exception("模板文件不能为空!");
    		}
    		if(templateName.startsWith(File.separator)) {
    			throw new Exception("模板文件名称不合法,不能以/或\\开始");
    		}
    		
    		if(!templateName.toLowerCase().endsWith("xls")
    				&&!templateName.toLowerCase().endsWith("xlsx")) {
    			throw new Exception("模板文件名称只能为xls或者xlsx格式!");
    		}
    		
    		String[] arrNames = templateName.split("\\.");
    		if(arrNames.length!=2) {
    			throw new Exception("模板文件名称不合法!");
    		}
    		File cfgFile = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "exportTemplates/"+templateName);
    		TemplateExportParams params = new TemplateExportParams();
    		params.setTemplateUrl(cfgFile.getAbsolutePath());//本机路径
    		Workbook book = ExcelExportUtil.exportExcel(params, map);
    		downloadXls(request,response,book,fileName+"."+arrNames[1]);
    	}
    	
    	public static void downloadXls(HttpServletRequest request,HttpServletResponse response,Workbook book,String fileName) throws Exception {
    		String browser = request.getHeader("User-Agent");
            if (-1 < browser.indexOf("MSIE 6.0") || -1 < browser.indexOf("MSIE 7.0")) {
                // IE6, IE7 浏览器
                response.addHeader("content-disposition", "attachment;filename="
                        + new String(fileName.getBytes(), "ISO8859-1"));
            } else if (-1 < browser.indexOf("MSIE 8.0")) {
                // IE8 
                response.addHeader("content-disposition", "attachment;filename="
                        + URLEncoder.encode(fileName, "UTF-8"));
            } else if (-1 < browser.indexOf("MSIE 9.0")) {
                // IE9
                response.addHeader("content-disposition", "attachment;filename="
                        + URLEncoder.encode(fileName, "UTF-8"));
            } else if (-1 < browser.indexOf("Chrome")) {
                // 谷歌
                response.addHeader("content-disposition",
                        "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
            } else if (-1 < browser.indexOf("Safari")) {
                // 苹果
                response.addHeader("content-disposition", "attachment;filename="
                        + new String(fileName.getBytes(), "ISO8859-1"));
            } else {
                // 火狐或者其他的浏览器
                response.addHeader("content-disposition",
                        "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
            }
            response.setHeader("content-Type", "application/vnd.ms-excel");
    		response.setCharacterEncoding("UTF-8");
    		OutputStream out = response.getOutputStream();
    		book.write(out);
    		out.close();
    	}
    	
    	/**
    	 * 非模板导出
    	 * @param fileName 下载时的文件名
    	 * @param title    导出的xls的标题
    	 * @param sheetName sheet的名称
    	 * @param names    表头的中文名称
    	 * @param rows     对应要显示的字段
    	 * @param lens     对应的宽度
    	 * @param list     显示的数据集
    	 * @param response
    	 * @throws Exception
    	 */
    	public static void excelXls(String fileName,String title,String sheetName,String[] names,String[] rows,int[] lens,List<Map<String,Object>> list,HttpServletResponse response) throws Exception {
            response.setHeader("content-Type", "application/vnd.ms-excel");
    		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8") + ".xls");
    		response.setCharacterEncoding("UTF-8");
    		List<ExcelExportEntity> entity = ExcelUtils.getEntity(names,rows,lens);
    		ExportParams params = new ExportParams(title,sheetName);
    		HSSFWorkbook book = (HSSFWorkbook) ExcelExportUtil.exportExcel(params,entity,list);
    		OutputStream out = response.getOutputStream();
    		book.write(out);
    		out.close();
    	}
    
    	public static List<ExcelExportEntity> getEntity(String[] names, String[] rows, int[] lens) throws Exception {
    		List<ExcelExportEntity> entity = new ArrayList<>();
    		if(names==null||rows==null||names.length<=0||rows.length<=0) {
    			throw new Exception("传入的表头名称和字段名称不能为空");
    		}
    		if(names.length!=rows.length) {
    			throw new Exception("传入的表头与字段名称不匹配");
    		}
    		for(int i=0;i<names.length;i++) {
    			ExcelExportEntity e = new ExcelExportEntity(names[i], rows[i], lens[i]);
    			e.setFormat("");
    			entity.add(e);
    		}
    		return entity;
    	}
    	
    	/**
         * 多表头导出Excel
         * @param fileName 文件名
         * @param sheetName sheet名称
         * @param titleName 标题
         * @param list 内容
         * @param headnum 合并标题
         * @param rows 字段
         * @param colums 内容需要合并的列   0正常不合并
    	 * @throws IOException 
         */
        public static void getHSSFWorkbook(String fileName,String firstTitle,String sheetName,int[] lens,List<String[]> titleName,String[] headnum,List<Map<String,Object>> list,String[] rows,int colums,HttpServletResponse response) throws IOException{
        	
        	String[][] values =new String[list.size()][titleName.get(0).length];
    		for (int i = 0; i < list.size(); i++) {
               Map<String,Object> obj = list.get(i);
               for (int j = 0; j < rows.length; j++) {
            	   values[i][j] = String.valueOf(obj.get(rows[j])==null?"":obj.get(rows[j]));
               }
             }
        	int biaoti = 2 ; //标题占据几行
        	//第一步 创建工作workbook
        	HSSFWorkbook   wb = new HSSFWorkbook();
    
            //第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            
            //第三步    标题
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, titleName.get(0).length-1));
            //标题的格式
            HSSFCellStyle style2 = wb.createCellStyle();
            //水平居中
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            // 生成一个字体	
         	HSSFFont font = wb.createFont();
         	font.setColor(IndexedColors.BLACK.getIndex());
    		font.setFontName("宋体"); //设置字体
    		font.setFontHeightInPoints((short) 16); //设置字号
    		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //设置字体样式 正常显示
    		// 把字体应用到当前的样式
    		style2.setFont(font);
    		//声明列对象
    		HSSFRow row = sheet.createRow(0);
    		HSSFCell cell = row.createCell(0);
            cell.setCellValue(firstTitle);
            cell.setCellStyle(style2);
            
            // 添加表头合并单元格动态合并单元格
            for (int i = 0; i < headnum.length; i++) {
                String[] temp = headnum[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow+biaoti, overrow+biaoti, startcol, overcol));
            }
         // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            //水平居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
          
           // 第五部创建表头
            for(int i=0;i<titleName.size();i++){
            	row = sheet.createRow(i+biaoti);
            	String[] title = titleName.get(i);
            	for(int j=0;j<title.length;j++){
            		if(i > 0) {
            			//判断是否有合并行
            			String[] preTitle = titleName.get(i-1);
        				if(!preTitle[j].equals(title[j])) {
        					cell = row.createCell(j);
        	                cell.setCellValue(title[j]);
        	                cell.setCellStyle(style);
        				}
            		}else {
            			if(j>0) {
                			if(!title[j].equals(title[j-1])) {
                				//判断是否是合并列
                    			cell = row.createCell(j);
            	                cell.setCellValue(title[j]);
            	                cell.setCellStyle(style);
                			}
                		}else {
                			cell = row.createCell(j);
        	                cell.setCellValue(title[j]);
        	                cell.setCellStyle(style);
                		}
            		}
            		
    			}
            }
            
            if(colums>0) {
            	//数据内容的合并
                int changeC = titleName.size()+biaoti;
                for(int i=0;i<values.length;i++){
                    for(int j=0;j<values[i].length;j++){
                        //将内容按顺序赋给对应的列对象
                    	if(i!=0&& j==(colums-1)) {
                    		if(!values[i][j].equals(values[i-1][j])) {
                    			sheet.addMergedRegion(new CellRangeAddress(changeC, titleName.size()+i+biaoti-1, j, j));
                    			changeC=titleName.size()+i+biaoti;
                    		}else {
                    			if(i==values.length-1) {
                    				sheet.addMergedRegion(new CellRangeAddress(changeC, titleName.size()+i+biaoti, j, j));
                    			}
                    		}
                    		
                    	}
                    }
                }
                //创建内容
                for(int i=0;i<values.length;i++){
                    row = sheet.createRow(i + titleName.size()+biaoti);
                    for(int j=0;j<values[i].length;j++){
                        //将内容按顺序赋给对应的列对象
                    	String value =values[i][j];
                    	if(i!=0&& j==(colums-1)) {
                    		if(!values[i][j].equals(values[i-1][j])) {
                    			cell = row.createCell(j);
                    			cell.setCellValue(value);
                    			cell.setCellStyle(style);
                    		}
                    	}else {
                    		cell = row.createCell(j);
                			cell.setCellValue(value);
                			cell.setCellStyle(style);
                    	}
                    	//sheet.autoSizeColumn(j, true);
                    }
                }
            }else {
            	//创建内容
                for(int i=0;i<values.length;i++){
                    row = sheet.createRow(i + titleName.size()+biaoti);
                    for(int j=0;j<values[i].length;j++){
                        //将内容按顺序赋给对应的列对象
                    	String value =values[i][j];
            			cell = row.createCell(j);
            			cell.setCellValue(value);
            			cell.setCellStyle(style);
                    }
                }
            }
            
            //设置行宽度
            for(int i=0;i<lens.length;i++){
            	sheet.setColumnWidth(i, 256*lens[i]); //设置某一列宽度
            }
            
            try {
                try {
                	System.out.println(fileName);
                    fileName = new String(fileName.getBytes(),"UTF-8");
                } catch (UnsupportedEncodingException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                response.setHeader("content-Type", "application/vnd.ms-excel");
        		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8") + ".xls");
        		response.setCharacterEncoding("UTF-8");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            
            OutputStream os = response.getOutputStream();
    	     wb.write(os);
    	     os.flush();
    	     os.close();
        }
        
        /**
         * 获取Excel单个sheet页所有单元格数据集
         * @param excelFile
         * @return
         * @throws Exception
         */
        public static List<Cell[]> getExcle(InputStream excelFile) throws Exception {
    		jxl.Workbook workbook = null;
    		int count=0;
    		List<Cell[]> list=new ArrayList<Cell[]>();
    		try {
    			workbook = jxl.Workbook.getWorkbook(excelFile);
    			Sheet sheet = workbook.getSheet(0);
    			count = sheet.getRows();
    			for(int i=0;i<count;i++){
    				Cell[] row = sheet.getRow(i);
    				list.add(row);
    			}
    		} catch (Exception e) {
    			e.getLocalizedMessage();
    		} finally {
    			if (workbook != null) {
    				workbook.close();
    			}
    		}
    		return list;
    	}
        
        /**
         * 获取Excel多个sheet页所有单元格数据集
         * @param excelFile
         * @return
         * @throws Exception
         */
        public static List<List<Cell[]>> getExcleAllSheet(InputStream excelFile) throws Exception {
    		jxl.Workbook workbook = null;
    		int count=0;
    		List<List<Cell[]>> allSheetList = new ArrayList<List<Cell[]>>();
    		try {
    			workbook = jxl.Workbook.getWorkbook(excelFile);
    			int sheetNum = workbook.getNumberOfSheets();
    			for (int i = 0; i < sheetNum; i++) {
    				List<Cell[]> list=new ArrayList<Cell[]>();
    				Sheet sheet = workbook.getSheet(i);
    				count = sheet.getRows();
    				for(int j=0;j<count;j++){
    					Cell[] row = sheet.getRow(j);
    					list.add(row);
    				}
    				allSheetList.add(list);
    			}
    			
    		} catch (Exception e) {
    			e.getLocalizedMessage();
    		} finally {
    			if (workbook != null) {
    				workbook.close();
    			}
    		}
    		return allSheetList;
    	}
    }

结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzMwMzM4_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NzMwMzM4_size_16_color_FFFFFF_t_70]: /images/20211126/0961d9898204411c88db6803e685d689.png