poi实现Excel文件的导入导出

poi结构说明

HSSF提供读写Microsoft Excel XLS格式档案的功能
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能
HWPF提供读写Microsoft Word DOC格式档案的功能
HSLF提供读写Microsoft PowerPoint格式档案的功能
HDGF提供读Microsoft Visio格式档案的功能
HPBF提供读Microsoft Publisher格式档案的功能
HSMF提供读Microsoft Outlook格式档案的功能

引入poi依赖包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.0</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.0</version>
</dependency>

一、Excel文件的导出

第一步、获取表内容数据：
根据表头内容与实体类属性对应的map，利用反射机制获取get方法来取出该实体数据

/**
* @param excelName  文件名称
* @param fileFormat 文件格式
* @param sheetName  sheet页名称
* @param map        表头内容-实体类属性
* @param lists      表数据
* @param response   导出
*/
public static void exportExcel(String excelName, String fileFormat, String sheetName,
                              Map<String, String> map, List<?> lists, HttpServletResponse response) {
	List<String> titleLists = new ArrayList<>();
	List<String> attributeLists = new ArrayList<>();
	for (String next : map.keySet()) {
		titleLists.add(next);
		attributeLists.add(map.get(next));
	}
	Map<Integer, List<String>> contents = new LinkedHashMap<>();
	for (int i = 0; i < lists.size(); i++) {
		List<String> list = new ArrayList<>();
		for (String s : attributeLists) {
			String value = EntityIsNull.getValue(s, lists.get(i),null);
			if (value != null && !"".equals(value)) {
				list.add(value);
			} else {
				list.add("");
			}
		}
		contents.put(i, list);
	}
	export(excelName, fileFormat, sheetName, titleLists, contents, response);
}

第二步、开始导出

/**
* @param excelName  文件名称
* @param fileFormat 文件格式
* @param sheetName  sheet页名称
* @param titles     表头内容
* @param contents   表格内容
* @param response   导出
*/
private static void export(String excelName, String fileFormat, String sheetName, List<String> titles,
                          Map<Integer, List<String>> contents, HttpServletResponse response) {
   Workbook workbook = null;
   try {
       if ("xls".equalsIgnoreCase(fileFormat)) {
           workbook = new HSSFWorkbook();
       } else if ("xlsx".equalsIgnoreCase(fileFormat)) {
           workbook = new XSSFWorkbook();
       } else {
           workbook = new SXSSFWorkbook(100);
       }
       sheetName = sheetName != null ? sheetName : "sheet";
       CellStyle style = getStyle(workbook, false);
       Sheet sheet = workbook.createSheet(sheetName);
       //定义当前表格下标
       int lastRowNum = 0;
       //创建行
       Row titleRow = sheet.createRow(lastRowNum);
       //设置行高
       titleRow.setHeightInPoints(22);
       //写入表格标题
       for (int i = 0; i < titles.size(); i++) {
           //创建列
           Cell titleCell = titleRow.createCell(i);
           //设置单元格属性
           titleCell.setCellType(CellType.STRING);
           //写入内容
           titleCell.setCellValue(titles.get(i));
           //设置样式
           titleCell.setCellStyle(style);
       }
       lastRowNum++;
       //写入表格内容
       for (int i = 0; i < contents.size(); i++) {
           Row contentRow = sheet.createRow(lastRowNum + i);
           contentRow.setHeightInPoints(22);
           List<String> list = contents.get(i);
           for (int j = 0; j < list.size(); j++) {
               Cell cell = contentRow.createCell(j);
               cell.setCellType(CellType.STRING);
               cell.setCellValue(list.get(j));
               cell.setCellStyle(style);
           }
       }
       //设置自动列宽
       setSizeColumn(sheet, titleRow.getPhysicalNumberOfCells());
       workbook.write(generateResponseExcel(excelName, response));
   } catch (Exception e) {
       e.printStackTrace();
   } finally {
       try {
           if (workbook != null) {
               workbook.close();
           }
       } catch (IOException e) {
           e.printStackTrace();
       }
   }
}

编辑表格内样式

/** 表格内样式调整 */
public static CellStyle getStyle(Workbook workbook, boolean setFont) {
    CellStyle cellStyle = workbook.createCellStyle();
    //设置内容居中-水平居中
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //设置内容居中-垂直居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    //设置边框 THIN--细实线 DOTTED--细虚线
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    //设置字体
    if (setFont) {
        Font font = workbook.createFont();
        //字体加粗
        font.setBold(true);
        //字体格式
        font.setFontName("宋体");
        //字体字号
        font.setFontHeight((short) (18 * 20));
        cellStyle.setFont(font);
    }
    //设置文字自动换行
    cellStyle.setWrapText(false);
    return cellStyle;
}

第三步、设定响应请求头格式，发送文件到客户端

/**
 * @param excelName 要生成的文件名字
 * @param response  返回表格
 */
private static ServletOutputStream generateResponseExcel(String excelName, HttpServletResponse response) throws IOException {
    excelName = excelName == null || "".equals(excelName) ? "excel" : URLEncoder.encode(excelName, "UTF-8");
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + excelName + ".xlsx");
    return response.getOutputStream();
}

二、Excel文件的导入

