之前因为工作需要实现了后端struts2框架，前端jsp实现的excel表格上传以及内容解析，现在整理一下。
前端jsp html部分代码如下：
<s:form action="planAction_uploadAndSaveExcel.action" method="post" enctype="multipart/form-data">
<s:file name="uploadFile" label="导入汇总表" id="filePath"></s:file>
<input type="hidden" id="recordname" name="recordName" value="${sessionScope.globle_user.loginname}"/>
<input type="submit" value="导入">
</s:form>form表单的action映射到struts后端 planAction类的uploadAndSaveExcel方法
//上传Excel文件并保存数据到数据库
public String uploadAndSaveExcel() throws Exception{
String directory = "/upload/role";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
//生成上传的文件对象
File target = new File(targetDirectory,uploadFileFileName);
//如果文件已经存在，则删除原有文件
if(target.exists()){
target.delete();
}
//复制file对象，实现上传
try {
FileUtils.copyFile(uploadFile, target);
} catch (IOException e) {
e.printStackTrace();
}
//解析上传的excel表格
String importresult = loadPlanInfo(uploadFileFileName,target);
String result=null;
if (importresult.equals("import success")){
result = "saveAndStartProcess";
}else if(importresult.equals("import error")){
result = "concentratedleave";
}
return result;
}loadPlanInfo方法对excel表格做解析处理，该方法只能对没有合并单元格的excel表格进行解析
/**
* 把Excele表读出的数据，组装成一个List,统一导入数据库
* @param uploadFileFileName
*/
public String loadPlanInfo(String uploadFileFileName,File target){
String randuuid =uuidUtil.get32UUID();
List<HolidayApply> holidayApplys = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf_time2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date new_enddate = new Date();
try{
InputStream fi = new FileInputStream(target);
Workbook wb = WorkbookFactory.create(fi);
Sheet sheet = wb.getSheetAt(1);
int rowNum = sheet.getLastRowNum();
for(int i=3;i<rowNum;i++){
HolidayApply applyInfo = new HolidayApply();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for(int j=1;j<cellNum;j++){
Cell cell = row.getCell(j);
String cellValue = null;
switch(cell.getCellType()){ //判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0 :
if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString()) || "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString()) || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString()) ) {
new_enddate = cell.getDateCellValue();
}else {
cellValue = String.valueOf((int)cell.getNumericCellValue());
}
break;
case 1 : cellValue = cell.getStringCellValue(); break;
case 2 : new_enddate = cell.getDateCellValue(); break;
case 3 : cellValue = ""; break;
case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
}
switch(j){//通过列数来判断对应插如的字段
case 1 : applyInfo.setName(cellValue);break;
case 2 : applyInfo.setServicy(cellValue);break;
case 3 :
if (cellValue.equals("休假")){
cellValue="10";
}
applyInfo.setEventclass(Integer.parseInt(cellValue));
break;
case 4 : applyInfo.setDestination(cellValue);break;
case 5 : applyInfo.setEvent(cellValue);break;
case 6 :
applyInfo.setStartdate(new_enddate);
applyInfo.setStrstartdate(sdf.format(new_enddate));
break;
//将结束时间设置到当天夜里23点，使得人员在位情况白天处于不在位
case 7 :
applyInfo.setEnddate(sdf_time2.parse(sdf.format(new_enddate)+" 23:00:00"));
applyInfo.setStrenddate(sdf.format(new_enddate));
break;
}
}
try {
applyInfo.setApplytime(new Date());
applyInfo.setRecordname(recordName);
applyInfo.setCountdays((int)countdays);
applyInfo.setProcuuid(randuuid);
holidayApplys.add(applyInfo);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
} catch(IOException e){
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
return "import success";
}而如果需要对存在合并单元格的excel表格，则对应的loadPlanInfo方法为：
/**
* 把Excele表读出的数据，组装成一个List,统一导入数据库
* @param uploadFileFileName
*/
public String loadPlanInfo(String uploadFileFileName,File target){
String randuuid =uuidUtil.get32UUID();
List<HolidayPlan> planInfolist = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf_time2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try{
InputStream fi = new FileInputStream(target);
Workbook wb = WorkbookFactory.create(fi);
Sheet sheet = wb.getSheetAt(1);
int rowNum = sheet.getLastRowNum();
for(int i=3;i<rowNum;i++){
int isempty = 0;//该标志作为表格中的某行是否为空行
HolidayPlan planInfo = new HolidayPlan();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for(int j=1;j<cellNum;j++){
Cell cell = row.getCell(j);
String cellValue = null;
boolean isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
//判断是否具有合并单元格
if(isMerge) {
cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
}else {
cellValue = getCellValue(cell);
}
switch (j){
case 1 : planInfo.setName(cellValue);break;
case 2 : planInfo.setServicy(cellValue);break;
case 3 :
if(cellValue.equals("休假")){
cellValue = "1";
planInfo.setEventclass(Integer.parseInt(cellValue));
}else if(cellValue.equals("探亲")){
cellValue = "5";
planInfo.setEventclass(Integer.parseInt(cellValue));
}else if(cellValue.equals("")){
//当请假类型为空时，说明该行记录为空，不再继续解析后续的列
isempty = 1;
j = cellNum;
}
break;
case 4 : planInfo.setTotaldays(Integer.parseInt(cellValue)); break;
case 5 :
Date new_startdate = new Date(cellValue);
planInfo.setStartdate(new_startdate);
planInfo.setStrstartdate(sdf.format(new_startdate));
break;
case 6 :
Date new_enddate = new Date(cellValue);
planInfo.setEnddate(new_enddate);
planInfo.setStrenddate(sdf.format(new_enddate)); break;
case 7 : planInfo.setActualdays(Integer.parseInt(cellValue)); break;
case 8 : planInfo.setDestination(cellValue); break;
case 9 :
if (cellValue != null && cellValue != ""){
planInfo.setDaysonroad(Integer.parseInt(cellValue));
} else{
planInfo.setDaysonroad(0);
}break;
}
}
//该行记录为空，增加一个空对象(只含name,userid等字段)，继续解析下一行
if (isempty == 1){
planInfolist.add(planInfo);
continue;
}
try {
planInfo.setStatus(HolidayPlan.HAS_APPLY);
planInfo.setApplytime(new Date());
planInfo.setRecordname(recordName);
int countdays = computeActualdays(planInfo);
planInfo.setCountdays(countdays);
planInfo.setProcuuid(randuuid);
planInfolist.add(planInfo);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
//为了前端展示方便，添加index
for (int i= 0 ; i< planInfolist.size();i++){
planInfolist.get(i).setIndex(i);
}
} catch(IOException e){
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return "import success";
}对excel的合并单元格做判断的方法如下：
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell) ;
}
}
}
return null ;
}
/**
* 判断合并了行
* @param sheet
* @param row
* @param column
* @return
*/
private boolean isMergedRow(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row == firstRow && row == lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 判断sheet页中是否含有合并单元格
* @param sheet
* @return
*/
private boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public String getCellValue(Cell cell){
if(cell == null) return "";
if(cell.getCellType() == Cell.CELL_TYPE_STRING){
return cell.getStringCellValue();
}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
return cell.getCellFormula() ;
}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
if ( "yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) ||
"m/d/yy".equals(cell.getCellStyle().getDataFormatString()) ||
"yy/m/d".equals(cell.getCellStyle().getDataFormatString()) ||
"mm/dd/yy".equals(cell.getCellStyle().getDataFormatString()) ||
"dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())||
"yyyy/m/d".equals(cell.getCellStyle().getDataFormatString()) ) {
return cell.getDateCellValue().toString();
}else {
return String.valueOf((int)cell.getNumericCellValue());
}
}
return "";
}以上就是前端ecxel表格上传以及后端解析的过程
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
