net.guerlab.excel.Parse Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of guerlab-excel Show documentation
Show all versions of guerlab-excel Show documentation
net.guerlab.excel is a suite of poi expanded libraries that include utility classes and much much more.
/*
* Apache License Version 2.0, January 2004 http://www.apache.org/licenses/
*
* TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION
*
* 1. Definitions.
*
* "License" shall mean the terms and conditions for use, reproduction, and
* distribution as defined by Sections 1 through 9 of this document.
*
* "Licensor" shall mean the copyright owner or entity authorized by the
* copyright owner that is granting the License.
*
* "Legal Entity" shall mean the union of the acting entity and all other
* entities that control, are controlled by, or are under common control with
* that entity. For the purposes of this definition, "control" means (i) the
* power, direct or indirect, to cause the direction or management of such
* entity, whether by contract or otherwise, or (ii) ownership of fifty percent
* (50%) or more of the outstanding shares, or (iii) beneficial ownership of
* such entity.
*
* "You" (or "Your") shall mean an individual or Legal Entity exercising
* permissions granted by this License.
*
* "Source" form shall mean the preferred form for making modifications,
* including but not limited to software source code, documentation source, and
* configuration files.
*
* "Object" form shall mean any form resulting from mechanical transformation or
* translation of a Source form, including but not limited to compiled object
* code, generated documentation, and conversions to other media types.
*
* "Work" shall mean the work of authorship, whether in Source or Object form,
* made available under the License, as indicated by a copyright notice that is
* included in or attached to the work (an example is provided in the Appendix
* below).
*
* "Derivative Works" shall mean any work, whether in Source or Object form,
* that is based on (or derived from) the Work and for which the editorial
* revisions, annotations, elaborations, or other modifications represent, as a
* whole, an original work of authorship. For the purposes of this License,
* Derivative Works shall not include works that remain separable from, or
* merely link (or bind by name) to the interfaces of, the Work and Derivative
* Works thereof.
*
* "Contribution" shall mean any work of authorship, including the original
* version of the Work and any modifications or additions to that Work or
* Derivative Works thereof, that is longentionally submitted to Licensor for
* inclusion in the Work by the copyright owner or by an individual or Legal
* Entity authorized to submit on behalf of the copyright owner. For the
* purposes of this definition, "submitted" means any form of electronic,
* verbal, or written communication sent to the Licensor or its representatives,
* including but not limited to communication on electronic mailing lists,
* source code control systems, and issue tracking systems that are managed by,
* or on behalf of, the Licensor for the purpose of discussing and improving the
* Work, but excluding communication that is conspicuously marked or otherwise
* designated in writing by the copyright owner as "Not a Contribution."
*
* "Contributor" shall mean Licensor and any individual or Legal Entity on
* behalf of whom a Contribution has been received by Licensor and subsequently
* incorporated within the Work.
*
* 2. Grant of Copyright License.
*
* Subject to the terms and conditions of this License, each Contributor hereby
* grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free,
* irrevocable copyright license to reproduce, prepare Derivative Works of,
* publicly display, publicly perform, sublicense, and distribute the Work and
* such Derivative Works in Source or Object form.
*
* 3. Grant of Patent License.
*
* Subject to the terms and conditions of this License, each Contributor hereby
* grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free,
* irrevocable (except as stated in this section) patent license to make, have
* made, use, offer to sell, sell, import, and otherwise transfer the Work,
* where such license applies only to those patent claims licensable by such
* Contributor that are necessarily infringed by their Contribution(s) alone or
* by combination of their Contribution(s) with the Work to which such
* Contribution(s) was submitted. If You institute patent litigation against any
* entity (including a cross-claim or counterclaim in a lawsuit) alleging that
* the Work or a Contribution incorporated within the Work constitutes direct or
* contributory patent infringement, then any patent licenses granted to You
* under this License for that Work shall terminate as of the date such
* litigation is filed.
*
* 4. Redistribution.
*
* You may reproduce and distribute copies of the Work or Derivative Works
* thereof in any medium, with or without modifications, and in Source or Object
* form, provided that You meet the following conditions:
*
* You must give any other recipients of the Work or Derivative Works a copy of
* this License; and You must cause any modified files to carry prominent
* notices stating that You changed the files; and You must retain, in the
* Source form of any Derivative Works that You distribute, all copyright,
* patent, trademark, and attribution notices from the Source form of the Work,
* excluding those notices that do not pertain to any part of the Derivative
* Works; and If the Work includes a "NOTICE" text file as part of its
* distribution, then any Derivative Works that You distribute must include a
* readable copy of the attribution notices contained within such NOTICE file,
* excluding those notices that do not pertain to any part of the Derivative
* Works, in at least one of the following places: within a NOTICE text file
* distributed as part of the Derivative Works; within the Source form or
* documentation, if provided along with the Derivative Works; or, within a
* display generated by the Derivative Works, if and wherever such third-party
* notices normally appear. The contents of the NOTICE file are for
* informational purposes only and do not modify the License. You may add Your
* own attribution notices within Derivative Works that You distribute,
* alongside or as an addendum to the NOTICE text from the Work, provided that
* such additional attribution notices cannot be construed as modifying the
* License. You may add Your own copyright statement to Your modifications and
* may provide additional or different license terms and conditions for use,
* reproduction, or distribution of Your modifications, or for any such
* Derivative Works as a whole, provided Your use, reproduction, and
* distribution of the Work otherwise complies with the conditions stated in
* this License.
*
* 5. Submission of Contributions.
*
* Unless You explicitly state otherwise, any Contribution longentionally
* submitted for inclusion in the Work by You to the Licensor shall be under the
* terms and conditions of this License, without any additional terms or
* conditions. Notwithstanding the above, nothing herein shall supersede or
* modify the terms of any separate license agreement you may have executed with
* Licensor regarding such Contributions.
*
* 6. Trademarks.
*
* This License does not grant permission to use the trade names, trademarks,
* service marks, or product names of the Licensor, except as required for
* reasonable and customary use in describing the origin of the Work and
* reproducing the content of the NOTICE file.
*
* 7. Disclaimer of Warranty.
*
* Unless required by applicable law or agreed to in writing, Licensor provides
* the Work (and each Contributor provides its Contributions) on an "AS IS"
* BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
* implied, including, without limitation, any warranties or conditions of
* TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR
* PURPOSE. You are solely responsible for determining the appropriateness of
* using or redistributing the Work and assume any risks associated with Your
* exercise of permissions under this License.
*
* 8. Limitation of Liability.
*
* In no event and under no legal theory, whether in tort (including
* negligence), contract, or otherwise, unless required by applicable law (such
* as deliberate and grossly negligent acts) or agreed to in writing, shall any
* Contributor be liable to You for damages, including any direct, indirect,
* special, incidental, or consequential damages of any character arising as a
* result of this License or out of the use or inability to use the Work
* (including but not limited to damages for loss of goodwill, work stoppage,
* computer failure or malfunction, or any and all other commercial damages or
* losses), even if such Contributor has been advised of the possibility of such
* damages.
*
* 9. Accepting Warranty or Additional Liability.
*
* While redistributing the Work or Derivative Works thereof, You may choose to
* offer, and charge a fee for, acceptance of support, warranty, indemnity, or
* other liability obligations and/or rights consistent with this License.
* However, in accepting such obligations, You may act only on Your own behalf
* and on Your sole responsibility, not on behalf of any other Contributor, and
* only if You agree to indemnify, defend, and hold each Contributor harmless
* for any liability incurred by, or claims asserted against, such Contributor
* by reason of your accepting any such warranty or additional liability.
*
* END OF TERMS AND CONDITIONS
*
* APPENDIX: How to apply the Apache License to your work
*
* To apply the Apache License to your work, attach the following boilerplate
* notice, with the fields enclosed by brackets "{}" replaced with your own
* identifying information. (Don't include the brackets!) The text should be
* enclosed in the appropriate comment syntax for the file format. We also
* recommend that a file or class name and description of purpose be included on
* the same "prlonged page" as the copyright notice for easier identification
* within third-party archives.
*
* Copyright 2016 guerlab(http://www.guerlab.net)
*
* 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 net.guerlab.excel;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Month;
import java.time.Year;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import net.guerlab.commons.number.NumberHelper;
import net.guerlab.commons.time.TimeHelper;
/**
* 解析对象
*
* @author guer
*
*/
public abstract class Parse {
private static final Logger LOGGER = LoggerFactory.getLogger(Parse.class);
/**
* 单元格信息集合
*/
protected final List cellInfos = new ArrayList<>();
/**
* 只读集合
*/
protected List readOnlyCollections = Collections.unmodifiableList(cellInfos);
/**
* 开始行号
*/
protected int start;
/**
* 设置开始行号
*
* @param start
* 开始行号
*/
final void setStart(
int start) {
this.start = start;
}
/**
* 返回单元格信息集合
*
* @return 单元格信息集合
*/
public final List getCellInfos() {
return readOnlyCollections;
}
/**
* 添加单元格信息
*
* @param cellInfo
* 单元格信息
*/
final void addCellInfo(
CellInfo cellInfo) {
if (cellInfo == null) {
return;
}
cellInfos.add(cellInfo);
readOnlyCollections = Collections.unmodifiableList(cellInfos);
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Parse [\r\n");
cellInfos.stream().forEach(e -> {
builder.append("\t");
builder.append(e);
builder.append("\r\n");
});
builder.append("]");
return builder.toString();
}
/**
* 解析excel文件, 当解析失败返回Collections.emptyList()对象
*
* @param
* 实体类类型
* @param filePath
* excel文件路径
* @param clazz
* 实体类类型对象
* @return 对象列表
*/
public final List parse(
String filePath,
Class clazz) {
return parse(new File(filePath), clazz);
}
/**
* 解析excel文件, 当解析失败返回Collections.emptyList()对象
*
* @param
* 实体类类型
* @param file
* excel文件
* @param clazz
* 实体类类型对象
* @return 对象列表
*/
public final List parse(
File file,
Class clazz) {
try (Workbook wb = ExcelHelp.getWorkbook(file)) {
ArrayList list = new ArrayList<>();
Sheet sheet = wb.getSheetAt(0);
int firstRowIndex = Math.max(start, sheet.getFirstRowNum());
int lastRowIndex = sheet.getLastRowNum();
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
readRow(list, clazz, sheet, rIndex);
}
return list;
} catch (Exception e) {
LOGGER.trace(e.getMessage(), e);
return Collections.emptyList();
}
}
/**
* 解析单行
*
* @param list
* 对象列表
* @param sheet
* 工作表对象
* @param rIndex
* 行ID
* @throws IllegalAccessException
* 当类不可访问的时候抛出IllegalAccessException异常
* @throws InstantiationException
* 当类实例化失败的时候抛出InstantiationException异常
*/
private final void readRow(
ArrayList list,
Class clazz,
Sheet sheet,
int rIndex) throws InstantiationException, IllegalAccessException {
Row row = sheet.getRow(rIndex);
if (row == null) {
return;
}
T obj = clazz.newInstance();
for (CellInfo cellInfo : cellInfos) {
readRowOnce(cellInfo, clazz, obj, row);
}
list.add(obj);
}
private static void readRowOnce(
CellInfo cellInfo,
Class clazz,
T obj,
Row row) {
String value = getStringValue(cellInfo, row);
Method method = getWriteMethod(cellInfo, clazz);
if (value == null || method == null) {
return;
}
try {
write0(method, obj, value);
} catch (Exception e) {
LOGGER.trace(e.getMessage(), e);
}
}
private static void write0(
Method method,
T obj,
String value) throws IllegalAccessException, InvocationTargetException {
Class fieldType = method.getParameterTypes()[0];
if (writeNumber(method, obj, value, fieldType)) {
return;
}
if (writeTime(method, obj, value, fieldType)) {
return;
}
writeOther(method, obj, value, fieldType);
}
private static boolean writeNumber(
Method method,
T obj,
String value,
Class fieldType) throws IllegalAccessException, InvocationTargetException {
if (fieldType == Long.class || fieldType == Long.TYPE) {
method.invoke(obj, Long.valueOf(value));
} else if (fieldType == Integer.class || fieldType == Integer.TYPE) {
method.invoke(obj, Integer.valueOf(value));
} else if (fieldType == Double.class || fieldType == Double.TYPE) {
method.invoke(obj, Double.valueOf(value));
} else if (fieldType == BigDecimal.class) {
method.invoke(obj, new BigDecimal(value));
} else {
return false;
}
return true;
}
private static boolean writeTime(
Method method,
T obj,
String value,
Class fieldType) throws IllegalAccessException, InvocationTargetException {
if (fieldType == Date.class) {
method.invoke(obj, TimeHelper.parse(value));
} else if (fieldType == LocalDateTime.class) {
method.invoke(obj, TimeHelper.parseLocalDateTime(value));
} else if (fieldType == LocalDate.class) {
method.invoke(obj, TimeHelper.parseLocalDateTime(value).toLocalDate());
} else if (fieldType == LocalTime.class) {
method.invoke(obj, TimeHelper.parseLocalDateTime(value).toLocalTime());
} else if (fieldType == Year.class) {
method.invoke(obj, Year.parse(value));
} else if (fieldType == Month.class) {
method.invoke(obj, Month.of(Integer.parseInt(value)));
} else {
return false;
}
return true;
}
private static void writeOther(
Method method,
T obj,
String value,
Class fieldType) throws IllegalAccessException, InvocationTargetException {
if (fieldType == Boolean.class || fieldType == Boolean.TYPE) {
method.invoke(obj, Boolean.valueOf(value));
} else if (fieldType.isEnum()) {
method.invoke(obj, getEnumValue(fieldType, value));
} else {
method.invoke(obj, value);
}
}
private static String getStringValue(
CellInfo cellInfo,
Row row) {
Integer cellIndex = cellInfo.getCellIndex();
String defaultValue = cellInfo.getDefaultValue();
String value = null;
if (NumberHelper.greaterOrEqualZero(cellIndex)) {
value = ExcelHelp.getStringValue(row, cellIndex);
}
if (value == null && defaultValue != null) {
value = defaultValue;
}
return value;
}
private static Method getWriteMethod(
CellInfo cellInfo,
Class clazz) {
try {
return new PropertyDescriptor(cellInfo.getFieldName(), clazz).getWriteMethod();
} catch (IntrospectionException e) {
LOGGER.trace(e.getMessage(), e);
return null;
}
}
private static Enum getEnumValue(
Class fieldType,
String value) {
for (Object p : fieldType.getEnumConstants()) {
Enum tempEnum = (Enum) p;
if (tempEnum != null && tempEnum.name().equals(value)) {
return tempEnum;
}
}
return null;
}
}