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); // 卡片number
HSSFCell 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);// 卡片有效期FORM
HSSFCell cardVailTocell = row.getCell(13);// 卡片有效期TO
HSSFCell 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