基于easypoi的excel表格导出

依赖：

<dependency>
           <groupId>cn.afterturn</groupId>
           <artifactId>easypoi-base</artifactId>
           <version>4.1.2</version>
       </dependency>
       <dependency>
           <groupId>cn.afterturn</groupId>
           <artifactId>easypoi-web</artifactId>
           <version>4.1.2</version>
       </dependency>
       <dependency>
           <groupId>cn.afterturn</groupId>
           <artifactId>easypoi-annotation</artifactId>
           <version>4.1.2</version>
       </dependency>

第一步：自定义注解:

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
  
/**
 * 自定义导出Excel数据注解
 **/
@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;
}

第二步：实体类:（为每个需要的字段打上@Excel注解）

import java.time.LocalDateTime;
  
import com.ciih.authcenter.client.util.excel.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
  
/**
 * (User)表实体类
 */
@SuppressWarnings("serial")
@EqualsAndHashCode(callSuper = true)
@Data
public class User {
    @Excel(name = "编号")
    @ApiModelProperty(value = "主键")
    private String id;
    @Excel(name = "账号")
    @ApiModelProperty(value = "账号")
    private String loginName;
    @Excel(name = "用户名")
    @ApiModelProperty(value = "用户名")
    private String userName;
    @ApiModelProperty(value = "用户名拼音")
    private String namePinyin;
    @Excel(name = "性别", readConverterExp = "1=男,0=女")
    @ApiModelProperty(value = "性别")
    private String gender;
    @Excel(name = "证件类型",readConverterExp="1=居民身份证,2=香港居民来往内地通行证,3=澳门居民来往内地通行证,4=台湾居民来往大陆通行证,6=护照")
    @ApiModelProperty(value = "证件类型")
    private String credType;
    @Excel(name = "证件号码")
    @ApiModelProperty(value = "证件号码")
    private String credNum;
    @ApiModelProperty(value = "机构id")
    private String orgId;
    @Excel(name = "机构名称")
    @ApiModelProperty(value = "机构名称")
    private String orgName;
    @Excel(name = "电话")
    @ApiModelProperty(value = "电话")
    private String phone;
    @Excel(name = "邮箱")
    @ApiModelProperty(value = "邮箱")
    private String email;
    @Excel(name = "人员类型",readConverterExp = "student=学生,teacher=教师,parent=家长,system=系统人员,developers=开发者,manager=管理员")
    @ApiModelProperty(value = "人员类型")
    private String personType;
    @Excel(name = "应用系统角色编码")
    @ApiModelProperty(value = "应用系统角色编码")
    private String appRoleCode;
    @ApiModelProperty(value = "创建时间")
    private LocalDateTime createTime;
    @ApiModelProperty(value = "更新时间")
    private LocalDateTime updateTime;
}

第三步：解析工具类:（读者请直接复制）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
  
import javax.servlet.http.HttpServletResponse;
  
import org.apache.commons.lang3.StringUtils;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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;
  
/**
 * Excel相关操作
 **/
public class ExcelUtil<T> {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
  
    private 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();
  
            /**
             * 这里是要将实体类的属性与excel表的列序号对应上，有两种方式：
             * 1.按照先后顺序进行一一对应。
             * 2.按照注解的name值与表头对应起来
             */
  
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
  
            //定义一个name到Excel表的index的映射Map
            HashMap<String, Integer> name2index = new HashMap<>();
  
            //默认第一行是表头
            Row r = sheet.getRow(0);
            for (int i = 0; i < allFields.length; i++) {
                Cell cell = r.getCell(i);
                if (cell == null) {
                    continue;
                } else {
                    // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
                    r.getCell(i).setCellType(CellType.STRING);
                    cell = r.getCell(i);
                    String c = cell.getStringCellValue();
                    if (StringUtils.isEmpty(c)) {
                        continue;
                    }
                    name2index.put(c, i);
                }
            }
  
