导出通用表格数据

whyclj

2019-06-29 83467fc92a2a945522cf4a6ebb28f86b41715be2

导出通用表格数据

 sensor/src/com/fgkj/servlets/EchartPictureDowloadServlet.java

@@ -22,6 +22,7 @@
import org.apache.poi.hssf.usermodel.HSSFWorkbook;



import com.fgkj.actions.ActionUtil;

import com.google.gson.JsonSyntaxException;

import com.google.gson.reflect.TypeToken;



import sun.misc.BASE64Decoder;

@@ -42,122 +43,32 @@
                //ExprotReport(req,resp);                        //导出报表

            }else if("charge-test-monData".equalsIgnoreCase(pageName)){

                //ExprotLD9MonTestData(req,resp);

            }else if("normal".equalsIgnoreCase(pageName)){

                ExprotNormalReport(req, resp);

            }

        }

    }

    

    /**

     * 历史放电数据页面中导出LD9设备的放电数据

     * @param req

     * @param resp

     * 导出通用表格

     */

    /*private void ExprotLD9MonTestData(HttpServletRequest req,

            HttpServletResponse resp) {

        String mon_vol_line = req.getParameter("mon_vol_line");            //单体电压折线图

        String group_vol_line = req.getParameter("group_vol_line");        //组端/在线电压折线图

        String test_curr_line = req.getParameter("test_curr_line");        //测试电流折线图

        String batt_real_cap = req.getParameter("batt_real_cap");        //单体实际容量柱状图

		
        String battinf = req.getParameter("battinf");                    //电池组信息

        String mon_test_data = req.getParameter("mon_test_data");        //单体测试数据

        String testdatainfo = req.getParameter("testdatainfo");        //单体测试数据

		
		
        bytes=new ArrayList<byte[]>();

    public void ExprotNormalReport(HttpServletRequest req, HttpServletResponse resp){

        String[] picNames = null;

        String[] picSours = null;

        String[][] tabData = null;

        

        try {

            //单体电压折线图

            if (ServletUtils.isNotNull(mon_vol_line)) {

                String[] url = mon_vol_line.split(",");

                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));

                //System.out.println("1111111111111111111111111");

            }

			
            //组端/在线电压折线图

            if (ServletUtils.isNotNull(group_vol_line)) {

                String[] url = group_vol_line.split(",");

                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));

                //System.out.println("2222222222222222222222222");

            }

			
            //单体电流曲线图

            if (ServletUtils.isNotNull(test_curr_line)) {

                String[] url = test_curr_line.split(",");

                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));

                //System.out.println("3333333333333333333333333333");

            }

			
            //单体电流曲线图

            if (ServletUtils.isNotNull(batt_real_cap)) {

                String[] url = batt_real_cap.split(",");

                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));

                //System.out.println("3333333333333333333333333333");

            }

            BattInf binf = ActionUtil.getGson().fromJson(battinf, BattInf.class);

            Ld9testdata_inf mondatainfo = ActionUtil.getGson().fromJson(testdatainfo, Ld9testdata_inf.class);

            List<Ld9testdata> montestdata = ActionUtil.getGson().fromJson(mon_test_data, new TypeToken<List<Ld9testdata>>(){}.getType());

            createMonTestDataExcel(bytes,binf,montestdata,mondatainfo,resp,req);

        } catch (IOException e) {

            e.printStackTrace();

        }

		
    }*/

	
    /*//生成历史数据中LD9单体数据的报表

    private void createMonTestDataExcel(List<byte[]> bytes,BattInf binf,List<Ld9testdata> montestdata,Ld9testdata_inf mondatainfo,HttpServletResponse resp,HttpServletRequest req) {

        try {

            picNames = ActionUtil.getGson().fromJson(req.getParameter("picNames"), String[].class);

            picSours = ActionUtil.getGson().fromJson(req.getParameter("picSours"), String[].class);

            tabData  = ActionUtil.getGson().fromJson(req.getParameter("tabData"), String[][].class);

            // 创建一个工作薄

            HSSFWorkbook wb = new HSSFWorkbook();

            HSSFSheet sheet1 = wb.createSheet("数据总表");

            // HSSFRow row = sheet1.createRow(2);

            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();

            int rownum = 1;

		
            sheet1.createRow(rownum);

            sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");

            sheet1.getRow(rownum).createCell(2).setCellValue(binf.getStationName()+"-"+binf.getBattGroupName());

            rownum++;

			
            sheet1.createRow(rownum);

            sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+binf.getBattProducer());

            sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(binf.getBattInUseDate(),"yyyy-MM-dd"));

            sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+binf.getMonCount());

            sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+binf.getMonVolStd()+"V");

            sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(binf.getMonCapStd(),0)+"AH");

            sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(binf.getMonResStd(),3)+"mΩ");

            sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(binf.getMonSerStd(),0));

			
            rownum++;

            sheet1.createRow(rownum);

            sheet1.getRow(rownum).createCell(1).setCellValue( "被测单体编号：#"+mondatainfo.getMon_num());

            sheet1.getRow(rownum).createCell(2).setCellValue( "测试类型："+binf.getBattGroupName5());

            sheet1.getRow(rownum).createCell(3).setCellValue( "测试时间："+formartDate(mondatainfo.getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));

            sheet1.getRow(rownum).createCell(4).setCellValue( "测试电流："+mondatainfo.getTest_curr()+"A");

            sheet1.getRow(rownum).createCell(5).setCellValue( "测试时长："+formatTestLong(mondatainfo.getTest_timelong()));

            sheet1.getRow(rownum).createCell(6).setCellValue( "测试容量："+formartDouble(mondatainfo.getTest_cap(),0)+"AH");

            sheet1.getRow(rownum).createCell(7).setCellValue( "终止原因："+binf.getBattGroupName6());

			
            rownum++;

            sheet1.createRow(rownum);

            sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");

            sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));

            rownum+=2;

			
            int picnum = 0;

            String[] picName = new String[]{

                    "单体电压曲线图","端电压曲线图","单体电流曲线图","单体实际容量柱状图"

            };

            for(int i=0;i<picName.length;i++){

                sheet1.createRow(rownum);

                sheet1.getRow(rownum).createCell(1).setCellValue(picName[i]);

                rownum++;

                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));

                anchor.setAnchorType(3);

                patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);

                picnum ++;rownum+=25;

            if(picNames != null && picSours != null){

                ExprotReportPicData(wb,req,resp,picNames,picSours);

            }



            createNewLD9DataSheet(wb,montestdata);

            if(tabData != null && tabData.length > 0){

                ExprotReportTabData(wb,req,resp,tabData);

            }

            

            // 转码防止乱码

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

@@ -166,81 +77,55 @@
            OutputStream out = resp.getOutputStream();

            wb.write(out);

            out.close();

        } catch (UnsupportedEncodingException e) {

        } catch (JsonSyntaxException | IOException e) {

            e.printStackTrace();

        }

    }

	
	


    private void ExprotReportTabData(HSSFWorkbook wb, HttpServletRequest req,

            HttpServletResponse resp, String[][] tabData) {

        HSSFSheet sheet1 = wb.createSheet("表格数据");

        int rownum = 1;

        for(int i=0;i<tabData[0].length;i++){

            sheet1.createRow(rownum);

            for(int j=0;j<tabData[i].length;j++){

                sheet1.getRow(rownum).createCell(j+1).setCellValue(tabData[i][j]);

            }

            rownum++;

        }

    }



    //导出多个图片数据

    private void ExprotReportPicData(HSSFWorkbook wb, HttpServletRequest req,

            HttpServletResponse resp,String[] picName,String[] picSource) {

        HSSFSheet sheet1 = wb.createSheet("图片数据");

		
        bytes = new ArrayList<byte[]>();

        //将图片数据转换成字节数组

        for(int i = 0 ;i<picSource.length;i++ ){

            try {

                bytes.add(new BASE64Decoder().decodeBuffer(picSource[i].split(",")[1]));

        } catch (IOException e) {

            e.printStackTrace();

        }

    }*/

	
	
    /*public void createNewLD9DataSheet(HSSFWorkbook wb,final List<Ld9testdata> montestdata){

        HSSFSheet sheet = wb.createSheet("单体数据表");

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

				
            int mon_count = 1;                        //总单体数量

            int total_col = montestdata.size();        //总记录条数

			
			
            List<String> tabTh = new ArrayList<String>(){{

                this.add("时间(HH:MM:SS)");this.add("组端电压(V)");this.add("在线电压(V)");this.add("电流(A)");this.add("测试容量(AH)");this.add("单体电压"+montestdata.get(0).getTest_monnum()+"(V)");

            }};

            //for(int i=1;i<=mon_count;i++){

            //    tabTh.add("单体电压"+i+"(V)");

            //}

			
            String[][] datas = new String[total_col][tabTh.size()];

			
            //System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);

			
            for(int j=0;j < datas.length;j++){

                int currnum = 0;	
                datas[j][currnum++] = formatTestLong(montestdata.get(j).getTest_timelong());

                datas[j][currnum++] = formartDouble(montestdata.get(j).getGroup_vol(),1)+"";

                datas[j][currnum++] = formartDouble(montestdata.get(j).getOnline_vol(),1)+"";

                datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_curr(),3)+"";

                datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_cap(),1)+"";

				
				
                datas[j][currnum++] =formartDouble(montestdata.get(j).getMon_vol(),3)+"";

				
                //datas[j][currnum++] = "";

				
            }    

            //System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);

			
            int maxcol = 254;            //设置最大列数

			
            int countReLine = (int) Math.ceil((double)total_col/maxcol);

            int totalrow = countReLine *(tabTh.size()+1);                    //excel总行数

            for(int i=0;i<totalrow;i++){

                sheet.createRow(i);

            }

			
            //System.out.println("countReLine:"+countReLine);

			
            int currRow = 0;            //当前行

            int arr_index = 0;            //当前列

            for(int i=0;i<countReLine;i++){

                for(int col = 0;col <= maxcol && col<=datas.length && arr_index<datas.length;col++){					
                    if(col == 0){

                        for(int k=0;k<tabTh.size();k++){

                            sheet.getRow(k+currRow).createCell(col).setCellValue(tabTh.get(k));

                        }

                    }else{

                        for(int k = 0;k<datas[arr_index].length;k++){

                            sheet.getRow(k+currRow).createCell(col).setCellValue(datas[arr_index][k]);

                        }

                        arr_index++;

        int rownum = 1;

        //创建图片

        if(bytes.size() > 0){			
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();

            for(int i=0;i<bytes.size();i++){

                sheet1.createRow(rownum);

                sheet1.getRow(rownum).createCell(1).setCellValue(i>=picName.length?"":picName[i]);

                rownum++;

                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (i + 1));

                anchor.setAnchorType(3);

                patriarch.createPicture(anchor,wb.addPicture(bytes.get(i),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);

                rownum+=25;

                    }

                }

                currRow += tabTh.size()+1;

            }        

			
            //System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);

        }

    }

    */



    /**

     * 导出报表