用的是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();
}
}
}
}
网友评论