表格的导入

public Object InputExcelOld(String filename, InputStream os, int issuerId,HashMap<String, String> provinceMap, HashMap<String, String> cityMap,Date paidTime) {int beginRowIndex = 1;// 从excel 中开始读取的起始行数int totalRows = 0;// 从excel 表的总行数String messeger = "";// excel出错的信息List<IsicApplicant> isisApplicants = new ArrayList<IsicApplicant>();try {// 根据文件的输入流，创建对Excel 工作薄文件的引用HSSFWorkbook workbook = new HSSFWorkbook(os);// 默认exce的书页是“sheet1”HSSFSheet sheet = workbook.getSheetAt(0);// 得到该excel 表的总行数totalRows = sheet.getLastRowNum();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// System.out.println("总行数是"+totalRows);// 循环读取excel表格的每行记录，并逐行进行保存for (int i = beginRowIndex; i <= totalRows; i++) {int index = 1;System.out.println("totalRows="+totalRows);try {HSSFRow row = sheet.getRow(i); // 获取一行每列的数据HSSFCell appliCardNumberTypecell = row.getCell(0);// 申请卡片类型System.out.println(appliCardNumberTypecell);HSSFCell appliCardNumberIdcell = row.getCell(1); // 卡片numberHSSFCell applicantNamecell = row.getCell(2);// 申请人英文名HSSFCell applicantNameCNcell = row.getCell(3);// 申请人中文名HSSFCell appliBirthdaycell = row.getCell(4);// 申请人出生日期System.out.println( row.getCell(4));HSSFCell appliSchoolNameCNcell = row.getCell(5);// 申请人学校中文名HSSFCell appliSchoolNamecell = row.getCell(6);// 申请人学校英文名HSSFCell appliEmailcell = row.getCell(7);// 申请人电子邮件HSSFCell appliMobilcell = row.getCell(8);// 申请人联系电话HSSFCell appliQQNmbercell = row.getCell(9);// 申请人qq号HSSFCell appliGendercell = row.getCell(10);// 申请人性别HSSFCell appliIDNumbercell = row.getCell(11);// 申请人身份证号码HSSFCell cardVailFromcell = row.getCell(12);// 卡片有效期FORMHSSFCell cardVailTocell = row.getCell(13);// 卡片有效期TOHSSFCell appliAddresscell = row.getCell(14);// 卡片邮件地址HSSFCell appliZipCodecell = row.getCell(15);// 邮编// HSSFCell appliNotecell = row.getCell(16);// 将数据赋给相关的变量// 创建isicapplicat保存数据IsicApplicant isic = new IsicApplicant();//添加是否删除默认值0  IS_DELETE(jerry)isic.setIsDeleted("0");//添加是否删打印认值0  IS_Print(jerry)isic.setIsPrinted("0");if (appliCardNumberTypecell != null) {if (appliCardNumberTypecell.getRichStringCellValue().toString().equalsIgnoreCase("isic")) {isic.setCardType(IsicCard.CARD_TYPE_ISIC);} else if (appliCardNumberTypecell.getRichStringCellValue().toString().equalsIgnoreCase("itic")) {isic.setCardType(IsicCard.CARD_TYPE_ITIC);} else {isic.setCardType(IsicCard.CARD_TYPE_IYTC);}} else {// 若没有找到默认是ISIC学生卡isic.setCardType(IsicCard.CARD_TYPE_ISIC);}index++;if (appliCardNumberIdcell != null) {String cardNumber = appliCardNumberIdcell.getRichStringCellValue().toString().replace(" ", "");IsicCard card = cs.findIsicCardById(cardNumber);if (!cardNumber.equals("")) {if (cardNumber.length() != 14) {messeger = "Card number is not correct";throw new Exception("卡号不正确");}if (card != null && card.getCardStatus() == 0) {isic.setCardNumber(cardNumber);} else {messeger = "Card number does not exist, or has been assigned";throw new Exception("卡号不存在,或已被分配");}}}index++;if (applicantNamecell != null) {messeger = "applicantName is not correct";String str = applicantNamecell.getRichStringCellValue().toString().trim();if (str.length() > 50) {throw new Exception("appliteName too loog");}isic.setApplicantName(str);} else {break;}index++;if (applicantNameCNcell != null) {messeger = "applicantNameCN is not correct";String str = applicantNameCNcell.getRichStringCellValue().toString();if (str.length() > 50) {throw new Exception("appliteNameCN too loog");}isic.setApplicantNameCn(str);}index++;if (appliBirthdaycell != null) {messeger = "Birthday is not correct";isic.setBirthday(appliBirthdaycell.getDateCellValue());}//if (appliBirthdaycell != null) {//messeger = "Birthday is not correct";//System.out.println("生日："+appliBirthdaycell);//String str = appliBirthdaycell.getRichStringCellValue().toString();//System.out.println("生日格式："+str);//SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd");//Date str1 = sdf1.parse(str); //isic.setBirthday(str1);//}index++;if (appliSchoolNamecell != null) {messeger = "SchoolName is not correct";String str = appliSchoolNamecell.getRichStringCellValue().toString();if (str.length() > 100) {throw new Exception("SchoolName too loog");}isic.setSchoolName(str);}index++;if (appliSchoolNameCNcell != null) {messeger = "SchoolNameCN is not correct";String str=appliSchoolNameCNcell.getRichStringCellValue().toString();if (str.length() > 50) {throw new Exception("SchoolNameCN too loog");}isic.setSchoolNameCn(str);}index++;if (appliEmailcell != null) {messeger = "The mailbox is not correct";String reg = "^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$";String str = appliEmailcell.getRichStringCellValue().toString().trim();if (str.length() > 100) {throw new Exception("SchoolNameCN too loog");}if (!(str.matches(reg))) {messeger = "The mailbox is not correct";throw new Exception("邮箱不正确");}isic.setEmail(str);}index++;if (appliMobilcell != null) {messeger = "appliMobilcell is not correct";String str=appliMobilcell.toString().trim();if (str.length() > 30) {throw new Exception("appliMobilcell too loog");}try {isic.setMobile((long) (appliMobilcell.getNumericCellValue())+"");} catch (Exception e) {isic.setMobile(str);}}index++;if (appliQQNmbercell != null) {messeger = "QQNmber is not correct";String str = appliQQNmbercell.toString();if (str.length() > 30) {throw new Exception("QQNmber too loog");}try {isic.setQqNumber((long) (appliQQNmbercell.getNumericCellValue())+"");} catch (Exception e) {isic.setQqNumber(str);}}index++;if (appliGendercell != null) {messeger = "Gender is not correct";isic.setGender(swicthsex(appliGendercell.getRichStringCellValue().toString()));}index++;if (appliIDNumbercell != null) {messeger = "IDNumber is not correct";String str=appliIDNumbercell.toString().trim();if (str.length() > 30) {throw new Exception("IDNumber too loog");}try {isic.setIdNumber((long) (appliIDNumbercell.getNumericCellValue())+"");} catch (Exception e) {isic.setIdNumber(str);}}index++;if (cardVailFromcell != null) {messeger = "CardValidDateFrom Format Error ";isic.setCardVailFrom(sdf.format(cardVailFromcell.getDateCellValue()));}index++;if (cardVailTocell != null) {messeger = "CardValidDateTo Format Error ";isic.setCardVailTo(sdf.format(cardVailTocell.getDateCellValue()));}index++;if (appliAddresscell != null) {messeger = "Address  Error ";String str=appliAddresscell.getRichStringCellValue().toString(); if(str.length()>100){throw new Exception(messeger);}isic.setAddress(str);isic.setExt05(str);}index++;if (appliZipCodecell != null) {messeger = "postCode Error ";String str = appliZipCodecell.getNumericCellValue()+"";if(str.length()>20){throw new Exception(messeger);}try {isic.setZipcode((long) (appliZipCodecell.getNumericCellValue())+"");} catch (Exception e) {isic.setZipcode(str);}}index++;if (isic.getCardNumber() != null&& isic.getCardNumber().length() > 0) {isic.setStatus(IsicApplicant.ACCEPTED);} else {isic.setStatus(IsicApplicant.PAID);}index++;isic.setApplyTime(paidTime);index++;isic.setIssuerId(issuerId);index++;isisApplicants.add(isic);} catch (Exception e) {return "Your Excel have error in row:" + (i + 1)+ " column:" + (char) (index + 64) + "   ( "+ messeger + " ) ";}}log.info("Excel file import success (" + totalRows + ")!");} catch (IOException e) {return "Your excel file format is incorrect,please upload excel format with .xls(97-2003).";}return isisApplicants;}

引入文件

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

引入 poi.jar