文章目录
- 一、src
- 二、须知 + trap
- 1. POI的版本号一定要注意`17`是`十七`而不是`一点七`,我在此处入坑,以为3.8比3.17新。实际上2者差了10年。
- 2. excel有2个类,03之前用HSSF,后缀是xls,03之后用XSSH,后缀是xlsx,长的那个是高的。
- 3.row在进行cell的遍历时,一定要注意区分空串和null。空串的CellType是BLANK,而null直接报NPE,所以这里要判断
- 4. excel的列有很多种类型,Cell对象在get的时候,如果getXxx方法和列的类型不匹配会报错,所以要提前先判断类型。类似于JDBC的操作。
- {1} `如果想获取原始输入,可以直接先把所有cell设为STRING,然后再判断是否为STRING`(BLANK不属于STRING)。但excel不能动态的改,也就是说,如果读取一个NUMERIC的单元格,在代码里修改为STRING,只是在内存中修改了,并且读取的时候会有问题(如果这个NUMERIC类型的数字小数点位数超过excel设定的默认值 (默认2位),比如源是12.122,那么在内存中转成STRING,再以getStringCellValue()读,会输出12.1219999999)。需要先修改类型,然后写入到一个临时的excel,此时这个临时excel的单元格就全是文本类型了,然后再读这个临时excel就可以放心用STRING类型接了。
- {2} 纯数字模样的单元格如果设置为文本类型,excel会在单元格左上角提示一个绿色三角号。
- {3} `全部改为STRING后,原来BLANK的会全部变为null,所以就不能用iterator了,因为iterator会跳过null的cell`
- tip
- 三、XSSF's example
一、src
https://www.cnblogs.com/gdwkong/p/8669220.html
HSSF - Microsoft Excel97-2007
XSSF - Microsoft Excel07之后,格式是xslx,03之前是xls,长的那个是高的
HWPF - Microsoft Word
HSLF - Microsoft PowerPoint
HDGF - Microsoft Visio
二、须知 + trap
1. POI的版本号一定要注意17
是十七
而不是一点七
,我在此处入坑,以为3.8比3.17新。实际上2者差了10年。
2. excel有2个类,03之前用HSSF,后缀是xls,03之后用XSSH,后缀是xlsx,长的那个是高的。
3.row在进行cell的遍历时,一定要注意区分空串和null。空串的CellType是BLANK,而null直接报NPE,所以这里要判断
4. excel的列有很多种类型,Cell对象在get的时候,如果getXxx方法和列的类型不匹配会报错,所以要提前先判断类型。类似于JDBC的操作。
{1} 如果想获取原始输入,可以直接先把所有cell设为STRING,然后再判断是否为STRING
(BLANK不属于STRING)。但excel不能动态的改,也就是说,如果读取一个NUMERIC的单元格,在代码里修改为STRING,只是在内存中修改了,并且读取的时候会有问题(如果这个NUMERIC类型的数字小数点位数超过excel设定的默认值 (默认2位),比如源是12.122,那么在内存中转成STRING,再以getStringCellValue()读,会输出12.1219999999)。需要先修改类型,然后写入到一个临时的excel,此时这个临时excel的单元格就全是文本类型了,然后再读这个临时excel就可以放心用STRING类型接了。
{2} 纯数字模样的单元格如果设置为文本类型,excel会在单元格左上角提示一个绿色三角号。
{3} 全部改为STRING后,原来BLANK的会全部变为null,所以就不能用iterator了,因为iterator会跳过null的cell
比如如下青色框起来的单元格,使用iterator遍历时直接越过
使用代码获取指定位置的cell时,会发现是null
输出:nullTE
**以下代码为将指定excel转成全部为文本的临时excel后再进行读取的操作**
import java.io.{File, FileOutputStream}
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook
object POI {
/*
* 实验是否可以在POI中把excel中的单元格全设为文本,然后再根据string取
* */
def main(args: Array[String]): Unit = {
val excelPath = "C:\\Users\\HASEE\\Desktop\\清单.xlsx"
val excelFile = new File(excelPath)
var excel = new XSSFWorkbook(excelPath)
//全部单元格修改为STRING并写入到一个临时excel
val newPath = toSTRING(excel,excelFile)
//重新给excel赋值,将临时excel赋给excel变量
excel = new XSSFWorkbook(newPath)
val iter = excel.sheetIterator()
while (iter.hasNext) {
val sheet = iter.next()
val rowIter = sheet.rowIterator()
while (rowIter.hasNext) {
val row = rowIter.next()
val cellIter = row.cellIterator()
while (cellIter.hasNext) {
val cellOp = Option(cellIter.next())
if (cellOp.isDefined) {
val cell = cellOp.get
cell.getCellTypeEnum match {
case CellType.NUMERIC => {
println("##" + cell.getNumericCellValue.toString)
cell.setCellType(CellType.STRING)
println(cell.getCellTypeEnum + "%%" + cell.getStringCellValue.toString)
}
case CellType.STRING => println(cell.getRichStringCellValue)
case CellType.BLANK => println("BLANK")
}
} else {
println("@@@")
}
}
}
}
}
}
package utils
import java.io.{File, FileOutputStream}
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook
object ExcelUtils {
/*
* 将指定的excel的单元格全部转为文本并写入到临时文件。临时文件名称为源文件后面加Tmp
* */
def toSTRING(excelFile: File) = {
val excel = new XSSFWorkbook(File)
val iter = excel.sheetIterator()
while (iter.hasNext) {
val sheet = iter.next()
val rowIter = sheet.rowIterator()
while (rowIter.hasNext) {
val row = rowIter.next()
val cellIter = row.cellIterator()
while (cellIter.hasNext) {
val cellOp = Option(cellIter.next())
if (cellOp.isDefined) {
val cell = cellOp.get
cell.setCellType(CellType.STRING)
} else {
println("cell为null")
}
}
}
}
//临时文件的输出流
val path = s"${excelFile.getParent}\\${excelFile.getName.substring(excelFile.getName.indexOf(".") + 1)}-Tmp.xlsx"
val tmpOut = new FileOutputStream(path)
excel.write(tmpOut)
path
}
}
tip
- 遍历cell时,如果用
row.getLastCellNum
作为遍历次数,如果当前row只有前面几列有数据,后面都是null,会导致不再遍历后面的,在生成导入数据时可能会有问题。一般会用第一行的列数作为遍历次数,因为第一行一般就是标题行,肯定是最大值。sheet.getRow(0).getPhysicalNumberOfCells - 亲测,在使用poi提供的迭代器时,会自动把null的cell给忽略,所以有时必须使用foreach
比如 - sheet、row、cell计数都是从0开始。
- 匹配cell类型用CellType,注意cell为null的情况并不包含在这里面
_NONE(-1),只有内部使用,一般用不到。注意,null和空串都不会匹配这个。 NUMERIC(0),数值、日期、分数类型的cell和 STRING(1),文本 FORMULA(2),公式 BLANK(3),空串 BOOLEAN(4),布尔 ERROR(5);???
- 巨坑!!!
sheet、row、cell
都是从0开始计,但获取最后一项时,不同的方法有不同的意义。
excel.getNumberOfSheets
获取的是总的sheet数,因为第一个sheet的索引是0
,所以循环的时候要用until
。但Last系列方法,sheet.getLastRowNum和row.getLastCellNum
,直接获取最后一项的索引值,所以这里即使是从0开始计,要用
to`for (i <- 0
until
excel.getNumberOfSheets)
for(j <- 0to
sheet.getLastRowNum)
for (k <- 0to
row.getLastCellNum)
三、XSSF’s example
example
object Excel2Other {
def main(args: Array[String]): Unit = {
val path = "C:\\Users\\Administrator\\Desktop\\123.xlsx"
val excel = new XSSFWorkbook(new FileInputStream(path))
val iterExcel = excel.iterator()
while (iterExcel.hasNext) {
val sheet = iterExcel.next()
val rowIter = sheet.iterator()
while(rowIter.hasNext) {
val row = rowIter.next()
/*用迭代器会自动把null的单元格给略过,只能用foreach
val cellIter = row.cellIterator()*/
var line = ""
val sheetColumnNum = sheet.getRow(0).getPhysicalNumberOfCells
for (i <- 0 until sheetColumnNum){
val cellOp = Option(row.getCell(i))
if(cellOp.isDefined){
val cell = cellOp.get
val cellType:CellType = cell.getCellTypeEnum
cellType match {
case CellType.NUMERIC => {
/*
poi会自动默认把数值类型的单元格转成java的double,
再转成String时,有时会以科学记数法的形式输出,
所以这里用DecimalFormat把double格式化
*/
val doubleValue = cell.getNumericCellValue
//取整数部分并保留2位小数,且第2位小数由后面的数四舍五入得来。
line += s"${new DecimalFormat("0.00").format(doubleValue)}|"
}
case CellType.STRING => line += s"${cell.getStringCellValue}|"
case CellType.BLANK => line += s"blank|" //空串cell
case CellType._NONE => line += s"none|" // 用于表示在初始化或缺少具体类型之前的状态。仅供内部使用。
}
}else{
line += "|"
}
}
println(line)
/* while (cellIter.hasNext) {
val cell = cellIter.next()
if (cell == None){
println("###")
}
val cellType:CellType = cell.getCellTypeEnum
cellType match {
case CellType.NUMERIC => line += s"${cell.getNumericCellValue}|"
case CellType.STRING => line += s"${cell.getStringCellValue}|"
case CellType._NONE => line += s"123|"
case CellType.BLANK => line += s"blank|"
}
}*/
//line = line.substring(0,line.length - 1)
}
}
}
}