2021.3.11 Android导出Excel表1

一、今日学习内容

   按照老师的体温样表的格式：

   

package com.example.tiwen;

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

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @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";

    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    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);
            //对齐格式
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //设置边框
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

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

    /**
     * 初始化Excel
     *
     * @param fileName 导出excel存放的地址（目录）
     */
    public static void initExcel(String fileName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            //设置行高
            sheet.setRowView(20, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<T> objList, String fileName,Context c,String classroom,String name,String ID_Number,String phone) {
        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);

                sheet.addCell(new Label(1,1,classroom));
                sheet.addCell(new Label(1,2,name));
                sheet.addCell(new Label(5,2,ID_Number));
                sheet.addCell(new Label(1,3,"健康"));
                sheet.addCell(new Label(5,3,phone));
                //创建单元格的标签
                Label label=new Label(0,0,"学生14天健康情况登记表");
                //将标签插入到单元格中
                sheet.addCell(label);
                sheet.addCell(new Label(0,1,"单位名称："));
                sheet.addCell(new Label(4,1,"填表日期："));
                sheet.addCell(new Label(0,2,"姓名："));
                sheet.addCell(new Label(4,2,"学号："));
                sheet.addCell(new Label(0,3,"目前健康情况："));
                sheet.addCell(new Label(4,3,"手机号："));
                sheet.addCell(new Label(0,4,"每日体温、健康状况检测（周期14天）"));
                sheet.addCell(new Label(0,5,"日期"));
                sheet.addCell(new Label(1,5,"每日体温"));
                sheet.addCell(new Label(2,5,"健康情况"));
                sheet.addCell(new Label(3,5,"当日所在地"));
                sheet.addCell(new Label(5,5,"备注"));
                sheet.addCell(new Label(0,20,"本人承诺：自觉履行疫情防控责任和义务，保证以上填报信息全部属实，如有隐瞒，自愿承担相应法律后果。"));
                sheet.addCell(new Label(0,21,"本人签字"));
                sheet.addCell(new Label(4,21,"签字日期"));

                for (int j = 0; j < 14 && j<objList.size();j++) {
                    Excel m= (Excel) objList.get(j);
                    //for (int i = 0; i < list.size(); i++) {
                    sheet.addCell(new Label(0,j+6,m.getDate1()));
                    sheet.addCell(new Label(1,j+6,m.getTiwen()));
                    sheet.addCell(new Label(2,j+6,"健康"));
                    sheet.addCell(new Label(3,j+6,m.getAddress()));
                    sheet.addCell(new Label(5,j+6,m.getS()));
                    //}
                    sheet.setRowView(j+6,350); //设置行高

                }

                sheet.mergeCells(0, 21, 1, 21); // 合并单元格-本人签字
                sheet.mergeCells(0, 20, 6, 20); // 合并单元格-承诺
                sheet.mergeCells(1, 1, 3, 1); // 合并单元格-单位名称
                sheet.mergeCells(5, 1, 6, 1); // 合并单元格-填表日期
                sheet.mergeCells(0, 4, 6, 4); // 合并单元格
                sheet.mergeCells(1, 2, 3, 2); // 合并单元格-姓名
                sheet.mergeCells(5, 2, 6, 2); // 合并单元格-学号
                sheet.mergeCells(0, 0, 6, 0); // 合并单元格
                sheet.mergeCells(5, 3, 6, 3); // 合并单元格-手机号
                sheet.mergeCells(1, 3, 3, 3); // 合并单元格-健康情况
                sheet.mergeCells(2, 21, 3, 21); // 合并单元格-本人签字
                sheet.mergeCells(5, 21, 6, 21); // 合并单元格-签字日期
                sheet.mergeCells(5, 5, 6, 5); // 合并单元格-备注
                sheet.mergeCells(3, 5, 4, 5); // 合并单元格-当日所在地
                for(int t=6;t<20;t++){
                    sheet.mergeCells(3, t, 4, t); // 合并单元格-所在地
                }
                for(int t=6;t<20;t++){
                    sheet.mergeCells(5, t, 6, t); // 合并单元格-备注
                }
                writebook.write();
                Toast.makeText(c,"导出Excel成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

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

        }
    }
}

ExcelActivity:

package com.example.tiwen;

import android.content.Context;
import android.os.Build;
import android.os.Bundle;
import android.os.Environment;
import android.widget.TextView;

import androidx.appcompat.app.AppCompatActivity;

import org.litepal.LitePal;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class ExcelActivity extends AppCompatActivity {
    private String name,ID1,class1,phonenumber1,date1,tiwen1,jiankang,address,s;
    private int i;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_excel);
        TextView tv1=(TextView) findViewById(R.id.show);

        List<Judge> judge= LitePal.findAll(Judge.class);
        for(Judge j:judge){
            name=j.getName();
            ID1=j.getID1();
            class1=j.getClass1();
            phonenumber1=j.getPhonenumber1();
        }

        List<Excel> list = new ArrayList<>();
        Excel bean=null;
        List<TiWen> tiwen= (List<TiWen>) LitePal.where("ID1 = ?",ID1).find(TiWen.class);
        i=0;
        for(TiWen t:tiwen){
            date1=t.getDate1();
            tiwen1=t.getTiwen();
            jiankang="健康";
            address=t.getAddress();
            s=t.getS();
            bean=new Excel(date1,tiwen1,jiankang,address,s);
            list.add(bean);
            i++;
        }
        File file = new File(getSDPath(this));
         if (!file.exists()) {
            file.mkdirs();
         }
        
        String fname="/个人体温记录表.xls";
        String fileName = getSDPath(this) +fname;//以name存在目录中
        tv1.setText("excel已导出至："+fileName);

        if(i>=14) {
            List<Excel> list1 = list.subList(i - 14, i);
            ExcelUtil.initExcel(fileName);
            ExcelUtil.writeObjListToExcel(list1, fileName,this, class1,name,ID1,phonenumber1);
        }
        if(i<14){
            ExcelUtil.initExcel(fileName);
            ExcelUtil.writeObjListToExcel(list, fileName,this, class1,name,ID1,phonenumber1);
        }


    }


    public String getSDPath(Context context) {
        File sdDir = null;
        boolean sdCardExist = Environment.getExternalStorageState().equals(
                Environment.MEDIA_MOUNTED);// 判断sd卡是否存在

        if (sdCardExist) {
            if (Build.VERSION.SDK_INT>=29){
                //Android10之后
                sdDir = context.getExternalFilesDir(null);
            }else {
                sdDir =getExternalFilesDir(Environment.DIRECTORY_PICTURES);;// 获取SD卡根目录

            }
        } else {
            sdDir = Environment.getRootDirectory();// 获取跟目录
        }
        return sdDir.toString();
    }
}

需要加入权限：

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

二、遇到的问题

   对于Excel表的格式的代码的编写很混乱

三、明日计划

   明天继续学习Android