java excel导入解析_Java Excel导入及数据解析详解

批量更新：

批量更新

状态

借读

性别

学号

缴费信息

班级

批量更新状态

data-default-file="" />

οnclick="batchUpdateState()">确定

οnclick="close_window()">取消

批量更新借读

data-default-file="" />

οnclick="batchUpdateReade()">确定

οnclick="close_window()">取消

批量更新性别

data-default-file="" />

οnclick="batchUpdateGender()">确定

οnclick="close_window()">取消

批量更新学号

data-default-file="" />

οnclick="batchUpdateStuNO()">确定

οnclick="close_window()">取消

批量更新缴费信息

data-default-file="" />

οnclick="batchUpdatePayment()">确定

οnclick="close_window()">取消

批量更新班级

data-default-file="" />

οnclick="batchUpdateClazz()">确定

οnclick="close_window()">取消

//点击弹出窗口2

$("#plgx_id").on("change", function() {

var n = $(this).val();

console.log(n);

switch (n) {

case 's1':

popup_name = $("#plgx_1");

open_window();

break;

case 's2':

popup_name = $("#plgx_2");

open_window();

break;

case 's3':

popup_name = $("#plgx_3");

open_window();

break;

case 's4':

popup_name = $("#plgx_4");

open_window();

break;

case 's5':

popup_name = $("#plgx_5");

open_window();

break;

case 's6':

popup_name = $("#plgx_6");

open_window();

break;

}

$(this).val("s0");

});

function batchUpdateState() {

var formData = new FormData();

var name = $("#input-file-1").val();

formData.append("file", $("#input-file-1")[0].files[0]);

formData.append("name", name);

$.ajax({

url : '../biz/sch/student/batchImportStudents',

type : 'POST',

cache : false,

data : formData,

// 告诉jQuery不要去处理发送的数据

processData : false,

// 告诉jQuery不要去设置Content-Type请求头

contentType : false,

beforeSend : function() {

console.log("正在进行，请稍候");

},

success : function(responseStr) {

alert("导入成功");

}

});

close_window();

$(".dropify-clear").trigger("click");

}

@ResponseBody

@PostMapping("/batchImportStudents")

public JSONObject batchImportStudents(@RequestParam("file") MultipartFile file,HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{

System.out.println("================================");

JSONObject jo = null;

JSONObject param = new JSONObject();

param.put("schId", 280010);

param.put("sheetName", "测试");

//获取学校

try {

String originalFilename = file.getOriginalFilename();//原文件名字

param.put("fileName", originalFilename);

InputStream is = file.getInputStream();//获取输入流

ReadExcel readExcel = new ReadExcelImpl();

//ExcelCheckCallback callback = new StuPLXHCheckCallback();

ExcelCheckCallback callback = null;

List list = readExcel.readExcel(is, callback, param );

if(list != null && !list.isEmpty())

jo = list.get(0);

System.out.println("jo: "+jo.toJSONString());

} catch (FileUploadException e) {

e.printStackTrace();

response.setStatus( 500);

return null;

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return jo;

}package com.moofen.core.util.excel;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

import org.apache.commons.fileupload.FileUploadException;

import com.alibaba.fastjson.JSONObject;

public interface ReadExcel {

@SuppressWarnings("rawtypes")

List readExcel(InputStream is, ExcelCheckCallback callback, JSONObject param ) throws Exception, IOException, FileUploadException;

}

package com.moofen.core.util.excel.impl;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.fileupload.FileUploadException;

import com.alibaba.fastjson.JSONObject;

import com.moofen.core.constant.ExcelConstant;

import com.moofen.core.util.excel.AbstractRead;

import com.moofen.core.util.excel.ExcelCheckCallback;

import com.moofen.core.util.excel.ReadExcel;

public class ReadExcelImpl implements ReadExcel {

@SuppressWarnings("rawtypes")

@Override

public List readExcel(InputStream is,

ExcelCheckCallback callback, JSONObject param)

throws Exception, IOException, FileUploadException {

//List fileItems = readFileItemFromHttp(request);

//JSONObject p = buildParam( readAllParam(fileItems), param);

AbstractRead reader = null;

List list = new ArrayList();

String fName = param.getString("fileName");

String fileName[] = fName.split("\\.");

String suffix = fileName[fileName.length - 1].toLowerCase();

if(suffix.endsWith(ExcelConstant.FILE_UPLOAD_LIMIT_XLS)){

reader = new ReadXls();

}else if(suffix.endsWith(ExcelConstant.FILE_UPLOAD_LIMIT_XLSX)){

reader = new ReadXlsx();

}

JSONObject jo = reader.readExcel(is, null, null);

list.add(jo);

return list;

}

}

package com.moofen.core.util.excel.impl;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import com.moofen.core.util.excel.AbstractRead;

import com.moofen.core.util.excel.ExcelCheckCallback;

public class ReadXls extends AbstractRead{

HSSFWorkbook hssfWorkbook = null;

@SuppressWarnings({ "rawtypes", "unchecked" })

public JSONObject readExcel(InputStream is, JSONObject param, ExcelCheckCallback callback) throws Exception, IOException{

hssfWorkbook = new HSSFWorkbook(is);

try{

JSONObject ret = new JSONObject();

Integer ss = 0, es = hssfWorkbook.getNumberOfSheets();

// Read the Sheet

for (int numSheet = ss; numSheet < es; numSheet++) {

HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);

if (hssfSheet == null) {

continue;

}

JSONArray sheetAry = new JSONArray();

// Read the Row

if(hssfSheet.getRow(0) == null){

//logger.info(hssfSheet.getSheetName()+" 无内容");

continue;

}

int coloumNum=hssfSheet.getRow(0).getPhysicalNumberOfCells();

for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

HSSFRow hssfRow = hssfSheet.getRow(rowNum);

if (hssfRow != null) {

List rowData = new ArrayList();

for( int c = 0; c < coloumNum; c++){

HSSFCell cellV = hssfRow.getCell( c );

if(cellV != null)

rowData.add( getValue(cellV) );

else

rowData.add("");

}

sheetAry.add( rowData);

}

}

ret.put(hssfSheet.getSheetName(), sheetAry);

}

//ret = callback.beforeHandleData(ret, param);

//if(callback.check(ret, param) == false){

//throw new Exception( callback.getCheckInfo() );

//}

//ret = callback.afterHandleData(ret, param);

return ret;

}catch(Exception e){

throw e;

}finally{

is.close();

}

}

}

