package com.ruoyi.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.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.UUID;

import org.apache.poi.hssf.usermodel.DVConstraint;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataValidation;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

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.CellRangeAddressList;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.ruoyi.common.utils.DateUtils;

import com.ruoyi.common.utils.StringUtils;

import com.ruoyi.framework.aspectj.lang.annotation.Excel;

import com.ruoyi.framework.config.RuoYiConfig;

import com.ruoyi.framework.web.domain.AjaxResult;/**

* Excel相关处理

*

* @author ruoyi*/

public class ExcelUtil{private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);public Classclazz;public ExcelUtil(Classclazz)

{this.clazz =clazz;

}/**

* 对excel表单默认第一个索引名转换成list

*

* @param input 输入流

* @return 转换后集合*/

public ListimportExcel(InputStream input) throws Exception

{returnimportExcel(StringUtils.EMPTY, input);

}/**

* 对excel表单指定表格索引名转换成list

*

* @param sheetName 表格索引名

* @param input 输入流

* @return 转换后集合*/

public ListimportExcel(String sheetName, InputStream input) throws Exception

{

List list = new ArrayList();

Workbook workbook=WorkbookFactory.create(input);

Sheet sheet= null;if(StringUtils.isNotEmpty(sheetName))

{//如果指定sheet名,则取指定sheet中的内容.

sheet =workbook.getSheet(sheetName);

}else{//如果传入的sheet名不存在则默认指向第1个sheet.

sheet = workbook.getSheetAt(0);

}if (sheet == null)

{throw new IOException("文件sheet不存在");

}int rows =sheet.getPhysicalNumberOfRows();if (rows > 0)

{//默认序号

int serialNum = 0;//有数据时才处理 得到类的所有field.

Field[] allFields =clazz.getDeclaredFields();//定义一个map用于存放列的序号和field.

Map fieldsMap = new HashMap();for (int col = 0; col < allFields.length; col++)

{

Field field=allFields[col];//将有注解的field存放到map中.

if (field.isAnnotationPresent(Excel.class))

{//设置类的私有字段属性可访问.

field.setAccessible(true);

fieldsMap.put(++serialNum, field);

}

}for (int i = 1; i < rows; i++)

{//从第2行开始取数据,默认第一行是表头.

Row row =sheet.getRow(i);int cellNum =serialNum;

T entity= null;for (int j = 0; j < cellNum; j++)

{

Cell cell=row.getCell(j);if (cell == null)

{continue;

}else{//先设置Cell的类型，然后就可以把纯数字作为String类型读进来了

row.getCell(j).setCellType(CellType.STRING);

cell=row.getCell(j);

}

String c=cell.getStringCellValue();if(StringUtils.isEmpty(c))

{continue;

}//如果不存在实例则新建.

entity = (entity == null ?clazz.newInstance() : entity);//从map中得到对应列的field.

Field field = fieldsMap.get(j + 1);//取得类型,并根据对象类型设置值.

Class> fieldType =field.getType();if (String.class ==fieldType)

{

field.set(entity, String.valueOf(c));

}else if ((Integer.TYPE == fieldType) || (Integer.class ==fieldType))

{

field.set(entity, Integer.parseInt(c));

}else if ((Long.TYPE == fieldType) || (Long.class ==fieldType))

{

field.set(entity, Long.valueOf(c));

}else if ((Float.TYPE == fieldType) || (Float.class ==fieldType))

{

field.set(entity, Float.valueOf(c));

}else if ((Short.TYPE == fieldType) || (Short.class ==fieldType))

{

field.set(entity, Short.valueOf(c));

}else if ((Double.TYPE == fieldType) || (Double.class ==fieldType))

{

field.set(entity, Double.valueOf(c));

}else if (Character.TYPE ==fieldType)

{if ((c != null) && (c.length() > 0))

{

field.set(entity, Character.valueOf(c.charAt(0)));

}

}else if (java.util.Date.class ==fieldType)

{if (cell.getCellTypeEnum() ==CellType.NUMERIC)

{

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

cell.setCellValue(sdf.format(cell.getNumericCellValue()));

c=sdf.format(cell.getNumericCellValue());

}else{

c=cell.getStringCellValue();

}

}else if (java.math.BigDecimal.class ==fieldType)

{

c=cell.getStringCellValue();

}

}if (entity != null)

{

list.add(entity);

}

}

}returnlist;

}/**

* 对list数据源将其里面的数据导入到excel表单

*

* @param list 导出数据集合

* @param sheetName 工作表的名称

* @return 结果*/

