4.java项目页面导出excel功能

用的是SSM框架,字段根据自己的业务需求改

1.前台页面

 <a  class="btn btn btn-primary"  onclick=downloadExcel() style=" margin:15px 0px 0px 55px">导出</a>

<form action="sourceRecord/downloadExcel" id="dynamicDownload" method="post">
        <input type="hidden" value="${date_start }" id="inpstart" name="inpstart">
        <input type="hidden" value="${date_end }" id="inpend" name="inpend">
        <input type="hidden" value="${userORname }" id="userORname" name="userORname">
        <input type="hidden" value="${PART_NAME }" id="dId" name="dId">
</form>

  /*导出按钮提交*/
  function downloadExcel(){
      $("#dynamicDownload").submit();
  }

2.后台相关代码

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

    /**
     * 导出考勤记录
     * @param request
     * @param response
     * @param model
     */
@RequestMapping(value="downloadExcel")
public void downLoadExcel(HttpServletRequest request,HttpServletResponse response,Model model){
      try{
         //1-查出要导出的数据
        String user_id = request.getSession().getAttribute("user_id").toString();//获取保存登陆信息的员工id
        HashMap<String, Object> paramMap = new HashMap<String, Object>();
        String date_start1 = request.getParameter("inpstart");
    String date_end1 = request.getParameter("inpend");
    String userORname1 = request.getParameter("userORname");
    String PART_NAME1 = request.getParameter("dId");
    paramMap.put("date_start", date_start1);
        paramMap.put("date_end", date_end1);
    paramMap.put("userORname", userORname1);
    paramMap.put("PART_NAME", PART_NAME1);
    List<Map<String, Object>> sourceRecordList=sourceRecordService.selectSourceRecordNotpage(paramMap);
         //第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wk = new HSSFWorkbook();
        //第二步,创建一个sheet表对象,创建row对象，getExcelStyle1是一个创建模板的方法,最后面有
    HSSFSheet sheet = getExcelStyle1(wk,"考勤信息表");
        HSSFRow row;
    HSSFCellStyle style = wk.createCellStyle();
    HSSFFont font = wk.createFont();
    font.setFontName("微软雅黑");
    font.setFontHeightInPoints((short)15);
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //第三步,查询出表内容放到map中
    SimpleDateFormat sdf_out = new SimpleDateFormat("yyyyMMdd");
    String time_out = sdf_out.format(new Date());
    String srcPath=request.getSession().getServletContext().getRealPath("")+"/考勤信息"+time_out+".xls";//设置将excel数据上传至服务器的路径
         for(int i=0;i<sourceRecordList.size();i++){
                row=sheet.createRow(i+1);//从第二行还是导入数据
                String USER_ID = sourceRecordList.get(i).get("USER_ID").toString();//查询列表中获取所需字段数据
                String USER_NAME = sourceRecordList.get(i).get("USER_NAME").toString();
                String PART_NAME = sourceRecordList.get(i).get("PART_NAME").toString();
                String ATTENDANCETEAM_NAME = sourceRecordList.get(i).get("ATTENDANCETEAM_NAME").toString();
                String POST_NAME = sourceRecordList.get(i).get("POST_NAME").toString();
                String ATTENDANCE_DATE = sourceRecordList.get(i).get("ATTENDANCE_DATE").toString();
                String ATTENDANCE_TIME = sourceRecordList.get(i).get("ATTENDANCE_TIME").toString();
                String EQUIPMENT_ADDRESS = sourceRecordList.get(i).get("EQUIPMENT_ADDRESS").toString();
                
                if(USER_ID !=null){
                    row.createCell((short)0).setCellValue(USER_ID);
                }else{
                    row.createCell((short)0).setCellValue("");
                }
                if(USER_NAME !=null){
                    row.createCell((short)1).setCellValue(USER_NAME);
                }else{
                    row.createCell((short)1).setCellValue("");
                }
                if(PART_NAME !=null){
                    row.createCell((short)2).setCellValue(PART_NAME);
                }else{
                    row.createCell((short)2).setCellValue("");
                }
                if(ATTENDANCETEAM_NAME !=null){
                    row.createCell((short)3).setCellValue(ATTENDANCETEAM_NAME);
                }else{
                    row.createCell((short)3).setCellValue("");
                }
                if(POST_NAME !=null){
                    row.createCell((short)4).setCellValue(POST_NAME);
                }else{
                    row.createCell((short)4).setCellValue("");
                }
                if(ATTENDANCE_DATE !=null){
                    row.createCell((short)5).setCellValue(ATTENDANCE_DATE);
                }else{
                    row.createCell((short)5).setCellValue("");
                }
                if(ATTENDANCE_TIME !=null){
                    row.createCell((short)6).setCellValue(ATTENDANCE_TIME);
                }else{
                    row.createCell((short)6).setCellValue("");
                }
                if(EQUIPMENT_ADDRESS !=null){
                    row.createCell((short)7).setCellValue(EQUIPMENT_ADDRESS);
                }else{
                    row.createCell((short)7).setCellValue("");
                }
            }
            try {
                FileOutputStream fout = new FileOutputStream(srcPath);//创建一个服务器管道地址
                wk.write(fout);//把excel数据写到服务器中
                //读服务器中的数据
                File exportFile = new File(srcPath);
                FileInputStream fs=null;
                //告诉浏览器这次请求是一个下载的数据流
                response.setContentType("APPLICATION/OCTET-STREAM");
                fs=new FileInputStream(exportFile);
                                //ExportUtil是一个下载的工具类,后面会有
                ExportUtil.download(response, fs, "考勤信息-"+time_out+".xls",null);
            } catch (Exception e) {
                e.printStackTrace();
            }
            
            
            
        }catch(Exception e){
            e.printStackTrace();
            
        }
        
        
    }
}

    /**
     * 创建一个excel模板:里面有表头信息
     */
    public HSSFSheet getExcelStyle1(HSSFWorkbook wk,String name){
        HSSFSheet sheet = wk.createSheet(name);
        //设置表的样式 新加一行
        HSSFRow row1 = sheet.createRow((int) 0);
        HSSFCellStyle style1 = wk.createCellStyle();
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font1 = wk.createFont();
        font1.setFontName("微软雅黑");
        font1.setFontHeightInPoints((short)12);
        style1.setFont(font1);
        row1.createCell((short)0).setCellValue("工号");
        row1.createCell((short)1).setCellValue("姓名");
        row1.createCell((short)2).setCellValue("部门");
        row1.createCell((short)3).setCellValue("所属考勤组");
        row1.createCell((short)4).setCellValue("岗位名称");
        row1.createCell((short)5).setCellValue("日期");
        row1.createCell((short)6).setCellValue("打卡时间");
        row1.createCell((short)7).setCellValue("设备地址");
        return sheet;
    }

