com.ruoyi.common.annotation.Excel Java Examples

The following examples show how to use com.ruoyi.common.annotation.Excel. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 6 votes vote down vote up
/**
 * 获取bean中的属性值
 *
 * @param vo 实体对象
 * @param field 字段
 * @param excel 注解
 * @return 最终的属性值
 */
private Object getTargetValue(T vo, Field field, Excel excel) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
    Object o = field.get(vo);
    if (StrUtil.isNotEmpty(excel.targetAttr())){
        String target = excel.targetAttr();
        if (target.contains(".")){
            String[] targets = target.split("[.]");
            for (String name : targets){
                o = getValue(o, name);
            }
        }else{
            o = getValue(o, target);
        }
    }
    return o;
}
 
Example #2
Source File: ExcelUtil.java    From supplierShop with MIT License 6 votes vote down vote up
/**
 * 创建表格样式
 */
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));
        row.setHeight((short) (attr.height() * 20));
    }
    // 如果设置了提示信息则鼠标放上去提示.
    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);
    }
}
 
Example #3
Source File: ExcelUtil.java    From supplierShop with MIT License 6 votes vote down vote up
/**
 * 获取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;
}
 
Example #4
Source File: ExcelUtil.java    From ruoyiplus with MIT License 6 votes vote down vote up
/**
 * 获取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;
}
 
Example #5
Source File: ExcelUtil.java    From supplierShop with MIT License 6 votes vote down vote up
/**
 * 填充excel数据
 * 
 * @param index 序号
 * @param row 单元格行
 * @param cell 类型单元格
 */
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 + 1 - 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++);
        }
    }
}
 
Example #6
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
public void init(List<T> list, String sheetName, Excel.Type type){
    if (list == null){
        list = new ArrayList<>();
    }
    this.list = list;
    this.sheetName = sheetName;
    this.type = type;
    createExcelField();
    createWorkbook();
}
 
Example #7
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
/**
 * 放到字段集合中
 */
private void putToFields(List<Field> fields){
    for (Field field : fields){
        Excel attr = field.getAnnotation(Excel.class);
        boolean flag = attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type);
        if (flag){
            this.fields.add(field);
        }
    }
}
 
Example #8
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
/**
 * 添加单元格
 */
private void addCell(Excel attr, Row row, T vo, Field field, int column, CellStyle cs){
    Cell cell;
    try{
        // 设置行高
        row.setHeight((short) (attr.height() * 20));
        // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
        if (attr.isExport()){
            // 创建cell
            cell = row.createCell(column);
            cell.setCellStyle(cs);

            // 用于读取对象中的属性
            Object value = getTargetValue(vo, field, attr);
            String dateFormat = attr.dateFormat();
            String readConverterExp = attr.readConverterExp();
            if (StrUtil.isNotEmpty(dateFormat) && ObjectUtil.isNotNull(value)){
                cell.setCellValue(DateUtil.format(((Date) value), dateFormat));
            }else if (StrUtil.isNotEmpty(readConverterExp) && ObjectUtil.isNotNull(value)){
                cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
            }else{
                cell.setCellType(CellType.STRING);
                // 如果数据存在就填入,不存在填入空格.
                cell.setCellValue(ObjectUtil.isNull(value) ? attr.defaultValue() : value + attr.suffix());
            }
        }
    }catch (Exception e){
        log.error("导出Excel失败%s", e);
    }
}
 
Example #9
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
/**
 * 创建单元格
 */
private void createCell(Excel attr, Row row, int column){
    // 创建列
    Cell cell = row.createCell(column);
    // 设置列中写入内容为String类型
    cell.setCellType(CellType.STRING);
    // 写入列名
    cell.setCellValue(attr.name());
    CellStyle cellStyle = createStyle(attr, row, column);
    cell.setCellStyle(cellStyle);
}
 
Example #10
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
/**
 * 填充excel数据
 *  @param index 序号
 *
 */
private void fillExcelData(int index){
    int startNo = index * SHEET_SIZE;
    int endNo = Math.min(startNo + SHEET_SIZE, list.size());
    // 写入各条记录,每条记录对应excel表中的一行
    CellStyle cs = workbook.createCellStyle();
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    for (int i = startNo; i < endNo; i++){
        Row row = sheet.createRow(i + 1 - startNo);
        // 得到导出对象.
        T vo = list.get(i);
        int excelsNo = 0;
        for (int column = 0; column < fields.size(); column++){
            // 获得field.
            Field field = fields.get(column);
            // 设置实体类私有属性可访问
            field.setAccessible(true);
            if (field.isAnnotationPresent(Excel.class)){
                addCell(field.getAnnotation(Excel.class), row, vo, field, column, cs);
            }
            if (field.isAnnotationPresent(Excels.class)){
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                Excel excel = excels[excelsNo++];
                addCell(excel, row, vo, field, column, cs);
            }
        }
    }
}
 
