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 returnPage;
}
/**
* 计算出去掉空行后的行数
* @param sheet
* @return
*/
private int getRightRows(Sheet sheet){
int cols = sheet.getColumns();
int rows = sheet.getRows();
int nullcellNum;
int afterRows = rows;
for(int i = 0; i < rows; i++){
nullcellNum = 0;
for(int j = 0; j < cols; j++){
String val = sheet.getCell(j, i).getContents();
if(StringUtils.isEmpty(val)){
nullcellNum ++;
}
}
if(nullcellNum >= cols){
afterRows --;
}
}
return afterRows;
}
/**
* 检查是否是空行
* @param cells
* @return
*/
public boolean isEmptyRow(Cell[] cells){
if(cells == null || cells.length == 0) return true;
for(int j = 0; j < cells.length; j++) {
Cell cell = cells[j];
if((cell.getType() != CellType.EMPTY) && !"".equals(cell.getContents().trim())) {
return false;
}
}
return true;
}
/**
* 检查excel行是否有效
* @param cells
* @param impType
* @return
*/
@SuppressWarnings("unused")
private String checkRow(Cell[] cells){
StringUtils stringUtils = new StringUtils();
if(cells == null || cells.length == 0) return "空行";
for(int i = 0; i < cells.length; i++) {
String colName = getColNameByIndex(statisticsCols,i);
String celValue = cells[i].getContents();
if("TITLE".equals(colName) && stringUtils.isEmpty(celValue)){
return "指标不能为空";
}
if("STYPE".equals(colName) && stringUtils.isEmpty(celValue)){
return "类型不能为空";
}
if("SYEAR".equals(colName) && stringUtils.isEmpty(celValue)){
return "年份不能为空";
}
if("SMONTH".equals(colName) && stringUtils.isEmpty(celValue)){
return "月份不能为空";
}
if("OPDATE".equals(colName) && stringUtils.isEmpty(celValue)){
return "导入时间不能为空";
}
if("TREE_ID".equals(colName) && stringUtils.isEmpty(celValue)){
return "树节点不能为空";
}
}
return "";
}
@SuppressWarnings("unused")
private String getColNameByIndex(String[] colName,int index){
if((0<= index) && (index <colName.length)) {
return colName[index];
}
return "";
}
}
</pre><pre name="code" class="java">
Excel模板：
| 指标 | 单位 | 现价 | 不变价 | 比上年同期增长% | S4 | 类型 | 备注 | 年份 | 月份 | 导入时间 | 树节点 |
| 地区生产总值 | 万元 | 30 | 30 | 30 | s1 | 2014 | 11 | 2014/8/21 | 15 | ||
| 第一产业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 2 | ||
| 农林牧渔业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 3 | ||
| 第二产业 | 万元 | 6 | 6 | 6 | s1 | 2014 | 11 | 2014/8/21 | 4 | ||
| 工业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 5 | ||
| 建筑业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 6 | ||
| 第三产业 | 万元 | 21 | 21 | 21 | s1 | 2014 | 11 | 2014/8/21 | 7 | ||
| 交通运输、仓储和邮政业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 8 | ||
| 批发和零售业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 9 | ||
| 住宿和餐饮业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 10 | ||
| 金融业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 11 | ||
| 房地产业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 12 | ||
| 营利性服务业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 11 | 2014/8/21 | 13 | ||
| 非营利性服务业 | 万元 | 3 | 3 | 3 | s1 | 2014 | 12 | 2014/8/21 | 14 |