pom.xml引入依赖
org.apache.poi poi-ooxml4.1.0
测试类
import cn.demo.com.dao.UserMapper;
import cn.demo.com.model.UserModel;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTest {
@Resource
private UserMapper userMapper;
@Test
public void user() throws Exception{
String path2= "F:\excel\user"; // 读取的文件夹名称
String fileName = "";
File[] allFile = getAllFile(path2);
for (File file : allFile) {
FileInputStream input = new FileInputStream(file);
MultipartFile proFile =new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(input));
fileName = proFile.getOriginalFilename();
String fileName1 = fileName.substring(0,fileName.length()-4);
try {
byte[] bytes = proFile.getBytes();
ByteArrayInputStream is = new ByteArrayInputStream(bytes);
boolean isExcel2003 = true;
if (fileName.matches("^.+\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
// 获取sheet页
Sheet sheet = wb.getSheetAt(0);
Row titleRow = sheet.getRow(1);
Map titleMap = new HashMap<>();
// 获取标题行
for (int i = 0; i < titleRow.getPhysicalNumberOfCells(); i++) {
titleMap.put(String.valueOf(titleRow.getCell(i)), i);
}
Integer a1 = titleMap.get("姓名");
Integer a2 = titleMap.get("手机号码");
Integer a3 = titleMap.get("住址");
Integer a4 = titleMap.get("邮箱");
// 存放待新增的操作日志
List logList = new ArrayList<>();
// 存放有问题的的数据
List> errorList = new ArrayList<>();
// excel中的总数量
int count = 0;
for (int r = 2; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row.getPhysicalNumberOfCells() == 0) {
break;
}
// 记录总量
count += 1;
String name = row.getCell(a1) == null ? null : getCellValue(row.getCell(a1));
String phone= row.getCell(a2) == null ? null : getCellValue(row.getCell(a2));
String address = row.getCell(a3) == null ? null : getCellValue(row.getCell(a3));
String email = row.getCell(a4) == null ? null : getCellValue(row.getCell(a4));
// 将有问题的数据全部存入一个map中，后面的判断中若是需要存入ErrorExcel就直接丢进去
Map copy = new HashMap<>();
copy.put("姓名", name);
copy.put("手机号码", phone);
copy.put("住址", address);
copy.put("邮箱", email);
if (phone.length() > 11) {
copy.put("错误信息","手机号码字数超长");
errorList.add(copy);
continue;
}
if (address.length() > 255) {
copy.put("错误信息","住址字数过长");
errorList.add(copy);
continue;
}
String id = UUID.randomUUID().toString();
UserModel record = new UserModel();
record.setId(id);
record.setType(name);
record.setOperationTime(phone);
record.setOperator(address);
record.setContent(email);
logList.add(record);
// 一条条导入
// userMapper.insertSelective(record);
//
// System.out.println(name + ":" + phone+ " :导入成功");
}
// 新增的条数
int addCount = logList.size();
// 失败的条数
int failedCount = errorList.size();
if (errorList.size() > 0) {
// 将有问题的数据记录存放至ErrorExcel
String filePar = "F:\excel\usererror\";
String fileDir = filePar + fileName1 + "_ErrorExcel.xlsx";
String sheetName = "ErrorSheet";
String[] title = {"姓名", "手机号码", "住址", "邮箱","错误信息"};
CreateExcelFile.createExcelXlsx(filePar, fileDir, sheetName, title);
CreateExcelFile.writeToExcelXls(fileDir, sheetName, errorList);
}
try {
// 批量导入
// if (logList.size() > 0) {
// userMapper.insertForeach(logList);
// }
}catch (Exception e){
System.out.println( fileName + " :导入失败");
e.printStackTrace();
}
} catch (Exception e) {
System.out.println("文件读取失败...");
}
}
}
public static File[] getAllFile(String directoryPath) {
List list = new ArrayList();
File baseFile = new File(directoryPath);
File[] files = baseFile.listFiles();
return files;
}
public static String getCellValue(Cell cell){
String cellValue = "";
String temp1 = "";
BigDecimal temp2 = null;
if(cell == null){
return cellValue;
}
CellType ct = cell.getCellType();
switch (ct) {
case NUMERIC: // 数字
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
cellValue = sdf.format(date);
}else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
}finally{
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue()+"";;
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case BLANK: // 空值
cellValue = "";
break;
case _NONE: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
public static void createExcelXlsx(String filePar,String fileDir, String sheetName, String titleRow[]) {
File file=new File(filePar);
if(!file.exists()){//如果文件夹不存在
file.mkdirs();//创建文件夹
}
//创建workbook
xWorkbook = new XSSFWorkbook();
//新建文件
FileOutputStream fileOutputStream = null;
XSSFRow row = null;
try {
CellStyle cellStyle = xWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
//添加Worksheet（不添加sheet时生成的xlsx文件打开时会报错)
xWorkbook.createSheet(sheetName);
xWorkbook.getSheet(sheetName).createRow(0);
//添加表头, 创建第一行
row = xWorkbook.getSheet(sheetName).createRow(0);
for (short j = 0; j < titleRow.length; j++) {
XSSFCell cell = row.createCell(j, CellType.BLANK);
cell.setCellValue(titleRow[j]);
cell.setCellStyle(cellStyle);
}
fileOutputStream = new FileOutputStream(fileDir);
xWorkbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public static void writeToExcelXls(String fileDir, String sheetName, List> mapList) throws Exception {
//创建workbook
File file = new File(fileDir);
try {
xWorkbook = new XSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//文件流
FileOutputStream fileOutputStream = null;
XSSFSheet sheet = xWorkbook.getSheet(sheetName);
// 获取表格的总行数
int rowCount = sheet.getLastRowNum() + 1; // 需要加一
//获取表头的列数
int columnCount = sheet.getRow(0).getLastCellNum();
try {
// 获得表头行对象
XSSFRow titleRow = sheet.getRow(0);
//创建单元格显示样式
CellStyle cellStyle = xWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
if (titleRow != null) {
for (int rowId = 0; rowId < mapList.size(); rowId++) {
Map map = mapList.get(rowId);
XSSFRow newRow = sheet.createRow(rowId + 1);
newRow.setHeight((short) (20 * 20));//设置行高 基数为20
for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) { //遍历表头
//trim()的方法是删除字符串中首尾的空格
String mapKey = titleRow.getCell(columnIndex).toString().trim();
XSSFCell cell = newRow.createCell(columnIndex);
cell.setCellStyle(cellStyle);
cell.setCellValue(map.get(mapKey) == null ? null : map.get(mapKey).toString());
}
}
}
fileOutputStream = new FileOutputStream(fileDir);
xWorkbook.write(fileOutputStream);
} catch (Exception e) {
throw e;
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