/**
* @param file 文件
* @param map  表头内容-实体类属性
* @return List<Map < String, String>>
*/
public static List<Map<String, String>> importExcel(MultipartFile file, Map<String, String> map) {
   //存储表格数据
   List<Map<String, String>> list = new ArrayList<>();
   String fileName = file.getOriginalFilename();
   if (fileName != null) {
   		fileName = fileName.substring(fileName.lastIndexOf(".") + 1);
   }
   Workbook workbook = null;
   InputStream inputStream = null;
   try {
       inputStream = file.getInputStream();
       if ("xls".equalsIgnoreCase(fileName)) {
           workbook = new HSSFWorkbook(inputStream);
       } else {
           workbook = new XSSFWorkbook(inputStream);
       }else {
		   workbook = WorkbookFactory.create(inputStream);
	   }
       //获取第一个sheet页内容
       Sheet sheetAt = workbook.getSheetAt(0);
       //获取表格第一行
       Row titleRow = sheetAt.getRow(0);
       //获取表格行数
       int rowCount = sheetAt.getPhysicalNumberOfRows();
       for (int i = 1; i < rowCount; i++) {
           Map<String, String> hashMap = new HashMap<>();
           //从表格第二行开始读取
           Row contentRow = sheetAt.getRow(i);
           if (!isEmptyRow(contentRow)) {
               //获取行中的列数
               int cellCount = contentRow.getPhysicalNumberOfCells();
               for (int j = 0; j < cellCount; j++) {
                   Cell titleCell = titleRow.getCell(j);
                   Cell contentCell = contentRow.getCell(j);
                   String value = null;
                   switch (contentCell.getCellType()) {
                       case STRING:
                           value = contentCell.getStringCellValue().trim();
                           value = StringUtils.isEmpty(value) ? "" : value;
                           break;
                       case NUMERIC:
                           if (DateUtil.isCellDateFormatted(contentCell)) {
                               Date date = contentCell.getDateCellValue();
                               value = DateUtils.getString(date, "yyyy-MM-dd HH:mm:ss");
                           } else {
                               contentCell.setCellType(CellType.STRING);
                               value = contentCell.getStringCellValue();
                               value = StringUtils.isEmpty(value) ? "" : value;
                           }
                           break;
                       case BOOLEAN:
                           value = String.valueOf(contentCell.getBooleanCellValue());
                           break;
                       default:
                           value = "";
                           break;
                   }
                   String title = titleCell.getStringCellValue();
                   hashMap.put(map.get(title), value);
               }
               list.add(hashMap);
           }
       }
       workbook.close();
       inputStream.close();
       return list;
   } catch (Exception e) {
       e.printStackTrace();
   }
   return null;
}

判断表格行数据是否为空

/**
* 判断表格行是否为空
*
* @param row 行
* @return boolean
*/
private static boolean isEmptyRow(Row row) {
   if (row == null) {
       return true;
   }
   //表开始列下标
   int firstCellNum = row.getFirstCellNum();
   //表最后列下标
   int lastCellNum = row.getLastCellNum();
   //空列数据
   int nullCellNum = 0;
   for (int i = firstCellNum; i < lastCellNum; i++) {
       Cell cell = row.getCell(i);
       if (cell == null || CellType.BLANK.equals(cell.getCellType())) {
           nullCellNum++;
           continue;
       }
       cell.setCellType(CellType.STRING);
       String cellValue = cell.getStringCellValue().trim();
       if (StringUtils.isEmpty(cellValue)) {
           nullCellNum++;
       }
   }
   return (lastCellNum - firstCellNum) == nullCellNum;
}

判断表格列值是否为空

/**
* 判断列是否为空
*
* @param cell 列
* @return boolean
*/
private static boolean isEmptyCell(Cell cell) {
   return cell == null || CellType.BLANK.equals(cell.getCellType());
}

设置表格自动列宽

/** 设置自动列宽 */
private static void setSizeColumn(Sheet sheet, int columnLength) {
   for (int columnNum = 0; columnNum <= columnLength; columnNum++) {
       int columnWidth = sheet.getColumnWidth(columnNum) / 256;
       for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
           Row currentRow; // 当前行未被使用过
           if (sheet.getRow(rowNum) == null) {
               currentRow = sheet.createRow(rowNum);
           } else {
               currentRow = sheet.getRow(rowNum);
           }
           if (currentRow.getCell(columnNum) != null) {
               Cell currentCell = currentRow.getCell(columnNum);
               if (currentCell.getCellType() == CellType.STRING) {
                   int length = currentCell.getStringCellValue().getBytes().length;
                   if (columnWidth < length) {
                       columnWidth = length;
                   }
               }
           }
       }
       sheet.setColumnWidth(columnNum, (int) ((columnWidth + 2.5) * 256));
   }
}

获取对应属性名的值

/**
* 获取对应属性名的值
*
* @param fieldName 字段名
* @param object    对象
* @param format    时间格式(yyyy-MM-dd HH:mm:ss)
* @return String
*/
public static String getValue(String fieldName, Object object, String format) {
	String value = null;
	try {
		String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
		Class<?> aClass = object.getClass();
		Field field = aClass.getDeclaredField(fieldName);
		/** 获取属性类型 */
		String type = field.getGenericType().getTypeName();
		Method method = aClass.getMethod(getter);
		if (type.contains("Date")) {
			Date date = (Date) method.invoke(object);
			/** 设置时间格式 */
			if (date != null) {
				if (format != null && !"".equals(format)) {
					value = DateUtils.getString(date, format);
				} else {
					value = DateUtils.getString(date);
				}
			}
		} else {
			value = method.invoke(object) + "";
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	return value;
}

测试

public static void main(String[] args) {
    //文件导出
    Map<String, String> map = new HashMap<>();
    map.put("序号", "id");
    map.put("姓名", "userName");
    map.put("地址", "site");
    List<User> userList = new ArrayList<>();
    User user = new User();
    user.setId(1);
    user.setUserName("张三");
    user.setSite("地球");
    userList.add(user);
    exportExcel("用户信息", "xlsx", "用户", map, userList, null);
    //文件导入
    File excelFile = new File("C:/test/用户信息.xlsx");
    List<Map<String, String>> list = importExcel(excelFile, map);
    System.out.println("list = " + list);
}