package com.vci.starter.poi.util;
|
|
import cn.hutool.core.collection.CollUtil;
|
import com.vci.starter.poi.annotation.ExcelColumn;
|
import com.vci.starter.poi.annotation.ExcelTitle;
|
import com.vci.starter.poi.bo.*;
|
import com.vci.starter.web.annotation.VciFieldType;
|
import com.vci.starter.web.enumpck.BooleanEnum;
|
import com.vci.starter.web.enumpck.VciFieldTypeEnum;
|
import com.vci.starter.web.exception.VciBaseException;
|
import com.vci.starter.web.util.LangBaseUtil;
|
import com.vci.starter.web.util.LocalFileUtil;
|
import com.vci.starter.web.util.VciBaseUtil;
|
import com.vci.starter.web.util.VciDateUtil;
|
import org.apache.commons.io.IOUtils;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.hssf.usermodel.*;
|
|
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.*;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
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;
|
|
import static com.vci.starter.poi.constant.ExcelLangCodeConstant.*;
|
|
/**
|
* excel操作类
|
* 2020/5/8，引入4.1版本的poi，全部的接口发生变化
|
* @author weidy
|
* @date 2020/2/19
|
*/
|
public class ExcelUtil {
|
|
/**
|
* 日志
|
*/
|
private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
|
|
/**
|
* 在读取文件的内容的时候，poi会自动判断文件是否为excel，所以不需要单独的方法，另外07还是03也不需要推断
|
* 提供写入到excel的方法，包括指明单元格和对象的两种方式
|
* 提供读取excel的方法，包括返回对象和map数据的两种方式
|
*/
|
|
|
/**
|
* 拷贝文件
|
*
|
* @param source 源文件
|
* @param target 目标文件
|
* @throws VciBaseException 拷贝文件出现错误或者文件不存在的时候会报错
|
*/
|
public static void copyFile(File source, File target) throws VciBaseException {
|
VciBaseUtil.alertNotNull(source, "来源文件", target, "目标文件");
|
if (!source.exists()) {
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{source.getName()});
|
}
|
if (!target.exists()) {
|
File folder = target.getParentFile();
|
folder.mkdirs();
|
try {
|
target.createNewFile();
|
} catch (IOException e) {
|
throw new VciBaseException(FILE_IO_EXCEPTION, new String[]{target.getName()});
|
}
|
}
|
FileInputStream is = null;
|
FileOutputStream os = null;
|
try {
|
is = new FileInputStream(source);
|
} catch (FileNotFoundException e) {
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{source.getName()});
|
}
|
try {
|
os = new FileOutputStream(target);
|
} catch (FileNotFoundException e) {
|
IOUtils.closeQuietly(is);
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{target.getName()});
|
}
|
try {
|
copyFile(is, os);
|
} catch (VciBaseException e) {
|
throw e;
|
} catch (Throwable e) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{source.getName(), target.getName()}, e);
|
} finally {
|
//防止因为异常没有关闭
|
IOUtils.closeQuietly(is);
|
IOUtils.closeQuietly(os);
|
}
|
}
|
|
/**
|
* 拷贝文件
|
*
|
* @param in 文件输入流
|
* @param os 文件输出流
|
* @throws VciBaseException 文件不存在或者写入错误的时候会抛出异常
|
*/
|
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();
|
break;
|
}
|
if ((sourceChannel.size() - sourceChannel.position()) < 20971520) {
|
length = (int) (sourceChannel.size() - sourceChannel.position());
|
} else {
|
length = 20971520;
|
}
|
sourceChannel.transferTo(sourceChannel.position(), length, targetChannel);
|
sourceChannel.position(sourceChannel.position() + length);
|
i++;
|
}
|
} catch (IOException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} finally {
|
IOUtils.closeQuietly(in);
|
IOUtils.closeQuietly(os);
|
}
|
}
|
|
/**
|
* 将jar中的文件拷贝到目标文件
|
*
|
* @param filePathInJar 在jar中的相对路径
|
* @param targetFile 目标文件
|
* @throws VciBaseException 拷贝出错的时候会抛出异常
|
*/
|
public static void copyFileFromJar(String filePathInJar, File targetFile) throws VciBaseException {
|
VciBaseUtil.alertNotNull(filePathInJar, "来源文件", targetFile, "目标文件");
|
if (!targetFile.exists()) {
|
File folder = targetFile.getParentFile();
|
folder.mkdirs();
|
try {
|
targetFile.createNewFile();
|
} catch (IOException e) {
|
throw new VciBaseException(FILE_IO_EXCEPTION, new String[]{targetFile.getName()});
|
}
|
}
|
FileOutputStream os = null;
|
InputStream ins = null;
|
try {
|
os = new FileOutputStream(targetFile);
|
} catch (FileNotFoundException e) {
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{targetFile.getName()});
|
}
|
try {
|
ins = ExcelUtil.class.getResourceAsStream(filePathInJar);
|
IOUtils.copy(ins, os);
|
} catch (IOException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("拷贝源文件到目标文件出现了错误", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} finally {
|
IOUtils.closeQuietly(ins);
|
IOUtils.closeQuietly(os);
|
}
|
}
|
|
/**
|
* 写入数据到excel文件中
|
*
|
* @param fileName 文件的名称，不能是jar中的文件，要包括绝对路径
|
* @param writeExcelOption 要写入的数据和配置
|
* @throws VciBaseException 文件未找到,写入数据出现了错误
|
*/
|
public static void writeDataToFile(String fileName, WriteExcelOption writeExcelOption) throws VciBaseException {
|
writeDataToFile(new File(fileName), writeExcelOption);
|
}
|
/**
|
* 写入数据到excel文件中
|
*
|
* @param file 文件对象
|
* @param writeExcelOption 要写入的数据和配置
|
* @param writeExcelOption 要写入的数据和配置
|
* @throws VciBaseException 文件未找到,写入数据出现了错误
|
*/
|
public static void writeDataToFile(File file, WriteExcelOption writeExcelOption) throws VciBaseException {
|
if (file == null) {
|
throw new VciBaseException("文件为空，无法找到文件");
|
}
|
if (!file.exists()) {
|
File folder = file.getParentFile();
|
if (!folder.exists()) {
|
folder.mkdirs();
|
}
|
try {
|
file.createNewFile();
|
} catch (IOException e) {
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{file.getName()});
|
}
|
}
|
Workbook workbook = null;
|
if(writeExcelOption!=null && writeExcelOption.isAppend()){
|
try {
|
if (file.length() == 0) {
|
workbook = createWorkbook(false);
|
}else {
|
workbook = getWorkbookByInputStream(new FileInputStream(file));
|
}
|
} catch (FileNotFoundException e) {
|
throw new VciBaseException("fileNotExist",new String[]{file.getName()},e);
|
}
|
}
|
OutputStream os = null;
|
try {
|
os = new FileOutputStream(file);
|
} catch (FileNotFoundException e) {
|
throw new VciBaseException(FILE_NOT_FOUND, new String[]{e.getMessage()}, e);
|
}
|
try {
|
writeDataToFile(os, writeExcelOption,workbook);
|
} catch (VciBaseException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到excel出错", e);
|
}
|
throw e;
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到excel出错", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} finally {
|
//防止之前因为异常没有关闭
|
IOUtils.closeQuietly(os);
|
}
|
}
|
|
/**
|
* 写入数据到excel文件中
|
*
|
* @param os 文件输出流，必须是excel文件的输出流
|
* @param writeExcelOption 要写入的数据和配置
|
* @throws VciBaseException 文件未找到,写入数据出现了错误
|
*/
|
public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption) throws VciBaseException {
|
writeDataToFile(os,writeExcelOption,null);
|
}
|
/**
|
* 写入数据到excel文件中
|
*
|
* @param os 文件输出流，必须是excel文件的输出流
|
* @param writeExcelOption 要写入的数据和配置
|
* @param workbook 工作簿对象，是追加数据的时候先获取workbook
|
* @throws VciBaseException 文件未找到,写入数据出现了错误
|
*/
|
public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption,Workbook workbook) throws VciBaseException {
|
if (os == null) {
|
throw new VciBaseException(OUTPUT_STREAM_IS_NULL);
|
}
|
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);
|
} catch (VciBaseException e) {
|
IOUtils.closeQuietly(os);
|
throw e;
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("写入数据到工作簿出错", e);
|
}
|
IOUtils.closeQuietly(os);
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
}
|
});
|
//是否有隐藏工作表
|
if(!CollectionUtils.isEmpty(writeExcelOption.getHideSheetList())){
|
writeExcelOption.getHideSheetList().stream().forEach(sheetName ->{
|
Sheet sheet = getSheetByWorkbook(finalWb, sheetName);
|
finalWb.setSheetHidden(finalWb.getSheetIndex(sheet),true);
|
});
|
}
|
|
//到这里说明已经写到工作簿
|
try {
|
wb.write(os);
|
} catch (IOException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("把工作簿上的数据写入到文件出错", e);
|
}
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} finally {
|
IOUtils.closeQuietly(os);
|
try {
|
wb.close();
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("关闭工作簿", e);
|
}
|
}
|
}
|
}
|
}
|
|
/**
|
* 获取excel某个属性所在的列索引
|
* @param excelFileName excel文件的名称
|
* @param poClass poi导入导出对象所属的类
|
* @param poFieldName 要查询的属性的名称
|
* @param sheetName 工作表的名称
|
* @return 列的索引，不存在的时候返回-1
|
*/
|
public static int getCellIndexForPoField(String excelFileName,String sheetName,Class<?> poClass,String poFieldName) throws VciBaseException{
|
return getCellIndexForPoField(new File(excelFileName),sheetName,poClass,poFieldName);
|
}
|
|
/**
|
* 获取excel某个属性所在的列索引
|
* @param excelFile excel文件的名称
|
* @param poClass poi导入导出对象所属的类
|
* @param poFieldName 要查询的属性的名称
|
* @param sheetName 工作表的名称
|
* @return 列的索引，不存在的时候返回-1
|
*/
|
public static int getCellIndexForPoField(File excelFile,String sheetName,Class<?> poClass,String poFieldName) throws VciBaseException{
|
VciBaseUtil.alertNotNull(excelFile,"excel的文件",poClass,"poi导入导出对象所属的类",poFieldName,"属性的名称");
|
if(!excelFile.exists()){
|
throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
|
}
|
if(StringUtils.isBlank(sheetName)){
|
sheetName = "Sheet1";
|
}
|
Field field = VciBaseUtil.getFieldForObject(poFieldName, poClass);
|
if(field == null){
|
throw new VciBaseException("对象中不存在此属性，{0}，{1}",new String[]{poFieldName,poClass.getName()});
|
}
|
if(field.isAnnotationPresent(ExcelColumn.class)){
|
ExcelColumn ec = getExcelColumnAnnotation(field);
|
String columnName = ec.value();
|
Workbook workbook = null;
|
try {
|
workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
|
} catch (FileNotFoundException e) {
|
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 e) {
|
logger.error("关闭工作表出现错误",e);
|
}
|
return i;
|
}
|
}
|
}
|
}
|
}
|
}
|
return -1;
|
}
|
|
/**
|
* 读取po对象里的各个属性的所在列的位置
|
* @param excelFile excel文件
|
* @param sheetName 列的名称
|
* @param poClass 导入导出的对象
|
* @return key是属性名称，value是列所在的位置
|
* @throws VciBaseException 文件不存在，读取出错的时候会抛出异常
|
*/
|
public static Map<String,Integer> listCellIndexForPO(File excelFile,String sheetName,Class<?> poClass) throws VciBaseException{
|
VciBaseUtil.alertNotNull(excelFile,"excel的文件",poClass,"poi导入导出对象所属的类");
|
if(!excelFile.exists()){
|
throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
|
}
|
if(StringUtils.isBlank(sheetName)){
|
sheetName = "Sheet1";
|
}
|
List<Field> fields = VciBaseUtil.getAllFieldForObj(poClass);
|
//列索引映射，key是属性的名称，value是列索引
|
Map<String,Integer> cellIndexMap = new HashMap<>();
|
if(!CollectionUtils.isEmpty(fields)){
|
Workbook workbook = null;
|
try {
|
workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
|
} catch (FileNotFoundException e) {
|
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 e) {
|
logger.error("关闭工作表出现错误",e);
|
}
|
}
|
}
|
return cellIndexMap;
|
}
|
|
/**
|
* 获取标题所在的行
|
* @param poClass poi导出导入对象
|
* @return 行索引
|
*/
|
public static int getRowIndexForTitle(Class<?> poClass){
|
if(poClass.isAnnotationPresent(ExcelTitle.class)){
|
ExcelTitle ec = poClass.getAnnotation(ExcelTitle.class);
|
if (ec == null) {
|
ec = poClass.getDeclaredAnnotation(ExcelTitle.class);
|
}
|
if(ec !=null){
|
return ec.rowIndexForTitle();
|
}
|
}
|
return 0;
|
}
|
|
/**
|
* 根据列序号获取列的名称
|
* @param colIndex 列序号
|
* @return 列的名称
|
*/
|
public static String getCellNameByIndex(int colIndex){
|
//A~Z
|
//AA~AZ
|
//BA~BZ
|
if(colIndex<26){
|
return String.valueOf((char)('A' + colIndex));
|
}else{
|
//
|
//大于等于26，取倍数和余数
|
int balance = colIndex%26;
|
int multiple = (colIndex-balance)/26;
|
String prefix = String.valueOf((char)('A' + (multiple-1)));
|
String suffix = String.valueOf((char)('A' + balance));
|
return prefix+suffix;
|
}
|
}
|
|
/**
|
* 创建工作簿，注意都是03版本的，为了兼容
|
*
|
* @return 工作簿对象
|
*/
|
public static Workbook createWorkbook(boolean is07) {
|
Workbook wb = null;
|
if(is07){
|
wb = new XSSFWorkbook();
|
}else{
|
wb = new HSSFWorkbook();
|
}
|
//导出都是些2003版本的，用于兼容
|
return wb;
|
}
|
|
|
/**
|
* 获取工作簿对象
|
*
|
* @param ins 文件输入流
|
* @return 工作簿，poi3.15以上版本不区分03还07
|
* @throws VciBaseException 如果不是excel文件，会直接报错
|
*/
|
public static Workbook getWorkbookByInputStream(InputStream ins) throws VciBaseException {
|
Workbook wb = null;
|
try {
|
wb = WorkbookFactory.create(ins);
|
} catch (IOException e) {
|
IOUtils.closeQuietly(ins);
|
throw new VciBaseException(WORKBOOK_INIT_FAIL, new String[]{LangBaseUtil.getErrorMsg(e)});
|
}
|
return wb;
|
}
|
|
/**
|
* 获取工作表，如果工作表名称不存在，则会创建一个
|
*
|
* @param wb 工作簿
|
* @param sheetName 工作表的名称
|
* @return 工作表对象
|
*/
|
private static Sheet getSheetByWorkbook(Workbook wb, String sheetName) {
|
if (StringUtils.isBlank(sheetName)) {
|
sheetName = "Sheet1";
|
}
|
Sheet sheet = wb.getSheet(sheetName);
|
if (sheet == null) {
|
sheet = wb.createSheet(sheetName);
|
}
|
return sheet;
|
}
|
|
/**
|
* 获取行的对象
|
*
|
* @param sheet 工作表对象
|
* @param rowIndex 行号，从0开始
|
* @return 行的对象，不存在会创建一行
|
*/
|
private static Row getRowBySheet(Sheet sheet, int rowIndex) {
|
Row row = sheet.getRow(rowIndex);
|
if (row == null) {
|
//说明这个时候还没有创建这行--即没有值
|
row = sheet.createRow(rowIndex);
|
}
|
return row;
|
}
|
|
/**
|
* 根据行获取单元格对象
|
*
|
* @param row 行对象
|
* @param cellIndex 列序号，从0开始
|
* @return 单元格的对象，不存在会创建
|
*/
|
private static Cell getCellByRow(Row row, int cellIndex) {
|
Cell cell = row.getCell(cellIndex);
|
if (cell == null) {
|
//说明还没有创建这个单元格
|
cell = row.createCell(cellIndex);
|
}
|
return cell;
|
}
|
|
/**
|
* 写入数据
|
*
|
* @param sheet 表格对象
|
* @param excelDataList 要写入的数据对象
|
*/
|
private static void writeDataToCell(Sheet sheet, List<WriteExcelData> excelDataList) 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);
|
}
|
copyStyle(sheet, cell, ed);
|
if(ed.isReadOnly() && ed.getObj() !=null && !(ed.getObj() instanceof RichTextString) && CollectionUtils.isEmpty(ed.getValidationDataList())){
|
//ed.setValidation(true);
|
List<String> list = new ArrayList<>();
|
list.add(ed.getObj().toString());
|
ed.setValidationDataList(list );
|
setValidation(sheet,ed);
|
}
|
centerCell(cell, ed);
|
Object value = ed.getObj();
|
if(StringUtils.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()));
|
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);
|
}
|
//设置自适应列宽
|
sheet.autoSizeColumn(ed.getCol());
|
if(ed.getWidth()!=null && ed.getWidth()>0){
|
sheet.setColumnWidth(cell.getColumnIndex(),(int)(ed.getWidth() + 0.72)*256);
|
}
|
});
|
mergedRegion(sheet, mergedDataList);
|
}
|
}
|
|
/**
|
* 为单元格设置公式
|
* @param cell 单元格的对象
|
* @param ed 数据对象
|
*/
|
private static void setFormula(Cell cell ,WriteExcelData ed){
|
if (ed.getObj() != null) {
|
cell.setCellFormula(ed.getObj().toString());
|
}
|
}
|
|
/**
|
* 设置名称管理器
|
* @param sheet 工作表
|
* @param ed 数据对象
|
*/
|
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));
|
}
|
}
|
|
/**
|
* 设置数据有效性
|
* @param sheet 工作表
|
* @param 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;
|
if (sheet instanceof XSSFSheet) {
|
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
|
XSSFDataValidationConstraint dvConstraint = null;
|
if(!CollectionUtils.isEmpty(ed.getValidationDataList())){
|
//说明是直接写内容的
|
dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(ed.getValidationDataList().toArray(new String[0]));
|
}else{
|
VciBaseUtil.alertNotNull(ed.getObj(),"写入有效性的时候，没有设置有效性的公式表达式");
|
dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(ed.getObj().toString());
|
}
|
validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
|
}else{
|
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet)sheet);
|
DataValidationConstraint dvConstraint = null;
|
if(!CollectionUtils.isEmpty(ed.getValidationDataList())){
|
//说明是直接写内容的
|
dvConstraint = dvHelper.createExplicitListConstraint(ed.getValidationDataList().toArray(new String[0]));
|
}else{
|
VciBaseUtil.alertNotNull(ed.getObj(),"写入有效性的时候，没有设置有效性的公式表达式");
|
dvConstraint = dvHelper.createFormulaListConstraint(ed.getObj().toString());
|
}
|
validation = (HSSFDataValidation)(new HSSFDataValidation(regions,dvConstraint));
|
}
|
if(validation instanceof XSSFDataValidation) {
|
validation.setSuppressDropDownArrow(true);
|
validation.setShowErrorBox(true);
|
}else {
|
validation.setSuppressDropDownArrow(false);
|
}
|
if(StringUtils.isNotBlank(ed.getValidationErrorMsg())){
|
validation.createErrorBox("error",ed.getValidationErrorMsg());
|
}
|
sheet.addValidationData(validation);
|
}
|
|
/**
|
* 获取范围的表达式
|
* @param excelData 数据对象
|
* @return 范围的表达式
|
*/
|
private static String getRange(WriteExcelData excelData){
|
//列是从A开始的
|
char start = (char)('A' + excelData.getCol());
|
int rowId = excelData.getRow() + 1;
|
int endRowId = excelData.getRowTo() +1;
|
if (excelData.getColTo() <= 25) {
|
char end = (char)('A' +excelData.getColTo());
|
return "$" + start + "$" + rowId + ":$" + end + "$" + endRowId;
|
} else {
|
char endPrefix = 'A';
|
char endSuffix = 'A';
|
if ((excelData.getColTo() - 25) / 26 == 0 || excelData.getColTo() == 51) {// 26-51之间，包括边界（仅两次字母表计算）
|
if ((excelData.getColTo() - 25) % 26 == 0) {// 边界值
|
endSuffix = (char)('A' + 25);
|
} else {
|
endSuffix = (char)('A' + (excelData.getColTo() - 25) % 26 - 1);
|
}
|
} else {// 51以上
|
if ((excelData.getColTo() - 25) % 26 == 0) {
|
endSuffix = (char)('A' + 25);
|
endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26 - 1);
|
} else {
|
endSuffix = (char)('A' + (excelData.getColTo() - 25) % 26 - 1);
|
endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26);
|
}
|
}
|
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + endRowId;
|
}
|
}
|
|
/**
|
* 往单元格里写入值
|
*
|
* @param cell 单元格对象
|
* @param value 要写入的值
|
*/
|
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((Integer) value);
|
} else if (value instanceof Long) {
|
cell.setCellValue((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());
|
}
|
}
|
}
|
|
/**
|
* 拷贝样式
|
*
|
* @param sheet 工作表
|
* @param cell 单元格
|
* @param excelData 要拷贝的信息
|
*/
|
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(StringUtils.isNotBlank(excelData.getDateFormat())){
|
HSSFDataFormat format= (HSSFDataFormat) sheet.getWorkbook().createDataFormat();
|
cell.getCellStyle().setDataFormat(format.getFormat(excelData.getDateFormat()));
|
}
|
}
|
}
|
}
|
|
/**
|
* 拷贝单元格的样式
|
* @param fromCell 来源端单元格
|
* @param toCell 目标端单元格
|
*/
|
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());
|
}
|
|
/**
|
* 居中数据
|
*
|
* @param cell 单元格
|
* @param excelData 要居中的内容
|
*/
|
private static void centerCell(Cell cell, WriteExcelData excelData) {
|
if (excelData.isCenter()) {
|
CellStyle cellStyle = cell.getCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
|
}
|
}
|
|
/**
|
* 合并单元格
|
*
|
* @param sheet 工作表
|
* @param mergedDataList 要合并的数据信息
|
*/
|
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());
|
}
|
List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
|
sheet.addMergedRegion(new CellRangeAddress(ed.getRow(), ed.getRowTo(), ed.getCol(), ed.getColTo()));
|
});
|
}
|
}
|
|
/**
|
* 从excel上读取数据
|
*
|
* @param file 文件
|
* @return 数据列表
|
* @throws VciBaseException 执行出错的时候会抛出异常
|
*/
|
public static List<SheetDataSet> readDataObjectFromExcel(File file) throws VciBaseException {
|
return readDataObjectFromExcel(file, SheetDataSet.class);
|
}
|
|
/**
|
* 从excel上读取数据
|
*
|
* @param file 文件
|
* @param doClass 数据对象的类型
|
* @param <T> 数据对象的类型
|
* @return 数据列表
|
* @throws VciBaseException 执行出错的时候会抛出异常
|
*/
|
public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass) throws VciBaseException {
|
return readDataObjectFromExcel(file, doClass, null);
|
}
|
|
/**
|
* 从excel上读取数据
|
*
|
* @param file 文件
|
* @param excelOption 读取的类型，包括数据的首行，
|
* @param doClass 数据对象的类
|
* @param <T> 数据对象的类型
|
* @return 数据列表
|
* @throws VciBaseException 执行出错的时候会抛出异常
|
*/
|
public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass, ReadExcelOption excelOption) throws VciBaseException {
|
return readDataObjectFromExcel(file, doClass,excelOption, null);
|
}
|
|
/**
|
* 从excel上读取数据
|
*
|
* @param file 文件
|
* @param excelOption 读取的类型，包括数据的首行，
|
* @param doClass 数据对象的类
|
* @param <T> 数据对象的类型
|
* @param processer 转换器
|
* @return 数据列表
|
* @throws VciBaseException 执行出错的时候会抛出异常
|
*/
|
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 e) {
|
String msg = READ_IMPORT_FILE_FAIL;
|
if (logger.isErrorEnabled()) {
|
logger.error(msg, e);
|
}
|
throw new VciBaseException(msg + LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
}
|
try {
|
return readDataObjectFromExcel(ins, doClass, excelOption,processer);
|
} catch (VciBaseException e) {
|
throw e;
|
} catch (Throwable e) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
|
} finally {
|
IOUtils.closeQuietly(ins);
|
}
|
}
|
|
/**
|
* 从excel上读取数据
|
*
|
* @param ins 文件流
|
* @param excelOption 读取的类型，包括数据的首行（默认1），工作表（默认0）和是否全部工作表（默认否）
|
* @param doClass 数据的对象
|
* @param <T> 数据对象所属的类型
|
* @param processer 转换器
|
* @return 数据列表
|
* @throws VciBaseException 执行出错的时候会抛出异常
|
*/
|
public static <T> List<T> readDataObjectFromExcel(InputStream ins, Class<T> doClass,ReadExcelOption excelOption, PoiImportProcesser<T> processer) throws VciBaseException {
|
if (ins == null) {
|
throw new VciBaseException(INPUT_STREAM_IS_NULL);
|
}
|
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 = Optional.of(excelColumnSet).get().values().stream().collect(Collectors.toMap(s -> s.value(), t -> t));
|
Map<String/**标题**/,Field> excelTitleFieldMap = new HashMap<>();
|
Map<String/**标题**/,ExcelColumnMap> extendAttrTitleMap = new HashMap<>();
|
final Field[] rowIndexFields = {null};
|
ReadExcelOption finalExcelOption = excelOption;
|
excelColumnSet.forEach(((field, excelColumn) -> {
|
excelTitleFieldMap.put(excelColumn.value(),field);
|
if (excelColumn.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(excelColumn.value())){
|
//说明是map类型,这种一般是扩展属性，将一堆属性转换为map里的值
|
List<ExcelColumnMap> excelColumnMaps = finalExcelOption.getExtendAttrMap().get(excelColumn.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);
|
String sheetName=sheet.getSheetName();
|
if(StringUtils.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<>();
|
if (excelOption.getFristRow() > 0) {
|
//等于0表示没有总标题
|
try {
|
Row titleRow = sheet.getRow(excelOption.getFristRow()-1);
|
int colsCount = titleRow.getLastCellNum();
|
List<String> titleList = new LinkedList<>();
|
for (int i = 0; i < colsCount; i++) {
|
String title = VciBaseUtil.getStringValueFromObject(getCellValue(titleRow.getCell(i),evaluator));
|
if (StringUtils.isNotBlank(title)) {
|
title= title.replace("*","").replace("☆","");
|
colsNameIndexMap.put(i, title);
|
titleList.add(title);
|
}
|
}
|
if (isDataSet) {
|
sheetDataSet.setColName(titleList);
|
}
|
} catch (Exception e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("读取excel文件中的标题信息出现了错误", e);
|
}
|
throw e;
|
}
|
}
|
|
//读取值
|
List<SheetRowData> rowDataList = new LinkedList<SheetRowData>();
|
if (sheet.getLastRowNum() > 0) {
|
for (int rowIndex = excelOption.getFristRow(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
|
Row rowDataSet = sheet.getRow(rowIndex);
|
if (rowDataSet == null) {
|
continue;
|
}
|
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 && StringUtils.isNotBlank(valueTemp.toString())) ){
|
notValue = true;
|
break;
|
}
|
}
|
if(!notValue){
|
//整行都是空，直接跳过
|
continue;
|
}
|
//实例化数据对象
|
SheetRowData rd = (isDataSet ? new SheetRowData() : null);
|
T obj = null;
|
if (!isDataSet) {
|
try {
|
obj = doClass.newInstance();
|
} catch (Throwable e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("实例化数据对象", e);
|
}
|
throw new VciBaseException("实例化数据对象," + LangBaseUtil.getErrorMsg(e));
|
}
|
}
|
|
//处理行号
|
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 < rowDataSet.getLastCellNum(); columnIndex++) {
|
Cell cell = rowDataSet.getCell(columnIndex);
|
Object value = getCellValue(cell, evaluator);
|
//说明是对象
|
String title = colsNameIndexMap.containsKey(columnIndex)?colsNameIndexMap.get(columnIndex):"";
|
//属性是否为map形式
|
boolean isMapField = false;
|
Field thisField = null;
|
if (StringUtils.isNotBlank(title) && excelTitleFieldMap.containsKey(title)) {
|
thisField = 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 (StringUtils.isNotBlank(title) && excelTitleMap.containsKey(title)) {
|
excelColumn = excelTitleMap.get(title);
|
}
|
ExcelColumnMap columnMap = null;
|
if(isMapField){
|
columnMap = extendAttrTitleMap.get(title);
|
}
|
boolean isNull = false;
|
if(value == null || (value instanceof String && StringUtils.isBlank(value.toString()))){
|
isNull = true;
|
}
|
if (!isNull) {
|
if (!isDataSet) {
|
if (thisField != null && (excelColumn != null || columnMap!=null)) {
|
if(!isMapField) {
|
if (value.getClass().equals(thisField.getType())) {
|
try {
|
thisField.set(obj, value);
|
} catch (IllegalAccessException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("读取excel的单元格的值后，为对象赋值出现了错误", e);
|
}
|
}
|
} else {
|
VciBaseUtil.setValueForField(thisField, obj, VciBaseUtil.getStringValueFromObject(value));
|
}
|
if (thisField.getType().equals(Date.class) && thisField.get(obj) == null) {
|
throw new VciBaseException(CELL_NOT_DATE_FORMATE, new String[]{title, (rowIndex + 1) + ""});
|
}
|
if (StringUtils.isNotBlank(excelColumn.regExg()) && !VciBaseUtil.getStringValueFromObject(value).matches(excelColumn.regExg())) {
|
throw new VciBaseException(CELL_VALUE_NOT_EQUAL_REG, new String[]{title, (rowIndex + 1) + "", excelColumn.regExgTitle()});
|
}
|
}else{
|
//说明是map
|
Object fieldValue = thisField.get(obj);
|
Map<String,String> data ;
|
if(fieldValue==null){
|
data = new HashMap<>();
|
}else{
|
data = (Map<String,String>)fieldValue;
|
}
|
data.put(columnMap.getColumnName(),value==null?"":value.toString());
|
try {
|
thisField.set(obj, data);
|
} catch (IllegalAccessException e) {
|
if (logger.isErrorEnabled()) {
|
logger.error("设置map类型的数据时候出现了错误", e);
|
}
|
}
|
}
|
}
|
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(CELL_VALUE_CAN_NOT_NULL, new String[]{title, (rowIndex + 1) + ""});
|
}
|
if(StringUtils.isNotBlank(excelColumn.regExg())){
|
throw new VciBaseException(CELL_VALUE_CAN_NOT_NULL_FOR_REG, new String[]{title, (rowIndex + 1) + ""});
|
}
|
}
|
}
|
}
|
if (isDataSet) {
|
rd.setData(rowDataMap);
|
rowDataList.add(rd);
|
} else {
|
dataSet.add(obj);
|
}
|
}
|
}
|
if(isDataSet){
|
sheetDataSet.setRowData(rowDataList);
|
}
|
}
|
}catch (Throwable e){
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e),new String[0],e);
|
}finally {
|
IOUtils.closeQuietly(ins);
|
try {
|
workbook.close();
|
} catch (IOException e) {
|
if(logger.isErrorEnabled()){
|
logger.error("关闭工作簿出现了错误",e);
|
}
|
}
|
}
|
|
return dataSet;
|
}
|
|
/**
|
* 从cell单元格上获取值
|
* @param cell 单元格对象
|
* @param evaluator 公式转换
|
* @return 单元格的值，没有转换为字符串
|
*/
|
private static Object getCellValue(Cell cell,FormulaEvaluator evaluator){
|
if(cell !=null ){
|
Object cellValue = null;
|
CellType cellTypeEnum = cell.getCellType();
|
switch (cellTypeEnum){
|
case STRING:
|
String thisCellValue = cell.getStringCellValue();
|
cellValue = StringUtils.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 = "";
|
break;
|
}
|
return cellValue;
|
}else{
|
return null;
|
}
|
}
|
|
/**
|
* 针对公式的计算值
|
* @param cell 公式转换后的单元格对象
|
* @return 公式的实际值
|
*/
|
private static Object getCellValue(CellValue cell) {
|
Object cellValue = null;
|
switch (cell.getCellTypeEnum()) {
|
case STRING:
|
String thisCellValue = cell.getStringValue();
|
cellValue = StringUtils.isEmpty(thisCellValue)?"":thisCellValue.trim();
|
break;
|
case NUMERIC:
|
//公式计算不会有日期型
|
cellValue=cell.getNumberValue();
|
break;
|
default:
|
break;
|
}
|
|
return cellValue;
|
}
|
|
|
/**
|
* 获取电子表格相关的注解
|
*
|
* @param doClass 返回的类
|
* @return 不是SheetDataSet的时候需要返回所有包含了excelcolumn注解的属性
|
*/
|
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 = allFields.stream().filter(field -> 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;
|
}
|
|
/**
|
* 获取属性上的excelColumn的注解
|
* @param field 属性对象
|
* @return excelColumn
|
*/
|
public static ExcelColumn getExcelColumnAnnotation(Field field){
|
ExcelColumn ec = field.getAnnotation(ExcelColumn.class);
|
if (ec == null) {
|
ec = field.getDeclaredAnnotation(ExcelColumn.class);
|
}
|
return ec;
|
}
|
|
/**
|
* 根据PO对象，导出模板
|
* @param doClass 导出的对象
|
* @param downloadTempOption 下载的选项
|
* @param processer 调用函数，获取枚举的值
|
* @param <T> 类型
|
* @return 文件的路径
|
*/
|
public static <T> String downloadImportTempByClass(Class<T> doClass,DownloadTempOption downloadTempOption,PoiTemplateProcesser<T> processer){
|
if(downloadTempOption == null){
|
downloadTempOption = new DownloadTempOption("");
|
}
|
//ok,写excel
|
String excelName = LocalFileUtil.getDefaultTempFolder() + File.separator + (StringUtils.isNotBlank(downloadTempOption.getExcelName())?downloadTempOption.getExcelName():"导入模板.xls");
|
try {
|
new File(excelName).createNewFile();
|
} catch (Throwable e) {
|
throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{excelName}, e);
|
}
|
List<WriteExcelData> excelDataList = new ArrayList<>();
|
//先处理标题
|
Map<Field, ExcelColumn> fieldExcelColumnMap = getExcelColumnAnnotations(doClass);
|
Map<String,ExcelColumn> fieldNameExcelColumnMap = new HashMap<>();
|
fieldExcelColumnMap.forEach((field,ec)->{
|
field.setAccessible(true);
|
fieldNameExcelColumnMap.put(field.getName(),ec);
|
});
|
//todo 后面处理标题的内容
|
//获取顺序，如果是继承上级的，就放后面随意处理
|
Field fields[] = doClass.getDeclaredFields();
|
Set<String> finedFields = new HashSet<>();
|
Workbook workbook = new HSSFWorkbook();
|
int index = 0;
|
for (int i = 0; i < fields.length; i++) {
|
//TODO, 继承的还没处理
|
Field field = fields[i];
|
field.setAccessible(true);
|
String fieldName = field.getName();
|
if(fieldNameExcelColumnMap.containsKey(fieldName)){
|
finedFields.add(fieldName);
|
ExcelColumn column = fieldNameExcelColumnMap.get(fieldName);
|
if(column.rowIndexColumn()){
|
continue;
|
}
|
WriteExcelData excelData = new WriteExcelData(0,index,StringUtils.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(StringUtils.isNotBlank(column.enumId()) && processer!=null){
|
Map<String, String> enumMap = processer.wrapperEnumMap(column.enumId(), doClass, field);
|
if(!CollectionUtils.isEmpty(enumMap)){
|
WriteExcelData thisRowNext = thisRowNextDatas.get(0);
|
thisRowNext.setValidation(true);
|
thisRowNext.setRowTo(defaultRowSize);
|
thisRowNext.setColTo(thisRowNext.getCol());
|
thisRowNext.setValidationDataList(enumMap.values().stream().collect(Collectors.toList()));
|
}
|
}
|
if(field.isAnnotationPresent(VciFieldType.class)){
|
VciFieldType fieldType = field.getDeclaredAnnotation(VciFieldType.class);
|
if(fieldType ==null){
|
fieldType = field.getAnnotation(VciFieldType.class);
|
}
|
if(fieldType!=null){
|
String dateFormat = "";
|
if(VciFieldTypeEnum.VTDateTime.equals(fieldType.value())){
|
dateFormat = VciDateUtil.DateTimeFormat;
|
}else if(VciFieldTypeEnum.VTDate.equals(fieldType.value())){
|
dateFormat = VciDateUtil.DateFormat;
|
}else if(VciFieldTypeEnum.VTTime.equals(fieldType.value())){
|
dateFormat = VciDateUtil.TimeFormat;
|
}
|
if(StringUtils.isNotBlank(dateFormat)){
|
String finalDateFormat = dateFormat;
|
thisRowNextDatas.stream().forEach(thisRowNext->{
|
thisRowNext.setDateFormat(finalDateFormat);
|
});
|
}
|
//boolean类型
|
if(VciFieldTypeEnum.VTBoolean.equals(fieldType.value())){
|
List<String> trueFlase = new ArrayList<>();
|
trueFlase.add("是");
|
trueFlase.add("否");
|
WriteExcelData thisRowNext = 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(StringUtils.isNotBlank(downloadTempOption.getSheetName())?downloadTempOption.getSheetName():"Sheet1",excelDataList);
|
writeDataToFile(excelName, excelOption);
|
return excelName;
|
}
|
|
/**
|
* * 合并多个ExcelSheet
|
* https://blog.csdn.net/lingerlan510/article/details/121943788
|
* @param files 文件字符串(file.toString)集合,按顺序进行合并，合并的Excel中Sheet名称不可重复
|
* @param excelName 合并后Excel名称(包含后缀和路径)
|
* @Date: 2020/9/18 15:31
|
*/
|
public static void mergeExcel(List<String> files, String excelName) {
|
Workbook newExcelCreat = excelName.toLowerCase(Locale.ROOT).endsWith(".xlsx")? new XSSFWorkbook():new HSSFWorkbook();
|
// 遍历每个源excel文件，TmpList为源文件的名称集合
|
for (int i = 0 ; i < files.size(); i ++) {
|
String fromExcelName = files.get(i);
|
try (InputStream in = new FileInputStream(fromExcelName)) {
|
Workbook fromExcel = null;
|
if(fromExcelName.toLowerCase(Locale.ROOT).endsWith(".xlsx")){
|
fromExcel = new XSSFWorkbook(in);
|
}else{
|
fromExcel = new HSSFWorkbook(in);
|
}
|
int length = fromExcel.getNumberOfSheets();
|
if (length <= 1) { //长度为1时
|
Sheet oldSheet = fromExcel.getSheetAt(0);
|
Sheet existSheet = newExcelCreat.getSheet(oldSheet.getSheetName());
|
String newSheetName = oldSheet.getSheetName();
|
if(existSheet!=null){
|
newSheetName += "_" +i;
|
}
|
Sheet newSheet = newExcelCreat.createSheet(newSheetName);
|
copySheet(newExcelCreat, oldSheet, newSheet);
|
} else {
|
for (int j = 0; j < length; j++) {// 遍历每个sheet
|
Sheet oldSheet = fromExcel.getSheetAt(j);
|
Sheet existSheet = newExcelCreat.getSheet(oldSheet.getSheetName());
|
String newSheetName = oldSheet.getSheetName();
|
if(existSheet!=null){
|
newSheetName += "_" +i + "_" + j;
|
}
|
Sheet newSheet = newExcelCreat.createSheet(newSheetName);
|
copySheet(newExcelCreat, oldSheet, newSheet);
|
}
|
}
|
|
//名称管理器
|
List<? extends Name> allNames = fromExcel.getAllNames();
|
if(!CollectionUtils.isEmpty(allNames)){
|
allNames.stream().forEach(name->{
|
Name name1 = newExcelCreat.createName();
|
name1.setNameName(name.getNameName());
|
name1.setRefersToFormula(name.getRefersToFormula());
|
});
|
}
|
} catch (IOException e) {
|
throw new VciBaseException("合并excel出错的了",new String[]{},e);
|
}
|
}
|
// 定义新生成的xlxs表格文件
|
String allFileName = excelName;
|
try (FileOutputStream fileOut = new FileOutputStream(allFileName)) {
|
newExcelCreat.write(fileOut);
|
fileOut.flush();
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
try {
|
newExcelCreat.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 合并单元格
|
*
|
* @param fromSheet 来源
|
* @param toSheet 目标
|
*/
|
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);
|
}
|
}
|
|
/**
|
* 复制单元格
|
*
|
* @param fromCell 来源
|
* @param toCell 目标
|
*/
|
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 && StringUtils.isNotBlank(value.toString())){
|
Font fontAt = fromCell.getSheet().getWorkbook().getFontAt(fromCell.getCellStyle().getFontIndexAsInt());
|
value.applyFont(fontAt);
|
}
|
toCell.setCellValue(value);
|
} else if (fromCellType == CellType.BLANK) {
|
// nothing21
|
} else 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());
|
} else {
|
// nothing29
|
}
|
}
|
|
/**
|
* 行复制功能
|
*
|
* @param wb 工作表
|
* @param oldRow 来源
|
* @param toRow 目标
|
*/
|
private static void copyRow(Workbook wb, Row oldRow, Row toRow) {
|
toRow.setHeight(oldRow.getHeight());
|
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
|
Cell tmpCell = (Cell)cellIt.next();
|
Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
|
copyCell(tmpCell, newCell);
|
}
|
}
|
|
/**
|
* Sheet复制
|
*
|
* @param wb 工作表
|
* @param fromSheet 来源
|
* @param toSheet 目标
|
*/
|
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));
|
}
|
for (Iterator rowIt = fromSheet.rowIterator(); 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 final String KEY_ATTR_CHAR = "☆";
|
|
/**
|
* 必输
|
*/
|
public static final String REQUIRED_CHAR = "*";
|
|
/**
|
* 追加数据到excel中，
|
* @param data 数据，必须是po对象
|
* @param excelName 目标的文件名称
|
* @param excelOption 写入的相关配置
|
* @param <T> 对象类型
|
*/
|
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 = ExcelUtil.readDataObjectFromExcel(new File(excelName),SheetDataSet.class,readExcelOption);
|
if(!CollectionUtils.isEmpty(sheetDataSets)) {
|
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= colName.get(i).replace(REQUIRED_CHAR,"").replace(KEY_ATTR_CHAR,"");
|
if(excelTitleFieldNameMap.containsKey(col)){
|
fieldColumnMap.put(excelTitleFieldNameMap.get(col),i);
|
}
|
}
|
//我们封装数据
|
List<WriteExcelData> writeExcelDataList = new ArrayList<>();
|
final Integer[] index = {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);
|
}
|
});
|
index[0]++;
|
});
|
WriteExcelOption writeExcelOption = new WriteExcelOption();
|
writeExcelOption.addSheetDataList(excelOption.getSheetName(),writeExcelDataList);
|
writeExcelOption.setAppend(true);
|
writeDataToFile(excelName,writeExcelOption);
|
}
|
}
|
}
|
}
|
|
|
}
|