Example #11
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 5 votes vote down vote up
/**
 * 对list数据源将其里面的数据导入到excel表单
 *
 * @return 结果
 */
public AjaxResult exportExcel() {
    OutputStream out = null;
    // 产生工作薄对象
    try{
        // 取出一共有多少个sheet.
        double sheetNo = Math.ceil(list.size() / SHEET_SIZE);
        for (int index = 0; index <= sheetNo; index++) {
            createSheet(sheetNo, index);
            // 产生一行
            Row row = sheet.createRow(0);
            // 写入各个字段的列头名称
            this.setCellTitle(fields, row);
            if (Excel.Type.EXPORT.equals(type)){
                fillExcelData(index);
            }
        }
        String filename = encodingFilename(sheetName);
        out = new FileOutputStream(getAbsoluteFile(filename));
        workbook.write(out);
        return AjaxResult.success(filename);
    } catch (Exception e) {
        log.error("导出Excel异常", e);
        throw new BusinessException("导出Excel失败,请联系网站管理员!");
    } finally {
        if (out != null) {
            try {
                out.close();
            } catch (IOException e1) {
                log.error(e1.getMessage(), e1);
            }
        }
    }
}
 
Example #12
Source File: ExcelUtil.java    From ruoyiplus with MIT License 5 votes vote down vote up
/**
 * 得到所有定义字段
 */
private void createExcelField()
{
    this.fields = new ArrayList<Field>();
    Field[] allFields = clazz.getDeclaredFields();
    // 得到所有field并存放到一个list中.
    for (Field field : allFields)
    {
        Excel attr = field.getAnnotation(Excel.class);
        if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
        {
            fields.add(field);
        }
    }
}
 
Example #13
Source File: ExcelUtil.java    From supplierShop with MIT License 5 votes vote down vote up
/**
 * 放到字段集合中
 */
private void putToField(Field field, Excel attr)
{
    if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
    {
        this.fields.add(new Object[] { field, attr });
    }
}
 
Example #14
Source File: ExcelUtil.java    From supplierShop with MIT License 5 votes vote down vote up
/**
 * 得到所有定义字段
 */
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);
            }
        }
    }
}
 
Example #15
Source File: ExcelUtil.java    From supplierShop with MIT License 5 votes vote down vote up
/**
 * 添加单元格
 */
public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
{
    Cell cell = null;
    try
    {
        // 设置行高
        row.setHeight((short) (attr.height() * 20));
        // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
        if (attr.isExport())
        {
            // 创建cell
            cell = row.createCell(column);
            cell.setCellStyle(styles.get("data"));

            // 用于读取对象中的属性
            Object value = getTargetValue(vo, field, attr);
            String dateFormat = attr.dateFormat();
            String readConverterExp = attr.readConverterExp();
            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(String.valueOf(value), readConverterExp));
            }
            else
            {
                // 设置列类型
                setCellVo(value, attr, cell);
            }
        }
    }
    catch (Exception e)
    {
        log.error("导出Excel失败{}", e);
    }
    return cell;
}
 
Example #16
Source File: ExcelUtil.java    From supplierShop with MIT License 5 votes vote down vote up
/**
 * 设置单元格信息
 * 
 * @param value 单元格值
 * @param attr 注解相关
 * @param cell 单元格信息
 */
public void setCellVo(Object value, Excel attr, Cell cell)
{
    if (ColumnType.STRING == attr.cellType())
    {
        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
    }
    else if (ColumnType.NUMERIC == attr.cellType())
    {
        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(Integer.parseInt(value + ""));
    }
}
 
Example #17
Source File: ExcelUtil.java    From supplierShop with MIT License 5 votes vote down vote up
/**
 * 创建单元格
 */
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;
}
 
Example #18
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 4 votes vote down vote up
/**
 * 对excel表单指定表格索引名转换成list
 *
 * @param sheetName 表格索引名
 * @param input     输入流
 * @return 转换后集合
 */
private List<T> importExcel(String sheetName, InputStream input) {
    this.type = Excel.Type.IMPORT;
    List<T> list = new ArrayList<>();

    try (Workbook workbook = WorkbookFactory.create(input)) {
        this.workbook = workbook;
        Sheet sheet;
        if (StrUtil.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) {
            // 定义一个map用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<>();
            //获取表头
            Row heard = sheet.getRow(0);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
                Cell cell = heard.getCell(i);
                if (ObjectUtil.isNotNull(cell)) {
                    String value = Convert.toStr(this.getCellValue(heard, i));
                    cellMap.put(value, i);
                } else {
                    cellMap.put(null, i);
                }
            }

            // 有数据时才处理 得到类的所有field.
            Field[] allFields = clazz.getDeclaredFields();
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<>();
            for (Field field : allFields) {
                // 将有注解的field存放到map中.
                Excel attr = field.getAnnotation(Excel.class);
                boolean annotation = attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type);
                if(annotation){
                    // 设置类的私有字段属性可访问.
                    field.setAccessible(true);
                    Integer column = cellMap.get(attr.name());
                    fieldsMap.put(column, field);
                }
            }
            this.getImportData(rows,fieldsMap);
        }
    } catch (InvalidFormatException | IOException | IllegalAccessException | InstantiationException e) {
        log.error(e.getMessage(), e);
    }
    return list;
}
 
