java excel数据组装json成数据

java excel数据组装json成数据

客户提供excel数据，androdi app是离线的，所以考虑将数据组装成json数据放到assets目录下面。

首先需要去下载pio的包，文章末尾我会放github的下载地址，代码很简单，pio的类就几个，主要还是自己如果去拼接json数据，自己找下规律，无非就是遍历行和列，先遍历行还是列

package cn.sccl.app.module.firstaid.utils;

import android.content.Context;
import android.content.res.AssetManager;

import com.google.gson.Gson;

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.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import cn.sccl.app.module.firstaid.bean.RowDataBean;
import cn.sccl.xlibrary.utils.XLogUtils;

public class
Test {

    private static List<RowDataBean> mList = new ArrayList<>();

    public static void test(Context context) {
        try {
            AssetManager assetManager = context.getAssets();
            InputStream inputStream = assetManager.open("111.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            //获取表格的第1个sheet
            XSSFSheet sheet = workbook.getSheetAt(0);
            int rowsCount = sheet.getPhysicalNumberOfRows();
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            //遍历行
            for (int r = 0; r < rowsCount; r++) {
                if (r == 4) break;
                if (r == 0) continue;
                RowDataBean rowDataBean = new RowDataBean();
                Row row = sheet.getRow(r);

                rowDataBean.setId(r + "");

                int cellsCount = row.getPhysicalNumberOfCells();
                List<RowDataBean.ColumnDataBean> columnList = new ArrayList<>();
                //遍历列
                for (int c = 0; c < cellsCount; c++) {
                    if (c == 5) break;
                    if (c == 0) continue;
                    String language = getCellAsString(sheet.getRow(0), c, formulaEvaluator);
                    String value = getCellAsString(row, c, formulaEvaluator);

                    String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;

                    RowDataBean.ColumnDataBean columnDataBean = new RowDataBean.ColumnDataBean(c + "", r + "");
                    columnDataBean.setLanguage(language);
                    columnDataBean.setValue(value);
                    columnList.add(columnDataBean);
                    XLogUtils.d(cellInfo);
                }
                rowDataBean.setDataBean(columnList);
                mList.add(rowDataBean);
            }


            //获取表格的第2个sheet
            XSSFSheet sheet2 = workbook.getSheetAt(1);
            int rowsCount2 = sheet2.getPhysicalNumberOfRows();
            //遍历行
            for (int r = 0; r < rowsCount2; r++) {
                if (r == 4) break;
                if (r == 0) continue;
                RowDataBean rowDataBean = mList.get(r-1);
//                RowDataBean rowDataBean = new RowDataBean();
                Row row = sheet2.getRow(r);

//                rowDataBean.setId(r + "");

                int cellsCount = row.getPhysicalNumberOfCells();
                List<RowDataBean.ColumnShortDataBean> columnList = new ArrayList<>();
                //遍历列
                for (int c = 0; c < cellsCount; c++) {
                    if (c == 5) break;
                    if (c == 0) continue;
                    String language = getCellAsString(sheet2.getRow(0), c, formulaEvaluator);
                    String value = getCellAsString(row, c, formulaEvaluator);

                    String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;

                    RowDataBean.ColumnShortDataBean columnDataBean = new RowDataBean.ColumnShortDataBean(c + "", r + "");
                    columnDataBean.setLanguage(language);
                    columnDataBean.setValue(value);
                    columnList.add(columnDataBean);
                    XLogUtils.d(cellInfo);
                }
                rowDataBean.setShortDataBean(columnList);
//                mList.add(rowDataBean);
            }


            XLogUtils.e(new Gson().toJson(mList));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 判断类型并返回值
     *
     * @param row
     * @param c
     * @param formulaEvaluator
     * @return
     */
    protected 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:
            }
        } catch (NullPointerException e) {
            e.printStackTrace();
        }
        return value;
    }
}

遍历代码没有新开线程，数据量大 最好还是新开一个线程吧，只有一个java类，注意好像office要2007以上才可以，以下的好像不兼容。、
我自己用的是office2013

jar包和源代码