核心思想:读取excel的数据,其中每行为一个java对象进行存储。实现批量添加数据。
1.excelUtil
public class ExcelUtil {
/**
* 读取Excel内容,只读取第一个sheet
*
* @param fileName
* 文件名称(包含文件路径)
* @return 读取结果,可能包含null值
*/
public static List<String[]> readExcel(String fileName) {
return readExcel(new File(fileName));
}
/**
* 读取Excel内容,只读取第一个sheet
*
* @param file
* 要读取的文件
* @return 读取结果,可能包含null值
*/
public static List<String[]> readExcel(File file) {
List<String[]> result = new ArrayList<String[]>();
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(fileInputStream);// 通用
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
String[] rowStrs = new String[row.getLastCellNum()];
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
rowStrs[cell.getColumnIndex()] = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
rowStrs[cell.getColumnIndex()] = cell.getStringCellValue().replaceAll("\t|\r|\n", "").trim();
break;
case Cell.CELL_TYPE_FORMULA:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getNumericCellValue());
break;
default:
rowStrs[cell.getColumnIndex()] = "";
break;
}
}
result.add(rowStrs);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
}
}
}
return result;
}
/**
* 以文本格式读取Excel内容,只读取第一个sheet
*
* @param file
* 要读取的文件
* @return 读取结果,可能包含null值
*/
public static List<String[]> readExcelWithText(File file) {
List<String[]> result = new ArrayList<String[]>();
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
Sheet sheet = WorkbookFactory.create(fileInputStream).getSheetAt(0);
for (Row row : sheet) {
String[] rowStrs = new String[row.getLastCellNum()];
for (Cell cell : row) {
cell.setCellType(Cell.CELL_TYPE_STRING);
rowStrs[cell.getColumnIndex()] = cell.getStringCellValue();
}
result.add(rowStrs);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
}
}
}
return result;
}
public static List<String> readExcelSheetNames(File file) {
List<String> result = new ArrayList<String>();
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(fileInputStream);// 通用
int num = wb.getNumberOfSheets();
for (int i = 0; i < num; i++) {
String sheetName = wb.getSheetName(i);
result.add(sheetName);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
}
}
}
return result;
}
/**
* 读取excel,指定sheet页内容
*
* @param file
* @param sheetNum
* 从0开始
* @return
*/
public static List<String[]> readExcel(File file, int sheetNum) {
List<String[]> result = new ArrayList<String[]>();
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(fileInputStream);// 通用
int sheetCount = wb.getNumberOfSheets();
if (sheetNum < sheetCount) {
Sheet sheet = wb.getSheetAt(sheetNum);
for (Row row : sheet) {
String[] rowStrs = new String[row.getLastCellNum()];
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
rowStrs[cell.getColumnIndex()] = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
rowStrs[cell.getColumnIndex()] = cell.getStringCellValue().replaceAll("\t|\r|\n", "").trim();
break;
case Cell.CELL_TYPE_FORMULA:
rowStrs[cell.getColumnIndex()] = String.valueOf(cell.getNumericCellValue());
break;
default:
rowStrs[cell.getColumnIndex()] = "";
break;
}
}
result.add(rowStrs);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
}
}
}
return result;
}
public static void exportExcel(List<ExcelSheetVO> excelSheetVOs, OutputStream out) throws IOException {
Workbook wb = new HSSFWorkbook();
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(wb.createDataFormat().getFormat("@"));
for (ExcelSheetVO vo : excelSheetVOs) {
Sheet sheet = wb.createSheet(vo.getName());
int rownum = 0;
for (List<String> datas : vo.getDatas()) {
Row row = sheet.createRow(rownum);
rownum++;
int cellNum = 0;
for (String str : datas) {
Cell cell = row.createCell(cellNum);
cell.setCellStyle(cs);
cell.setCellValue(str);
cellNum++;
}
}
}
wb.write(out);
}
}
2.ExcelSheetVO
public class ExcelSheetVO {
private String name;
private List<List<String>> datas = new ArrayList<List<String>>();
public ExcelSheetVO() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<List<String>> getDatas() {
return datas;
}
public void setDatas(List<List<String>> datas) {
this.datas = datas;
}
public void addData(List<String> data) {
datas.add(data);
}
/**
* 增加空的数据,为了把excel格式设置为文本类型。
*
* @param rows
* @param columns
*/
public void addEmptyData(int rows, int columns) {
for (int i = 0; i < rows; i++) {
List<String> data = new ArrayList<String>();
for (int j = 0; j < columns; j++) {
data.add("");
}
addData(data);
}
}
}
3.service
public List<Map<String, Object>> batchAddTeacher(String path){
List<String[]> content = ExcelUtil.readExcel(path);
List<Map<String, Object>> errors=new ArrayList<Map<String,Object>>();
try{
for(int i=2;i<content.size();i++){//读取excel的每一行
Teacher teacher=new Teacher();
if(content.get(i).length>1 &&content.get(i)[1]!=null && content.get(i)[1]!=""){
if(BaseConstant.SPECIAL_NAME_UNAVAILABLE!=null&& BaseConstant.SPECIAL_NAME_UNAVAILABLE.length>0){
boolean flag=true;
for (String str : BaseConstant.SPECIAL_NAME_UNAVAILABLE) {
if(str.equalsIgnoreCase(content.get(i)[1])){
flag=false;
Map<String, Object> map=new LinkedHashMap<String, Object>();
map.put("message", "第"+(i+1)+"行"+2+"列"+" 用户名定义不合法");
errors.add(map);
break;
}
}
if(flag){
teacher.setName(content.get(i)[1]);
}
}else{
teacher.setName(content.get(i)[1]);
}
}else{
Map<String, Object> map=new LinkedHashMap<String, Object>();
map.put("message", "第"+(i+1)+"行"+2+"列"+" 姓名不能为空");
errors.add(map);
continue;
}
if(content.get(i).length>2 && content.get(i)[2]!=null && content.get(i)[2]!="" && content.get(i)[2].equals("男")){
teacher.setSex("0");
}else if(content.get(i).length>2 && content.get(i)[2]!=null && content.get(i)[2]!="" && content.get(i)[2].equals("女")){
teacher.setSex("1");
}else{
Map<String, Object> map=new LinkedHashMap<String, Object>();
map.put("message", "第"+(i+1)+"行"+3+"列"+" 性别无法识别");
errors.add(map);
continue;
}
if(content.get(i).length>3 && content.get(i)[3]!=null && content.get(i)[3]!=""){
teacher.setIntroduction(content.get(i)[3]);
}else{
Map<String, Object> map=new LinkedHashMap<String, Object>();
map.put("message", "第"+i+"行"+4+"列"+" 简历不能为空");
errors.add(map);
continue;
}
save(teacher);
}
}catch(Exception e){
e.printStackTrace();
}
return errors;
}