1、加上需要的依赖：
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
2、创建工具类ExcelUtil(可直接复制)
package com.luo.config;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
@Component
public class ExeclUtil {
private static String excelPath;
/* @Value("${excelPath}")
public void setExcelPath(String excelPath) {
ExeclUtil.excelPath = excelPath;
}*/
public static void download(HttpServletRequest request, HttpServletResponse response, String title, Workbook workbook) throws IOException {
String path = excelPath + "/ExcelData//" + title + ".xls";
File file = new File(path);
//判断目标文件所在的目录是否存在
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
FileOutputStream fos = new FileOutputStream(new File(path));
workbook.write(fos);
workbook.close();
fos.close();
response.setHeader("content-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + title + ".xls");
byte[] buff = new byte[1024];
BufferedInputStream bis = null;
OutputStream os = null;
try {
os = response.getOutputStream();
bis = new BufferedInputStream(new FileInputStream(new File(path)));
int i = bis.read(buff);
while (i != -1) {
os.write(buff, 0, buff.length);
os.flush();
i = bis.read(buff);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
System.out.println("success");
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
System.out.println(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
System.out.println("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
System.out.println("excel文件不能为空");
} catch (Exception e) {
System.out.println(e.getMessage());
}
return list;
}
/**
* 处理单元格格式的简单方式
*
* @param hssfCell
* @return
*/
public static String formatCell(Cell hssfCell) {
if (hssfCell == null) {
return "";
} else {
if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
/**
* 处理单元格格式的第二种方式: 包括如何对单元格内容是日期的处理
*
* @param cell
* @return
*/
public static String formatCell2(HSSFCell cell) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
//针对单元格式为日期格式
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return String.valueOf(cell.getNumericCellValue());
} else {
return cell.getStringCellValue();
}
}
/**
* 处理单元格格式的第三种方法:比较全面
*
* @param cell
* @return
*/
public static String formatCell3(HSSFCell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
//日期格式的处理
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return String.valueOf(cell.getNumericCellValue());
//字符串
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
// 公式
case HSSFCell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
// 空白
case HSSFCell.CELL_TYPE_BLANK:
return "";
// 布尔取值
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() + "";
//错误类型
case HSSFCell.CELL_TYPE_ERROR:
return cell.getErrorCellValue() + "";
}
return "";
}
}
3、创建实体类
package com.luo.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import java.util.Date;
@ExcelTarget("news")
public class News {
@Excel(name="编号",orderNum="1",width = 18)
private String id;//编号
@Excel(name="标题",orderNum="2",width = 18)
private String title;//标题
@Excel(name="作者",orderNum="3",width = 18)
private String author;//作者
@Excel(name="发布人",orderNum="4",width = 18)
private String person;//发布人
private String showDate;//发布时间
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getPerson() {
return person;
}
public void setPerson(String person) {
this.person = person;
}
public String getShowDate() {
return showDate;
}
public void setShowDate(String showDate) {
this.showDate = showDate;
}
@Override
public String toString() {
return "News{" +
"id='" + id + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", person='" + person + '\'' +
", showDate=" + showDate +
'}';
}
}
4、Controller层
@RequestMapping("/exportExcel")
@ResponseBody
public void exportExcel(HttpServletResponse response){
List<News> list=newsService.getDomesticNews();
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("列表", "列表"),
News.class, list);
ExeclUtil.downLoadExcel("taskList.xls", response, workbook);
}
}
5、前端页面
<a class="btn btn-default" href="/exportExcel"><span class=" glyphicon glyphicon-download-alt l_left"></span>导出</a>
注意：直接请求URL即可，不要用ajax请求！
