excel导出

于 2014-09-19 10:41:34 发布
阅读量803 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： JAVA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/wtfpewfn/article/details/39395071

一.struts配置文件

 

<!-- 文件下载action映射配置 -->
    <package name="grs-function-cutOver-export" namespace="/grs/function/cutOverExport" extends="struts-default">
        <action name="*CutOverExportAction" method="{1}"  class="com.sctf.grs.action.CutOverAction">
            <!-- 下载  -->
            <result name="success" type="stream">
                <param name="contentType">application/octet-stream;charset=ISO-8859-1</param>
                <param name="inputName">exportExcel</param>
                <param name="contentDisposition">attachment;filename="${fileName}"</param>
                <param name="bufferSize">4096</param>
            </result>
            <!-- 提示或错误页面跳转  -->
            <result name="input">/functions/fileOperations/blank.jsp</result>
        </action>
    </package>


二.Action 类


    /**
     *
     * @Title: exportExcel
     * @return
     * @throws java.io.UnsupportedEncodingException
     */
    public String exportExcel() throws UnsupportedEncodingException,FileNotFoundException, IOException{
        Map params = getQueryMap();
        String startDate = "";
        String endDate = "";
        if(params != null){
            startDate = (null != params.get("START_DATE") && !"".equals(params.get("START_DATE"))) ? String.valueOf(params.get("START_DATE")) : "";
            endDate = (null != params.get("END_DATE") && !"".equals(params.get("END_DATE"))) ? String.valueOf(params.get("END_DATE")) : "";
        }
        //设置名字
         fileName = "割接统计数据" + startDate + "至" + endDate + ".xls";
        fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
        return "success";
    }
    @SuppressWarnings("all")
    public InputStream getExportExcel() throws UnsupportedEncodingException,FileNotFoundException, IOException {
        Map params = getQueryMap();
        //判断有无参数，若无参数则直接return
        if(params == null || (params != null && params.get("START_DATE") == null && params.get("END_DATE") == null)){
            return null;
        }
        Map extraMap = new HashMap();//用于配置生成excel参数
        String startDate = "";
        String endDate = "";
        if(params != null && params.size() > 0&&String.valueOf(params.get("exportData")).equals("true")){
            startDate = (null != params.get("START_DATE") && !"".equals(params.get("START_DATE"))) ? String.valueOf(params.get("START_DATE")) : "";
            endDate = (null != params.get("END_DATE") && !"".equals(params.get("END_DATE"))) ? String.valueOf(params.get("END_DATE")) : "";
            extraMap.put("titleName", "割接统计数据（" + startDate + "至" + endDate + "）");
            extraMap.put("titleRowIndex", 0);
            //实例化EXCEL操作类
            ExportExcel2003 ex = new ExportExcel2003();
            //查询数据
            Map queryMap=new HashMap();
            queryMap.put("userId", Util.getInstance().getUserInfo(ServletActionContext.getRequest()).getUserID());
            List<Map> groupList=beforeService.queryUserById(queryMap);
            boolean flag=false;
            for(Map m:groupList){
                String groupCode=String.valueOf(m.get("groupCode"));
                if(groupCode.equals("GRS_CUTOVER_STARTER")){
                    flag=true;
                    break;
                }
            }
            String templateFile="/conf/excel/cutOver/cutOver.xls";
            String orgName="";
            if(flag){
                templateFile="/conf/excel/cutOver/cutOver_filiale.xls";
                User user = Util.getInstance().getUserInfo(ServletActionContext.getRequest());
                Map tempMap=new HashMap();
                tempMap.put("orgId", user.getOrgID());
                List<Map<?,?>> list=preAccessControlDAO.queryOrgIdByUserId(tempMap);
                if(list.size()>0){
                    Map orgMap=list.get(0);
                    orgName=String.valueOf(orgMap.get("filialeName"));
                    orgName=orgName.substring(0,orgName.length()-3);
                    params.put("ORG_NAME",orgName);
                }


            }


            List<Map> proList = cutOverService.queryCutoverReport(params);
            ClassPathResource cpr = new ClassPathResource(templateFile);
            File file = cpr.getFile();
            HSSFWorkbook workbook = ex.createExcel(file, proList, 0, null, 4, extraMap);
            // 将wb 工作空间 转化成输入流，为导出赋值
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                workbook.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] b = os.toByteArray();
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            try {
                os.close();//关闭输出流
            } catch (Exception e) {
                e.printStackTrace();
            }
            return in;
        }else{
            return null;
        }
    }