package com.moofen.core.util.excel.impl;

import java.io.IOException;

import java.io.InputStream;

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.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import com.moofen.core.util.excel.AbstractRead;

import com.moofen.core.util.excel.ExcelCheckCallback;

public class ReadXlsx extends AbstractRead{

XSSFWorkbook xssfWorkbook = null;

@SuppressWarnings({ "unchecked", "rawtypes" })

public JSONObject readExcel(InputStream is, JSONObject param, ExcelCheckCallback callback)

throws Exception, IOException {

try{

xssfWorkbook = new XSSFWorkbook(is);

JSONObject ret = new JSONObject();

Integer ss = 0, es = xssfWorkbook.getNumberOfSheets();

// Read the Sheet

for (int numSheet = ss; numSheet < es; numSheet++) {

XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);

if (xssfSheet == null) {

return null;

}

JSONArray list = new JSONArray();

// Read the Row

if(xssfSheet.getRow(0) == null){

//logger.info(xssfSheet.getSheetName()+" 无内容");

continue;

}

int coloumNum=xssfSheet.getRow(0).getPhysicalNumberOfCells();

for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

XSSFRow xssfRow = xssfSheet.getRow(rowNum);

if (xssfRow != null) {

JSONArray line = new JSONArray();

for( int c = 0; c < coloumNum; c++){

XSSFCell cellV = xssfRow.getCell( c );

if(cellV != null)

line.add(getValue(cellV));

else

line.add("");

}

list.add( line);

}

}

ret.put(xssfSheet.getSheetName(), list);

}

//ret = callback.beforeHandleData(ret, param);

//if(callback.check(ret, param) == false){

//throw new Exception( callback.getCheckInfo() );

//}

//ret = callback.afterHandleData(ret, param);

return ret;

}catch(Exception e){

throw e;

}finally{

is.close();

}

}

}

package com.moofen.core.util.excel;

import java.io.IOException;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.FormulaEvaluator;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.NumberToTextConverter;

import com.alibaba.fastjson.JSONObject;

public abstract class AbstractRead {

@SuppressWarnings("rawtypes")

public abstract JSONObject readExcel(InputStream is, JSONObject param, ExcelCheckCallback callback) throws Exception, IOException;

protected String getValue(Cell c) {

String r=null;

if(c==null){

r="";

}

switch(c.getCellType()){

case Cell.CELL_TYPE_BLANK://为 空值3

r="";

break;

case Cell.CELL_TYPE_BOOLEAN://boolean型4

r=c.getBooleanCellValue()+"";

break;

case Cell.CELL_TYPE_ERROR://错误 5

r="";

break;

case Cell.CELL_TYPE_FORMULA://公式型 2

Workbook wb = c.getSheet().getWorkbook();//取得workbook

CreationHelper helper = wb.getCreationHelper();//取得wb的帮助

FormulaEvaluator ev = helper.createFormulaEvaluator();//取得helper的公式计算方法

r=getValue(ev.evaluateInCell(c));//调用自身方法，括号里取得cellValue

break;

case Cell.CELL_TYPE_NUMERIC://数值型 0

if(DateUtil.isCellDateFormatted(c)){//如果是excel日期格式

SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//规定日期格式

Date d = c.getDateCellValue();//取得日期

r = s.format(d);//转换日期格式

}else{

// r = getNumber( String.valueOf(c.getNumericCellValue()) );//数值的话，转换为String

r = NumberToTextConverter.toText(c.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_STRING://字符串型 1

r=c.getRichStringCellValue().toString();//推荐使用getRichStringCellValue()；

//getStringCellValue()为老方法

break;

default:

r="";

}

return r.trim();

}

/*private String getNumber( String num ){

String[] tmp = num.split("\\.");

if(tmp.length == 2){

Integer i = Integer.parseInt( tmp[1] );

if(i > 0)

return num;

else

return tmp[0];

}else{

return num;

}

}*/

}