最近写了一个spring，springmvc，hibernate，jsp的项目。后来想着在前端页面添加一个导出功能，所以就上网查了资料，实现了Excel导出功能，这里记录下自己的过程。
1、在java项目中引入导出功能需要的jar包
poi-3.9.jar
poi-examples-3.9.jar
poi-excelant-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
poi-scratchpad-3.9.jar
项目中使用的是maven结构，所以在pom.xml文件中添加如下部分：
2、要获取的产品实体类-HMproductModel .java
package com.cpic.caf.template.home.repository.model;
import java.math.BigDecimal;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class HMproductModel {
@Id
private String product_id; //
private String productcode; //
private String product_name; // '',
private String company_code; // '
private String risk_type; // '
private String ins_period_type; // '
private Integer ins_period; // '
private String payments; // '
private String insert_ope; //
private String modify_ope; //
private String check_flag; //
private String check_reason; // '
get方法
set方法
3、生成Excel文件的工具类-ExcelUtil.java
package com.lin.utils; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * 生成Excel文件的工具类 * @author libo */ public class ExcelUtil { /** * 创建excel文档， * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 * */ public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) { // 创建excel工作簿 SXSSFWorkbook wb = new SXSSFWorkbook(100);//在内存中只保留100行记录,超过100就将之前的存储到磁盘里 // 创建第一个sheet（页），并命名 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for(int i=0;i<keys.length;i++){ sheet.setColumnWidth(i, (int) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow(0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式（用于列名） f.setFontHeightInPoints((short)10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式（用于值） f2.setFontHeightInPoints((short)10); f2.setColor(IndexedColors.BLACK.getIndex()); // 设置第一种单元格的样式（用于列名） cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式（用于值） cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for(int i=0;i<columnNames.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (int i = 1; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行，在页sheet上 Row row1 = sheet.createRow(i); // 在row行上创建一个方格 for(int j=0;j<keys.length;j++){ Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; } }
4、controller层-ProductController .java
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.cpic.caf.template.home.repository.model.HMproductModel;
import com.cpic.caf.template.home.service.batch.ManageProductService;
import com.cpic.caf.template.home.util.BankEnum;
import com.cpic.caf.template.home.util.ExcelUtil;
import com.cpic.caf.template.home.util.ProductEnum;
import com.cpic.caf.template.home.util.TypeEnum;
/**
* 后台用户-controller
* @author libo
*/
@Controller
@RequestMapping("/batch/exportproduct")
public class ProductController {
@Autowired
private ManageProductService manageProductService;
/*@Autowired
private TBatchCpicService tbatchCpicService;
@Autowired
private TBatchSetupService tbatchSetupService;*/
@InitBinder
public void initBinder(WebDataBinder binder) {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
dateFormat.setLenient(true);
binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
}
/*@Resource
private SysUserService sysUserService;
@Resource
private RoleService rService;*/
/* @Value(value="${headImgPath}") //后台图片保存地址
private String headImgPath;
@Value(value="${uploadHost}")
private String uploadHost; //项目host路径
@Value(value="${sysUserDefImg}")
private String sysUserDefImg; //系统用户默认头像
*//**
* 导出系统用户数据
* @param req
* @param res
* @param name
* @param phone
* @param email
* @param roleId
* @param createTimeStart
* @param createTimeEnd
* @param status
* @param departmentId
* @throws IOException
*/
/* @RequestMapping(value="/get")
@ResponseBody
public PageUtil<HMproductModel> query(
HttpServletRequest req,HttpServletResponse res,String company_code,String product_id,String risk_type,String check_flag,int current) {
return this.manageProductService.getByPage(company_code, product_id, risk_type,check_flag,current);
}*/
@ResponseBody
@RequestMapping(value="/exportproduct",produces="application/json;charset=utf-8", method=RequestMethod.GET)
public void exportSysUsers(HttpServletRequest req,HttpServletResponse res,String company_code,String product_id,String risk_type,String check_flag) throws IOException{
Map<String,Object> params = new HashMap<String,Object>();
/* params.put("name", "".equals(name) || null == name ? null : name);
params.put("phone", "".equals(phone) || null == phone ? null : phone);
params.put("email", "".equals(email) || null == email ? null : email);
params.put("roleId", "".equals(roleId) || null == roleId ? null : roleId);*/
/* params.put("createTimeStart", "".equals(createTimeStart) || null == createTimeStart ? null : createTimeStart+" 00:00:00");
params.put("createTimeEnd", "".equals(createTimeEnd) || null == createTimeEnd ? null : createTimeEnd+" 23:59:59");*/
/* HttpServletRequest req;
HttpServletResponse res = null;*/
System.out.println("company_code======"+company_code);
System.out.println("product_id======"+product_id);
System.out.println("risk_type======"+risk_type);
System.out.println("check_flag======"+check_flag);
Date d = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String formatTime = sdf.format(d);
String fileName="产品信息表-"+formatTime;
//填充projects数据 this.manageProductService.getByPage(company_code, product_id, risk_type,check_flag,current)
// List<HMproductModel> userList = sysUserService.getUserList(params);
List<HMproductModel> userList = this.manageProductService.export(company_code, product_id, risk_type,check_flag);
List<Map<String,Object>> list=createExcelRecord(userList);
String columnNames[]={"好买产品代码","产品名称","保险产品代码","保险公司名称","产品类型","保险期间类型","保险期限","支付方式","创建人","最后修改人","审核状态","不通过原因"};//列名
String keys[] = {"product_id", "product_name", "productcode", "company_code", "risk_type", "ins_period_type", "ins_period", "payments", "insert_ope"
, "modify_ope", "check_flag", "check_reason"};//map中的key
/* for(i=0;i<data.length;i++){
content+=getTableRow(data[i].product_id,data[i].product_name,data[i].productcode,data[i].company_code,
data[i].risk_type,data[i].ins_period_type,data[i].ins_period,data[i].payments,
data[i].insert_ope,data[i].modify_ope,data[i].check_flag,data[i].check_reason);
}*/
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = res.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
/**
* 生成Excel数据
* @param userList
* @return
*/
private List<Map<String, Object>> createExcelRecord(List<HMproductModel> userList) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listmap.add(map);
HMproductModel model = null;
for (int j = 0; j < userList.size(); j++) {
model = userList.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
mapValue.put("product_id", model.getProduct_id());
mapValue.put("product_name", model.getProduct_name());
mapValue.put("productcode", model.getProductcode());
mapValue.put("company_code",ProductEnum.getName(model.getCompany_code()));
mapValue.put("risk_type", ProductEnum.getName(model.getRisk_type()));
mapValue.put("ins_period_type", TypeEnum.getName(model.getIns_period_type()));
mapValue.put("ins_period", model.getIns_period());
mapValue.put("payments", BankEnum.getName(model.getPayments()));
mapValue.put("insert_ope", model.getInsert_ope());
mapValue.put("modify_ope", model.getModify_ope());
mapValue.put("check_flag", ProductEnum.getName(model.getCheck_flag()));
mapValue.put("check_reason", model.getCheck_reason());
listmap.add(mapValue);
}
return listmap;
}
}
前端jsp页面代码：
/** * 点击导出按钮，导出用户数据 */ $("#exportproduct").click(function(){ var company_code = $("#input1").val(); var product_id = $("#product_id").val(); var risk_type = $("#input2").val(); var check_flag = $("#input3").val(); window.location.href = "../../batch/exportproduct/exportproduct?company_code="+company_code+'&product_id='+product_id+'&risk_type='+risk_type+'&check_flag='+check_flag; });
<button id="exportproduct" type="button" class="btn btn-default" >导出Excel</button>
460
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
