Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
/*
* Copyright 2016-2021 the original author.All rights reserved.
* Kingstar([email protected])
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.teasoft.beex.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.WorkbookFactory;
import org.teasoft.bee.osql.exception.BeeIllegalBusinessException;
import org.teasoft.honey.osql.core.ExceptionHelper;
import org.teasoft.honey.osql.core.Logger;
import org.teasoft.honey.util.StringUtils;
/**
* 读取Excel,支持xls,xlsx.Read the Excel, support xls,xlsx.
* Excel行号从0开始.the Excel line number start 0.
* @author Kingstar
*/
public class ExcelReader {
private ExcelReader() {}
/**
* 返回首个Excel sheet的所有行.Returns all rows of the first Excel sheet.
* @param inputStream InputStream of the Excel file
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List readExcel(InputStream inputStream) {
Sheet sheet = getSheet(inputStream);
return getListBySheet(sheet);
}
/**
* 返回首个Excel sheet的所有行.Returns all rows of the first Excel sheet.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List readExcel(String fullPath) throws FileNotFoundException {
return readExcel(new FileInputStream(fullPath));
}
/**
* 返回名称为sheetName的Excel sheet的所有行.Returns all rows of Excel sheet with sheetname.
* @param inputStream InputStream of the Excel file.
* @param sheetName sheet name
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List readExcel(InputStream inputStream, String sheetName) {
Sheet sheet = getSheet(inputStream, sheetName);
return getListBySheet(sheet);
}
/**
* 返回名称为sheetName的Excel sheet的所有行.Returns all rows of Excel sheet with sheetname.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param sheetName sheet name
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List readExcel(String fullPath, String sheetName) throws FileNotFoundException {
return readExcel(new FileInputStream(fullPath), sheetName);
}
/**
* 返回首个Excel sheet中从开始行到结束行的记录.
* Returns the records from the beginning line to the end line in the first Excel sheet.
* @param inputStream InputStream of the Excel file
* @param startRow 开始行(首行为0).start row(0,1,...)
* @param endRow 结束行.end row.
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List readExcel(InputStream inputStream, int startRow, int endRow) {
Sheet sheet = getSheet(inputStream);
return getListBySheet(sheet, startRow, endRow);
}
/**
* 返回首个Excel sheet中从开始行到结束行的记录.
* Returns the records from the beginning line to the end line in the first Excel sheet.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param startRow 开始行(首行为0).start row(0,1,...)
* @param endRow 结束行.end row.
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List readExcel(String fullPath, int startRow, int endRow) throws FileNotFoundException {
return readExcel(new FileInputStream(fullPath), startRow, endRow);
}
/**
* 返回首个Excel sheet中从开始行到结束行的记录.
* Returns the records from the beginning line to the end line in Excel sheet with sheetname.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param sheetName sheet name
* @param startRow 开始行(首行为0).start row(0,1,...)
* @param endRow 结束行.end row.
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List readExcel(String fullPath, String sheetName, int startRow, int endRow)
throws FileNotFoundException {
return readExcel(new FileInputStream(fullPath), sheetName, startRow, endRow);
}
public static List readExcel(String fullPath, int sheetIndex, int startRow, int endRow)
throws FileNotFoundException {
return readExcel(new FileInputStream(fullPath), sheetIndex, startRow, endRow);
}
/**
* 返回首个Excel sheet中从开始行到结束行的记录.
* Returns the records from the beginning line to the end line in Excel sheet with sheetname.
* @param inputStream InputStream of the Excel file
* @param sheetName sheet name
* @param startRow 开始行(首行为0).start row(0,1,...)
* @param endRow 结束行.end row.
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List readExcel(InputStream inputStream, String sheetName, int startRow, int endRow) {
Sheet sheet = getSheet(inputStream, sheetName);
return getListBySheet(sheet, startRow, endRow);
}
public static List readExcel(InputStream inputStream, int sheetIdex, int startRow, int endRow) {
Sheet sheet = getSheet(inputStream, sheetIdex);
return getListBySheet(sheet, startRow, endRow);
}
/**
* 检测首行标题行并返回首个sheet的所有记录.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param hopeTitleArray 期望的标题数组.Expected title array.
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List checkAndReadExcel(String fullPath, String[] hopeTitleArray) throws FileNotFoundException {
return checkAndReadExcel(fullPath, hopeTitleArray, 0); //默认标题在第0行.
}
/**
* 检测指定标题行并返回首个sheet的所有记录.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param hopeTitles 期望的标题(用逗号隔开).Expected title (separated by commas).
* @param titleRow 标题所在行(首行为0). line number of title row(start from 0)
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List checkAndReadExcel(String fullPath, String hopeTitles, int titleRow) throws FileNotFoundException {
String[] hopeTitleArray = hopeTitles.split(",");
return checkAndReadExcel(new FileInputStream(fullPath), hopeTitleArray, titleRow);
}
public static List checkAndReadExcel(String fullPath, int sheetIndex, String hopeTitles, int titleRow) throws FileNotFoundException {
String[] hopeTitleArray = hopeTitles.split(",");
return checkAndReadExcel(new FileInputStream(fullPath),sheetIndex, hopeTitleArray, titleRow);
}
/**
* 检测指定标题行并返回首个sheet的所有记录.
* @param fullPath 完整的Excel文件路径(包含文件名).Full Excel file path (including file name)
* @param hopeTitleArray 期望的标题数组.Expected title array.
* @param titleRow 标题所在行(首行为0). line number of title row(start from 0)
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
* @throws FileNotFoundException if the file does not exist
*/
public static List checkAndReadExcel(String fullPath, String[] hopeTitleArray, int titleRow)
throws FileNotFoundException {
return checkAndReadExcel(new FileInputStream(fullPath), hopeTitleArray, titleRow);
}
/**
* 检测指定标题行并返回首个sheet的所有记录.
* @param inputStream InputStream of the Excel file
* @param hopeTitles 期望的标题(用逗号隔开).Expected title (separated by commas).
* @param titleRow 标题所在行(首行为0). line number of title row(start from 0)
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List checkAndReadExcel(InputStream inputStream, String hopeTitles, int titleRow) {
String[] hopeTitleArray = hopeTitles.split(",");
return checkAndReadExcel(inputStream, hopeTitleArray, titleRow);
}
/**
* 检测指定标题行并返回首个sheet的所有记录.
* @param inputStream InputStream of the Excel file
* @param hopeTitleArray 期望的标题数组.Expected title array.
* @param titleRow 标题所在行(首行为0). line number of title row(start from 0)
* @return 可包含多个String数组结构的多行记录的list. list can contain more than one record with String array struct.
*/
public static List checkAndReadExcel(InputStream inputStream, String[] hopeTitleArray,
int titleRow) {
Sheet sheet = getSheet(inputStream);
return _check(sheet,hopeTitleArray, titleRow);
}
public static List checkAndReadExcel(InputStream inputStream,int sheetIndex, String[] hopeTitleArray,
int titleRow) {
Sheet sheet = getSheet(inputStream,sheetIndex);
return _check(sheet,hopeTitleArray, titleRow);
}
private static List _check(Sheet sheet, String[] hopeTitleArray,int titleRow){
List list = getListBySheet(sheet, 0, titleRow);
if (titleRow > (list.size() - 1)) {
Logger.warn("The title line number is greater than the maximum data line number!");
return null;
}
String msg = checkTitle(hopeTitleArray, list.get(titleRow));
if (StringUtils.isNotEmpty(msg)) { //检测标题没通过.
if (msg.startsWith("Warn:")) {
Logger.warn(msg);
} else {
Logger.warn("Title wrong number is (start from 0): " + msg);
}
return null;
}
return getListBySheet(sheet);
}
private static Sheet getSheet(InputStream inputStream) {
return getSheet(inputStream, 0);
}
private static Sheet getSheet(InputStream inputStream, int sheetIndex) {
Workbook workbook = null;
Sheet sheet = null;
try {
workbook = WorkbookFactory.create(inputStream);
sheet = workbook.getSheetAt(sheetIndex);
} catch (Exception e) {
Logger.error(e.getMessage());
throw ExceptionHelper.convert(e);
} finally {
try {
if (workbook != null) workbook.close();
if(inputStream!=null) inputStream.close();
} catch (IOException e2) {
Logger.warn("Have exception when close Workbook. " + e2.getMessage());
}
}
return sheet;
}
private static Sheet getSheet(InputStream inputStream, String sheetName) {
Workbook workbook = null;
Sheet sheet = null;
try {
workbook = WorkbookFactory.create(inputStream);
sheet = workbook.getSheet(sheetName); //根据sheet名称获取
} catch (Exception e) {
Logger.error(e.getMessage());
throw ExceptionHelper.convert(e);
} finally {
try {
if (workbook != null) workbook.close();
if(inputStream!=null) inputStream.close();
} catch (IOException e2) {
Logger.warn("Have exception when close Workbook. " + e2.getMessage());
}
}
return sheet;
}
private static List getListBySheet(Sheet sheet) {
return getListBySheet(sheet, 0, -1);
}
/**
*
* @param sheet
* @param startRow 开始行,从0开始
* @param endRow 结束行(包括),如果小于0,则获取所有行
* @return
*/
private static List getListBySheet(Sheet sheet, int startRow, int endRow) {
List list = new ArrayList<>();
if(sheet==null) return list;
int rows = sheet.getLastRowNum(); //最后的行号,不是总行数. 如何判断是无数据的空行???
int columns = 0;
String[] colStr = null;
if (endRow < 0) endRow = rows; //最后的行号,不是总行数.
if (startRow > endRow) {
throw new BeeIllegalBusinessException("endRow need less than startRow!");
}
if (endRow > rows) endRow = rows;
// //从前三行(从startRow开始)中获取最大列数.
// int c1 = 0;
// int c2 = 0;
// int c3 = 0;
// try {
// c1=sheet.getRow(startRow).getLastCellNum();
// if(startRow!=0) { //要考虑首行，可能是标题行，一般都会有多列些
// int c0=sheet.getRow(0).getLastCellNum();
// if(c0>c1) c1=c0;
// }
// } catch (Exception e) {
// c1 = 0;
// }
//
// try {
// if (startRow + 1 <= endRow) c2 = sheet.getRow(startRow + 1).getLastCellNum();
// } catch (Exception e) {
// c2 = 0;
// }
//
// try {
// if (startRow + 2 <= endRow) c3 = sheet.getRow(startRow + 2).getLastCellNum();
// } catch (Exception e) { //获取空行会报异常 V1.11 fixed bug
// c3 = 0;
// }
// long t1=System.currentTimeMillis();
int maxCol = 0;
int temp =0;
//find max Col
for (int t = startRow; t <= endRow; t++) {
try {
temp = sheet.getRow(t).getLastCellNum();
} catch (Exception e) {
temp = 0;
}
if (temp > maxCol) maxCol = temp;
}
// long t2=System.currentTimeMillis();
// System.out.println("==============================");
// System.out.println(t2-t1);
// columns=getMaxColumn(c1,c2,c3);
columns=maxCol;
for (int r = startRow; r <= endRow; r++) { // 循环遍历表格的行
Row row = sheet.getRow(r); // 获取单元格中指定的行对象
if (row != null) {
colStr = new String[columns];
for (int c = 0; c < columns; c++) { // 循环遍历行中的单元格
Cell cell = row.getCell(c);
colStr[c] = trim(getValue(cell));
}
list.add(colStr);
}else {
list.add(new String[] {""}); // 空行
}
}
return list;
}
// private static int getMaxColumn(int c1,int c2,int c3) {
// int max=c1;
// if(c2>max) max=c2;
// if(c3>max) max=c3;
// return max;
// }
@SuppressWarnings("deprecation")
private static String getValue(Cell cell) {
if (cell == null) {
return null;
}
String result="";
switch (cell.getCellTypeEnum()) {
case NUMERIC:// 数字类型
short formatType = cell.getCellStyle().getDataFormat();
if (formatType == 14 || formatType == 31 || formatType == 57 || formatType == 58 || formatType == 20
|| formatType == 32) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = null;
if (formatType == 14)
sdf = new SimpleDateFormat("yyyy/M/dd");
else if (formatType == 31)
sdf = new SimpleDateFormat("yyyy年MM月dd日");
else if (formatType == 57)
sdf = new SimpleDateFormat("yyyy年MM月");
else if (formatType == 58)
sdf = new SimpleDateFormat("M月d日");
else if (formatType == 20)
sdf = new SimpleDateFormat("HH:mm");
else //32
sdf = new SimpleDateFormat("h时mm分");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);
} else if (formatType == 177) {
SimpleDateFormat sdf = null;
sdf = new SimpleDateFormat("yyyy/M/d");
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (formatType == 178) {
SimpleDateFormat sdf = null;
sdf = new SimpleDateFormat("d-MMM-yy");
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (formatType == 179) {
SimpleDateFormat sdf = null;
sdf = new SimpleDateFormat("MM/dd/yy");
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (formatType == 180) {
SimpleDateFormat sdf = null;
sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (org.apache.poi.hssf.usermodel.HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy/MM/dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else {
double cur = cell.getNumericCellValue();
long longVal = Math.round(cur);
Object inputValue = null;
if (Double.parseDouble(longVal + ".0") == cur)
inputValue = longVal;
else
inputValue = cur;
result = String.valueOf(inputValue);
}
break;
case STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case BLANK:
result = "";
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
default:
result = cell.getStringCellValue();
break;
}
return result;
}
private static String trim(String str) {
if (str == null) return null;
return str.trim();
}
public static String checkTitle(String[] hopeTitleArray, String[] excelTitle) {
if (StringUtils.isEmpty(hopeTitleArray) || StringUtils.isEmpty(excelTitle))
return "Warn: hopeTitleArray or excelTitle is empty!";
if (hopeTitleArray.length != excelTitle.length) {
return "Warn: the length of hopeTitleArray and excelTitle are diffenent!";
}
StringBuilder msg = new StringBuilder();
for (int i = 0; i < hopeTitleArray.length; i++) {
if (!hopeTitleArray[i].trim().equals(excelTitle[i].trim())) {
msg.append(i).append(" ,");
}
}
return msg.toString();
}
}
