Mybatis简单的通用报表导入

Service层

	//多了就写成枚举
	private final static String AFTER_EXCEL2007_FORMAT = "xlsx";
	private final static String BEFORE_EXCEL2007_FORMAT = "xls";

	/**
	 * @param isTitle Excel文件首行是否为标题行
	 * @param tableName 表名
	 * @param idName 该表主键名
	 * @param columns Excel文件每一列的对应的表的字段名
	 * @author Freeman
	 * */
	@Override
	public void importExcel(boolean isTitle, String tableName, String idName, String[] columns
		, MultipartFile file) throws IOException {
		String fileName = file.getOriginalFilename();

		if (fileName == null) {
			throw new ApiException("请输入正确的文件类型");
		}
		String fileFormat = fileName.substring(fileName.lastIndexOf(".") + 1);
		String[] values = new String[columns.length];

		if (AFTER_EXCEL2007_FORMAT.equals(fileFormat)) {
			XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
			for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
				XSSFSheet sheet = workbook.getSheetAt(i);
				insertExcel(isTitle, tableName, idName, columns, values, sheet);
			}
		} else if (BEFORE_EXCEL2007_FORMAT.equals(fileFormat)) {
			HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
			for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
				HSSFSheet sheet = workbook.getSheetAt(i);
				insertExcel(isTitle, tableName, idName, columns, values, sheet);
			}
		} else {
			throw new ApiException("请输入正确的文件类型");
		}
	}

	private void insertExcel(boolean isTitle, String tableName, String idName, String[] columns
		, String[] values, Sheet sheet) {
		int rowNum = sheet.getLastRowNum();
		String cellStr = null;
		Row row = null;
		Cell cell = null;

		String[] withIdValues = new String[columns.length + 1];
		String[] withIdColumns = new String[columns.length + 1];
		withIdColumns[0] = idName;

		for (int i = isTitle ? 1 : 0; i < rowNum + 1; i++) {
			row = sheet.getRow(i);

			for (int j = 0; j < columns.length; j++) {
				cell = row.getCell(j);
				cellStr = cell == null ? null : cell.getStringCellValue();
				withIdColumns[j + 1] = columns[j];
				values[j] = cellStr;
				withIdValues[j + 1] = cellStr;
			}
			if (!"".equals(idName) && idName != null) {
				withIdValues[0] = UUID.randomUUID().toString();
				uploadMapper.insertByExcel(tableName, withIdColumns, withIdValues);
			} else {
				uploadMapper.insertByExcel(tableName, columns, values);
			}
		}
	}

Mapper层

	int insertByExcel(@Param("table") String table, @Param("columns") String[] columns
		, @Param("values") String[] values);

Mapper对应的sql

	<insert id="insertByExcel">
        INSERT INTO ${table}
            <foreach collection="columns" item="column" open="(" separator="," close=")">
                ${column}
            </foreach>
        VALUES
            <foreach collection="values" item="value" open="(" separator="," close=")">
                #{value}
            </foreach>
    </insert>