Scala 表格处理工具

工作业务需要处理表格，写了一个表格工具类

处理 txt , xls ，xlsx , csv 

package utils

import com.github.tototoshi.csv.{CSVReader, CSVWriter}

import java.io.{File, FileInputStream, FileOutputStream}
import org.apache.commons.io.FileUtils
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import java.text.DecimalFormat
import scala.collection.mutable
import scala.collection.mutable.ListBuffer
import scala.language.implicitConversions

/**
 * 需要导入的包
 * "org.apache.poi" % "poi" % "3.5-FINAL",
 * "com.github.tototoshi" %% "scala-csv" % "1.3.10"
 *
 * @author btown
 */
class TableFile(data: List[List[String]]) {


  /**
   * @param row 行号（start 1）
   * @param col 列号（start 1）
   * @return 具体坐标的元素
   */
  def apply(row: Int, col: Int): String = data(row - 1)(col - 1)

  /**
   * @return 总行数
   */
  def rowNumber: Int = data.length

  /**
   * @return 总列数
   */
  def colNumber: Int = data.head.length

  /**
   * @return 首行元素集合
   */
  def rowHead: List[String] = data.head

  /**
   * @param rowNum 行号（start 1）
   * @return 具体一行的所有元素集合
   */
  def row(rowNum: Int): List[String] = data(rowNum - 1)

  /**
   * @param rowNums 行号（start 1）
   * @return 输入列的所有元素集合
   */
  def rowsTableFile(rowNums: Int*): TableFile = {
    val resData = ListBuffer[List[String]]()
    for (rowNum <- rowNums) {
      resData.append(row(rowNum))
    }
    TableFile(resData.toList)
  }

  /**
   * @return 首列元素集合
   */
  def colHead: List[String] = col(1)

  /**
   * @param colNum 列号（start 1）
   * @return 具体一列的所有元素集合
   */
  def col(colNum: Int): List[String] = {
    val col = ListBuffer[String]()
    for (rowNum <- data.indices) {
      col.append(data(rowNum)(colNum - 1))
    }
    col.toList
  }

  /**
   * @param colNums 需要获取的列号（start 1）
   * @return 输入列的所有元素集合
   */
  def colsTableFile(colNums: Int*): TableFile = {
    val resData = ListBuffer[List[String]]()
    for (rowNum <- data.indices) {
      val rowData = ListBuffer[String]()
      for (colNum <- colNums.sorted) {
        rowData.append(data(rowNum)(colNum - 1))
      }
      resData.append(rowData.toList)
    }
    TableFile(resData.toList)
  }

  /**
   * @return 获取一个对象数组的实例（标题用标号确定顺序）
   */
  def toTableObj: List[Map[String, String]] = {
    val numberFormat = new DecimalFormat("000")
    val res = ListBuffer[Map[String, String]]()
    for (rowNum <- 2 to rowNumber) {
      val rowData = mutable.Map[String, String]()
      for (colNum <- rowHead.indices) {
        rowData.put(numberFormat.format(colNum) + "$" + rowHead(colNum), data(rowNum - 1)(colNum))
      }
      res.append(rowData.toMap)
    }
    res.toList
  }

  /**
   * 将表格以 \t 分割的文本格式写入文件
   *
   * @param filePath 文件路径
   */
  def writeTxt(filePath: String): Unit = FileUtils.writeStringToFile(new File(filePath), toString, "UTF-8")

  /**
   * 将表格以标准xls格式写入文件
   *
   * @param filePath  文件路径
   * @param sheetName 工作簿名可忽略
   */
  def writeXls(filePath: String, sheetName: String = "sheet 1"): Unit = writeExcel(filePath, sheetName, new HSSFWorkbook)

  /**
   * 将表格以标准xlsx格式写入文件
   *
   * @param filePath  文件路径
   * @param sheetName 工作簿名可忽略
   */
  def writeXlsx(filePath: String, sheetName: String = "sheet 1"): Unit = writeExcel(filePath, sheetName, new XSSFWorkbook)

  private def writeExcel(filePath: String, sheetName: String, workbook: Workbook): Unit = {
    val sheet = workbook.createSheet(sheetName)
    for (i <- data.indices) {
      val row = sheet.createRow(i)
      for (j <- data(i).indices) {
        row.createCell(j).setCellValue(data(i)(j))
      }
    }
    val fileStream = new FileOutputStream(filePath)
    workbook.write(fileStream)
    fileStream.flush()
    fileStream.close()
  }

