logo标签列表

如何在安卓手机上创建Excel文件？

androidexcel
25

我需要编程创建一个Excel文件。有没有API可以创建Excel文件，或者其他的方法？
2011年11月7日更新
我尝试了来自这个链接的 "Create an Excel Spreadsheet" 的示例，并且我在 workbook.write(); 处得到了NullPointerException。使用这个方法，我可以在SD卡上创建Excel文件，但是当我使用MS Office 2007打开该Excel文件时，我会得到“无法读取文件”的消息
以下是堆栈跟踪，ExcelStudy 是我的活动，它使用WriteExcel类 

W/System.err(  235): java.lang.NullPointerException
W/System.err(  235):    at jxl.biff.StringHelper.getUnicodeBytes(StringHelper.java:133)
W/System.err(  235):    at jxl.biff.FontRecord.getData(FontRecord.java:289)
W/System.err(  235):    at jxl.biff.WritableRecordData.getBytes(WritableRecordData.java:71)
W/System.err(  235):    at jxl.write.biff.File.write(File.java:132)
W/System.err(  235):    at jxl.biff.Fonts.write(Fonts.java:110)
W/System.err(  235):    at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:699)
W/System.err(  235):    at comm.study.code.WriteExcel.write(WriteExcel.java:49)
W/System.err(  235):    at comm.study.code.ExcelStudy.createExcelFile(ExcelStudy.java:64)
W/System.err(  235):    at comm.study.code.ExcelStudy$1.onClick(ExcelStudy.java:47)
W/System.err(  235):    at android.view.View.performClick(View.java:2364)
W/System.err(  235):    at android.view.View.onTouchEvent(View.java:4179)
W/System.err(  235):    at android.widget.TextView.onTouchEvent(TextView.java:6541)
W/System.err(  235):    at android.view.View.dispatchTouchEvent(View.java:3709)
W/System.err(  235):    at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
W/System.err(  235):    at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
W/System.err(  235):    at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
W/System.err(  235):    at com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
W/System.err(  235):    at com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
W/System.err(  235):    at android.app.Activity.dispatchTouchEvent(Activity.java:2061)
W/System.err(  235):    at com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
W/System.err(  235):    at android.view.ViewRoot.handleMessage(ViewRoot.java:1691)
W/System.err(  235):    at android.os.Handler.dispatchMessage(Handler.java:99)
W/System.err(  235):    at android.os.Looper.loop(Looper.java:123)
W/System.err(  235):    at android.app.ActivityThread.main(ActivityThread.java:4363)
W/System.err(  235):    at java.lang.reflect.Method.invokeNative(Native Method)
W/System.err(  235):    at java.lang.reflect.Method.invoke(Method.java:521)
W/System.err(  235):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
W/System.err(  235):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
W/System.err(  235):    at dalvik.system.NativeStart.main(Native Method)
你检查过文件是否已创建了吗？并添加更多的logcat消息。 - Pankaj Kumar
是的，我把那个大小为13.5 KB的Excel文件从SD卡复制到了我的本地驱动器，并打开它，但是出现了“无法读取文件”的错误。 - Sandy
好的。当您再次遇到错误时，请分享您的代码。 - Pankaj Kumar
@Sandy，你最后是否弄清楚如何解决了呢？或者你找到了在Android中创建Excel文档的替代解决方案了吗？ - Snake
在这里查看轻量级解决方案：https://stackoverflow.com/a/77171826/12544067 - undefined
5个回答
14

首先，您需要访问此链接，从中可以下载最新的库：

http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.tar.gz

