POI-Excel导出

最新推荐文章于 2024-09-03 20:52:56 发布

Deer__青春

最新推荐文章于 2024-09-03 20:52:56 发布

阅读量226

点赞数

分类专栏： java 文章标签： javaweb

本文链接：https://blog.csdn.net/qq_40195958/article/details/78820540

版权

java 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

导出为分类导出多个数据

导入jar包依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>

controller


/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.modules.mainfunction.web.mpsfenxi;

import java.io.IOException;
import java.util.Map;

import javax.servlet.ServletOutputStream;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.thinkgem.jeesite.common.web.BaseController;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * mpsController
 * @author 
 * @version 
 */
@Controller
@RequestMapping(value = "${adminPath}/mainfunction/mpsfenxi/cdlyMpsFenxiToExcel")
public class CdlyMpsFenxiToExcelController extends BaseController {

    @RequestMapping(value = {"impl"})
    public String impleexcelChangeRecord(String str ,HttpServletRequest req, HttpServletResponse rep, Model model) throws IOException {
        String nameExcel = req.getParameter("nameExcel");
        JSONArray list = JSONArray.fromObject(nameExcel);
        JSONArray ziList =new  JSONArray();
        JSONArray waiList =new  JSONArray();
        JSONArray yuanList =new  JSONArray();
        System.out.println("++++"+list.size());
        for(int i=0;i<list.size();i++){
            JSONObject aa = list.getJSONObject(i);
            if("自制".equals(aa.get("waiGou"))){
                ziList.add(aa);
            }else if("外购".equals(aa.get("waiGou"))){
                waiList.add(aa);
            }else if("原始".equals(aa.get("waiGou"))){
                yuanList.add(aa);
            }
        }
       // System.out.println("---------"+ziList.toString());
        //System.out.println("-++++++++++++-"+waiList.toString());
      //  System.out.println("==========="+yuanList.toString());
        String msg = null;
        try{
            Boolean bl = false;
            bl = quotaExportChangeRecord(req,rep,ziList,waiList,yuanList);//调用导出方法
            msg = "导出成功！";
        }catch(Exception e){
            e.printStackTrace();
            msg = "导出失败！";
        }
        req.setAttribute("MSG", msg);
        return null;
    }

