android实现Excel的读写

android（poi.jar 和 jxl.jar）实现Excel的读取：

    jxl参考：https://blog.csdn.net/qq_36982160/article/details/82421940
    poi参考：https://blog.csdn.net/shangming150/article/details/78261095

 

ExcelUtil.java工具类：

/**
 * FileName: ExcelUtil
 * Author: bai
 * Date: 2019/4/15 9:14
 */
public class ExcelUtil {
    private static WritableFont arial14font = null;
    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";

    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            //对齐格式
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //设置边框
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化Excel
     *
     * @param fileName 导出excel存放的地址（目录）
     * @param colName excel中包含的列名（可以有多个）
     */
    public static void initExcel(String fileName,String sheetName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            //创建标题栏
            sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            //设置行高
            sheet.setRowView(0, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);
                WritableSheet sheet = writebook.getSheet(0);

                for (int j = 0; j < objList.size(); j++) {
                    ProjectBean projectBean = (ProjectBean) objList.get(j);
                    List<String> list = new ArrayList<>();
                    list.add(projectBean.getName());
                    list.add(projectBean.getProject());
                    list.add(projectBean.getYear() );

                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                        if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }
                    }
                    //设置行高
                    sheet.setRowView(j + 1, 350);
                }

                writebook.write();
                Toast.makeText(c, "导出Excel成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }

    /**
     * 读取EXCEL内容
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List getXlsData(String filePath) throws Exception {
        List<Map<String,Object>> list = new ArrayList<>();
        Map<String,Object> mapResult = new HashMap<>();//根据具体的生成对应的对象文件

        File file = new File(filePath);
        if (!file.exists()) {
           return null;
        }

        // 创建输入流
        InputStream stream = new FileInputStream(filePath);
        // 获取Excel文件对象
        Workbook rwb = Workbook.getWorkbook(stream);
        // 获取文件的指定工作表 默认的第一个
        rwb.getSheets();
        Sheet sheet = rwb.getSheet(0);
        // 行数(表头的目录不需要，从1开始)
        for (int i = 1; i < sheet.getRows(); i++) {
            // 列数
            for (int j = 0; j < sheet.getColumns(); j++) {
                // 获取第i行，第j列的值
                Cell keyCell = sheet.getCell(j, 0);
                // 获取第i行，第j列的值
                Cell valueCell = sheet.getCell(j, i);
                mapResult.put(keyCell.getContents(),valueCell.getContents());
            }
            // 把刚获取的列存入list，也可以存入到数据库
            list.add(mapResult);
        }
        return list;
    }
}

 

 

使用：

class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        //创建文件夹
        var filePath = Environment.getExternalStorageDirectory().path+ "/AndroidExcelDemo"
        try {
            val file = File(filePath)
            if (!file.exists()) {
                file.mkdirs()
            }
        }catch ( e:Exception){
           Log.e("bai",e.message)
        }

        //Excel文件名
        val excelFileName = "/motian.xlsx"

        //Excel的列字段
        val columnTitles : Array<String> = arrayOf("姓名","项目","年")

        //文件的全路径
        filePath += excelFileName

        //初始化 Excel的文件、表单名称、列字段
        ExcelUtil.initExcel(filePath,"project", columnTitles)

        //内容
        val projectBean = ProjectBean()
        projectBean.name="漠天"
        projectBean.project ="使用excel"
        projectBean.year = "2019"

        val list:List<ProjectBean>  = arrayListOf(projectBean)
        //写入到excel文件里内容
        ExcelUtil.writeObjListToExcel(list, filePath, this)

        //读取Excel的内容
        val resultFromXLS  = ExcelUtil.getXlsData(filePath)
        Log.e("bai",resultFromXLS[0].toString())

    }
}