"原文地址：[链接] 数据库 Excel 导出操作代码过于冗长惨不忍睹, 无法复用。目录 [链接] [链接] [链接] [链接] 注解配合工具类做了个小工具如下: [链接][链接]第一步：自定义注解: [链接] package com.ruoyi.framework.aspectj.lang.ann ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 3 浏览 • 5 个月前

Java之基于注解的Excel导出

原文地址：https://blog.csdn.net/wenxingchen/article/details/84791205

数据库 Excel 导出操作代码过于冗长惨不忍睹, 无法复用。

注解配合工具类做了个小工具如下:

第一步：自定义注解:

package com.ruoyi.framework.aspectj.lang.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/


自定义导出 Excel 数据注解



@author ruoyi
/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
/*

导出到 Excel 中的名字.
*/
public String name();

/

日期格式, 如: yyyy-MM-dd
*/
public String dateFormat() default "";

/

读取内容转表达式 (如: 0= 男,1= 女,2= 未知)
*/
public String readConverterExp() default "";

/

导出时在 excel 中每个列的高度 单位为字符
*/
public double height() default 14;

/

导出时在 excel 中每个列的宽 单位为字符
*/
public double width() default 20;

/

文字后缀, 如 % 90 变成 90%
*/
public String suffix() default "";

/

当值为空时, 字段的默认值
*/
public String defaultValue() default "";

/

提示信息
*/
public String prompt() default "";

/

设置只能选择不能输入的列内容.
*/
public String[] combo() default {};

/

是否导出数据, 应对需求: 有时我们需要导出一份模板, 这是标题需要但内容需要用户手工填写.
*/
public boolean isExport() default true;
}

第二步：实体类:

package com.ruoyi.project.system.dict.domain;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import com.ruoyi.framework.web.domain.BaseEntity;
/


字典数据表 sys_dict_data



@author ruoyi
*/
public class DictData extends BaseEntity
{
private static final long serialVersionUID = 1L;
/ 字典编码 */
@Excel(name = "字典编码")
private Long dictCode;
/ 字典排序 */
@Excel(name = "字典排序")
private Long dictSort;
/ 字典标签 */
@Excel(name = "字典标签")
private String dictLabel;
/ 字典键值 */
@Excel(name = "字典键值")
private String dictValue;
/ 字典类型 */
@Excel(name = "字典类型")
private String dictType;
/ 字典样式 */
@Excel(name = "字典样式")
private String cssClass;
/ 表格字典样式 */
private String listClass;
/ 是否默认（Y 是 N 否） */
@Excel(name = "是否默认", readConverterExp = "Y= 是,N= 否")
private String isDefault;
/ 状态（0 正常 1 停用） */
@Excel(name = "状态", readConverterExp = "0= 正常,1= 停用")
private String status;
public Long getDictCode()
{
return dictCode;
}
public void setDictCode(Long dictCode)
{
this.dictCode = dictCode;
}
public Long getDictSort()
{
return dictSort;
}
public void setDictSort(Long dictSort)
{
this.dictSort = dictSort;
}
public String getDictLabel()
{
return dictLabel;
}
public void setDictLabel(String dictLabel)
{
this.dictLabel = dictLabel;
}
public String getDictValue()
{
return dictValue;
}
public void setDictValue(String dictValue)
{
this.dictValue = dictValue;
}
public String getDictType()
{
return dictType;
}
public void setDictType(String dictType)
{
this.dictType = dictType;
}
public String getCssClass()
{
return cssClass;
}
public void setCssClass(String cssClass)
{
this.cssClass = cssClass;
}
public String getListClass()
{
return listClass;
}
public void setListClass(String listClass)
{
this.listClass = listClass;
}
public String getIsDefault()
{
return isDefault;
}
public void setIsDefault(String isDefault)
{
this.isDefault = isDefault;
}
public String getStatus()
{
return status;
}
public void setStatus(String status)
{
this.status = status;
}
@Override
public String toString() {
return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
.append("dictCode", getDictCode())
.append("dictSort", getDictSort())
.append("dictLabel", getDictLabel())
.append("dictValue", getDictValue())
.append("dictType", getDictType())
.append("cssClass", getCssClass())
.append("listClass", getListClass())
.append("isDefault", getIsDefault())
.append("status", getStatus())
.append("createBy", getCreateBy())
.append("createTime", getCreateTime())
.append("updateBy", getUpdateBy())
.append("updateTime", getUpdateTime())
.append("remark", getRemark())
.toString();
}
}

