upstream/piflow-bundle/src/main/scala/cn/piflow/bundle/util/ExcelToJson.java
1package cn.piflow.bundle.util;
2
3
4import org.apache.hadoop.conf.Configuration;
5import org.apache.hadoop.fs.FSDataInputStream;
6import org.apache.hadoop.fs.FileSystem;
7import org.apache.hadoop.fs.Path;
8import org.apache.poi.hssf.usermodel.HSSFDateUtil;
9import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
11import org.apache.poi.poifs.filesystem.POIFSFileSystem;
12import org.apache.poi.ss.usermodel.Cell;
13import org.apache.poi.ss.usermodel.Row;
14import org.apache.poi.ss.usermodel.Sheet;
15import org.apache.poi.ss.usermodel.Workbook;
16import org.apache.poi.xssf.usermodel.XSSFWorkbook;
17
18import java.io.File;
19import java.io.FileInputStream;
20import java.io.FileNotFoundException;
21import java.io.IOException;
22import java.text.DateFormat;
23import java.text.SimpleDateFormat;
24import java.util.Date;
25import java.util.HashMap;
26import java.util.Map;
27
28public class ExcelToJson {
29
30// public static final String XLSX = ".xlsx";
31// public static final String XLS=".xls";
32
33 public static final Configuration configuration = new Configuration();
34
35
36 public static net.sf.json.JSONArray readExcel(String pathStr,String hdfsUrl) throws IOException {
37
38 configuration.set("fs.defaultFS",hdfsUrl);
39
40 if (pathStr.endsWith(".xlsx")) {
41 return readXLSX(pathStr);
42 }else {
43 return readXLS(pathStr);
44 }
45
46// return new net.sf.json.JSONArray();
47 }
48
49
50 public static net.sf.json.JSONArray readXLSX(String pathStr) throws IOException{
51
52 FileSystem fs = FileSystem.get(configuration);
53 FSDataInputStream fdis = fs.open(new Path(pathStr));
54
55 System.out.println("xlsx");
56
57 Workbook book = new XSSFWorkbook(fdis);
58 Sheet sheet = book.getSheetAt(0);
59 return read(sheet, book);
60 }
61
62
63 public static net.sf.json.JSONArray readXLS(String pathStr) throws IOException{
64
65 FileSystem fs = FileSystem.get(configuration);
66 FSDataInputStream fdis = fs.open(new Path(pathStr));
67
68 System.out.println("xls");
69
70 POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fdis);
71 Workbook book = new HSSFWorkbook(poifsFileSystem);
72 Sheet sheet = book.getSheetAt(0);
73 return read(sheet, book);
74 }
75
76 public static net.sf.json.JSONArray read(Sheet sheet, Workbook book) throws IOException{
77 int rowStart = sheet.getFirstRowNum(); // 首行下标
78 int rowEnd = sheet.getLastRowNum(); // 尾行下标
79 // 如果首行与尾行相同,表明只有一行,直接返回空数组
80 if (rowStart == rowEnd) {
81 book.close();
82 return new net.sf.json.JSONArray();
83 }
84 // 获取第一行JSON对象键
85 Row firstRow = sheet.getRow(rowStart);
86 int cellStart = firstRow.getFirstCellNum();
87 int cellEnd = firstRow.getLastCellNum();
88 Map<Integer, String> keyMap = new HashMap<Integer, String>();
89 for (int j = cellStart; j < cellEnd; j++) {
90 keyMap.put(j,getValue(firstRow.getCell(j), rowStart, j, book, true).toLowerCase().replaceAll(" ","_"));
91 }
92
93 // 获取每行JSON对象的值
94 net.sf.json.JSONArray array = new net.sf.json.JSONArray();
95 for(int i = rowStart+1; i <= rowEnd ; i++) {
96 Row eachRow = sheet.getRow(i);
97 net.sf.json.JSONObject obj = new net.sf.json.JSONObject();
98 StringBuffer sb = new StringBuffer();
99 for (int k = cellStart; k < cellEnd; k++) {
100 if (eachRow != null) {
101 String val = getValue(eachRow.getCell(k), i, k, book, false);
102 sb.append(val); // 所有数据添加到里面,用于判断该行是否为空
103 obj.put(keyMap.get(k),val);
104 }
105 }
106 if (sb.toString().length() > 0) {
107 array.add(obj);
108 }
109 }
110 book.close();
111 return array;
112 }
113
114 /**
115 * 获取每个单元格的数据
116 * @param cell 单元格对象
117 * @param rowNum 第几行
118 * @param index 该行第几个
119 * @param book 主要用于关闭流
120 * @param isKey 是否为键:true-是,false-不是。 如果解析Json键,值为空时报错;如果不是Json键,值为空不报错
121 * @return
122 * @throws IOException
123 */
124 public static String getValue(Cell cell, int rowNum, int index, Workbook book, boolean isKey) throws IOException{
125
126 // 空白或空
127 if (cell == null || cell.getCellType()==Cell.CELL_TYPE_BLANK ) {
128 if (isKey) {
129 book.close();
130 throw new NullPointerException(String.format("the key on row %s index %s is null ", ++rowNum,++index));
131 }else{
132 return "";
133 }
134 }
135 // 0. 数字 类型
136 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
137 if (HSSFDateUtil.isCellDateFormatted(cell)) {
138 Date date = cell.getDateCellValue();
139 DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
140 return df.format(date);
141 }
142 String val = cell.getNumericCellValue()+"";
143 val = val.toUpperCase();
144 if (val.contains("E")) {
145 val = val.split("E")[0].replace(".", "");
146 }
147 return val;
148 }
149 // 1. String类型
150 if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
151 String val = cell.getStringCellValue();
152 if (val == null || val.trim().length()==0) {
153 if (book != null) {
154 book.close();
155 }
156 return "";
157 }
158 return val.trim();
159 }
160 // 2. 公式 CELL_TYPE_FORMULA
161 if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
162 return cell.getStringCellValue();
163 }
164 // 4. 布尔值 CELL_TYPE_BOOLEAN
165 if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
166 return cell.getBooleanCellValue()+"";
167 }
168 // 5. 错误 CELL_TYPE_ERROR
169 return "";
170 }
171
172
173
174
175
176
177
178
179
180}