Android Poi包导入导出execl数据

已于 2022-03-21 19:33:58 修改
阅读量3.6k 收藏
点赞数
文章标签： java
于 2022-03-21 19:31:49 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qizhuocai/article/details/123643616
版权

需要下载两个包

1.poi-3.12-android-a.jar

2.poi-ooxml-schemas-3.12-20150511-a.jar

新建个execl单元格实体类

public class Local_execl_entity {
    public String getCell0() {
        return cell0;
    }

    public Local_execl_entity setCell0(String cell0) {
        this.cell0 = cell0;
        return this;
    }

    private String cell0;
    private String cell1;
    private String cell2;
    private String cell3;
    private String cell4;
    private String cell5;
    private String cell6;
    private String cell7;
    private String cell8;
    private String cell9;
    public String getCell1() {
        return cell1;
    }

    public Local_execl_entity setCell1(String cell1) {
        this.cell1 = cell1;
        return this;
    }

    public String getCell2() {
        return cell2;
    }

    public Local_execl_entity setCell2(String cell2) {
        this.cell2 = cell2;
        return this;
    }

    public String getCell3() {
        return cell3;
    }

    public Local_execl_entity setCell3(String cell3) {
        this.cell3 = cell3;
        return this;
    }

    public String getCell4() {
        return cell4;
    }

    public Local_execl_entity setCell4(String cell4) {
        this.cell4 = cell4;
        return this;
    }

    public String getCell5() {
        return cell5;
    }

    public Local_execl_entity setCell5(String cell5) {
        this.cell5 = cell5;
        return this;
    }

    public String getCell6() {
        return cell6;
    }

    public Local_execl_entity setCell6(String cell6) {
        this.cell6 = cell6;
        return this;
    }

    public String getCell7() {
        return cell7;
    }

    public Local_execl_entity setCell7(String cell7) {
        this.cell7 = cell7;
        return this;
    }

    public String getCell8() {
        return cell8;
    }

    public Local_execl_entity setCell8(String cell8) {
        this.cell8 = cell8;
        return this;
    }

    public String getCell9() {
        return cell9;
    }

    public Local_execl_entity setCell9(String cell9) {
        this.cell9 = cell9;
        return this;
    }



}
/**
 * 自定义一个String适配器
 */
public class StringNullAdapter extends TypeAdapter<String> {

    @Override
    public void write(JsonWriter jsonWriter, String s) throws IOException {
        if (s == null) {//序列化使用的是adapter的write方法
            //jsonWriter.nullValue();//这个方法是错的，而是应该将null转成""
            jsonWriter.value("");
            return;
        }
        jsonWriter.value(s);
    }

    @Override
    public String read(JsonReader jsonReader) throws IOException {
        if (jsonReader.peek() == JsonToken.NULL) {//反序列化使用的是read方法
            jsonReader.nextNull();
            return "";
        }
        return jsonReader.nextString();
    }
}

操作类

public class ExeclUtil {

    private static final String TAG = ExeclUtil.class.getSimpleName();

