基于Spring +Apache POI 导入 ajaxSubmit提交方式导入excel

Excel--导入

ajaxSubmit提交方式导入excel  

html代码

<form class="form-inline" id="ajax-form" method="post">
<input type="file" id="file" name="file"/>
<a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-save'" id="importExcel">导入EXCEL</a>
</form>

ajax本身是不能传输文件的，要想异步提交需要用到ajaxSubmit提交 需要引入jquery-form.js

js代码

$("#ajax-form").ajaxSubmit({
url:_basePath+'operate_excel/import.do',
type:"post",
dataType:"json",
success: function(data){},error: function() {}});

controller代码

@RequestMapping(value="import",method=RequestMethod.POST)
public voidimportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
Result result = null;
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("file");
InputStream input = file.getInputStream();
List<ProjectScore> proScoreList = ImportExcel.importProjectScore(input, multipartRequest);

}}

/**
*
* @Method:importProjectScore
* @Description：TODO()
* @param file
* @param request
* @return
* List<ProjectScore>
*/
public static List<ProjectScore> importProjectScore(
InputStream input,HttpServletRequest request){
try {
// 创建需要批量插入数据集合
List<ProjectScore> list = new ArrayList<ProjectScore>();
// 创建对Excel工作簿文件的引用
Workbook wookbook = new HSSFWorkbook(input);
// 在Excel文档中，第一张工作表的缺省索引是0
Sheet sheet = wookbook.getSheetAt(0);
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 遍历行 从第二行开始遍历
for (int i = 2; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
ProjectScore object = new ProjectScore();
// 设置时间
Cell dateCell = row.getCell(0);
if (dateCell != null && !dateCell.equals("")) {
dateCell.setCellType(Cell.CELL_TYPE_STRING);
String date = dateCell.getStringCellValue().trim();
if (date != null && !StringUtils.isEmpty(date)) {
object.setDate(date);
}
}
// 设置成本归属
Cell departCell = row.getCell(1);
if (departCell != null && !departCell.equals("")) {
departCell.setCellType(Cell.CELL_TYPE_STRING);
String depart = departCell.getStringCellValue().trim();
if (depart != null && !StringUtils.isEmpty(depart)) {
object.setCatId((int)Double.parseDouble(depart));
}
}
// 设置姓名
Cell nameCell = row.getCell(2);
if (nameCell != null && !nameCell.equals("")) {
nameCell.setCellType(Cell.CELL_TYPE_STRING);
String name = nameCell.getStringCellValue().trim();
if (name != null && !StringUtils.isEmpty(name)) {
object.setUserName(name);
}
}
// 设置项目名称
Cell proNameCell = row.getCell(3);
if (proNameCell != null && !proNameCell.equals("")) {
proNameCell.setCellType(Cell.CELL_TYPE_STRING);
String proName = proNameCell.getStringCellValue().trim();
if (proName != null && !StringUtils.isEmpty(proName)) {
object.setProjectName(proName);
}
}
// 设置绩效分数
Cell scoreCell = row.getCell(4);
if (scoreCell != null && !scoreCell.equals("")) {
scoreCell.setCellType(Cell.CELL_TYPE_STRING);
String score = scoreCell.getStringCellValue().trim();
if (score != null && !StringUtils.isEmpty(score)) {
object.setScore(Double.parseDouble(score));
}
}
list.add(object);
}
}
return list;
} catch (IOException e) {
logger.error("创建导入excel对象报错！", e);
}
return null;
}
/**
* 根据字节数组获取File
* @param b 字节数组
* @param outputFile 输出的路径(保存路径)
* @return
*/
public static File getFileFromBytes(byte[] b, String outputFile) {
BufferedOutputStream stream = null;
File file = null;
try {
file = new File(outputFile);
FileOutputStream fstream = new FileOutputStream(file);
stream = new BufferedOutputStream(fstream);
stream.write(b);
} catch (Exception e) {
logger.error("文件保存出错",e);
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e1) {
logger.error("文件流关闭出错",e1);
}
}
}
return file;
}