How to operate Excel files using Android

This article introduces how to use Android to operate Excel files

When operating Excel files and exporting reports in Android, the open source library jxl is mainly used. It was first used on java, but it can also be used on Android. Similar POI can only be used for Java, not Android, because it relies on many libraries.

To use jxl, you need to import the jxl.jar package in the Android project. jxl can complete the basic reading and writing operations of Excel. Its support and non-support are as follows:
1. jxl only supports Excel2003 format, not Support Excel2007 format. That is, xls files are supported, but xlsx files are not supported.
2. jxl does not support direct modification of excel files, but it can be modified indirectly by copying a new file and overwriting the original file.
3. jxl can only recognize images in PNG format and cannot recognize images in other formats.


As can be seen from the above, jxl does not support Excel2007, which is very bad, especially when Excel2007 has become the mainstream Excel format. However, there is now a temporary method for Android to read the 2007 format. If we carefully analyze the file format of xlsx, we will find that the xlsx file is actually a compressed package. There are various files in the compressed package, and the data is generally placed in "xl/ sharedStrings.xml" and "xl/worksheets/sheet1.xml". Based on this, when we determine that the Excel file is in 2007 format, we can decompress it, extract sharedStrings.xml and sheet1.xml from it, and then use XML parsing tools to parse out the specific data.


The following is an example of reading and writing code for Excel files, which supports reading and writing in 2003 format, as well as reading in 2007 format:

import java.io.File;  
import java.io.IOException;  
import java.io.InputStream;  
import java.util.ArrayList;  
import java.util.List;  
import java.util.zip.ZipEntry;  
import java.util.zip.ZipException;  
import java.util.zip.ZipFile;  
  
import org.xmlpull.v1.XmlPullParser;  
import org.xmlpull.v1.XmlPullParserException;  
  
import android.util.Log;  
import android.util.Xml;  
  
import jxl.Sheet;  
import jxl.Workbook;  
import jxl.write.Label;  
import jxl.write.WritableSheet;  
import jxl.write.WritableWorkbook;  
  
public class ExcelUtil {  
    private final static String TAG = "ExcelUtil";  
  
    public static List<List<Object>> read(String file_name) {  
        String extension = file_name.lastIndexOf(".") == -1 ? "" : file_name  
                .substring(file_name.lastIndexOf(".") + 1);  
        if ("xls".equals(extension)) {// 2003  
            Log.d(TAG, "read2003XLS, extension:" + extension);  
            return read2003XLS(file_name);  
        } else if ("xlsx".equals(extension)) {  
            Log.d(TAG, "read2007XLSX, extension:" + extension);  
            return read2007XLSX(file_name);  
        } else {  
            Log.d(TAG, "不支持的文件类型, extension:" + extension);  
            return null;  
        }  
    }  
  
    public static List<List<Object>> read2003XLS(String path) {  
        List<List<Object>> dataList = new ArrayList<List<Object>>();  
        try {  
            Workbook book = Workbook.getWorkbook(new File(path));  
            // book.getNumberOfSheets();  //获取sheet页的数目  
            // 获得第一个工作表对象  
            Sheet sheet = book.getSheet(0);  
            int Rows = sheet.getRows();  
            int Cols = sheet.getColumns();  
            Log.d(TAG, "当前工作表的名字:" + sheet.getName());  
            Log.d(TAG, "总行数:" + Rows + ", 总列数:" + Cols);  
  
            List<Object> objList = new ArrayList<Object>();  
            String val = null;  
            for (int i = 0; i < Rows; i++) {  
                boolean null_row = true;  
                for (int j = 0; j < Cols; j++) {  
                    // getCell(Col,Row)获得单元格的值，注意getCell格式是先列后行，不是常见的先行后列  
                    Log.d(TAG, (sheet.getCell(j, i)).getContents() + "\t");  
                    val = (sheet.getCell(j, i)).getContents();  
                    if (val == null || val.equals("")) {  
                        val = "null";  
                    } else {  
                        null_row = false;  
                    }  
                    objList.add(val);  
                }  
                Log.d(TAG, "\n");  
                if (null_row != true) {  
                    dataList.add(objList);  
                    null_row = true;  
                }  
                objList = new ArrayList<Object>();  
            }  
            book.close();  
        } catch (Exception e) {  
            Log.d(TAG, e.getMessage());  
        }  
  
        return dataList;  
    }  
  
