在项目中导出Excel表格使用了easypoi,easypoi的底层原理是poi,所以如果觉得easypoi不好用的话,使用原始的原理也是可以的。
使用poi实现导出Excel表格
Jakarta POI HSSF API组件:
HSSF(用于操作Excel的组件)提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象,样式和格式,有以下几种常用的对象:
常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
样式:
HSSFCellStyle cell样式
一个Excel文件对应一个workbook,一个workbook中有多个sheet组成,一个sheet是由多个行(row)和列(cell)组成。
使用poi导出Excel表格基本步骤:
1.用HSSFWorkbook打开或者创建“Excel文件对象”
2.用HSSFWorkbook对象返回或者创建Sheet对象
3.用Sheet对象返回行对象,用行对象得到Cell对象
4.对Cell对象读写。
5.将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面
编写一个工具类
public class ExcelUtil {
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
}
控制器代码:
@Controller
@RequestMapping(value = "/report")
public class ReportFormController extends BaseController {
@Resource(name = "reportService")
private ReportManager reportService;
/**
* 导出报表
* @return
*/
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request,HttpServletResponse response) throws Exception {
//获取数据
List<PageData> list = reportService.bookList(page);
//excel标题
String[] title = {"名称","性别","年龄","学校","班级"};
//excel文件名
String fileName = "学生信息表"+System.currentTimeMillis()+".xls";
//sheet名
String sheetName = "学生信息表";
for (int i = 0; i < list.size(); i++) {
content[i] = new String[title.length];
PageData obj = list.get(i);
content[i][0] = obj.get("stuName").tostring();
content[i][1] = obj.get("stuSex").tostring();
content[i][2] = obj.get("stuAge").tostring();
content[i][3] = obj.get("stuSchoolName").tostring();
content[i][4] = obj.get("stuClassName").tostring();
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
前端页面代码:
<button id="js-export" type="button" class="btn btn-primary">导出Excel</button> $('#js-export').click(function(){ window.location.href="/report/exportBooksTable.do; });
使用easypoi实现导出Excel表格
1.在maven项目中导入jar包依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
2.创建实体类(一个简单的例子)
public class User {
@Excel(name = "id")
@NotBlank(message = "该字段不能为空")
private String id;
@Excel(name = "姓名")
@Pattern(regexp = "[\\u4E00-\\u9FA5]{2,5}", message = "姓名中文2-5位")
private String name;
@Max(value = 20)
@Excel(name = "年龄")
private Integer age;
@Excel(name = "生日", importFormat = "yyyy-MM-dd")
private Date birthday;
public User() {
}
public User(String id, String name, Integer age, Date birthday) {
super();
this.id = id;
this.name = name;
this.age = age;
this.birthday = birthday;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
3.编写controller
@Controller
public class ExcelExportController {
private static final Logger log = LoggerFactory.getLogger(ExcelExportController.class);
@GetMapping("export.do")
public void export(HttpServletResponse response) {
try {
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=user.xlsx");
// =========easypoi部分
ExportParams exportParams = new ExportParams();
// exportParams.setDataHanlder(null);//和导入一样可以设置一个handler来处理特殊数据
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, data());
workbook.write(response.getOutputStream());
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
/***
* 创建模拟数据
*
* @return
*/
private List<User> data() {
List<User> list = new ArrayList<>();
for (int i = 0; i < 20; i++) {
list.add(new User("id-" + i, "Leftso-" + i, 15 + i, new Date()));
}
return list;
}
}