POI操作 导出Excel表格

最近在做导出Excel表格的功能，在网上查了资料，把网上的资料和自己做的，现在总结一下，留个纪念。

POI中可能会用到一些需要设置EXCEL单元格格式的操作小结：

先获取工作薄对象:

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

一、设置背景色：

setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

三、设置居中:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

四、设置字体:

HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小

HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行

七、合并单元格:

Region region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
sheet.addMergedRegion(region1);

八、加边框

  HSSFCellStyle cellStyle= wookBook.createCellStyle();
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

自己实现的例子，关键方法如下：

/**     * 国际路单导出     * @param trackingList     * @param response     * @throws Exception     * @date 20****     * @author ***     */    private void exportExcelData(List<PackageTrackingEntity> trackingList,            HttpServletResponse response) throws Exception{                        try{        //Excel保存路径        XLSExport xls = new XLSExport();        xls.createRow(0);                //设置合并单元格        xls.setCell(0, "********表");        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 10);        xls.getSheet().addMergedRegion(range);        //设置居中        HSSFCellStyle style = xls.getWorkbook().createCellStyle();        style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //设置字体        HSSFFont headfont = xls.getWorkbook().createFont();            headfont.setFontName("宋体");            headfont.setFontHeightInPoints((short) 18);// 字体大小            headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗          style.setFont(headfont);        xls.getWorkbook().getSheetAt(0).getRow(0).getCell(0).setCellStyle(style);        xls.createRow(1);        //设置列头格式        HSSFCellStyle style1 = xls.getWorkbook().createCellStyle();        style1.setAlignment(HSSFCellStyle.VERTICAL_CENTER);        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //设置列头字体        HSSFFont headfont1 = xls.getWorkbook().createFont();            headfont1.setFontName("宋体");            headfont1.setFontHeightInPoints((short) 12);// 字体大小            headfont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗          style1.setFont(headfont1);        //设置单元格格式        HSSFCellStyle cellstyle = xls.getWorkbook().createCellStyle();        cellstyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //设置列头字体        HSSFFont cellfont = xls.getWorkbook().createFont();            cellfont.setFontName("宋体");            cellfont.setFontHeightInPoints((short) 12);// 字体大小           cellstyle.setFont(cellfont);                            //设置条码单元格格式        HSSFCellStyle codestyle = xls.getWorkbook().createCellStyle();        //codestyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);        //codestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //字体        HSSFFont codefont = xls.getWorkbook().createFont();            codefont.setFontName("宋体");            codefont.setFontHeightInPoints((short) 12);// 字体大小           codestyle.setFont(codefont);        xls.getSheet().setColumnWidth(1, 10 * 256);        xls.getSheet().setColumnWidth(2, 20 * 256);        xls.getSheet().setColumnWidth(3, 20 * 256);        xls.getSheet().setColumnWidth(4, 20 * 256);        xls.getSheet().setColumnWidth(5, 15 * 256);        xls.getSheet().setColumnWidth(6, 15 * 256);        xls.getSheet().setColumnWidth(7, 15 * 256);        xls.getSheet().setColumnWidth(8, 10 * 256);        xls.getSheet().setColumnWidth(9, 20 * 256);//设置第十列，条码列        xls.getSheet().setColumnWidth(10, 15 * 256);                String[] tableHeader ={"序号","总包号码","原寄局","寄达局","航班","袋数","件数","重量","备注","邮件条码","操作员"};        for(int i = 0 ; i<tableHeader.length ;i ++){            xls.setCell(i,tableHeader[i]);            xls.getWorkbook().getSheetAt(0).getRow(1).getCell(i).setCellStyle(style1);        }        int rowIndex = trackingList.size();// 表内容行数        //填充数据        if (trackingList != null){            for ( int i= 2; i < rowIndex + 2; i++ ){                xls.createRow(i);                xls.setCell(0,i-1);                xls.setCell(1,trackingList.get(i-2).getPackageno());                xls.setCell(2, trackingList.get(i-2).getSetpost());                xls.setCell(3,trackingList.get(i-2).getArrivepost());                xls.setCell(4, trackingList.get(i-2).getFlight());                xls.setCell(5,Integer.parseInt(trackingList.get(i-2).getBagno()));                xls.setCell(6,Integer.parseInt(trackingList.get(i-2).getMailno()));                xls.setCell(7,trackingList.get(i-2).getWeight());                xls.setCell(8, trackingList.get(i-2).getSealingtype());                if(trackingList.get(i-2).getSealingtype().equals("航挂")&&trackingList.get(i-2).getM_mailcode()==null){                    xls.setCell(9,trackingList.get(i-2).getMailType());                }                else if(trackingList.get(i-2).getSealingtype().equals("平邮")&&trackingList.get(i-2).getMailType().equals("正常件")){                    xls.setCell(9,"");                }                else if(trackingList.get(i-2).getSealingtype().equals("平邮")&&trackingList.get(i-2).getMailType().equals("退件")){                    xls.setCell(9,trackingList.get(i-2).getMailType());                }                else xls.setCell(9,trackingList.get(i-2).getM_mailcode());                xls.setCell(10, trackingList.get(i-2).getOper());                //设置单元格                for(int j=0;j<11;j++)                    if(j==9){//设置条码                        xls.getWorkbook().getSheetAt(0).getRow(i).getCell(j).setCellStyle(codestyle);                    }                    else{                    xls.getWorkbook().getSheetAt(0).getRow(i).getCell(j).setCellStyle(cellstyle);                    }                }            }            xls.createRow(trackingList.size() + 2);//        xls.createCell(0, mails.size()+2, "总计:"+mails.size()+"                                " +//                "日期:"+new SimpleDateFormat("yyyy-MM-dd").format(new Date()), mails.size()+2, 0, mails.size()+2, 3, null, null, 0);        //合并单元格        CellRangeAddress rangeLast = new CellRangeAddress(trackingList.size() + 2,0, 0,2);           xls.getSheet().addMergedRegion(range);                xls.setCell(0,"封发日期:"+trackingList.get(0).getSealingdate());        xls.getWorkbook().getSheetAt(0).getRow(trackingList.size() + 2).getCell(0).setCellStyle(codestyle);                String fileName = "*******表_" + DateTime.format(new Date(),"yyMMddHHmm") + ".xls";        response.setContentType("application/vnd.ms-excel");        response.setCharacterEncoding("utf-8");        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gbk"), "iso8859-1"));        OutputStream os = response.getOutputStream();        xls.getWorkbook().write(os);        os.flush();        }catch(Exception e){            e.printStackTrace();        }    }