java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

时间：2022-05-11 21:37:54

一、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();

    }

}

调用案例

java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

二、基于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.加上截图进行说明

java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

java导出数据EXCEL的工具类（以spring-webmvc-4.0.4jar为基础）

相关文章