public AjaxResult exportExcel(Listlist, String sheetName)

{

OutputStreamout = null;

HSSFWorkbook workbook= null;try{//得到所有定义字段

Field[] allFields =clazz.getDeclaredFields();

List fields = new ArrayList();//得到所有field并存放到一个list中.

for(Field field : allFields)

{if (field.isAnnotationPresent(Excel.class))

{

fields.add(field);

}

}//产生工作薄对象

workbook = newHSSFWorkbook();//excel2003中每个sheet中最多有65536行

int sheetSize = 65536;//取出一共有多少个sheet.

double sheetNo = Math.ceil(list.size() /sheetSize);for (int index = 0; index <= sheetNo; index++)

{//产生工作表对象

HSSFSheet sheet =workbook.createSheet();if (sheetNo == 0)

{

workbook.setSheetName(index, sheetName);

}else{//设置工作表的名称.

workbook.setSheetName(index, sheetName +index);

}

HSSFRow row;

HSSFCell cell;//产生单元格//产生一行

row = sheet.createRow(0);//写入各个字段的列头名称

for (int i = 0; i < fields.size(); i++)

{

Field field= fields.get(i);

Excel attr= field.getAnnotation(Excel.class);//创建列

cell =row.createCell(i);//设置列中写入内容为String类型

cell.setCellType(CellType.STRING);

HSSFCellStyle cellStyle=workbook.createCellStyle();

cellStyle.setAlignment(HorizontalAlignment.CENTER);

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);if (attr.name().indexOf("注：") >= 0)

{

HSSFFont font=workbook.createFont();

font.setColor(HSSFFont.COLOR_RED);

cellStyle.setFont(font);

cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());

sheet.setColumnWidth(i,6000);

}else{

HSSFFont font=workbook.createFont();//粗体显示

font.setBold(true);//选择需要用到的字体格式

cellStyle.setFont(font);

cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());//设置列宽

sheet.setColumnWidth(i, (int) ((attr.width() + 0.72) * 256));

row.setHeight((short) (attr.height() * 20));

}

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

cellStyle.setWrapText(true);

cell.setCellStyle(cellStyle);//写入列名

cell.setCellValue(attr.name());//如果设置了提示信息则鼠标放上去提示.

if(StringUtils.isNotEmpty(attr.prompt()))

{//这里默认设了2-101列提示.

setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);

}//如果设置了combo属性则本列只能选择不能输入

if (attr.combo().length > 0)

{//这里默认设了2-101列只能选择不能输入.

setHSSFValidation(sheet, attr.combo(), 1, 100, i, i);

}

}int startNo = index *sheetSize;int endNo = Math.min(startNo +sheetSize, list.size());//写入各条记录,每条记录对应excel表中的一行

HSSFCellStyle cs =workbook.createCellStyle();

cs.setAlignment(HorizontalAlignment.CENTER);

cs.setVerticalAlignment(VerticalAlignment.CENTER);for (int i = startNo; i < endNo; i++)