Example #19
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 4 votes vote down vote up
private void getImportData(int rows, Map<Integer, Field> fieldsMap) throws IllegalAccessException, InstantiationException {
    for (int i = 1; i < rows; i++) {
        // 从第2行开始取数据,默认第一行是表头.
        Row row = sheet.getRow(i);
        T entity = null;
        for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) {
            Object val = this.getCellValue(row, entry.getKey());
            // 如果不存在实例则新建.
            entity = (entity == null ? clazz.newInstance() : entity);
            // 从map中得到对应列的field.
            Field field = fieldsMap.get(entry.getKey());
            // 取得类型,并根据对象类型设置值.
            Class<?> fieldType = field.getType();
            if (String.class == fieldType) {
                field.set(entity, Convert.toStr(val));
            } else if (Integer.TYPE == fieldType || Integer.class == fieldType) {
                field.set(entity, Convert.toInt(val));
            } else if (Long.TYPE == fieldType || Long.class == fieldType) {
                field.set(entity, Convert.toLong(val));
            } else if (Float.TYPE == fieldType || Float.class == fieldType) {
                field.set(entity, Convert.toFloat(val));
            } else if (Short.TYPE == fieldType || Short.class == fieldType) {
                field.set(entity, Convert.toShort(val));
            } else if (Double.TYPE == fieldType || Double.class == fieldType) {
                field.set(entity, Convert.toDouble(val));
            }else if (java.util.Date.class == fieldType) {
                if (val instanceof String){
                    val = DateUtil.parse(Convert.toStr(val));
                    field.set(entity, val);
                }else if (val instanceof Double){
                    val = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) val);
                    field.set(entity, val);
                }
            }else if (BigDecimal.class == fieldType){
                field.set(entity, Convert.toBigDecimal(val));
            }
            if (ObjectUtil.isNotNull(fieldType)){
                Excel attr = field.getAnnotation(Excel.class);
                String propertyName = field.getName();
                if (StrUtil.isNotEmpty(attr.targetAttr())){
                    propertyName = field.getName() + "." + attr.targetAttr();
                }else if (StrUtil.isNotEmpty(attr.readConverterExp())){
                    val = reverseByExp(String.valueOf(val), attr.readConverterExp());
                }
                ReflectUtil.setFieldValue(entity, propertyName, val);
            }
        }
        if (entity != null) {
            list.add(entity);
        }
    }
}
 
Example #20
Source File: ExcelUtil.java    From ruoyiplus with MIT License 4 votes vote down vote up
/**
 * 填充excel数据
 * 
 * @param index 序号
 * @param row 单元格行
 * @param cell 类型单元格
 */
public void fillExcelData(int index, Row row, Cell cell)
{
    int startNo = index * sheetSize;
    int endNo = Math.min(startNo + sheetSize, list.size());
    // 写入各条记录,每条记录对应excel表中的一行
    CellStyle cs = wb.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;
                    }

                    // 用于读取对象中的属性
                    Object value = getTargetValue(vo, field, attr);
                    String dateFormat = attr.dateFormat();
                    String readConverterExp = attr.readConverterExp();
                    if (StringUtils.isNotEmpty(dateFormat))
                    {
                        cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                    }
                    else if (StringUtils.isNotEmpty(readConverterExp))
                    {
                        cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                    }
                    else
                    {
                        cell.setCellType(CellType.STRING);
                        // 如果数据存在就填入,不存在填入空格.
                        cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
                    }
                }
            }
            catch (Exception e)
            {
                log.error("导出Excel失败{}", e.getMessage());
            }
        }
    }
}
 
Example #21
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 2 votes vote down vote up
/**
 * 对list数据源将其里面的数据导入到excel表单
 *
 * @param list 导出数据集合
 * @param sheetName 工作表的名称
 * @return 结果
 */
public AjaxResult exportExcel(List<T> list, String sheetName){
    this.init(list, sheetName, Excel.Type.EXPORT);
    return exportExcel();
}
 
Example #22
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 2 votes vote down vote up
/**
 * 对list数据源将其里面的数据导入到excel表单
 *
 * @param sheetName 工作表的名称
 * @return 结果
 */
public AjaxResult importTemplateExcel(String sheetName){
    this.init(null, sheetName, Excel.Type.IMPORT);
    return exportExcel();
}