From c007f0ca1785db093d48f4846cda82fe8e955765 Mon Sep 17 00:00:00 2001
From: kimi <kimi42345@gmail.com>
Date: 星期三, 27 五月 2020 09:59:29 +0800
Subject: [PATCH] merage
---
src/main/java/com/highdatas/mdm/util/ExcelUtil.java | 128 ++++++++++++++++++++++++++++--------------
1 files changed, 85 insertions(+), 43 deletions(-)
diff --git a/src/main/java/com/highdatas/mdm/util/ExcelUtil.java b/src/main/java/com/highdatas/mdm/util/ExcelUtil.java
index 7f25671..48cb1c5 100644
--- a/src/main/java/com/highdatas/mdm/util/ExcelUtil.java
+++ b/src/main/java/com/highdatas/mdm/util/ExcelUtil.java
@@ -13,6 +13,8 @@
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
+import java.math.BigDecimal;
+import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
@@ -39,49 +41,49 @@
List list = new ArrayList<>();
Workbook work = null;
- try{
- // 鍒涘缓excel宸ヤ綔绨�
- work = getWorkbook(in, fileName);
- if (null == work) {
- throw new Exception("鍒涘缓Excel宸ヤ綔钖勪负绌猴紒");
- }
-
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
-
- for (int i = 0; i < work.getNumberOfSheets(); i++) {
-
- sheet = work.getSheetAt(i);
- if(sheet == null) {
- continue;
+ try {
+ // 鍒涘缓excel宸ヤ綔绨�
+ work = getWorkbook(in, fileName);
+ if (null == work) {
+ throw new Exception("鍒涘缓Excel宸ヤ綔钖勪负绌猴紒");
}
- // 婊よ繃绗竴琛屾爣棰�
- for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
- row = sheet.getRow(j);
- if (row == null || row.getFirstCellNum() == j) {
+ Sheet sheet = null;
+ Row row = null;
+ Cell cell = null;
+
+ for (int i = 0; i < work.getNumberOfSheets(); i++) {
+
+ sheet = work.getSheetAt(i);
+ if (sheet == null) {
continue;
}
- ViewMappingItem item = new ViewMappingItem();
- Cell preCell = row.getCell(0);
- if (preCell != null) {
- item.setPre(preCell.getStringCellValue());
- }
+ // 婊よ繃绗竴琛屾爣棰�
+ for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
+ row = sheet.getRow(j);
+ if (row == null || row.getFirstCellNum() == j) {
+ continue;
+ }
- Cell fixCell = row.getCell(1);
- if (fixCell != null) {
- item.setFix(fixCell.getStringCellValue());
- }
+ ViewMappingItem item = new ViewMappingItem();
+ Cell preCell = row.getCell(0);
+ if (preCell != null) {
+ item.setPre(preCell.getStringCellValue());
+ }
- list.add(item);
+ Cell fixCell = row.getCell(1);
+ if (fixCell != null) {
+ item.setFix(getValByCell(fixCell).toString());
+ }
+
+ list.add(item);
+ }
}
- }
- }catch (Exception e) {
+ } catch (Exception e) {
e.printStackTrace();
return null;
- }finally {
+ } finally {
try {
work.close();
} catch (IOException e) {
@@ -91,8 +93,48 @@
return list;
}
+ private static Object getValByCell(Cell cell) {
+ if (cell == null) {
+ return "";
+ }
+ Object obj = null;
+ switch (cell.getCellTypeEnum()) {
+ case BOOLEAN:
+ obj = cell.getBooleanCellValue();
+ break;
+ case ERROR:
+ obj = cell.getErrorCellValue();
+ break;
+ case FORMULA:
+ try {
+ obj = String.valueOf(cell.getStringCellValue());
+ } catch (IllegalStateException e) {
+ String valueOf = String.valueOf(cell.getNumericCellValue());
+ BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));
+ bd = bd.setScale(2, RoundingMode.HALF_UP);
+ obj = bd;
+ }
+ break;
+ case NUMERIC:
+ obj = cell.getNumericCellValue();
+ break;
+ case STRING:
+ String value = String.valueOf(cell.getStringCellValue());
+ value = value.replace(" ", "");
+ value = value.replace("\n", "");
+ value = value.replace("\t", "");
+ obj = value;
+ break;
+ default:
+ break;
+ }
+ return obj;
+
+ }
+
/**
* 鍒ゆ柇鏂囦欢鏍煎紡
+ *
* @param in
* @param fileName
* @return
@@ -102,7 +144,7 @@
Workbook book = null;
String filetype = fileName.substring(fileName.lastIndexOf("."));
- if(".xls".equals(filetype)) {
+ if (".xls".equals(filetype)) {
book = new HSSFWorkbook(in);
} else if (".xlsx".equals(filetype)) {
book = new XSSFWorkbook(in);
@@ -134,7 +176,7 @@
//寰幆瀛楁鍚嶆暟缁勶紝鍒涘缓鏍囬琛�
Row row = sheet.createRow(0);
- for (int j = 0; j< fieldList.size(); j++){
+ for (int j = 0; j < fieldList.size(); j++) {
//鍒涘缓鍒�
SysField sysField = fieldList.get(j);
Cell cell = row.createCell(j);
@@ -143,13 +185,13 @@
cell.setCellValue(sysField.getAlias());
}
//鍒涘缓鏅�氳
- for (int i = 0;i<datalist.size();i++){
+ for (int i = 0; i < datalist.size(); i++) {
//鍥犱负绗竴琛屽凡缁忕敤浜庡垱寤烘爣棰樿锛屾晠浠庣浜岃寮�濮嬪垱寤�
- row = sheet.createRow(i+1);
+ row = sheet.createRow(i + 1);
//濡傛灉鏄涓�琛屽氨璁╁叾涓烘爣棰樿
Map<String, Object> oneData = datalist.get(i);
- for (int j = 0;j<fieldList.size();j++){
+ for (int j = 0; j < fieldList.size(); j++) {
//鍒涘缓鍒�
SysField sysField = fieldList.get(j);
@@ -164,7 +206,7 @@
response.setCharacterEncoding("UTF-8");
response.setHeader("content-type", "application/octet-stream;charset=UTF-8");
//榛樿Excel鍚嶇О
- response.setHeader("Content-Disposition", "attachment;fileName="+ URLEncoder.encode("mapping鏁版嵁.xlsx","UTF-8"));
+ response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("mapping鏁版嵁.xlsx", "UTF-8"));
try {
response.flushBuffer();
@@ -175,15 +217,15 @@
}
- private static String transCellType(Object value){
+ private static String transCellType(Object value) {
String str = null;
- if (value instanceof Date){
+ if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
str = sdf.format(date);
- }else{
+ } else {
str = String.valueOf(value);
- if (str == "null"){
+ if (str == "null") {
str = "";
}
}
--
Gitblit v1.8.0