poi之excel导入

小鱼儿 2022-07-21 00:22 218阅读 0赞

获取list数据:

import java.io.BufferedInputStream;
    import java.io.ByteArrayInputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedList;
    import java.util.List;
    
    import jxl.Cell;
    import jxl.CellType;
    import jxl.DateCell;
    import jxl.NumberCell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    
    import org.apache.log4j.Logger;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    /**
     * EXCEL文档解析工具类 该工具能将EXCEL文档中的表解析为由JAVA基础类构成的数据集合 整个EXCEL表由多个行组成.每行用一个LIST表示.
     * EXCEL中的行由一个LIST表示,各列的数据索引从0开始一一对齐存放在这个LIST中; 多个行构成整个表,由一个LIST存放多个行.
     * 
     */
    public class ExcelSheetParser {
    
    	private Logger logger = Logger.getLogger(ExcelSheetParser.class);
    
    	private HSSFWorkbook workbook;
    
    	public ExcelSheetParser(File excelFile) throws FileNotFoundException,
    			IOException {
    
    		workbook = new HSSFWorkbook(new FileInputStream(excelFile));
    	}
    
    	// 读Excel表格--工作薄,工作表,行,单元格
    	/**
    	 * 2003
    	 */
    	public static List<List> readExcel2003(String filePath) {
    		List<List> result = new ArrayList<List>();
    		File file = new File(filePath);
    		Workbook wb = null;
    		// StringBuffer sb = new StringBuffer();
    		try {
    			// 传入的是文件,获取工作薄
    			wb = Workbook.getWorkbook(file);
    			if (wb != null) {
    				// 获取所有的工作表
    				Sheet[] sheets = wb.getSheets();
    				if (sheets != null && sheets.length != 0) {
    					for (int i = 0; i < sheets.length; i++) {
    						// 获取每一个工作表,此后的操作都在工作表上面进行
    						Sheet sheet = wb.getSheet(i);
    						//
    						// 获取行的长度
    						int rows_len = sheet.getRows();
    						for (int j = 1; j < rows_len; j++) {
    							// 获取所有的列
    							Cell[] cells = sheet.getRow(j);
    							if (null != cells && cells.length <= 1) {
    								continue;
    							}
    							List<Object> rowData = new ArrayList<Object>();
    							if (cells != null && cells.length != 0) {
    								for (int k = 0; k < cells.length; k++) {
    									// 获取每一列
    									Cell cell = cells[k];
    									Object data = cell.getContents();
    									if (cell.getType() == CellType.DATE) {
    										DateCell dc = (DateCell) cell;
    										data = DateUtil.dateToStr(dc.getDate());
    									} else if (cell.getType() == CellType.NUMBER
    											|| cell.getType() == CellType.NUMBER_FORMULA) {
    										NumberCell nc = (NumberCell) cell;
    										data = "" + nc.getValue();
    									}
    									rowData.add(data);
    								}
    							}
    							result.add(rowData);
    						}
    					}
    				}
    			}
    		} catch (BiffException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} finally {
    			if (null != wb) {
    				wb.close();
    			}
    		}
    		return result;
    	}
    	
    	/**
    	 * 2007版本
    	 * 
    	 * @param filePath
    	 * @return
    	 */
    	public static List<List> readExcel2007(byte[] excelByte) {
    		List<List> result = new ArrayList<List>();
    		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
    		XSSFWorkbook xwb = null;
    		ByteArrayInputStream input = null;
    		try {
    			// 读取文件IO流
    //			input = new FileInputStream(new File(filePath));
    			input = new ByteArrayInputStream(excelByte);
    			// 转换成XSS对象
    			xwb = new XSSFWorkbook(new BufferedInputStream(input));
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		// 读取第一章表格内容
    		if (null != xwb) {
    			XSSFSheet sheet = xwb.getSheetAt(0);
    			Object value = null;
    			XSSFRow row = null;
    			XSSFCell cell = null;
    
    			XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象
    			int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。
    
    			// System.out.println(sheet.getPhysicalNumberOfRows());
    			// 循环内容项 不循环标题 所以+1
    			for (int i = sheet.getFirstRowNum() + 1; i <= sheet
    					.getPhysicalNumberOfRows(); i++) {
    				List<Object> rowData = new ArrayList<Object>();
    				row = sheet.getRow(i);
    				if (row == null) {
    					continue;
    				}
    				List<String> linked = new LinkedList<String>();
    				for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
    					cell = row.getCell(j);
    					if (null != cell) {
    						value = getDateCellValue(cell);
    					} else {
    						value = "";
    					}
    
    					rowData.add(value);
    				}
    				result.add(rowData);
    			}
    		}
    		return result;
    	}
    
    	/**
    	 * 2007版本
    	 * 
    	 * @param filePath
    	 * @return
    	 */
    	public static List<List> readExcel2007(String filePath) {
    		List<List> result = new ArrayList<List>();
    		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
    		XSSFWorkbook xwb = null;
    		FileInputStream input = null;
    		try {
    			// 读取文件IO流
    			input = new FileInputStream(new File(filePath));
    			// 转换成XSS对象
    			xwb = new XSSFWorkbook(new BufferedInputStream(input));
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		// 读取第一章表格内容
    		if (null != xwb) {
    			XSSFSheet sheet = xwb.getSheetAt(0);
    			Object value = null;
    			XSSFRow row = null;
    			XSSFCell cell = null;
    
    			XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象
    			int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。
    
    			// System.out.println(sheet.getPhysicalNumberOfRows());
    			// 循环内容项 不循环标题 所以+1
    			for (int i = sheet.getFirstRowNum() + 1; i <= sheet
    					.getPhysicalNumberOfRows(); i++) {
    				List<Object> rowData = new ArrayList<Object>();
    				row = sheet.getRow(i);
    				if (row == null) {
    					continue;
    				}
    				List<String> linked = new LinkedList<String>();
    				for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
    					cell = row.getCell(j);
    					if (null != cell) {
    						value = getDateCellValue(cell);
    					} else {
    						value = "";
    					}
    
    					rowData.add(value);
    				}
    				result.add(rowData);
    			}
    		}
    		return result;
    	}
    
    	/**
    	 * 2007版本
    	 * 
    	 * @param filePath
    	 * @return
    	 */
    	public static List<List> importBlackExcel2007(String filePath) {
    		List<List> result = new ArrayList<List>();
    		XSSFWorkbook xwb = null;
    		FileInputStream input = null;
    		try {
    			// 读取文件IO流
    			input = new FileInputStream(new File(filePath));
    			// 转换成XSS对象
    			xwb = new XSSFWorkbook(new BufferedInputStream(input));
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		// 读取第一章表格内容
    		if (null != xwb) {
    			XSSFSheet sheet = xwb.getSheetAt(0);
    			Object value = null;
    			XSSFRow row = null;
    			XSSFCell cell = null;
    
    			XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象
    			int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。
    
    			// System.out.println(sheet.getPhysicalNumberOfRows());
    			// 循环内容项 不循环标题 所以+1
    			for (int i = sheet.getFirstRowNum() + 1; i <= sheet
    					.getPhysicalNumberOfRows(); i++) {
    				List<Object> rowData = new ArrayList<Object>();
    				row = sheet.getRow(i);
    				if (row == null) {
    					continue;
    				}
    				List<String> linked = new LinkedList<String>();
    				for (int j = 0; j < colNum; j++) {
    					cell = row.getCell(j);
    					if (null != cell) {
    						value = getDateCellValue(cell);
    					} else {
    						value = "";
    					}
    
    					rowData.add(value);
    				}
    				result.add(rowData);
    			}
    		}
    		return result;
    	}
    
    	/**
    	 * 获取单元格数据内容为日期类型的数据
    	 * 
    	 * @param cell
    	 * @return
    	 */
    	private static String getDateCellValue(XSSFCell cell) {
    		String result = "";
    		try {
    			int cellType = cell.getCellType();
    			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
    				if (HSSFDateUtil.isCellDateFormatted(cell)) {
    					Date date = cell.getDateCellValue();
    					result = (date.getYear() + 1900) + "-"
    							+ (date.getMonth() + 1) + "-" + date.getDate();
    				} else {
    					String date = getStringCellValue(cell);
    					result = date.trim();
    				}
    			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
    				String date = getStringCellValue(cell);
    				result = date.trim();
    			} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
    				result = "";
    			}
    		} catch (Exception e) {
    			// System.out.println("日期格式不正确!");
    			e.printStackTrace();
    		}
    		return result;
    	}
    
    	/**
    	 * 获取单元格数据内容为字符串类型的数据
    	 * 
    	 * @param cell
    	 * @return
    	 */
    	private static String getStringCellValue(XSSFCell cell) {
    		if (cell == null) {
    			return "";
    		}
    		String strCell = "";
    		switch (cell.getCellType()) {
    		case HSSFCell.CELL_TYPE_STRING:
    			strCell = cell.getStringCellValue();
    			break;
    		case HSSFCell.CELL_TYPE_NUMERIC:
    			strCell = String.valueOf(cell.getNumericCellValue());
    			break;
    		case HSSFCell.CELL_TYPE_BOOLEAN:
    			strCell = String.valueOf(cell.getBooleanCellValue());
    			break;
    		case HSSFCell.CELL_TYPE_BLANK:
    			strCell = "";
    			break;
    		default:
    			strCell = "";
    			break;
    		}
    		if (strCell == null || strCell.equals("")) {
    			return "";
    		}
    
    		return strCell;
    	}
    
    	/**
    	 * 2007版本(读取内容包含excel表头)
    	 * 
    	 * @param filePath
    	 * @return
    	 */
    	public static List<List> readExcel2007ContainHeader(String filePath) {
    		List<List> result = new ArrayList<List>();
    		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
    		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
    		XSSFWorkbook xwb = null;
    		FileInputStream input = null;
    		try {
    			// 读取文件IO流
    			input = new FileInputStream(new File(filePath));
    			// 转换成XSS对象
    			xwb = new XSSFWorkbook(new BufferedInputStream(input));
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		// 读取第一章表格内容
    		if (null != xwb) {
    			XSSFSheet sheet = xwb.getSheetAt(0);
    			Object value = null;
    			XSSFRow row = null;
    			XSSFCell cell = null;
    
    			XSSFRow headerrow = sheet.getRow(0); // 表头 得到标题的内容对象
    			int colNum = headerrow.getPhysicalNumberOfCells();// 得到每行的列数。
    
    			// System.out.println(sheet.getPhysicalNumberOfRows());
    			for (int i = sheet.getFirstRowNum(); i <= sheet
    					.getPhysicalNumberOfRows(); i++) {
    				List<Object> rowData = new ArrayList<Object>();
    				row = sheet.getRow(i);
    				if (row == null) {
    					continue;
    				}
    				List<String> linked = new LinkedList<String>();
    				for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
    					cell = row.getCell(j);
    					if (null != cell) {
    						value = getDateCellValue(cell);
    					} else {
    						value = "";
    					}
    
    					rowData.add(value);
    				}
    				result.add(rowData);
    			}
    		}
    		return result;
    	}
    
    	/**
    	 * 2003版本(读取内容包含excel表头)
    	 * 
    	 * @param filePath
    	 * @return
    	 */
    	public static List<List> readExcel2003ContainHeader(String filePath) {
    		List<List> result = new ArrayList<List>();
    		File file = new File(filePath);
    		Workbook wb = null;
    		// StringBuffer sb = new StringBuffer();
    		try {
    			// 传入的是文件,获取工作薄
    			wb = Workbook.getWorkbook(file);
    			if (wb != null) {
    				// 获取所有的工作表
    				Sheet[] sheets = wb.getSheets();
    				if (sheets != null && sheets.length != 0) {
    					for (int i = 0; i < sheets.length; i++) {
    						// 获取每一个工作表,此后的操作都在工作表上面进行
    						Sheet sheet = wb.getSheet(i);
    						//
    						// 获取行的长度
    						int rows_len = sheet.getRows();
    						for (int j = 0; j < rows_len; j++) {
    							// 获取所有的列
    							Cell[] cells = sheet.getRow(j);
    							if (null != cells && cells.length <= 1) {
    								continue;
    							}
    							List<Object> rowData = new ArrayList<Object>();
    							if (cells != null && cells.length != 0) {
    								for (int k = 0; k < cells.length; k++) {
    									// 获取每一列
    									Cell cell = cells[k];
    									Object data = cell.getContents();
    									if (cell.getType() == CellType.DATE) {
    										DateCell dc = (DateCell) cell;
    										data = DateUtil.dateToStr(dc.getDate());
    									} else if (cell.getType() == CellType.NUMBER
    											|| cell.getType() == CellType.NUMBER_FORMULA) {
    										NumberCell nc = (NumberCell) cell;
    										data = "" + nc.getValue();
    									}
    									rowData.add(data);
    								}
    							}
    							result.add(rowData);
    						}
    					}
    				}
    			}
    		} catch (BiffException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} finally {
    			if (null != wb) {
    				wb.close();
    			}
    		}
    		return result;
    	}
    
    }

