Source code

Download source archive ↓

upstream/piflow-bundle/src/main/scala/cn/piflow/bundle/generic/excel/ExcelRead.scala

1package cn.piflow.bundle.generic.excel
2
3import cn.piflow.conf.bean.PropertyDescriptor
4import cn.piflow.conf.util.{ImageUtil, MapUtil}
5import cn.piflow.conf.{ConfigurableStop, Port, StopGroup}
6import cn.piflow.{JobContext, JobInputStream, JobOutputStream, ProcessContext}
7import org.apache.spark.sql.SparkSession
8
9class ExcelRead extends ConfigurableStop{
10  val authorEmail: String = "ygang@cnic.cn"
11  val description: String = "Create a DataFrame from an Excel file"
12  val inportList: List[String] = List(Port.DefaultPort)
13  val outportList: List[String] = List(Port.DefaultPort)
14
15  var filePath: String = _
16  var header: String = _
17  var dataAddress: String = _
18  //  var setErrorCellsToFallbackValues: String = _
19  //  var timestampFormat: String = _
20  //  var excerptSize: String = _
21
22  override def perform(in: JobInputStream, out: JobOutputStream, pec: JobContext): Unit = {
23    val spark = pec.get[SparkSession]()
24
25    val frame = spark.read
26      .format("com.crealytics.spark.excel")
27      .option("dataAddress",dataAddress)
28      //      .option("setErrorCellsToFallbackValues", setErrorCellsToFallbackValues)
29      //      .option("timestampFormat", timestampFormat)
30      //      .option("excerptSize", excerptSize.toInt)
31      .option("header", header)
32      .load(filePath)
33
34    frame.show()
35
36    out.write(frame)
37  }
38
39  override def setProperties(map: Map[String, Any]): Unit = {
40    filePath = MapUtil.get(map,"filePath").asInstanceOf[String]
41    header = MapUtil.get(map,"header").asInstanceOf[String]
42    dataAddress = MapUtil.get(map,"dataAddress").asInstanceOf[String]
43    //    setErrorCellsToFallbackValues = MapUtil.get(map,"setErrorCellsToFallbackValues").asInstanceOf[String]
44    //    timestampFormat = MapUtil.get(map,"timestampFormat").asInstanceOf[String]
45    //    excerptSize = MapUtil.get(map,"excerptSize").asInstanceOf[String]
46  }
47
48  override def getPropertyDescriptor(): List[PropertyDescriptor] = {
49    var descriptor : List[PropertyDescriptor] = List()
50
51
52    val filePath = new PropertyDescriptor()
53      .name("filePath")
54      .displayName("FilePath")
55      .description("The path of excel file")
56      .defaultValue("")
57      .required(true)
58      .example("/test/test.xlsx")
59    descriptor = filePath :: descriptor
60
61    val header = new PropertyDescriptor()
62      .name("header")
63      .displayName("Header")
64      .description("Whether the excel file has a header")
65      .defaultValue("true")
66      .allowableValues(Set("true","false"))
67      .required(true)
68      .example("true")
69    descriptor = header :: descriptor
70
71    val dataAddress = new PropertyDescriptor()
72      .name("dataAddress")
73      .displayName("DataAddress")
74      .description("The location of data to read or write can be specified with the dataAddress option, Currently the following address styles are supported:" +
75        "A1: Start cell of the data. Reading will return all rows below and all columns to the right. Writing will start here and use as many columns and rows as required.\n" +
76        "'My Sheet'!A1: Same as above, but with a specific sheet.\n" +
77        "A1:F35: Cell range of data. Reading will return only rows and columns in the specified range. Writing will start in the first cell (B3 in this example) " +
78        "and use only the specified columns and rows. If there are more rows or columns in the DataFrame to write, they will be truncated. Make sure this is what you want.\n" +
79        "'My Sheet'!A1:F35: Same as above, but with a specific sheet.")
80      .defaultValue("'Sheet1'!")
81      .required(true)
82      .example("A1")
83    descriptor = dataAddress :: descriptor
84
85    //    val setErrorCellsToFallbackValues = new PropertyDescriptor()
86    //      .name("setErrorCellsToFallbackValues")
87    //      .displayName("setErrorCellsToFallbackValues")
88    //      .description("default: false, where errors will be converted to null. If true, any ERROR cell values (e.g. #N/A) will be converted to the zero values of the column's data type.")
89    //      .defaultValue("false")
90    //      .required(false)
91    //      .example("fales")
92    //    descriptor = setErrorCellsToFallbackValues :: descriptor
93    //
94    //
95    //    val timestampFormat = new PropertyDescriptor()
96    //      .name("timestampFormat")
97    //      .displayName("timestampFormat")
98    //      .description("default: yyyy-mm-dd hh:mm:ss[.fffffffff]")
99    //      .defaultValue("yyyy-mm-dd hh:mm:ss")
100    //      .required(true)
101    //      .example("yyyy-mm-dd hh:mm:ss")
102    //    descriptor = timestampFormat :: descriptor
103    //
104    //    val excerptSize = new PropertyDescriptor()
105    //      .name("excerptSize")
106    //      .displayName("excerptSize")
107    //      .description("default: 10. If set and if schema inferred, number of rows to infer schema from")
108    //      .defaultValue("10")
109    //      .required(true)
110    //      .example("10")
111    //    descriptor = excerptSize :: descriptor
112
113    descriptor
114  }
115
116  override def getIcon(): Array[Byte] = {
117    ImageUtil.getImage("icon/excel/excelParse.png",this.getClass.getName)
118  }
119
120  override def getGroup(): List[String] = {
121    List(StopGroup.ExcelGroup)
122  }
123
124  override def initialize(ctx: ProcessContext): Unit = {
125
126  }
127
128}