  /**
   * 将表格以逗号分割的csv格式分割的格式写入文件
   *
   * @param filePath 文件路径
   */
  def writeCsv(filePath: String): Unit = {
    val writer = CSVWriter.open(new File(filePath))
    data.foreach(row => {
      writer.writeRow(row)
    })
    writer.close()
  }

  /**
   * 按照文件后缀名写入文件
   *
   * @param filePath 文件路径
   */
  def writeFileAsSuffix(filePath: String): Unit = {
    filePath.substring(filePath.lastIndexOf(".") + 1) match {
      case "txt" =>
        writeTxt(filePath)
      case "xls" =>
        writeXls(filePath)
      case "xlsx" =>
        writeXlsx(filePath)
      case "csv" =>
        writeCsv(filePath)
    }
  }

  /**
   * @return 转化为文革格式字符串
   */
  override def toString: String = {
    val str = new mutable.StringBuilder()
    for (rowNum <- data.indices) {
      if (rowNum != 0 || rowNum == data.length - 1) {
        str.append("\n")
      }
      for (colNum <- data(rowNum).indices) {
        str.append(data(rowNum)(colNum))
        if (colNum != data(rowNum).length - 1) {
          str.append("\t")
        }
      }
    }
    str.toString()
  }

}

object TableFile {

  implicit class TableFileConvert(file: File) {
    /**
     * 转化文本格式的表格
     */
    def txt2TableFile: TableFile = TableFile.apply(file, "txt")

    /**
     * 转化标准xls表格
     */
    def xls2TableFile: TableFile = TableFile.apply(file, "xls")

    /**
     * 转化标准xlsx表格
     */
    def xlsx2TableFile: TableFile = TableFile.apply(file, "xlsx")

    /**
     * 转化标准csv表格
     */
    def csv2TableFile: TableFile = TableFile.apply(file, "csv")
  }

  /**
   * 构建一个TableFile对象实例
   *
   * @param file      原始文件
   * @param tableType 表格格式
   *                  txt   \t 分割的文本格式
   *                  csv   , 分割的标准csv格式
   *                  xls   标准xls格式
   *                  xlsx  标准xlsx格式
   */
  def apply(file: File, tableType: String): TableFile = {
    var data: List[List[String]] = null
    tableType match {
      case "xls" =>
        data = readExcel(new HSSFWorkbook(new FileInputStream(file)))
      case "xlsx" =>
        data = readExcel(new XSSFWorkbook(new FileInputStream(file)))
      case "csv" =>
        val reader = CSVReader.open(file)
        data = reader.all()
        reader.close()
      case _ =>
        val tempData: ListBuffer[List[String]] = ListBuffer[List[String]]()
        val lines = FileUtils.readLines(file, "UTF-8")
        val rowNumber = lines.size()
        for (row <- 0 until lines.size()) {
          tempData.append(lines.get(row).split("\t").toList)
        }
        data = tempData.toList
    }
    TableFile(data)
  }

  def apply(data: List[List[String]]): TableFile = {
    val dataInit = data.toBuffer
    for (i <- data.indices.reverse) {
      if (data(i).length != data.head.length) {
        dataInit.remove(i)
      }
    }
    new TableFile(dataInit.toList)
  }

  /**
   * 读取excel表格内容
   *
   * @param sheetIndex 工作簿下标（start 0）
   */
  private def readExcel(workbook: Workbook, sheetIndex: Int = 0): List[List[String]] = {
    val sheet = workbook.getSheetAt(sheetIndex)
    val rowNumber = sheet.getPhysicalNumberOfRows
    val cells = sheet.getRow(0).getPhysicalNumberOfCells
    val tempData = ListBuffer[List[String]]()
    for (r <- 0 until rowNumber) {
      val row = sheet.getRow(r)
      if (row != null) {
        val rowData = ListBuffer[String]()
        for (c <- 0 until cells) {
          rowData.append(row.getCell(c).toString)
        }
        tempData.append(rowData.toList)
      }
    }
    tempData.toList
  }
}

使用就很简单了

    var file: TableFile = new File("C:\\test\\111.txt").txt2TableFile
    
    file = new File("C:\\test\\111.xls").xls2TableFile

    file = new File("C:\\test\\1111.xlsx").xlsx2TableFile

    file = new File("C:\\test\\111.csv").csv2TableFile

也可以转换为Java用，但是没有隐式转换创建就稍微要麻烦一点