public class ExcelImport {
public static AjaxMsg importPerson(HttpServletRequest request, String fileId, File file,
IFileService fileService, IPersonService psersonService) throws IOException {
style = null;// style null
InputStream is = new FileInputStream(file);
Workbook workbook = new HSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
boolean error = false;// excel
AjaxMsg msg = new AjaxMsg(true, "");
DecimalFormat df = new DecimalFormat("#"); //
Map<String, List<TPerson>> maps = new HashMap<String, List<TPerson>>();
if (true) {
List<TPerson> personList = new ArrayList<TPerson>();
for (int i = 3; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
TPerson person = new TPerson();
// name
Cell cell1 = row.getCell(0);
if (cell1 != null) {
if (cell1.getCellType() == Cell.CELL_TYPE_STRING) {
person.setName(cell1.getStringCellValue());
} else if (cell1.getCellType() == Cell.CELL_TYPE_NUMERIC) {
person.setName(cell1.getNumericCellValue() + "");
}
} else {
person.setName("");
}
if (person.getName() == null || person.getName().trim() == "") {// ，
cell1.setCellStyle(createRedStyle(workbook,cell1.getCellStyle()));
error = true;
}
// oldname
Cell cell2 = row.getCell(1);
if (cell2 != null) {
if (cell2.getCellType() == Cell.CELL_TYPE_STRING) {
person.setOldName(cell2.getStringCellValue());
} else if (cell2.getCellType() == Cell.CELL_TYPE_NUMERIC) {
person.setOldName(cell2.getNumericCellValue() + "");
}
} else {
person.setOldName("");
}
// shortname
Cell cell3 = row.getCell(2);
if (cell3 != null) {
if (cell3.getCellType() == Cell.CELL_TYPE_STRING) {
person.setShortName(cell3.getStringCellValue());
} else if (cell3.getCellType() == Cell.CELL_TYPE_NUMERIC) {
person.setShortName(cell3.getNumericCellValue()+ "");
}
} else {
person.setShortName("");
}
personList.add(person);
}
maps.put(person.getName(), personList);
}
if (is != null) {
is.close();
}
try {
if (error) {
maps.clear();
msg.setSuccess(false);
String fUUID = createErrorFile(fileId, file, fileService, workbook);
msg.setResult(fUUID);// ，
msg.setMsg("valiError");
} else {
AjaxMsg result = personService.insertPersonData(request, maps);//
if (!result.isSuccess()) {
msg.setMsg("synError");
msg.setSuccess(false);
}
}
} catch (Exception e) {
e.printStackTrace();
msg.setMsg(" ");
msg.setSuccess(false);
}
return msg;
}
private static CellStyle style;
//
private static CellStyle createRedStyle(Workbook workbook,
CellStyle oldStyle) {
if(style!=null){
return style;
}
style = workbook.createCellStyle();
style.cloneStyleFrom(oldStyle);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.RED.index);
return style;
}
private static boolean isPhoneNumber(String input) {
Pattern p = null;
Matcher m = null;
boolean b = false;
p = Pattern.compile("^[1][3,4,5,8][0-9]{9}$"); //
m = p.matcher(input);
b = m.matches();
return b;
}
private static boolean isEmail(String input) {
boolean a = false;
a = input
.matches("^[a-z0-9A-Z]+[- | a-z0-9A-Z . _]+@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-z]{2,}$");
return a;
}
/**
* Excel ， Excel
*/
private static String createErrorFile(String fileId, File file,
IFileService fileService, Workbook workbook) throws IOException,
FileNotFoundException {
TFile oldFile = fileService.find(fileId);
OutputStream os = new FileOutputStream(file);
workbook.write(os);
if (os != null) {
os.flush();
os.close();
}
return oldFile.getUuid();
}
}