用Scala读取Excel文件 [英] Reading Excel file with Scala

问题描述

我正在编写一个快速测试，该测试将用户注册到电子表格中的数据中.

I am writing a quick test that registers a user with the data from a spreadsheet.

想法是转到网站>单击注册>在excel行A1和B1中读取电子邮件和密码>在注册站点上使用此数据>完成注册>注销>使用行A2和B2中的信息注册新用户>继续，直到电子表格中的行为空.

The idea is Go to the website > click register > Read excel rows A1 and B1 for email and password > use this data on registration site> finish the registration > log out > Register a new user with information from rows A2 and B2 > continue until rows in the spreadsheet are empty.

我设法利用随机的用户信息自动完成了注册过程，现在我只需要对电子表格中的特定电子邮件和密码进行相同的设置即可.

I have managed to automate the registration process with random user information and now I just need to make it do the same with the specific email and password taken from the spreadsheet.

我曾经尝试过使用Apache Poi，但不确定如何使用它以及如何使其自身循环直到电子表格的末尾.

I have tried using Apache Poi, but not exactly sure how to use it and how to make it loop itself until the end of the spreadsheet.

这是我到目前为止所拥有的，但是我认为这是错误的:

This what I have so far but i believe it's wrong:

val myData = new File("/desktop/files.file.xmls")

val fis = new FileInputStream(myData)

val myWorkbook = new HSSFWorkbook(fis)

val mySheet = myWorkbook.getSheetAt(0)

val rowIterator = mySheet.iterator()

while(rowIterator.hasNext){

val row = rowIterator.next()

  val cellIterator = row.cellIterator()

  while(cellIterator.hasNext) {
    val cell = cellIterator.next()
      cell.getCellType match {
        case Cell.CELL_TYPE_STRING => {
          print(cell.getStringCellValue + "\t")
        }
        case Cell.CELL_TYPE_NUMERIC => {
          print(cell.getNumericCellValue + "\t")
        }

        case Cell.CELL_TYPE_BLANK => {
          print("null" + "\t")
        }

      }
  }
  println("")

推荐答案

请注意，我使用的是poi 3.17.所以我的build.sbt有

Just as a note, I'm using poi 3.17. So my build.sbt has

"org.apache.poi" % "poi" % "3.17"
"org.apache.poi" % "poi-ooxml" % "3.17"

在其中.如果您使用的是其他版本，请在问题中注明，然后我将更新我的答案.

in it. If you're using a different version then state so in the question and I'll update my answer.

这是我的示例excel文件:

Here's my example excel file:

首先，导入:

import org.apache.poi.ss.usermodel.{ DataFormatter, WorkbookFactory, Row }
import java.io.File
import collection.JavaConversions._ // lets you iterate over a java iterable

然后，您可以使用WorkbookFactory提取文件并获取工作表:

Then you can pull in your file with the WorkbookFactory and get your sheet:

val f = new File("Example.xlsx")
val workbook = WorkbookFactory.create(f)
val sheet = workbook.getSheetAt(0) // Assuming they're in the first sheet here.

接下来，如果您注意

Next, if you pay attention to the type of Sheet you'll notice that it implements Iterable<Row> which means that you can just use for with it to loop over all the rows:

for (row <- sheet) {
    // Do things
}

前提是您当然不需要从循环中返回任何内容.如果需要这样做，应该可以

provided that you don't need to return anything from the loop of course. If you need to do that, you should be able to do

sheet.map { row => }

接下来，要获取单元格的实际值，您需要格式化程序:

Next, to get the actual value of the cell you'll want a formatter:

val formatter = new DataFormatter()

然后拉出A列，在 row 的索引 0 上调用 getCell :

and then to pull the A column, you call getCell on the row at index 0:

val maybeA = Option(row.getCell(0, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL)) // lift null to None
val maybeB = Option(row.getCell(1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL))

然后，我假设您只有在同时拥有这两个单元格的情况下才想做某事，在这种情况下，您可以利用a来理解这两个单元格:

then I assume you only want to do something if you have both of these cells, in which case you can leverage a for comprehension across these two:

val maybeEmailAndPass = for {
    a <- maybeA
    b <- maybeB
} yield {
    val email = formatter.formatCellValue(a) 
    val pass = formatter.formatCellValue(b)
    (email, pass)
}
println(maybeEmailAndPass)

然后，如果您有任何东西，就可以随心所欲地做.

then if you have something you can do whatever you want with it.

上面在我的例子中给出的结果给了我

And the above ran on my example gives me

Some((Row1 Email,Row1 Pass))
Some((Row2 Email,Row2 Pass))

这篇关于用Scala读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！