Android 使用jxl创建与写入Excel

    最近有个项目需要需要android手机这边对一些信息进行录入后输出一个自定义名字的excel文档，找了下相关资料，结合使用生成Excel的jxl，最后成功实现了功能，在此记录一下实现过程，以免后续忘了。

    首先需要在程序里面导入jxl，这里在build.gradle中的dependencies里面添加一下就好。

dependencies {
    ......
    api 'net.sourceforge.jexcelapi:jxl:2.6.12'
}

然后添加写入权限，顺便写一下动态权限的获取好了，放在程序的开头即可。 

    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

private void checkPermission() {
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
            PermissionRequest permissionRequest = new PermissionRequest.Builder(this)
                    .permissions(Manifest.permission.WRITE_EXTERNAL_STORAGE)
                    .onDenied(new PermissionRequest.Action() {
                        @Override
                        public void onCall(List<String> permissions) {
                            AlertDialog alertDialog = new AlertDialog.Builder(MainActivity.this)
                                    .setTitle("提示")
                                    .setMessage("APP需要写SD卡权限，请进入系统设置界面开启并重新打开App")
                                    .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                                        @Override
                                        public void onClick(DialogInterface dialog, int which) {
                                            AppSettingCompat.go(MainActivity.this);
                                            MainActivity.this.finish();
                                        }
                                    })
                                    .setNegativeButton("取消", new DialogInterface.OnClickListener() {
                                        @Override
                                        public void onClick(DialogInterface dialog, int which) {
                                            MainActivity.this.finish();
                                        }
                                    }).create();
                            alertDialog.setCancelable(false);
                            alertDialog.setCanceledOnTouchOutside(false);
                            alertDialog.show();
                        }
                    })
                    .setRationaleCallback(new PermissionRequest.OnRationaleCallback() {
                        @Override
                        public void onRationale(List<String> rationalePermissions, final IRationaleExecutor executor) {
                            AlertDialog alertDialog = new AlertDialog.Builder(MainActivity.this)
                                    .setTitle("提示")
                                    .setMessage("APP需要写SD卡权限，是否继续")
                                    .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                                        @Override
                                        public void onClick(DialogInterface dialog, int which) {
                                            executor.proceed();
                                        }
                                    })
                                    .setNegativeButton("取消", new DialogInterface.OnClickListener() {
                                        @Override
                                        public void onClick(DialogInterface dialog, int which) {
                                            MainActivity.this.finish();
                                        }
                                    }).create();
                            alertDialog.setCancelable(false);
                            alertDialog.setCanceledOnTouchOutside(false);
                            alertDialog.show();
                        }
                    })
                    .build();
            permissionRequest.request();
        }
    }

    在导入jxl后，就能将我们的数据写入Excel了，先初始化excel文件，fileName是生成Excel的文件名，titleName是带输入数据中重复属性的名字(比如带输入的数据是一堆商品，titleName可以是价格，生产日期等等)，如果不需要可以去掉。

