Source code

Download source archive ↓

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}