用 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.
接下来如果注意表格你会注意到它实现了 Iterable 这意味着你可以使用 for 和它来循环所有的行:
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 列，您可以在索引 0 处的 row 上调用 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屋！
