目录
废话不多说直接上代码 , 具体注释代码里都有写到
一 : pom引入(这里也涵盖导出的pom)
<!-- 导入和导出excel时需要的jar包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
二 : 具体工具类代码
package com.xhkjedu.common;
import com.xhkjedu.model.schooluser.TUserBean;
import com.xhkjedu.service.schooluser.UserService;
import com.xhkjedu.utils.MD5;
import com.xhkjedu.utils.N_Utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.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.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* hxk Excel 导入工具类
*/
@Component
public class PoiUtils {
//学生导入
/**
* 2003- 版本的excel
*/
private final static String excel2003L = ".xls";
/**
* 2007+ 版本的excel
*/
private final static String excel2007U = ".xlsx";
/**
* 用户DAo
*/
@Autowired
UserService userService;
/*
* @Author hxk
* @Date 2020/03/30 10:52
* @Description //TODO Excel批量导入学生用户信息
* @Param [file, createid]
* @return java.util.Map<java.lang.String,java.lang.Object>
**/
public Map<String , Object> readExcelStudentUser(MultipartFile file , Integer createid) throws Exception {
MD5 md5 = new MD5();
Map<String , Object> resultMap = new HashMap<>();
try{
if(null == file || file.isEmpty()){
throw new Exception("文件不存在!");
}
InputStream in = file.getInputStream();
String fileName = file.getOriginalFilename();
Workbook work = this.getWorkbook(in, fileName);
if(null == work){
throw new Exception("Excel内容为空!");
}
Integer sheet_size = work.getNumberOfSheets();
if(sheet_size == 0){
throw new Exception("Excel内容为空!");
}
Sheet sheet = null;// 分表数据
Row row = null;// 行数据
sheet = work.getSheetAt(0);//只取第一个sheet
if(sheet == null){
throw new Exception("Excel第一个分表内容为空!");
}
// 准备集放置学生信息
List<TUserBean> students = new ArrayList<>();
// 准备放置数据库已存在学生信息
List<TUserBean> studentsExisted = new ArrayList<>();
int rows = sheet.getLastRowNum(); // 获取最后一个实际行的下标,比行数小1
int firstRowNum = sheet.getFirstRowNum(); // 获取第一个实际行的下标,(firstRowNum=0)
/*
* 遍历当前sheet中的所有行
* 排除第一行 第一行为标题头 不纳入遍历
*/
for (int x = firstRowNum+2; x <= rows; x++) {
// 获取行
row = sheet.getRow(x);
if(row == null ){//若此行为空,则跳出循环继续下一行
continue;
}else if (isEmptyCell(row.getCell(0)) && isEmptyCell(row.getCell(1))
&& isEmptyCell(row.getCell(2))&& isEmptyCell(row.getCell(3))){
continue;
}
TUserBean student = new TUserBean();// 每一行一个学生信息
int firstCellNum = row.getFirstCellNum();// 每一行的第一列,(firstCellNum=0)
int lastCellNum = row.getLastCellNum();// 每一行的最后一列,获取列数,比最后一列列标大1
//遍历所有的列
for (int y = firstCellNum; y < lastCellNum; y++) {
if(y == 0){ // 姓名
if (isEmptyCell(row.getCell(0))) {
throw new Exception("第" + (x + 1) + "行姓名不能为空");
} else {
Object username = this.getCellValue(row.getCell(0));
// set 用户名
student.setUsername(username.toString());
}
}else if(y == 1){// 登录名
if(isEmptyCell(row.getCell(1)) ){
throw new Exception("第"+(x+1)+"行登录账号不能为空");
}if(userService.isUserEmpty(this.getCellValue(row.getCell(1)).toString()) ){
studentsExisted.add(student);
continue;
}else{
String loginname = this.getCellValue(row.getCell(1)).toString();
student.setLoginname(loginname);//登录名默认为学号
}
}else if(y == 2){//手机号
if(isEmptyCell(row.getCell(2))){
student.setUserphone("");
}else{
if(!isPhone(this.getCellValue(row.getCell(2)).toString())){
throw new Exception("第"+(x+1)+"行手机号不合法");
}
Object phone = this.getCellValue(row.getCell(2));
student.setUserphone(phone.toString());
}
}
}
// 简易用户密码加密student.setLoginpwd(md5.getMD5ofStr(md5.getMD5ofStr(md5.getMD5ofStr("123456"))));
student.setCreateid(createid);
student.setUserstate(1);
student.setUsertype(2);
students.add(student);
}
resultMap.put("code" , 0);
resultMap.put("students" , students);
resultMap.put("studentsExisted" , studentsExisted);
}catch (Exception e){
resultMap.put("code" , 1);
resultMap.put("msg" , e.getMessage());
throw new Exception(e.getMessage());
}
return resultMap;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
* @param inStr,fileName
* @author hxk
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
Workbook wb;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析Excel格式有误!");
}
return wb;
}
/**
* 判断单元格是否是空值
* @param cell
* @author hxk
* @return
*/
public Boolean isEmptyCell(Cell cell){
String value = null;
if(cell != null){
value = this.getCellValue(cell).toString();
}
Boolean rtn = false;
//if(cell == null || this.getCellValue(cell).equals("") || this.getCellValue(cell) == null){
if(cell == null || N_Utils.isEmpty(value)){
rtn = true;
}
return rtn;
}
/**
* 描述:对表格中数值进行格式化
* @param cell
* @author hxk
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0"); //格式化数字
// 此处需注意 若想使用以下几个方法 需保证poi 架包版本低于4.0
// 4.0 或至后版本会弃用该方法
// 弃用方法分别是
// getCellType() Cell.CELL_TYPE_STRING
// Cell.CELL_TYPE_BOOLEAN Cell.CELL_TYPE_BLANK
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
/**
* 验证手机号
* @param phone
* @return
* @author hxk
*/
public static boolean isPhone(String phone) {
String regex = "^((13[0-9])|(14[5,6,7,9])|(15([0-3]|[5-9]))|(166)|(17[0,1,3,5,6,7,8])|(18[0-9])|(19[8|9]))\\d{8}$";
if (phone.length() != 11) {
return false;
} else {
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(phone);
boolean isMatch = m.matches();
return isMatch;
}
}
}