Excel导出

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)

posted @ 2019-07-22 16:51  啊龑是个大西瓜  阅读(205)  评论(0编辑  收藏  举报