From 5b623ba0cf530207953c4ec708d6d3706634c139 Mon Sep 17 00:00:00 2001
From: chenjiahe <763432473@qq.com>
Date: 星期一, 04 九月 2023 10:55:08 +0800
Subject: [PATCH] 新版excel
---
src/main/java/com/hx/util/ExcelUtil.java | 487 ++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 362 insertions(+), 125 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 5cbea70..463d446 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,14 +1,11 @@
package com.hx.util;
+import com.alibaba.fastjson.JSON;
import com.hx.exception.TipsException;
-import org.apache.poi.POIXMLDocument;
+import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
-import org.apache.poi.openxml4j.opc.OPCPackage;
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
@@ -60,27 +57,30 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
- for (int i = 0; i < headList.length; i++) {
+ HSSFCell cell;
+ for (int i = 0; i < headList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
- Map<String, Object> dataMap = dataList.get(n);
+ row_value = sheet.createRow(n + 1);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
}
@@ -116,7 +116,7 @@
*/
public static File createExcel(String[] headList, String[] fieldList,
List<Map<String, Object>> dataList) throws Exception {
- File file = File.createTempFile("temp", ".xls");
+ File file = File.createTempFile("temp", ".xlsx");
try{
// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
HSSFWorkbook workbook = new HSSFWorkbook();
@@ -128,27 +128,29 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
+ HSSFCell cell;
for (int i = 0; i < headList.length; i++) {
-
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
- Map<String, Object> dataMap = dataList.get(n);
+ row_value = sheet.createRow(n + 1);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
}
@@ -197,8 +199,8 @@
//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
HSSFCellStyle boderStyle = workbook.createCellStyle();
//鍨傜洿灞呬腑
- boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER); // 鍒涘缓涓�涓眳涓牸寮�
// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
@@ -207,32 +209,126 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
+ HSSFCell cell;
for (int i = 0; i < headList.length; i++) {
//楂樺害
row.setHeight(height.shortValue());
sheet.setColumnWidth(i,width);
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
cell.setCellStyle(boderStyle);
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
+ row_value = sheet.createRow(n + 1);
row_value.setHeight(height.shortValue());
- Map<String, Object> dataMap = dataList.get(n);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
sheet.setColumnWidth(i,width);
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ // 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+ cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+ cell.setCellStyle(boderStyle);
+ }
+ // ===============================================================
+ }
+ }
+
+ // 鏂板缓涓�杈撳嚭鏂囦欢娴�
+ FileOutputStream fOut = new FileOutputStream(file);
+ // 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
+ workbook.write(fOut);
+ fOut.flush();
+ // 鎿嶄綔缁撴潫锛屽叧闂枃浠�
+ fOut.close();
+ }catch (Exception e){
+
+ }finally {
+ file.deleteOnExit();
+ }
+ return file;
+ }
+
+ /**鏃犻檺鍒惰鏁扮敓鎴恊cxel锛岀敓鎴愪复鏃舵枃浠�
+ * @param headList
+ * Excel鏂囦欢Head鏍囬闆嗗悎
+ * @param fieldList
+ * Excel鏂囦欢Field鏍囬闆嗗悎 鏍规嵁field鏉ュ鎵句綅缃～鍏呰〃鏍�
+ * @param dataList
+ * Excel鏂囦欢鏁版嵁鍐呭閮ㄥ垎
+ * @param height 鍗曞厓鏍奸珮搴︼紝榛樿450
+ * @param width 鍗曞厓鏍煎搴︼紝榛樿5000
+ * @throws Exception
+ */
+ public static File createXSSExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
+ ,Integer height,Integer width) throws Exception {
+ File file = File.createTempFile("temp", ".xls");
+ try{
+
+ if(height == null){
+ height = 450;
+ }
+ if(width == null){
+ width = 5000;
+ }
+ // 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
+ //XSSFWorkbook workbook = new XSSFWorkbook();
+ SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
+
+ //鍚堝苟鐨勫崟鍏冩牸鏍峰紡
+ CellStyle boderStyle = workbook.createCellStyle();
+ //鍨傜洿灞呬腑
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+
+ // 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+ // 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+ // HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+ Sheet sheet = workbook.createSheet();
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
+ Row row = sheet.createRow(0);
+ // ===============================================================
+ Cell cell;
+ for (int i = 0; i < headList.length; i++) {
+ //楂樺害
+ row.setHeight(height.shortValue());
+ sheet.setColumnWidth(i,width);
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+ cell = row.createCell(i);
+ // 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ // 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+ cell.setCellValue(headList[i]);
+ cell.setCellStyle(boderStyle);
+ }
+ // ===============================================================
+ if (dataList != null) {
+ Row row_value;
+ Map<String, Object> dataMap;
+ for (int n = 0; n < dataList.size(); n++) {
+ // 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
+ row_value = sheet.createRow(n + 1);
+ row_value.setHeight(height.shortValue());
+ dataMap = dataList.get(n);
+ // ===============================================================
+ for (int i = 0; i < fieldList.length; i++) {
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+ sheet.setColumnWidth(i,width);
+ cell = row_value.createCell(i);
+ // 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
cell.setCellStyle(boderStyle);
@@ -337,9 +433,9 @@
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -355,24 +451,24 @@
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ if(cell.getCellType() != CellType.BOOLEAN){
arrayString.add(cell.getStringCellValue().trim());
}else{
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -428,9 +524,9 @@
XSSFCell cell = xssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -446,24 +542,24 @@
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ if(cell.getCellType() != CellType.BOOLEAN){
arrayString.add(cell.getStringCellValue().trim());
}else{
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -482,17 +578,9 @@
/**鍒ゆ柇excel鐨勭増鏈�*/
- public static Workbook create(InputStream inp) throws Exception {
- if (!inp.markSupported()) {
- inp = new PushbackInputStream(inp, 8);
- }
- if (POIFSFileSystem.hasPOIFSHeader(inp)) {
- return new HSSFWorkbook(inp);
- }
- if (POIXMLDocument.hasOOXMLHeader(inp)) {
- return new XSSFWorkbook(OPCPackage.open(inp));
- }
- throw new IllegalArgumentException("浣犵殑excel鐗堟湰鐩墠poi瑙ｆ瀽涓嶄簡");
+ public static Workbook create(InputStream inp) throws IOException {
+ //杩欐牱鍐� excel 鑳藉吋瀹�03鍜�07
+ return WorkbookFactory.create(inp);
}
/**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
@@ -513,12 +601,20 @@
Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
// 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ Sheet hssfsheet;
+ Row hssfrow;
+ List<String> arrayString;
+ Cell cell;
+ short format;
+ Date d;
+ DateFormat formater;
+ HSSFDataFormatter dataFormatter;
for(int s=0;s<book.getNumberOfSheets();s++) {
- Sheet hssfsheet = book.getSheetAt(s);
+ hssfsheet = book.getSheetAt(s);
int col = 0;
// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
- Row hssfrow = hssfsheet.getRow(j);
+ hssfrow = hssfsheet.getRow(j);
if(hssfrow!=null){
if(j == 0) {
col = hssfrow.getPhysicalNumberOfCells();
@@ -528,43 +624,43 @@
}
}
// 鍗曡鏁版嵁
- List<String> arrayString = new ArrayList<>();
+ arrayString = new ArrayList<>();
for (int i = 0; i < col; i++) {
- Cell cell = hssfrow.getCell(i);
+ cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- short format = cell.getCellStyle().getDataFormat();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- Date d = cell.getDateCellValue();
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- Date d = cell.getDateCellValue();
+ d = cell.getDateCellValue();
//DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
- HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
@@ -603,13 +699,21 @@
List<List<String>> list = new ArrayList<>();
Workbook book = create(new BufferedInputStream(file.getInputStream()));
+ Sheet hssfsheet;
+ Row hssfrow;
+ List<String> arrayString;
+ Cell cell;
+ short format;
+ Date d;
+ DateFormat formater;
+ HSSFDataFormatter dataFormatter;
// 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
for(int s=0;s<book.getNumberOfSheets();s++) {
- Sheet hssfsheet = book.getSheetAt(s);
+ hssfsheet = book.getSheetAt(s);
int col = 0;
// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
- Row hssfrow = hssfsheet.getRow(j);
+ hssfrow = hssfsheet.getRow(j);
if(hssfrow!=null){
if(j == 0) {
col = hssfrow.getPhysicalNumberOfCells();
@@ -619,43 +723,43 @@
}
}
// 鍗曡鏁版嵁
- List<String> arrayString = new ArrayList<>();
+ arrayString = new ArrayList<>();
for (int i = 0; i < col; i++) {
- Cell cell = hssfrow.getCell(i);
+ cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- short format = cell.getCellStyle().getDataFormat();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- Date d = cell.getDateCellValue();
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- Date d = cell.getDateCellValue();
+ d = cell.getDateCellValue();
//DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
- HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
@@ -674,38 +778,161 @@
return null;
}
- private static String getCellVal(Cell cell) {
- if (null == cell) {
- return "";
+
+ /**
+ * 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * @param file 鏂囦欢
+ * @return 鏁版嵁
+ */
+ public static List<List<String>> readExcelData(File file){
+ if(!isExcel(file)){
+ throw new TipsException("璇蜂笂浼爀xcel鐨勬枃浠舵牸寮忥紒");
}
- switch (cell.getCellType()) {
- // 鏁板瓧
- case HSSFCell.CELL_TYPE_NUMERIC:
- // 鏃ユ湡鏍煎紡鐨勫鐞�
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
+ List<List<String>> listData = new ArrayList<>();
+ try{
+ //rowCacheSize 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ //bufferSize 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ //open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+ Workbook book = StreamingReader.builder()
+ .rowCacheSize(100)
+ .bufferSize(4096)
+ .open(new FileInputStream(file));
+
+ //鏄惁瀛樺湪鏁版嵁
+ boolean isData;
+ List<String> arrayString;
+ short format;
+ Date d;
+ DateFormat formater;
+ Sheet sheet;
+ //閬嶅巻鎵�鏈夌殑sheet
+ for(int i=0;i<book.getNumberOfSheets();i++){
+ sheet = book.getSheetAt(i);
+ //閬嶅巻鎵�鏈夌殑琛�
+ for (Row row : sheet) {
+ isData = false;
+ arrayString = new ArrayList<>();
+ //閬嶅巻鎵�鏈夌殑鍒�
+ for (Cell cell : row) {
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ isData = true;
+ format = cell.getCellStyle().getDataFormat();
+ if(format == 14 || format == 31 || format == 57 || format == 58){
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ } else {
+ if(CellType.STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ }else if(CellType.FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ }else{
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ } else if(cell.getCellType() == CellType.BLANK){
+ arrayString.add("");
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ isData = true;
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ if(isData){
+ listData.add(arrayString);
+ }
}
- return String.valueOf(cell.getNumericCellValue());
- // 瀛楃涓�
- case HSSFCell.CELL_TYPE_STRING:
- return cell.getStringCellValue();
- // 鍏紡
- case HSSFCell.CELL_TYPE_FORMULA:
- return cell.getCellFormula();
- // 绌虹櫧
- case HSSFCell.CELL_TYPE_BLANK:
- return "";
- case HSSFCell.CELL_TYPE_BOOLEAN:
- return cell.getBooleanCellValue() + "";
- // 閿欒绫诲瀷
- case HSSFCell.CELL_TYPE_ERROR:
- return cell.getErrorCellValue() + "";
- default:
- break;
+ }
+ }catch (Exception e){
+ e.printStackTrace();
+ throw new RuntimeException(e.getMessage());
}
- return "";
+ return listData;
}
+
+ /**
+ * 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * @param file 鏂囦欢
+ * @return 鏁版嵁
+ */
+ public static List<List<String>> readExcelData(MultipartFile file){
+ if(!isExcel(file)){
+ throw new TipsException("璇蜂笂浼爀xcel鐨勬枃浠舵牸寮忥紒");
+ }
+ List<List<String>> listData = new ArrayList<>();
+ try{
+ //rowCacheSize 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ //bufferSize 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ //open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+ Workbook book = StreamingReader.builder()
+ .rowCacheSize(100)
+ .bufferSize(4096)
+ .open(new BufferedInputStream(file.getInputStream()));
+
+ //鏄惁瀛樺湪鏁版嵁
+ boolean isData;
+ List<String> arrayString;
+ short format;
+ Date d;
+ DateFormat formater;
+ Sheet sheet;
+ //閬嶅巻鎵�鏈夌殑sheet
+ for(int i=0;i<book.getNumberOfSheets();i++){
+ sheet = book.getSheetAt(i);
+ //閬嶅巻鎵�鏈夌殑琛�
+ for (Row row : sheet) {
+ isData = false;
+ arrayString = new ArrayList<>();
+ //閬嶅巻鎵�鏈夌殑鍒�
+ for (Cell cell : row) {
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ isData = true;
+ format = cell.getCellStyle().getDataFormat();
+ if(format == 14 || format == 31 || format == 57 || format == 58){
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ } else {
+ if(CellType.STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ }else if(CellType.FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ }else{
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ } else if(cell.getCellType() == CellType.BLANK){
+ arrayString.add("");
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ isData = true;
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ if(isData){
+ listData.add(arrayString);
+ }
+ }
+ }
+ }catch (Exception e){
+ e.printStackTrace();
+ throw new RuntimeException(e.getMessage());
+ }
+ return listData;
+ }
+
/**
* p鍒ゆ柇鏄惁excel鏂囦欢
@@ -713,16 +940,27 @@
* @return
*/
public static boolean isExcel(MultipartFile file){
- String fileName = file.getOriginalFilename();
+ return isExcel(file.getOriginalFilename());
+ }
+
+
+ /**
+ * p鍒ゆ柇鏄惁excel鏂囦欢
+ * @param file
+ * @return
+ */
+ public static boolean isExcel(File file){
+ return isExcel(file.getName());
+ }
+
+
+ /**鍒ゆ柇鏂囦欢鏍煎紡鏄笉鏄痚xcel*/
+ public static boolean isExcel(String fileName){
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return false;
}
return true;
}
-
-
-
-
public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
File file = File.createTempFile("temp", ".xls");
@@ -741,8 +979,8 @@
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle boderStyle = workbook.createCellStyle();
- boderStyle.setVerticalAlignment((short)1);
- boderStyle.setAlignment((short)2);
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFSheet sheet = workbook.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFRow row = sheet.createRow(0);
@@ -752,7 +990,6 @@
row.setHeight(height.shortValue());
sheet.setColumnWidth(i, width);
anchor = row.createCell(i);
- anchor.setCellType(1);
anchor.setCellValue(headList[i]);
anchor.setCellStyle(boderStyle);
}
@@ -769,7 +1006,6 @@
for(int i = 0; i < fieldList.length; ++i) {
sheet.setColumnWidth(i, width);
cell = row_value.createCell(i);
- cell.setCellType(1);
Object value = dataMap.get(fieldList[i]);
if (value != null && "class java.io.File".equals(value.getClass().toString())) {
File file2 = (File)value;
@@ -779,7 +1015,7 @@
bufferImg = ImageIO.read(file2);
ImageIO.write(bufferImg, "jpg", byteArrayOut);
HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1);
- anchor1.setAnchorType(0);
+ anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
}
} else {
@@ -802,4 +1038,5 @@
return file;
}
+
}
\ No newline at end of file
--
Gitblit v1.8.0