今天老板要我添加一个功能,需求(用户点击页面导出按钮然后查询数据库并让显示下载Excle),在百度上参考许多代码,最后
1、由于我用的Maven,所以选择这样的方式导入(大家都懂滴)
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2、页面代码
//这是导出按钮
<button id="js-export" type="button" class="layui-btn btn btn-primary">导出Excel</button>
//JS请求方法,(注意请求地址填写自己的,后面的参数是条件查询要导出的数据,如果没有条件可以不加)
$('#js-export').click(function(){
window.location.href="/admin/signUserInfo/export?name=" + $("#name").val() + "&sex=" + $("#sex").val() + "&phone=" + $("#phone").val();
});
3、Util工具类
package com.huan.qycq.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
public class ExcelUtil {
/**
* description:生成一个excle表格,并填充数据
* @param sheetName
* @param title
* @param values
* @param wb
* @return:org.apache.poi.hssf.usermodel.HSSFWorkbook
* @since: 1.0.0
* @Author:zhongzewei
* @Date: 2019/3/25 14:23
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
// 第一步,创建一个webbook,对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
// 背景色
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.YELLOW.index);
// 设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
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;
}
/**
* 发送响应流方法
*/
public static 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();
}
}
}
4、实体类
//用的是mongodb数据库
@Data
@Document(collection ="sign_user_info")
public class SignUserInfo extends BaseEntity {
//用户code
private String userCode;
//用户名
private String name;
//电话
private String phone;
//地址
private String address;
//性别
private String sex;
//签到次数
private long count;
}
5、Controller层
@Controller
@RequestMapping("/admin/signUserInfo")
public class SignUserInfoController {
//依赖注入
@Autowired
private SignUserInfoService signUserInfoService;
/**
* 导出报表
* @return
*/
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "name", required = false) String name,
@RequestParam(value = "sex", required = false) String sex,
@RequestParam(value = "phone", required = false) String phone) throws Exception {
String fileName = "签到信息"+System.currentTimeMillis()+".xls"; //文件名
String sheetName = "签到信息";//sheet名
//Excle标题
String []title = new String[]{"用户code","用户名","电话","地址","性别","签到次数"};
//从数据库查询要导出的数据
List<SignUserInfo> list = signUserInfoService.querySignUserInfoList(name, sex, phone);
//声明一个二维数组
String [][]values = new String[list.size()][];
for(int i=0;i<list.size();i++){
values[i] = new String[title.length];
//将对象内容转换成string
SignUserInfo obj = list.get(i);
values[i][0] = obj.getUserCode();
values[i][1] = obj.getName();
values[i][2] = obj.getPhone();
values[i][3] = obj.getAddress();
values[i][4] = obj.getSex();
//由于这个属性是Long类型,+ ""转变为String
values[i][5] = obj.getCount() + "";
}
//调用Util里的方法,生成已个Excle
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);
//将文件存到指定位置
try {
//调用Util里的发送响应流方法
ExcelUtil.setResponseHeader(response, fileName);
ServletOutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
6、Service接口
public interface SignUserInfoService {
/**
* 查询要生成Excle的数据
* @param name
* @param sex
* @param phone
* @return
*/
List<SignUserInfo> querySignUserInfoList(String name, String sex, String phone);
}
7、Service接口实现类
@Service
public class SignUserInfoServiceImpl implements SignUserInfoService {
//依赖注入
@Autowired
private SignUserInfoDAO signUserInfoDAO;
@Override
public List<SignUserInfo> querySignUserInfoList(String name, String sex, String phone) {
return signUserInfoDAO.querySignUserInfoList(name, sex, phone);
}
}
8、Dao层查询数据库
//我用的是mongodb数据库查询
@Repository
public class SignUserInfoDAO extends BaseDAO<SignUserInfo> {
public List<SignUserInfo> querySignUserInfoList(String name, String sex, String phone) {
Query query = new Query();
if (!StringUtil.isBlank(name)) {
query.addCriteria(new Criteria().where("name").regex(".*?" + name + ".*"));
}
if (!StringUtil.isBlank(sex)) {
query.addCriteria(new Criteria().where("sex").is(sex));
}
if (!StringUtil.isBlank(phone)) {
query.addCriteria(new Criteria().where("phone").regex(".*?" + phone + ".*"));
}
return this.findList(query);
}
}
如果你们用的Mysql,正常查询返回数据就ok