赞
踩
记录获取文件服务器(网络地址)中的excel转换为json格式传送到前端
pom文件
- <!--Excel to Json-->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.17</version>
- </dependency>
- <dependency>
- <groupId>com.google.code.gson</groupId>
- <artifactId>gson</artifactId>
- <version>2.8.2</version>
- </dependency>
- <dependency>
- <groupId>net.sf.json-lib</groupId>
- <artifactId>json-lib</artifactId>
- <version>2.4</version>
- <classifier>jdk15</classifier>
- </dependency>
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>fastjson</artifactId>
- <version>1.2.56</version>
- <scope>compile</scope>
- </dependency>
- <!--Excel to Json End-->

Controller部分
- /**
- * 读取Excel转换成 Json
- *
- */
- @PostMapping("/previewExcelToJson")
- @ResponseBody
- public String previewExcel(@RequestBody String path) {
- try {
- JSONArray dataArray = new JSONArray();
- URL httpurl=new URL(URLDecoder.decode(path, "UTF-8"));
- InputStream is;
- HttpURLConnection httpConn=(HttpURLConnection)httpurl.openConnection();
- httpConn.setDoOutput(true);// 使用 URL 连接进行输出
- httpConn.setDoInput(true);// 使用 URL 连接进行输入
- httpConn.setUseCaches(false);// 忽略缓存
- httpConn.setRequestMethod("GET");// 设置URL请求方法
- //可设置请求头
- httpConn.setRequestProperty("Content-Type", "application/octet-stream");
- httpConn.setRequestProperty("Connection", "Keep-Alive");// 维持长连接
- httpConn.setRequestProperty("Charset", "UTF-8");
- httpConn.connect();
- if (httpConn.getResponseCode() >= 400 ) {
- is = httpConn.getErrorStream();
- }
- else{
- is = httpConn.getInputStream();
- }
- InputStream inStream =is;
-
- XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inStream);
- // 循环工作表Sheet
- for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
- XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
- String sheetName = xssfSheet.getSheetName();
- if (xssfSheet == null) {
- continue;
- }
- //当前sheet的json文件
- JSONObject sheetJson = new JSONObject();
- //当前sheet的array,作为sheetJson 的value值
- net.sf.json.JSONArray sheetArr = new net.sf.json.JSONArray();
- //sheet的第一行,获取作为json的key值
- JSONArray key = new JSONArray();
- int xssfLastRowNum = xssfSheet.getLastRowNum();
- // 循环行Row
- for (int rowNum = 0; rowNum <= xssfLastRowNum; rowNum++) {
- XSSFRow xssfRow = xssfSheet.getRow(rowNum);
- if (xssfRow == null) {
- continue;
- }
-
- // 循环列Cell,在这里组合json文件
- int firstCellNum = xssfRow.getFirstCellNum();
- int lastCellNum = xssfRow.getLastCellNum();
- JSONObject rowJson = new JSONObject();
- for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
- XSSFCell cell = null;
- try {
- cell = xssfRow.getCell(cellNum);
- if (cell == null) {
- rowJson.put(key.getString(cellNum), "");
- continue;
- }
- if (rowNum == 0)
- key.add(toString(cell));
- else {
- //若是列号超过了key的大小,则跳过
- if (cellNum >= key.size()) continue;
- rowJson.put(key.getString(cellNum), toString(cell));
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- if (rowJson.keySet().size() > 0)
- sheetArr.add(rowJson);
- }
- sheetJson.put(sheetName, shuffleData(sheetArr));
- dataArray.add(sheetJson);
- }
- return dataArray.toString();
- } catch (Exception e) {
- e.printStackTrace();
- return JSONNull.getInstance().toString();
- }
- }

Controller中用到的方法
- /**
- * 解析json
- *
- * @param cell
- * @return
- */
- private static Object toString(XSSFCell cell) {
- switch (cell.getCellTypeEnum()) {
- case _NONE:
- cell.setCellType(CellType.STRING);
- return "";
- case NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
- return sdf.format(cell.getDateCellValue());
- }
- cell.setCellType(CellType.STRING);
- return cell.getStringCellValue();
- case STRING:
- String val = cell.getStringCellValue();
- if ("无".equalsIgnoreCase(val)) return "";
- //将其中的map格式和数组格式的字符串,转化为相应的数据类型
- if (val.indexOf("{") > -1) {
- JSONObject jsonObject = JSONObject.fromObject(val);
- Map<String, Integer> mapJson = JSONObject.fromObject(jsonObject);
- return mapJson;
- }
- if (val.indexOf("[") > -1) {
- val = val.substring(1, val.length() - 1);
- String[] array = val.split(",");
- return array;
- }
- return val;
- case FORMULA:
- return cell.getCellFormula();
- case BLANK:
- return "";
- case BOOLEAN:
- return cell.getBooleanCellValue() + "";
- case ERROR:
- return "非法字符";
- default:
- return "未知字符";
- }
- }
-
- /**
- * 输出数据
- */
- private static net.sf.json.JSONArray shuffleData(net.sf.json.JSONArray sheetArr) {
- net.sf.json.JSONArray array = new net.sf.json.JSONArray();
- for (int i = 0; i < sheetArr.size(); i++) {
- JSONObject object = sheetArr.getJSONObject(i);
- int count = 0;
- int length = 0;
- for (Object key : object.keySet()) {
- Object o = object.get((String) key);
- length++;
- boolean b = StringUtils.isEmpty(o.toString());
- if (b) {
- count++;
- }
- }
- if (count != length) {
- array.add(object);
- }
- }
- return array;
- }

以上 记录一下 大部分代码是查询的 然后有一些问题 进行修改了一下 所以弄成原创吧 哈哈
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。