一键导出excel功能（JAVA使用OPI实现excel导出）（SpringBoot）

E!RP系统浏览器端一键导出功能

1. pom添加坐标

<!--poi（导出excel）-->
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.9</version>
 </dependency>

2. html中添加导出标签

<a type="button" class="btn btn-default" href="/common/export">导出</a>

3. resources可添加excel模板
   
   4. 编写controller代码

package com.sierte.phone.controller;

import com.sierte.phone.pojo.PMain;
import com.sierte.phone.service.PMainService;
import com.sierte.phone.util.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.tomcat.util.http.ResponseUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.ClassUtils;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@Controller
@RequestMapping("common")
public class CommonController {

    @Autowired
    private PMainService pMainService;

    /**
     * 导出至excel
     * @return
     */
    @RequestMapping("export")
    public String exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {

        List<PMain> pMainList = pMainService.findAllPMain();

        /*String webPath=request.getServletContext().getRealPath("/");*/
        String path = ClassUtils.getDefaultClassLoader().getResource("static/client_down_model.xls").getPath();

        Workbook wb = fillExcelDataWithTemplate(pMainList, path);

        export(response,wb,"手机信息.xls");
        return null;
    }

    /**
     * @param templateFileUrl
     *            excel模板的路径 /admin/page/JYZ/client/client_info.xls
     * @return
     */
    public static Workbook fillExcelDataWithTemplate(List<PMain> list ,String templateFileUrl) {

        Workbook wb = null ;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileUrl));
            wb = new HSSFWorkbook(fs);
            // 取得 模板的 第一个sheet 页
            Sheet sheet = wb.getSheetAt(0);
            // 拿到sheet页有 多少列
            int cellNums = sheet.getRow(0).getLastCellNum();
            // 从第2行 开搞    下标1  就是第2行
            int rowIndex = 1;
            Row row ;
            for(PMain pMain : list){
                row = sheet.createRow(rowIndex);
                rowIndex ++;
                row.createCell(0).setCellValue(pMain.getId());
                row.createCell(1).setCellValue(pMain.getPhone());
                row.createCell(2).setCellValue(pMain.getName());
                row.createCell(3).setCellValue(pMain.getPosition1());
                row.createCell(4).setCellValue(pMain.getPosition2());
                row.createCell(5).setCellValue(pMain.getAccount());
                row.createCell(6).setCellValue(pMain.getChange_count());
                row.createCell(7).setCellValue(pMain.getUpdate_count());
                row.createCell(8).setCellValue(DateUtil.formatDate(pMain.getStart_time(), "yyyy-MM-dd"));
                row.createCell(9).setCellValue(DateUtil.formatDate(pMain.getEnd_time(), "yyyy-MM-dd"));
                row.createCell(10).setCellValue(DateUtil.formatDate(pMain.getChange_time(), "yyyy-MM-dd"));
                row.createCell(11).setCellValue(pMain.getExplain());
                row.createCell(12).setCellValue(pMain.getImei());
                if (pMain.getStatus().equals("0")) {
                    row.createCell(13).setCellValue("闲置");
                } else if (pMain.getStatus().equals("1")) {
                    row.createCell(13).setCellValue("在用");
                } else if (pMain.getStatus().equals("2")) {
                    row.createCell(13).setCellValue("异常");
                } else if (pMain.getStatus().equals("3")) {
                    row.createCell(13).setCellValue("注销");
                }

                /*row.createCell(1).setCellValue(client.getBianhao());
                row.createCell(2).setCellValue(client.getName());
                row.createCell(3).setCellValue(client.getPhone());
                row.createCell(4).setCellValue(client.getRemark());
                row.createCell(5).setCellValue(DateUtil.formatDate(client.getCreateDateTime(), "yyyy-MM-dd HH:mm:ss"));
                */
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 导出
     * @param response
     * @param wb
     * @param fileName
     * @throws Exception
     */
    public  void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }
}

5. 日期格式化工具类

package com.sierte.phone.util;

import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtil {

    /**
     * yyyyMMdd hhmmssSSS
     * 日期对象转字符串
     */
    public static String formatDate(Date date, String format){
        String result="";
        SimpleDateFormat sdf=new SimpleDateFormat(format);
        if(date!=null){
            result=sdf.format(date);
        }
        return result;
    }
}