Android导出数据到Excel表

一、引入所需依赖

implementation 'org.apache.poi:poi:3.17'

二、常用方法介绍

public class DownloadToExcelFile {

    public void download(Context context){
        
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        
        //建一个Sheet
        HSSFSheet sheet = hssfWorkbook.createSheet();
        //HSSFSheet sheet = wb.createSheet("可以在这里指定名称");

        //====================================设置属性===============================================
        
        //设置列宽
        sheet.setColumnWidth(0,2000);//（第几列，多宽）
        sheet.setColumnWidth(1,7000);//（第几列，多宽）
        sheet.setColumnWidth(2,7000);//（第几列，多宽）
        sheet.setColumnWidth(3,7000);//（第几列，多宽）
        
        HSSFFont txtStyle = hssfWorkbook.createFont(); //字体格式设置对象
        txtStyle.setFontName("黑体"); // 设置字体黑体
        txtStyle.setBold(true); // 字体加粗
        txtStyle.setFontHeightInPoints(( short ) 16 ); // 设置字体大小
        txtStyle.setColor(HSSFFont.COLOR_RED);//字体颜色

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); // 生成行格式设置对象
        cellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 横向居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中对齐
        cellStyle.setFont(txtStyle);//设置字体属性

        //====================================写入数据===============================================

        HSSFRow row1 = sheet.createRow(0);//第1行（参数：行）
        row1.setHeight((short) 500);//设置行高（参数：高度）
        HSSFCell cell0=row1.createCell(0);//第1格
        HSSFCell cell1=row1.createCell(1);//第2格
        HSSFCell cell2=row1.createCell(2);//第3格
        HSSFCell cell3=row1.createCell(3);//第4格

        //设置格子属性样式
        cell0.setCellStyle(cellStyle);
        cell1.setCellStyle(cellStyle);
        cell2.setCellStyle(cellStyle);
        cell3.setCellStyle(cellStyle);
        
        //设置格子 数据值
        cell0.setCellValue("第1格内容");
        cell1.setCellValue("第2格内容");
        cell2.setCellValue("第3格内容");
        cell3.setCellValue("第4格内容");

        //====================================写入本地文件===============================================
        
        String filePath = "/sdcard/Record/";
        String fileName = "我的文件名"+getNowTime()+".xls";

        String resultPath=filePath+fileName;

        File file = new File(resultPath);
        //判断文件是否存在，不存在则创建
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        try {
            FileOutputStream fileOutputStream=new FileOutputStream(resultPath);
            hssfWorkbook.write(fileOutputStream);
            fileOutputStream.close();
            Toast.makeText(context,"下载成功", Toast.LENGTH_SHORT).show();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private String getNowTime(){
        Date time=new Date();
        SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMddHHmmss");
        return sdf.format(time);
    }

}

三、写一个实现案例

(1) 最终效果

（2）我们有一个实体类

public class MyBean {

    int id;//序号
    String weighValue;//大象体重
    String time;//称重时间
    String date;//称重日期

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getWeighValue() {
        return weighValue;
    }

    public void setWeighValue(String weighValue) {
        this.weighValue = weighValue;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
    
}

（3）数据下载类实现

import android.content.Context;
import android.util.Log;
import android.widget.Toast;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class DownloadDataUtil {

    public void download(Context context, List<MyBean> list){

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();

        //设置列宽
        sheet.setColumnWidth(0,2000);
        sheet.setColumnWidth(1,7000);
        sheet.setColumnWidth(2,7000);
        sheet.setColumnWidth(3,7000);

        //=================================定义表头属性===============================================
        HSSFFont font = wb.createFont(); // 生成字体格式设置对象
        font.setFontName("黑体"); // 设置字体黑体
        font.setBold(true); // 字体加粗
        font.setFontHeightInPoints(( short ) 16 ); // 设置字体大小
        font.setColor(HSSFFont.COLOR_NORMAL);//字体颜色

        HSSFCellStyle cellStyle = wb.createCellStyle(); // 生成行格式设置对象
        cellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 横向居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中对齐
        cellStyle.setFont(font);

        //=================================定义内容属性===============================================
        HSSFFont txtContent = wb.createFont(); // 生成字体格式设置对象
        txtContent.setFontName("黑体"); // 设置字体黑体
        txtContent.setBold(false); // 字体加粗
        txtContent.setFontHeightInPoints(( short ) 12 ); // 设置字体大小
        txtContent.setColor(HSSFFont.COLOR_RED);//字体颜色

        HSSFCellStyle cellStyleContent = wb.createCellStyle(); // 生成行格式设置对象
        cellStyleContent.setBorderBottom(BorderStyle.THIN);// 下边框
        cellStyleContent.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyleContent.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyleContent.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyleContent.setAlignment(HorizontalAlignment.CENTER); // 横向居中对齐
        cellStyleContent.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中对齐
        cellStyleContent.setFont(txtContent);

        //====================================写入数据===============================================
        for (int k=0;k<list.size()+1;k++){
            HSSFRow row = sheet.createRow(k);

            if (k==0){
                HSSFCell cell0=row.createCell(0);
                HSSFCell cell1=row.createCell(1);
                HSSFCell cell2=row.createCell(2);
                HSSFCell cell3=row.createCell(3);
                cell0.setCellStyle(cellStyle);
                cell1.setCellStyle(cellStyle);
                cell2.setCellStyle(cellStyle);
                cell3.setCellStyle(cellStyle);

                row.setHeight((short) 500);
                cell0.setCellValue("序号");
                cell1.setCellValue("大象体重");
                cell2.setCellValue("时间");
                cell3.setCellValue("日期");
            }else {

                HSSFCell cell0=row.createCell(0);
                HSSFCell cell1=row.createCell(1);
                HSSFCell cell2=row.createCell(2);
                HSSFCell cell3=row.createCell(3);
                cell0.setCellStyle(cellStyleContent);
                cell1.setCellStyle(cellStyleContent);
                cell2.setCellStyle(cellStyleContent);
                cell3.setCellStyle(cellStyleContent);

                row.setHeight((short) 500);
                cell0.setCellValue(list.get(k-1).getId());
                cell1.setCellValue(list.get(k-1).getWeighValue());
                cell2.setCellValue(list.get(k-1).getTime());
                cell3.setCellValue(list.get(k-1).getDate());
            }
        }

        String filePath = "/sdcard/Record/";
        String fileName = "我的文件名"+getNowTime()+".xls";

        String resultPath=filePath+fileName;

        File file = new File(resultPath);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                Log.e("fxHou","Create Fail IOException"+e);
                e.printStackTrace();
            }
        }

        try {
            FileOutputStream fileOutputStream=new FileOutputStream(resultPath);
            wb.write(fileOutputStream);
            fileOutputStream.close();
            Toast.makeText(context, "DOWNLOAD SUCCESS ："+resultPath, Toast.LENGTH_SHORT).show();
        } catch (FileNotFoundException e) {
            Log.e("fxHou","Download Fail FileNotFoundException"+e);
            e.printStackTrace();
        } catch (IOException e) {
            Log.e("fxHou","Download Fail IOException"+e);
            e.printStackTrace();
        }

    }

    private String getNowTime(){
        Date time=new Date();
        SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMddHHmmss");
        return sdf.format(time);
    }

}

（4）调用

List<MyBean> list=new ArrayList<>();//曹冲称象数据
new DownloadDataUtil().download(mContext,list);