最近有个需求,将产品配置的Excel文件解析成json文件,然后json文件和表情图片打包成gzip的表情包,东西比较多,我分成两篇文章来介绍。这篇文章讲的是如何读取Excel文件,将数据转换成json。
第一步:下载POI相关jar和Gson.jar
下载地址在:
http://poi.apache.org/download.html
Gson.jar下载地址
http://download.csdn.net/detail/u010637692/8348917
下载完成后将
这几个jar添加到项目依赖
第二步:读取excel数据生成json数据
for (File memeFile : childDirectory.listFiles()) {
String tempFilePath = memeFile.getAbsolutePath();
if (memeFile.getAbsolutePath().indexOf(".jpg") != -1
|| memeFile.getAbsolutePath().indexOf(".png") != -1) {
imgPath = memeFile.getPath();
imgPath = memeFile.getAbsolutePath();
}
}
// 第一步将xls生成json文件
String memeDirectoryName = childDirectory.getAbsolutePath();
memeDirectoryName=java.net.URLDecoder.decode(memeDirectoryName,"UTF-8");
String mutilLanFilePath = memeDirectoryName + "/multi_language.xls";
String examTextFilePath = memeDirectoryName + "/example.xls";
String targetMetajsonFilePath = memeDirectoryName + "/meta.json";
readMemeTextToJson(mutilLanFilePath, examTextFilePath, targetMetajsonFilePath);}
public static void readMemeTextToJson(String multiLanFilePath, String examTextFilePath, String targetJsonFilePath) {
ArrayList<MemeTextExample> examples = new ArrayList<>();
String lang = "en";
try {
// read language example
Sheet sheetExample = readSheet(multiLanFilePath);
int rowCount = sheetExample.getLastRowNum() + 1;
// 从第一行开始,第0行是标题
for (int r = 1; r < rowCount; r++) {
Row row = sheetExample.getRow(r);
if (row.getCell(0).getStringCellValue().equals(lang)) {
MemeTextExample mte = new MemeTextExample();
mte.setLang(row.getCell(0).getStringCellValue());
mte.setExampleId(row.getCell(1).getStringCellValue());
examples.add(mte);
}
}
// read example meme text
Sheet sheetText = readSheet(examTextFilePath);
int txtRowCount = sheetText.getLastRowNum() + 1;
// 从第一行开始,第0行是标题
for (MemeTextExample mte : examples) {
ArrayList<MemeText> memeTexts = new ArrayList<>();
for (int r = 1; r < txtRowCount; r++) {
Row row = sheetText.getRow(r);
if (row.getCell(0).getStringCellValue().equals(mte.getExampleId())) {
MemeText mt = new MemeText();
mt.setExampleId(row.getCell(0).getStringCellValue());
mt.setContent(row.getCell(6).getStringCellValue());
mt.setTxtId(row.getCell(1).getStringCellValue());
mt.setTxtType(row.getCell(2).getStringCellValue());
mt.setWidth(row.getCell(5).getNumericCellValue());
mt.setX(row.getCell(3).getNumericCellValue());
mt.setY(row.getCell(4).getNumericCellValue());
memeTexts.add(mt);
}
}
mte.setTexts(memeTexts);
}
Gson gson = new Gson();
String gsonString = gson.toJson(examples);
System.out.println(gsonString);
FileUtils.writeStringToFile(targetJsonFilePath, gsonString);
} catch (Exception e) {
if (e != null) {
e.printStackTrace();
FileUtils.appendTextMethod(currentDirectory + "/result.txt", e.getMessage());
}
}
}
private static Sheet readSheet(String fileName) {
boolean isE2007 = false; // 判断是否是excel2007格式
if (fileName.endsWith("xlsx"))
isE2007 = true;
try {
InputStream input = new FileInputStream(fileName); // 建立输入流
Workbook wb = null;
// 根据文件格式(2003或者2007)来初始化
if (isE2007)
wb = new XSSFWorkbook(input);
else
wb = new HSSFWorkbook(input);
Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
return sheet;
} catch (Exception e) {
if (e != null) {
e.printStackTrace();
FileUtils.appendTextMethod(currentDirectory + "/result.txt", e.getMessage());
}
return null;
}
}
//在文件尾换行写入文本
public static void appendTextMethod(String fileName, String content) {
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
// 打开一个写文件器,构造函数中的第二个参数true表示以追加形式写文件
FileWriter writer = new FileWriter(fileName, true);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
System.out.println(df.format(new Date()));// new Date()为获取当前系统时间
writer.write(df.format(new Date()) + System.getProperty("line.separator") + content
+ System.getProperty("line.separator"));
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//覆盖写入文本
public static void writeStringToFile(String filePath, String json) {
try {
FileOutputStream fos = new FileOutputStream(filePath);
fos.write(json.getBytes());
fos.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}