    /**
     * 读取 xls 格式
     * @param fileName
     * @return
     */
    public static  ArrayList<Local_execl_entity>  readXLSFileFromAssets( String  fileName ) {
        ArrayList<Local_execl_entity> contactList = new ArrayList<>();
        try {

            if(fileName.equals(""))
            {
                ToastUtil.showToast("请选择导入的文件！！");
                return null;
            }
            File file = new File(fileName);
            InputStream myInput;
            myInput = new FileInputStream(file);
            // Create a POIFSFileSystem object
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            // Create a workbook using the File System
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            // XSSF
            // Get the first sheet from workbook
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            /** We now need something to iterate through the cells. **/
            Iterator<Row> rowIter = mySheet.rowIterator();

            while (rowIter.hasNext()) {
                synchronized ("error") {
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                Local_execl_entity bean = new Local_execl_entity();
                String strcel="";
                while (cellIter.hasNext()) {
                    try {
                        HSSFCell myCell = (HSSFCell) cellIter.next();
                        strcel=  numberFormat(myCell);
                        if (myCell.getColumnIndex() == 0) {
                            bean.setCell0(strcel);
                        }
                        if (myCell.getColumnIndex() == 1) {
                            //  strcel=  numberFormat(myCell);
                            bean.setCell1(strcel);
                        }
                        if (myCell.getColumnIndex() == 2) {
                            bean.setCell2(strcel);
                        }
                        if (myCell.getColumnIndex() == 3) {
                            bean.setCell3(strcel);
                        }
                        if (myCell.getColumnIndex() == 4) {
                            bean.setCell4(strcel);
                        }
                        if (myCell.getColumnIndex() == 5) {
                            bean.setCell5(strcel);
                        }
                        if (myCell.getColumnIndex() == 6) {
                            bean.setCell6(strcel);
                        }
                        if (myCell.getColumnIndex() == 7) {
                            bean.setCell7(strcel);
                        }
                        if (myCell.getColumnIndex() == 8) {
                            bean.setCell8(strcel);
                        }
                        if (myCell.getColumnIndex() == 9) {
                            bean.setCell9(strcel);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        return null;
                    }
                }
                if (!TextUtils.isEmpty( bean.getCell1())&&!TextUtils.isEmpty( bean.getCell1())) {
                    contactList.add(bean);
                    KLog.d(TAG, "readXLSFileFromAssets: "+strcel+"    ");
                    continue;
                }
                }
            }
            KLog.d(TAG, "execl XLS数据获取: "+ GsonUtils.toJson(contactList));
        } catch (Exception e) {
            KLog.d(TAG, "readXLSFileFromAssets: 异常："+e);
            return null;
        }
        return contactList;
    }

    /**
     * 处理int类型
     * @param cell
     * @return
     */
    private static String numberFormat(Cell cell){
        try {
            if (cell == null) {
                return "";
            }
            String value = cell.toString();
            if (TextUtils.isEmpty(value) ) {
                return "";
            }
            NumberFormat numberFormat = NumberFormat.getInstance();
            // 不显示千位分割符，否则显示结果会变成类似1,234,567,890
            numberFormat.setGroupingUsed(false);
            int i = cell.getCellType();
            if (i == 1) {//字符串类型
                return value;
            } else {
                value =numberFormat.format(cell.getNumericCellValue());
                return value;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
    /**
     * 读取 xlsx 格式
     * @param fileName
     * @return
     */
    public static ArrayList<Local_execl_entity> readXLSXFileFromAssets(  String  fileName) {
        ArrayList<Local_execl_entity> contactList = new ArrayList<>();
        try {
            InputStream myInput;
            if(fileName.equals(""))
            {
                ToastUtil.showToast("请选择导入的文件！！");
                return null;
            }
            File file = new File(fileName);
            myInput = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(myInput);
            XSSFSheet mySheet = workbook.getSheetAt(0);
            /** We now need something to iterate through the cells. **/
            Iterator<Row> rowIter = mySheet.rowIterator();
            while (rowIter.hasNext()) {
                try {
                    synchronized ("error") {
                        Row myRow = (Row) rowIter.next();
                        Iterator<Cell> cellIter = myRow.cellIterator();
                        Local_execl_entity bean = new Local_execl_entity();
                        String strcel = "";
                        while (cellIter.hasNext()) {
                            XSSFCell myCell = (XSSFCell) cellIter.next();
                            strcel = numberFormat(myCell);
                            if (myCell.getColumnIndex() == 0) {
                                bean.setCell0(strcel);
                            }
                            if (myCell.getColumnIndex() == 1) {
                                bean.setCell1(strcel);
                            }
                            if (myCell.getColumnIndex() == 2) {
                                bean.setCell2(strcel);
                            }
                            if (myCell.getColumnIndex() == 3) {
                                bean.setCell3(strcel);
                            }
                            if (myCell.getColumnIndex() == 4) {
                                bean.setCell4(strcel);
                            }
                            if (myCell.getColumnIndex() == 5) {
                                bean.setCell5(strcel);
                            }
                            if (myCell.getColumnIndex() == 6) {
                                bean.setCell6(strcel);
                            }
                            if (myCell.getColumnIndex() == 7) {
                                bean.setCell7(strcel);
                            }
                            if (myCell.getColumnIndex() == 8) {
                                bean.setCell8(strcel);
                            }
                            if (myCell.getColumnIndex() == 9) {
                                bean.setCell9(strcel);
                            }
                        }
                        if (!TextUtils.isEmpty(bean.getCell1()) && !TextUtils.isEmpty(bean.getCell1())) {
                            contactList.add(bean);
                            continue;
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
        } catch (Exception e) {
            KLog.d(TAG, "execl数据获取异常: "+e);
            return null;
        }
        return contactList;
    }

    /**
     * 写Excel xlsx
     */
    public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("sheet"));
            for (int j = 0; j < objList.size(); j++) {
                GsonBuilder gsonBuilder = new GsonBuilder();
            //    gsonBuilder.setDateFormat("yyyy-MM-dd");//设置时间格式
                gsonBuilder.registerTypeAdapter(String.class, new StringNullAdapter());
                Gson gson = gsonBuilder.create();
              //  ExeclBindingBook projectBean = //gson.fromJson(gson.toJson(objList.get(j)),ExeclBindingBook.class);
//导出的业务处理
                List<String> list = new ArrayList<>();
                list.add(“数据1”);
                 list.add(“数据2”);
                 list.add(“数据3”);
 list.add(“数据4”);
 list.add(“数据5”);
                Row row = sheet.createRow(j);
                for (int i = 0; i < list.size(); i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(list.get(i));
//                    sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                    if (list.get(i).length() <= 4) {
                        //设置列宽
                        sheet.setColumnHidden(list.get(i).length() + 8,true);
                    } else {
                        sheet.setColumnHidden(list.get(i).length() + 5,true);
                        //设置列宽
                      //  sheet.setColumnView(i, list.get(i).length() + 5);
                    }
                }
                //设置行高
                 //  sheet.setRowView(j + 1, 350);
            }
            FileOutputStream fos = new FileOutputStream(new File(fileName).getAbsolutePath());
            workbook.write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            KLog.d(TAG, "writeObjListToExcel: 导出异常"+e);
            e.printStackTrace();
        }
    }
}

包就不放了，网上找就可以了。

如果项目混淆的话在app proguard-rules.pro加上；

-keep class com.bea.xml.stream.** { *;}
-keep class com.wutka.dtd.** { *;}
-keep class org.** { *;}
-keep class aavax.xml.** { *;}
-keep class com.microsoft.schemas.office.x2006.** { *; }
-keep class schemaorg_apache_xmlbeans.** { *; }
-keep class schemasMicrosoftComOfficeExcel.** { *; }
-keep class schemasMicrosoftComOfficeOffice.** { *; }
-keep class schemasMicrosoftComVml.** { *; }
-keep class repackage.** { *; }
-keep class schemaorg_apache_xmlbeans.** { *; }

qizhuocai
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值