Android EXCEL parses xls and xlsx in a simple way

Preface

Excel parsing is generally done on the server side, but if the mobile side is to implement the function of parsing Excel, there is a way to do it.

However, since Android is natively implemented in Java/Kotlin, you can also refer to how the server side parses Excel.

First of all, jxl, which was a popular framework for parsing office documents in the past, is currently an official version that cannot parse xlsx on the mobile side.

Then POI, which is the mainstream framework for handling office documents today, can be imported or generated. The disadvantage is that the official dependent packages are large, and the latest version of the official sdk required for android projects requires minSDK 24 or more.

Finally, a lighter and simpler solution was found: the simplified library android5 xlsx for POI packages by a foreign developer, which retains the ability to parse xls and xlsx above Android5 (the developer himself spits a slot below android5 to parse Excel is a bit around)

github address of android5xlsx

Here are some simple steps to use this library in my project (non-source analysis explanation, please understand): (Android 10 environment testing works)

Use steps

1. Remove the restrictions on the 65K method:

android {
    compileSdkVersion 29
    buildToolsVersion "29.0.2"
    defaultConfig {
		.....
        versionName "1.0"
        multiDexEnabled true  //true turns on multi-dex to remove the 65k limit
        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

}

2. Import two jar packages from the core of android5xlsx into the project lib folder

Encapsulates a simple operation to parse the contents of an Excel file in a tool class, ExcelUtils:

Excel Parsing Tool Class Code

import android.util.Log;
import com.blankj.utilcode.util.LogUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

/**
 * @description: Excel Tool class
 * @author: ODM
 * @date: 2020/4/11
 */
public class ExcelUtils {

    /**
     * Read Excel File
     * @param file
     * @throws FileNotFoundException
     */
    public static void readExcel(File file) throws FileNotFoundException {
        if(file == null) {
            Log.e("NullFile","read Excel Error, file is empty");
            return;
        }
        InputStream stream = new FileInputStream(file);
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int rowsCount = sheet.getPhysicalNumberOfRows();
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (int r = 0; r<rowsCount; r++) {
                Row row = sheet.getRow(r);
                int cellsCount = row.getPhysicalNumberOfCells();
                //Read one line at a time
                for (int c = 0; c<cellsCount; c++) {
                    //Convert the contents of each grid to a string
                    String value = getCellAsString(row, c, formulaEvaluator);
                    String cellInfo = "r:"+r+"; c:"+c+"; v:"+value;
                    LogUtils.d(cellInfo);
                }
            }
        } catch (Exception e) {
            /* proper exception handling to be here */
            LogUtils.e(e.toString());
        }

    }

    /**
     * Read the contents of each line in the excel file
     * @param row
     * @param c
     * @param formulaEvaluator
     * @return
     */
    private static String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) {
        String value = "";
        try {
            Cell cell = row.getCell(c);
            CellValue cellValue = formulaEvaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = ""+cellValue.getBooleanValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double numericValue = cellValue.getNumberValue();
                    if(HSSFDateUtil.isCellDateFormatted(cell)) {
                        double date = cellValue.getNumberValue();
                        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yy");
                        value = formatter.format(HSSFDateUtil.getJavaDate(date));
                    } else {
                        value = ""+numericValue;
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = ""+cellValue.getStringValue();
                    break;
                default:
                    break;
            }
        } catch (NullPointerException e) {
            /* proper error handling should be here */
            LogUtils.e(e.toString());
        }
        return value;
    }

    /**
     * Simply determine whether an Excel file is an Excel file based on the type suffix name
     * @param file file
     * @return Is Excel File
     */
    public static boolean checkIfExcelFile(File file){
        if(file == null) {
            return false;
        }
        String name = file.getName();
        //"."Escape characters are required
        String[] list = name.split("\\.");
        //Less than two elements after partitioning indicate that the type name cannot be obtained
        if(list.length < 2) {
            return false;
        }
        String  typeName = list[list.length - 1];
        //Satisfies xls or xlsx to be able to
        return "xls".equals(typeName) || "xlsx".equals(typeName);
    }
}

3. Simple parsing of an Excel file demonstration

Open the file manager on the page, select the local excel file of your mobile phone, and print out the contents of the excel file using ExcelUtils:

By the way, reading Excel also requires permissions to read and write files.

class MemberFragment : BaseMVVMFragment() {

    // Open the file selector that comes with the system
    private fun openFileSelector() {
        val intent = Intent(Intent.ACTION_GET_CONTENT)
        intent.addCategory(Intent.CATEGORY_OPENABLE)
        intent.type = "*/*"
//        intent.type = "application/vnd.ms-excel application/x-excel" unknown invalid reason
        this.startActivityForResult(intent, 1)
    }

    override fun onActivityResult(
        requestCode: Int,
        resultCode: Int,  data: Intent?
    ) {
        super.onActivityResult(requestCode, resultCode, data)
        if (data == null) {
            // User did not select any files and returned directly
            return
        }
        val uri: Uri? = data.data // Get URI of user selection file
        uri?.let {
            val file = UriUtils.uri2File(it)
            if(ExcelUtils.checkIfExcelFile(file)){
                ExcelUtils.readExcel(file) //Read Excel file content
            }
        }
    }

}

In the local file manager, choose any excel file, here is the choice to read the test2.xlsx file, the following is the contents of the excel file

Parse results: print results in log display

You can see that you can read each line from left to right, all the way down.

Students who need it can transform the parsing tool class to convert the parsing result to the desired entity class object or write to Excel. More specific and varied operations can be referred to as the developer's advice demo Come on.

summary

I think this is a very simple and lightweight solution for parsing Excel's xls xlsx content on the Android side.

The code in my article is simple and simple, only to provide a way of thinking to achieve this function for your classmates~

Thank you very much for your reading. Welcome to exchange and discuss ~

Tags: Android Excel Apache Java

Posted on Tue, 05 May 2020 13:45:39 -0400 by rcoursey