    public static List<List<Object>> read2007XLSX(String path) {  
        List<List<Object>> dataList = new ArrayList<List<Object>>();  
        String str_c = "";  
        String v = null;  
        boolean flat = false;  
        List<String> ls = new ArrayList<String>();  
        try {  
            ZipFile xlsxFile = new ZipFile(new File(path));  
            ZipEntry sharedStringXML = xlsxFile.getEntry("xl/sharedStrings.xml");  
            if (sharedStringXML == null) {  
                Log.d(TAG, "空文件:" + path);  
                return dataList;  
            }  
            InputStream inputStream = xlsxFile.getInputStream(sharedStringXML);  
            XmlPullParser xmlParser = Xml.newPullParser();  
            xmlParser.setInput(inputStream, "utf-8");  
            int evtType = xmlParser.getEventType();  
            while (evtType != XmlPullParser.END_DOCUMENT) {  
                switch (evtType) {  
                case XmlPullParser.START_TAG:  
                    String tag = xmlParser.getName();  
                    if (tag.equalsIgnoreCase("t")) {  
                        ls.add(xmlParser.nextText());  
                    }  
                    break;  
                case XmlPullParser.END_TAG:  
                    break;  
                default:  
                    break;  
                }  
                evtType = xmlParser.next();  
            }  
            ZipEntry sheetXML = xlsxFile.getEntry("xl/worksheets/sheet1.xml");  
            InputStream inputStreamsheet = xlsxFile.getInputStream(sheetXML);  
            XmlPullParser xmlParsersheet = Xml.newPullParser();  
            xmlParsersheet.setInput(inputStreamsheet, "utf-8");  
            int evtTypesheet = xmlParsersheet.getEventType();  
            List<Object> objList = new ArrayList<Object>();  
            String val = null;  
            boolean null_row = true;  
  
            while (evtTypesheet != XmlPullParser.END_DOCUMENT) {  
                switch (evtTypesheet) {  
                case XmlPullParser.START_TAG:  
                    String tag = xmlParsersheet.getName();  
                    if (tag.equalsIgnoreCase("row")) {  
                    } else if (tag.equalsIgnoreCase("c")) {  
                        String t = xmlParsersheet.getAttributeValue(null, "t");  
                        if (t != null) {  
                            flat = true; // 字符串型  
                            // Log.d(TAG, flat + "有");  
                        } else { // 非字符串型，可能是整型  
                            // Log.d(TAG, flat + "没有");  
                            flat = false;  
                        }  
                    } else if (tag.equalsIgnoreCase("v")) {  
                        v = xmlParsersheet.nextText();  
                        if (v != null) {  
                            if (flat) {  
                                str_c += ls.get(Integer.parseInt(v)) + "  ";  
                                val = ls.get(Integer.parseInt(v));  
                                null_row = false;  
                            } else {  
                                str_c += v + "  ";  
                                val = v;  
                            }  
                            objList.add(val);  
                        }  
                    }  
                    break;  
                case XmlPullParser.END_TAG:  
                    if (xmlParsersheet.getName().equalsIgnoreCase("row") && v != null) {  
                        str_c += "\n";  
                        if (null_row != true) {  
                            dataList.add(objList);  
                            null_row = true;  
                        }  
                        objList = new ArrayList<Object>();  
                    }  
                    break;  
                }  
                evtTypesheet = xmlParsersheet.next();  
            }  
            Log.d(TAG, str_c);  
        } catch (ZipException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } catch (XmlPullParserException e) {  
            e.printStackTrace();  
        }  
        if (str_c == null) {  
            str_c = "解析文件出现问题";  
            Log.d(TAG, str_c);  
        }  
  
        return dataList;  
    }  
  
    public static int writeExcel(String file_name, List<List<Object>> data_list) {  
        try {  
            WritableWorkbook book = Workbook.createWorkbook(new File(file_name));  
            WritableSheet sheet1 = book.createSheet("sheet1", 0);  
            for (int i = 0; i < data_list.size(); i++) {  
                List<Object> obj_list = data_list.get(i);  
                for (int j = 0; j < obj_list.size(); j++) {  
                    Label label = new Label(j, i, obj_list.get(j).toString());  
                    sheet1.addCell(label);  
                }  
            }  
            book.write();  
            book.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
            return -1;  
        }  
        return 0;  
    }

　

The above is the detailed content of How to operate Excel files using Android. For more information, please follow other related articles on the PHP Chinese website!