【Android】安卓通过Apache POI读写Excel

前言

Apache POI是Java领域最完善的Office文件读写库

但是由于JDK和AndroidSDK在源码上存在差异，所以原版的Apache POI库，并不能直接在安卓上使用

这里我们用的是修改和精简过后，适合安卓的版本

准备

两个Jar包，poi-android.jar和poi-ooxml-schemas.jar

核心代码

	//读表格
	InputStream is = Resources.readAssetStream("abc.xlsx");
	XSSFWorkbook workbook = Excel.createWorkbookFromTemplate(is);
	XSSFSheet sheet = Excel.getSheet(workbook, 0);
	int v1 = Excel.getCellInt(sheet, 0, 0);
	int v2 = Excel.getCellInt(sheet, 0, 1);
	int v3 = Excel.getCellInt(sheet, 0, 2);
	Console.info("POI", "Read Excel", v1, v2, v3);
	//写表格
	Excel.setCellString(sheet, 0, 0, "2");
	Excel.setCellString(sheet, 0, 1, "2");
	Excel.setCellString(sheet, 0, 2, "2");
	Console.info("POI", "Write Excel");
	//保存表格
	String file = AndroidFile.getAndroidExternalFile("abc.xlsx");
	Excel.write(workbook, file);
	Console.info("POI", "Save Excel");
	//成功
	TipBox.tipInCenter("Demo Finish");

	package com.android.commons.library.poi;
	
	import com.easing.commons.android.helper.exception.BizException;
	import com.easing.commons.android.time.Times;
	
	import java.io.FileInputStream;
	import java.io.FileOutputStream;
	import java.io.InputStream;
	import java.io.OutputStream;
	import java.time.LocalDateTime;
	import java.util.Date;
	
	import lombok.SneakyThrows;
	
	import org.apache.poi.ss.usermodel.*;
	import org.apache.poi.ss.util.CellRangeAddress;
	import org.apache.poi.xssf.usermodel.*;
	
	@SuppressWarnings("all")
	public class Excel {
	
	    //=======================================  Workbook  =======================================
	
	    //创建一个空的Workbook对象
	    @SneakyThrows
	    public static XSSFWorkbook createWorkbook() {
	        return new XSSFWorkbook();
	    }
	
	    //以指定输入流为模版，创建一个Workbook对象
	    @SneakyThrows
	    public static XSSFWorkbook createWorkbookFromTemplate(InputStream is) {
	        return new XSSFWorkbook(is);
	    }
	
	    //以指定文件为模版，创建一个Workbook对象
	    @SneakyThrows
	    public static XSSFWorkbook createWorkbookFromTemplate(String file) {
	        InputStream is = new FileInputStream(file);
	        return Excel.createWorkbookFromTemplate(is);
	    }
	
	    //将Workbook写出到指定输出流
	    @SneakyThrows
	    public static void write(XSSFWorkbook xbook, OutputStream os) {
	        xbook.write(os);
	        os.flush();
	        os.close();
	    }
	
	    //将Workbook写出到指定文件
	    @SneakyThrows
	    public static void write(XSSFWorkbook xbook, String file) {
	        OutputStream os = new FileOutputStream(file);
	        Excel.write(xbook, os);
	    }
	
	    //关闭Workbook
	    @SneakyThrows
	    public static void closeWorkbook(XSSFWorkbook xbook) {
	        xbook.close();
	    }
	
	    //=======================================  Sheet  =======================================
	
	    //创建一个空的Sheet对象
	    @SneakyThrows
	    public static XSSFSheet createSheet(XSSFWorkbook workbook) {
	        return workbook.createSheet();
	    }
	
	    //创建一个空的Sheet对象
	    @SneakyThrows
	    public static XSSFSheet createSheet(XSSFWorkbook workbook, String sheetName) {
	        return workbook.createSheet(sheetName);
	    }
	
	    //获取指定位置的Sheet对象
	    @SneakyThrows
	    public static XSSFSheet getSheet(XSSFWorkbook workbook, int index) {
	        return workbook.getSheetAt(index);
	    }
	
	    //获取指定名称的Sheet对象
	    @SneakyThrows
	    public static XSSFSheet getSheet(XSSFWorkbook workbook, String sheetName) {
	        return workbook.getSheet(sheetName);
	    }
	
	    //复制一个Sheet到Workbook
	    public static XSSFSheet cloneSheet(XSSFWorkbook xbook, String name, String newName) {
	        XSSFSheet sheet = xbook.cloneSheet(xbook.getSheetIndex(name));
	        if (newName != null && !newName.equals(""))
	            xbook.setSheetName(xbook.getSheetIndex(sheet), newName);
	        return sheet;
	    }
	
	    //移除指定位置的Sheet
	    public static void removeSheet(XSSFWorkbook xbook, int index) {
	        if (xbook.getNumberOfSheets() != 0)
	            xbook.removeSheetAt(index);
	    }
	
	    //移除指定名称的Sheet
	    public static void removeSheet(XSSFWorkbook xbook, String name) {
	        if (xbook.getNumberOfSheets() != 0)
	            xbook.removeSheetAt(xbook.getSheetIndex(name));
	    }
	
	    //=======================================  XSSFCell  =======================================
	
	    //获取一个Cell对象
	    public static XSSFCell getCell(XSSFSheet sheet, int row, int col) {
	        if (sheet.getRow(row) == null)
	            sheet.createRow(row);
	        if (sheet.getRow(row).getCell(col) == null)
	            sheet.getRow(row).createCell(col, Cell.CELL_TYPE_BLANK);
	        return sheet.getRow(row).getCell(col);
	    }
	
	    //获取Cell格式类型
	    //枚举值参考Cell.CELL_TYPE_XXX常量
	    public static int getCellType(XSSFCell cell) {
	        return cell.getCellType();
	    }
	
	    //获取文本单元格的值
	    public static String getCellString(XSSFCell cell) {
	        int cellType = cell.getCellType();
	        if (cellType != Cell.CELL_TYPE_BLANK && cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC)
	            throw BizException.of("unsupported cell type, a string type cell is needed");
	        cell.setCellType(Cell.CELL_TYPE_STRING);
	        return cell.getStringCellValue().trim();
	    }
	
	    //获取文本单元格的值，并转为整数
	    public static int getCellInt(XSSFCell cell) {
	        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
	            return (int) cell.getNumericCellValue();
	        String value = getCellString(cell);
	        return Integer.valueOf(value);
	    }
	
	    //获取文本单元格的值，并转为浮点数
	    public static double getCellDouble(XSSFCell cell) {
	        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
	            return cell.getNumericCellValue();
	        String value = getCellString(cell);
	        return Double.valueOf(value);
	    }
	
	    //获取文本单元格的值，并转为布尔型
	    public static boolean getCellBool(XSSFCell cell, boolean default_value) {
	        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
	            return cell.getBooleanCellValue();
	        String value = getCellString(cell);
	        if (value.equals("是") || value.equalsIgnoreCase("TRUE")) return true;
	        if (value.equals("否") || value.equalsIgnoreCase("FALSE")) return false;
	        return default_value;
	    }
	
	    //获取文本单元格的值，并转为Date
	    public static Date getCellDate(XSSFCell cell) {
	        if (isDateCell(cell))
	            return DateUtil.getJavaDate(getCellDouble(cell));
	        String value = getCellString(cell);
	        Date date = Times.parseDate(value);
	        return date;
	    }
	
	    //获取文本单元格的值，并转为LocalDateTime
	    public static LocalDateTime getCellDateTime(XSSFCell cell) {
	        Date date = getCellDate(cell);
	        LocalDateTime dateTime = Times.getDateTime(date);
	        return dateTime;
	    }
	
	    //获取文本单元格的值
	    public static String getCellString(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellString(cell);
	    }
	
	    //获取文本单元格的值，并转为整数
	    public static int getCellInt(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellInt(cell);
	    }
	
	    //获取文本单元格的值，并转为浮点数
	    public static double getCellDouble(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellDouble(cell);
	    }
	
	    //获取文本单元格的值，并转为布尔型
	    public static boolean getCellBool(XSSFSheet sheet, int row, int col, boolean default_value) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellBool(cell, default_value);
	    }
	
	    //获取单元格中的Date
	    public static Date getCellDate(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellDate(cell);
	    }
	
	    //获取单元格中的Date
	    public static LocalDateTime getCellDateTime(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return getCellDateTime(cell);
	    }
	
	    //设置文本单元格的值
	    public static void setCellString(XSSFCell cell, Object text) {
	        cell.setCellType(Cell.CELL_TYPE_STRING);
	        cell.setCellValue(new XSSFRichTextString(text.toString()));
	    }
	
	    //设置文本单元格的值
	    public static void setCellString(XSSFSheet sheet, int row, int col, Object text) {
	        XSSFCell cell = getCell(sheet, row, col);
	        setCellString(cell, text == null ? "" : text.toString());
	    }
	
	    //判断单元格内容是否为空
	    public static boolean isEmptyCell(XSSFCell cell) {
	        return getCellString(cell).equals("");
	    }
	
	    //判断单元格内容是否为空
	    public static boolean isEmptyCell(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return isEmptyCell(cell);
	    }
	
	    //判断单元格是否是日期格式
	    public static boolean isDateCell(XSSFSheet sheet, int row, int col) {
	        XSSFCell cell = getCell(sheet, row, col);
	        return isDateCell(cell);
	    }
	
	    //判断单元格是否是日期格式
	    public static boolean isDateCell(XSSFCell cell) {
	        return DateUtil.isCellDateFormatted(cell);
	    }
	
	    //=======================================  Merge  =======================================
	
	    //获取合并单元格所在的区域
	    public static CellRangeAddress getMergedCellAddress(XSSFSheet sheet, XSSFCell cell) {
	        for (int mergeNums = sheet.getNumMergedRegions(), i = 0; i = merger.getFirstRow() && cell.getRowIndex() = merger.getFirstColumn() && cell.getColumnIndex()