    @SuppressWarnings("deprecation")
    public Boolean quotaExportChangeRecord(HttpServletRequest req, HttpServletResponse rep
            ,JSONArray ziList, JSONArray waiList, JSONArray yuanList) throws IOException{
        //创建工作簿workbook
        //利用工作簿对象创建工作表sheet
        //利用工作表对象创建行row
        //利用行对象创建单元格对象cell
        HSSFWorkbook wb= new HSSFWorkbook(); //创建工作薄
        HSSFSheet sheet=wb.createSheet(); //利用工作薄创建工作表

        HSSFFont font=wb.createFont();//获得字体对象
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗  
        HSSFCellStyle cellstyle=wb.createCellStyle();//设置表格样式
        cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//对齐方式  

        HSSFFont font1 = wb.createFont();//创建字体对象
        font1.setFontHeightInPoints((short) 12);
        HSSFCellStyle cellstyle2=wb.createCellStyle();
        cellstyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellstyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellstyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellstyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellstyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//对齐方式  
        cellstyle2.setFont(font1); 

        HSSFRow row = null;//创建行对象
        HSSFCell cell =null;//创建列对象

        CellRangeAddress cellRangeAddress =null;// new CellRangeAddress(0, 0, 0, 11);
        String yuanShi[] = new String[]{"序号","MPS住编号","子编号","相关单号","相关编号","产品编码","名称","规格","客户规格","订单数量","单位","交付日期"};
        // 合并单元格           前两个参数行     后两个列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0, 9));//合并单元格
        row = sheet.createRow((short)0);//通过工作薄创建行对象
        cell = row.createCell((short)0);//通过行对象获得列对象
        cell.setCellValue("原始订单");//设置列的值
        cell.setCellStyle(cellstyle2);//设置表格样式
        sheet.setDefaultRowHeight((short)30);
       //每次 
        row = sheet.createRow((short)1);//新建行对象
        for(int s=0;s<yuanShi.length;s++){
            cell = row.createCell((short)s);
            cell.setCellValue(yuanShi[s]);
            cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)0,(short)(15 * 300));
        }

        for(int j=0;j<yuanList.size();j++){
            JSONObject stu = yuanList.getJSONObject(j);
            row = sheet.createRow((short)j+2);

             cell = row.createCell((short)0);//创建列
             cell.setCellValue(j+1);//序号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)0,(short)(15 * 150));

             cell = row.createCell((short)1);
             cell.setCellValue(stu.get("mpsZhuBian").toString());//MPS主编号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)1,(short)(15 * 300));

             cell = row.createCell((short)2);
             cell.setCellValue(stu.get("ziBianHao").toString());//子编号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)2,(short)(15 * 300));

             cell = row.createCell((short)3);
             cell.setCellValue(stu.get("xiangGuanDanHao").toString());//相关单号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)3,(short)(15 * 300));

             cell = row.createCell((short)4);
             cell.setCellValue(stu.get("xiangGuanbianHao").toString());//相关单号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)4,(short)(15 * 300));

             cell = row.createCell((short)5);
             cell.setCellValue(stu.get("bianMa").toString());//产品编码
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)5,(short)(15 * 300));

             cell = row.createCell((short)6);
             cell.setCellValue(stu.get("mingCheng").toString());//产品编码
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)6,(short)(15 * 300));

             cell = row.createCell((short)7);
             cell.setCellValue(stu.get("guiGe").toString());//产品编码
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)7,(short)(15 * 300));

             cell = row.createCell((short)8);
             cell.setCellValue(stu.get("keHuJianCheng").toString());//产品编码
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)8,(short)(15 * 300));

             cell = row.createCell((short)9);
             cell.setCellValue(stu.get("dingDanNum").toString());//订单数量
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)9,(short)(15 * 300));

             cell = row.createCell((short)10);
             cell.setCellValue(stu.get("danWei").toString());//单位
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)10,(short)(15 * 150));

             cell = row.createCell((short)11);
             cell.setCellValue(stu.get("yaoQiuDate").toString().subSequence(0, 10).toString());//要求交付日期
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)11,(short)(15 * 300));

        }
        //
        String attribute[] = new String[]{"序号","MPS主编号","子编号","相关单号","相关单号子单号","客户简称","单据日期","编码","名称","规格","单位","订单数量","要求交付日期","产品类型","外购自/制"};
        int num1 = yuanList.size()+2;
        sheet.addMergedRegion(new CellRangeAddress(num1,num1,0, 14));
        row = sheet.createRow(num1);
        cell = row.createCell(0);
        cell.setCellValue("自制产品");
        cell.setCellStyle(cellstyle2);
        sheet.setDefaultRowHeight((short)30);

        num1 = num1+1;
        row = sheet.createRow((short)num1);
        for(int n=0;n<attribute.length;n++){
                cell = row.createCell((short)n);
                cell.setCellValue(attribute[n]);
                cell.setCellStyle(cellstyle2);
                sheet.setColumnWidth((short)n,(short)(15 * 300));//设置单元格列宽short 为列宽  
        }
        num1 = num1+1;
        sheet.addMergedRegion(new CellRangeAddress(3,3,0,14));//工作表增加一个范围地址（合并的单元格）
        for (int i = 0; i < ziList.size(); i++) {  
            JSONObject stu = ziList.getJSONObject(i);
             row=sheet.createRow((short)i+num1);//创建行对象

             cell = row.createCell((short)0);
             cell.setCellValue(i+1);//MPS主编号
             cell.setCellStyle(cellstyle);
             sheet.setColumnWidth((short)0,(short)(15 * 150));

             cell = row.createCell((short)1);
            cell.setCellValue(stu.get("mpsZhuBian").toString());//MPS主编号
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)1,(short)(15 * 300));

            cell = row.createCell((short)2);
            cell.setCellValue(stu.get("ziBianHao").toString());//子单号
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)2,(short)(15 * 150));

            cell = row.createCell((short)3);
            cell.setCellValue(stu.get("xiangGuanDanHao").toString());//相关单号
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)3,(short)(15 * 300));

            cell = row.createCell((short)4);
            cell.setCellValue(stu.get("xiangGuanZi").toString());//相关单号子单号
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)4,(short)(15 * 300));

            cell = row.createCell((short)5);
            cell.setCellValue(stu.get("keHuJianCheng").toString());//客户简称
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)5,(short)(15 * 300));

            cell = row.createCell((short)6);
            cell.setCellValue(stu.get("danJuDate").toString());//单据日期
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)6,(short)(15 * 300));

            cell = row.createCell((short)7);
            cell.setCellValue(stu.get("bianMa").toString());//编码
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)7,(short)(15 * 300));

            cell = row.createCell((short)8);
            cell.setCellValue(stu.get("mingCheng").toString());//名称
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)8,(short)(15 * 300));

            cell = row.createCell((short)9);
            cell.setCellValue(stu.get("guiGe").toString());//产品规格
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)9,(short)(15 * 300));

            cell = row.createCell((short)10);
            cell.setCellValue(stu.get("danWei").toString());//单位
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)10,(short)(15 * 150));

            cell = row.createCell((short)11);
            cell.setCellValue(stu.get("dingDanNum").toString());//订单数量
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)11,(short)(15 * 300));

            cell = row.createCell((short)12);
            cell.setCellValue(stu.get("yaoQiuDate").toString().substring(0, 9));//要求完成日期
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)12,(short)(15 * 300));

            cell = row.createCell((short)13);
            cell.setCellValue(stu.get("itemLeiXing").toString());//产品类型
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)13,(short)(15 * 300));

            cell = row.createCell((short)14);
            cell.setCellValue(stu.get("waiGou").toString());//外购、自制
            cell.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)14,(short)(15 * 150));

        }

        int  num = num1+2;
       sheet.addMergedRegion(new CellRangeAddress(num,num,0,14));//工作表增加一个范围地址（合并的单元格）
                row=sheet.createRow((short)num);
                HSSFCell     cell2 = row.createCell((short)0);
                cell2.setCellValue("外购材料");
                cell2.setCellStyle(cellstyle2);
                sheet.setDefaultRowHeight((short)30);

                num = num+1;

        for (int i = 0; i < waiList.size(); i++) {  
            JSONObject stu = waiList.getJSONObject(i);

            row=sheet.createRow((short)(i+num));//创建行对象

            HSSFCell cell1 = row.createCell((short)0);//利用行对象，创建列
           cell1.setCellValue(i+1);//编码
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)0,(short)(15 * 150));//设置单元格列宽

            cell1 = row.createCell((short)1);
            cell1.setCellValue(stu.get("mpsZhuBian").toString());//MPS主编号
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)1,(short)(15 * 300));

            cell1 = row.createCell((short)2);
            cell1.setCellValue(stu.get("ziBianHao").toString());//子单号
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)2,(short)(15 * 150));

            cell1 = row.createCell((short)3);
            cell1.setCellValue(stu.get("xiangGuanDanHao").toString());//相关单号
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)3,(short)(15 * 300));

            cell1 = row.createCell((short)4);
            cell1.setCellValue(stu.get("xiangGuanZi").toString());//相关单号子单号
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)4,(short)(15 * 300));

            cell1 = row.createCell((short)5);
            cell1.setCellValue(stu.get("keHuJianCheng").toString());//客户简称
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)5,(short)(15 * 300));

            cell1 = row.createCell((short)6);
            cell1.setCellValue(stu.get("danJuDate").toString());//单据日期
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)6,(short)(15 * 300));

            cell1 = row.createCell((short)7);
            cell1.setCellValue(stu.get("bianMa").toString());//编码
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)7,(short)(15 * 300));

            cell1 = row.createCell((short)8);
            cell1.setCellValue(stu.get("mingCheng").toString());//名称
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)8,(short)(15 * 300));

            cell1 = row.createCell((short)9);
            cell1.setCellValue(stu.get("guiGe").toString());//产品规格
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)9,(short)(15 * 300));

            cell1 = row.createCell((short)10);
            cell1.setCellValue(stu.get("danWei").toString());//单位
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)10,(short)(15 * 150));

            cell1 = row.createCell((short)11);
            cell1.setCellValue(stu.get("dingDanNum").toString());//订单数量
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)11,(short)(15 * 300));

            cell1 = row.createCell((short)12);
            cell1.setCellValue(stu.get("yaoQiuDate").toString());//要求完成日期
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)12,(short)(15 * 300));

            cell1 = row.createCell((short)13);
            cell1.setCellValue(stu.get("itemLeiXing").toString());//产品类型
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)13,(short)(15 * 300));

            cell1 = row.createCell((short)14);
            cell1.setCellValue(stu.get("waiGou").toString());//外购、自制
            cell1.setCellStyle(cellstyle);
            sheet.setColumnWidth((short)14,(short)(15 * 150));

        }

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，  
        // 第六步，将文件存到指定位置  
        boolean t =false;
        try {  
            ServletOutputStream servletoutputstream = rep.getOutputStream();
            String fileName = "";
            if(req.getHeader("user-agent").indexOf("MSIE") != -1 || req.getHeader("user-agent").indexOf("rv:11") !=-1) {
                fileName = java.net.URLEncoder.encode("MRP分析导出","utf-8") + ".xls"; 
            }else{
                fileName = new String("MRP分析导出".getBytes("utf-8"),"iso-8859-1")+ ".xls"; 
            } 
            rep.setHeader("Content-disposition", "attachment; filename="+ fileName);
            rep.setContentType("application/vnd.ms-excel;charset=utf-8");
            wb.write(servletoutputstream); 
            servletoutputstream.flush();
            t =true;
        }  
        catch (Exception e) {  
            e.printStackTrace();  
        } 
        return t;
   }

}

以上controller
页面

function infoExel(){
  var str = "";
  $("#tb tr").each(function(){
    var text = $(this).children("td:first").text();//遍历获取tr中每第一个的值
    str += text+",";  
  });
    str = str.substring(0,str.length-1);
    $("#idExcel").val(str);//将值赋给表单
  doucument.forms[1].action="";//提交页面第二个form表单
  doucument.forms[1].submit();
}

<form:form mothod="post" style="display:none">
     <input type="hidden" id="idExcel" name="nameExcel"/>
</form:form>