// 初始化Excel
    public static void initExcel(String fileName, String[] titleName) {
        initFormat();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet("这里是Excel第一页名字", 0);
            sheet.addCell(new Label(0, 0, fileName, fileNameFormat));
            for (int col = 0; col < colName.length; col++) {
                // Excel最上面的属性值
                sheet.addCell(new Label(col, 0, titleName[col], titleFormat));
            }
            sheet.setRowView(0, 340); //设置行高
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    输入的字体在initFormat()里面初始化，这里也可以设置一下字体的颜色，字号等等，简化起见，这里设置了三个格式，用于文件标题，文件属性和文件内容。

    private static WritableCellFormat fileNameFormat = null;
    private static WritableCellFormat titleFormat = null;
    private static WritableCellFormat contentFormat = null;

    // 设置格式字体等参数
    private static void initFormat() {
        try {
            WritableFont font1 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            fileNameFormat = new WritableCellFormat(font1);
            fileNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
            fileNameFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            fileNameFormat.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
            WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            titleFormat = new WritableCellFormat(font2);
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            titleFormat.setBackground(Colour.GRAY_25);
            WritableFont font3 = new WritableFont(WritableFont.ARIAL, 10);
            contentFormat = new WritableCellFormat(font3);
            contentFormat.setAlignment(jxl.format.Alignment.CENTRE);//居中
            contentFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //边框
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    再来就是写入Excel内容了，这里的DeviceInfo是我对待写入的数据做的封装，如果大家想用，可以放入自己的封装数据，或者直接传入List数据，不影响使用。

@SuppressWarnings("unchecked")
    public static <T> void writeDataToExcel(DeviceInfo deviceInfo, String fileName, Context context) {
        if (deviceInfo != null) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding("UTF-8");
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);
                WritableSheet sheet = writebook.getSheet(0);
                sheet.setColumnView(1, deviceInfo.getDeviceNo().length() + 2); //列宽
                sheet.setColumnView(3, deviceInfo.getLocation().length() + 2); //列宽
                sheet.setColumnView(5, 50); //设置列宽
                // 这里第一行数据已由title占据了，待写入数据从第二行开始写入(r为1)
                sheet.addCell(new Label(0, 1, StringUtils.isNotEmpty(deviceInfo.getName()) ? deviceInfo.getName() : "", contentFormat));
                sheet.addCell(new Label(1, 1, StringUtils.isNotEmpty(deviceInfo.getDeviceNo()) ? deviceInfo.getDeviceNo() : "", contentFormat));
                sheet.addCell(new Label(2, 1, StringUtils.isNotEmpty(deviceInfo.getAddress()) ? deviceInfo.getAddress() : "", contentFormat));
                sheet.addCell(new Label(3, 1, StringUtils.isNotEmpty(deviceInfo.getLocation()) ? deviceInfo.getLocation() : "", contentFormat));
                sheet.addCell(new Label(4, 1, StringUtils.isNotEmpty(deviceInfo.getDeviceType()) ? deviceInfo.getDeviceType() : "", contentFormat));
                sheet.setRowView(1, 350); //设置行高
                if (!CollectionsUtils.isEmpty(deviceInfo.getEquipmentList())) {
                    for (int j = 0; j < deviceInfo.getEquipmentList().size(); j++) {
                        DeviceInfo equipmentInfo = (DeviceInfo) deviceInfo.getEquipmentList().get(j);
                        sheet.addCell(new Label(0, j + 2, StringUtils.isNotEmpty(equipmentInfo.getName()) ? equipmentInfo.getName() : "", contentFormat));
                        sheet.addCell(new Label(1, j + 2, StringUtils.isNotEmpty(equipmentInfo.getEquipmentNo()) ? equipmentInfo.getEquipmentNo() : "", contentFormat));
                        sheet.addCell(new Label(4, j + 2, StringUtils.isNotEmpty(equipmentInfo.getEquipmentType()) ? equipmentInfo.getEquipmentType() : "", contentFormat));
                        sheet.addCell(new Label(5, j + 2, StringUtils.isNotEmpty(equipmentInfo.getQrCode()) ? equipmentInfo.getQrCode() : "", contentFormat));
                        sheet.setRowView(j, 350); //设置行高
                    }
                }
                Toast.makeText(context, "导出成功", Toast.LENGTH_SHORT).show();
                writebook.write();
            } 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要放到相应的文件夹里面，生成的照片又要放另一个文件夹里，并需要按格式命名。这里在根目录下面创建了一个叫“测试文件夹名”的一级文件夹，下面的“Info”二级文件夹用来放Excel，平级的“photo”文件夹放照片。

// 是否存在SD卡
    private static boolean existSDCard() {
        return Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState());
    }

    // 获取存储根目录
    private static String getRootStorage() {
        if (existSDCard()) {
            return Environment.getExternalStorageDirectory().getAbsolutePath();
        } else {
            return null;
        }
    }

    // 获取设施信息根目录
    public static String getInfoRoot() {
        if (existSDCard()) {
            File root = new File(getRootStorage(), "测试文件夹名");
            if (!root.exists()) {
                root.mkdir();
            }
            String rootString = getRootStorage() + File.separator + "测试文件夹名";
            File infoRoot = new File(rootString, "Info");
            if (!infoRoot.exists()) {
                infoRoot.mkdir();
            }
            return rootString + File.separator + "Info";
        }
        return null;
    }

    // 获取设施信息根目录
    public static String getPhotoRoot(String fileName) {
        if (existSDCard()) {
            File root = new File(getRootStorage(), "测试文件夹名");
            if (!root.exists()) {
                root.mkdir();
            }
            String rootString = getRootStorage() + File.separator + "测试文件夹名";
            File photoRoot = new File(rootString, "Photo");
            if (!photoRoot.exists()) {
                photoRoot.mkdir();
            }
            String rootString2 = rootString + File.separator + "Photo";
            File photoFile = new File(rootString2, fileName);
            if (!photoFile.exists()) {
                photoFile.mkdir();
            }
            return rootString2 + File.separator + fileName;
        }
        return null;
    }

    // 将时间戳转换为时间
    public static String stampToDate(String s) {
        String res;
        Pattern pattern = Pattern.compile("[0-9]*");
        if (!pattern.matcher(s).matches()) return "";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long lt = new Long(s);
        Date date = new Date(lt);
        res = simpleDateFormat.format(date);
        return res;
    }

        最后写一下代码的调用。

 // 输出excel文档
            Date currentDate = new Date();
            long currentTime = currentDate.getTime();
            String time = ExcelUtils.stampToDate(String.valueOf(currentTime));
            String filePath = ExcelUtils.getInfoRoot() + File.separator + time + "_测试Excel.xls";
            ExcelUtils.initExcel(filePath, title);
            ExcelUtils.writeDataToExcel(deviceInfo, filePath, this);