            for (int col = 0; col < allFields.length; col++) {
                Field field = allFields[col];
                // 将有注解的field存放到map中.
                if (field.isAnnotationPresent(Excel.class)) {
                    Excel excel = field.getAnnotation(Excel.class);
                    String name = excel.name();
                    Integer index = name2index.get(name);
                    if (index != null) {
                        field.setAccessible(true);
                        fieldsMap.put(index, 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 = allFields.length;
                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);
                    // 取得类型,并根据对象类型设置值.
                    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 (Date.class == fieldType) {
                        //对字符串解析成日期
  
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  
                        String s = cell.getStringCellValue()
                                .replaceAll("/", "-")
                                .replaceAll("上午", "")
                                .replaceAll("下午", "")
                                .replaceAll("  ", " ");
                        System.out.println("----------------------------:" + s);
                        Date parse = sdf.parse(s);
                        field.set(entity, parse);
//                        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);
                }
            }
        }
  
        return list;
    }
  
    /**
     * 对list数据源将其里面的数据导入到excel表单
     * 针对List<Map>类型的数据
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public static void exportMapExcel(List<Map> list, String[] title, String sheetName, HttpServletResponse response) {
        if (null == list || list.size() == 0) {
            return;
        }
  
        HSSFWorkbook workbook = null;
        try {
            // 产生工作薄对象
            workbook = new HSSFWorkbook();
            // 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);
                if (null == title || title.length == 0) {
                    throw new RuntimeException("导出错误");
                }
                for (int i = 0; i < title.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(title[i]);
                }
                int cell_idx = 0;
                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // 写入各条记录,每条记录对应excel表中的一行
                for (int i = startNo; i < endNo; i++) {
                    row = sheet.createRow(i + 1 - startNo);
                    // 得到导出对象.
                    Map map = list.get(i);
                    Set keySet = map.keySet();
                    Iterator values = keySet.iterator();
                    cell_idx = 0;
                    while (values.hasNext()) {
                        Object value = map.get(values.next());
                        cell = row.createCell(cell_idx);
                        cell.setCellValue(value.toString());
                        cell_idx++;
                    }
                }
            }
            //String filename = encodingFilename(sheetName);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(sheetName, "UTF-8"));
//            response.setContentType("application/octet-stream");
//            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            //return filename;
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new RuntimeException("导出Excel失败，请联系网站管理员！");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
  
    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public void exportExcel(List<?> list, String sheetName, HttpServletResponse response) {
        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);
                }
            }
  
            // 产生工作薄对象
            workbook = new HSSFWorkbook();
            // 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(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
                        sheet.setColumnWidth(i, 6000);
                    } else {
                        HSSFFont font = workbook.createFont();
                        // 粗体显示
                        font.setBold(true);
                        // 选择需要用到的字体格式
//                        cellStyle.setFont(font);
                        //设置颜色
//                        cellStyle.setFillForegroundColor(HSSFColor.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(new SimpleDateFormat(dateFormat).format((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(field.get(vo) == null ? attr.defaultValue() : field.get(vo) + attr.suffix());
                                }
                            }
                        } catch (Exception e) {
                            log.error("导出Excel失败{}", e.getMessage());
                        }
                    }
                }
            }
            //String filename = encodingFilename(sheetName);
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName, "UTF-8"));
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new RuntimeException("导出Excel失败，请联系网站管理员！");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
  
    /**
     * 设置单元格上提示
     *
     * @param sheet         要设置的sheet.
     * @param promptTitle   标题
     * @param promptContent 内容
     * @param firstRow      开始行
     * @param endRow        结束行
     * @param firstCol      开始列
     * @param endCol        结束列
     * @return 设置好的sheet.
     */
    private static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow,
                                           int endRow, int firstCol, int endCol) {
        // 构造constraint对象
        DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
        dataValidationView.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(dataValidationView);
        return sheet;
    }
  
    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    private static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow,
                                               int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(dataValidationList);
        return sheet;
    }
  
    /**
     * 解析导出值 0=男,1=女,2=未知
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @return 解析后值
     * @throws Exception
     */
    private static String 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) {
            throw e;
        }
        return propertyValue;
    }
  
    /**
     * 编码文件名
     */
    /*private static String encodingFilename(String filename) {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xls";
        return filename;
    }*/
}

第四步：无论导入导出，Excel表格的第一行都是跟实体类上@Excel注解的name属性进行映射对应的。

以下是示例:导出Excel表（该方法最好是返回void或者返回null），否则后台会报错（不影响运行）。

@PostMapping("/export")
@ResponseBody
public void export()
{
    ExcelUtil<User> excelUtil = new ExcelUtil<>(User.class);
    //要导出的数据集
    List<User> list = getList();
    excelUtil.exportExcel(list, "userInformation.xlsx", ServletUtils.getResponse());
}