java实现Excel导入功能
最近研究了开源系统MyExam，对于java实现Excel导入分享下
前台代码：
<form id="importForm" method="post" enctype="multipart/form-data">
<div>
<h1><fmt:message key="questionimport"/></h1>
<table class="yTable margintop">
<tr>
<th><fmt:message key="selectfile"/>：</th>
<td><input id="files" name="files" type="file" /></td>
</tr>
</table>
<br />
</div>
<div class="Btn">
<input id="downloadBtn" type="button" value="<fmt:message key="downloadtemplate"/>" />
<input id="importBtn" type="button" value="<fmt:message key="import"/>" />
</div>
</form>
js文件：
define(function(require){
var $ = require('jquery');
var contextPath=$("#contextPath").val();
require('dialog')($);
require('uriTemplate')($);
var IFA = require('util');
require('dateTimePicker')($);
require("../../../styles/cim/jquery-ui-1.8.14.custom.css");
$("#importBtn").click(function(){
var url =contextPath+'/question/import.do';
$("#importForm").attr("action",url);
$("#importForm").submit();
});
$("#downloadBtn").click(function(){
url = contextPath+'/download/questionTemplate.xlsx';
location.href = url;
});
});
Java代码：
/**
* 导入试题
*
* @author Eric
*
*/
@SuppressWarnings("unchecked")
@RequestMapping(value = "/question/import.json", method = RequestMethod.POST)
public String create(final ExcelDTO excelDTO,
final BindingResult result, final ModelMap model,
HttpServletRequest request){
excelValidator.validate(excelDTO, result);
// 插入数据的list
List<importQuestionDTO> importQuestionList = new LinkedList<importQuestionDTO>();
if (result.hasErrors()) {
model.put("importFileTypeError1", "importFileTypeError");
} else {
try {
final Map<String, Object> parse = parseExcel(excelDTO);
final boolean dataTooManyErrorFlag = (Boolean) parse
.get("dataTooManyErrorFlag");
if (dataTooManyErrorFlag) {
model.put("importFileDataTooManyErroFlag1", true);
} else {
// 格式正确的list
importQuestionList = (List<importQuestionDTO>) parse.get("formatCorrectList");
}
} catch (Exception e) {
model.put("importFileReadErrorMsg1", "true");
return view(model, "importQuestionView", TopMenu.EXAM,
SideMenu.QUESTION_MANAGEMENT);
}
final Map<String, Object> resultMap = insertDB(importQuestionList, request);
model.put("lastId", resultMap.get("insertLastId"));
model.put("count", resultMap.get("successCount"));
model.put("importVoucherSuccessFlag1", "true");
}
return view(model, "importQuestionView", TopMenu.EXAM,
SideMenu.QUESTION_MANAGEMENT);
}
/**
* <p>
* Description: 插入数据
* </p>
*
* @author Eric
*/
private Map<String, Object> insertDB(
List<importQuestionDTO> questionList,
HttpServletRequest request) {
final Date date = new Date();
final Integer creatorAndModifier = (Integer) securityHelper.getID(request);
for (importQuestionDTO questionDTO : questionList) {
setDefaultValue(date, creatorAndModifier, questionDTO);
}
//return personProfileService.importClubCustomer(clubCustomerList);
return null;
}
/**
* <p>
* Description: 设置其他默认必须数据
* </p>
*
* @author Eric
*/
private void setDefaultValue(final Date date,
final Integer creatorAndModifier,
importQuestionDTO questionDTO) {
questionDTO.setCreationDate(new Date());
questionDTO.setCreator(creatorAndModifier);
questionDTO.setModificationDate(new Date());
questionDTO.setModifier(creatorAndModifier);
questionDTO.setOrigin("import");
}
/**
* <p>
* Description: 解析excel
* </p>
*
* @author Eric
*/
private Map<String, Object> parseExcel(final ExcelDTO excelDTO)
throws BiffException, IOException {
final Map<String, Object> map = new HashMap<String, Object>(3);
Workbook workbook = null;
try {CommonsMultipartFile file=(CommonsMultipartFile) excelDTO.getFiles().get(0);
String fileName = file.getFileItem().getName();
String extension = fileName
.substring(fileName.lastIndexOf(".") + 1);
InputStream is = file.getInputStream();
// if ("xlsx".equals(extension)) {
// XSSFWorkbook workbook2007 = new XSSFWorkbook(is);
// final XSSFSheet sheet = workbook2007.getSheetAt(0);
// final int totalRows = sheet.getLastRowNum();
// if (totalRows > 3000) {
// map.put("dataTooManyErrorFlag", true);
// } else {
// map.putAll(packagePersonProfile2007(sheet, totalRows));
// map.put("dataTooManyErrorFlag", false);
// }
// }
workbook = Workbook.getWorkbook(is);
final Sheet sheet = workbook.getSheet(0);
final int totalRows = sheet.getRows();
if (totalRows > 3000) {
map.put("dataTooManyErrorFlag", true);
} else {
map.putAll(packagePersonProfile(sheet, totalRows));
map.put("dataTooManyErrorFlag", false);
}
} catch (BiffException e) {
throw e;
} catch (IOException e) {
throw e;
} finally {
if (workbook != null) {
workbook.close();
}
}
return map;
}
/**
* <p>
* Description: 将excel2003封装成list
* </p>
*
* @author Eric
*/
private Map<String, List<importQuestionDTO>> packagePersonProfile(
final Sheet sheet, final Integer totalRows) {
final List<importQuestionDTO> formatCorrectList = new LinkedList<importQuestionDTO>();
final Map<String, List<importQuestionDTO>> packageResult = new HashMap<String, List<importQuestionDTO>>(
2);
for (int i = 1; i < totalRows; i++) {
importQuestionDTO questionDTO = new importQuestionDTO();
List<OptionDTO> optionDTO=new ArrayList<OptionDTO>();
final String content = sheet.getCell(0, i).getContents().trim();
final String analyse = sheet.getCell(1, i).getContents().trim();
final String answer = sheet.getCell(2, i).getContents().trim();
final String category = sheet.getCell(3, i).getContents().trim();
int num=num(sheet, i);
for (int k = num; k > 3; k--) {
OptionDTO optionDTO2=new OptionDTO();
optionDTO2.setContent(sheet.getCell(k, i).getContents().trim());
optionDTO.add(optionDTO2);
}
try {
questionDTO = constructclubCustomerDTO(content, analyse,
answer, category, (ArrayList<OptionDTO>) optionDTO);
formatCorrectList.add(questionDTO);
} catch (NumberFormatException e) {
//log.catching(e);
}
}
packageResult.put("formatCorrectList", formatCorrectList);
return packageResult;
}
/**
* <p>
* Description: 封装所需的DTO
* </p>
*
* @author Eric
*/
private importQuestionDTO constructclubCustomerDTO(String content,
String analyse, String answer, String category, ArrayList<OptionDTO> optionDTO) {
final importQuestionDTO importQuestionDTO = new importQuestionDTO();
importQuestionDTO.setContent(content);
importQuestionDTO.setAnalysis(analyse);
importQuestionDTO.setAnswer(answer);
importQuestionDTO.setCategory(category);
importQuestionDTO.setOptions(optionDTO);
return importQuestionDTO;
}
public int num(final Sheet sheet, final Integer i){
int a;
for (int j = 0; ; j++) {
if (StringUtils.isBlank(sheet.getCell(4+j, i).getContents().trim())) {
a=4+j;
break;
}
}
return a;
}
/**
* <p>
* Description: 将excel2007封装成list
* </p>
*
* @author Eric
*/
private Map<String, List<QuestionDTO>> packagePersonProfile2007(
final XSSFSheet sheet, final Integer totalRows) {
// 填充的list
final List<QuestionDTO> formatCorrectList = new LinkedList<QuestionDTO>();
final Map<String, List<QuestionDTO>> packageResult = new HashMap<String, List<QuestionDTO>>(
2);
for (int i = 1; i < totalRows; i++) {
QuestionDTO clubCustomerDTO = new QuestionDTO();
}
return packageResult;
}
/**
* 导入试题
*
* @author Eric
*
*/
@RequestMapping(value = "/importPaper.do", method = RequestMethod.GET)
public String importPaperIndex(final ModelMap model){
return view(model, "importPaperView", TopMenu.EXAM,
SideMenu.PAPER_MANAGEMENT);
}
Service代码：
public Map<String, Object> importQuestion(
final List<importQuestionDTO> questionList) {
// 插入新数据，并返回最后插入的personProfile的id
final Integer insertLastId = questionDao
.insertQuestionBatch(questionList);
final Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("insertLastId", insertLastId);
return resultMap;
}
DAO：
public Integer insertQuestionBatch(
final List<importQuestionDTO> questionDTOs) {
Integer lastId = (Integer) getSqlMapClientTemplate().execute(
new SqlMapClientCallback<Object>() {
public Object doInSqlMapClient(final SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
for (importQuestionDTO clubCustomerDTOs : questionDTOs) {
int id=(Integer) executor.insert(
"question.insert",
clubCustomerDTOs);
List<OptionDTO> optionDTOs=clubCustomerDTOs.getOptions();
for (OptionDTO optionDTO:optionDTOs ) {
optionDTO.setQuestionId(id);
executor.insert(
"question.insertOption",
optionDTO);
}
}
executor.executeBatch();
//final Integer id = (Integer) executor
//.queryForObject("person_profile.selectLastInsertID");
return 5;
}
});
return lastId;
}
// 根据id删除
public void deleteQuestion(int id) {
getSqlMapClientTemplate().delete("question.deleteById", id);
}
public int insertQuestion(QuestionDTO questionDTO) {
return (Integer) getSqlMapClientTemplate().insert("question.insert",
questionDTO);
}
浙公网安备 33010602011771号