Java 实现excel的导入导出

一、包

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

二、导出

 /**
     * 导出功能
     * 注意：泛型T类字段名和containBean集合里字段名字的一致性
     *
     * @param response
     * @param title       表名
     * @param headers     表头
     * @param list        数据集
     * @param containBean 数据集类型字段
     * @param <T>
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, String title, String[] headers, List<T> list, List<String> containBean) throws Exception {
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(title);
            HSSFRow row = sheet.createRow(0);
            /*创建第一行表头*/
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }
            Iterator<T> it = list.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                T t = (T) it.next();
                /*反射得到字段*/
                Field[] fields = t.getClass().getDeclaredFields();
                /*如果需要匹配*/
                if (CollectionUtils.isNotEmpty(containBean)) {
                    for (int j = 0; j < containBean.size(); j++) {
                        for (int i = 0; i < fields.length; i++) {
                            Field field = fields[i];
                            if (!field.getName().equals(containBean.get(j))) {
                                continue;
                            }
                            /*给每一列set值*/
                            setCellValue(t, field, row, j);
                        }
                    }
                } else {
                    for (int i = 0; i < fields.length; i++) {
                        Field field = fields[i];
                        setCellValue(t, field, row, i);
                    }
                }
            }
            /*application/vnd.ms-excel告诉浏览器要下载的是个excel*/
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            /*请求头设置，Content-Disposition为下载标识，attachment标识以附件方式下载*/
            response.addHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes(), "ISO8859-1") + ".xls");
            workbook.write(response.getOutputStream());
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
    }

    /**
     * 设置每一行中的列
     *
     * @param t
     * @param field
     * @param row
     * @param index
     * @param <T>
     */
    private static <T> void setCellValue(T t, Field field, HSSFRow row, int index) {
        HSSFCell cell = row.createCell(index);
        Object value = invoke(t, field);
        String textValue = null;
        if (value != null) {
            if (value instanceof Date) {
                Date date = (Date) value;
                textValue = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
            } else {
                textValue = value.toString();
            }
        }
        if (textValue != null) {
            cell.setCellValue(textValue);
        }
    }

    /**
     * 反射映射数据集字段
     *
     * @param t
     * @param field
     * @param <T>
     * @return
     */
    private static <T> Object invoke(T t, Field field) {
        try {
            String fieldName = field.getName();
            PropertyDescriptor pd = new PropertyDescriptor(fieldName, t.getClass());
            Method method = pd.getReadMethod();
            return method.invoke(t);
        } catch (Exception e) {
            return null;
        }
    }

参数分别是：response、title（文件名称）、headers（表格头中文）、list（数据集合）、containBean（表格头英文，与实体类中对应）

测试：

String title = "测试导出用户数据";  
String[] headers = {"用户名","密码","账号","真实名称","权限","状态","权限id","创建时间"};  
List<UserEnty> userList = transactionService.queryUser();
List<String> listColumn = Arrays.asList("username","password","id","realName","roleId","status","orgId","createTime");  
try {     
		PortExcelUtil.exportExcel(response,title,headers,userList,listColumn);
	 } catch (Exception e) {     
	 		e.printStackTrace();
	  }

三、导入

