Java开发使用Excel批量导入数据

Excel批量导入数据
1. 问题场景
考试系统开发中，类似考题、学生信息等，手动录入太繁杂，在excel当中编辑好再导入相对便捷，因此需要这样一个接口。
2. 实现方法
Dao层省略，只列出接口层和服务层处理逻辑：
接口类ExcelDealControllerjava：
@Autowired
private QuestionService queService;


@RequestMapping(value= "/addques",method=RequestMethod.POST)
public String dealQuestionInfoExcel(MultipartFile file, String subId) {
String result =  queService.uploadQuestionInfoExcel(file,subId);
log.info("上传文件的结果："+result);


return "redirect:../../question.html";
}
处理类：QuestionService.java
@Autowired
private QuestionDao queDao;

@Autowired
private OptionDao optDao;


@Autowired
private ServletContext context;


private JSONObject resultObj;
private String subId = "";

// 处理上传考题的信息的excel
public String uploadQuestionInfoExcel(MultipartFile mf, String sId) {
resultObj = new JSONObject();
subId = sId;


// 获取上传路径baseUrl
String baseUrl = context.getRealPath("") + "\\upload\\excel\\";
if (null != mf) {
String fileName = mf.getOriginalFilename();
if (!"".equalsIgnoreCase(fileName)) {
// 传到服务器文件夹中的文件名称为 ctimeMillis+3个随机整数+原始文件名.suffix
StringBuilder sb = new StringBuilder("que_");
Long currentTimeMillis = System.currentTimeMillis();
String ctm = currentTimeMillis.toString();
// 截取后六位
ctm = ctm.substring(ctm.length() - 6, ctm.length());
sb.append(ctm);
for (int i = 0; i < 3; i++) {
sb.append((int) (Math.random() * 10));
}


// 获取后缀
String suffix = fileName.substring(fileName.indexOf("."),
fileName.length());
sb.append(suffix);
if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
// 上传的是excel
try {
mf.transferTo(new File(baseUrl + sb));
// insert Question
dealQuestionInfoExcel(baseUrl + sb);
resultObj.put(Constants.RESPONSE_CODE_KEY,
RESCODE.SUCCESS);
resultObj.put(Constants.RESPONSE_MSG_KEY,
RESCODE.SUCCESS.getMsg());
return resultObj.toString();
} catch (IllegalStateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ExcelAnalyzeException e) {
resultObj.put(Constants.RESPONSE_CODE_KEY,
RESCODE.EXCEL_ERROR);
resultObj.put(Constants.RESPONSE_MSG_KEY,
RESCODE.EXCEL_ERROR.getMsg());
e.printStackTrace();
return resultObj.toString();
}
}
}
}


resultObj.put(Constants.RESPONSE_CODE_KEY, RESCODE.FILE_ERROR);
resultObj.put(Constants.RESPONSE_MSG_KEY, RESCODE.FILE_ERROR.getMsg());


return resultObj.toString();
}

/**
* 解析考题信息的excel文档  excel中 按如下顺序 subject, level,type,typeName, content, answer;
* 若题目类型是选择题还要读取选项字段options,格式如下："A&&content$$B&&content"
* @param filepath
* @throws ExcelAnalyzeException 
*/
private Question q;
private void dealQuestionInfoExcel(String filepath) throws ExcelAnalyzeException{
try{
List<List<String>> analyzeExcel = AnalyzeExcel.analyzeExcel(0, filepath);
for(List<String> list :analyzeExcel){

q = new Question();
q.setSubjectId(subId);//科目id
q.setSubject(list.get(0));//科目
q.setLevel(Integer.parseInt(list.get(1)));//难度
q.setType(Integer.parseInt(list.get(2)));//题目类型代码
q.setTypeName(list.get(3));//题目类型
q.setContent(list.get(4));//题目内容

q.setCreateTime(new Date());//创建时间
String abs = list.get(4).substring(0, 6) + "...";
q.setAbs(abs);//题目缩略

//判断Excel中的列数，依此来判断是不是选择题
if(list.size() == Constants.EXCEL_QUES_COLUMN_NUM){
q.setAnswer(list.get(5));//标准答案

String qid = queDao.addQuestion(q);
//判断options列有没有数据，若有数据，则把相应的选项存起来
if(list.get(6) != null && !list.get(6).equals("")){
saveOptions(list.get(6), qid);
}
}
else{
q.setAnswer("");
queDao.addQuestion(q);
}

}
//14789  14513   13824  13368 ms耗时2：20650 ms 21105 ms  开了hibernate.jdbc.batch_size 12119 ms 12329 ms12801 ms 
}catch(Exception e){
throw new ExcelAnalyzeException(e);
}


}

private boolean saveOptions(String optStr, String qid)
{
List<Option> optList = new ArrayList<>();
String[] opts = optStr.split("$$");
for(String opt:opts){
String[] o = opt.split("&&");
String sym = o[0];
String name = o[1];
Option option = new Option();
option.setName(name);
option.setSymbol(sym);
option.setQuestionId(qid);
optList.add(option);
}
return optDao.addOptList(optList);
}
Excel解析类：AnalyzeExcel.java
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import com.geariot.platform.exam.exception.ExcelAnalyzeException;


public class AnalyzeExcel {
static Logger log = Logger.getLogger(AnalyzeExcel.class);


public static List<List<String>> analyzeExcel(int sheetAt, String filepath)
throws ExcelAnalyzeException {
List<List<String>> result = null;
NPOIFSFileSystem fs = null;
OPCPackage pkg = null;
try {
// Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = null;


if (filepath.endsWith(".xls")) {
fs = new NPOIFSFileSystem(new File(filepath));
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(fs.getRoot(),true);
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(sheetAt);
/* HSSFSheet sheet = workbook.getSheet("people"); */
rowIterator = sheet.iterator();
} else if (filepath.endsWith(".xlsx")) {
pkg = OPCPackage.open(new File(filepath));
XSSFWorkbook workbookx = new XSSFWorkbook(pkg);
XSSFSheet sheetAt2 = workbookx.getSheetAt(sheetAt);
rowIterator = sheetAt2.iterator();
}


/* HSSFRow row1 = sheet.getRow(2); */
result = new ArrayList<List<String>>();


while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();


List<String> rowData = new ArrayList<String>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();


// String data = formatter.formatCellValue(cell); //Returns
// the formatted value of a cell as a String regardless of
// the cell type.


if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
rowData.add(cell.getStringCellValue());
} else {
double n = cell.getNumericCellValue();
Long lon = (long) n;
rowData.add(lon.toString());
}


}
result.add(rowData);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}finally{
try {
if(fs!=null){
fs.close();
}

if(pkg!=null){
pkg.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return result;
}

}