scala日常<1>

前置条件

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

正文

package com.bigdata.scala.sample


import java.io.{File, FileWriter}
import java.time.LocalDate

import org.apache.log4j.Logger
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.{XSSFCell, XSSFRow, XSSFSheet, XSSFWorkbook}

import scala.collection.mutable.ListBuffer

/**
 * 读取excel转成csv文件,每个sheet页生成单独的文件
 *
 * TODO 代办：找个输出流,带zip压缩,一次压缩一个文件.或者直接压缩文件夹
 */
object ExcelReader {
  val logger=Logger.getLogger(getClass.getSimpleName)
  val src="C:\Users\soraka\Desktop\input\excel\xxx数据1130.xlsx"
  val dst_dir="D:\output"+File.separator+LocalDate.now()


  def main(args: Array[String]): Unit = {
     val file = new File(dst_dir)
    if(!file.exists()){
      file.mkdirs()
      logger.error(s"${dst_dir}目录创建成功")
    }

    val workbook = new XSSFWorkbook(src)

//    sheet页码从0开始.
    for (i <- Range(0, workbook.getNumberOfSheets)){

      val list=ListBuffer[String]()
//      遍历每个sheet页
      val sheet: XSSFSheet = workbook.getSheetAt(i)
      val sheetName: String = sheet.getSheetName
      val dst=dst_dir++File.separator+sheetName+".csv"
      val fw = new FileWriter(dst)
//      遍历每行
//      val rowNm: Int = sheet.getLastRowNum
//      getPhysicalNumberOfRows得到的是非空的行数,但还是不准。目前只能对生成的row对象判空处理
      val rowNm: Int = sheet.getPhysicalNumberOfRows
//      val rowNm: Int = sheet.getrow
      logger.error(s"${sheetName}总行数=$rowNm")
      for (n <- Range(0, rowNm)) {
        val row: XSSFRow = sheet.getRow(n)
        val buffer = new StringBuffer()
        if(row !=null){
//        getPhysicalNumberOfCells得到的是非空的列数
//        val columnNm: Int = row.getLastCellNum

          val columnNm: Int = row.getPhysicalNumberOfCells
//          println(s"n=$n")
//          println(s"columnNm=$columnNm")

//                遍历一行的每列
          for (j <- Range(0, columnNm)) {
            val cell: XSSFCell = row.getCell(j)
            var columnValue=""

            if(cell != null){
              if(cell.getCellType.equals(CellType.NUMERIC)){
                // 去掉.0
                columnValue=cell.getNumericCellValue.toInt.toString
              }else{
                columnValue=cell.toString
              }
            }else{
              columnValue=""
            }

            buffer.append(columnValue)
            if(j<columnNm-1){
              buffer.append(",")
            }
          }
          //        do sth...
          //        按行写出
          fw.write(buffer.toString)
          if(n<rowNm-1){
            fw.write("\r\n")
          }
        }
      }
      fw.close()
      logger.error(s"${dst}文件已生成")


//      close资源时如果打开了excel文件会抛异常,没人打开excel此程序也会等待一会才关闭。目前注释掉
//      workbook.close()
    }
    }
}
```
```