AndroidでデータをExcelファイルに書き出す方法

要求事項の説明

アプリ内のデータをExcelシートとしてエクスポートする。

実装する

依存パッケージの追加

アプリのbuild.gradleに依存パッケージを追加します。

implementation group: 'net.sourceforge.jexcelapi', name: 'jxl', version: '2.6.12'

多くの読者が、依存関係に問題があると述べています。私は、それがWebプロキシの問題であると推測していますが、この問題が発生した場合、以下のサイトにアクセスしてください。 このアドレス で、対応するjarパッケージをダウンロードし、手動でインポートしてください。

それでもうまくいかない場合は、作者が書いた jar パッケージを このアドレス jarパッケージは自由にダウンロードできますので、ダウンロードしてapp/libsディレクトリに置き、jarパッケージファイルを右クリックし Add as Library をクリックすればOKです。

excelツールクラスの書き方

package cn.xiaojii.cashgift.util.io;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write;
import jxl.write;
import jxl.write;
import jxl.write;
WritableSheet; import jxl.write;
WritableWorkbook; import jxl.write;
WriteException; import jxl.write;

/**
 * @author dmrfcoder
 * @date 2018/8/9
 */
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";
   
   /**
     * Cell formatting Font size Color Alignment, background color, etc...
     */
    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);
            //Alignment format
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //set border
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

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

    /**
     * Initialize Excel
     *
     * @param fileName The address (directory) where the exported excel is stored
     * @param colName the name of the column contained in excel (can have more than one)
     */
    public static void initExcel(String fileName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
           // Set the name of the sheet
            WritableSheet sheet = workbook.createSheet("bill", 0);
            //create the title bar
            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));
            }
            // Set the row height
            sheet.setRowView(0, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook ! = null) { try {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static 
 void writeObjListToExcel(List
 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

 list = new ArrayList<>();
                    list.add(projectBean.getName());
                    list.add(projectBean.getProject());
                    list.add(projectBean.getMoney());
                    list.add(projectBean.getYear() " " projectBean.getMonth() " " projectBean.getDay());
                    list.add(projectBean.getBeizhu());

                    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) {
                            //set the column width
                            sheet.setColumnView(i, list.get(i).length() 8);
                        } else {
                            //set the column width
                            sheet.setColumnView(i, list.get(i).length() 5);
                        }
                    }
                    //set the row height
                    sheet.setRowView(j 1, 350);
                }

                writebook.write();
                Toast.makeText(c, "Export Excel successfully", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writer ! = null) { try {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

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

        }
    }
}



		String filePath = Environment.getExternalStorageDirectory() "/AndroidExcelDemo";
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }


        String excelFileName = "/demo.xls";


        String[] title = {"Name", "Age", "Boy"};
        String sheetName = "demoSheetName";


        List
 demoBeanList = new ArrayList<>();
        DemoBean demoBean1 = new DemoBean("ZhangSan", 10, true);
        DemoBean demoBean2 = new DemoBean("Little Red", 12, false);
        DemoBean demoBean3 = new DemoBean("李四", 18, true);
        DemoBean demoBean4 = new DemoBean("王香", 13, false);
        demoBeanList.add(demoBean1);
        demoBeanList.add(demoBean2);
        demoBeanList.add(demoBean3);
        demoBeanList.add(demoBean4);
        filePath = filePath excelFileName;


        ExcelUtil.initExcel(filePath, sheetName, title);


        ExcelUtil.writeObjListToExcel(demoBeanList, filePath, context);

        textView.setText("excel has been exported to:" filePath);

使用方法

		String filePath = Environment.getExternalStorageDirectory() "/AndroidExcelDemo";
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }


        String excelFileName = "/demo.xls";


        String[] title = {"Name", "Age", "Boy"};
        String sheetName = "demoSheetName";


        List
 demoBeanList = new ArrayList<>();
        DemoBean demoBean1 = new DemoBean("ZhangSan", 10, true);
        DemoBean demoBean2 = new DemoBean("Little Red", 12, false);
        DemoBean demoBean3 = new DemoBean("李四", 18, true);
        DemoBean demoBean4 = new DemoBean("王香", 13, false);
        demoBeanList.add(demoBean1);
        demoBeanList.add(demoBean2);
        demoBeanList.add(demoBean3);
        demoBeanList.add(demoBean4);
        filePath = filePath excelFileName;


        ExcelUtil.initExcel(filePath, sheetName, title);


        ExcelUtil.writeObjListToExcel(demoBeanList, filePath, context);

        textView.setText("excel has been exported to:" filePath);

最終的に生成されたExcelは以下のようになります。

ファイルの読み取りと書き込みのパーミッションを要求しないと、エクスポートに失敗することに注意してください。 ギズーブ にあります。