ExportUtil 下载的工具类

    import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;

public class ExportUtil {

    /**
     * 导出文件
     * 
     * @param response
     * @param file
     *            导出文件
     * @param name
     * @param contentType
     * @throws IOException
     */
    public static void download(HttpServletResponse response, File file,
            String name, String contentType) throws IOException {
        String fileName = StringUtils.isBlank(name) ? file.getName() : name;
        download(response, new FileInputStream(file), fileName, contentType);
    }

    /**
     * 下载数据/文件
     * 
     * @param response
     *            HTTP输出
     * @param inputStream
     *            文件流
     * @param fileName
     *            文件名
     * @param contentType
     *            ContentType in HTTP Header
     * @throws IOException
     *             IO异常
     */
    public static void download(HttpServletResponse response,
            InputStream inputStream, String fileName, String contentType)
            throws IOException {

        response.setContentType(StringUtils.isEmpty(contentType) ? "application/octet-stream"
                : contentType);
        response.setHeader("Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes("gbk"), "ISO-8859-1"));
        response.setStatus(HttpServletResponse.SC_OK);

        BufferedInputStream reader = null;
        try {
            reader = new BufferedInputStream(inputStream);
            IOUtils.copy(reader, response.getOutputStream());
        } finally {
            if (reader != null) {
                reader.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
}