导入excel解析数据

实际上就是附件上传 上传之后用解析excel

示例：

1.用uploadfy上传 需要jquery.uploadify.v2.1.4.min.js

<input type="file" id="attach" value=""/>
<div id="up_file_queue" style="display:none">
</div>

js:

$("#attach").uploadify({
'uploader': '/resource/js/plugs/uploadify/uploadify.swf',//进度条，Uploadify里面含有 
'script': '/meter/importMeterReading',//一般处理程序 
'cancelImg': '/resource/js/plugs/uploadify/cancel.png',//取消图片路径 
'buttonImg': '/resource/js/plugs/uploadify/uploadButton.jpg',//图片路径 
'queueID': 'up_file_queue',
'auto': true,
'multi': true,
fileDataName : "attach",
height : 27,
width : 63,
method:'post',
removeCompleted : true,
fileExt:"*.xls;*.xlsx;",
fileDesc : 'xls;xlsx;',
sizeLimit:'10485760', 
onComplete:function(event, ID, fileObj, response, data){  
jQuery("#" + jQuery(event.target).attr('id') + ID).find('a').attr('href','javascript:void(0)').click(function(){
var ao =  jQuery.parseJSON(response);
deleteAffix(ao.id,ao.filename,$(this));
});
jQuery("#" + jQuery(event.target).attr('id') + ID).addClass('completed');
//判断返回值
if(response=="\"fail\""){
$.messageBox({message:"导入失败",level: "error"});
return false;
}else if(response=="\"3\""){
$.messageBox({message:"只支持xls和xlsx格式",level: "info"});
$('#attach').uploadifyClearQueue();
return false;
}else if (response=="\"1\""){
$.messageBox({message:"请选择系统提供的模板",level: "info"});
$('#attach').uploadifyClearQueue();
return false;
}else{
$("#meterDialog").createDialog({
url:'/meter/toSureRecording',
width:870,
title:"请核对信息"
});
}
},
onError:function(event,ID,fileObj,errorObj){
debugger;
$.messageBox({message:"上传有误",level: "error"});  
$('#attach').uploadifyClearQueue();
}
});

java：

public Object uploadContract(MultipartFile attach,HttpServletRequest request)
throws Exception { 
try {
String uploadFileFileName=attach.getOriginalFilename();
String ext = uploadFileFileName.substring(uploadFileFileName
.lastIndexOf("."));
MeterRecordExcel excelReader=new MeterRecordExcel();
Object result=null;
if(ext.equals(".xls")){
result=excelReader.readExcelXLS(attach.getInputStream());
}else if(ext.equals(".xlsx")){
result=excelReader.readExcelXLSX(attach.getInputStream());
}else{
return 3;
}
if(null!=result && !result.toString().equals("1") && !result.toString().equals("3")){
//解析result 判断获取的数据是否合法
List<Map<String,Object>> importList=meterReadingRecordService.fenxiResultExcel((List<MeterRecord>)result);
request.getSession().setAttribute("importList", importList);
result="2";
}
return result;
} catch (Exception e) {
e.printStackTrace();
}
return "fail";
}

MeterRecordExcel ：

package com.harmony.meter.common;


import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import com.harmony.meter.domain.MeterRecord;


/**
 * 操作Excel表格的功能类
 * 
 * @author：
 * @version
 */


public class MeterRecordExcel {

public Object readExcelXLS(InputStream inputStream) {
try {
POIFSFileSystem poiFileSystem = new POIFSFileSystem(inputStream);
// 得到文档对象
HSSFWorkbook workbook= new HSSFWorkbook(poiFileSystem);
// 得到第一个表单
HSSFSheet aSheet= workbook.getSheetAt(0);
if(null==aSheet || null==aSheet.getRow(0) || aSheet.getRow(0).getPhysicalNumberOfCells()<8){
return "1";//表单格式不对
}
int lastRow=1;
List<MeterRecord> result = new ArrayList<MeterRecord>();
for (int i = 1; i <= lastRow; i++) {
MeterRecord MeterRecord = new MeterRecord();
for (int j = 0; j <= 13; j++) {
HSSFRow row = aSheet.getRow(i); // 得到 第 n 行
if(null==row){
break;
}
HSSFCell cell = row.getCell(j); // 得到每行 第 n列
String param = getCellValue(cell); // 解析当前列的值
setMeterRecordDetail(j,MeterRecord,param);
}
result.add(MeterRecord);
//如果下一行不为空才会遍历下一行
if(null !=aSheet.getRow(i+1)){
lastRow++;
}
}
return result;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}


public Object readExcelXLSX(InputStream inputStream) {
try {
// 得到文档对象
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);  
// 得到第一个表单
XSSFSheet aSheet= workbook.getSheetAt(0);
if(null==aSheet || null==aSheet.getRow(0) || aSheet.getRow(0).getPhysicalNumberOfCells()<8){
return "1";//表单格式不对
}
int lastRow=1;
List<MeterRecord> result = new ArrayList<MeterRecord>();
for (int i = 1; i <= lastRow; i++) {
MeterRecord meterRecord = new MeterRecord();
for (int j = 0; j <= 13; j++) {
XSSFRow row = aSheet.getRow(i); // 得到 第 n 行
if(null==row){
break;
}
XSSFCell cell = row.getCell(j); // 得到每行 第 n列
String param = getCellValueX(cell); // 解析当前列的值
setMeterRecordDetail(j,meterRecord,param);
}
result.add(meterRecord);
//如果下一行不为空才会遍历下一行
if(null !=aSheet.getRow(i+1)){
lastRow++;
}
}
return result;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/*
* s设置返回的数据
*/
private void setMeterRecordDetail(int j, MeterRecord meterRecord, String param) {
if(j==0){
meterRecord.setBiaohao(param);
}else if(j==1){
meterRecord.setHuhao(param);
}else if(j==2){
meterRecord.setShangci(param);
}else if(j==3){
meterRecord.setZheci(param);
}else if(j==4){
meterRecord.setShuiliang(param);
}else if(j==5){
meterRecord.setJbstate(param);
}else if(j==6){
meterRecord.setCopyDate(param);
}else if(j==7){
meterRecord.setCopyUser(param);
}

}
private String getCellValueX(XSSFCell cell) {
String param = "";
if (null!=cell){
int type = cell.getCellType();
switch (type) {
case 0:
if(HSSFDateUtil.isCellDateFormatted(cell)){
//用于转化为日期格式
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
param = formater.format(d);
}else{
Double d = cell.getNumericCellValue();
param = d.toString();
}
break;
case 1:
param = cell.getStringCellValue();
break;
default:
param = "";
break;
}
}
return param;
}
private String getCellValue(HSSFCell cell) {
String param = "";
if (null!=cell) {
int type = cell.getCellType();
switch (type) {
case 0:
if(HSSFDateUtil.isCellDateFormatted(cell)){
//用于转化为日期格式
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
param = formater.format(d);
}else{
Double d = cell.getNumericCellValue();
param = d.toString();
}
break;
case 1:
param = cell.getStringCellValue();
break;
default:
param = "";
break;
}
}
return param;
}
}