Service层
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public interface ExportDataService {
void exportEmployee(HttpServletRequest request, HttpServletResponse response);//导出EXCEL
}
实现层
import com.kude.mapper.EmployeeMapper;
import com.kude.mapper.MemberMapper;
import com.kude.mapper.ShopMapper;
import com.kude.pojo.Employee;
import com.kude.pojo.EmployeeExample;
import com.kude.service.ExportDataService;
import com.kude.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
/**
* @author szj
* @date 2019/7/22 15:04
*/
@Service
public class ExportDataServiceImpl implements ExportDataService {
@Autowired
private MemberMapper memberMapper;
@Autowired
private EmployeeMapper employeeMapper;
@Autowired
private ShopMapper shopMapper;
/**
* 数据导出EXcl表格实现
* @param request
* @param response
*/
public void exportEmployee(HttpServletRequest request, HttpServletResponse response) {
//获取数据
EmployeeExample employeeExample = new EmployeeExample();//example 用来查询的封装类 example创建createCriteria 通过criteria设置查询条件
EmployeeExample.Criteria memcriteria = employeeExample.createCriteria();//
List<Employee> employees= employeeMapper.selectByExample(employeeExample);
//excel标题
String[] title = {"id","店铺id","员工姓名","性别","出生日期","身份证号","联系电话","角色id","入职日期","在职状态","家庭住址","所属店铺","登录密码","最后登录时间"};
//excel文件名
String fileName = "员工信息.xls";
//sheet名
String sheetName = "员工信息";
String[][] content=new String[employees.size()][title.length];
for (int i = 0; i < employees.size(); i++) {
content[i][0] = employees.get(i).getId().toString();//id
content[i][1] = employees.get(i).getShopId().toString();//店铺id
content[i][2] = employees.get(i).getName();//员工姓名
content[i][3] = employees.get(i).getSex();//性别
content[i][4] = employees.get(i).getBirthday();//出生日期
content[i][5] = employees.get(i).getIdnumber();//身份证号
content[i][6] = employees.get(i).getTel();//联系电话
content[i][7] = employees.get(i).getRoleId().toString();//角色id
content[i][8] = employees.get(i).getEntyTime();//入职日期
content[i][9] = employees.get(i).getStatus().toString();//在职状态
content[i][10] = employees.get(i).getAddress();//家庭住址
content[i][11] = employees.get(i).getShop();//所属店铺
content[i][12] = employees.get(i).getPassword();//登录密码
content[i][13] = employees.get(i).getLastloginTime();//最后登录时间
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
//响应到客户端
try {
ExcelUtil.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
}catch (Exception e) {
e.printStackTrace();
}
}
控制层
import com.kude.common.ServerResponse;
import com.kude.service.ExportDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* @author szj
* @date 2019/7/22 15:02
*/
@Controller
@RequestMapping("/export")
public class ExportDateController {
@Autowired
private ExportDataService exportDataService;
/**
* 导出员工报表
* @return
*/
@RequestMapping("/export_employee.do")
@ResponseBody
public ServerResponse<String> exportEmployee(HttpServletRequest request, HttpServletResponse response) throws Exception {
response.setHeader("Access-Control-Allow-Origin","*");
exportDataService.exportEmployee(request, response);
return ServerResponse.createBySuccessMessage("数据导出成功");
}
}
工具类
package com.kude.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
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(HorizontalAlignment.CENTER_SELECTION);
; // 创建一个居中格式
//声明列对象
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("utf-8"), "ISO-8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
（注：文章摘自：https://www.cnblogs.com/newbest/p/9973593.html）