springmvc实现导出数据信息为excle表格示例代码

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

/**

* 导出信息

* @param model

*/

@RequestMapping("exportCustomer.do")

@SystemControllerLog(description = "数据库表单导出Excle")

public void exportCustomer(ModelMap model) {

//TODO 如需添加条件

//model.addAttribute("username", nameStr);

//获取需要导出的数据List

List cusList=customerService.exportCustomer(model);

//使用方法生成excle模板样式

HSSFWorkbook workbook = customerService.createExcel(cusList, request);

SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式

try {

//定义excle名称 ISO-8859-1防止名称乱码

String msg = new String(

("客户信息_" + format.format(new Date()) + ".xls").getBytes(),

"ISO-8859-1");

// 以导出时间作为文件名

response.setContentType("application/vnd.ms-excel");

response.addHeader("Content-Disposition", "attachment;filename="

+ msg);

workbook.write(response.getOutputStream());

} catch (IOException e) {

logger.error(e);

}

}

2.Service中createExcel方法

public HSSFWorkbook createExcel(List cusList,

HttpServletRequest request) {

// 创建一个webbook，对应一个excel文件

HSSFWorkbook workbook = new HSSFWorkbook();

// 在webbook中添加一个sheet,对应excel文件中的sheet

HSSFSheet sheet = 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行

HSSFRow row = sheet.createRow(0);

// 创建单元格，并设置表头，设置表头居中

HSSFCellStyle style = workbook.createCellStyle();

// 创建一个居中格式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 带边框

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

// 生成一个字体

HSSFFont font = workbook.createFont();

// 字体增粗

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 字体大小

font.setFontHeightInPoints((short) 12);

// 把字体应用到当前的样式

style.setFont(font);

// 单独设置整列居中或居左

HSSFCellStyle style1 = workbook.createCellStyle();

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFCellStyle style2 = workbook.createCellStyle();

style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

HSSFCellStyle style3 = workbook.createCellStyle();

style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);

HSSFFont hssfFont = workbook.createFont();

hssfFont.setColor(HSSFFont.COLOR_RED);

hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

style3.setFont(hssfFont);

HSSFCellStyle style4 = workbook.createCellStyle();

style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);

HSSFFont hssfFont1 = workbook.createFont();

hssfFont1.setColor(HSSFFont.COLOR_NORMAL);

hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

style4.setFont(hssfFont1);

HSSFCell cell = row.createCell(0);

cell.setCellValue("序号");

cell.setCellStyle(style);

cell = row.creatbdBnEWHjGeCell(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 (int i = 0; i < cusList.size(); i++) {

String logTypeDis = "";

row = sheet.createRow(i + 1);

CMcustomer cMcustomer = cusList.get(i);

// 创建单元格，并设置值

// 编号列居左

HSSFCell c1 = row.createCell(0);

c1.setCellStyle(style2);

c1.setCellValue(i);

HSSFCell c2 = row.createCell(1);

c2.setCellStyle(style1);

c2.setCellValue(cMcustomer.getCustomername());//客户姓名

String sexStr = cMcustomer.getSex();//性别 0:女,1:男

String sex="";

if ("1".equals(sexStr)) {

sex="男";

}

if ("0".equals(sexStr)) {

sex="女";

}

HSSFCell c3 = row.createCell(2);//性别

c3.setCellStyle(style1);

c3.setCellValue(sex);

String statusStr = cMcustomer.getStatus();//客户状态1.在职，2.离职

String status="";

if ("1".equals(statusStr)) {

status="在职";

}

if ("2".equals(statusStr)) {

status="离职";

}

HSSFCell c4 = row.createCell(3);//状态

c4.setCellStyle(style1);

c4.setCellValue(status);

String customerid = cMcustomer.getCustomerid();//客户id

List phoneList = cMphoneMapper.selectByCustomerid(customerid);

String phone="";

if (phoneList!=null&&phoneList.size()>0) {

for (int j = 0; j < phoneList.size(); j++) {

phone = phoneList.get(j).getPhone();

}

}

HSSFCell c5 = row.createCell(4);//电话

c5.setCellStyle(style1);

c5.setCellValue(phone);

List emailList = cMemailMapper.selectAll(customerid);

String email="";

if (emailList!=null&&emailList.size()>0) {

for (int j = 0; j < emailList.size(); j++) {

email = emailList.get(j).getEmail();

bdBnEWHjG }

}

HSSFCell c6 = row.createCell(5);//邮箱

c6.setCellStyle(style1);

c6.setCellValue(email);

CMaddress cMaddress=new CMaddress();

cMaddress.setCustomerid(customerid);

List adderssList = cMaddressMapper.selectAll(cMaddress);

String adderss="";

if (adderssList!=null&&adderssList.size()>0) {

for (int j = 0; j < adderssList.size(); j++) {

adderss = adderssList.get(j).getAddress();

}

}

HSSFCell c7 = 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).setCellbdBnEWHjGValue(phone);

row.createCell(5).setCellValue(email);

row.createCell(6).setCellValue(adderss);

}

return workbook;

}

3.页面jsp调用

//导出信息

function exporBtn(){

$.ajax({

type:"POST",

url:"<%=path%>/customer/exportCustomer.do",

success:function(data){

window.open('<%=path%>/customer/exportCustomer.do');

}

});

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。