package com.nhXJH.common.utils.poi;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.nhXJH.common.annotation.Excel;
import com.nhXJH.common.annotation.Excel.ColumnType;
import com.nhXJH.common.annotation.Excel.Type;
import com.nhXJH.common.annotation.Excels;
import com.nhXJH.common.config.LibraryMSConfig;
import com.nhXJH.common.core.domain.AjaxResult;
import com.nhXJH.common.core.text.Convert;
import com.nhXJH.common.exception.UtilException;
import com.nhXJH.common.utils.DateUtils;
import com.nhXJH.common.utils.DictUtils;
import com.nhXJH.common.utils.StringUtils;
import com.nhXJH.common.utils.file.FileTypeUtils;
import com.nhXJH.common.utils.file.FileUtils;
import com.nhXJH.common.utils.file.ImageUtils;
import com.nhXJH.common.utils.reflect.ReflectUtils;
/**
* Excel相关处理
*
* @author nhXJH
*/
public class ExcelUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
/**
* Excel sheet最大行数,默认65536
*/
public static final int sheetSize = 65536;
/**
* 工作表名称
*/
private String sheetName;
/**
* 导出类型(EXPORT:导出数据;IMPORT:导入模板)
*/
private Type type;
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 导入导出数据列表
*/
private List<T> list;
/**
* 注解列表
*/
private List<Object[]> fields;
/**
* 当前行号
*/
private int rownum;
/**
* 标题
*/
private String title;
/**
* 最大高度
*/
private short maxHeight;
/**
* 统计列表
*/
private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
/**
* 数字格式
*/
private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
/**
* 实体对象
*/
public Class<T> clazz;
public ExcelUtil(Class<T> clazz) {
this.clazz = clazz;
}
public void init(List<T> list, String sheetName, String title, Type type) {
if (list == null) {
list = new ArrayList<T>();
}
this.list = list;
this.sheetName = sheetName;
this.type = type;
this.title = title;
createExcelField();
createWorkbook();
createTitle();
}
/**
* 创建excel第一行标题
*/
public void createTitle() {
if (StringUtils.isNotEmpty(title)) {
Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
this.fields.size() - 1));
}
}
/**
* 对excel表单默认第一个索引名转换成list
*
* @param is 输入流
* @return 转换后集合
*/
public List<T> importExcel(InputStream is) throws Exception {
return importExcel(is, 0);
}
/**
* 对excel表单默认第一个索引名转换成list
*
* @param is 输入流
* @param titleNum 标题占用行数
* @return 转换后集合
*/
public List<T> importExcel(InputStream is, int titleNum) throws Exception {
return importExcel(StringUtils.EMPTY, is, titleNum);
}
/**
* 对excel表单指定表格索引名转换成list
*
* @param sheetName 表格索引名
* @param titleNum 标题占用行数
* @param is 输入流
* @return 转换后集合
*/
public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception {
this.type = Type.IMPORT;
this.wb = WorkbookFactory.create(is);
List<T> list = new ArrayList<T>();
// 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
if (sheet == null) {
throw new IOException("文件sheet不存在");
}
boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
Map<String, PictureData> pictures;
if (isXSSFWorkbook) {
pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
}
else {
pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
}
// 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
int rows = sheet.getLastRowNum();
if (rows > 0) {
// 定义一个map用于存放excel列的序号和field.
Map<String, Integer> cellMap = new HashMap<String, Integer>();
// 获取表头
Row heard = sheet.getRow(titleNum);
for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
Cell cell = heard.getCell(i);
if (StringUtils.isNotNull(cell)) {
String value = this.getCellValue(heard, i).toString();
cellMap.put(value, i);
}
else {
cellMap.put(nu
没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
收起资源包目录
基于Springboot+vue的图书管理系统(源代码+数据库) - 图书管理模块- 仓储管理模块- 用户管理模块.zip (748个子文件)
index.css 499KB
iconfont.css 25KB
demo.css 8KB
index.css 4KB
.env.development 215B
.editorconfig 514B
.eslintignore 298B
401.gif 160KB
.gitignore 256B
demo_index.html 424KB
ie.html 23KB
index.html 5KB
favicon.ico 6KB
ExcelUtil.java 43KB
Convert.java 24KB
HTMLFilter.java 19KB
SysUserServiceImpl.java 15KB
GenTableServiceImpl.java 15KB
UUID.java 15KB
SysMenuServiceImpl.java 14KB
ReflectUtils.java 14KB
StringUtils.java 14KB
VelocityUtils.java 13KB
BaseBookClassCnServiceImpl.java 12KB
SysUserController.java 11KB
SysRoleServiceImpl.java 11KB
BasePurchaseRequisitionController.java 10KB
LibraryMechanismController.java 10KB
SysDeptServiceImpl.java 9KB
HttpUtils.java 9KB
BaseFileController.java 9KB
Base64.java 9KB
BookStockController.java 8KB
LibraryAuthController.java 8KB
SysRoleController.java 8KB
BaseBookClassCnController.java 8KB
GenUtils.java 8KB
BookStockBrowHistoryController.java 8KB
BaseDeptAuthorityController.java 8KB
GenTableColumn.java 7KB
GenTable.java 7KB
FileUtils.java 7KB
LogAspect.java 7KB
GenController.java 7KB
SysUser.java 7KB
FileUploadUtils.java 7KB
SysJobController.java 7KB
BasePurchaseRequisitionSubController.java 7KB
SysJobServiceImpl.java 7KB
TestController.java 7KB
VerifyCodeUtils.java 7KB
LibraryBookController.java 6KB
SysDeptController.java 6KB
LibraryPressController.java 6KB
StockBookshelfController.java 6KB
Server.java 6KB
ApprovalQueueController.java 6KB
RedisCache.java 6KB
TokenService.java 6KB
SysConfigServiceImpl.java 6KB
PurchaseTemplateController.java 6KB
SecurityConfig.java 6KB
SysDictTypeServiceImpl.java 6KB
PurchaseLinkController.java 6KB
IpUtils.java 5KB
JobInvokeUtil.java 5KB
SysRole.java 5KB
SysProfileController.java 5KB
BookStockLogController.java 5KB
PurchaseRequisitionController.java 5KB
SysMenu.java 5KB
LibraryStockController.java 5KB
UnitConversionController.java 5KB
MeasurementUtilController.java 5KB
SysMenuController.java 5KB
SysOperLog.java 5KB
BasePurchaseNodeController.java 5KB
FileTokenController.java 5KB
StockStoreyController.java 5KB
LibraryBook.java 5KB
DictUtils.java 5KB
SysFileController.java 5KB
DruidConfig.java 5KB
SysConfigController.java 5KB
LibraryBookDto.java 5KB
SysDictTypeController.java 5KB
DataScopeAspect.java 5KB
SysPostController.java 5KB
SysLoginService.java 5KB
PermissionService.java 4KB
ISysUserService.java 4KB
SysJob.java 4KB
ScheduleUtils.java 4KB
Anlianys.java 4KB
LoginUser.java 4KB
SysDictData.java 4KB
SysDept.java 4KB
DbMapController.java 4KB
CaptchaConfig.java 4KB
SysDictDataController.java 4KB
共 748 条
- 1
- 2
- 3
- 4
- 5
- 6
- 8
资源评论
博士僧小星
- 粉丝: 1945
- 资源: 5903
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功