处理excel中读出来的list例子:

/**
    	 * 报表文件导入
    	 * 
    	 * @return
    	 */
    	@RequestMapping(value = "/filePage", method = RequestMethod.POST)
    	public String filePage(HttpServletRequest request, Model model) throws IOException {
    		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    		MultipartFile file = multipartRequest.getFile("docFile");
    		// 若没有选择上传文件就点击提交时，返回上传页面，并给出提示
    		if (file.getSize() == 0) {
    			model.addAttribute("msg", "请选择正确的文件");
    		}
    		String[] fileName = file.getOriginalFilename().split("\\.");
    		String suffixName = fileName[fileName.length - 1];
    		// 判断类型是否符合
    		if (!("xlsx".equals(suffixName))) {
    			model.addAttribute("msg", "导入文件失败！仅支持文件类型【xlsx】");
    		}
    		byte[] arr = file.getBytes();
    		int num = 0;
    		try {
    			num = num + this.planLoanInfoExcelToObject(arr);
    		} catch (ExcelOperateException e) {
    			model.addAttribute("msg", e.getMessage());
    			e.printStackTrace();
    		}
    		return "";
    	}

/**
    	 * 导入excel行为业务对象
    	 * 
    	 * @return
    	 */
    	public int planLoanInfoExcelToObject(byte[] arr) throws ExcelOperateException {
    		try {
    			List<List> planLoanInfoExcelList = ExcelSheetParser.readExcel2007(arr);
    			//将excel中的数据封装为对象后装入list，如果数据量大，考虑分批次查询和插入
    			List<PlanLoanInfo> infos = new ArrayList<PlanLoanInfo>();
    			for (int i = 0; i < planLoanInfoExcelList.size(); i++) {
    				List datas = planLoanInfoExcelList.get(i);
    				// TODO 验证数据准确性，否则就抛出异常
    				PlanLoanInfo planLoanInfo = new PlanLoanInfo();
    				for (int j = 0; j < datas.size(); j++) {
    					// 先检查数据正确性再封装对象
    					String data = "";
    					switch (j) {
    					case 0:
    						data = (String) datas.get(0);
    						planLoanInfo.setPlanPayAmt(new BigDecimal(Double.parseDouble(data)).multiply(new BigDecimal(10000)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						break;
    					case 1:
    						data = (String) datas.get(1);
    						planLoanInfo.setPlanTotalIncome(new BigDecimal(Double.parseDouble(data)).multiply(new BigDecimal(10000)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanTotalIncome());
    						break;
    					case 2:
    						data = (String) datas.get(2);
    						planLoanInfo.setPlanTotalProfit(new BigDecimal(Double.parseDouble(data)).multiply(new BigDecimal(10000)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanTotalProfit());
    						break;
    					case 3:
    						data = (String) datas.get(3);
    						planLoanInfo.setPlanInlibBalance(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanInlibBalance());
    						break;
    					case 4:
    						data = (String) datas.get(4);
    						planLoanInfo.setPlanCustMagPerLib(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanCustMagPerLib());
    						break;
    					case 5:
    						data = (String) datas.get(5);
    						planLoanInfo.setPlanPerEarner(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanPerEarner());
    						break;
    					case 6:
    						data = (String) datas.get(6);
    						planLoanInfo.setPlanPerProfit(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanPerProfit());
    						break;
    					case 7:
    						data = (String) datas.get(7);
    						planLoanInfo.setPlanOverDueRateBig(Double.parseDouble(data));
    						//System.out.println(planLoanInfo.getPlanOverDueRateBig());
    						break;
    					case 8:
    						data = (String) datas.get(8);
    						planLoanInfo.setPlanOverDueRateSmall(Double.parseDouble(data));
    						//System.out.println(planLoanInfo.getPlanOverDueRateSmall());
    						break;
    					case 9:
    						data = (String) datas.get(9);
    						planLoanInfo.setPlanPartsAre(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanPartsAre());
    						break;
    					case 10:
    						data = (String) datas.get(10);
    						planLoanInfo.setPlanOverDueTotalReturnPayment(new BigDecimal(Double.parseDouble(data)).setScale(2,BigDecimal.ROUND_HALF_UP));
    						//System.out.println(planLoanInfo.getPlanOverDueTotalReturnPayment());
    						break;
    					case 11:
    						data = (String) datas.get(11);
    						planLoanInfo.setPlanCustMagTotal((int)Double.parseDouble(data));
    						//System.out.println(planLoanInfo.getPlanCustMagTotal());
    						break;
    					case 12:
    						data = (String) datas.get(12);
    						planLoanInfo.setPlanPayRollTotal((int)Double.parseDouble(data));
    						//System.out.println(planLoanInfo.getPlanPayRollTotal());
    						break;
    					}
    				}
    				infos.add(planLoanInfo);
    			}
    			//批量插入
    			int totalRecord = planLoanInfoService.insertIntoBatch(infos);
    			return totalRecord;
    		} catch (Exception e) {
    			e.printStackTrace();
    			throw new ExcelOperateException("excel导入数据操作异常!");
    		}
    	}

mybaits批量插入:

<insert id="insertIntoBatchIgnore" parameterType="java.util.List">
    	    INSERT INTO tb_plan_xiaodai_loaninfo(PLANPAYAMT,PLANTOTALINCOME,PLANTOTALPROFIT,PLANINLIBBALANCE,PLANCUSTMAGPERLIB,PLANPEREARNER,PLANPERPROFIT
    		,PLANOVERDUERATEBIG,PLANOVERDUERATESMALL,PLANPARTSARE,PLANOVERDUETOTALRETURNPAYMENT,PLANCUSTMAGTOTAL,PLANPAYROLLTOTAL,CREATEDATE)
    		VALUES 
    	    <foreach collection="list" item="item" index="index" separator="," >
    	        (#{item.planPayAmt},#{item.planTotalIncome},#{item.planTotalProfit},#{item.planInlibBalance},#{item.planCustMagPerLib},#{item.planPerEarner},#{item.planPerProfit},#{item.planOverDueRateBig},#{item.planOverDueRateSmall}
    	        ,#{item.planPartsAre},#{item.planOverDueTotalReturnPayment},#{item.planCustMagTotal},#{item.planPayRollTotal},now())
    	    </foreach>
    	</insert>

html:

<form id="from" action="${ctx}/evaluation/filePage" method="post" enctype="multipart/form-data" target="_self"  οnsubmit="return checkImport()">
                        <table class="table table-hover table-striped" style="table-layout:fixed">
                          <tbody id="loadData">
                          <tr>
                          	<td><input type="file" name="docFile" value="" id="docFile" style="width:180px;" validate="{required:true}"></td>
    						<td align="left">
    							<button type="submit" class="btn green">导入</button>
    						</td>
    						<td>
    							<a class="btn btn-default" role="button" οnclick="exportPlanLoanInfo();">下载xxx信息表 </a>
    						</td>
                          </tr>
                          </tbody>
                        </table>
                       </form>

下载excel模版:

function exportPlanLoanInfo(){
    	window.location.href= "${ctx }/evaluation/downloadExcel";
    }

@RequestMapping("downloadExcel")
    	public void downloadExcel() {
    		String filePath = PropertiesUtil.getProperties("PLAN_LOANINFO_FILE_PATH").toString();
    		OutputStream out=null;
    		try {
    			String name="个贷计划信息表";
    			String fileTxt = filePath + name +".xlsx";
    			FileInputStream file=new FileInputStream(fileTxt);
    			response.reset();// 清空输出流   
    			out = response.getOutputStream();			
    		    response.setHeader("Content-disposition", "attachment; filename="+new String(name.getBytes("GBK"), "ISO-8859-1")+".xlsx");// 设定输出文件头   
    		    response.setContentType("application/ms-excel;charset=UTF-8");// 定义输出类型 
    		    byte[] buffer=new byte[1024];
    			int ch=0;
    			 while ((ch = file.read(buffer)) != -1) {
    				   out.write(buffer,0,ch);
    				  }		
    		} catch (IOException e1) {
    			e1.printStackTrace();
    		}		
    		try {
    			out.close();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}

其中filePath为excel所在文件位置。