{

row= sheet.createRow(i + 1 -startNo);//得到导出对象.

T vo = (T) list.get(i);for (int j = 0; j < fields.size(); j++)

{//获得field.

Field field = fields.get(j);//设置实体类私有属性可访问

field.setAccessible(true);

Excel attr= field.getAnnotation(Excel.class);try{//设置行高

row.setHeight((short) (attr.height() * 20));//根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.

if(attr.isExport())

{//创建cell

cell =row.createCell(j);

cell.setCellStyle(cs);if (vo == null)

{//如果数据存在就填入,不存在填入空格.

cell.setCellValue("");continue;

}

String dateFormat=attr.dateFormat();

String readConverterExp=attr.readConverterExp();if(StringUtils.isNotEmpty(dateFormat))

{

cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) field.get(vo)));

}else if(StringUtils.isNotEmpty(readConverterExp))

{

cell.setCellValue(convertByExp(String.valueOf(field.get(vo)), readConverterExp));

}else{

cell.setCellType(CellType.STRING);//如果数据存在就填入,不存在填入空格.

cell.setCellValue(StringUtils.isNull(field.get(vo)) ? attr.defaultValue() : field.get(vo) +attr.suffix());

}

}

}catch(Exception e)

{

log.error("导出Excel失败{}", e.getMessage());

}

}

}

}

String filename=encodingFilename(sheetName);out = newFileOutputStream(getAbsoluteFile(filename));

workbook.write(out);returnAjaxResult.success(filename);

}catch(Exception e)

{

log.error("导出Excel异常{}", e.getMessage());return AjaxResult.error("导出Excel失败，请联系网站管理员！");

}finally{if (workbook != null)

{try{

workbook.close();

}catch(IOException e1)

{

e1.printStackTrace();

}

}if (out != null)

{try{out.close();

}catch(IOException e1)

{

e1.printStackTrace();

}

}

}

}/**

* 设置单元格上提示

*

* @param sheet 要设置的sheet.

* @param promptTitle 标题

* @param promptContent 内容

* @param firstRow 开始行

* @param endRow 结束行

* @param firstCol 开始列

* @param endCol 结束列

* @return 设置好的sheet.*/

public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, intfirstRow,int endRow, int firstCol, intendCol)

{//构造constraint对象

DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");//四个参数分别是：起始行、终止行、起始列、终止列

CellRangeAddressList regions = newCellRangeAddressList(firstRow, endRow, firstCol, endCol);//数据有效性对象

HSSFDataValidation dataValidationView = newHSSFDataValidation(regions, constraint);

dataValidationView.createPromptBox(promptTitle, promptContent);

sheet.addValidationData(dataValidationView);returnsheet;

}/**

* 设置某些列的值只能输入预制的数据,显示下拉框.

*

* @param sheet 要设置的sheet.

* @param textlist 下拉框显示的内容

* @param firstRow 开始行

* @param endRow 结束行

* @param firstCol 开始列

* @param endCol 结束列

* @return 设置好的sheet.*/

public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, intendRow,int firstCol, intendCol)

{//加载下拉列表内容

DVConstraint constraint =DVConstraint.createExplicitListConstraint(textlist);//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列

CellRangeAddressList regions = newCellRangeAddressList(firstRow, endRow, firstCol, endCol);//数据有效性对象

HSSFDataValidation dataValidationList = newHSSFDataValidation(regions, constraint);

sheet.addValidationData(dataValidationList);returnsheet;

}/**

* 解析导出值 0=男,1=女,2=未知

*

* @param propertyValue 参数值

* @param converterExp 翻译注解

* @return 解析后值

* @throws Exception*/

public staticString convertByExp(String propertyValue, String converterExp) throws Exception

{try{

String[] convertSource= converterExp.split(",");for(String item : convertSource)

{

String[] itemArray= item.split("=");if (itemArray[0].equals(propertyValue))

{return itemArray[1];

}

}

}catch(Exception e)

{throwe;

}returnpropertyValue;

}/**

* 编码文件名*/

publicString encodingFilename(String filename)

{

filename= UUID.randomUUID().toString() + "_" + filename + ".xls";returnfilename;

}/**

* 获取下载路径

*

* @param filename 文件名称*/

publicString getAbsoluteFile(String filename)

{

String downloadPath= RuoYiConfig.getDownloadPath() +filename;

File desc= newFile(downloadPath);if (!desc.getParentFile().exists())

{

desc.getParentFile().mkdirs();

}returndownloadPath;

}

}