/**
	 * 读取excel  数
	 *
	 * @param fileName
	 * @param fins
	 * @param params
	 * @return
	 * @throws IOException
	 */
	public static JSONArray readExcel(String fileName, FileInputStream fins, ImportParams params, String[] columName, String[] headerName) throws IOException {

		String extension = fileName.lastIndexOf('.') == -1 ? "" : fileName.substring(fileName.lastIndexOf('.') + 1);
		if ("xls".equals(extension)) {
			return readExcel2003(fins, params, columName, headerName);
		} else if ("xlsx".equals(extension)) {
			return readExcel2007(fins, params, columName, headerName);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

/**
	 * @param fins
	 * @param params
	 * @return
	 * @throws IOException
	 */
	private static int readExcel2003TotalRow(FileInputStream fins, ImportParams params) throws IOException {
		HSSFWorkbook hwb = new HSSFWorkbook(fins);
		HSSFSheet sheet = hwb.getSheetAt(0);
		return sheet.getPhysicalNumberOfRows();
	}


	private static int readExcel2007TotalRow(FileInputStream fins, ImportParams params) throws IOException {
		XSSFWorkbook xwb = new XSSFWorkbook(fins);
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(params.getSheetNum());
		return sheet.getPhysicalNumberOfRows();
	}

	/***
	 * 读取2003  excel 2003 文档.
	 * @param fins 文件流
	 * @param params  参数
	 * @param colunName  文档标题对应的实体类参数
	 * @param headerName  文档抬头
	 * @return
	 * @throws IOException
	 */
	private static JSONArray readExcel2003(FileInputStream fins, ImportParams params, String[] colunName, String[] headerName) throws IOException {
		HSSFWorkbook hwb = new HSSFWorkbook(fins);
		HSSFSheet sheet = hwb.getSheetAt(0);
		HSSFRow row = sheet.getRow(params.getStartRows());
		HSSFCell cell = row.getCell(params.getCellNum());
		JSONArray jsonArray = new JSONArray();
		boolean flag = true;
		int totalNum = 0;
		int rowNum = 0;
		if (cell == null) {
			return jsonArray;
		}
		for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
			Sheet sheetData = hwb.getSheetAt(i);
			if (sheetData == null) {
				continue;
			}
			/*统计导入的总条数，要是你的excell包含了表头，就不用加1了*/
			if (sheetData.getLastRowNum() > 0) {
				totalNum += sheetData.getLastRowNum();
			}
			rowNum = row.getPhysicalNumberOfCells();
			Row rowsHeader = sheetData.getRow(0);
			for (int k = 0; k < rowNum; k++) {
				Cell cellData = rowsHeader.getCell(k);
				if (!cellData.toString().equals(headerName[k])) {
					flag = false;
				}
			}
			if (flag) {
				/*遍历行，这里j的初始值取1是因为我的表格里第一行是表头*/
				for (int j = 1; j < sheetData.getPhysicalNumberOfRows(); j++) {
					Cell cellData = null;
					JSONObject jsonObject = new JSONObject();
					for (int k = 0; k < rowNum; k++) {
						Row rows = sheetData.getRow(j);
						/*解析列，下标从0开始*/
						cellData = rows.getCell(k);
						jsonObject.put(colunName[k], cellData.toString());
						if (cellData == null) {
							continue;
						}
					}
					jsonArray.add(jsonObject);
				}
			}

		}
		return jsonArray;
	}

	/**
	 * 读取2007 文件
	 *
	 * @param fins
	 * @param params
	 * @return
	 * @throws IOException
	 */
	@JSONField(serialize = false)
	private static JSONArray readExcel2007(FileInputStream fins, ImportParams params, String[] colunName, String[] headerName) throws IOException {
		XSSFWorkbook xwb = new XSSFWorkbook(fins);
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(params.getSheetNum());
		Object value = null;
		XSSFRow row = sheet.getRow(params.getStartRows());
		XSSFCell cell = row.getCell(params.getCellNum());
		JSONArray jsonArray = new JSONArray();
		boolean flag = true;
		int totalNum = 0;
		int rowNum = 0;

		for (int i = 0; i < xwb.getNumberOfSheets(); i++) {
			Sheet sheetData = xwb.getSheetAt(i);
			if (sheetData == null) {
				continue;
			}


			/*统计导入的总条数，要是你的excell包含了表头，就不用加1了*/
			if (sheetData.getLastRowNum() > 0) {
				totalNum += sheetData.getLastRowNum();
			}
			rowNum = row.getPhysicalNumberOfCells();
			Row rowsHeader = sheetData.getRow(0);
			for (int k = 0; k < rowNum; k++) {
				Cell cellData = rowsHeader.getCell(k);
				if (!cellData.toString().equals(headerName[k])) {
					flag = false;
				}
			}
			if (flag) {
				/*遍历行，这里j的初始值取1是因为我的表格里第一行是表头*/
				for (int j = 1; j < sheetData.getPhysicalNumberOfRows(); j++) {
					Cell cellData = null;
					JSONObject jsonObject = new JSONObject();
					Row rows = sheetData.getRow(j);
					for (int k = 0; k < rowNum; k++) {
						/*解析列，下标从0开始*/
						cellData = rows.getCell(k);
						jsonObject.put(colunName[k], cellData.toString());
						if (cellData == null) {
							continue;
						}
					}
					jsonArray.add(jsonObject);
				}
			}
		}
		return jsonArray;
	}

返回的是JSONArray。
测试：

public static void main(String[] args) throws IOException {
   File file = new File("D:\\test\\tses1.xlsx");

   FileInputStream fileInputStream = new FileInputStream(file);

   String filename = file.getName();

   ImportParams importParams = new ImportParams();

   String[] columName = {"username","password","id","realName","roleId","status","orgId","createTime"};

   String[] headerName = {"用户名","密码","账号","真实名称","权限","状态","权限id","创建时间"};

   JSONArray jsonArray = ReadExcel.readExcel(filename, fileInputStream, importParams,columName,headerName);

   for(int i=0; i<jsonArray.size(); i++) {
      JSONObject jsonObject = (JSONObject) jsonArray.get(i);
      UserEnty userEnty = jsonObject.toJavaObject(UserEnty.class);
      System.out.println(userEnty.toString());
   }
}

注意：1、如果表格中的字段与headerName不一致，jsonObject 会为null；
2、headerName要与columName 和excel的表头相同；
3、JSONObject jsonObject = (JSONObject) jsonArray.get(i); UserEnty userEnty = jsonObject.toJavaObject(UserEnty.class);
这个是将jsonObject 变成一个实体类，便于插入。