From 7f9b53d982c0210ffc25db3a639bd858fc5db79b Mon Sep 17 00:00:00 2001
From: chenjiahe <763432473@qq.com>
Date: 星期三, 31 五月 2023 10:56:55 +0800
Subject: [PATCH] 数据库公用方法添加
---
src/main/java/com/hx/util/ExcelUtil.java | 742 +++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 721 insertions(+), 21 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 3598df9..a21a8dc 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,24 +1,30 @@
package com.hx.util;
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.FileOutputStream;
+import com.hx.exception.TipsException;
+import org.apache.poi.POIXMLDocument;
+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.xssf.usermodel.*;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.imageio.ImageIO;
+import java.awt.image.BufferedImage;
+import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
-
import java.util.List;
import java.util.Map;
-import com.hx.exception.TipsException;
-import org.apache.poi.hssf.usermodel.*;
-import org.apache.poi.ss.usermodel.Cell;
-import org.springframework.web.multipart.MultipartFile;
-
/**
- *
+ *
* @author hjr
*/
public final class ExcelUtil {
@@ -37,8 +43,8 @@
* @throws Exception
*/
public static String createExcel(String outPath, String excelName,
- String[] headList, String[] fieldList,
- List<Map<String, Object>> dataList) throws Exception {
+ String[] headList, String[] fieldList,
+ List<Map<String, Object>> dataList) throws Exception {
String filePath = null;
// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
@@ -51,10 +57,11 @@
// 鍦ㄧ储寮�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);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -62,14 +69,16 @@
}
// ===============================================================
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);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -80,7 +89,7 @@
}
// 鏂板缓涓�杈撳嚭鏂囦欢娴�
- File file = SimpleTool.createFile(outPath,excelName);
+ File file = SimpleTool.createFile(outPath, excelName);
FileOutputStream fOut = new FileOutputStream(file);
// 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
workbook.write(fOut);
@@ -96,6 +105,254 @@
return filePath;
}
+ /**鐢熸垚涓存椂鏂囦欢
+ * @param headList
+ * Excel鏂囦欢Head鏍囬闆嗗悎
+ * @param fieldList
+ * Excel鏂囦欢Field鏍囬闆嗗悎 鏍规嵁field鏉ュ鎵句綅缃～鍏呰〃鏍�
+ * @param dataList
+ * Excel鏂囦欢鏁版嵁鍐呭閮ㄥ垎
+ * @throws Exception
+ */
+ public static File createExcel(String[] headList, String[] fieldList,
+ List<Map<String, Object>> dataList) throws Exception {
+ File file = File.createTempFile("temp", ".xlsx");
+ try{
+ // 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
+ HSSFWorkbook workbook = new HSSFWorkbook();
+
+ // 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+ // 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+ // HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+ HSSFSheet sheet = workbook.createSheet();
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
+ HSSFRow row = sheet.createRow(0);
+ // ===============================================================
+ HSSFCell cell;
+ for (int i = 0; i < headList.length; i++) {
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+ cell = row.createCell(i);
+ // 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+ 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鐨勪綅缃垱寤鸿
+ row_value = sheet.createRow(n + 1);
+ dataMap = dataList.get(n);
+ // ===============================================================
+ for (int i = 0; i < fieldList.length; i++) {
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+ cell = row_value.createCell(i);
+ // 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+ cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ // 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+ cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+ }
+ // ===============================================================
+ }
+ }
+
+ // 鏂板缓涓�杈撳嚭鏂囦欢娴�
+ FileOutputStream fOut = new FileOutputStream(file);
+ // 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
+ workbook.write(fOut);
+ fOut.flush();
+ // 鎿嶄綔缁撴潫锛屽叧闂枃浠�
+ fOut.close();
+ }catch (Exception e){
+
+ }finally {
+ file.deleteOnExit();
+ }
+ return file;
+ }
+
+ /**鐢熸垚涓存椂鏂囦欢
+ * @param headList
+ * Excel鏂囦欢Head鏍囬闆嗗悎
+ * @param fieldList
+ * Excel鏂囦欢Field鏍囬闆嗗悎 鏍规嵁field鏉ュ鎵句綅缃～鍏呰〃鏍�
+ * @param dataList
+ * Excel鏂囦欢鏁版嵁鍐呭閮ㄥ垎
+ * @throws Exception
+ */
+ public static File createExcel(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 宸ヤ綔绨�
+ HSSFWorkbook workbook = new HSSFWorkbook();
+
+ //鍚堝苟鐨勫崟鍏冩牸鏍峰紡
+ HSSFCellStyle boderStyle = workbook.createCellStyle();
+ //鍨傜洿灞呬腑
+ boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+ boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+
+ // 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+ // 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+ // HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+ HSSFSheet sheet = workbook.createSheet();
+ // 鍦ㄧ储寮�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鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+ cell = row.createCell(i);
+ // 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+ 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鐨勪綅缃垱寤鸿
+ 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);
+ }
+ // ===============================================================
+ }
+ }
+
+ // 鏂板缓涓�杈撳嚭鏂囦欢娴�
+ 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();
+
+
+ //鍚堝苟鐨勫崟鍏冩牸鏍峰紡
+ XSSFCellStyle boderStyle = workbook.createCellStyle();
+ //鍨傜洿灞呬腑
+ boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+ boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+
+ // 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+ // 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+ // HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+ XSSFSheet sheet = workbook.createSheet();
+ // 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
+ XSSFRow row = sheet.createRow(0);
+ // ===============================================================
+ XSSFCell 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) {
+ XSSFRow 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);
+ }
+ // ===============================================================
+ }
+ }
+
+ // 鏂板缓涓�杈撳嚭鏂囦欢娴�
+ FileOutputStream fOut = new FileOutputStream(file);
+ // 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
+ workbook.write(fOut);
+ fOut.flush();
+ // 鎿嶄綔缁撴潫锛屽叧闂枃浠�
+ fOut.close();
+ }catch (Exception e){
+
+ }finally {
+ file.deleteOnExit();
+ }
+ return file;
+ }
+
+
private static String objToString(Object obj) {
if (obj == null) {
return "";
@@ -104,10 +361,34 @@
return (String) obj;
} else if (obj instanceof Date) {
return null;// DateUtil.dateToString((Date)
- // obj,DateUtil.DATESTYLE_SHORT_EX);
+ // obj,DateUtil.DATESTYLE_SHORT_EX);
} else {
return obj.toString();
}
+ }
+ }
+
+ /**
+ * 璇诲彇Excel鏁版嵁
+ * @param file
+ * @param header
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {
+
+ String fileName = file.getOriginalFilename();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ //鍒ゆ柇涓嶅悓鏍煎紡澶勭悊鏂规硶涓嶅悓
+ if(fileName.matches("^.+\\.(?i)(xls)$")){
+ //xls鏍煎紡浣跨敤HSSF
+ return readExcelByeFileData(file, header);
+ }else{
+ //xlsx鏍煎紡浣跨敤XSSF
+ return readExcelByeFileDataToXSSF(file, header);
}
}
@@ -187,7 +468,11 @@
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- arrayString.add(cell.getStringCellValue().trim());
+ if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ arrayString.add(cell.getStringCellValue().trim());
+ }else{
+ arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
+ }
//arrayString[i] = cell.getStringCellValue().trim();
}
}
@@ -198,4 +483,419 @@
return list;
}
-}
+ /**
+ * 璇诲彇 Excel鏂囦欢鍐呭
+ *
+ * @param file
+ * @param header 鏄惁鍖呮嫭琛ㄥご
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {
+
+ String fileName = file.getOriginalFilename();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+
+ XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
+
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
+ XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
+ XSSFRow xssfrow = xssfSheet.getRow(j);
+ if(xssfrow!=null){
+ if(j == 0) {
+ col = xssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ List<String> arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ XSSFCell cell = xssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == 0) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ short 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");
+ // 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();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ } else {
+ if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(XSSFCell.CELL_TYPE_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){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ arrayString.add(cell.getStringCellValue().trim());
+ }else{
+ arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
+ }
+
+
+ //arrayString[i] = cell.getStringCellValue().trim();
+ }
+ }
+ list.add(arrayString);
+ }
+ }
+ }
+ return list;
+ }
+
+
+ /**鍒ゆ柇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瑙ｆ瀽涓嶄簡");
+ }
+
+ /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+ * 閫氳繃娴佽鍙朎xcel鏂囦欢
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception {
+ try {
+
+ String fileName = file.getName();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+ Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
+
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ for(int s=0;s<book.getNumberOfSheets();s++) {
+ Sheet hssfsheet = book.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ Row hssfrow = hssfsheet.getRow(j);
+ if(hssfrow!=null){
+ if(j == 0) {
+ col = hssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ List<String> arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ Cell cell = hssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == 0) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ short 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");
+ // 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();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ DateFormat 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()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(HSSFCell.CELL_TYPE_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){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ arrayString.add(cell.getStringCellValue().trim());
+ //arrayString[i] = cell.getStringCellValue().trim();
+ }
+ }
+ list.add(arrayString);
+ }
+ }
+ }
+ return list;
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ return null;
+ }
+
+
+
+
+ /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+ * 閫氳繃娴佽鍙朎xcel鏂囦欢
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
+ try {
+
+ String fileName = file.getOriginalFilename();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+ Workbook book = create(new BufferedInputStream(file.getInputStream()));
+
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ for(int s=0;s<book.getNumberOfSheets();s++) {
+ Sheet hssfsheet = book.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ Row hssfrow = hssfsheet.getRow(j);
+ if(hssfrow!=null){
+ if(j == 0) {
+ col = hssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ List<String> arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ Cell cell = hssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == 0) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ short 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");
+ // 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();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ DateFormat 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()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(HSSFCell.CELL_TYPE_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){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ arrayString.add(cell.getStringCellValue().trim());
+ //arrayString[i] = cell.getStringCellValue().trim();
+ }
+ }
+ list.add(arrayString);
+ }
+ }
+ }
+ return list;
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ return null;
+ }
+
+ private static String getCellVal(Cell cell) {
+ if (null == cell) {
+ return "";
+ }
+ 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();
+ }
+ 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;
+ }
+ return "";
+ }
+
+ /**
+ * p鍒ゆ柇鏄惁excel鏂囦欢
+ * @param file
+ * @return
+ */
+ public static boolean isExcel(MultipartFile file){
+ String fileName = file.getOriginalFilename();
+ 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");
+ FileOutputStream fileOut = null;
+ BufferedImage bufferImg = null;
+
+ try {
+ ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
+ if (height == null) {
+ height = 450;
+ }
+
+ if (width == null) {
+ width = 1000;
+ }
+
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ HSSFCellStyle boderStyle = workbook.createCellStyle();
+ boderStyle.setVerticalAlignment((short)1);
+ boderStyle.setAlignment((short)2);
+ HSSFSheet sheet = workbook.createSheet();
+ HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
+ HSSFRow row = sheet.createRow(0);
+
+ HSSFCell anchor;
+ for(int i = 0; i < headList.length; ++i) {
+ row.setHeight(height.shortValue());
+ sheet.setColumnWidth(i, width);
+ anchor = row.createCell(i);
+ anchor.setCellType(1);
+ anchor.setCellValue(headList[i]);
+ anchor.setCellStyle(boderStyle);
+ }
+
+ HSSFRow row_value = null;
+ anchor = null;
+ HSSFCell cell = null;
+ if (dataList != null) {
+ for(int n = 0; n < dataList.size(); ++n) {
+ row_value = sheet.createRow(n + 1);
+ row_value.setHeight(height.shortValue());
+ Map<String, Object> dataMap = (Map)dataList.get(n);
+
+ 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;
+ if (file2 == null) {
+ cell.setCellValue("");
+ } else {
+ 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);
+ patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
+ }
+ } else {
+ cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+ cell.setCellStyle(boderStyle);
+ }
+ }
+ }
+ }
+
+ FileOutputStream fOut = new FileOutputStream(file);
+ workbook.write(fOut);
+ fOut.flush();
+ fOut.close();
+ } catch (Exception var25) {
+ var25.printStackTrace();
+ } finally {
+ file.deleteOnExit();
+ }
+
+ return file;
+ }
+}
\ No newline at end of file
--
Gitblit v1.8.0