在Android开发中有时候我们需要将数据直接生成Excel表格并导出，效果图如下：
效果图.png
方法如下：
1.下载jxl-2.6.12.jar包：放置到Android项目的libs文件夹下，并引用依赖
2.将自己要存入表格的数据，抽象成一个实体类，例如：FaceTestEntity
3.调用jxl这个jar包的Api，生成表格，设置样式，写入数据，导出表格。
我将生成表格的代码封装成了工具类，需要的同学可以拿到直接使用，或根据自身需求修改样式参数：
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);
arial10font.setColour(Colour.DARK_BLUE);
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);
//对齐格式
arial12font.setColour(Colour.BLACK);
arial12font.setPointSize(10);
//设置边框
arial12format.setBorder(Border.ALL, BorderLineStyle.THIN);
arial12format.setAlignment(jxl.format.Alignment.CENTRE);
} catch (WriteException e) {
e.printStackTrace();
}
}
public static WritableCellFormat getHeader() {
WritableFont font = new WritableFont(WritableFont.TIMES, 12,
WritableFont.BOLD);// 定义字体
try {
font.setColour(Colour.BLUE);// 蓝色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);
try {
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN,
Colour.BLACK);// 黑色边框
format.setBackground(Colour.GREEN);// 黄色背景
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}
public static <T> void setSheetHeader(Context context, String fileName, String[] colName,
String sheetName, List<T> objList) {
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding("UTF-8");
workbookSettings.setFormulaAdjust(true);
workbookSettings.setRefreshAll(true);
// 创建Excel工作表
OutputStream os = new FileOutputStream(file);
workbook = Workbook.createWorkbook(os);
//设置表格的名字
WritableSheet sheet = workbook.createSheet(sheetName, 0);
Label label;
for (int i = 0; i < colName.length; i++) {
// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
// 在Label对象的子对象中指明单元格的位置和内容
label = new Label(i, 0, colName[i], getHeader());
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
}
for (int i = 0; i < objList.size(); i++) {
FaceTestEntity faceTestEntity = (FaceTestEntity) objList.get(i);
Label name = new Label(0, i + 1, faceTestEntity.getName());
Label trackId = new Label(1, i + 1, String.valueOf(faceTestEntity.getTrackId()));
Label liveNess = new Label(2, i + 1, String.valueOf(faceTestEntity.isLiveness()));
Label rgbTime = new Label(3, i + 1, String.valueOf(faceTestEntity.getRGB_Tiem()));
Label rgbIrTime = new Label(4, i + 1, String.valueOf(faceTestEntity.getRGB_IR_Time()));
Label compareTime = new Label(5, i + 1, String.valueOf(faceTestEntity.getCompare_time()));
Label compareSimilar = new Label(6, i + 1, String.valueOf(faceTestEntity.getCompareSimilar()));
sheet.addCell(name);
sheet.addCell(trackId);
sheet.addCell(liveNess);
sheet.addCell(rgbTime);
sheet.addCell(rgbIrTime);
sheet.addCell(compareTime);
sheet.addCell(compareSimilar);
}
ToastUtils.showShortToast(context, "写入成功");
sheet.setColumnView(0, 350);
workbook.write();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
/**
* 初始化Excel
*
* @param fileName 导出excel存放的地址（目录）
* @param colName excel中包含的列名（可以有多个）
*/
public static void initExcel(String fileName, String sheetName, String[] colName) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding("UTF-8");
workbookSettings.setFormulaAdjust(true);
workbookSettings.setRefreshAll(true);
FileOutputStream os = new FileOutputStream(file);
workbook = Workbook.createWorkbook(os);
//设置表格的名字
WritableSheet sheet = workbook.createSheet(sheetName, 0);
//创建标题栏
sheet.addCell((WritableCell) new Label(0, 0, "这是标题栏", arial14format));
for (int col = 0; col < colName.length; col++) {
// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
// 在Label对象的子对象中指明单元格的位置和内容
sheet.addCell(new Label(col, 1, colName[col], /*getHeader()*/arial10format));
sheet.setColumnView(0, colName[col].length() * 8);
}
//设置行高
sheet.setRowView(0, 340);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 写入实体类数据到Excel文件
*/
@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
if (objList != null && objList.size() > 0) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName), workbook);
WritableSheet sheet = writebook.getSheet(0);
if (objList.size() > 0) {
for (int j = 0; j < objList.size(); j++) {
//要写入表格的数据对象
FaceTestEntity faceTestEntity = (FaceTestEntity) objList.get(j);
List<String> list = new ArrayList<>();
list.add(faceTestEntity.getName());
list.add(String.valueOf(faceTestEntity.getTrackId()));
list.add(String.valueOf(faceTestEntity.isLiveness()));
list.add(String.valueOf(faceTestEntity.getRGB_Tiem()));
list.add(String.valueOf(faceTestEntity.getRGB_IR_Time()));
list.add(String.valueOf(faceTestEntity.getCompare_time()));
list.add(String.valueOf(faceTestEntity.getCompareSimilar()));
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(i, j + 2, list.get(i), arial12format));
if (list.get(i).length() <= 4) {
//设置列宽 ，第一个参数是列的索引，第二个参数是列宽
sheet.setColumnView(i, list.get(i).length() * 7);
} else {
//设置列宽
sheet.setColumnView(i, list.get(i).length() * 4);
}
}
//设置行高，第一个参数是行数，第二个参数是行高
sheet.setRowView(j, 340);
}
}
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();
}
}
}
}
}
}
使用方法，记得使用前申请文件读写权限
private void excelExecute() {
String dirPath = Environment.getExternalStorageDirectory() + File.separator + "AndroidExcel";
File file = new File(dirPath);
if (!file.exists())
file.mkdirs();
String testExcelPath = dirPath + File.separator + "testExcel.xls";
List<FaceTestEntity> list = new ArrayList<>();
FaceTestEntity faceTestEntity1 = new FaceTestEntity("小狗", 1,
false, 123, 234, 234,
0.92);
FaceTestEntity faceTestEntity2 = new FaceTestEntity("大熊", 2,
false, 34, 355, 243,
0.94);
FaceTestEntity faceTestEntity3 = new FaceTestEntity("黑虎", 3,
false, 24, 56, 356,
0.90);
FaceTestEntity faceTestEntity4 = new FaceTestEntity("滤后", 4,
false, 25, 46, 234,
0.27);
FaceTestEntity faceTestEntity5 = new FaceTestEntity("祖居", 5,
false, 25, 256, 234,
0.46);
FaceTestEntity faceTestEntity6 = new FaceTestEntity("哆啦", 6,
false, 47, 47, 267,
0.96);
FaceTestEntity faceTestEntity7 = new FaceTestEntity("钱出", 7,
false, 256, 245, 256,
0.95);
FaceTestEntity faceTestEntity8 = new FaceTestEntity("尽在", 8,
false, 256, 256, 234,
0.99);
list.add(faceTestEntity1);
list.add(faceTestEntity2);
list.add(faceTestEntity3);
list.add(faceTestEntity4);
list.add(faceTestEntity5);
list.add(faceTestEntity6);
list.add(faceTestEntity7);
list.add(faceTestEntity8);
String[] colName = {"name", "trackId", "isLiveness", "RGB_Tiem",
"RGB_IR_Time", "compare_time", "compareSimilar"};
ProgressDialog progressDialog = new ProgressDialog(this);
progressDialog.setMessage("表格创建中...");
progressDialog.show();
ExcelUtil.initExcel(testExcelPath, "测试表1", colName);
// ExcelUtil.setSheetHeader(this, testExcelPath, colName, "测试表1", list);
ExcelUtil.writeObjListToExcel(list, testExcelPath, this);
progressDialog.dismiss();
}
网友评论