第三步：解析工具类:

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<T>
{
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
public Class<T> clazz;
public ExcelUtil(Class<T> clazz)
{
this.clazz = clazz;
}
/

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

@param input 输入流
@return 转换后集合
*/
public List<T> importExcel(InputStream input) throws Exception
{
return importExcel(StringUtils.EMPTY, input);
}

/


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



@param sheetName 表格索引名


@param input 输入流


@return 转换后集合
*/
public List<T> importExcel(String sheetName, InputStream input) throws Exception
{
List<T> list = new ArrayList<T>();
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<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
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 </span>=<span style="color: rgba(0, 0, 0, 1)"> cell.getStringCellValue();
         </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (StringUtils.isEmpty(c))
         {
             </span><span style="color: rgba(0, 0, 255, 1)">continue</span><span style="color: rgba(0, 0, 0, 1)">;
         }

         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 如果不存在实例则新建.</span>
         entity = (entity == <span style="color: rgba(0, 0, 255, 1)">null</span> ?<span style="color: rgba(0, 0, 0, 1)"> clazz.newInstance() : entity);
         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 从map中得到对应列的field.</span>
         Field field = fieldsMap.<span style="color: rgba(0, 0, 255, 1)">get</span>(j + <span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">);
         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 取得类型,并根据对象类型设置值.</span>
         Class&lt;?&gt; fieldType =<span style="color: rgba(0, 0, 0, 1)"> field.getType();
         </span><span style="color: rgba(0, 0, 255, 1)">if</span> (String.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType)
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, String.valueOf(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> ((Integer.TYPE == fieldType) || (Integer.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType))
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, Integer.parseInt(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> ((Long.TYPE == fieldType) || (Long.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType))
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, Long.valueOf(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> ((Float.TYPE == fieldType) || (Float.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType))
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, Float.valueOf(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> ((Short.TYPE == fieldType) || (Short.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType))
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, Short.valueOf(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> ((Double.TYPE == fieldType) || (Double.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType))
         {
             field.</span><span style="color: rgba(0, 0, 255, 1)">set</span><span style="color: rgba(0, 0, 0, 1)">(entity, Double.valueOf(c));
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (Character.TYPE ==<span style="color: rgba(0, 0, 0, 1)"> fieldType)
         {
             </span><span style="color: rgba(0, 0, 255, 1)">if</span> ((c != <span style="color: rgba(0, 0, 255, 1)">null</span>) &amp;&amp; (c.length() &gt; <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">))
             {
                 field.</span><span style="color: rgba(0, 0, 255, 1)">set</span>(entity, Character.valueOf(c.charAt(<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)));
             }
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (java.util.Date.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType)
         {
             </span><span style="color: rgba(0, 0, 255, 1)">if</span> (cell.getCellTypeEnum() ==<span style="color: rgba(0, 0, 0, 1)"> CellType.NUMERIC)
             {
                 SimpleDateFormat sdf </span>= <span style="color: rgba(0, 0, 255, 1)">new</span> SimpleDateFormat(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">yyyy-MM-dd HH🇲🇲ss</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
                 cell.setCellValue(sdf.format(cell.getNumericCellValue()));
                 c </span>=<span style="color: rgba(0, 0, 0, 1)"> sdf.format(cell.getNumericCellValue());
             }
             </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
             {
                 c </span>=<span style="color: rgba(0, 0, 0, 1)"> cell.getStringCellValue();
             }
         }
         </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span> (java.math.BigDecimal.<span style="color: rgba(0, 0, 255, 1)">class</span> ==<span style="color: rgba(0, 0, 0, 1)"> fieldType)
         {
             c </span>=<span style="color: rgba(0, 0, 0, 1)"> cell.getStringCellValue();
         }
     }
     </span><span style="color: rgba(0, 0, 255, 1)">if</span> (entity != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
     {
         list.add(entity);
     }
 }

}
return list;
}


/


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



@param list 导出数据集合


@param sheetName 工作表的名称


@return 结果
*/
public AjaxResult exportExcel(List<T> list, String sheetName)
{
OutputStream out = null;
HSSFWorkbook workbook = null;
try
{
// 得到所有定义字段
Field[] allFields = clazz.getDeclaredFields();
List<Field> fields = new ArrayList<Field>();
// 得到所有 field 并存放到一个 list 中.
for (Field field : allFields)
{
if (field.isAnnotationPresent(Excel.class))
{
fields.add(field);
}
}
 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 产生工作薄对象</span>
 workbook = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> HSSFWorkbook();
 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> excel2003中每个sheet中最多有65536行</span>
 <span style="color: rgba(0, 0, 255, 1)">int</span> sheetSize = <span style="color: rgba(128, 0, 128, 1)">65536</span><span style="color: rgba(0, 0, 0, 1)">;
 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 取出一共有多少个sheet.</span>
 <span style="color: rgba(0, 0, 255, 1)">double</span> sheetNo = Math.ceil(list.size() /<span style="color: rgba(0, 0, 0, 1)"> sheetSize);
 </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> index = <span style="color: rgba(128, 0, 128, 1)">0</span>; index &lt;= sheetNo; index++<span style="color: rgba(0, 0, 0, 1)">)
 {
     </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 产生工作表对象</span>
     HSSFSheet sheet =<span style="color: rgba(0, 0, 0, 1)"> workbook.createSheet();
     </span><span style="color: rgba(0, 0, 255, 1)">if</span> (sheetNo == <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)
     {
         workbook.setSheetName(index, sheetName);
     }
     </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
     {
         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 设置工作表的名称.</span>
         workbook.setSheetName(index, sheetName +<span style="color: rgba(0, 0, 0, 1)"> index);
     }
     HSSFRow row;
     HSSFCell cell; </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 产生单元格

     </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 产生一行</span>
     row = sheet.createRow(<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">);
     </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写入各个字段的列头名称</span>
     <span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = <span style="color: rgba(128, 0, 128, 1)">0</span>; i &lt; fields.size(); i++<span style="color: rgba(0, 0, 0, 1)">)
     {
         Field field </span>= fields.<span style="color: rgba(0, 0, 255, 1)">get</span><span style="color: rgba(0, 0, 0, 1)">(i);
         Excel attr </span>= field.getAnnotation(Excel.<span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 创建列</span>
         cell =<span style="color: rgba(0, 0, 0, 1)"> row.createCell(i);
         </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 设置列中写入内容为String类型</span>





                    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);
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写入列名</span>

                    cell.setCellValue(attr.name());
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 如果设置了提示信息则鼠标放上去提示.</span>
                <span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (StringUtils.isNotEmpty(attr.prompt()))
                {
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 这里默认设了2-101列提示.</span>
                    setHSSFPrompt(sheet, <span style="color: rgba(128, 0, 0, 1)">""</span>, attr.prompt(), <span style="color: rgba(128, 0, 128, 1)">1</span>, <span style="color: rgba(128, 0, 128, 1)">100</span><span style="color: rgba(0, 0, 0, 1)">, i, i);
                }
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 如果设置了combo属性则本列只能选择不能输入</span>
                <span style="color: rgba(0, 0, 255, 1)">if</span> (attr.combo().length &gt; <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">)
                {
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 这里默认设了2-101列只能选择不能输入.</span>
                    setHSSFValidation(sheet, attr.combo(), <span style="color: rgba(128, 0, 128, 1)">1</span>, <span style="color: rgba(128, 0, 128, 1)">100</span><span style="color: rgba(0, 0, 0, 1)">, i, i);
                }
            }

            </span><span style="color: rgba(0, 0, 255, 1)">int</span> startNo = index *<span style="color: rgba(0, 0, 0, 1)"> sheetSize;
            </span><span style="color: rgba(0, 0, 255, 1)">int</span> endNo = Math.min(startNo +<span style="color: rgba(0, 0, 0, 1)"> sheetSize, list.size());
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 写入各条记录,每条记录对应excel表中的一行</span>
            HSSFCellStyle cs =<span style="color: rgba(0, 0, 0, 1)"> workbook.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);
            cs.setVerticalAlignment(VerticalAlignment.CENTER);
            </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = startNo; i &lt; endNo; i++<span style="color: rgba(0, 0, 0, 1)">)
            {
                row </span>= sheet.createRow(i + <span style="color: rgba(128, 0, 128, 1)">1</span> -<span style="color: rgba(0, 0, 0, 1)"> startNo);
                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 得到导出对象.</span>
                T vo = (T) list.<span style="color: rgba(0, 0, 255, 1)">get</span><span style="color: rgba(0, 0, 0, 1)">(i);
                </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> j = <span style="color: rgba(128, 0, 128, 1)">0</span>; j &lt; fields.size(); j++<span style="color: rgba(0, 0, 0, 1)">)
                {
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 获得field.</span>
                    Field field = fields.<span style="color: rgba(0, 0, 255, 1)">get</span><span style="color: rgba(0, 0, 0, 1)">(j);
                    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 设置实体类私有属性可访问</span>
                    field.setAccessible(<span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">);
                    Excel attr </span>= field.getAnnotation(Excel.<span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
                    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)">
                    {
                        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 设置行高</span>
                        row.setHeight((<span style="color: rgba(0, 0, 255, 1)">short</span>) (attr.height() * <span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">));
                        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.</span>
                        <span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (attr.isExport())
                        {
                            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 创建cell</span>
                            cell =<span style="color: rgba(0, 0, 0, 1)"> row.createCell(j);
                            cell.setCellStyle(cs);
                            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (vo == <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
                            {
                                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 如果数据存在就填入,不存在填入空格.</span>
                                cell.setCellValue(<span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">);
                                </span><span style="color: rgba(0, 0, 255, 1)">continue</span><span style="color: rgba(0, 0, 0, 1)">;
                            }

                            String dateFormat </span>=<span style="color: rgba(0, 0, 0, 1)"> attr.dateFormat();
                            String readConverterExp </span>=<span style="color: rgba(0, 0, 0, 1)"> attr.readConverterExp();
                            </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (StringUtils.isNotEmpty(dateFormat))
                            {
                                cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) field.</span><span style="color: rgba(0, 0, 255, 1)">get</span><span style="color: rgba(0, 0, 0, 1)">(vo)));
                            }
                            </span><span style="color: rgba(0, 0, 255, 1)">else</span> <span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (StringUtils.isNotEmpty(readConverterExp))
                            {
                                cell.setCellValue(convertByExp(String.valueOf(field.</span><span style="color: rgba(0, 0, 255, 1)">get</span><span style="color: rgba(0, 0, 0, 1)">(vo)), readConverterExp));
                            }
                            </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
                            {
                                cell.setCellType(CellType.STRING);
                                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 如果数据存在就填入,不存在填入空格.</span>
                                cell.setCellValue(StringUtils.isNull(field.<span style="color: rgba(0, 0, 255, 1)">get</span>(vo)) ? attr.defaultValue() : field.<span style="color: rgba(0, 0, 255, 1)">get</span>(vo) +<span style="color: rgba(0, 0, 0, 1)"> attr.suffix());
                            }
                        }
                    }
                    </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e)
                    {
                        log.error(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">导出Excel失败{}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, e.getMessage());
                    }
                }
            }
        }
        String filename </span>=<span style="color: rgba(0, 0, 0, 1)"> encodingFilename(sheetName);
        </span><span style="color: rgba(0, 0, 255, 1)">out</span> = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> FileOutputStream(getAbsoluteFile(filename));
        workbook.write(</span><span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> AjaxResult.success(filename);
    }
    </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e)
    {
        log.error(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">导出Excel异常{}</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, e.getMessage());
        </span><span style="color: rgba(0, 0, 255, 1)">return</span> AjaxResult.error(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">导出Excel失败，请联系网站管理员！</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    }
    </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)">
    {
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (workbook != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
        {
            </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)">
            {
                workbook.close();
            }
            </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (IOException e1)
            {
                e1.printStackTrace();
            }
        }
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (<span style="color: rgba(0, 0, 255, 1)">out</span> != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
        {
            </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)">
            {
                </span><span style="color: rgba(0, 0, 255, 1)">out</span><span style="color: rgba(0, 0, 0, 1)">.close();
            }
            </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (IOException e1)
            {
                e1.printStackTrace();
            }
        }
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
 * 设置单元格上提示
 * 
 * @param sheet 要设置的sheet.
 * @param promptTitle 标题
 * @param promptContent 内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> firstRow,
        </span><span style="color: rgba(0, 0, 255, 1)">int</span> endRow, <span style="color: rgba(0, 0, 255, 1)">int</span> firstCol, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> endCol)
{
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 构造constraint对象</span>
    DVConstraint constraint = DVConstraint.createCustomFormulaConstraint(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DD1</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 四个参数分别是：起始行、终止行、起始列、终止列</span>
    CellRangeAddressList regions = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 数据有效性对象</span>
    HSSFDataValidation dataValidationView = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> HSSFDataValidation(regions, constraint);
    dataValidationView.createPromptBox(promptTitle, promptContent);
    sheet.addValidationData(dataValidationView);
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sheet;
}

</span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, <span style="color: rgba(0, 0, 255, 1)">int</span> firstRow, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> endRow,
        </span><span style="color: rgba(0, 0, 255, 1)">int</span> firstCol, <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> endCol)
{
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 加载下拉列表内容</span>
    DVConstraint constraint =<span style="color: rgba(0, 0, 0, 1)"> DVConstraint.createExplicitListConstraint(textlist);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列</span>
    CellRangeAddressList regions = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 数据有效性对象</span>
    HSSFDataValidation dataValidationList = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> HSSFDataValidation(regions, constraint);
    sheet.addValidationData(dataValidationList);
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sheet;
}

</span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
 * 解析导出值 0=男,1=女,2=未知
 * 
 * @param propertyValue 参数值
 * @param converterExp 翻译注解
 * @return 解析后值
 * @throws Exception
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> String convertByExp(String propertyValue, String converterExp) throws Exception
{
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)">
    {
        String[] convertSource </span>= converterExp.split(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">,</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String item : convertSource)
        {
            String[] itemArray </span>= item.split(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">=</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
            </span><span style="color: rgba(0, 0, 255, 1)">if</span> (itemArray[<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">].equals(propertyValue))
            {
                </span><span style="color: rgba(0, 0, 255, 1)">return</span> itemArray[<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">];
            }
        }
    }
    </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e)
    {
        </span><span style="color: rgba(0, 0, 255, 1)">throw</span><span style="color: rgba(0, 0, 0, 1)"> e;
    }
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> propertyValue;
}

</span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
 * 编码文件名
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String encodingFilename(String filename)
{
    filename </span>= UUID.randomUUID().toString() + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">_</span><span style="color: rgba(128, 0, 0, 1)">"</span> + filename + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">.xls</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> filename;
}

</span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
 * 获取下载路径
 * 
 * @param filename 文件名称
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getAbsoluteFile(String filename)
{
    String downloadPath </span>= RuoYiConfig.getDownloadPath() +<span style="color: rgba(0, 0, 0, 1)"> filename;
    File desc </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> File(downloadPath);
    </span><span style="color: rgba(0, 0, 255, 1)">if</span> (!<span style="color: rgba(0, 0, 0, 1)">desc.getParentFile().exists())
    {
        desc.getParentFile().mkdirs();
    }
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> downloadPath;
}

}

第四步：使用:

@Log(title = "字典数据", businessType = BusinessType.EXPORT)
    @RequiresPermissions("system:dict:export")
    @PostMapping("/export")
    @ResponseBody
    public AjaxResult export(DictData dictData)
    {
        List<DictData> list = dictDataService.selectDictDataList(dictData);
        ExcelUtil<DictData> util = new ExcelUtil<DictData>(DictData.class);
        return util.exportExcel(list, "dictData");}

Java之基于注解的Excel导出

第一步：自定义注解:

第二步：实体类:

第三步：解析工具类:

第四步：使用:

相关帖子

随便看看

Java之基于注解的Excel导出