本方法是在maven项目下,前后端分离开发,进行导入excel操作。
首先在maven项目中加入如下依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
然后是具体的导入代码:
@Override
public List<GroupUsersInfo> batchImport(MultipartFile file)
{
String fileName = file.getOriginalFilename();
boolean notNull = false;
List<GroupUsersInfo> userList = new ArrayList<GroupUsersInfo>();
String message = null;
try
{
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$"))
{
message = "上传文件格式不正确";
logger.error(message);
throw new RuntimeException(message);
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$"))
{
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003)
{
wb = new HSSFWorkbook(is);
}
else
{
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet != null)
{
notNull = true;
}
logger.info("导入用户文件" + notNull);
GroupUsersInfo groupUsersInfo = null;
for (int r = 1; r <= sheet.getLastRowNum(); r++)
{
Row row = sheet.getRow(r);
if (row == null || row.getCell(0) == null)
{
continue;
}
groupUsersInfo = new GroupUsersInfo();
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String account = row.getCell(0).getStringCellValue();
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String name = row.getCell(1).getStringCellValue();
// 一些其他参数,根据需要 用相应的类接收。。。
userList.add(groupUsersInfo);
}
}
catch(Exception e)
{
message = "************导入客户数据失败************";
logger.error(message, e);
return null;
}
return userList;
}