从excel导入数据
直接上代码,前端js代码自己写(就是选择excel文件上传)
@RequestMapping(value = "/uploadInterview")
@ResponseBody
public JSONObject uploadInterview(MultipartFile file, String custtype, HttpServletRequest request) throws Exception {
MoocMUserPojo moocMUserPojoNow = (MoocMUserPojo) request.getSession().getAttribute("user");
logger.debug(file.getOriginalFilename() + " " + custtype);
JSONObject object = new JSONObject();
object.put("status", -1);
try {
//判断文件是否为空
if (file == null) {
object.put("status", -1);
return object;
}
String name = file.getOriginalFilename();
long size = file.getSize();
if (name == null || ExcelUtil.EMPTY.equals(name) && size == 0) {
object.put("status", -2);//文件内容异常
return object;
}
//读取Excel数据到List中
List<ArrayList<String>> list = new ExcelRead().readExcel1(file);
List<TblMoocInterview> rightStus = new ArrayList<TblMoocInterview>();
List<StuCheckLogPojo> stuCheckLogBeans = new ArrayList<StuCheckLogPojo>();
//list中存的就是excel中的数据,可以根据excel中每一列的值转换成你所需要的值(从0开始),如:
TblMoocInterview tblMoocInterview = null;
List<TblMoocInterview> listMoocUser = new ArrayList<TblMoocInterview>();
for (ArrayList<String> arr : list) {
tblMoocInterview = new TblMoocInterview();
tblMoocInterview.setPhone(arr.get(0)); //每一行的第一个单元格--username
tblMoocInterview.setInterview_method(arr.get(2)); //访谈方式
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
if(("").equals(arr.get(3))){
Date dateTime = new Date();
logger.debug(arr.get(3) + "---------------------------"+dateTime);
String dt =df.format(dateTime);
logger.debug(dt + "---------------------------"+dateTime);
tblMoocInterview.setCreate_time(dt+"");
}else{
logger.debug(arr.get(3) + "--------------6666666666666666666-------------");
tblMoocInterview.setCreate_time(arr.get(3)); //访谈时间
}
tblMoocInterview.setContent(arr.get(4)); //访谈内容
tblMoocInterview.setInterviewer_id(Integer.valueOf(moocMUserPojoNow.getUserid())); //推荐人
listMoocUser.add(tblMoocInterview);
}
int total = 0;
for (int i = 0; i < listMoocUser.size(); i++) {
//每一个客户对象
TblMoocInterview userPojo = listMoocUser.get(i);
boolean flag = true;
String str = "";
/***********手机**************/
if (null == userPojo.getPhone()) {
str += "联系电话不能为空,";
flag = false;
} else if (!Pattern.matches("^(1[3|4|5|7|8][0-9])\\d{8}$", userPojo.getPhone())) {
str += "联系电话不正确(11位手机号),";
flag = false;
}
List<MoocUserPojo> li =moocUserService.selectByPhone(userPojo.getPhone());
if(li.size()==0){
str +="用户不存在";
flag = false;
}
if(!DateUtils.isDate(userPojo.getCreate_time())){
str +="时间格式不对";
flag = false;
}else if(("").equals(userPojo.getInterview_method())){
str +="访谈方式未填";
flag = false;
}else if(("").equals(userPojo.getContent())){
str +="访谈内容未填";
flag = false;
}
if (flag) {
rightStus.add(userPojo);
total += moocUserService.addInterview(Integer.valueOf(li.get(0).getUserid()),userPojo.getInterviewer_id() ,userPojo.getContent(),userPojo.getInterview_method(),userPojo.getCreate_time());
} else {
StuCheckLogPojo checkLogPojo = new StuCheckLogPojo();
//设置账号、提示信息
checkLogPojo.setAccount(userPojo.getPhone());
checkLogPojo.setMsg(str);
stuCheckLogBeans.add(checkLogPojo);
}
}
StuCheckRequestPojo checkRequestPojo = new StuCheckRequestPojo();
checkRequestPojo.setRcount(rightStus.size() + "");
checkRequestPojo.setEcount(stuCheckLogBeans.size() + "");
checkRequestPojo.setErrorBean(stuCheckLogBeans);
object.put("status", checkRequestPojo);
} catch (Exception e) {
e.printStackTrace();
}
return object;
}
读取excel文件数据方法
根据需要选择一种方法即可,也可自行更改方法
package com.cy.common.utils;
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 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 org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 读取Excel
*/
public class ExcelRead {
public int totalRows; //sheet中总行数
public static int totalCells; //每一行总单元格数
/**
* read the Excel .xlsx,.xls
* @param file jsp中的上传文件
* @return
* @throws IOException
*/
public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {
if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
return null;
}else{
String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
if(!ExcelUtil.EMPTY.equals(postfix)){
if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
return readXls(file);
}else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
return readXlsx(file);
}else{
return null;
}
}
}
return null;
}
public List<ArrayList<String>> readExcel1(MultipartFile file) throws IOException {
if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
return null;
}else{
String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
if(!ExcelUtil.EMPTY.equals(postfix)){
if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
return readXls1(file);
}else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
return readXlsx1(file);
}else{
return null;
}
}
}
return null;
}
/**
* read the Excel 2010 .xlsx
* @param file
* @return
* @throws IOException
*/
@SuppressWarnings("deprecation")
public List<ArrayList<String>> readXlsx(MultipartFile file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
XSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = file.getInputStream();
// 创建文档
wb = new XSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
if(xssfSheet == null){
continue;
}
totalRows = xssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 1;rowNum <= totalRows;rowNum++){
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(xssfRow!=null){
String str = "";
rowList = new ArrayList<String>();
totalCells = xssfRow.getLastCellNum();
//读取列,从第一列开始
// for(int c=0;c<=totalCells+1;c++){
for(int c=0;c<=6;c++){
XSSFCell cell = xssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelUtil.EMPTY);
continue;
}else {
str+=xssfRow.getCell(c);
}
rowList.add(ExcelUtil.getXValue(cell).trim());
}
if(str.length()<=0){
continue;
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* read the Excel 2010 .xlsx
* @param file
* @return
* @throws IOException
*/
@SuppressWarnings("deprecation")
public List<ArrayList<String>> readXlsx1(MultipartFile file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
XSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = file.getInputStream();
// 创建文档
wb = new XSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
if(xssfSheet == null){
continue;
}
totalRows = xssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 2;rowNum <= totalRows-1;rowNum++){
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(xssfRow!=null){
String str = "";
rowList = new ArrayList<String>();
totalCells = xssfRow.getLastCellNum();
//读取列,从第一列开始
for(int c=0;c<=4;c++){
XSSFCell cell = xssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelUtil.EMPTY);
continue;
}else {
str+=xssfRow.getCell(c);
}
rowList.add(ExcelUtil.getXValue(cell).trim());
}
if(str.trim().length()<=0){
continue;
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* read the Excel 2010 .xlsx
* @param file
* @return
*/
public List<ArrayList<String>> readXls(MultipartFile file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
HSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = file.getInputStream();
// 创建文档
wb = new HSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
if(hssfSheet == null){
continue;
}
totalRows = hssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 1;rowNum <= totalRows;rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow!=null){
String str = "";
rowList = new ArrayList<String>();
totalCells = hssfRow.getLastCellNum();
//读取列,从第一列开始
// for(short c=0;c<=totalCells+1;c++){
for(short c=0;c<=6;c++){
HSSFCell cell = hssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelUtil.EMPTY);
continue;
}else {
str+=hssfRow.getCell(c);
}
rowList.add(ExcelUtil.getHValue(cell).trim());
}
if(str.length()<=0){
continue;
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* read the Excel 2010 .xlsx
* @param file
* @return
*/
public List<ArrayList<String>> readXls1(MultipartFile file){
List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
// IO流读取文件
InputStream input = null;
HSSFWorkbook wb = null;
ArrayList<String> rowList = null;
try {
input = file.getInputStream();
// 创建文档
wb = new HSSFWorkbook(input);
//读取sheet(页)
for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
if(hssfSheet == null){
continue;
}
totalRows = hssfSheet.getLastRowNum();
//读取Row,从第二行开始
for(int rowNum = 2;rowNum <= totalRows-1;rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow!=null){
String str = "";
rowList = new ArrayList<String>();
totalCells = hssfRow.getLastCellNum();
//读取列,从第一列开始
for(short c=0;c<=4;c++){
HSSFCell cell = hssfRow.getCell(c);
if(cell==null){
rowList.add(ExcelUtil.EMPTY);
continue;
}else {
str+=hssfRow.getCell(c);
}
rowList.add(ExcelUtil.getHValue(cell).trim());
}
if(str.trim().length()<=0){
continue;
}
list.add(rowList);
}
}
}
return list;
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
}