三.excel工具类;

/

**
* 无模板的生成方法
* @param out 输出流
* @param parm 自定义列名（要与SQL列名对应）
* @param dataSet 查询返回的数据集
* @param sheetName 自定义sheet名称
* @param style 自定义样式
*/
public static void createExcel(OutputStream out,LinkedHashMap parm,List<Map> dataSet,String sheetName,HSSFCellStyle style){
HSSFWorkbook workbook = new HSSFWorkbook();
//sheet名
if(sheetName == null){
sheetName = "sheet1";
}
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(16);
// 创建表头   
insertDataSetWithNoTemplet(sheet,parm,dataSet,style);
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(out!=null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}


/**有模板的生成方法
* @param templet 模板File
* @param dataSet输出流
* @param sheetindex 简单模板需要的列对应参数(复杂模板调用不需要)
* @param dataSet 结果集 要求都是List<Map>
* @param sheetindex 默认第一个
* @param style 自定义样式
* @param isSimple 是否为简单模板 0是复杂 1是简单 2是巡检自定义 4是割接
*/
public static HSSFWorkbook createExcel(File templet, List<Map> dataSet, int sheetindex, HSSFCellStyle style, int isSimple, Map extraMap){
FileInputStream fis;
HSSFWorkbook workbook = null;
try {
fis = new FileInputStream(templet);
//如果配置文件有，那么按照配置文件写入，如果没有，那么自动遍历每行找到空行和表头
workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(sheetindex);
//如果为1 插入简单模板数据，如果为0插入复杂模板数据
if(isSimple == 1){
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 居中
                cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
insertDataSetWithSimpleTemplet(sheet,dataSet,cellStyle);
}else if(isSimple == 0){
if(dataSet.get(0)!=null){
Map dataSetMap = dataSet.get(0);
insertDataSetWithComplexTemplet(sheet, dataSetMap, style);
}else{
throw new IOException("数据集不正确。");
}
}else if(isSimple == 2){
                //生成巡检统计EXCEL
                String titleName = null != extraMap.get("titleName") ? String.valueOf(extraMap.get("titleName")) : "";
                int titleRowIndex = null != extraMap.get("titleRowIndex") ? Integer.parseInt(String.valueOf(extraMap.get("titleRowIndex"))) : 0;
                //定义cell样式
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 居中
                cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                insertDataSetWithInspectionTemplet(sheet, dataSet, cellStyle, titleRowIndex, titleName);
            }else if(isSimple == 3){
                //生成重保统计EXCEL
                String titleName = null != extraMap.get("titleName") ? String.valueOf(extraMap.get("titleName")) : "";
                int titleRowIndex = null != extraMap.get("titleRowIndex") ? Integer.parseInt(String.valueOf(extraMap.get("titleRowIndex"))) : 0;
                //定义cell样式
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 居中
                cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                insertDataSetWithEmphasisProTemplet(sheet, dataSet, cellStyle, titleRowIndex, titleName);
            }
            else if(isSimple == 4){
                //生成重保统计EXCEL
                String titleName = null != extraMap.get("titleName") ? String.valueOf(extraMap.get("titleName")) : "";
                int titleRowIndex = null != extraMap.get("titleRowIndex") ? Integer.parseInt(String.valueOf(extraMap.get("titleRowIndex"))) : 0;
                //定义cell样式
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 居中
                insertDataSetWithCutOverTemplet(sheet, dataSet, cellStyle, titleRowIndex, titleName);
            }
} catch(Exception e){
e.printStackTrace();
}
return workbook;
}
   /**
     * 生成巡检统计EXCEL
     * @param sheet
     * @param dataSet
     * @param style
     * @param titleRowIndex
     * @param titleName
     */
    public static void insertDataSetWithInspectionTemplet(HSSFSheet sheet,List<Map> dataSet,HSSFCellStyle style, int titleRowIndex, String titleName){
        int fenleiCount1 = 0;
        int fenleiCount2 = 0;
        int fenleiCount3 = 0;
        int fenleiCount4 = 0;
        //找出标题行
        HSSFRow titleRow = sheet.getRow(titleRowIndex);
//        if(titleRow.getRowNum() > 1){
//            System.out.println("length:" + titleRow.getRowNum());
//            System.out.println("标题行必须为一行一列的数据！");
//            log.info("巡检模版标题行必须为一行一列的数据！");
//            return;
//        }
        //设定标题
        HSSFCell titleCell = titleRow.getCell(0);
        titleCell.setCellValue(titleName);


        //找出第一个空行,准备填写数据
        int rowindex = sheet.getLastRowNum();
        int fieldNameRowIndex = rowindex - 1;//字段名行
        System.out.println("最后一行"+sheet.getLastRowNum());
        // 新建Map 参数读取模板列
        List<String> parm = new ArrayList();
        // 得到参数行
        HSSFRow parmrow = sheet.getRow(rowindex);
        for (int rownum = 0; rownum < parmrow.getLastCellNum(); rownum++) {
            HSSFCell cell = parmrow.getCell(rownum);
            if(cell==null){
                parm.add("");
                continue;
            }
            String key = cell.getStringCellValue();
            parm.add(key);
        }
        //删除模板参数行
        sheet.removeRow(sheet.getRow(rowindex));
        for (Map rowdata : dataSet) {
            //排除合计行
            if(rowdata != null && rowdata.get("filialeId").toString().equals("-1")){
                continue;
            }
            HSSFRow datarow = sheet.createRow(rowindex);
            //此处以数组长度做限制，防止越界
            for(int cellnum = 0; cellnum < parm.size(); cellnum++){
                HSSFCell cell = datarow.createCell(cellnum);
                if(rowdata.get(parm.get(cellnum)) == null){
                    cell.setCellValue("");
                }else{
                    String val = "";
                    if(rowdata.get(parm.get(cellnum)) instanceof CLOB){
                        try {
                            oracle.sql.CLOB clob = (oracle.sql.CLOB)rowdata.get(parm.get(cellnum));
                            val = clob.stringValue();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }else{
                        val = String.valueOf(rowdata.get(parm.get(cellnum)));
                    }
                    cell.setCellValue(val);
                    //查询需要合并的行号和列号
                    if(parm.get(cellnum).equals("FENLEI")){
                        //若为分公司类别
                        if(val.equals("一类")){
                            fenleiCount1++;
                        }else if(val.equals("二类")){
                            fenleiCount2++;
                        }else if(val.equals("三类")){
                            fenleiCount3++;
                        }else if(val.equals("四类")){
                            fenleiCount4++;
                        }
                    }
                }
                if(style != null){
                    cell.setCellStyle(style);
                }
            }
            rowindex++;
        }
        //合并单元格
        //数组中参数：一类总数，二类总数，三类总数，四类总数
        int [] array = {fenleiCount1, fenleiCount2, fenleiCount3, fenleiCount4};
        for(int i = 0; i < array.length; i++){
            if(array[i] <= 0){
                continue;
            }
            int beforeLength = 0;
            //计算前面所有字段的和
            for(int k = 0; k < i; k++){
                beforeLength += array[k];
            }
            int colStart = fieldNameRowIndex + beforeLength + 1;//column开始
            int colEnd = fieldNameRowIndex + beforeLength + array[i];//column结束
            // 四个参数分别是：起始行，起始列，结束行，结束列
            sheet.addMergedRegion(new Region(colStart, (short)0, colEnd, (short)0));
        }
        //添加合计行，为最后一行数据
        Map totalMap = dataSet.get(dataSet.size() - 1);
        if(null != totalMap){
            HSSFRow datarow = sheet.createRow(rowindex);
            //此处以数组长度做限制，防止越界
            for(int cellnum = 0; cellnum < parm.size(); cellnum++){
                HSSFCell cell = datarow.createCell(cellnum);
                if(totalMap.get(parm.get(cellnum)) == null){
                    cell.setCellValue("");
                }else{
                    cell.setCellValue(String.valueOf(totalMap.get(parm.get(cellnum))));
                }
                if(style != null){
                    cell.setCellStyle(style);
                }
            }
            //合并单元格
            sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short)0, sheet.getLastRowNum(), (short)1));
        }
    }

四.前端页面

 window.open("../../grs/function/cutOverExport/exportExcelCutOverExportAction.action?" + params);

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值