然后，在onCreate或onResume方法中添加以下代码：

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet firstSheet = workbook.createSheet("Sheet No: 1");
HSSFSheet secondSheet = workbook.createSheet("Sheet No: 2");
HSSFRow rowA = firstSheet.createRow(0);
HSSFCell cellA = rowA.createCell(0);
cellA.setCellValue(new HSSFRichTextString("Sheet One"));
HSSFRow rowB = secondSheet.createRow(0);
HSSFCell cellB = rowB.createCell(0);
cellB.setCellValue(new HSSFRichTextString("Sheet two"));
FileOutputStream fos = null;
try {
    String str_path = Environment.getExternalStorageDirectory().toString();
    File file ;
    file = new File(str_path, getString(R.string.app_name) + ".xls");
    fos = new FileOutputStream(file);
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    if (fos != null) {
        try {
            fos.flush();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    Toast.makeText(MainActivity.this, "Excel Sheet Generated", Toast.LENGTH_SHORT).show();
}

// 要查看此Excel文件，请在Eclipse的文件浏览器中转到SDCard路径 -> Excel.xls -> 拉取它 -> 查看。

我有JSON格式的数据，如何转换成Excel文件？ - Karthi
@Karthi 制作一个数据矩阵，并使用循环将数据放入Excel中。 例如： `Cell c; Sheet sheet1= wb.createSheet(fileName); int i=0;int j=0; for (ArrayList<String> rowList:reportDataLOL) { Row row=sheet1.createRow(i++); for(String item:rowList) { c=row.createCell(j++); c.setCellValue(item); } j=0; }`reportDataLOL 是一个包含ArrayList<String>的ArrayList。 - sanidhya pal
@user2368799，您上面的链接出现了404未找到错误。 - user8181649
java.lang.IllegalArgumentException: 在工作表名称“Sheet No: 1”中发现无效字符“：”，建议移除。 - Shubham AgaRwal
我遇到一个异常，像是关于 awt.Color 的问题。有解决方法。 - shafeeq
下载了那个库之后，下一步是什么？我应该把它放在哪里？ - Ravi Vaniya
4
1
谢谢您的帮助，我已经尝试了这个方法，但是在workbook.write();代码中出现了NullpointerException异常。请问我该如何解决？ - Sandy
1
@Sandy：你的问题是关于创建Excel文件的API，我给了你两个例子。听到你尝试了推荐的教程我很高兴，但是我需要看一下代码和堆栈跟踪才能帮助你解决空指针异常的问题（NPE可能有很多原因...）。我建议你提出一个新的、更具体的问题，欢迎在评论中留下其链接以便我查看。 - Amos M. Carpenter
@Sandy：看起来和http://stackoverflow.com/questions/7119462/problem-in-creating-spreadsheetxls-file一样的问题，那个问题还没有得到解答，但是问一个新问题不是更容易吗？你原来的问题已经被回答了，你的NullPointerException是一个“新问题”。 - Amos M. Carpenter
1
我理解了你所说的。我想确保你和Pankaj指定的API适用于Android。因此，我在这里发布了那个空指针异常问题。我接受Pankaj的答案，因为它包含更多的文档，并且我将会点赞你的答案。如果我以后能够解决那个问题，我会在这里发布解决方案。感谢你为我花费宝贵的时间。 - Sandy
4
首先在你的应用的 build.gradle 文件中添加以下依赖项，然后按照其他答案的步骤进行操作：
implementation 'org.apache.poi:poi:3.17'
implementation 'org.apache.poi:poi-ooxml:3.17'
如果我们遇到重复类错误，以下是解决方法：https://dev59.com/fZjga4cB1Zd3GeqPNKRh#68139550 - Reejesh PK
1

bean类

class Bean {
        String initial, firstName, middleName, lastName;

        Bean(String initial, String firstName, String middleName, String lastName) {
            this.initial = initial;
            this.firstName = firstName;
            this.middleName = middleName;
            this.lastName = lastName;
        }

        public String getInitial() {
            return initial;
        }

        public String getFirstName() {
            return firstName;
        }

        public String getMiddleName() {
            return middleName;
        }

        public String getLastName() {
            return lastName;
        }

    }

创建Excel表格的代码

sheet.addCell(new Label(0, 0, "NameInitial"));
sheet.addCell(new Label(columnNumber,rowNumber,dataString));

 File directory, sd, file;
    WritableWorkbook workbook;

    void createExcelSheet() {
        String csvFile = "ExcelsheetName.xls";
        sd = Environment.getExternalStorageDirectory();
        directory = new File(sd.getAbsolutePath());
        file = new File(directory, csvFile);
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));
        try {
            workbook = Workbook.createWorkbook(file, wbSettings);
            createFirstSheet();
            createSecondSheet();
            //closing cursor
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    void createFirstSheet() {
        try {
            List<Bean> listdata = new ArrayList<>();

            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            //Excel sheet name. 0 (number)represents first sheet
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            // column and row title
            sheet.addCell(new Label(0, 0, "NameInitial"));
            sheet.addCell(new Label(1, 0, "firstName"));
            sheet.addCell(new Label(2, 0, "middleName"));
            sheet.addCell(new Label(3, 0, "lastName"));

            for (int i = 0; i < listdata.size(); i++) {
                sheet.addCell(new Label(0, i + 1, listdata.get(i).getInitial()));
                sheet.addCell(new Label(1, i + 1, listdata.get(i).getFirstName()));
                sheet.addCell(new Label(2, i + 1, listdata.get(i).getMiddleName()));
                sheet.addCell(new Label(3, i + 1, listdata.get(i).getLastName()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    void createSecondSheet() {

        try {
            List<Bean> listdata = new ArrayList<>();
            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            listdata.add(new Bean("mr","firstName1","middleName1","lastName1"));
            //Excel sheet name. 0 (number)represents first sheet
            WritableSheet sheet = workbook.createSheet("sheet2", 0);
            // column and row title
            sheet.addCell(new Label(0, 0, "NameInitial"));
            sheet.addCell(new Label(1, 0, "firstName"));
            sheet.addCell(new Label(2, 0, "middleName"));
            sheet.addCell(new Label(3, 0, "lastName"));

            for (int i = 0; i < listdata.size(); i++) {
                sheet.addCell(new Label(0, i + 1, listdata.get(i).getInitial()));
                sheet.addCell(new Label(1, i + 1, listdata.get(i).getFirstName()));
                sheet.addCell(new Label(2, i + 1, listdata.get(i).getMiddleName()));
                sheet.addCell(new Label(3, i + 1, listdata.get(i).getLastName()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

从Excel表格中读取数据。
 public void readDataFromExcelSheet() {
        List<Bean> listOfBean = new ArrayList<>();
        try {
            String filename = "ExcelsheetName.xls";
            // Creating Input Stream
            File sd = Environment.getExternalStorageDirectory();
            File directory = new File(sd.getAbsolutePath());
            File file = new File(directory, filename);
            Workbook workbook = null;
            WorkbookSettings ws = new WorkbookSettings();
            ws.setGCDisabled(true);
            workbook = Workbook.getWorkbook(file, ws);

            int noOfSheets = workbook.getNumberOfSheets();//this is return how many sheets available in excelsheet
            String sheetsNames[] = workbook.getSheetNames();//this is return all sheets names available in excelsheet
            for (int x = 0; x < noOfSheets; x++)//here take all sheets
            {
                Sheet sheet = workbook.getSheet(x);//here i taken first sheet
                int rowCount = sheet.getRows();//count total number of row or data in that sheet
                for (int i = 0; i < rowCount; i++) {//take every row data
                    Cell[] column = sheet.getRow(i);//take all data of one row

                /*
                 for taking one by one column data 
                */
                    for (int j = 0; j < column.length; j++) {//take every column data of row
                        System.out.print("" + column[j].getContents() + "\t");//take one by one data
                    }
                /*
                 for taking column data  
                */
                    listOfBean.add(new Bean(column[0].getContents(), column[1].getContents(), column[2].getContents(), column[3].getContents()));

                }

//                if you want take different data from different sheets then use switch case
                Sheet sheet1;
                int rowCount1;
                switch (x) {
                    case 0:
                        //write code for sheet 0 read data
                        sheet1 = workbook.getSheet(0);//here i taken first sheet
                        rowCount1 = sheet1.getRows();//count total number of row or data in that sheet
                        for (int i = 0; i < rowCount1; i++) {//take every row data
                            Cell[] column = sheet1.getRow(i);//take all data of one row
                            listOfBean.add(new Bean(column[0].getContents(), column[1].getContents(), column[2].getContents(), column[3].getContents()));
                        }
                        break;
                    case 1:
                        //write code for sheet 1 read data
                        sheet1 = workbook.getSheet(1);//here i taken first sheet
                        rowCount1 = sheet1.getRows();//count total number of row or data in that sheet
                        for (int i = 0; i < rowCount1; i++) {//take every row data
                            Cell[] column = sheet1.getRow(i);//take all data of one row
                            listOfBean.add(new Bean(column[0].getContents(), column[1].getContents(), column[2].getContents(), column[3].getContents()));
                        }
                        break;
                }


            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
0

您可以直接使用 Android Worksheet 库。

在您的 gradle 文件中添加以下内容：implementation 'com.github.elirehema:worksheet:0.0.1'

在您的 Activity 类中实现它：

public class MainActivity extends AppCompatActivity {
private WorkSheet workSheet;
private Button button;
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    button = findViewById(R.id.create_excel_sheet);
    final String path  = "ExternalFilePath";
    
    button.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            try {
                workSheet = new WorkSheet.Builder(getApplicationContext(), path)
                        .setSheet(List<Object>)
                        .writeSheet();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    });


}}

阅读更多

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接