/**

     *  导出复杂excel 合并单元格 （HSSFWorkbook）

     */

    @GetMapping("/testHSSFWorkbook.do")

    public void testExport1(HttpServletResponse response)

        throws Exception

    {

        /** 第一步，创建一个Workbook，对应一个Excel文件  */

        HSSFWorkbook wb = new HSSFWorkbook();

        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */

        HSSFSheet sheet = wb.createSheet("南京市城镇社会保险参保人员花名册");

        /** 第三步，设置样式以及字体样式*/

        HSSFCellStyle titleStyle = createTitleCellStyle(wb);

        HSSFCellStyle headerStyle = createHeadCellStyle(wb);

        HSSFCellStyle contentStyle = createContentCellStyle(wb);

        /** 第四步，创建标题 ,合并标题单元格 */

        // 行号

        int rowNum = 0;

        // 总列数

        int totalColumn = 10;

        // 创建第一页的第一行，索引从0开始

        HSSFRow row0 = sheet.createRow(rowNum++);

        row0.setHeight((short) 800);// 设置行高

        String title = "南京市城镇社会保险参保人员花名册";

        HSSFCell c00 = row0.createCell(0);

        c00.setCellValue(title);

        c00.setCellStyle(titleStyle);

        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalColumn));//标题合并单元格操作，11+1为总列数

        // 第二行

        HSSFRow row1 = sheet.createRow(rowNum++);

        row1.setHeight((short) 500);

        String[] row_first = {"单位名称(公章)：", "", "", "", "", "", "", "", "劳动和社保保障证号（单位代码）：", "", ""};

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

        {

            HSSFCell tempCell = row1.createCell(i);

            tempCell.setCellStyle(headerStyle);

            tempCell.setCellValue(row_first[i]);

        }

        //第三行

        HSSFRow row2 = sheet.createRow(rowNum++);

        row2.setHeight((short) 700);

        String[] row_second = {"序号", "社会保障卡号（个人代码）", "姓名", "性别", "身份证号码", "民族", "户口性质", "参加工作时间",

            "进本单位参保时间", "月缴费基数（元）", "备注"};

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

        {

            HSSFCell tempCell = row2.createCell(i);

            tempCell.setCellValue(row_second[i]);

            tempCell.setCellStyle(headerStyle);

        }

        //循环每一行数据

        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); //查询出来的数据

        Map<String,Object> map = new HashMap<String,Object>();

        map.put("name", "1");

        map.put("r1", "111");

        map.put("r2", "222");

        map.put("r3", "333");

        map.put("r4", "444");

        map.put("r5", "555");

        map.put("r6", "666");

        dataList.add(map);

        dataList.add(map);//加多一条list

        for (Map<String, Object> excelData : dataList)

        {

            HSSFRow tempRow = sheet.createRow(rowNum++);

            tempRow.setHeight((short) 500);

            // 循环单元格填入数据

            for (int j = 0; j < 7; j++)

            {

                HSSFCell tempCell = tempRow.createCell(j);

                tempCell.setCellStyle(contentStyle);

                String tempValue;

                if (j == 0)

                {

                    // 乡镇、街道名称

                    tempValue = excelData.get("name").toString();

                }

                else if (j == 1)

                {

                    // 登记数（人）

                    tempValue = excelData.get("r1").toString();

                }

                else if (j == 2)

                {

                    // 办证总数（人）

                    tempValue = excelData.get("r2").toString();

                }

                else if (j == 3)

                {

                    // 办证率（%）

                    tempValue = excelData.get("r3").toString();

                }

                else if (j == 4)

                {

                    // 登记户数（户）

                    tempValue = excelData.get("r4").toString();

                }

                else if (j == 5)

                {

                    // 签订数（份）

                    tempValue = excelData.get("r5").toString();

                }

                else

                {

                    // 备注

                    tempValue = excelData.get("r6").toString();

                }

                tempCell.setCellValue(tempValue);

            }

        }

        // 尾行备注1

        HSSFRow foot1 = sheet.createRow(rowNum++);

        foot1.setHeight((short) 300);

        String[] row_foot1 = {"备注:  1、“参加工作时间”：指首次参加工作时间。", "", "", "", "", "", "",

            "", "5、机关事业单位人员应注明是否参加养老保险。", "", ""};

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

        {

            HSSFCell tempCell = foot1.createCell(i);

            tempCell.setCellValue(row_foot1[i]);

        }

        int remarkRowNum1 = dataList.size() + 3;

        // 合并

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum1, remarkRowNum1, 0, 2));//备注1

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum1, remarkRowNum1, 8, 10));//备注5

        // 尾行备注2

        HSSFRow foot2 = sheet.createRow(rowNum++);

        foot2.setHeight((short) 300);

        String[] row_foot2 = {"           2、“进本单位参保时间”：指在本单位缴费起始时间。", "", "", "", "", "", "",

            "", "6、本表一式两份，社会保险经办机构、缴费单位各留一份", "", ""};

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

        {

            HSSFCell tempCell = foot2.createCell(i);

            tempCell.setCellValue(row_foot2[i]);

        }

        int remarkRowNum2 = dataList.size() + 4;

        // 合并

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum2, remarkRowNum2, 0, 2));//备注1

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum2, remarkRowNum2, 8, 10));//备注5

        // 尾行备注3

        HSSFRow foot3 = sheet.createRow(rowNum++);

        foot3.setHeight((short) 300);

        String[] row_foot3 = {"           3、“月缴费基数”：指月平均收入总和。", "", "", "", "", "", "",

            "", "7、社会保险政策咨询电话：12333", "", ""};

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

        {

            HSSFCell tempCell = foot3.createCell(i);

            tempCell.setCellValue(row_foot3[i]);

        }

        int remarkRowNum3 = dataList.size() + 5;

        // 合并

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum3, remarkRowNum3, 0, 2));//备注1

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum3, remarkRowNum3, 8, 10));//备注5

        // 尾行备注4

        HSSFRow foot4 = sheet.createRow(rowNum++);

        foot4.setHeight((short) 300);

        String[] row_foot4 = {"           4、跨社会保险结算年度办理补缴时须出具劳动合同原件、工资报表原件等相关资料。",

            "", "", "", "", "", "", "", "8、南京人力资源和社会保障网址：www.njhrss.gov.cn", "", ""};

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

        {

            HSSFCell tempCell = foot4.createCell(i);

            tempCell.setCellValue(row_foot4[i]);

        }

        int remarkRowNum4 = dataList.size() + 6;

        // 合并

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum4, remarkRowNum4, 0, 2));//备注1

        sheet.addMergedRegion(new CellRangeAddress(remarkRowNum4, remarkRowNum4, 8, 10));//备注5

        // 最后一行

        HSSFRow foot = sheet.createRow(rowNum++);

        foot.setHeight((short) 300);

        String[] row_foot = {"单位负责人：", "", "填报人：", "", "联系电话：", "", "", "", "", "填报日期：  年  月  日", ""};

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

        {

            HSSFCell tempCell = foot.createCell(i);

            tempCell.setCellValue(row_foot[i]);

        }

        for (int i = 0; i <= totalColumn; i++)

        {

            sheet.autoSizeColumn((short)i,true); //调整列宽

        }

        //导出

        String fileName = "南京市城镇社会保险参保人员花名册.xls";

        try

        {

            response.setCharacterEncoding("UTF-8");

            response.setHeader("content-Type", "application/vnd.ms-excel");

            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            OutputStream stream = response.getOutputStream();

            if (null != wb && null != stream)

            {

                wb.write(stream);// 将数据写出去

                wb.close();

                stream.close();

            }

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }