使用POI导入Excel数据到系统

1.首先通过页面的上传组件上传一个excel类型的文件后，在java段能得到一个File类型的对象，如果你使用的是struts1，得到的File对象可能是FormFile类型的，如下：

 

FormFile file；

2.得到File对象后，就可以进行处理，一行一行的读取数据

 

try {POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(file.getInputStream()));HSSFWorkbook wb = new HSSFWorkbook(fs);// 全局验证.HSSFSheet sheet = wb.getSheetAt(0);int rowsnum = sheet.getLastRowNum();if (rowsnum < 1) {//如果没有数据，进行处理，如提示用户resStr = LocaleMessage.getValue(request, "file.data.is.empty");file.getInputStream().close();} else {HSSFRow row = sheet.getRow(0);//HSSFCell cell0 = null;String celval = "";List codeList = new ArrayList();for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {row = sheet.getRow(i);HSSFCell cell0 = row.getCell((short) 0);    //订单号HSSFCell cell1  = row.getCell((short) 1 );  //订单名称HSSFCell cell2  = row.getCell((short) 2 );  //员工号HSSFCell cell3  = row.getCell((short) 3 );  //约定到期日HSSFCell cell4  = row.getCell((short) 4 );  //实际到账日HSSFCell cell5  = row.getCell((short) 5 );  //提前回款金额HSSFCell cell6  = row.getCell((short) 6 );  //提前回款天数HSSFCell cell7  = row.getCell((short) 7 );  //节省资金成本奖励HSSFCell cell8  = row.getCell((short) 8 );  //奖励工资HSSFCell cell9  = row.getCell((short) 9 );  //奖励信息费HSSFCell cell10 = row.getCell((short) 10);//奖励费用//将获取到的数据保存到业务对象BusinessSanctionTqhk tqhk = new BusinessSanctionTqhk();tqhk.setLastUpdateUser(userName);tqhk.setIsuse("T");tqhk.setYf(paraseDate(sjyf));tqhk.setOrderNumber(getCellValue(getCellValue(cell0)));tqhk.setCustomerFullName(getCellValue(getCellValue(cell1)));tqhk.setBusiness(getCellValue(getCellValue(cell2)));tqhk.setYdqr(getCellValue(getCellValue(cell3)));tqhk.setSjdzrq(getCellValue(getCellValue(cell4)));tqhk.setTjhkje(getCellValue(getCellValue(cell5)));tqhk.setTjhkts(getCellValue(getCellValue(cell6)));tqhk.setJszjcbjl(getCellValue(getCellValue(cell7)));tqhk.setJlgzFifteen(getCellValue(getCellValue(cell8)));tqhk.setJlxxfSeventeen(getCellValue(getCellValue(cell9)));tqhk.setJlfyTen(getCellValue(getCellValue(cell10)));codeList.add(tqhk);}if (codeList.size() > 0) {//删除数据库中相同月份的数据，根据实际处理this.deleteByFullStatementName("com.yutong.crm.sanction.model.SanctionReportImport.deleteTqhkjl", sjyf);//循环写入数据库for(int i=0;i<codeList.size();i++){BusinessSanctionTqhk tqhk = (BusinessSanctionTqhk)codeList.get(i);this.insertByFullStatementName("com.yutong.crm.sanction.model.SanctionReportImport.insertTqhkjl", tqhk);}}}resStr = resStr+"导入 提前还款奖励表 数据成功";} catch (Exception e) {// TODO: handle exceptionresStr = "导入 提前还款奖励表 数据失败"+resStr +"\n"+e.getMessage()+"\n";}

3.下面是对Excel表格中不同类型内容的处理

public final static DateFormat DEFAULT_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");/** * 对excel表格的值进行处理. *  * @param cell *            cell * @return str */private String getCellValue(HSSFCell cell) {//先判断是不是日期int dataFormat = cell.getCellStyle().getDataFormat();if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {  return getDateValue(cell); } String str = "";// System.err.println("***************TYPE is "+cell.getCellType());if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {//也可以在这里先判断是不是日期//if(HSSFDateUtil.isCellDateFormatted(cell)){//return getDateValue(cell); //}double dd = cell.getNumericCellValue();DecimalFormat df = new DecimalFormat("0.##");str = String.valueOf(df.format(dd)); // .trim();}if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {str = cell.getStringCellValue(); // .trim();}if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {str = String.valueOf(cell.getBooleanCellValue()); // .trim();}if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {str = ""; // .trim();}return str.trim();}private String getCellValue(String cellValue) {if (cellValue != null && cellValue.length() > 0) {return cellValue.trim();//if (cellValue.indexOf(".0") > 0)//return cellValue.substring(0, cellValue.length() - 2);//根据实际情况处理//else//return cellValue;} elsereturn "";}/**   *  返回时间内的特殊时间格式 OFFICE2003   *  @param cell   * @return   */ private static String getDateValue(HSSFCell cell){ return DEFAULT_DATE_FORMAT.format(cell.getDateCellValue()); }public Date paraseDate(String sjyf){    SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM");    try {Date date = sFormat.parse(sjyf);return date;} catch (ParseException e) {// TODO Auto-generated catch blocke.printStackTrace();}//出错插入当前日期return new Date();    }