java导出数据EXCEL的工具类
一、springboot中可以使用
pom文件
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency>
工具类代码
import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.List; import java.util.Map; public class ObjectExcel { public static void buildExcelDocument(Map<String, Object> model,String fileName,HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); fileName=fileName+DateUtils.getDateTime("HHmmss")+".xls"; HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"ISO-8859-1")); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式 headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont headerFont = workbook.createFont(); //标题字体 headerFont.setBold(true); headerFont.setFontHeightInPoints((short)11); headerStyle.setFont(headerFont); short width = 20,height=25*20; sheet.setDefaultColumnWidth(width); HSSFRow row = sheet.createRow(0); for(int i=0; i<len; i++){ //设置标题 String title = titles.get(i); cell = row.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式 contentStyle.setAlignment(HorizontalAlignment.CENTER); List<Map<String,Object>> varList = (List<Map<String,Object>>) model.get("varList"); int varCount = varList.size(); for(int i=0; i<varCount; i++){ HSSFRow rows = sheet.createRow(i+1); Map<String,Object> vpd = varList.get(i); /* int j=0; for(String key : vpd.keySet()) { HSSFCell cells = rows.createCell(j); cells.setCellStyle(contentStyle); cells.setCellValue(String.valueOf(vpd.get(key))); j++; }*/ for(int j=0;j<len;j++){ String varstr = vpd.get("var"+(j+1)) != null ? String.valueOf(vpd.get("var"+(j+1))) : ""; HSSFCell cells = rows.createCell(j); cells.setCellStyle(contentStyle); cells.setCellValue(varstr); } } OutputStream out = response.getOutputStream(); workbook.write(out); out.close(); workbook.close(); } }
调用案例
二、基于springmvc
1.本工具类继承于 spring-webmvc-4.0.4jar文件心中的一个类 AbstractExcelView
2.代码如下
package com.skjd.util; import java.util.Date; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.web.servlet.view.document.AbstractExcelView; import com.skjd.util.PageData; import com.skjd.util.Tools; /** * 导出到EXCEL * 类名称：ObjectExcelView.java * 类描述： * @author FH * 作者单位： * 联系方式： * @version 1.0 */ public class ObjectExcelView extends AbstractExcelView{ @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); //标题字体 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short)11); headerStyle.setFont(headerFont); short width = 20,height=25*20; sheet.setDefaultColumnWidth(width); for(int i=0; i<len; i++){ //设置标题 String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell,title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for(int i=0; i<varCount; i++){ PageData vpd = varList.get(i); for(int j=0;j<len;j++){ String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : ""; cell = getCell(sheet, i+1, j); cell.setCellStyle(contentStyle); setText(cell,varstr); } } } }
3.特此送上案例如下
/** * 导出数据 * @return * @throws Exception */ @RequestMapping(value="/excel") public ModelAndView excel(Page page) throws Exception{ logBefore(logger, "导出数据"); ModelAndView mv = this.getModelAndView(); PageData pd = new PageData(); pd = this.getPageData(); page.setPd(pd); Map<String,Object> dataMap = new HashMap<String,Object>(); List<String> titles = new ArrayList<String>(); titles.add("订单号"); titles.add("乘客姓名"); titles.add("用户手机号"); titles.add("状态"); titles.add("类型"); titles.add("司机姓名"); titles.add("司机手机号"); titles.add("车牌号"); titles.add("订单创建时间"); titles.add("订单结束时间"); titles.add("里程"); titles.add("费用"); dataMap.put("titles", titles); List<PageData> orderList = orderService.datalist(page); List<PageData> varList = new ArrayList<PageData>(); for(int i=0;i<orderList.size();i++){ PageData vpd = new PageData(); vpd.put("var1", orderList.get(i).getString("ordernumber")); vpd.put("var2", orderList.get(i).getString("customer_name")); vpd.put("var3", orderList.get(i).getString("customer_phone")); if(orderList.get(i).get("order_status")!=null){ if("0".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "生成订单");} if("1".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "司机已接单");} if("2".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "确认上车");} if("3".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "行程结束");} if("4".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "完成订单");} if("5".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "到达乘客上车点");} if("99".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "取消订单");} } if(orderList.get(i).get("order_type")!=null){ if("1".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "实时专车");} if("2".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "预约专车");} if("3".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "送机");} if("4".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "接机");} } vpd.put("var6", orderList.get(i).getString("name")); vpd.put("var7", orderList.get(i).getString("phone")); vpd.put("var8", orderList.get(i).getString("license_number")); if(orderList.get(i).get("create_time")!=null){vpd.put("var9", orderList.get(i).get("create_time").toString());} if(orderList.get(i).get("end_time")!=null){vpd.put("var10", orderList.get(i).get("end_time").toString());} /*vpd.put("ordernumber", value);*/ if(orderList.get(i).get("actual_price")!=null){vpd.put("var12", orderList.get(i).get("actual_price").toString());} varList.add(vpd); } dataMap.put("varList", varList); ObjectExcelView erv = new ObjectExcelView(); mv = new ModelAndView(erv,dataMap); return mv; }
4.加上截图进行说明
