app的build.gradle里添加依赖
implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'
下面的代码展示了从assets中读取一个配置文件，然后导出成excel文件
public class ExcelUtils {
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(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(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 filePath 存放excel文件的路径（path/demo.xls）
* @param sheetName Excel表格的表名
* @param colName excel中包含的列名（可以有多个）
*/
public static void initExcel(String filePath, String sheetName, String[] colName) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(filePath);
if (!file.exists()) {
file.createNewFile();
} else {
return;
}
workbook = Workbook.createWorkbook(file);
//设置表格的名字
WritableSheet sheet = workbook.createSheet(sheetName, 0);
//创建标题栏
sheet.addCell((WritableCell) new Label(0, 0, filePath, arial14format));
for (int col = 0; col < colName.length; col++) {
sheet.addCell(new Label(col, 0, colName[col], arial10format));
}
//设置行高
sheet.setRowView(0, 340);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 将制定类型的List写入Excel中
*
* @param objList 待写入的list
* @param fileName
* @param mContext
* @param <T>
*/
@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context mContext) {
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++) {
LanguageModel demoBean = (LanguageModel) objList.get(j);
List<String> list = new ArrayList<>();
list.add(demoBean.getVarName());
list.add(demoBean.getCnStr());
list.add(demoBean.getEnStr());
list.add(demoBean.getViStr());
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) {
//设置列宽
sheet.setColumnView(i, list.get(i).length() + 8);
} else {
//设置列宽
sheet.setColumnView(i, list.get(i).length() + 5);
}
}
//设置行高
sheet.setRowView(j + 1, 350);
}
writebook.write();
workbook.close();
//LogUtil.e("导出Excel成功");
} 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();
}
}
}
}
}
/**
* 将lang.properties导出到excel文件
*/
public static void exportExcel(String fileName) {
try {
Context context = ContextUtils.getCurApplication();
InputStream inputStream = context.getAssets().open("lang.properties");
BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
String line;
Map<String,LanguageModel> map = new HashMap<>();
while ((line = reader.readLine()) != null) {
if (line.startsWith("#")) continue;
String[] arr = line.split("=");
String varName = arr[0];
String str = arr[1];
if (varName.endsWith("_zh") || varName.endsWith("_en") || varName.endsWith("_vi")) {
String tag = varName.substring(0,varName.length()-3);
if (!map.containsKey(tag)) {
LanguageModel model = new LanguageModel();
model.setVarName(tag);
map.put(tag, model);
}
if (varName.endsWith("_zh")) {
map.get(tag).setCnStr(str);
} else if (varName.endsWith("_en")) {
map.get(tag).setEnStr(str);
} else if (varName.endsWith("_vi")) {
map.get(tag).setViStr(str);
}
}
}
// map to list
List<LanguageModel> list = new ArrayList<>();
for (String tag: map.keySet()) {
list.add(map.get(tag));
}
String sheetName = "多语言对照表";
String[] title = {"变量名", "中文", "英文", "越南文"};
initExcel(fileName,sheetName, title);
writeObjListToExcel(list,fileName,context);
} catch (IOException e) {
e.printStackTrace();
}
}
// 一个变量名，对应3种语言的字符串
public static final class LanguageModel {
private String varName="";
private String cnStr="";
private String enStr="";
private String viStr="";
public String getVarName() {
return varName;
}
public void setVarName(String varName) {
this.varName = varName;
}
public String getCnStr() {
return cnStr;
}
public void setCnStr(String cnStr) {
this.cnStr = cnStr;
}
public String getEnStr() {
return enStr;
}
public void setEnStr(String enStr) {
this.enStr = enStr;
}
public String getViStr() {
return viStr;
}
public void setViStr(String viStr) {
this.viStr = viStr;
}
}
}
使用示例：
if (Environment.getExternalStorageState().equals(
Environment.MEDIA_MOUNTED)) {
String filePath = "/sdcard/mydir";
File file = new File(filePath);
if (!file.exists()) {
file.mkdirs();
}
String fileName = "test.xls";
String fullPath = filePath + File.pathSeparator + fileName;
ExcelUtils.exportExcel(fullPath);
} else {
Toast.makeText(this, "请安装SD卡", Toast.LENGTH_SHORT).show();
}
网友评论