FME技巧分享之动态读取excel文件

FME读取excel的坑：

1、无法将以某行设置为字段行作为动态参数，比如当我们遇到各类型复杂表格时，

比如这种。 虽然FME可以设置从某行开始读取，但该设置并不能设置为参数。

2、如果某行内容的字符编码发生变化，则读取会报错，如下图所示，某行字符编码变为UTF-16LE，之前字符编码为UTF-8，出现不一致，则FME无法读取，唯一的解决办法是读取前提前修改字符编码，但是FME并没有读取前修改的功能。

---

解决办法：

用python的Pandas库，来手搓该读模块。废话不多说直接上干货。首先设置两个参数

 读取行数，以及excel文件路径。

如下图所示，该表格需求从7行开始读取。

 

在pythoncaller转换器中input函数写入代码，取参数行作为属性行，然后遍历出FMEfeature

import fme
import fmeobjects
import pandas as pd

class FeatureProcessor(object):
    """Template Class Interface:
    When using this class, make sure its name is set as the value of the 'Class
    to Process Features' transformer parameter.
    """

    def __init__(self):
        """Base constructor for class members."""
        pass

    def input(self, feature):
        road=FME_MacroValues['bg']
        alldata=pd.read_excel(road,header=None)
        for i in range(int(FME_MacroValues['属性行顺序']),len(alldata)):
            for a, corner in enumerate(alldata.iloc[int(FME_MacroValues['属性行顺序'])-1]):
                xx=alldata[a].at[i]
                feature.setAttribute(corner,xx)
            self.pyoutput(feature)
        
        #print(alldata.iloc[0])
       # 
            #feature.setAttribute(i,xx)
        
        """This method is called for each FME Feature entering the 
        PythonCaller. If knowledge of all input Features is not required for 
        processing, then the processed Feature can be emitted from this method 
        through self.pyoutput(). Otherwise, the input FME Feature should be 
        cached to a list class member and processed in process_group() when 
        'Group by' attributes(s) are specified, or the close() method.

        :param fmeobjects.FMEFeature feature: FME Feature entering the 
            transformer.
        """
        

    def close(self):
        """This method is called once all the FME Features have been processed
        from input().
        """
        pass

    def process_group(self):
        """When 'Group By' attribute(s) are specified, this method is called 
        once all the FME Features in a current group have been sent to input().

        FME Features sent to input() should generally be cached for group-by 
        processing in this method when knowledge of all Features is required. 
        The resulting Feature(s) from the group-by processing should be emitted 
        through self.pyoutput().

        FME will continue calling input() a number of times followed
        by process_group() for each 'Group By' attribute, so this 
        implementation should reset any class members for the next group.
        """
        pass

表格读取成功