Excel文件转换成json文件 ExcelToJson
Excel文件转换成json文件 ExcelToJson
这是我发的第一篇,代码写的有点乱,也没注释,哈哈哈,测试没问题,拿走就能用,没有验证是否有合并单元格,所以就不要拿特别炫酷的表格来试了,转出来的文件数据不对,有时间会把这部分进行完善
pom文件 , 导入fastjson和poi
<dependencies><dependency><groupId>com.alibabagroupId><artifactId>fastjsonartifactId><version>1.2.7version>dependency><dependency><groupId>org.apache.poigroupId><artifactId>poiartifactId><version>3.9version>dependency><dependency><groupId>org.apache.poigroupId><artifactId>poi-ooxmlartifactId><version>3.9version>dependency>dependencies>
main方法
import java.util.Scanner;
public class Demo {public static void main(String[] args) throws Exception{//input写目录,扫描目录下所有xls和xlsx文件进行转换//input写具体文件,直接转换成json文件//output写生成json文件的保存路径ExcelToJson.excelToJson(input , output);}
}
ExcelToJson 类
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import xyz.wbls.javautils.FileUtils;import java.io.*;public class ExcelToJson {/*** excelToJson方法** @param input 要转换的excel文件或者excel所在文件夹路径* @param output 转换完成后保存文件路径* @throws IOException 异常*/public static void excelToJson(String input, String output) throws IOException {File in = new File(input);File out = new File(output);if (!out.exists()) out.mkdirs();if (in.isDirectory()) {//获取文件夹下所有xls和xlsx后缀的文件//FileUtils.getFiles(file , ...ext) 是另一篇文章// https://blog.csdn.net/qq_25278517/article/details/107610175File[] files = FileUtils.getFiles(in, "xls", "xlsx");for (File file : files) {saveFile(toJson(file.getCanonicalPath()), out, file.getName());}} else saveFile(toJson(input), out, in.getName());if (bufferedWriter != null) bufferedWriter.close();}private static BufferedWriter bufferedWriter;/*** @param jsonString 转换完成后的json字符串* @param out 转换完成后json文件保存路径* @param fileName 文件名* @throws IOException 异常*/private static void saveFile(String jsonString, File out, String fileName) throws IOException {if (jsonString == null) return;File file = new File(out.getCanonicalPath() + "\\" + fileName + ".json");file.createNewFile();System.out.println(file.getCanonicalPath());bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "utf-8"));bufferedWriter.write(jsonString);bufferedWriter.flush();}/*** @param filePath 文件路径* @return 返回转换以后的json字符串* @throws IOException 异常*/private static String toJson(String filePath) throws IOException {Workbook wb;if (filePath == null) return null;FileInputStream fis = new FileInputStream(filePath);String ext = filePath.substring(filePath.lastIndexOf("."));if (ext.equals(".xls")) wb = new HSSFWorkbook(fis);else if (ext.equals(".xlsx")) wb = new XSSFWorkbook(fis);else return null;int sheetNum = wb.getNumberOfSheets();JSONObject jsonObject = new JSONObject();for (int s = 0; s < sheetNum; s++) {Sheet sheet = wb.getSheetAt(s);int rowCount = sheet.getPhysicalNumberOfRows();if (rowCount <= 1) continue;int firstRow = 0;for (int i = 0; i < rowCount; i++) {if (!isFirstRow(sheet, i)) continue;firstRow = i;break;}Row row1 = sheet.getRow(firstRow);int colCount = row1.getPhysicalNumberOfCells();JSONArray jsonArray = new JSONArray();for (int i = firstRow + 1; i < rowCount; i++) {Row row = sheet.getRow(i);if (row != null) {JSONObject rowObj = new JSONObject(true);for (int j = 0; j < colCount; j++) {Cell cell = row.getCell(j);if (cell != null) {switch (cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC:rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());break;case Cell.CELL_TYPE_FORMULA:if (DateUtil.isCellDateFormatted(cell)) {rowObj.put(row1.getCell(j).getStringCellValue(), cell.getDateCellValue());} else {rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());}break;case Cell.CELL_TYPE_STRING:rowObj.put(row1.getCell(j).getStringCellValue(), cell.getStringCellValue());break;default:rowObj.put(row1.getCell(j).getStringCellValue(), "");}} else rowObj.put(row.getCell(j).getStringCellValue(), "");}jsonArray.add(rowObj);}}jsonObject.put(sheet.getSheetName(), jsonArray);}return JSONArray.toJSONString(jsonObject, true);}/*** 进行简单的判断是否可以做首行 , 过滤表格顶部有合并单元格* 对每一行做判断 ,得到以一个每列都不为空的行 ,作为首行* @param sheet sheet 工作表* @param number number 当前工作表的第n行* @return 可以做为首行返回true , 不可以返回false*/public static boolean isFirstRow(Sheet sheet,int number){Row row = sheet.getRow(number);int physicalNumberOfCells = row.getPhysicalNumberOfCells();boolean isFirstRow = false;for (int j = 0; j < physicalNumberOfCells; j++) {if (row.getCell(j) == null) break;else if (row.getCell(j).getStringCellValue().equals("")) break;else if (j == physicalNumberOfCells - 1) isFirstRow = true;}return isFirstRow;}
}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！