| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057 |
- package com.goafanti.common.utils.excel;
- import java.io.ByteArrayInputStream;
- import java.io.ByteArrayOutputStream;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.lang.reflect.Field;
- import java.math.BigDecimal;
- import java.net.URL;
- import java.net.URLConnection;
- 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.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.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.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.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import com.goafanti.common.bo.Result;
- import com.goafanti.common.constant.AFTConstants;
- import com.goafanti.common.error.BusinessException;
- import com.goafanti.common.utils.DateUtils;
- import com.goafanti.common.utils.StringUtils;
- import com.goafanti.common.utils.excel.Excel.ColumnType;
- /**
- * Excel相关处理
- *
- *
- */
- public class NewExcelUtil<T> {
- private static final Logger log = LoggerFactory.getLogger(NewExcelUtil.class);
- /**
- * Excel sheet最大行数,默认65536
- */
- public static final int sheetSize = 65536;
-
- public String downloadPath = "";
-
-
- /**
- * 工作表名称
- */
- private String sheetName;
- /**
- * 工作薄对象
- */
- private Workbook wb;
- /**
- * 工作表对象
- */
- private Sheet sheet;
- /**
- * 样式列表
- */
- private Map<String, CellStyle> styles;
- /**
- * 导入导出数据列表
- */
- private List<T> list;
- /**
- * 注解列表
- */
- private List<Object[]> fields;
- /**
- * 最大高度
- */
- 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 NewExcelUtil(Class<T> clazz) {
- this.clazz = clazz;
- }
- public void init(List<T> list, String sheetName) {
- if (list == null) {
- list = new ArrayList<T>();
- }
- this.list = list;
- this.sheetName = sheetName;
- createExcelField();
- createWorkbook();
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @return 结果
- */
- public Result exportExcel(List<T> list, String sheetName) {
- this.init(list, sheetName);
- return exportExcel();
- }
-
- /**
- * 直接导出CXCEL表
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param response
- */
- public Result exportExcel(List<T> list, String sheetName, HttpServletResponse response) {
- this.init(list, sheetName);
- exportExcel(response);
- return new Result().data(1);
- }
-
-
- /**
- * 导出CXCEL表到指定目录
- * @param list 导出数据集合
- * @param sheetName 工作表的名称
- * @param response
- */
- public Result exportExcel(List<T> list, String sheetName, String uploadPath) {
- this.init(list, sheetName);
- this.downloadPath=uploadPath;
- return exportExcel();
- }
- private void exportExcel(HttpServletResponse response) {
- try {
- OutputStream out = response.getOutputStream();
- // 取出一共有多少个sheet.
- double sheetNo = Math.ceil(list.size() / sheetSize);
- String dateFilename =DateFilename(sheetName);
- for (int index = 0; index <= sheetNo; index++) {
- createSheet(sheetNo, index);
- // 产生一行
- Row title = sheet.createRow(0);
- Cell cell = title.createCell(0);
- cell.setCellValue(sheetName);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
- sheet.addMergedRegion(region);
- cell.setCellStyle(styles.get("total"));
- Row row = sheet.createRow(1);
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields) {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- fillExcelData(index, row);
- addStatisticsRow();
- }
- response.addHeader("Content-Disposition", "attachment;filename=" + new String(dateFilename.getBytes(),"iso-8859-1"));
- response.setContentType("application/octet-stream;charset=utf-8");
- wb.write(out);
- out.flush();
- out.close();
- } catch (Exception e) {
- log.error("导出Excel异常{}", e.getMessage());
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
-
-
- private String DateFilename(String sheetName) {
- sheetName = sheetName + "_" + DateUtils.formatDate(new Date(), AFTConstants.YYYYMMDDHHMMSS)+".xlsx";
- return sheetName;
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param sheetName 工作表的名称
- * @return 结果
- */
- public Result importTemplateExcel(String sheetName) {
- this.init(null, sheetName);
- return exportExcel();
- }
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @return 结果
- */
- public Result exportExcel() {
- OutputStream out = null;
- try {
- // 取出一共有多少个sheet.
- double sheetNo = Math.ceil(list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++) {
- createSheet(sheetNo, index);
- // 产生一行
- Row title = sheet.createRow(0);
- Cell cell = title.createCell(0);
- cell.setCellValue(sheetName);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
- sheet.addMergedRegion(region);
- cell.setCellStyle(styles.get("total"));
- Row row = sheet.createRow(1);
- // 产生一行
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields) {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- fillExcelData(index, row);
- addStatisticsRow();
- }
- String filename = encodingFilename(sheetName);
- out = new FileOutputStream(getAbsoluteFile(filename));
- wb.write(out);
- return new Result().data(filename);
- } catch (Exception e) {
- log.error("导出Excel异常{}", e.getMessage());
- throw new BusinessException("导出Excel失败,请联系网站管理员!");
- } finally {
- if (wb != null) {
- try {
- wb.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- if (out != null) {
- try {
- out.close();
- } catch (IOException e1) {
- e1.printStackTrace();
- }
- }
- }
- }
- /**
- * 填充excel数据
- *
- * @param index 序号
- * @param row 单元格行
- */
- public void fillExcelData(int index, Row row) {
- int startNo = index * sheetSize;
- int endNo = Math.min(startNo + sheetSize, list.size());
- for (int i = startNo; i < endNo; i++) {
- row = sheet.createRow(i + 2 - startNo);
- // 得到导出对象.
- T vo = (T) list.get(i);
- int column = 0;
- for (Object[] os : fields) {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 设置实体类私有属性可访问
- field.setAccessible(true);
- this.addCell(excel, row, vo, field, column++);
- }
- }
- }
- /**
- * 创建表格样式
- *
- * @param wb 工作薄对象
- * @return 样式列表
- */
- private Map<String, CellStyle> createStyles(Workbook wb) {
- // 写入各条记录,每条记录对应excel表中的一行
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
- //
- CellStyle style = wb.createCellStyle();
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setBorderRight(BorderStyle.THIN);
- style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderLeft(BorderStyle.THIN);
- style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderTop(BorderStyle.THIN);
- style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderBottom(BorderStyle.THIN);
- style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- Font dataFont = wb.createFont();
- dataFont.setFontName("Arial");
- dataFont.setFontHeightInPoints((short) 10);
- style.setFont(dataFont);
- styles.put("data", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font headerFont = wb.createFont();
- headerFont.setFontName("Arial");
- headerFont.setFontHeightInPoints((short) 10);
- headerFont.setBold(true);
- headerFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(headerFont);
- styles.put("header", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font totalFont = wb.createFont();
- totalFont.setFontName("Arial");
- totalFont.setFontHeightInPoints((short) 10);
- totalFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(totalFont);
- styles.put("total", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.LEFT);
- styles.put("data1", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- styles.put("data2", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.RIGHT);
- styles.put("data3", style);
- return styles;
- }
- /**
- * 创建单元格
- */
- public Cell createCell(Excel attr, Row row, int column) {
- // 创建列
- Cell cell = row.createCell(column);
- // 写入列信息
- cell.setCellValue(attr.name());
- setDataValidation(attr, row, column);
- cell.setCellStyle(styles.get("header"));
- return cell;
- }
-
- /**
- * 创建单元格
- */
- public Cell createTotal(String str, Row row, int column) {
- // 创建列
- Cell cell = row.createCell(column);
- // 写入列信息
- cell.setCellValue(str);
- sheet.setColumnWidth(column, (int) ((14 + 0.72) * 256));
- cell.setCellStyle(styles.get("header"));
- return cell;
- }
- /**
- * 设置单元格信息
- *
- * @param value 单元格值
- * @param attr 注解相关
- * @param cell 单元格信息
- */
- public void setCellVo(Object value, Excel attr, Cell cell) {
- if (ColumnType.STRING == attr.cellType()) {
- cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
- } else if (ColumnType.NUMERIC == attr.cellType()) {
- cell.setCellValue(StringUtils.contains(toStr(value), ".") ? toDouble(value) : toInt(value));
- } else if (ColumnType.IMAGE == attr.cellType()) {
- ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(),
- cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
- String imagePath = toStr(value);
- if (StringUtils.isNotEmpty(imagePath)) {
- byte[] data = getImage(imagePath);
- getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
- cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
- }
- }
- }
- /**
- * 获取画布
- */
- public static Drawing<?> getDrawingPatriarch(Sheet sheet) {
- if (sheet.getDrawingPatriarch() == null) {
- sheet.createDrawingPatriarch();
- }
- return sheet.getDrawingPatriarch();
- }
- /**
- * 获取图片类型,设置图片插入类型
- */
- public int getImageType(byte[] value) {
- String type = getFileExtendName(value);
- if ("JPG".equalsIgnoreCase(type)) {
- return Workbook.PICTURE_TYPE_JPEG;
- } else if ("PNG".equalsIgnoreCase(type)) {
- return Workbook.PICTURE_TYPE_PNG;
- }
- return Workbook.PICTURE_TYPE_JPEG;
- }
- /**
- * 创建表格样式
- */
- public void setDataValidation(Excel attr, Row row, int column) {
- if (attr.name().indexOf("注:") >= 0) {
- sheet.setColumnWidth(column, 6000);
- } else {
- // 设置列宽
- sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
- }
- // 如果设置了提示信息则鼠标放上去提示.
- if (StringUtils.isNotEmpty(attr.prompt())) {
- // 这里默认设了2-101列提示.
- setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
- }
- // 如果设置了combo属性则本列只能选择不能输入
- if (attr.combo().length > 0) {
- // 这里默认设了2-101列只能选择不能输入.
- setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
- }
- }
- /**
- * 添加单元格
- */
- @SuppressWarnings("deprecation")
- public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
- Cell cell = null;
- try {
- // 设置行高
- row.setHeight(maxHeight);
- // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
- if (attr.isExport()) {
- // 创建cell
- cell = row.createCell(column);
- int align = attr.align().value();
- cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
- // 用于读取对象中的属性
- Object value = getTargetValue(vo, field, attr);
- String dateFormat = attr.dateFormat();
- String readConverterExp = attr.readConverterExp();
- String separator = attr.separator();
- if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
- cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
- } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
- cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
- } else if (value instanceof BigDecimal && -1 != attr.scale()) {
- cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
- } else{
- // 设置列类型
- setCellVo(value, attr, cell);
- }
- addStatisticsData(column, toStr(value), attr);
- }
- } catch (Exception e) {
- log.error("导出Excel失败{}", e);
- }
- return cell;
- }
-
- /**
- * 设置 POI XSSFSheet 单元格提示
- *
- * @param sheet 表单
- * @param promptTitle 提示标题
- * @param promptContent 提示内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- */
- public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
- int firstCol, int endCol) {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- dataValidation.createPromptBox(promptTitle, promptContent);
- dataValidation.setShowPromptBox(true);
- sheet.addValidationData(dataValidation);
- }
- /**
- * 设置某些列的值只能输入预制的数据,显示下拉框.
- *
- * @param sheet 要设置的sheet.
- * @param textlist 下拉框显示的内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- * @return 设置好的sheet.
- */
- public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- // 加载下拉列表内容
- DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
- // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- // 数据有效性对象
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- // 处理Excel兼容性问题
- if (dataValidation instanceof XSSFDataValidation) {
- dataValidation.setSuppressDropDownArrow(true);
- dataValidation.setShowErrorBox(true);
- } else {
- dataValidation.setSuppressDropDownArrow(false);
- }
- sheet.addValidationData(dataValidation);
- }
- /**
- * 解析导出值 0=男,1=女,2=未知
- *
- * @param propertyValue 参数值
- * @param converterExp 翻译注解
- * @param separator 分隔符
- * @return 解析后值
- */
- public static String convertByExp(String propertyValue, String converterExp, String separator) {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource) {
- String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue)) {
- for (String value : propertyValue.split(separator)) {
- if (itemArray[0].equals(value)) {
- propertyString.append(itemArray[1] + separator);
- break;
- }
- }
- } else {
- if (itemArray[0].equals(propertyValue)) {
- return itemArray[1];
- }
- }
- }
- return StringUtils.stripEnd(propertyString.toString(), separator);
- }
- /**
- * 反向解析值 男=0,女=1,未知=2
- *
- * @param propertyValue 参数值
- * @param converterExp 翻译注解
- * @param separator 分隔符
- * @return 解析后值
- */
- public static String reverseByExp(String propertyValue, String converterExp, String separator) {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource) {
- String[] itemArray = item.split("=");
- if (StringUtils.containsAny(separator, propertyValue)) {
- for (String value : propertyValue.split(separator)) {
- if (itemArray[1].equals(value)) {
- propertyString.append(itemArray[0] + separator);
- break;
- }
- }
- } else {
- if (itemArray[1].equals(propertyValue)) {
- return itemArray[0];
- }
- }
- }
- return StringUtils.stripEnd(propertyString.toString(), separator);
- }
- /**
- * 合计统计信息
- */
- private void addStatisticsData(Integer index, String text, Excel entity) {
- if (entity != null && entity.isStatistics()) {
- Double temp = 0D;
- if (!statistics.containsKey(index)) {
- statistics.put(index, temp);
- }
- try {
- temp = Double.valueOf(text);
- } catch (NumberFormatException e) {
- }
- statistics.put(index, statistics.get(index) + temp);
- }
- }
- /**
- * 创建统计行
- */
- public void addStatisticsRow() {
- if (statistics.size() > 0) {
- Cell cell = null;
- Row row = sheet.createRow(sheet.getLastRowNum() + 1);
- Set<Integer> keys = statistics.keySet();
- cell = row.createCell(0);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue("合计");
- for (Integer key : keys) {
- cell = row.createCell(key);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
- }
- statistics.clear();
- }
- }
- /**
- * 编码文件名
- */
- public String encodingFilename(String filename) {
- filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
- return filename;
- }
- /**
- * 获取下载路径
- *
- * @param filename 文件名称
- */
- public String getAbsoluteFile(String filename) {
- String downloadPath = this.downloadPath+"/" + filename;
- File desc = new File(downloadPath);
- if (!desc.getParentFile().exists()) {
- desc.getParentFile().mkdirs();
- }
- log.debug(downloadPath);
- return downloadPath;
- }
- /**
- * 获取bean中的属性值
- *
- * @param vo 实体对象
- * @param field 字段
- * @param excel 注解
- * @return 最终的属性值
- * @throws Exception
- */
- private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
- Object o = field.get(vo);
- if (StringUtils.isNotEmpty(excel.targetAttr())) {
- String target = excel.targetAttr();
- if (target.indexOf(".") > -1) {
- String[] targets = target.split("[.]");
- for (String name : targets) {
- o = getValue(o, name);
- }
- } else {
- o = getValue(o, target);
- }
- }
- return o;
- }
- /**
- * 以类的属性的get方法方法形式获取值
- *
- * @param o
- * @param name
- * @return value
- * @throws Exception
- */
- private Object getValue(Object o, String name) throws Exception {
- if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
- Class<?> clazz = o.getClass();
- Field field = clazz.getDeclaredField(name);
- field.setAccessible(true);
- o = field.get(o);
- }
- return o;
- }
- /**
- * 得到所有定义字段
- */
- private void createExcelField() {
- this.fields = new ArrayList<Object[]>();
- List<Field> tempFields = new ArrayList<>();
- tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
- tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
- for (Field field : tempFields) {
- // 单注解
- if (field.isAnnotationPresent(Excel.class)) {
- putToField(field, field.getAnnotation(Excel.class));
- }
- // 多注解
- if (field.isAnnotationPresent(Excels.class)) {
- Excels attrs = field.getAnnotation(Excels.class);
- Excel[] excels = attrs.value();
- for (Excel excel : excels) {
- putToField(field, excel);
- }
- }
- }
- this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort()))
- .collect(Collectors.toList());
- this.maxHeight = getRowHeight();
- }
- /**
- * 根据注解获取最大行高
- */
- public short getRowHeight() {
- double maxHeight = 0;
- for (Object[] os : this.fields) {
- Excel excel = (Excel) os[1];
- maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
- }
- return (short) (maxHeight * 20);
- }
- /**
- * 放到字段集合中
- */
- private void putToField(Field field, Excel attr) {
- if (attr != null) {
- this.fields.add(new Object[] { field, attr });
- }
- }
- /**
- * 创建一个工作簿
- */
- public void createWorkbook() {
- this.wb = new SXSSFWorkbook(500);
- }
- /**
- * 创建工作表
- *
- * @param sheetNo sheet数量
- * @param index 序号
- */
- public void createSheet(double sheetNo, int index) {
- this.sheet = wb.createSheet();
- this.styles = createStyles(wb);
- // 设置工作表的名称.
- if (sheetNo == 0) {
- wb.setSheetName(index, sheetName);
- } else {
- wb.setSheetName(index, sheetName + index);
- }
- }
- /**
- * 获取单元格值
- *
- * @param row 获取的行
- * @param column 获取单元格列号
- * @return 单元格值
- */
- public Object getCellValue(Row row, int column) {
- if (row == null) {
- return row;
- }
- Object val = "";
- try {
- Cell cell = row.getCell(column);
- if (StringUtils.isNotNull(cell)) {
- if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
- val = cell.getNumericCellValue();
- if (DateUtil.isCellDateFormatted(cell)) {
- val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
- } else {
- if ((Double) val % 1 != 0) {
- val = new BigDecimal(val.toString());
- } else {
- val = new DecimalFormat("0").format(val);
- }
- }
- } else if (cell.getCellTypeEnum() == CellType.STRING) {
- val = cell.getStringCellValue();
- } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
- val = cell.getBooleanCellValue();
- } else if (cell.getCellTypeEnum() == CellType.ERROR) {
- val = cell.getErrorCellValue();
- }
- }
- } catch (Exception e) {
- return val;
- }
- return val;
- }
- /**
- * 转换为字符串<br>
- * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static String toStr(Object value) {
- return toStr(value, null);
- }
- /**
- * 转换为字符串<br>
- * 如果给定的值为null,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static String toStr(Object value, String defaultValue) {
- if (null == value) {
- return defaultValue;
- }
- if (value instanceof String) {
- return (String) value;
- }
- return value.toString();
- }
- /**
- * 转换为double<br>
- * 如果给定的值为空,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static Double toDouble(Object value) {
- return toDouble(value, null);
- }
- /**
- * 转换为double<br>
- * 如果给定的值为空,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static Double toDouble(Object value, Double defaultValue) {
- if (value == null) {
- return defaultValue;
- }
- if (value instanceof Double) {
- return (Double) value;
- }
- if (value instanceof Number) {
- return ((Number) value).doubleValue();
- }
- final String valueStr = toStr(value, null);
- if (StringUtils.isEmpty(valueStr)) {
- return defaultValue;
- }
- try {
- // 支持科学计数法
- return new BigDecimal(valueStr.trim()).doubleValue();
- } catch (Exception e) {
- return defaultValue;
- }
- }
- /**
- * 转换为int<br>
- * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @return 结果
- */
- public static Integer toInt(Object value) {
- return toInt(value, null);
- }
- /**
- * 转换为int<br>
- * 如果给定的值为空,或者转换失败,返回默认值<br>
- * 转换失败不会报错
- *
- * @param value 被转换的值
- * @param defaultValue 转换错误时的默认值
- * @return 结果
- */
- public static Integer toInt(Object value, Integer defaultValue) {
- if (value == null) {
- return defaultValue;
- }
- if (value instanceof Integer) {
- return (Integer) value;
- }
- if (value instanceof Number) {
- return ((Number) value).intValue();
- }
- final String valueStr = toStr(value, null);
- if (StringUtils.isEmpty(valueStr)) {
- return defaultValue;
- }
- try {
- return Integer.parseInt(valueStr.trim());
- } catch (Exception e) {
- return defaultValue;
- }
- }
- /**
- * 获取文件类型
- *
- * @param photoByte 文件字节码
- * @return 后缀(不含".")
- */
- public static String getFileExtendName(byte[] photoByte) {
- String strFileExtendName = "JPG";
- if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
- && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97)) {
- strFileExtendName = "GIF";
- } else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70)) {
- strFileExtendName = "JPG";
- } else if ((photoByte[0] == 66) && (photoByte[1] == 77)) {
- strFileExtendName = "BMP";
- } else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71)) {
- strFileExtendName = "PNG";
- }
- return strFileExtendName;
- }
- public static byte[] getImage(String imagePath) {
- InputStream is = getFile(imagePath);
- try {
- return IOUtils.toByteArray(is);
- } catch (Exception e) {
- log.error("图片加载异常 {}", e);
- return null;
- } finally {
- IOUtils.closeQuietly(is);
- }
- }
- public static InputStream getFile(String imagePath) {
- try {
- byte[] result = readFile(imagePath);
- result = Arrays.copyOf(result, result.length);
- return new ByteArrayInputStream(result);
- } catch (Exception e) {
- log.error("获取图片异常 {}", e);
- }
- return null;
- }
- /**
- * 读取文件为字节数据
- *
- * @param key 地址
- * @return 字节数据
- */
- public static byte[] readFile(String url) {
- InputStream in = null;
- ByteArrayOutputStream baos = null;
- try {
- if (url.startsWith("http")) {
- // 网络地址
- URL urlObj = new URL(url);
- URLConnection urlConnection = urlObj.openConnection();
- urlConnection.setConnectTimeout(30 * 1000);
- urlConnection.setReadTimeout(60 * 1000);
- urlConnection.setDoInput(true);
- in = urlConnection.getInputStream();
- } else {
- // 本机地址(需修改)
- String localPath = "127.0.0.1";
- String downloadPath = localPath + StringUtils.substringAfter(url, "/profile");
- in = new FileInputStream(downloadPath);
- }
- return IOUtils.toByteArray(in);
- } catch (Exception e) {
- log.error("获取文件路径异常 {}", e);
- return null;
- } finally {
- IOUtils.closeQuietly(baos);
- }
- }
-
-
- }
|