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

However, since Android is implemented natively in Java/Kotlin, you can also refer to the server’s method of parsing Excel.

First of all, JXL, the popular framework for parsing Office documents in the past, but the current official version does not parse XLSX on mobile.

POI is now the mainstream framework for processing Office documents, which can be imported or generated. The disadvantages are: the official dependency package is relatively large, and the SDK required by the latest version of the official Android project requires minSDK 24 or above.

Finally found a relatively light and simple solution is through a foreign developer to simplify the POI package after the library android5xlsx, retains the function of parsing XLS and XLSX in Android5 above (the developer teased that parsing Excel in Android5 below is really a bit tricky)

android5xlsxThe making of address

Here are some steps to simply use the library in my project (non-source analysis) :

Using the step

I. Lifting the limit of 65 K method:

android {
    compileSdkVersion 29
    buildToolsVersion "29.0.2"
    defaultConfig {
		.....
        versionName "1.0"
        multiDexEnabled true  //trueEnable multiple dex to remove the 65K limittestInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"}}Copy the code

Import the two JAR packages of the core of Android5xLSX into the project lib folder

The simple operation of parsing the contents of an Excel file is encapsulated in a utility class ExcelUtils:

Excel parses the utility 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 tools *@author: ODM
 * @date: 2020/4/11 * /
public class ExcelUtils {

    /** * Read the Excel file *@param file
     * @throws FileNotFoundException
     */
    public static void readExcel(File file) throws FileNotFoundException {
        if(file == null) {
            Log.e("NullFile"."Error reading Excel, 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 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 if the file is Excel * based on the type suffix@paramThe file file *@returnWhether Excel file */
    public static boolean checkIfExcelFile(File file){
        if(file == null) {
            return false;
        }
        String name = file.getName();
        "Requires an escape character
        String[] list = name.split("\ \.");
        // If the number of elements is less than 2, the type name cannot be obtained
        if(list.length < 2) {
            return false;
        }
        String  typeName = list[list.length - 1];
        // Only XLS or XLSX can be used
        return "xls".equals(typeName) || "xlsx".equals(typeName); }}Copy the code

Three, simple analysis of an Excel file presentation

Open the file manager on the page, select the local Excel file of the mobile phone, and use ExcelUtils to print out the content of the Excel file:

By the way, reading Excel also requires the corresponding read and write file permissions

class MemberFragment : BaseMVVMFragment() {

    // Open the system's own file selector
    private fun openFileSelector(a) {
        val intent = Intent(Intent.ACTION_GET_CONTENT)
        intent.addCategory(Intent.CATEGORY_OPENABLE)
        intent.type = "* / *"
// intent.type = "application/vnd.ms-excel application/x-excel
        this.startActivityForResult(intent, 1)}override fun onActivityResult(
        requestCode: Int,
        resultCode: Int.data: Intent?). {
        super.onActivityResult(requestCode, resultCode, data)
        if (data= =null) {
            // The user does not select any file and returns directly
            return
        }
        val uri: Uri? = data.data // Get the URI of the user selection fileuri? .let {val file = UriUtils.uri2File(it)
            if(ExcelUtils.checkIfExcelFile(file)){
                ExcelUtils.readExcel(file) // Read the contents of Excel file}}}}Copy the code

In the local file manager, select any Excel file, in this case select test2. XLSX file, the following is the excel file contents

Analysis result: log print result is displayed

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

If you need it, you can transform the parsing tool class according to your needs. You can convert the parsing result into the entity class object you need, or write it into Excel. For more specific and diverse operations, please refer to the demo given by the developer.

conclusion

I think this is in the Android end parsing Excel XLS XLSX content scheme, the use of relatively simple and lightweight quite good scheme.

The code in my article is relatively simple, only to provide you with a way to achieve this function ~

Thank you very much for reading, welcome to exchange and discussion ~