接口:
/**
*
* Description: 导入excel表
* @param map
* @param request
* @param session
* @return
* @author shiqianyu
* @throws Exception
* @Data 2018年3月12日 下午2:40:52
*/
@RequestMapping("/importExcelData")
public ModelAndView importExcelData(ModelMap map,HttpServletRequest request,HttpSession session,@RequestParam(value = "file", required = true)MultipartFile multfile) throws Exception{
//获取页面信息
Map<String, String> pageCrawlValue = StringUtil.getArrayValue(request.getParameterMap());
pageCrawlValue.put("CRAWLRESULTPATH", pageCrawlValue.get("CRAWLRESULTPATH")+File.separator+pageCrawlValue.get("PAGECRAWLNAME"));
PageCrawlTaskManage pageCrawlTaskManage = new PageCrawlTaskManage();
pageCrawlTaskManage.setRegx(pageCrawlValue.get("PageRegx"));
pageCrawlTaskManage.setPage_prdfix(pageCrawlValue.get("PAGE_PRDFIX"));
pageCrawlTaskManage.setCtCycle(pageCrawlValue.get("CTCYCLE"));
pageCrawlTaskManage.setCrawlResultPath(pageCrawlValue.get("CRAWLRESULTPATH"));
pageCrawlTaskManage.setProxyDeployId(pageCrawlValue.get("PROXYID"));
pageCrawlTaskManage.setCrawlIntervalTime(Integer.valueOf(pageCrawlValue.get("CRAWL_INTERVAL_TIME")));
pageCrawlTaskManage.setPageCrawlName(pageCrawlValue.get("PAGECRAWLNAME"));
pageCrawlTaskManage.setIsDrill(pageCrawlValue.get("isDrill"));
pageCrawlTaskManage.setCtCycleUnit(pageCrawlValue.get("CTCYCLEUNIT"));
pageCrawlTaskManage.setDataSourceId(pageCrawlValue.get("DATASOURCEID"));
pageCrawlTaskManage.setPage_start(pageCrawlValue.get("PAGE_START"));
pageCrawlTaskManage.setPage_end(pageCrawlValue.get("PAGE_END"));
pageCrawlTaskManage.setDescribe(pageCrawlValue.get("describe"));
pageCrawlTaskManage.setThreadCount(pageCrawlValue.get("CTTHREADCOUNT"));
pageCrawlTaskManage.setPage_suffix(pageCrawlValue.get("PAGE_SUFFIX")); int startRow = Integer.parseInt(pageCrawlValue.get("startRow"))-1;//导入excel 起始行号 -3
int endRow = Integer.parseInt(pageCrawlValue.get("endRow"))-1; //导入excel 结束行号 -3
int column = Integer.parseInt(pageCrawlValue.get("column"))-1; //导入 excel指定的列号 -1 //获取页码后缀
List<String> suffixs = new ArrayList<String>(); //检查文件
ExcelData.checkFile(multfile);
//获得Workbook工作薄对象
Workbook workbook = ExcelData.getWorkBook(multfile);
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = startRow;
//获得当前sheet的结束行
int lastRowNum = endRow;
//循环行
for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){ //firstRowNum+1;
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
if(cellNum==column){
Cell cell = row.getCell(cellNum);
cells[cellNum] = ExcelData.getCellValue(cell);
} }
list.add(cells);
}
}
} for(int i= 0;i<list.size();i++){//startRow
String[] arr=null;
arr = list.get(i);//[张飞, As255, c2, d2, f2]
if(arr!=null){
//遍历列
for(int j=0;j<arr.length;j++){
if(j==column){
suffixs.add(arr[j]);//As255
}
}
}
} map.put("pageCrawlTaskManage", pageCrawlTaskManage);
map.put("times",suffixs);
return new ModelAndView("crawl/CrawlTaskToExcel");
}
工具类:
package com.dimensoft.splider.util; import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List; import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile; /**
* 解析excel 上传数据
* @author shiqianyu
*
*/
public class ExcelData { private static final Logger log = Logger.getLogger(ExcelData.class); public static List<String[]> getExcelData(MultipartFile file) throws IOException{
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
}
return list;
} /**
* 检查文件
* @param file
* @throws IOException
*/
public static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
log.error("文件不存在！");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
log.error(fileName + "不是excel文件");
}
} public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007 及2007以上
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
log.error(e.getMessage());
}
return workbook;
} public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = stringDateProcess(cell);
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
} /**
* 时间格式处理
* @return
* @author Liu Xin Nan
* @data 2017年11月27日
*/
public static String stringDateProcess(Cell cell){
String result = new String();
if (true) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
} return result;
}
}