首先是效果图
excel表格内容
页面
数据库
jsp代码
控制层代码
@RequestMapping("/importAdmissionExcel")
public String importAdmissionExcel(ModelMap map, HttpServletRequest request, MultipartHttpServletRequest requestf) throws SQLException {
MultipartFile zipFile = requestf.getFile("zipFile");
String rets = "";
if(zipFile==null){
rets+=",请上传准考证库.xls文件";
}else{
if(!zipFile.getOriginalFilename().toLowerCase().endsWith(".xls")){
rets+=",上传准考证文件只能是.xls格式文件";
}
}
if(!rets.equals("")){
map.put("msg", rets.substring(1));
return "library/otheradmission/importExcel";
}
Connection conn = null;
PreparedStatement pstmt = null;
try {
String level = userDetails.getLevelid();
Long startTime = System.currentTimeMillis();
String examId = (String) request.getSession().getAttribute("currentExamId");
String depcode = userDetails.getDepcode().trim();
String zipFileName = "" + zipFile.getName() + ".xls";
String targetDirectory = StaticConstants.getAdmissionPicPath(examId, depcode);
if (!new File(targetDirectory).exists()) {
new File(targetDirectory).mkdirs();
}
File ziptarget = new File(targetDirectory, zipFileName);
FileUtils.copyInputStreamToFile(zipFile.getInputStream(), ziptarget);
String tableNameDataAdmission2 = "EXAM_" + examId + "_ADMISSION_DATA";
//System.out.println(tableNameDataAdmission2);
if (this.commonJdbcdao.isExistTable(tableNameDataAdmission2)) { //判断准考证数据是否已经导入
//Boolean issuper = userDetails.getSuperuser();
String uuname = userDetails.getUsername();
String depcodeg = userDetails.getDepcode();
}
//创建新的考生准考证表
String tableNameDataAdmission = "EXAM_" + examId + "_ADMISSION_DATA";
if (!this.admissionService.isExistTable(tableNameDataAdmission)) {
StringBuilder sb = new StringBuilder("CREATE TABLE " + tableNameDataAdmission + " (");
sb.append(" `ZJBH` varchar(64) NOT NULL DEFAULT '', ");
sb.append(" `KMDM` varchar(32) NOT NULL DEFAULT '',");
sb.append(" `ZKZH` varchar(96) DEFAULT NULL,");
sb.append(" `SS` varchar(32) DEFAULT NULL,");
sb.append(" `KQ` varchar(32) DEFAULT NULL, ");
sb.append(" `KDH` varchar(32) DEFAULT NULL,");
sb.append(" `KCH` varchar(16) DEFAULT NULL,");
sb.append(" `ZWH` varchar(16) DEFAULT NULL,");
sb.append(" `SIGNUPID` varchar(144) DEFAULT NULL,");
sb.append(" `ZKZDY` decimal(10,0) DEFAULT '0',");
sb.append(" `ZKZDY_DATE` varchar(20) DEFAULT NULL,");
sb.append(" PRIMARY KEY (`ZJBH`,`KMDM`),");
sb.append(" KEY `IN_EXAM_" + examId + "_ADMISSION_DATA` (`ZJBH`)");
sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8");
//创建表
if (this.admissionService.isExistTable(tableNameDataAdmission.toUpperCase()) == false) {
this.commonJdbcdao.getJdbcTemplate().update(sb.toString());
}
}
//创建新的考生准考证表
String _dep_admis = "EXAM_" + examId + "_dep_admis";
if (!this.admissionService.isExistTable(_dep_admis)) {
StringBuilder sb = new StringBuilder("CREATE TABLE " + _dep_admis + " (");
sb.append(" `DEPCODE` varchar(20) NOT NULL COMMENT 'add field length for encrpty',");
sb.append(" `LEVELID` varchar(20) NOT NULL DEFAULT '',");
sb.append(" `TITLE` varchar(120) DEFAULT NULL,");
sb.append(" `PARENTCODE` varchar(20) DEFAULT NULL,");
sb.append(" `INCHARGE` varchar(36) DEFAULT NULL,");
sb.append(" `PHONE1` varchar(20) DEFAULT NULL,");
sb.append(" `PHONE2` varchar(20) DEFAULT NULL,");
sb.append(" `PHONE3` varchar(20) DEFAULT NULL,");
sb.append(" `ADDRESS` varchar(200) DEFAULT NULL,");
sb.append(" `ZIP` varchar(32) DEFAULT NULL,");
sb.append(" `EMAIL` varchar(64) DEFAULT NULL,");
sb.append(" `PXM` varchar(2) DEFAULT NULL,");
sb.append(" `KCBZ` varchar(5) DEFAULT NULL,");
sb.append(" `KSFZR` varchar(10) DEFAULT NULL,");
sb.append(" `LXFS` varchar(20) DEFAULT NULL,");
sb.append(" `XH` varchar(2) DEFAULT NULL,");
sb.append(" `SFKDCS` varchar(2) DEFAULT NULL,");
sb.append(" `CPKD` varchar(2) DEFAULT NULL,");
sb.append(" PRIMARY KEY (`DEPCODE`,`LEVELID`)");
sb.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
//创建表
if (this.admissionService.isExistTable(_dep_admis.toUpperCase()) == false) {
this.commonJdbcdao.getJdbcTemplate().update(sb.toString());
}
}
String sql_count = "SELECT count(*) FROM exam_" + examId + "_department WHERe parentcode = '144' ";
Integer drcount = this.commonJdbcdao.getJdbcTemplate().queryForInt(sql_count);
if (drcount == 0){
String sql = "INSERT INTO exam_"+examId+"_department (DEPCODE,LEVELID,TITLE,PARENTCODE) " +
"SELECt DEPCODE,LEVELID,TITLE,PARENTCODE FROM public_department WHERe parentcode = '144' ";
this.commonJdbcdao.getJdbcTemplate().update(sql);
}
String zkz_count = "SELECT COUNT(*) FROM tp_global_exam_zkz WHERe examid = '" + examId + "' ";
Integer zkzcount = this.commonJdbcdao.getJdbcTemplate().queryForInt(zkz_count);
if (zkzcount == 0){
String sql = "INSERT INTO tp_global_exam_zkz (examid,CODE,begindate,fulltitle) " +
"VALUES ('"+examId+"','"+examId+"','"+DateUtil.getCurrentDate().replace("-","").substring(0,6)+"','"+examId+"') ";
this.commonJdbcdao.getJdbcTemplate().update(sql);
}
// this.commonJdbcdao.getJdbcTemplate().update("drop table Exam_" + examId + "_ADM_CHANGE");
if (!this.admissionService.isExistTable("Exam_" + examId + "_ADM_CHANGE")) {
threeCommonService.createlinkTable("Exam_" + examId + "_ADM_CHANGE", "Exam_" + examId + "_ADM_CHANGE_PHOTO", "Exam_" + examId + "_ADM_CHANGE_SUBS", examId);
}
this.commonJdbcdao.getJdbcTemplate().update("TRUNCATE table Exam_" + examId + "_ADM_CHANGE");
Map zkzStatuesMap = this.queryZkzStatus(examId, userDetails.getDepcode());
if(zkzStatuesMap != null && !zkzStatuesMap.isEmpty() ){
String zkzStatus = (String)zkzStatuesMap.get("ZKZSTATUS");
if(zkzStatus != null && zkzStatus.equals("1")){
map.put("msg", "准考证信息正在导入还未完成，请稍后查看结果！");
return "library/otheradmission/importExcel";
}
}
//System.out.println("开始上传：" + DateUtil.getCurrentDateTime());
String yszipFile = zipFile.getOriginalFilename();
String[] yzxx = yszipFile.split("_");
long templateId = this.admissionService.queryTemplateId(Long.valueOf(examId));
List templateList = this.commonService.findExamByExamId(templateId); //根据数据包文件名进行校验
// Statement stmt = null;
DESPlus des = new DESPlus(0);
des.setNotEncryptSwitch(false);
ReadDBF read = new ReadDBF();
ReadWriteFile readexcel = new ReadWriteFile();//excel读取方法
String changeColName = " ZJHM, KSXM, BMDS,ZJBH, ZJLB, SS, SIGNUPID";
String dbfColName = changeColName;
int readcount = read.getReadcount();
String insertChangeStr = "";
insertChangeStr = "INSERT INTO " + " Exam_" + examId + "_ADM_CHANGE " + " (ZJHM, KSXM, BMDS,ZJBH, ZJLB, SS, SIGNUPID) VALUES(";
for (int i = 0; i < changeColName.split(",").length; i++) {
insertChangeStr += "?,";
}
insertChangeStr = insertChangeStr.substring(0, insertChangeStr.length() - 1);
insertChangeStr += " )";
conn = this.commonJdbcdao.getJdbcTemplate().getDataSource().getConnection();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(insertChangeStr);
Workbook workbook =readexcel.getWorkbook(targetDirectory+"/"+zipFileName);//读取excel
int zhs = 0;
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
// 读取首行 即,表头
String rw = "";
Row firstRow = sheet.getRow(firstRowIndex);
if (firstRow == null) {
continue;
}
Cell cell = firstRow.getCell(0);
rw = readexcel.getCellValue(cell, true);
Long batchCount = 1L;
int ksxmsz=99;
int zjhmsz=99;
int bmdssz=99;
for(int bt=0;bt<3;bt++){
if("KSXM".equals(readexcel.getCellValue(firstRow.getCell(bt), true))){
ksxmsz=bt;
}
if("ZJHM".equals(readexcel.getCellValue(firstRow.getCell(bt), true))){
zjhmsz=bt;
}
if("BMDS".equals(readexcel.getCellValue(firstRow.getCell(bt), true))){
bmdssz=bt;
}
}
System.out.println("KSXM="+ksxmsz+"ZJHM="+zjhmsz+"BMDS="+bmdssz);
if (ksxmsz==99||zjhmsz==99||bmdssz==99){
map.put("msg", "导入准考证信息异常！");
}
for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
batchCount++;
Row currentRow = sheet.getRow(rowIndex);// 当前行
// String ksxm = readexcel.getCellValue(currentRow.getCell(3), true);
// String mzjhm = readexcel.getCellValue(currentRow.getCell(2), true);
// String zjhm = des.encrypt_r(mzjhm.toUpperCase());
zhs += 1;
System.out.println("Excel数据文件读取完成：" + DateUtil.getCurrentDateTime() + "，used:" + (System.currentTimeMillis() - startTime) / 1000 + "s");
// stmt = conn.createStatement();
Long signupid = 0L;
System.out.println("开始将关联表数据同步到数据库：" + DateUtil.getCurrentDateTime());
startTime = System.currentTimeMillis();
System.out.println("开始插入临时表：" + DateUtil.getCurrentDateTime());
if (batchCount % 3000 == 0) {
pstmt.executeBatch();
// stmt.executeBatch();
conn.commit();
pstmt.clearBatch();
System.out.println("关联表提交数据量：" + batchCount + " " + DateUtil.getCurrentDateTime());
}
Map map1 = new HashMap<>();
map1.put("广州", "14401");
map1.put("深圳", "14402");
map1.put("珠海", "14403");
map1.put("汕头", "14404");
map1.put("韶关", "14405");
map1.put("河源", "14406");
map1.put("梅州", "14407");
map1.put("惠州", "14408");
map1.put("汕尾", "14409");
map1.put("东莞", "14410");
map1.put("中山", "14411");
map1.put("江门", "14412");
map1.put("佛山", "14413");
map1.put("阳江", "14414");
map1.put("湛江", "14415");
map1.put("茂名", "14416");
map1.put("肇庆", "14417");
map1.put("清远", "14418");
map1.put("潮州", "14419");
map1.put("揭阳", "14420");
map1.put("云浮", "14421");
map1.put("省直", "14499");
pstmt.setString(1, currentRow.getCell(zjhmsz) != null ? des.encrypt(readexcel.getCellValue(currentRow.getCell(zjhmsz), true).trim()) : "");
pstmt.setString(2, currentRow.getCell(ksxmsz) != null ? des.encrypt(readexcel.getCellValue(currentRow.getCell(ksxmsz), true).trim()) : "");
String bmds1=readexcel.getCellValue(currentRow.getCell(bmdssz), true);
if (readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("广州")){
pstmt.setString(3, map1.get("广州"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("深圳")){
pstmt.setString(3, map1.get("深圳"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("珠海")){
pstmt.setString(3, map1.get("珠海"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("珠海")){
pstmt.setString(3, map1.get("珠海"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("汕头")){
pstmt.setString(3, map1.get("汕头"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("韶关")){
pstmt.setString(3, map1.get("韶关"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("河源")){
pstmt.setString(3, map1.get("河源"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("梅州")){
pstmt.setString(3, map1.get("梅州"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("惠州")){
pstmt.setString(3, map1.get("惠州"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("汕尾")){
pstmt.setString(3, map1.get("汕尾"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("东莞")){
pstmt.setString(3, map1.get("东莞"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("中山")){
pstmt.setString(3, map1.get("中山"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("江门")){
pstmt.setString(3, map1.get("江门"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("佛山")){
pstmt.setString(3, map1.get("佛山"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("阳江")){
pstmt.setString(3, map1.get("阳江"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("湛江")){
pstmt.setString(3, map1.get("湛江"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("茂名")){
pstmt.setString(3, map1.get("茂名"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("肇庆")){
pstmt.setString(3, map1.get("肇庆"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("清远")){
pstmt.setString(3, map1.get("清远"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("潮州")){
pstmt.setString(3, map1.get("潮州"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("揭阳")){
pstmt.setString(3, map1.get("揭阳"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("云浮")){
pstmt.setString(3, map1.get("云浮"));
}else if(readexcel.getCellValue(currentRow.getCell(bmdssz), true).contains("省直")){
pstmt.setString(3, map1.get("省直"));
}
pstmt.setString(4, String.valueOf(rowIndex));
pstmt.setString(5,"1");
pstmt.setString(6,"144");
pstmt.setString(7, String.valueOf(rowIndex));
// int m = 1;
// for (int o = 0; o < 7; o++) {
// if (o == 0 || o == 1) {
// pstmt.setString(m++, currentRow.getCell(o) != null ? des.encrypt(readexcel.getCellValue(currentRow.getCell(o), true).trim()) : "");
// System.out.println(readexcel.getCellValue(currentRow.getCell(o), true));
// }else if(o == 5 ){
// pstmt.setString(m++,"144");
// }else if(o == 4 ){
// pstmt.setString(m++,"1");
// }else if(o == 3 || o == 6){
// pstmt.setString(m++, String.valueOf(rowIndex));
// } else if(o == 2){
// String bmds1=readexcel.getCellValue(currentRow.getCell(o), true);
// System.out.println("bmds="+bmds1);
// pstmt.setString(m++,map1.get(bmds1));
// }
// }
pstmt.addBatch();
}
pstmt.executeBatch();
conn.commit();
pstmt.clearBatch();
conn.close();
}
String resultStr = " 考场excel数据文件共" + zhs + "条记录，"
+ "插入" + zhs + "条";
map.put("msg", resultStr);
System.out.println("end----"+resultStr);
} catch (Exception e) {
pstmt.clearBatch();
pstmt.close();
conn.rollback();
conn.close();
map.put("msg", "导入准考证信息异常！");
// this.updateZkzStatus(examid, userDetails.getDepcode(), "3");//异常状态
e.printStackTrace();
logService.addLog(((Security) SecurityContextHolder.getContext().getAuthentication().getPrincipal()).getUsername(), 1, "导入准考证失败", IpAddr.getIpAddr(ServletActionContext.getRequest()), examid);
return "library/otheradmission/importExcel";
}
return "library/otheradmission/importExcel";
}
以上代码是读取excel内容，创建数据库表，并将内容按照要求进行转换，并插入
下面是读取excel文件操作
public class ReadWriteFile {
//指定文件路径和名称
private String filePath;
private static final String EXTENSION_XLS = "xls";
private static final String EXTENSION_XLSX = "xlsx";
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public void creatTxtFile() throws IOException {
File filename = new File(this.getFilePath());
if (!filename.exists()) {
filename.createNewFile();
}
}
public String readTxtFile() {
String readStr = "";
try {
BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(this.getFilePath()), "UTF-8"));
String s = "";
while ((s = in.readLine()) != null) {
readStr += s + "n";
}
in.close();
} catch (Exception ex) {
Logger.getLogger(ReadWriteFile.class.getName()).log(Level.SEVERE, null, ex);
}
return readStr;
}
public void writeTxtFile(String newStr) throws IOException {
this.creatTxtFile();
try {
BufferedWriter osw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(this.getFilePath(), false), "UTF-8"));
osw.write(newStr);
osw.flush();
osw.close();
} catch (Exception ex) {
//ex.printStackTrace();
}
}
public Workbook getWorkbook(String filePath) throws IOException {
Workbook workbook = null;
InputStream is = new FileInputStream(filePath);
if (filePath.endsWith(EXTENSION_XLS)) {
workbook = new HSSFWorkbook(is);
} else if (filePath.endsWith(EXTENSION_XLSX)) {
workbook = new XSSFWorkbook(is);
}
return workbook;
}
public void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException {
// 常规检查
File file = new File(filePath);
if (!file.exists()) {
throw new FileNotFoundException("传入的文件不存在：" + filePath);
}
if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) {
throw new FileFormatException("传入的文件不是excel");
}
}
public void readExcel(String filePath) throws FileNotFoundException, FileFormatException {
// 检查
this.preReadCheck(filePath);
// 获取workbook对象
Workbook workbook = null;
try {
workbook = this.getWorkbook(filePath);
// 读文件 一个sheet一个sheet地读取
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
System.out.println("=======================" + sheet.getSheetName() + "=========================");
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
// 读取首行 即,表头
Row firstRow = sheet.getRow(firstRowIndex);
for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
Cell cell = firstRow.getCell(i);
String cellValue = this.getCellValue(cell, true);
System.out.print(" " + cellValue + "t");
}
System.out.println("");
// 读取数据行
for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
Row currentRow = sheet.getRow(rowIndex);// 当前行
int firstColumnIndex = currentRow.getFirstCellNum(); // 首列
int lastColumnIndex = currentRow.getLastCellNum();// 最后一列
for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
String currentCellValue = this.getCellValue(currentCell, true);// 当前单元格的值
System.out.print(currentCellValue + "t");
}
System.out.println("");
}
System.out.println("======================================================");
}
} catch (Exception e) {
//e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
//e.printStackTrace();
}
}
}
}
public String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
if (treatAsStr) {
// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
// 加上下面这句，临时把它当做文本来读取
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
