package com.vci.ubcs.starter.poi.util;
|
|
import com.vci.ubcs.starter.bo.WriteExcelData;
|
import com.vci.ubcs.starter.exception.VciBaseException;
|
import com.vci.ubcs.starter.poi.annotation.ExcelColumn;
|
import com.vci.ubcs.starter.poi.annotation.ExcelTitle;
|
import com.vci.ubcs.starter.poi.bo.*;
|
import com.vci.ubcs.starter.util.LocalFileUtil;
|
import com.vci.ubcs.starter.web.annotation.VciFieldType;
|
import com.vci.ubcs.starter.web.enumpck.VciFieldTypeEnum;
|
import com.vci.ubcs.starter.web.util.LangBaseUtil;
|
import com.vci.ubcs.starter.web.util.VciBaseUtil;
|
import org.apache.commons.io.IOUtils;
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
|
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
|
import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
|
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.hssf.util.HSSFColor;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
|
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
|
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
|
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springblade.core.tool.utils.StringUtil;
|
import org.springframework.util.CollectionUtils;
|
|
import java.io.*;
|
import java.lang.reflect.Field;
|
import java.math.BigDecimal;
|
import java.nio.channels.FileChannel;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
public class ExcelUtil {
|
private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
|
public static final String KEY_ATTR_CHAR = "☆";
|
public static final String REQUIRED_CHAR = "*";
|
/**
|
* 替换字符
|
*/
|
public static final String SPECIAL_CHAR = "VCI";
|
|
public ExcelUtil() {
|
}
|
|
public static void copyFile(File source, File target) throws VciBaseException {
|
VciBaseUtil.alertNotNull(new Object[]{source, "来源文件", target, "目标文件"});
|
if (!source.exists()) {
|
throw new VciBaseException("fileNotFound", new String[]{source.getName()});
|
} else {
|
File is;
|
if (!target.exists()) {
|
is = target.getParentFile();
|
is.mkdirs();
|
|
try {
|
target.createNewFile();
|
} catch (IOException var17) {
|
throw new VciBaseException("fileIoException", new String[]{target.getName()});
|
}
|
}
|
|
is = null;
|
FileOutputStream os = null;
|
|
FileInputStream isF;
|
try {
|
isF = new FileInputStream(source);
|
} catch (FileNotFoundException var16) {
|
throw new VciBaseException("fileNotFound", new String[]{source.getName()});
|
}
|
|
try {
|
os = new FileOutputStream(target);
|
} catch (FileNotFoundException var15) {
|
IOUtils.closeQuietly(isF);
|
throw new VciBaseException("fileNotFound", new String[]{target.getName()});
|
}
|
|
try {
|
copyFile(isF, os);
|
} catch (VciBaseException var12) {
|
throw var12;
|
} catch (Throwable var13) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var13), new String[]{source.getName(), target.getName()}, var13);
|
} finally {
|
IOUtils.closeQuietly(isF);
|
IOUtils.closeQuietly(os);
|
}
|
|
}
|
}
|
|
public static void copyFile(FileInputStream in, FileOutputStream os) throws VciBaseException {
|
try {
|
FileChannel sourceChannel = in.getChannel();
|
FileChannel targetChannel = os.getChannel();
|
int i = 0;
|
int length = 2097152;
|
|
while(true) {
|
if (sourceChannel.position() == sourceChannel.size()) {
|
sourceChannel.close();
|
targetChannel.close();
|
}
|
|
if (sourceChannel.size() - sourceChannel.position() < 20971520L) {
|
length = (int)(sourceChannel.size() - sourceChannel.position());
|
} else {
|
length = 20971520;
|
}
|
|
sourceChannel.transferTo(sourceChannel.position(), (long)length, targetChannel);
|
sourceChannel.position(sourceChannel.position() + (long)length);
|
++i;
|
}
|
} catch (IOException var10) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", var10);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var10), new String[0], var10);
|
} catch (Throwable var11) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", var11);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var11), new String[0], var11);
|
} finally {
|
IOUtils.closeQuietly(in);
|
IOUtils.closeQuietly(os);
|
}
|
}
|
|
public static void copyFileFromJar(String filePathInJar, File targetFile) throws VciBaseException {
|
VciBaseUtil.alertNotNull(new Object[]{filePathInJar, "来源文件", targetFile, "目标文件"});
|
File os;
|
if (!targetFile.exists()) {
|
os = targetFile.getParentFile();
|
os.mkdirs();
|
|
try {
|
targetFile.createNewFile();
|
} catch (IOException var12) {
|
throw new VciBaseException("fileIoException", new String[]{targetFile.getName()});
|
}
|
}
|
|
os = null;
|
InputStream ins = null;
|
|
FileOutputStream osF;
|
try {
|
osF = new FileOutputStream(targetFile);
|
} catch (FileNotFoundException var11) {
|
throw new VciBaseException("fileNotFound", new String[]{targetFile.getName()});
|
}
|
|
try {
|
ins = ExcelUtil.class.getResourceAsStream(filePathInJar);
|
IOUtils.copy(ins, osF);
|
} catch (IOException var13) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", var13);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var13), new String[0], var13);
|
} catch (Throwable var14) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", var14);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var14), new String[0], var14);
|
} finally {
|
IOUtils.closeQuietly(ins);
|
IOUtils.closeQuietly(osF);
|
}
|
|
}
|
|
public static void writeDataToFile(String fileName, WriteExcelOption writeExcelOption) throws VciBaseException {
|
writeDataToFile(new File(fileName), writeExcelOption);
|
}
|
|
public static void writeDataToFile(File file, WriteExcelOption writeExcelOption) throws VciBaseException {
|
if (file == null) {
|
throw new VciBaseException("文件为空，无法找到文件");
|
} else {
|
if (!file.exists()) {
|
File folder = file.getParentFile();
|
if (!folder.exists()) {
|
folder.mkdirs();
|
}
|
|
try {
|
file.createNewFile();
|
} catch (IOException var14) {
|
throw new VciBaseException("fileNotFound", new String[]{file.getName()});
|
}
|
}
|
|
Workbook workbook = null;
|
if (writeExcelOption != null && writeExcelOption.isAppend()) {
|
try {
|
if (file.length() == 0L) {
|
workbook = createWorkbook(false);
|
} else {
|
workbook = getWorkbookByInputStream(new FileInputStream(file));
|
}
|
} catch (FileNotFoundException var13) {
|
throw new VciBaseException("fileNotExist", new String[]{file.getName()}, var13);
|
}
|
}
|
|
OutputStream os = null;
|
|
try {
|
os = new FileOutputStream(file);
|
} catch (FileNotFoundException var12) {
|
throw new VciBaseException("fileNotFound", new String[]{var12.getMessage()}, var12);
|
}
|
|
try {
|
writeDataToFile(os, writeExcelOption, workbook);
|
} catch (VciBaseException var15) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到excel出错", var15);
|
}
|
|
throw var15;
|
} catch (Throwable var16) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到excel出错", var16);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var16), new String[0], var16);
|
} finally {
|
IOUtils.closeQuietly(os);
|
}
|
|
}
|
}
|
|
public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption) throws VciBaseException {
|
writeDataToFile(os, writeExcelOption, (Workbook)null);
|
}
|
|
public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption, Workbook workbook) throws VciBaseException {
|
if (os == null) {
|
throw new VciBaseException("outputStreamIsNull");
|
} else {
|
if (writeExcelOption == null) {
|
IOUtils.closeQuietly(os);
|
} else {
|
Workbook wb = null;
|
if (writeExcelOption.isAppend() && workbook != null) {
|
wb = workbook;
|
} else {
|
wb = createWorkbook(writeExcelOption.isRevision07());
|
}
|
|
Workbook finalWb = wb;
|
writeExcelOption.getWriteDataMap().forEach((sheetName, excelDataList) -> {
|
Sheet sheet = getSheetByWorkbook(finalWb, sheetName);
|
|
try {
|
writeDataToCell(sheet, excelDataList,workbook);
|
} catch (VciBaseException var6) {
|
IOUtils.closeQuietly(os);
|
throw var6;
|
} catch (Throwable var7) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到工作簿出错", var7);
|
}
|
|
IOUtils.closeQuietly(os);
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var7), new String[0], var7);
|
}
|
});
|
if (!CollectionUtils.isEmpty(writeExcelOption.getHideSheetList())) {
|
Workbook finalWb1 = wb;
|
writeExcelOption.getHideSheetList().stream().forEach((sheetName) -> {
|
Sheet sheet = getSheetByWorkbook(finalWb1, sheetName);
|
finalWb1.setSheetHidden(finalWb1.getSheetIndex(sheet), true);
|
});
|
}
|
|
try {
|
wb.write(os);
|
} catch (IOException var13) {
|
if (logger.isErrorEnabled()) {
|
logger.error("把工作簿上的数据写入到文件出错", var13);
|
}
|
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var13), new String[0], var13);
|
} finally {
|
IOUtils.closeQuietly(os);
|
try {
|
wb.close();
|
} catch (Throwable var12) {
|
if (logger.isErrorEnabled()) {
|
logger.error("关闭工作簿", var12);
|
}
|
}
|
|
}
|
}
|
|
}
|
}
|
|
public static int getCellIndexForPoField(String excelFileName, String sheetName, Class<?> poClass, String poFieldName) throws VciBaseException {
|
return getCellIndexForPoField(new File(excelFileName), sheetName, poClass, poFieldName);
|
}
|
|
public static int getCellIndexForPoField(File excelFile, String sheetName, Class<?> poClass, String poFieldName) throws VciBaseException {
|
VciBaseUtil.alertNotNull(new Object[]{excelFile, "excel的文件", poClass, "poi导入导出对象所属的类", poFieldName, "属性的名称"});
|
if (!excelFile.exists()) {
|
throw new VciBaseException("fileNotExist", new String[]{excelFile.getAbsolutePath()});
|
} else {
|
if (StringUtil.isBlank(sheetName)) {
|
sheetName = "Sheet1";
|
}
|
|
Field field = VciBaseUtil.getFieldForObject(poFieldName, poClass);
|
if (field == null) {
|
throw new VciBaseException("对象中不存在此属性，{0}，{1}", new String[]{poFieldName, poClass.getName()});
|
} else {
|
if (field.isAnnotationPresent(ExcelColumn.class)) {
|
ExcelColumn ec = getExcelColumnAnnotation(field);
|
String columnName = ec.value();
|
Workbook workbook = null;
|
|
try {
|
workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
|
} catch (FileNotFoundException var15) {
|
throw new VciBaseException("fileNotExist", new String[]{excelFile.getAbsolutePath()});
|
}
|
|
if (workbook != null) {
|
Sheet sheet = workbook.getSheet(sheetName);
|
if (sheet != null) {
|
Row row = sheet.getRow(getRowIndexForTitle(poClass));
|
if (row != null) {
|
short lastCellNum = row.getLastCellNum();
|
|
for(short i = 0; i < lastCellNum; ++i) {
|
Cell cell = row.getCell(i);
|
if (columnName.equals(cell.getStringCellValue())) {
|
try {
|
workbook.close();
|
} catch (IOException var14) {
|
logger.error("关闭工作表出现错误", var14);
|
}
|
|
return i;
|
}
|
}
|
}
|
}
|
}
|
}
|
|
return -1;
|
}
|
}
|
}
|
|
public static Map<String, Integer> listCellIndexForPO(File excelFile, String sheetName, Class<?> poClass) throws VciBaseException {
|
VciBaseUtil.alertNotNull(new Object[]{excelFile, "excel的文件", poClass, "poi导入导出对象所属的类"});
|
if (!excelFile.exists()) {
|
throw new VciBaseException("fileNotExist", new String[]{excelFile.getAbsolutePath()});
|
} else {
|
if (StringUtil.isBlank(sheetName)) {
|
sheetName = "Sheet1";
|
}
|
|
List<Field> fields = VciBaseUtil.getAllFieldForObj(poClass);
|
Map<String, Integer> cellIndexMap = new HashMap();
|
if (!CollectionUtils.isEmpty(fields)) {
|
Workbook workbook = null;
|
|
try {
|
workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
|
} catch (FileNotFoundException var14) {
|
throw new VciBaseException("fileNotExist", new String[]{excelFile.getAbsolutePath()});
|
}
|
|
if (workbook != null) {
|
Sheet sheet = workbook.getSheet(sheetName);
|
if (sheet != null) {
|
Row row = sheet.getRow(getRowIndexForTitle(poClass));
|
if (row != null) {
|
Map<String, String> columnNamesMap = new HashMap();
|
fields.stream().forEach((field) -> {
|
if (field.isAnnotationPresent(ExcelColumn.class)) {
|
ExcelColumn ec = getExcelColumnAnnotation(field);
|
String columnName = ec.value();
|
columnNamesMap.put(columnName, field.getName());
|
}
|
|
});
|
short lastCellNum = row.getLastCellNum();
|
|
for(short i = 0; i < lastCellNum; ++i) {
|
Cell cell = row.getCell(i);
|
String cellName = cell.getStringCellValue();
|
if (columnNamesMap.containsKey(cellName)) {
|
cellIndexMap.put(columnNamesMap.get(cellName), Integer.valueOf(i));
|
}
|
}
|
}
|
}
|
|
try {
|
workbook.close();
|
} catch (IOException var13) {
|
logger.error("关闭工作表出现错误", var13);
|
}
|
}
|
}
|
|
return cellIndexMap;
|
}
|
}
|
|
public static int getRowIndexForTitle(Class<?> poClass) {
|
if (poClass.isAnnotationPresent(ExcelTitle.class)) {
|
ExcelTitle ec = (ExcelTitle)poClass.getAnnotation(ExcelTitle.class);
|
if (ec == null) {
|
ec = (ExcelTitle)poClass.getDeclaredAnnotation(ExcelTitle.class);
|
}
|
|
if (ec != null) {
|
return ec.rowIndexForTitle();
|
}
|
}
|
|
return 0;
|
}
|
|
public static String getCellNameByIndex(int colIndex) {
|
if (colIndex < 26) {
|
return String.valueOf((char)(65 + colIndex));
|
} else {
|
int balance = colIndex % 26;
|
int multiple = (colIndex - balance) / 26;
|
String prefix = String.valueOf((char)(65 + (multiple - 1)));
|
String suffix = String.valueOf((char)(65 + balance));
|
return prefix + suffix;
|
}
|
}
|
|
public static Workbook createWorkbook(boolean is07) {
|
Workbook wb = null;
|
if (is07) {
|
wb = new XSSFWorkbook();
|
} else {
|
wb = new HSSFWorkbook();
|
}
|
|
return (Workbook)wb;
|
}
|
|
public static Workbook getWorkbookByInputStream(InputStream ins) throws VciBaseException {
|
Workbook wb = null;
|
|
try {
|
wb = WorkbookFactory.create(ins);
|
return wb;
|
} catch (IOException var3) {
|
IOUtils.closeQuietly(ins);
|
throw new VciBaseException("workBookInitFail", new String[]{LangBaseUtil.getErrorMsg(var3)});
|
}
|
}
|
|
private static Sheet getSheetByWorkbook(Workbook wb, String sheetName) {
|
if (StringUtil.isBlank(sheetName)) {
|
sheetName = "Sheet1";
|
}
|
|
Sheet sheet = wb.getSheet(sheetName);
|
if (sheet == null) {
|
sheet = wb.createSheet(sheetName);
|
}
|
|
return sheet;
|
}
|
|
private static Row getRowBySheet(Sheet sheet, int rowIndex) {
|
Row row = sheet.getRow(rowIndex);
|
if (row == null) {
|
row = sheet.createRow(rowIndex);
|
}
|
|
return row;
|
}
|
|
private static Cell getCellByRow(Row row, int cellIndex) {
|
Cell cell = row.getCell(cellIndex);
|
if (cell == null) {
|
cell = row.createCell(cellIndex);
|
}
|
|
return cell;
|
}
|
|
private static void writeDataToCell(Sheet sheet, List<WriteExcelData> excelDataList,Workbook workbook) throws VciBaseException {
|
if (sheet != null && !CollectionUtils.isEmpty(excelDataList)) {
|
List<WriteExcelData> mergedDataList = new ArrayList();
|
excelDataList.stream().forEach((ed) -> {
|
Row row = getRowBySheet(sheet, ed.getRow());
|
Cell cell = getCellByRow(row, ed.getCol());
|
if (ed.isMerged() && (ed.getRowTo() > ed.getRow() || ed.getColTo() > ed.getCol())) {
|
mergedDataList.add(ed);
|
}
|
// TODO: 修改编号列为文本
|
if("id".equalsIgnoreCase(ed.getObjCode())){
|
//CellStyle textStyle = workbook.createCellStyle();
|
HSSFDataFormat format = (HSSFDataFormat)sheet.getWorkbook().createDataFormat();
|
Row copyStyleRow = sheet.getRow(ed.getRow());
|
cell = copyStyleRow.getCell(ed.getCol());
|
cell.getCellStyle().setDataFormat(format.getFormat("@"));
|
}
|
|
copyStyle(sheet, cell, ed);
|
if (ed.isReadOnly() && ed.getObj() != null && !(ed.getObj() instanceof RichTextString) && CollectionUtils.isEmpty(ed.getValidationDataList())) {
|
List<String> list = new ArrayList();
|
list.add(ed.getObj().toString());
|
ed.setValidationDataList(list);
|
setValidation(sheet, ed);
|
}
|
|
centerCell(cell, ed);
|
Object value = ed.getObj();
|
if (StringUtil.isNotBlank(ed.getFontColor())) {
|
String rtsValue = value == null ? "" : value.toString();
|
RichTextString ts = cell instanceof HSSFCell ? new HSSFRichTextString(rtsValue) : new XSSFRichTextString(rtsValue);
|
Font font = sheet.getWorkbook().createFont();
|
font.setColor(VciBaseUtil.getShort(ed.getFontColor()));
|
((RichTextString)ts).applyFont(font);
|
value = ts;
|
}
|
|
if (ed.isFormula()) {
|
setFormula(cell, ed);
|
} else if (ed.isNameRefer()) {
|
setNameRefer(sheet, ed);
|
} else if (ed.isValidation()) {
|
setValidation(sheet, ed);
|
} else {
|
setCellValue(cell, value);
|
}
|
|
if (ed.getWidth() != null && ed.getWidth() > 0) {
|
sheet.setColumnWidth(cell.getColumnIndex(), (int)((double)ed.getWidth() + 0.72) * 256);
|
}
|
|
});
|
mergedRegion(sheet, mergedDataList);
|
}
|
|
}
|
|
private static void setFormula(Cell cell, WriteExcelData ed) {
|
if (ed.getObj() != null) {
|
cell.setCellFormula(ed.getObj().toString());
|
}
|
|
}
|
|
private static void setNameRefer(Sheet sheet, WriteExcelData ed) {
|
if (ed.getObj() != null) {
|
Name name = sheet.getWorkbook().createName();
|
name.setNameName(ed.getObj().toString());
|
name.setRefersToFormula(sheet.getSheetName() + "!" + getRange(ed));
|
}
|
|
}
|
|
private static void setValidation(Sheet sheet, WriteExcelData ed) {
|
if (ed.getRowTo() < ed.getRow()) {
|
ed.setRowTo(ed.getRow());
|
}
|
|
if (ed.getColTo() < ed.getCol()) {
|
ed.setColTo(ed.getCol());
|
}
|
|
CellRangeAddressList regions = new CellRangeAddressList(ed.getRow(), ed.getRowTo(), ed.getCol(), ed.getColTo());
|
DataValidation validation = null;
|
XSSFDataValidationConstraint dvConstraint;
|
if (sheet instanceof XSSFSheet) {
|
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
|
dvConstraint = null;
|
if (!CollectionUtils.isEmpty(ed.getValidationDataList())) {
|
dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint((String[])ed.getValidationDataList().toArray(new String[0]));
|
} else {
|
VciBaseUtil.alertNotNull(new Object[]{ed.getObj(), "写入有效性的时候，没有设置有效性的公式表达式"});
|
dvConstraint = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint(ed.getObj().toString());
|
}
|
|
validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, regions);
|
} else {
|
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet)sheet);
|
dvConstraint = null;
|
DataValidationConstraint dvConstraintF;
|
if (!CollectionUtils.isEmpty(ed.getValidationDataList())) {
|
dvConstraintF = dvHelper.createExplicitListConstraint((String[])ed.getValidationDataList().toArray(new String[0]));
|
} else {
|
VciBaseUtil.alertNotNull(new Object[]{ed.getObj(), "写入有效性的时候，没有设置有效性的公式表达式"});
|
dvConstraintF = dvHelper.createFormulaListConstraint(ed.getObj().toString());
|
}
|
|
validation = new HSSFDataValidation(regions, dvConstraintF);
|
}
|
|
if (validation instanceof XSSFDataValidation) {
|
((DataValidation)validation).setSuppressDropDownArrow(true);
|
((DataValidation)validation).setShowErrorBox(true);
|
} else {
|
((DataValidation)validation).setSuppressDropDownArrow(false);
|
}
|
|
if (StringUtil.isNotBlank(ed.getValidationErrorMsg())) {
|
((DataValidation)validation).createErrorBox("error", ed.getValidationErrorMsg());
|
}
|
|
sheet.addValidationData((DataValidation)validation);
|
}
|
|
private static String getRange(WriteExcelData excelData) {
|
char start = (char)(65 + excelData.getCol());
|
int rowId = excelData.getRow() + 1;
|
int endRowId = excelData.getRowTo() + 1;
|
char endPrefix;
|
if (excelData.getColTo() <= 25) {
|
endPrefix = (char)(65 + excelData.getColTo());
|
return "$" + start + "$" + rowId + ":$" + endPrefix + "$" + endRowId;
|
} else {
|
endPrefix = 'A';
|
// boolean endSuffix = true;
|
char endSuffix;
|
if ((excelData.getColTo() - 25) / 26 != 0 && excelData.getColTo() != 51) {
|
if ((excelData.getColTo() - 25) % 26 == 0) {
|
endSuffix = 'Z';
|
endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26 - 1);
|
} else {
|
endSuffix = (char)(65 + (excelData.getColTo() - 25) % 26 - 1);
|
endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26);
|
}
|
} else if ((excelData.getColTo() - 25) % 26 == 0) {
|
endSuffix = 'Z';
|
} else {
|
endSuffix = (char)(65 + (excelData.getColTo() - 25) % 26 - 1);
|
}
|
|
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + endRowId;
|
}
|
}
|
|
private static void setCellValue(Cell cell, Object value) {
|
if (value != null) {
|
if (value instanceof Date) {
|
cell.setCellValue((Date)value);
|
} else if (value instanceof String) {
|
cell.setCellValue(value.toString());
|
} else if (value instanceof Boolean) {
|
cell.setCellValue((Boolean)value);
|
} else if (value instanceof Integer) {
|
cell.setCellValue((double)(Integer)value);
|
} else if (value instanceof Long) {
|
cell.setCellValue((double)(Long)value);
|
} else if (value instanceof BigDecimal) {
|
cell.setCellValue(((BigDecimal)value).doubleValue());
|
} else if (value instanceof Double) {
|
cell.setCellValue((Double)value);
|
} else if (value instanceof RichTextString) {
|
cell.setCellValue((RichTextString)value);
|
} else {
|
cell.setCellValue(value.toString());
|
}
|
}
|
|
}
|
|
private static void copyStyle(Sheet sheet, Cell cell, WriteExcelData excelData) {
|
if (excelData.isCopyStyle()) {
|
Row copyStyleRow = sheet.getRow(excelData.getCopyStyleRow());
|
if (copyStyleRow != null) {
|
Cell copyStyleCell = copyStyleRow.getCell(excelData.getCopyStyleCol());
|
copyStyleForCell(copyStyleCell, cell);
|
if (StringUtil.isNotBlank(excelData.getDateFormat())) {
|
HSSFDataFormat format = (HSSFDataFormat)sheet.getWorkbook().createDataFormat();
|
cell.getCellStyle().setDataFormat(format.getFormat(excelData.getDateFormat()));
|
}
|
}
|
}
|
|
}
|
|
private static void copyStyleForCell(Cell fromCell, Cell toCell) {
|
CellStyle cellStyle = toCell.getCellStyle();
|
CellStyle fromStyle = fromCell.getCellStyle();
|
cellStyle.cloneStyleFrom(fromStyle);
|
cellStyle.setAlignment(fromStyle.getAlignment());
|
cellStyle.setBorderBottom(fromStyle.getBorderBottom());
|
cellStyle.setBorderLeft(fromStyle.getBorderLeft());
|
cellStyle.setBorderRight(fromStyle.getBorderRight());
|
cellStyle.setBorderTop(fromStyle.getBorderTop());
|
cellStyle.setDataFormat(fromStyle.getDataFormat());
|
cellStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
|
cellStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
|
cellStyle.setFillPattern(fromStyle.getFillPattern());
|
cellStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
|
cellStyle.setWrapText(fromStyle.getWrapText());
|
cellStyle.setLocked(fromStyle.getLocked());
|
cellStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
|
cellStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
|
cellStyle.setTopBorderColor(fromStyle.getTopBorderColor());
|
cellStyle.setRightBorderColor(fromStyle.getRightBorderColor());
|
}
|
|
private static void centerCell(Cell cell, WriteExcelData excelData) {
|
if (excelData.isCenter()) {
|
CellStyle cellStyle = cell.getCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
}
|
|
}
|
|
private static void mergedRegion(Sheet sheet, Collection<WriteExcelData> mergedDataList) {
|
if (!CollectionUtils.isEmpty(mergedDataList)) {
|
mergedDataList.stream().forEach((ed) -> {
|
if (ed.getRowTo() < ed.getRow()) {
|
ed.setRowTo(ed.getRow());
|
}
|
|
if (ed.getColTo() < ed.getCol()) {
|
ed.setColTo(ed.getCol());
|
}
|
|
sheet.addMergedRegion(new CellRangeAddress(ed.getRow(), ed.getRowTo(), ed.getCol(), ed.getColTo()));
|
});
|
}
|
|
}
|
|
public static List<SheetDataSet> readDataObjectFromExcel(File file) throws VciBaseException {
|
return readDataObjectFromExcel(file, SheetDataSet.class);
|
}
|
|
public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass) throws VciBaseException {
|
return readDataObjectFromExcel(file, doClass, (ReadExcelOption)null);
|
}
|
|
public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass, ReadExcelOption excelOption) throws VciBaseException {
|
return readDataObjectFromExcel((File)file, doClass, excelOption, (PoiImportProcesser)null);
|
}
|
|
public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass, ReadExcelOption excelOption, PoiImportProcesser<T> processer) throws VciBaseException {
|
InputStream ins = null;
|
|
try {
|
ins = new FileInputStream(file);
|
} catch (Throwable var15) {
|
String msg = "readImportFileFail";
|
if (logger.isErrorEnabled()) {
|
logger.error(msg, var15);
|
}
|
|
throw new VciBaseException(msg + LangBaseUtil.getErrorMsg(var15), new String[0], var15);
|
}
|
|
List var5;
|
try {
|
var5 = readDataObjectFromExcel((InputStream)ins, doClass, excelOption, processer);
|
} catch (VciBaseException var12) {
|
throw var12;
|
} catch (Throwable var13) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var13), new String[0], var13);
|
} finally {
|
IOUtils.closeQuietly(ins);
|
}
|
|
return var5;
|
}
|
|
public static <T> List<T> readDataObjectFromExcel(InputStream ins, Class<T> doClass, ReadExcelOption excelOption, PoiImportProcesser<T> processer) throws VciBaseException {
|
if (ins == null) {
|
throw new VciBaseException("inputStreamIsNull");
|
} else {
|
if (doClass == null) {
|
doClass = (Class<T>) SheetDataSet.class;
|
}
|
|
boolean isDataSet = false;
|
if (doClass.equals(SheetDataSet.class)) {
|
isDataSet = true;
|
}
|
|
Map<Field, ExcelColumn> excelColumnSet = getExcelColumnAnnotations(doClass);
|
Map<String, ExcelColumn> excelTitleMap = (Map)((Map)Optional.of(excelColumnSet).get()).values().stream().collect(Collectors.toMap((s) -> {
|
// return s.value();
|
// ExcelColumn s1 = (ExcelColumn) s;
|
// s1.value();
|
// ((InvocationHandler) ((Proxy) s).).memberValues.get("value");
|
return ((ExcelColumn) s).value();
|
// return ((AnnotationInvocationHandler) ((Proxy) s).h).memberValues.get("value");
|
}, (t) -> {
|
return t;
|
}));
|
Map<String, Field> excelTitleFieldMap = new HashMap();
|
Map<String, ExcelColumnMap> extendAttrTitleMap = new HashMap();
|
Field[] rowIndexFields = new Field[]{null};
|
ReadExcelOption finalExcelOption = excelOption;
|
excelColumnSet.forEach((field, excelColumnx) -> {
|
excelTitleFieldMap.put(excelColumnx.value(), field);
|
if (excelColumnx.rowIndexColumn()) {
|
rowIndexFields[0] = field;
|
}
|
|
field.setAccessible(true);
|
if ((field.getType().equals(Map.class) || field.getType().getSuperclass().equals(Map.class)) && !CollectionUtils.isEmpty(finalExcelOption.getExtendAttrMap()) && finalExcelOption.getExtendAttrMap().containsKey(excelColumnx.value())) {
|
List<ExcelColumnMap> excelColumnMaps = (List) finalExcelOption.getExtendAttrMap().get(excelColumnx.value());
|
if (!CollectionUtils.isEmpty(excelColumnMaps)) {
|
excelColumnMaps.stream().forEach((ss) -> {
|
excelTitleFieldMap.put(ss.getTitle(), field);
|
extendAttrTitleMap.put(ss.getTitle(), ss);
|
});
|
}
|
}
|
|
});
|
Field rowIndexField = null;
|
if (rowIndexFields[0] != null) {
|
rowIndexField = rowIndexFields[0];
|
}
|
|
Workbook workbook = getWorkbookByInputStream(ins);
|
if (excelOption == null) {
|
excelOption = new ReadExcelOption();
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
List<T> dataSet = new ArrayList();
|
|
try {
|
for(int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); ++sheetIndex) {
|
Sheet sheet = workbook.getSheetAt(sheetIndex);
|
if (StringUtil.isNotBlank(excelOption.getSheetName())) {
|
if (!sheet.getSheetName().equalsIgnoreCase(excelOption.getSheetName())) {
|
continue;
|
}
|
} else if (!excelOption.isReadAllSheet() && sheetIndex != excelOption.getSheetIndex()) {
|
continue;
|
}
|
|
SheetDataSet sheetDataSet = new SheetDataSet();
|
sheetDataSet.setSheetName(sheet.getSheetName());
|
if (isDataSet) {
|
dataSet.add((T) sheetDataSet);
|
}
|
|
Map<Integer, String> colsNameIndexMap = new HashMap();
|
int rowIndex;
|
if (excelOption.getFristRow() > 0) {
|
try {
|
Row titleRow = sheet.getRow(excelOption.getFristRow() - 1);
|
rowIndex = titleRow.getLastCellNum();
|
List<String> titleList = new LinkedList();
|
|
for(int i = 0; i < rowIndex; ++i) {
|
String title = VciBaseUtil.getStringValueFromObject(getCellValue(titleRow.getCell(i), evaluator));
|
if (StringUtil.isNotBlank(title)) {
|
title = title.replace("*", "").replace("☆", "");
|
colsNameIndexMap.put(i, title);
|
titleList.add(title);
|
}
|
}
|
|
if (isDataSet) {
|
sheetDataSet.setColName(titleList);
|
}
|
} catch (Exception var52) {
|
if (logger.isErrorEnabled()) {
|
logger.error("读取excel文件中的标题信息出现了错误", var52);
|
}
|
|
throw var52;
|
}
|
}
|
|
List<SheetRowData> rowDataList = new LinkedList();
|
if (sheet.getLastRowNum() > 0) {
|
for(rowIndex = excelOption.getFristRow(); rowIndex <= sheet.getLastRowNum(); ++rowIndex) {
|
Row rowDataSet = sheet.getRow(rowIndex);
|
if (rowDataSet != null) {
|
boolean notValue = false;
|
|
for(int i = 0; i < rowDataSet.getLastCellNum(); ++i) {
|
Cell cellTemp = rowDataSet.getCell(i);
|
Object valueTemp = getCellValue(cellTemp, evaluator);
|
if (valueTemp != null && !(valueTemp instanceof String) || valueTemp instanceof String && valueTemp != null && StringUtil.isNotBlank(valueTemp.toString())) {
|
notValue = true;
|
break;
|
}
|
}
|
|
if (notValue) {
|
SheetRowData rd = isDataSet ? new SheetRowData() : null;
|
T obj = null;
|
if (!isDataSet) {
|
try {
|
obj = doClass.newInstance();
|
} catch (Throwable var49) {
|
if (logger.isErrorEnabled()) {
|
logger.error("实例化数据对象", var49);
|
}
|
|
throw new VciBaseException("实例化数据对象," + LangBaseUtil.getErrorMsg(var49));
|
}
|
}
|
|
if (isDataSet) {
|
rd.setRowIndex(String.valueOf(rowIndex));
|
}
|
|
if (!isDataSet && rowIndexField != null) {
|
VciBaseUtil.setValueForField(rowIndexField, obj, String.valueOf(rowIndex));
|
}
|
|
Map<Integer, String> rowDataMap = new HashMap();
|
|
for(int columnIndex = 0; columnIndex < sheetDataSet.getColName().size(); ++columnIndex) {
|
Cell cell = rowDataSet.getCell(columnIndex);
|
Object value = getCellValue(cell, evaluator);
|
String title = colsNameIndexMap.containsKey(columnIndex) ? (String)colsNameIndexMap.get(columnIndex) : "";
|
boolean isMapField = false;
|
Field thisField = null;
|
if (StringUtil.isNotBlank(title) && excelTitleFieldMap.containsKey(title)) {
|
thisField = (Field)excelTitleFieldMap.get(title);
|
}
|
|
if (thisField != null) {
|
thisField.setAccessible(true);
|
if (thisField.getType().equals(Map.class) || thisField.getType().getSuperclass().equals(Map.class)) {
|
isMapField = true;
|
}
|
}
|
|
ExcelColumn excelColumn = null;
|
if (StringUtil.isNotBlank(title) && excelTitleMap.containsKey(title)) {
|
excelColumn = (ExcelColumn)excelTitleMap.get(title);
|
}
|
|
ExcelColumnMap columnMap = null;
|
if (isMapField) {
|
columnMap = (ExcelColumnMap)extendAttrTitleMap.get(title);
|
}
|
|
boolean isNull = false;
|
if (value == null || value instanceof String && StringUtil.isBlank(value.toString())) {
|
isNull = true;
|
}
|
|
if (!isNull) {
|
if (!isDataSet) {
|
if (thisField != null && (excelColumn != null || columnMap != null)) {
|
if (isMapField) {
|
Object fieldValue = thisField.get(obj);
|
Object data;
|
if (fieldValue == null) {
|
data = new HashMap();
|
} else {
|
data = (Map)fieldValue;
|
}
|
|
((Map)data).put(columnMap.getColumnName(), value == null ? "" : value.toString());
|
|
try {
|
thisField.set(obj, data);
|
} catch (IllegalAccessException var50) {
|
if (logger.isErrorEnabled()) {
|
logger.error("设置map类型的数据时候出现了错误", var50);
|
}
|
}
|
} else {
|
if (value.getClass().equals(thisField.getType())) {
|
try {
|
thisField.set(obj, value);
|
} catch (IllegalAccessException var51) {
|
if (logger.isErrorEnabled()) {
|
logger.error("读取excel的单元格的值后，为对象赋值出现了错误", var51);
|
}
|
}
|
} else {
|
VciBaseUtil.setValueForField(thisField, obj, VciBaseUtil.getStringValueFromObject(value));
|
}
|
|
if (thisField.getType().equals(Date.class) && thisField.get(obj) == null) {
|
throw new VciBaseException("cellNotDateFormate", new String[]{title, rowIndex + 1 + ""});
|
}
|
|
if (StringUtil.isNotBlank(excelColumn.regExg()) && !VciBaseUtil.getStringValueFromObject(value).matches(excelColumn.regExg())) {
|
throw new VciBaseException("cellValueNotEqualReg", new String[]{title, rowIndex + 1 + "", excelColumn.regExgTitle()});
|
}
|
}
|
}
|
|
if (processer != null && thisField != null) {
|
processer.process(value, obj, thisField);
|
}
|
} else {
|
rowDataMap.put(columnIndex, VciBaseUtil.getStringValueFromObject(value));
|
}
|
} else {
|
if (!isDataSet && thisField != null && excelColumn != null) {
|
if (!excelColumn.nullable()) {
|
throw new VciBaseException("cellValueCanNotNull", new String[]{title, rowIndex + 1 + ""});
|
}
|
|
if (StringUtil.isNotBlank(excelColumn.regExg())) {
|
throw new VciBaseException("cellValueCanNotNullForReg", new String[]{title, rowIndex + 1 + ""});
|
}
|
}
|
rowDataMap.put(columnIndex, VciBaseUtil.getStringValueFromObject(value));
|
}
|
}
|
|
if (isDataSet) {
|
rd.setData(rowDataMap);
|
rowDataList.add(rd);
|
} else {
|
dataSet.add(obj);
|
}
|
}
|
}
|
}
|
}
|
|
if (isDataSet) {
|
sheetDataSet.setRowData(rowDataList);
|
}
|
}
|
} catch (Throwable var53) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var53), new String[0], var53);
|
} finally {
|
IOUtils.closeQuietly(ins);
|
|
try {
|
workbook.close();
|
} catch (IOException var48) {
|
if (logger.isErrorEnabled()) {
|
logger.error("关闭工作簿出现了错误", var48);
|
}
|
}
|
|
}
|
|
return dataSet;
|
}
|
}
|
|
private static Object getCellValue(Cell cell, FormulaEvaluator evaluator) {
|
if (cell != null) {
|
Object cellValue = null;
|
CellType cellTypeEnum = cell.getCellTypeEnum();
|
switch (cellTypeEnum) {
|
case STRING:
|
String thisCellValue = cell.getStringCellValue();
|
cellValue = StringUtil.isEmpty(thisCellValue) ? "" : thisCellValue.trim();
|
break;
|
case BOOLEAN:
|
boolean boolValue = cell.getBooleanCellValue();
|
cellValue = boolValue;
|
break;
|
case FORMULA:
|
cellValue = getCellValue(evaluator.evaluate(cell));
|
break;
|
case NUMERIC:
|
if (DateUtil.isCellDateFormatted(cell)) {
|
cellValue = cell.getDateCellValue();
|
} else {
|
cellValue = cell.getNumericCellValue();
|
}
|
break;
|
default:
|
cellValue = "";
|
}
|
|
return cellValue;
|
} else {
|
return null;
|
}
|
}
|
|
private static Object getCellValue(CellValue cell) {
|
Object cellValue = null;
|
switch (cell.getCellTypeEnum()) {
|
case STRING:
|
String thisCellValue = cell.getStringValue();
|
cellValue = StringUtil.isEmpty(thisCellValue) ? "" : thisCellValue.trim();
|
break;
|
case NUMERIC:
|
cellValue = cell.getNumberValue();
|
}
|
|
return cellValue;
|
}
|
|
public static <T> Map<Field, ExcelColumn> getExcelColumnAnnotations(Class<T> doClass) {
|
Map<Field, ExcelColumn> excelColumnMap = new HashMap();
|
if (!doClass.equals(SheetDataSet.class)) {
|
List<Field> allFields = VciBaseUtil.getAllFieldForObj(doClass);
|
List<Field> hasExcelColumnFields = (List)allFields.stream().filter((field) -> {
|
return field.isAnnotationPresent(ExcelColumn.class);
|
}).collect(Collectors.toList());
|
if (!CollectionUtils.isEmpty(hasExcelColumnFields)) {
|
hasExcelColumnFields.stream().forEach((field) -> {
|
ExcelColumn ec = getExcelColumnAnnotation(field);
|
excelColumnMap.put(field, ec);
|
});
|
}
|
}
|
|
return excelColumnMap;
|
}
|
|
public static ExcelColumn getExcelColumnAnnotation(Field field) {
|
ExcelColumn ec = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
|
if (ec == null) {
|
ec = (ExcelColumn)field.getDeclaredAnnotation(ExcelColumn.class);
|
}
|
|
return ec;
|
}
|
|
public static <T> String downloadImportTempByClass(Class<T> doClass, DownloadTempOption downloadTempOption, PoiTemplateProcesser<T> processer) {
|
if (downloadTempOption == null) {
|
downloadTempOption = new DownloadTempOption("");
|
}
|
|
String excelName = LocalFileUtil.getDefaultTempFolder() + File.separator + (StringUtil.isNotBlank(downloadTempOption.getExcelName()) ? downloadTempOption.getExcelName() : "导入模板.xls");
|
|
try {
|
(new File(excelName)).createNewFile();
|
} catch (Throwable var22) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(var22), new String[]{excelName}, var22);
|
}
|
|
List<WriteExcelData> excelDataList = new ArrayList();
|
Map<Field, ExcelColumn> fieldExcelColumnMap = getExcelColumnAnnotations(doClass);
|
Map<String, ExcelColumn> fieldNameExcelColumnMap = new HashMap();
|
fieldExcelColumnMap.forEach((fieldx, ec) -> {
|
fieldx.setAccessible(true);
|
fieldNameExcelColumnMap.put(fieldx.getName(), ec);
|
});
|
Field[] fields = doClass.getDeclaredFields();
|
Set<String> finedFields = new HashSet();
|
new HSSFWorkbook();
|
int index = 0;
|
|
for(int i = 0; i < fields.length; ++i) {
|
Field field = fields[i];
|
field.setAccessible(true);
|
String fieldName = field.getName();
|
if (fieldNameExcelColumnMap.containsKey(fieldName)) {
|
finedFields.add(fieldName);
|
ExcelColumn column = (ExcelColumn)fieldNameExcelColumnMap.get(fieldName);
|
if (!column.rowIndexColumn()) {
|
WriteExcelData excelData = new WriteExcelData(0, index, StringUtil.isNotBlank(column.value()) ? column.value() : fieldName);
|
if (!column.nullable()) {
|
excelData.setFontColor(String.valueOf(HSSFColor.HSSFColorPredefined.RED.getIndex()));
|
excelData.setObj(excelData.getObj() + "*");
|
}
|
|
excelData.setReadOnly(true);
|
excelData.setCenter(true);
|
if (column.width() > 0) {
|
excelData.setWidth(column.width());
|
}
|
|
List<WriteExcelData> thisRowNextDatas = new ArrayList();
|
if (downloadTempOption.getDefaultRowSize() == null || downloadTempOption.getDefaultRowSize() < 0) {
|
downloadTempOption.setDefaultRowSize(1);
|
}
|
|
Integer defaultRowSize = downloadTempOption.getDefaultRowSize();
|
|
for(int j = 0; j < defaultRowSize; ++j) {
|
thisRowNextDatas.add(new WriteExcelData(1 + j, index, ""));
|
}
|
|
if (StringUtil.isNotBlank(column.enumId()) && processer != null) {
|
Map<String, String> enumMap = processer.wrapperEnumMap(column.enumId(), doClass, field);
|
if (!CollectionUtils.isEmpty(enumMap)) {
|
WriteExcelData thisRowNext = (WriteExcelData)thisRowNextDatas.get(0);
|
thisRowNext.setValidation(true);
|
thisRowNext.setRowTo(defaultRowSize);
|
thisRowNext.setColTo(thisRowNext.getCol());
|
thisRowNext.setValidationDataList((List)enumMap.values().stream().collect(Collectors.toList()));
|
}
|
}
|
|
if (field.isAnnotationPresent(VciFieldType.class)) {
|
VciFieldType fieldType = (VciFieldType)field.getDeclaredAnnotation(VciFieldType.class);
|
if (fieldType == null) {
|
fieldType = (VciFieldType)field.getAnnotation(VciFieldType.class);
|
}
|
|
if (fieldType != null) {
|
String dateFormat;
|
if (VciFieldTypeEnum.VTDateTime.equals(fieldType.value())) {
|
dateFormat = "yyyy-MM-dd HH:mm:ss";
|
} else if (VciFieldTypeEnum.VTDate.equals(fieldType.value())) {
|
dateFormat = "yyyy-MM-dd";
|
} else if (VciFieldTypeEnum.VTTime.equals(fieldType.value())) {
|
dateFormat = "HH:mm:ss";
|
} else {
|
dateFormat = "";
|
}
|
|
if (StringUtil.isNotBlank(dateFormat)) {
|
thisRowNextDatas.stream().forEach((thisRowNextx) -> {
|
thisRowNextx.setDateFormat(dateFormat);
|
});
|
}
|
|
if (VciFieldTypeEnum.VTBoolean.equals(fieldType.value())) {
|
List<String> trueFlase = new ArrayList();
|
trueFlase.add("是");
|
trueFlase.add("否");
|
WriteExcelData thisRowNext = (WriteExcelData)thisRowNextDatas.get(0);
|
thisRowNext.setValidation(true);
|
thisRowNext.setRowTo(defaultRowSize);
|
thisRowNext.setColTo(thisRowNext.getCol());
|
thisRowNext.setValidationDataList(trueFlase);
|
}
|
}
|
}
|
|
excelDataList.addAll(thisRowNextDatas);
|
excelDataList.add(excelData);
|
++index;
|
}
|
}
|
}
|
|
WriteExcelOption excelOption = new WriteExcelOption();
|
excelOption.addSheetDataList(StringUtil.isNotBlank(downloadTempOption.getSheetName()) ? downloadTempOption.getSheetName() : "Sheet1", excelDataList);
|
writeDataToFile(excelName, excelOption);
|
return excelName;
|
}
|
|
public static void mergeExcel(List<String> files, String excelName) {
|
Workbook newExcelCreat = excelName.toLowerCase(Locale.ROOT).endsWith(".xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
|
|
for(int i = 0; i < files.size(); ++i) {
|
String fromExcelName = (String)files.get(i);
|
|
try {
|
InputStream in = new FileInputStream(fromExcelName);
|
Throwable var6 = null;
|
|
try {
|
Workbook fromExcel = null;
|
if (fromExcelName.toLowerCase(Locale.ROOT).endsWith(".xlsx")) {
|
fromExcel = new XSSFWorkbook(in);
|
} else {
|
fromExcel = new HSSFWorkbook(in);
|
}
|
|
int length = ((Workbook)fromExcel).getNumberOfSheets();
|
Sheet oldSheet;
|
if (length <= 1) {
|
oldSheet = ((Workbook)fromExcel).getSheetAt(0);
|
oldSheet = ((Workbook)newExcelCreat).getSheet(oldSheet.getSheetName());
|
String newSheetName = oldSheet.getSheetName();
|
if (oldSheet != null) {
|
newSheetName = newSheetName + "_" + i;
|
}
|
|
Sheet newSheet = ((Workbook)newExcelCreat).createSheet(newSheetName);
|
copySheet((Workbook)newExcelCreat, oldSheet, newSheet);
|
} else {
|
for(int j = 0; j < length; ++j) {
|
oldSheet = ((Workbook)fromExcel).getSheetAt(j);
|
Sheet existSheet = ((Workbook)newExcelCreat).getSheet(oldSheet.getSheetName());
|
String newSheetName = oldSheet.getSheetName();
|
if (existSheet != null) {
|
newSheetName = newSheetName + "_" + i + "_" + j;
|
}
|
|
Sheet newSheet = ((Workbook)newExcelCreat).createSheet(newSheetName);
|
copySheet((Workbook)newExcelCreat, oldSheet, newSheet);
|
}
|
}
|
|
List<? extends Name> allNames = ((Workbook)fromExcel).getAllNames();
|
if (!CollectionUtils.isEmpty(allNames)) {
|
allNames.stream().forEach((name) -> {
|
Name name1 = newExcelCreat.createName();
|
name1.setNameName(name.getNameName());
|
name1.setRefersToFormula(name.getRefersToFormula());
|
});
|
}
|
} catch (Throwable var64) {
|
var6 = var64;
|
throw var64;
|
} finally {
|
if (in != null) {
|
if (var6 != null) {
|
try {
|
in.close();
|
} catch (Throwable var58) {
|
var6.addSuppressed(var58);
|
}
|
} else {
|
in.close();
|
}
|
}
|
|
}
|
} catch (IOException var66) {
|
throw new VciBaseException("合并excel出错的了", new String[0], var66);
|
}
|
}
|
|
String allFileName = excelName;
|
|
try {
|
FileOutputStream fileOut = new FileOutputStream(allFileName);
|
Throwable var69 = null;
|
|
try {
|
((Workbook)newExcelCreat).write(fileOut);
|
fileOut.flush();
|
} catch (Throwable var60) {
|
var69 = var60;
|
throw var60;
|
} finally {
|
if (fileOut != null) {
|
if (var69 != null) {
|
try {
|
fileOut.close();
|
} catch (Throwable var59) {
|
var69.addSuppressed(var59);
|
}
|
} else {
|
fileOut.close();
|
}
|
}
|
|
}
|
} catch (IOException var62) {
|
var62.printStackTrace();
|
} finally {
|
try {
|
((Workbook)newExcelCreat).close();
|
} catch (IOException var57) {
|
var57.printStackTrace();
|
}
|
|
}
|
|
}
|
|
private static void mergeSheetAllRegion(Sheet fromSheet, Sheet toSheet) {
|
int num = fromSheet.getNumMergedRegions();
|
CellRangeAddress cellR = null;
|
|
for(int i = 0; i < num; ++i) {
|
cellR = fromSheet.getMergedRegion(i);
|
toSheet.addMergedRegion(cellR);
|
}
|
|
}
|
|
private static void copyCell(Cell fromCell, Cell toCell) {
|
copyStyleForCell(fromCell, toCell);
|
if (fromCell.getCellComment() != null) {
|
toCell.setCellComment(fromCell.getCellComment());
|
}
|
|
CellType fromCellType = fromCell.getCellType();
|
toCell.setCellType(fromCellType);
|
if (fromCellType == CellType.NUMERIC) {
|
if (DateUtil.isCellDateFormatted(fromCell)) {
|
toCell.setCellValue(fromCell.getDateCellValue());
|
} else {
|
toCell.setCellValue(fromCell.getNumericCellValue());
|
}
|
} else if (fromCellType == CellType.STRING) {
|
RichTextString value = fromCell.getRichStringCellValue();
|
if (value != null && StringUtil.isNotBlank(value.toString())) {
|
Font fontAt = fromCell.getSheet().getWorkbook().getFontAt(fromCell.getCellStyle().getFontIndexAsInt());
|
value.applyFont(fontAt);
|
}
|
|
toCell.setCellValue(value);
|
} else if (fromCellType != CellType.BLANK) {
|
if (fromCellType == CellType.BOOLEAN) {
|
toCell.setCellValue(fromCell.getBooleanCellValue());
|
} else if (fromCellType == CellType.ERROR) {
|
toCell.setCellErrorValue(fromCell.getErrorCellValue());
|
} else if (fromCellType == CellType.FORMULA) {
|
toCell.setCellFormula(fromCell.getCellFormula());
|
}
|
}
|
|
}
|
|
private static void copyRow(Workbook wb, Row oldRow, Row toRow) {
|
toRow.setHeight(oldRow.getHeight());
|
Iterator cellIt = oldRow.cellIterator();
|
|
while(cellIt.hasNext()) {
|
Cell tmpCell = (Cell)cellIt.next();
|
Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
|
copyCell(tmpCell, newCell);
|
}
|
|
}
|
|
private static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet) {
|
mergeSheetAllRegion(fromSheet, toSheet);
|
int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
|
|
for(int i = 0; i <= length; ++i) {
|
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
|
}
|
|
Iterator rowIt = fromSheet.rowIterator();
|
|
while(rowIt.hasNext()) {
|
Row oldRow = (Row)rowIt.next();
|
Row newRow = toSheet.createRow(oldRow.getRowNum());
|
copyRow(wb, oldRow, newRow);
|
}
|
|
List<? extends DataValidation> dataValidations = fromSheet.getDataValidations();
|
if (!CollectionUtils.isEmpty(dataValidations)) {
|
dataValidations.stream().forEach((dv) -> {
|
toSheet.addValidationData(dv);
|
});
|
}
|
|
}
|
|
public static <T> void appendDataToExcel(Collection<T> data, String excelName, AppendDataExcelOption excelOption) {
|
if (!CollectionUtils.isEmpty(data)) {
|
T t1 = data.stream().findFirst().get();
|
Map<Field, ExcelColumn> excelColumnSet = getExcelColumnAnnotations(t1.getClass());
|
Map<String, String> excelTitleFieldNameMap = new HashMap();
|
Set<String> readOnlyFields = new HashSet();
|
excelColumnSet.forEach((field, excelColumn) -> {
|
excelTitleFieldNameMap.put(excelColumn.value(), field.getName());
|
if (excelColumn.readOnly()) {
|
readOnlyFields.add(field.getName());
|
}
|
|
});
|
ReadExcelOption readExcelOption = new ReadExcelOption();
|
readExcelOption.setSheetName(excelOption.getSheetName());
|
readExcelOption.setFristRow(excelOption.getTitleRowIndex() + 1);
|
List<SheetDataSet> sheetDataSets = readDataObjectFromExcel(new File(excelName), SheetDataSet.class, readExcelOption);
|
if (!CollectionUtils.isEmpty(sheetDataSets)) {
|
SheetDataSet sheetDataSet = (SheetDataSet)sheetDataSets.get(0);
|
Map<String, Integer> fieldColumnMap = new HashMap();
|
List<String> colName = sheetDataSet.getColName();
|
if (!CollectionUtils.isEmpty(colName)) {
|
for(int i = 0; i < colName.size(); ++i) {
|
String col = ((String)colName.get(i)).replace("*", "").replace("☆", "");
|
if (excelTitleFieldNameMap.containsKey(col)) {
|
fieldColumnMap.put(excelTitleFieldNameMap.get(col), i);
|
}
|
}
|
|
List<WriteExcelData> writeExcelDataList = new ArrayList();
|
Integer[] index = new Integer[]{excelOption.getTitleRowIndex() + 1};
|
data.stream().forEach((d) -> {
|
Map<String, String> dataMap = VciBaseUtil.objectToMapString(d);
|
fieldColumnMap.forEach((field, col) -> {
|
if (CollectionUtils.isEmpty(excelOption.getWriteFields()) || excelOption.getWriteFields().contains(field)) {
|
WriteExcelData excelData = new WriteExcelData(index[0], col, dataMap.getOrDefault(field, ""));
|
excelData.setReadOnly(excelOption.isReadOnlyFromField() && readOnlyFields.contains(field) || !CollectionUtils.isEmpty(excelOption.getReadOnlyFields()) && excelOption.getReadOnlyFields().contains(field));
|
writeExcelDataList.add(excelData);
|
}
|
|
});
|
Integer var7 = index[0];
|
Integer var8 = index[0] = index[0] + 1;
|
});
|
WriteExcelOption writeExcelOption = new WriteExcelOption();
|
writeExcelOption.addSheetDataList(excelOption.getSheetName(), writeExcelDataList);
|
writeExcelOption.setAppend(true);
|
writeDataToFile(excelName, writeExcelOption);
|
}
|
}
|
}
|
|
}
|
}
|
