Android：生成Excel表格并保存到本地

提醒
本文实例是使用Kotlin进行开发演示的。

一、技术方案

• org.apache.poi:poi
• org.apache.poi:poi-ooxml

二、添加依赖

[versions]

poi = "5.2.3"
log4j = "2.24.2"

[libraries]

#https://mvnrepository.com/artifact/org.apache.poi/poi
apache-poi = { module = "org.apache.poi:poi", version.ref = "poi" }
apache-poi-ooxml = { module = "org.apache.poi:poi-ooxml", version.ref = "poi" }
# https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core 上面的 apache-poi 需要添加log4j-core
log4j-core = { module = "org.apache.logging.log4j:log4j-core", version.ref = "log4j" }

implementation(libs.apache.poi)
implementation(libs.apache.poi.ooxml)
implementation(libs.log4j.core)

三、效果图

四、示例代码

TestPoi.kt

package com.example.test.test

import org.apache.poi.ss.usermodel.HorizontalAlignment
import org.apache.poi.ss.usermodel.VerticalAlignment
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.File
import java.io.FileOutputStream

data class Fruit(
    val id: Long,
    var name: String,
    var price: String,
    var desc: String?,
    var count: Int = 0,
    var tip: String? = null
)


fun getProductList(): List<Fruit> {
    val productList: MutableList<Fruit> = mutableListOf()
    productList.add(Fruit(1001, "蓝莓", "40.00", "新鲜水果", 10))
    productList.add(Fruit(1002, "葡萄", "15.00", "新鲜水果", 20))
    productList.add(Fruit(1003, "苹果", "12.00", "新鲜水果", 30))
    productList.add(Fruit(1004, "香蕉", "8.00", "新鲜水果", 15))
    productList.add(Fruit(1005, "西瓜", "4.00", "新鲜水果", 6))
    productList.add(Fruit(1006, "橙子", "5.00", "新鲜水果", 9))
    productList.add(Fruit(1007, "柚子", "5.00", "新鲜水果", 5))
    productList.add(Fruit(1008, "火龙果", "9.00", "新鲜水果", 11))
    productList.add(Fruit(1009, "猕猴桃", "10.00", "新鲜水果", 12))
    productList.add(Fruit(1010, "哈密瓜", "7.00", "新鲜水果", 6))
    productList.add(Fruit(1011, "皇冠梨", "5.00", "新鲜水果", 8))
    return productList
}

fun createExcelSheet() {
    // 创建一个新的工作簿
    val workbook = XSSFWorkbook()
    // 创建一个工作表(sheet)
    val sheet = workbook.createSheet("水果清单")

    //创建XSSFFont对象
    val headXSSFFont = workbook.createFont()

    //设置字体样式，如字体名称、字体大小、加粗等, 下面设置了字体名称为Arial、字体大小为12、加粗
    headXSSFFont.fontName = "Arial"
    headXSSFFont.fontHeightInPoints = 14
    headXSSFFont.bold = true

    // 创建一个XSSFCellStyle对象来表示要设置的样式
    val headXSSFCellStyle = workbook.createCellStyle()
    // 将字体样式设置到XSSFCellStyle对象中
    headXSSFCellStyle.setFont(headXSSFFont)


    // 居中对齐
    headXSSFCellStyle.alignment = HorizontalAlignment.CENTER
    headXSSFCellStyle.verticalAlignment = VerticalAlignment.CENTER

    // 创建行（0基索引）
    var xssFRow = sheet.createRow(0)
    //设置行高
    xssFRow.heightInPoints = 40F
//    xssFRow.height = 600
    //设置样式
//    xssFRow.rowStyle = headXSSFCellStyle
    val excleHead = arrayOf("编号", "水果名", "价格（斤）", "库存（箱）", "描述", "备注")
    for ((index, item) in excleHead.withIndex()) {
        var width = 20 * 256
        if (index == 2 || index == 3) {
            width = 15 * 256
        } else if (index == 4 || index == 5) {
            width = width shl 1
        }
        // 设置列宽
        sheet.setColumnWidth(index, width)
        val xSSFCell = xssFRow.createCell(index)
        //设置样式
        xSSFCell.cellStyle = headXSSFCellStyle
        xSSFCell.setCellValue(item)
    }

    val deviceInfoList = getProductList()

    val xSSFFont = workbook.createFont()
    xSSFFont.fontHeightInPoints = 12
    val xSSFCellStyle = workbook.createCellStyle()
    xSSFCellStyle.setFont(xSSFFont)
    xSSFCellStyle.alignment = HorizontalAlignment.CENTER
    xSSFCellStyle.verticalAlignment = VerticalAlignment.CENTER

    for ((index, item) in deviceInfoList.withIndex()) {
        xssFRow = sheet.createRow(index + 1)
        xssFRow.heightInPoints = 40F
//        xssFRow.height = 600
        val cXSSFCell0 = xssFRow.createCell(0)
        cXSSFCell0.setCellValue(item.id.toString())
        cXSSFCell0.cellStyle = xSSFCellStyle

        val cXSSFCell1 = xssFRow.createCell(1)
        cXSSFCell1.setCellValue(item.name)
        cXSSFCell1.cellStyle = xSSFCellStyle

        val cXSSFCell2 = xssFRow.createCell(2)
        cXSSFCell2.setCellValue(item.price)
        cXSSFCell2.cellStyle = xSSFCellStyle

        val cXSSFCell3 = xssFRow.createCell(3)
        cXSSFCell3.setCellValue(item.count.toString())
        cXSSFCell3.cellStyle = xSSFCellStyle

        val cXSSFCell4 = xssFRow.createCell(4)
        cXSSFCell4.setCellValue(item.desc.toString())
        cXSSFCell4.cellStyle = xSSFCellStyle
    }

    try {
        val fileOutputStream = FileOutputStream("水果清单.xlsx")
//        val fileOutputStream = FileOutputStream(File("D:\\水果清单.xlsx"))// Windows磁盘: D盘
//        val fileOutputStream = FileOutputStream(File("/Users/chinadragon/Desktop/水果清单.xlsx"))// mac 文件地址
        workbook.write(fileOutputStream)
        fileOutputStream.close()
        workbook.close()
        println("成功创建水果清单表格")
    } catch (e: Exception) {
        println("生成水果清单表格发生异常 $e")

    }
}

fun main() {
    createExcelSheet()
}