springmvc实现导出数据信息为excle表格示例代码
1.项目增加导出日志信息
2.项目中导入poi-*.jar等操作excel文件的jar文件
- poi-3.7-20120326.jar
- poi-excelant-3.7-20101029.jar
- poi-ooxml-3.7.jar
- poi-ooxml-schemas-3.7.jar
Excel导出就是根据前台条件将参数传到controller，根据参数去数据库中进行查询，查询出list集合，将list集合生成excle数据下载。
代码片段：
Contorller.Java
/**
*导出信息
*@parammodel
*/
@RequestMapping("exportCustomer.do")
@SystemControllerLog(description="数据库表单导出Excle")
publicvoidexportCustomer(ModelMapmodel){
//TODO如需添加条件
//model.addAttribute("username",nameStr);
//获取需要导出的数据List
List<CMcustomer>cusList=customerService.exportCustomer(model);
//使用方法生成excle模板样式
HSSFWorkbookworkbook=customerService.createExcel(cusList,request);
SimpleDateFormatformat=newSimpleDateFormat("yyyyMMddHHmmss");//定义文件名格式
try{
//定义excle名称ISO-8859-1防止名称乱码
Stringmsg=newString(
("客户信息_"+format.format(newDate())+".xls").getBytes(),
"ISO-8859-1");
//以导出时间作为文件名
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition","attachment;filename="
+msg);
workbook.write(response.getOutputStream());
}catch(IOExceptione){
logger.error(e);
}
}
2.Service中createExcel方法
publicHSSFWorkbookcreateExcel(List<CMcustomer>cusList,
HttpServletRequestrequest){
//创建一个webbook，对应一个excel文件
HSSFWorkbookworkbook=newHSSFWorkbook();
//在webbook中添加一个sheet,对应excel文件中的sheet
HSSFSheetsheet=workbook.createSheet("客户信息表");
//设置列宽
sheet.setColumnWidth(0,25*100);
sheet.setColumnWidth(1,35*100);
sheet.setColumnWidth(2,35*100);
sheet.setColumnWidth(3,40*100);
sheet.setColumnWidth(4,45*100);
sheet.setColumnWidth(5,45*100);
sheet.setColumnWidth(6,50*100);
sheet.setColumnWidth(7,80*100);
sheet.setColumnWidth(8,35*100);
sheet.setColumnWidth(9,40*100);
//在sheet中添加表头第0行
HSSFRowrow=sheet.createRow(0);
//创建单元格，并设置表头，设置表头居中
HSSFCellStylestyle=workbook.createCellStyle();
//创建一个居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//带边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//生成一个字体
HSSFFontfont=workbook.createFont();
//字体增粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//字体大小
font.setFontHeightInPoints((short)12);
//把字体应用到当前的样式
style.setFont(font);
//单独设置整列居中或居左
HSSFCellStylestyle1=workbook.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStylestyle2=workbook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFCellStylestyle3=workbook.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFonthssfFont=workbook.createFont();
hssfFont.setColor(HSSFFont.COLOR_RED);
hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style3.setFont(hssfFont);
HSSFCellStylestyle4=workbook.createCellStyle();
style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFonthssfFont1=workbook.createFont();
hssfFont1.setColor(HSSFFont.COLOR_NORMAL);
hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style4.setFont(hssfFont1);
HSSFCellcell=row.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell=row.createCell(1);
cell.setCellValue("客户姓名");
cell.setCellStyle(style);
cell=row.createCell(2);
cell.setCellValue("性别");
cell.setCellStyle(style);
cell=row.createCell(3);
cell.setCellValue("状态");
cell.setCellStyle(style);
cell=row.createCell(4);
cell.setCellValue("电话");
cell.setCellStyle(style);
cell=row.createCell(5);
cell.setCellValue("邮箱");
cell.setCellStyle(style);
cell=row.createCell(6);
cell.setCellValue("地址");
cell.setCellStyle(style);
for(inti=0;i<cusList.size();i++){
StringlogTypeDis="";
row=sheet.createRow(i+1);
CMcustomercMcustomer=cusList.get(i);
//创建单元格，并设置值
//编号列居左
HSSFCellc1=row.createCell(0);
c1.setCellStyle(style2);
c1.setCellValue(i);
HSSFCellc2=row.createCell(1);
c2.setCellStyle(style1);
c2.setCellValue(cMcustomer.getCustomername());//客户姓名
StringsexStr=cMcustomer.getSex();//性别0:女,1:男
Stringsex="";
if("1".equals(sexStr)){
sex="男";
}
if("0".equals(sexStr)){
sex="女";
}
HSSFCellc3=row.createCell(2);//性别
c3.setCellStyle(style1);
c3.setCellValue(sex);
StringstatusStr=cMcustomer.getStatus();//客户状态1.在职，2.离职
Stringstatus="";
if("1".equals(statusStr)){
status="在职";
}
if("2".equals(statusStr)){
status="离职";
}
HSSFCellc4=row.createCell(3);//状态
c4.setCellStyle(style1);
c4.setCellValue(status);
Stringcustomerid=cMcustomer.getCustomerid();//客户id
List<CMphone>phoneList=cMphoneMapper.selectByCustomerid(customerid);
Stringphone="";
if(phoneList!=null&&phoneList.size()>0){
for(intj=0;j<phoneList.size();j++){
phone=phoneList.get(j).getPhone();
}
}
HSSFCellc5=row.createCell(4);//电话
c5.setCellStyle(style1);
c5.setCellValue(phone);
List<CMemail>emailList=cMemailMapper.selectAll(customerid);
Stringemail="";
if(emailList!=null&&emailList.size()>0){
for(intj=0;j<emailList.size();j++){
email=emailList.get(j).getEmail();
}
}
HSSFCellc6=row.createCell(5);//邮箱
c6.setCellStyle(style1);
c6.setCellValue(email);
CMaddresscMaddress=newCMaddress();
cMaddress.setCustomerid(customerid);
List<CMaddress>adderssList=cMaddressMapper.selectAll(cMaddress);
Stringadderss="";
if(adderssList!=null&&adderssList.size()>0){
for(intj=0;j<adderssList.size();j++){
adderss=adderssList.get(j).getAddress();
}
}
HSSFCellc7=row.createCell(6);//地址
c7.setCellStyle(style1);
c7.setCellValue(adderss);
//使用默认格式
row.createCell(1).setCellValue(cMcustomer.getCustomername());
row.createCell(2).setCellValue(sex);
row.createCell(3).setCellValue(status);
row.createCell(4).setCellValue(phone);
row.createCell(5).setCellValue(email);
row.createCell(6).setCellValue(adderss);
}
returnworkbook;
}
3.页面jsp调用
//导出信息
functionexporBtn(){
$.ajax({
type:"POST",
url:"<%=path%>/customer/exportCustomer.do",
success:function(data){
window.open('<%=path%>/customer/exportCustomer.do');
}
});
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。