依赖
<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>Excel导入
总结一下目标，就是要将excel中的数据行、逐一提取，最后得到一个list，这个list的每个元素就是excel的每个数据行的实例，之后的操作就是常规的java逻辑处理了。
可以把这个过程分为三步：
1）接收数据
后台使用 MultipartFile 接收文件数据（这里复习一下，springmvc接收参数的几种方式）。
前端如果是前后端分离项目我们不用管，
如果是jsp项目可以用文件标签<input type="file" name="" value=""/>传参。
2）提取数据
提取数据的底层是用IO流实现的，我们这里使用封装好的工具类。excel工具类有很多、很多，我一般是使用适配性最好的，不然一会儿springboot项目、一会儿spring项目等等，还得换不同的工具类；效率什么的不是首要考虑项。
使用工具类后，我们就得到了一个List<List<String>>：Excel的每个数据行组成一个List<String>，多个数据行就组成了List<List<String>>
这里有一个小坑，说明一下；有时候你导入的数据，如果是数字比如32位的卡号等等，用工具类提取出来成了科学计数法、或者后面加了小数点；这就说明你用的这个工具类没有将数字类型数据进行处理，你需要在工具类中找到数字类型，添加toText()方法。
当然你也可以使用我后面提供的工具类，这个问题已经作了处理。
3）将list的元素处理成实例对象，方便后续处理
在作转换的时候，还可以加一些校验、限制，比如限制excel导入总行数不得超过多少、限制某列参数不能重复等等。
下面提供一下我用的excel导入工具类，springboot或者spring项目都可以用
public class ImportExeclUtil {
private static int totalRows = 0;// 总行数
private static int totalCells = 0;// 总列数
private static String errorInfo;// 错误信息
/** 无参构造方法 */
public ImportExeclUtil()
{
}
public static int getTotalRows()
{
return totalRows;
}
public static int getTotalCells()
{
return totalCells;
}
public static String getErrorInfo()
{
return errorInfo;
}
/**
*
* 根据流读取Excel文件
*
*
* @param inputStream
* @param isExcel2003
* @return
* @see [类、类#方法、类#成员]
*/
public List<List<String>> read(InputStream inputStream, boolean isExcel2003)
throws IOException
{
List<List<String>> dataLst = null;
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
if (isExcel2003)
{
wb = new HSSFWorkbook(inputStream);
}
else
{
wb = new XSSFWorkbook(inputStream);
}
dataLst = readDate(wb);
return dataLst;
}
/**
*
* 读取数据
*
* @param wb
* @return
* @see [类、类#方法、类#成员]
*/
private List<List<String>> readDate(Workbook wb)
{
List<List<String>> dataLst = new ArrayList<List<String>>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (totalRows >= 1 && sheet.getRow(0) != null)
{
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = 1; r < totalRows; r++)
{
Row row = sheet.getRow(r);
if (row == null)
{
continue;
}
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (int c = 0; c < getTotalCells(); c++)
{
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell)
{
// 以下是判断数据的类型
switch (cell.getCellTypeEnum())
{
case NUMERIC: // 数字
//如果是日期的话
if(cell != null && HSSFDateUtil.isCellDateFormatted(cell)){
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
String da = formater.format(d);
cellValue = da;
break;
}
cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
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 ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowLst.add(cellValue);
}
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
return dataLst;
}
/**
*
* 根据Excel表格中的数据判断类型得到值
*
* @param cell
* @return
* @see [类、类#方法、类#成员]
*/
/*private static String getCellValue(Cell cell)
{
String cellValue = "";
if (null != cell)
{
// 以下是判断数据的类型
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
;: // 数字
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell))
{
Date theDate = cell.getDateCellValue();
SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
cellValue = dff.format(theDate);
}
else
{
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}*/
/**
*
* 根据实体成员变量的类型得到成员变量的值
*
* @param realValue
* @param fields
* @param f
* @param cellValue
* @return
* @see [类、类#方法、类#成员]
*/
private static Object getEntityMemberValue(Object realValue, Field[] fields, int f, String cellValue)
{
String type = fields[f].getType().getName();
switch (type)
{
case "char":
case "java.lang.Character":
case "java.lang.String":
realValue = cellValue;
break;
case "java.util.Date":
realValue = StringUtils.isBlank(cellValue) ? null : DateUtil.strToDate(cellValue, DateUtil.YYYY_MM_DD);
break;
case "java.lang.Integer":
realValue = StringUtils.isBlank(cellValue) ? null : Integer.valueOf(cellValue);
break;
case "int":
case "float":
case "double":
case "java.lang.Double":
case "java.lang.Float":
case "java.lang.Long":
case "java.lang.Short":
case "java.math.BigDecimal":
realValue = StringUtils.isBlank(cellValue) ? null : new BigDecimal(cellValue);
break;
default:
break;
}
return realValue;
}
/**
*
* 根据路径或文件名选择Excel版本
*
*
* @param filePathOrName
* @param in
* @return
* @throws IOException
* @see [类、类#方法、类#成员]
*/
public static Workbook chooseWorkbook(String filePathOrName, InputStream in)
throws IOException
{
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName);
if (isExcel2003)
{
wb = new HSSFWorkbook(in);
}
else
{
wb = new XSSFWorkbook(in);
}
return wb;
}
static class ExcelVersionUtil
{
/**
*
* 是否是2003的excel，返回true是2003
*
*
* @param filePath
* @return
* @see [类、类#方法、类#成员]
*/
public static boolean isExcel2003(String filePath)
{
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
*
* 是否是2007的excel，返回true是2007
*
*
* @param filePath
* @return
* @see [类、类#方法、类#成员]
*/
public static boolean isExcel2007(String filePath)
{
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
public static class DateUtil
{
// ======================日期格式化常量=====================//
public static final String YYYY_MM_DDHHMMSS = "yyyy-MM-dd HH:mm:ss";
public static final String YYYY_MM_DD = "yyyy-MM-dd";
public static final String YYYY_MM = "yyyy-MM";
public static final String YYYY = "yyyy";
public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss";
public static final String YYYYMMDD = "yyyyMMdd";
public static final String YYYYMM = "yyyyMM";
public static final String YYYYMMDDHHMMSS_1 = "yyyy/MM/dd HH:mm:ss";
public static final String YYYY_MM_DD_1 = "yyyy/MM/dd";
public static final String YYYY_MM_1 = "yyyy/MM";
/**
*
* 自定义取值，Date类型转为String类型
*
* @param date 日期
* @param pattern 格式化常量
* @return
* @see [类、类#方法、类#成员]
*/
public static String dateToStr(Date date, String pattern)
{
SimpleDateFormat format = null;
if (null == date)
return null;
format = new SimpleDateFormat(pattern, Locale.getDefault());
return format.format(date);
}
/**
* 将字符串转换成Date类型的时间
* <hr>
*
* @param s 日期类型的字符串<br>
* datePattern :YYYY_MM_DD<br>
* @return java.util.Date
*/
public static Date strToDate(String s, String pattern)
{
if (s == null)
{
return null;
}
Date date = null;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
try
{
date = sdf.parse(s);
}
catch (ParseException e)
{
e.printStackTrace();
}
return date;
}
}
}再提供一个应用实例：
@ApiOperation(value = "以导入excel方式，上传要申请学分的用户")
@GetMapping(value = "/uuApplyUserInfo")
public AjaxResult uuApplyUserInfo(@RequestParam(value = "files",required = false) MultipartFile files) {
try {
//工具类
ImportExeclUtil readExcelUtil = new ImportExeclUtil();
List<List<String>> read = readExcelUtil.read(files.getInputStream(), true);
if (CollectionUtils.isNotEmpty(read)){
List<ApplyCreditUserDto> importList = read.stream().map(e -> {
ApplyCreditUserDto importDto = new ApplyCreditUserDto();
importDto.setUserName(e.get(0));
importDto.setCreditCardNo(e.get(1));
importDto.setCreditCardPwd(e.get(2));
return importDto;
}).collect(Collectors.toList());
if (CollectionUtils.isEmpty(importList)){
return AjaxResult.error("不能导入空文件");
}
//最多导入1W条
final int maxInt = 10000;
if (importList.size() > maxInt){
return AjaxResult.error("导入最多修改1W条");
}
List<String> orderIds = importList.stream()
.map(ApplyCreditUserDto::getUserName)
.distinct()
.collect(Collectors.toList());
if (!Objects.equals(orderIds.size(),importList.size())){
return AjaxResult.error("导入信息中，有用户");
}
//调用业务层
return applyCreditLogService.uuApplyUserInfo(importList);
}else{
return AjaxResult.error("不能导入空文件");
}
} catch (Exception e) {
e.printStackTrace();
return AjaxResult.error("导入失败，更新数据库时报错！报错信息：" + e.toString());
}
}
Excel导出
这里先说明一下这里要做的是把从数据库查到的数据行，导出到excel；和下载一个静态文件（比如下载Excel静态文件模板），是不一样的需求。
自定义注解
为了方便，提高复用效率，这里自定义2个注解；
第一个：@EnableExcel，用来开启Excel表格的导出，用在装导出数据的实体类上；
/**
* 标记类是否开启Excel
* @Author: Sunlong
* @date: 2020/5/10 20:29
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExcel {
}
第二个：@ExcelRow，用在装导出数据的实体类的属性上，用来映射字段与excel的对应关系；
/**
* excel 表格 列名注解
*
* @author sunlong
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelRow {
/**
* Excel 对应列名
* @return
*/
String name();
/**
* excel 列名备注
* @return
*/
String note() default "";
}
代码逻辑—提取导出工具类
a、通过反射获取自定义注解EnableExcel 判断是否开启Excel导出
b、通过反射获取自定义注解ExcelRow 获取列对应的属性
c、把属性对应的列下标取出来，属性名做为key，下标做为value放到map中
d、遍历要导出的数据集合，通过属性描述器PropertyDescriptor获取对应属性下标及属性值并设置到cell单元格中
还是为了方便，已经提高复用效率，我们将上述代码提取成一个工具类，如下：
public class ExportExcelUtils {
/**
* workbook
* @param titleList
* @return
*/
public static HSSFWorkbook getWorkBook(List<String> titleList){
//第一步，创建一个workbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 一个sheet
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFRow rowTitle = sheet.createRow(0); // 第一行 标题
// 设置标题
for (int i = 0; i < titleList.size(); i++) {
HSSFCell cell = rowTitle.createCell(i);
cell.setCellValue(titleList.get(i));
}
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
/*sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));
sheet.addMergedRegion(new CellRangeAddress(titleList.size()-1,titleList.size()-1,titleList.size()-1,titleList.size()+1));*/
return wb;
}
public static <T> HSSFWorkbook getWorkBook(List<String> titleList , List<T> dataList) throws IntrospectionException, InvocationTargetException, IllegalAccessException {
if (CollectionUtils.isNotEmpty(dataList)) {
T t1 = dataList.get(0);
Class<?> t1Class = t1.getClass();
EnableExcel enableExcel = t1Class.getAnnotation(EnableExcel.class);
if (enableExcel == null) {
throw new IllegalArgumentException("EnableExcel 注解没有在实体类启用");
}
Field[] fields = t1Class.getDeclaredFields();
if (fields != null && fields.length > 0) {
Map<String , Integer> titleMap = new HashMap<>(titleList.size()); // 存放属性名称对应的下标
int fieldExcelSize = 0; // 类中ExcelRow 注解的数量
for (Field field : fields) {
field.setAccessible(true);
String fieldName = field.getName();
ExcelRow excelRow = field.getAnnotation(ExcelRow.class);
if (excelRow != null) {
String name = excelRow.name();
if (StringUtils.isEmpty(name)) {
throw new IllegalArgumentException("ExcelRow 注解name属性不能为空");
}
int index = titleList.indexOf(name.trim());
if (index != -1) {
fieldExcelSize++;
titleMap.put(fieldName , index);
}
}
}
if (!(titleList.size() == fieldExcelSize)) {
throw new IllegalArgumentException("ExcelRow 注解name属性对应的列数不对");
}
HSSFWorkbook workBook = getWorkBook(titleList);
HSSFSheet sheet = workBook.getSheetAt(0);
for (T t : dataList) {
int lastRowNum = sheet.getLastRowNum();
HSSFRow row = sheet.createRow(lastRowNum + 1);
BeanInfo beanInfo = Introspector.getBeanInfo(t.getClass());
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
String fieldName = propertyDescriptor.getName();
if (titleMap.containsKey(fieldName)) {
Method readMethod = propertyDescriptor.getReadMethod();
if (readMethod != null) {
Class<?> returnType = readMethod.getReturnType();
String simpleName = returnType.getSimpleName();
Object invoke = readMethod.invoke(t);
String value = "";
// 可以根据不同的类型返回不同的数据
if ("date".equalsIgnoreCase(simpleName)) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (invoke != null) {
value = simpleDateFormat.format(invoke);
}
}
if (invoke != null && "".equals(value)) {
value = invoke.toString();
}
row.createCell(titleMap.get(fieldName)).setCellValue(value);
}
}
}
}
return workBook;
}
}
return null;
}
}
应用实例
创建一个用来装导出数据的类（为了不与项目其他功能冲突，我一般都是新建一个专门做导出的实体类）
@EnableExcel
@Data
public class UserEntity {
@ExcelRow(name = "name")
private String username;
@ExcelRow(name = "pass")
private String password;
@ExcelRow(name = "date")
private Date createDate;
}
模拟导出功能：
public class Test {
public static void main(String[] args) throws IllegalAccessException, IntrospectionException, InvocationTargetException, IOException {
List<String> titleList = new ArrayList<>();
titleList.add("name");
titleList.add("pass");
titleList.add("date");
List<UserEntity> userEntities = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserEntity userEntity1 = new UserEntity();
userEntity1.setUsername("username"+i);
userEntity1.setPassword("password"+i);
userEntity1.setCreateDate(new Date());
userEntities.add(userEntity1);
}
HSSFWorkbook workBook = ExportExcelUtils.getWorkBook(titleList, userEntities);
if (workBook != null) {
File file = new File("D:\\test_export.xlsx");
workBook.write(file);
}
}
}
2433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
