最近有个项目需要需要android手机这边对一些信息进行录入后输出一个自定义名字的excel文档，找了下相关资料，结合使用生成Excel的jxl，最后成功实现了功能，在此记录一下实现过程，以免后续忘了。
首先需要在程序里面导入jxl，这里在build.gradle中的dependencies里面添加一下就好。
dependencies {
......
api 'net.sourceforge.jexcelapi:jxl:2.6.12'
}
然后添加写入权限，顺便写一下动态权限的获取好了，放在程序的开头即可。
private void checkPermission() {
if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
PermissionRequest permissionRequest = new PermissionRequest.Builder(this)
.permissions(Manifest.permission.WRITE_EXTERNAL_STORAGE)
.onDenied(new PermissionRequest.Action() {
@Override
public void onCall(List permissions) {
AlertDialog alertDialog = new AlertDialog.Builder(MainActivity.this)
.setTitle("提示")
.setMessage("APP需要写SD卡权限，请进入系统设置界面开启并重新打开App")
.setPositiveButton("确定", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
AppSettingCompat.go(MainActivity.this);
MainActivity.this.finish();
}
})
.setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
MainActivity.this.finish();
}
}).create();
alertDialog.setCancelable(false);
alertDialog.setCanceledOnTouchOutside(false);
alertDialog.show();
}
})
.setRationaleCallback(new PermissionRequest.OnRationaleCallback() {
@Override
public void onRationale(List rationalePermissions, final IRationaleExecutor executor) {
AlertDialog alertDialog = new AlertDialog.Builder(MainActivity.this)
.setTitle("提示")
.setMessage("APP需要写SD卡权限，是否继续")
.setPositiveButton("确定", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
executor.proceed();
}
})
.setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
MainActivity.this.finish();
}
}).create();
alertDialog.setCancelable(false);
alertDialog.setCanceledOnTouchOutside(false);
alertDialog.show();
}
})
.build();
permissionRequest.request();
}
}在导入jxl后，就能将我们的数据写入Excel了，先初始化excel文件，fileName是生成Excel的文件名，titleName是带输入数据中重复属性的名字(比如带输入的数据是一堆商品，titleName可以是价格，生产日期等等)，如果不需要可以去掉。
// 初始化Excel
public static void initExcel(String fileName, String[] titleName) {
initFormat();
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("这里是Excel第一页名字", 0);
sheet.addCell(new Label(0, 0, fileName, fileNameFormat));
for (int col = 0;
col < colName.length;
col++) {
// Excel最上面的属性值
sheet.addCell(new Label(col, 0, titleName[col], titleFormat));
}
sheet.setRowView(0, 340);
//设置行高
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}输入的字体在initFormat()里面初始化，这里也可以设置一下字体的颜色，字号等等，简化起见，这里设置了三个格式，用于文件标题，文件属性和文件内容。
private static WritableCellFormat fileNameFormat = null;
private static WritableCellFormat titleFormat = null;
private static WritableCellFormat contentFormat = null;
// 设置格式字体等参数
private static void initFormat() {
try {
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
fileNameFormat = new WritableCellFormat(font1);
fileNameFormat.setAlignment(jxl.format.Alignment.CENTRE);
fileNameFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
fileNameFormat.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
titleFormat = new WritableCellFormat(font2);
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
titleFormat.setBackground(Colour.GRAY_25);
WritableFont font3 = new WritableFont(WritableFont.ARIAL, 10);
contentFormat = new WritableCellFormat(font3);
contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
//居中
contentFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
//边框
} catch (WriteException e) {
e.printStackTrace();
}
}再来就是写入Excel内容了，这里的DeviceInfo是我对待写入的数据做的封装，如果大家想用，可以放入自己的封装数据，或者直接传入List数据，不影响使用。
@SuppressWarnings("unchecked")
public static void writeDataToExcel(DeviceInfo deviceInfo, String fileName, Context context) {
if (deviceInfo != null) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding("UTF-8");
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName), workbook);
WritableSheet sheet = writebook.getSheet(0);
sheet.setColumnView(1, deviceInfo.getDeviceNo().length() + 2);
//列宽
sheet.setColumnView(3, deviceInfo.getLocation().length() + 2);
//列宽
sheet.setColumnView(5, 50);
//设置列宽
// 这里第一行数据已由title占据了，待写入数据从第二行开始写入(r为1)
sheet.addCell(new Label(0, 1, StringUtils.isNotEmpty(deviceInfo.getName()) ? deviceInfo.getName() : "", contentFormat));
sheet.addCell(new Label(1, 1, StringUtils.isNotEmpty(deviceInfo.getDeviceNo()) ? deviceInfo.getDeviceNo() : "", contentFormat));
sheet.addCell(new Label(2, 1, StringUtils.isNotEmpty(deviceInfo.getAddress()) ? deviceInfo.getAddress() : "", contentFormat));
sheet.addCell(new Label(3, 1, StringUtils.isNotEmpty(deviceInfo.getLocation()) ? deviceInfo.getLocation() : "", contentFormat));
sheet.addCell(new Label(4, 1, StringUtils.isNotEmpty(deviceInfo.getDeviceType()) ? deviceInfo.getDeviceType() : "", contentFormat));
sheet.setRowView(1, 350);
//设置行高
if (!CollectionsUtils.isEmpty(deviceInfo.getEquipmentList())) {
for (int j = 0;
j < deviceInfo.getEquipmentList().size();
j++) {
DeviceInfo equipmentInfo = (DeviceInfo) deviceInfo.getEquipmentList().get(j);
sheet.addCell(new Label(0, j + 2, StringUtils.isNotEmpty(equipmentInfo.getName()) ? equipmentInfo.getName() : "", contentFormat));
sheet.addCell(new Label(1, j + 2, StringUtils.isNotEmpty(equipmentInfo.getEquipmentNo()) ? equipmentInfo.getEquipmentNo() : "", contentFormat));
sheet.addCell(new Label(4, j + 2, StringUtils.isNotEmpty(equipmentInfo.getEquipmentType()) ? equipmentInfo.getEquipmentType() : "", contentFormat));
sheet.addCell(new Label(5, j + 2, StringUtils.isNotEmpty(equipmentInfo.getQrCode()) ? equipmentInfo.getQrCode() : "", contentFormat));
sheet.setRowView(j, 350);
//设置行高
}
}
Toast.makeText(context, "导出成功", Toast.LENGTH_SHORT).show();
writebook.write();
} 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();
}
}
}
}
} 说一下文件夹的创建，由于项目需要，生成的Excel要放到相应的文件夹里面，生成的照片又要放另一个文件夹里，并需要按格式命名。这里在根目录下面创建了一个叫“测试文件夹名”的一级文件夹，下面的“Info”二级文件夹用来放Excel，平级的“photo”文件夹放照片。
// 是否存在SD卡
private static boolean existSDCard() {
return Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState());
}// 获取存储根目录
private static String getRootStorage() {
if (existSDCard()) {
return Environment.getExternalStorageDirectory().getAbsolutePath();
} else {
return null;
}
}// 获取设施信息根目录
public static String getInfoRoot() {
if (existSDCard()) {
File root = new File(getRootStorage(), "测试文件夹名");
if (!root.exists()) {
root.mkdir();
}
String rootString = getRootStorage() + File.separator + "测试文件夹名";
File infoRoot = new File(rootString, "Info");
if (!infoRoot.exists()) {
infoRoot.mkdir();
}
return rootString + File.separator + "Info";
}
return null;
}// 获取设施信息根目录
public static String getPhotoRoot(String fileName) {
if (existSDCard()) {
File root = new File(getRootStorage(), "测试文件夹名");
if (!root.exists()) {
root.mkdir();
}
String rootString = getRootStorage() + File.separator + "测试文件夹名";
File photoRoot = new File(rootString, "Photo");
if (!photoRoot.exists()) {
photoRoot.mkdir();
}
String rootString2 = rootString + File.separator + "Photo";
File photoFile = new File(rootString2, fileName);
if (!photoFile.exists()) {
photoFile.mkdir();
}
return rootString2 + File.separator + fileName;
}
return null;
}// 将时间戳转换为时间
public static String stampToDate(String s) {
String res;
Pattern pattern = Pattern.compile("[0-9]*");
if (!pattern.matcher(s).matches()) return "";
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
long lt = new Long(s);
Date date = new Date(lt);
res = simpleDateFormat.format(date);
return res;
}最后写一下代码的调用。
// 输出excel文档
Date currentDate = new Date();
long currentTime = currentDate.getTime();
String time = ExcelUtils.stampToDate(String.valueOf(currentTime));
String filePath = ExcelUtils.getInfoRoot() + File.separator + time + "_测试Excel.xls";
ExcelUtils.initExcel(filePath, title);
ExcelUtils.writeDataToExcel(deviceInfo, filePath, this);
【Android 使用jxl创建与写入Excel】
