导入excel数据到数据库

 

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

结果