package cn.ffcs.system.controller;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import jxl.*;
import jxl.read.biff.BiffException;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.dubbo.config.annotation.Reference;
import cn.ffcs.system.common.StringUtils;
import cn.ffcs.uam.service.StatisticsService;
/**
* 综合统计数据导入管理
* */
@Controller
@RequestMapping(value="/admin/statistics/excel")
public class StatisticsImportController {
@Reference(registry = "real-registry")
private StatisticsService statisticsService;
private String[] statisticsCols = {
"TITLE","UNIT","S1","S2","S3", "S4","STYPE",
"REMARK","SYEAR","SMONTH","OPDATE","TREE_ID"
};
SimpleDateFormat formate = new SimpleDateFormat("yyyy/MM/dd");
private int listLimits = 200;
@RequestMapping(value="/doImport", method=RequestMethod.POST)
@SuppressWarnings({ "rawtypes", "unchecked"})
public String doImport(HttpSession session, HttpServletRequest request,
@RequestParam("excelFile") MultipartFile excelFile, ModelMap map) {
String returnPage = "/statistics/importResult.ftl";
Workbook rwb = null;
WorkbookSettings wbs = new WorkbookSettings();
//String rootFolder = Constants.RESOURSE_SAVE_ROOT_PATH+"statistics/import-failure";
String path = request.getSession().getServletContext().getRealPath("upload");
String fileName = excelFile.getOriginalFilename();
wbs.setEncoding("UTF-8");
//wbs.setWriteAccess(null);
//wbs.setUseTemporaryFileDuringWrite(true);
File targetFile = new File(path, fileName);
if(!targetFile.exists()){
targetFile.mkdirs();
}
try {
excelFile.transferTo(targetFile);
} catch (Exception e) {
e.printStackTrace();
}
//wbs.setTemporaryFileDuringWriteDirectory(targetFile);//临时文件夹的位置
try{
rwb = Workbook.getWorkbook(targetFile, wbs);
Sheet sheet = rwb.getSheet(0);
if(sheet.getRows()<2){
map.put("tipErrMessage", "没有需要导入的数据");
return returnPage;
}
List list = null;
int rows = sheet.getRows();
int index = 0;
for(int i = 0; i < rows; i++) {
String checkStatus = "";
Cell[] cells = sheet.getRow(i);
if(!isEmptyRow(cells)) {//检查非空行的数据
checkStatus = checkRow(cells); //检查数据有效性
}
if(new StringUtils().isEmpty(checkStatus)) continue;
else {
map.addAttribute("tipErrMessage", "导入失败,第"+(i+1)+"行,"+checkStatus);
return returnPage;
}
}
//int records = this.getRightRows(sheet) - 1;//正确记录数 = 总记录数 - 空行 - 表头
for(int i = 1; i < sheet.getRows(); i++){
Cell[] cells = sheet.getRow(i);
if(index == 0) list = new ArrayList();
if(!isEmptyRow(cells)) {//跳过空行
Map rowMap = new HashMap<String,String>();
for(int j = 0; j < statisticsCols.length; j++) {
String colName = statisticsCols[j];
String colValue = "";
try {
Cell cell = sheet.getCell(j,i);
if(cell.getType() == CellType.DATE){
DateCell dateCell = (DateCell)cell;
colValue = formate.format(dateCell.getDate());
} else if(cell.getType() == CellType.EMPTY) {
colValue = "";
} else {
colValue = cell.getContents().trim();
colValue = colValue.replaceAll("\"","");
}
if("OPDATE".equals(colName)) { //检查日期
colValue = colValue.toString().replaceAll("-","/");
colValue = formate.format(formate.parse(colValue.toString()));
}
rowMap.put(colName, colValue);
}catch(Exception e){
}
}
list.add(rowMap);
index++;
}
}
rwb.close();
if(list.size() > 0){
int temp = statisticsService.insertByExcel(list);
map.addAttribute("tipErrMessage", temp !=0 ? "导入成功,共导入"+temp+"条数据":"导入失败");
}
}catch(IOException e){
return returnPage;
}catch(BiffException e) {
map.addAttribute("tipErrMessage", "excel版本错误!请使用2003版excel");
return returnPage;
}
return return
springMVC+mybatis 把excel文件导入oracle数据库
最新推荐文章于 2020-07-14 21:55:54 发布
本文介绍了如何结合SpringMVC和Mybatis框架,将Excel文件中的数据有效导入到Oracle数据库中,实现数据的批量处理和存储。
摘要由CSDN通过智能技术生成