excel导出

 public void excel(HttpServletResponse response) {
// 设置响应contenType
//        response.setContentType("application/pdf");
        response.setContentType("application/x-msdownload");

        // 设置文件名称
        try {
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("name.xlsx", "GBK"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        try {
            OutputStream outputStream = response.getOutputStream();
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.单元格合并
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, 3);//起始行,结束行,起始列,结束列

            CellRangeAddress callRangeAddress10 = new CellRangeAddress(1, 2, 0, 0);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress11 = new CellRangeAddress(1, 2, 3, 3);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress12 = new CellRangeAddress(1, 1, 1, 2);//起始行,结束行,起始列,结束列
           //3.加载合并单元格对象 
           sheet.addMergedRegion(callRangeAddress);
           sheet.addMergedRegion(callRangeAddress10);
           sheet.addMergedRegion(callRangeAddress11); 
           sheet.addMergedRegion(callRangeAddress12);
            //4.创建工作表
            HSSFSheet sheet = workbook.createSheet("部门");
            //5.样式
            HSSFCellStyle headStyle = createCellStyle(workbook, (short) 10, true, true);

            HSSFCellStyle lineStyle = createCellStyle(workbook, (short) 10, false, true);
            //设置默认列宽
            sheet.setDefaultColumnWidth(15);
            //6创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("部门");

            String[] titles = {"序号", "部门信息", "", "来自那个数据库"};//""为占位字符串
            HSSFRow row1 = sheet.createRow(1);
            for (int i = 0; i < titles.length; i++) {
                HSSFCell cell1 = row1.createCell(i);
                cell1.setCellStyle(lineStyle);
                cell1.setCellValue(titles[i]);
            }
            String[] titles1 = {"主键", "部门名称"};
            HSSFRow row2 = sheet.createRow(2);
            for (int i = 0; i < titles1.length; i++) {
                HSSFCell cell2 = row2.createCell(i+1);
                cell2.setCellStyle(lineStyle);
                cell2.setCellValue(titles1[i]);
            }
            List<Dept> daoAll = dao.findAll();
            //7.向excel中添加数据
            int j = 1;
            for (int i = 0; i < daoAll.size(); i++) {
                //创建数据行,前面有三行,头标题行和列标题行
                HSSFRow row3 = sheet.createRow(i+ 3);
                HSSFCell cell0 = row3.createCell(0);
                cell0.setCellStyle(lineStyle);
                cell0.setCellValue(j++);

                HSSFCell cell1 = row3.createCell(1);
                cell1.setCellStyle(lineStyle);
                cell1.setCellValue(daoAll.get(i).getDeptno());

                HSSFCell cell2 = row3.createCell(2);
                cell2.setCellStyle(lineStyle);
                cell2.setCellValue(daoAll.get(i).getDname());

                HSSFCell cell3 = row3.createCell(3);
                cell3.setCellStyle(lineStyle);
                cell3.setCellValue(daoAll.get(i).getDb_source());
            }

            //添加缓冲流
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            ByteArrayInputStream bis = new ByteArrayInputStream(baos.toByteArray());
            byte[] buf = new byte[10240];
            int len = bis.read(buf);

            while (len != -1) {
                outputStream.write(buf, 0, len);
                len = bis.read(buf);
            }
            response.setCharacterEncoding("UTF-8");
            OutputStream out = new BufferedOutputStream(outputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize, boolean flag, boolean flag1) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if (flag1) {
            HorizontalAlignment center = HorizontalAlignment.CENTER;
            style.setAlignment(center);//水平居中
        }

        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if (flag) {
//            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }