使用poi导出excel，及合并单元格边框显示问题

使用poi导出excel，及合并单元格边框显示问题。
首先创建workbook、sheet

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("导出数据表");

设置合并单元格，可以使用Ragion或者CellRagionAddress,后者为较新版本。
Ragion的参数为（开始行，（short）开始列），结束行，（short）结束列）。

Region region0 = new Region(0, (short) 0, 2, (short) 0);

CellRagionAddress的参数为（开始行，结束行，开始列，结束列）。

CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 2, 0, 0);

将设置合并单元格的样式添加到sheet中

sheet.addMergedRegion(region0);

创建行

HSSFRow row1 = sheet.createRow(0);

设置表格样式

HSSFCellStyle style = workbook.createCellStyle();

在行内创建单元格并赋值、设置样式

    HSSFCell cell = row1.createCell(0);
    cell.setCellValue(regionMap.get(region0));
    cell_.setCellStyle(style);

输出路径

        FileOutputStream fos;
        try {
            fos = new FileOutputStream(EXCEL_PATH);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

使用以上方式便可以导出excel了，但是由于有合并单元格，所以会出现边框显示不完全的问题，所以在创建单元格的时候，使用了Region。调用以下方法可以解决边框显示问题
使用cellRangeAddress也可以解决，代码也在下面

public static void setRegionStyle(HSSFSheet sheet, Region region,
            HSSFCellStyle cs) {
        for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);

                        }
        }
    }

public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
            HSSFCellStyle cs) {

        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

            HSSFRow row = sheet.getRow(i);
            if (row == null)
                row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);

            }
        }
    }

当需要添加下面的数据时可以使用以下方法添加

public static void setValueByRowAndCol(int row, int col, String value,
            HSSFSheet sheet, HSSFCellStyle style) {
        HSSFRow hssfrow = sheet.getRow(row);
        HSSFCell cell = null;
        if (hssfrow == null) {
            hssfrow = sheet.createRow(row);
            cell = hssfrow.createCell(col);
            cell.setCellType(HSSFCell.ENCODING_UTF_16);
            cell.setCellValue(new HSSFRichTextString(value));
            cell.setCellStyle(style);
    } <span class="hljs-keyword">else</span> {
        cell = hssfrow.createCell(col);
        cell.setCellType(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(<span class="hljs-keyword">new</span> HSSFRichTextString(<span class="hljs-keyword">value</span>));
        cell.setCellStyle(style);
    }

}</pre>

但是使用时发现光使用此方法却只有下方和右方有边框，还需要设置边框属性。
以下为完整代码

public class PoiAction extends ActionSupport {

    private static final long serialVersionUID = -4424287278463717550L;

    private static String EXCEL_PATH = "D:/out-" + new Date().getTime()
            + ".xls";

    public String showSheetTitle() {
        exportExcel();
        System.out.println("hello");
        return SUCCESS;
    }

    @SuppressWarnings("deprecation")
    public void exportExcel() {
        // 创建workbook 设置sheet
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("导出数据表");
        // List<Region> regionList = new ArrayList<Region>();
        Map<Region, String> regionMap = new HashMap<Region, String>();
        Region region0 = new Region(0, (short) 0, 2, (short) 0);// 用户订单号
        regionMap.put(region0, "用户订单号");
        Region region1 = new Region(0, (short) 1, 0, (short) 5);// 收件方信息
        regionMap.put(region1, "收件方信息");
        Region region2 = new Region(0, (short) 6, 0, (short) 44);// 运单其它信息
        regionMap.put(region2, "运单其它信息");
        Region region3 = new Region(1, (short) 1, 2, (short) 1);// 收件公司
        regionMap.put(region3, "收件公司");
        Region region4 = new Region(1, (short) 2, 2, (short) 2);// 联系人
        regionMap.put(region4, "联系人");
        Region region5 = new Region(1, (short) 3, 2, (short) 3);// 联系电话
        regionMap.put(region5, "联系电话");
        Region region6 = new Region(1, (short) 4, 2, (short) 4);// 手机号码
        regionMap.put(region6, "手机号码");
        Region region7 = new Region(1, (short) 5, 2, (short) 5);// 收件详细地址
        regionMap.put(region7, "收件详细地址");
        Region region8 = new Region(1, (short) 6, 2, (short) 6);// 付款方式
        regionMap.put(region8, "付款方式");
        Region region9 = new Region(1, (short) 7, 2, (short) 7);// 第三方付月结卡号
        regionMap.put(region9, "第三方付月结卡号");
        Region region10 = new Region(1, (short) 8, 2, (short) 8);// 托寄物内容
        regionMap.put(region10, "托寄物内容");
        Region region11 = new Region(1, (short) 9, 2, (short) 9);// 托寄物数量
        regionMap.put(region11, "托寄物数量");
        Region region12 = new Region(1, (short) 10, 2, (short) 10);// 件数
        regionMap.put(region12, "件数");
        Region region13 = new Region(1, (short) 11, 2, (short) 11);// 实际重量（KG）
        regionMap.put(region13, "实际重量（KG）");
        Region region14 = new Region(1, (short) 12, 2, (short) 12);// 计费重量（KG）
        regionMap.put(region14, "计费重量（KG）");
        // 将设置合并单元格的样式添加到sheet中
        Iterator<Region> iterator = regionMap.keySet().iterator();
        while (iterator.hasNext()) {
            sheet.addMergedRegion(iterator.next());
        }
        // 创建二行
        HSSFRow row1 = sheet.createRow(0);
        HSSFRow row2 = sheet.createRow(1);
        // 边框样式---为setRegionStyle设置
        HSSFCellStyle styleBorder = workbook.createCellStyle();
        styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 七种样式
        HSSFCellStyle styleNoColor = SetStyleUtils.setStyleNoColor(workbook);
        HSSFCellStyle styleLightYellow = SetStyleUtils
                .setStyleLightYellow(workbook);
        HSSFCellStyle styleLightYellowStrong = SetStyleUtils
                .setStyleLightYellowStrong(workbook);
        HSSFCellStyle styleLightYellow_Red = SetStyleUtils
                .setStyleLightYellowRed(workbook);
        HSSFCellStyle styleTan = SetStyleUtils.setStyleTan(workbook);
        HSSFCellStyle styleTanStrong = SetStyleUtils
                .setStyleTanStrong(workbook);
        HSSFCellStyle styleTan_Red = SetStyleUtils.setStyleTanRed(workbook);

        // 边框样式---为setRegionStyle设置
        HSSFCellStyle styleBorder = workbook.createCellStyle();
        styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 七种样式
        HSSFCellStyle styleNoColor = SetStyleUtils.setStyleNoColor(workbook);
        HSSFCellStyle styleLightYellow = SetStyleUtils
                .setStyleLightYellow(workbook);
        HSSFCellStyle styleLightYellowStrong = SetStyleUtils
                .setStyleLightYellowStrong(workbook);
        HSSFCellStyle styleLightYellow_Red = SetStyleUtils
                .setStyleLightYellowRed(workbook);
        HSSFCellStyle styleTan = SetStyleUtils.setStyleTan(workbook);
        HSSFCellStyle styleTanStrong = SetStyleUtils
                .setStyleTanStrong(workbook);
        HSSFCellStyle styleTan_Red = SetStyleUtils.setStyleTanRed(workbook);

        // row1
        HSSFCell cell_row1_0 = row1.createCell(0);
        cell_row1_0.setCellValue(regionMap.get(region0));
        setRegionStyle(sheet, region0, styleBorder);
        cell_row1_0.setCellStyle(styleNoColor);

        HSSFCell cell_row1_1 = row1.createCell(1);
        cell_row1_1.setCellValue(regionMap.get(region1));
        setRegionStyle(sheet, region1, styleBorder);
        cell_row1_1.setCellStyle(styleLightYellowStrong);

        HSSFCell cell_row1_2 = row1.createCell(6);
        cell_row1_2.setCellValue(regionMap.get(region2));
        setRegionStyle(sheet, region2, styleBorder);
        cell_row1_2.setCellStyle(styleTanStrong);

        // row2---收件方信息
        HSSFCell cell_row2_0 = row2.createCell(1);
        cell_row2_0.setCellValue(regionMap.get(region3));
        setRegionStyle(sheet, region3, styleBorder);
        cell_row2_0.setCellStyle(styleLightYellow_Red);

        HSSFCell cell_row2_1 = row2.createCell(2);
        cell_row2_1.setCellValue(regionMap.get(region4));
        setRegionStyle(sheet, region4, styleBorder);
        cell_row2_1.setCellStyle(styleLightYellow_Red);

        HSSFCell cell_row2_2 = row2.createCell(3);
        cell_row2_2.setCellValue(regionMap.get(region5));
        setRegionStyle(sheet, region5, styleBorder);
        cell_row2_2.setCellStyle(styleLightYellow);

        HSSFCell cell_row2_3 = row2.createCell(4);
        cell_row2_3.setCellValue(regionMap.get(region6));
        setRegionStyle(sheet, region6, styleBorder);
        cell_row2_3.setCellStyle(styleLightYellow);

        HSSFCell cell_row2_4 = row2.createCell(5);
        cell_row2_4.setCellValue(regionMap.get(region7));
        setRegionStyle(sheet, region7, styleBorder);
        cell_row2_4.setCellStyle(styleLightYellow_Red);

        // row2---运单其他信息
        HSSFCell cell_row2_5 = row2.createCell(6);
        cell_row2_5.setCellValue(regionMap.get(region8));
        setRegionStyle(sheet, region8, styleBorder);
        cell_row2_5.setCellStyle(styleTan_Red);

        HSSFCell cell_row2_6 = row2.createCell(7);
        cell_row2_6.setCellValue(regionMap.get(region9));
        setRegionStyle(sheet, region9, styleBorder);
        cell_row2_6.setCellStyle(styleTan);

        HSSFCell cell_row2_7 = row2.createCell(8);
        cell_row2_7.setCellValue(regionMap.get(region10));
        setRegionStyle(sheet, region10, styleBorder);
        cell_row2_7.setCellStyle(styleTan_Red);

        HSSFCell cell_row2_8 = row2.createCell(9);
        cell_row2_8.setCellValue(regionMap.get(region11));
        setRegionStyle(sheet, region11, styleBorder);
        cell_row2_8.setCellStyle(styleTan_Red);

        HSSFCell cell_row2_9 = row2.createCell(10);
        cell_row2_9.setCellValue(regionMap.get(region12));
        setRegionStyle(sheet, region12, styleBorder);
        cell_row2_9.setCellStyle(styleTan_Red);

        HSSFCell cell_row2_10 = row2.createCell(11);
        cell_row2_10.setCellValue(regionMap.get(region13));
        setRegionStyle(sheet, region13, styleBorder);
        cell_row2_10.setCellStyle(styleTan);

        HSSFCell cell_row2_11 = row2.createCell(12);
        cell_row2_11.setCellValue(regionMap.get(region14));
        setRegionStyle(sheet, region14, styleBorder);
        cell_row2_11.setCellStyle(styleTan);

        // 输出路径设置

        FileOutputStream fos;
        try {
            fos = new FileOutputStream(EXCEL_PATH);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
         }
    /**
     * 解决合并单元格边框消失问题，不仅需要调用此方法，单元格自身也需要正常设置上下左右的边框
     * 
     * @param sheet
     * @param region
     * @param cs
     */
    @SuppressWarnings("deprecation")
    public static void setRegionStyle(HSSFSheet sheet, Region region,
            HSSFCellStyle cs) {
        for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
            HSSFRow row = HS
FCellUtil.getRow(i, sheet);
            for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

}

/**
 * 表格样式
 * 
 * @author lxm
 * 
 */
public class SetStyleUtils {
    /**
     * 无背景颜色普通字体
     * 
     * @param workbook
     * @return
     */
    public static HSSFCellStyle setStyleNoColor(HSSFWorkbook workbook) {
        HSSFCellStyle styleNoColor = workbook.createCellStyle();
        styleNoColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleNoColor.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleNoColor.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleNoColor.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleNoColor.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleNoColor.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleNoColor.setWrapText(true);
        return styleNoColor;
    }

    /**
     * LightYellow背景颜色，普通字体
     * 
     * @param workbook
     * @return
     */
    public static HSSFCellStyle setStyleLightYellow(HSSFWorkbook workbook) {
        HSSFCellStyle styleLightYellow = workbook.createCellStyle();
        styleLightYellow.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleLightYellow.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleLightYellow.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleLightYellow.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleLightYellow.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleLightYellow.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleLightYellow.setFillForegroundColor(IndexedColors.LIGHT_YELLOW
                .getIndex());
        styleLightYellow.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleLightYellow.setWrapText(true);
        return styleLightYellow;
    }

    /**
     * LightYellow背景颜色，加粗字体
     * 
     * @param workbook
     * @return
     */
    public static HSSFCellStyle setStyleLightYellowStrong(HSSFWorkbook workbook) {
        HSSFCellStyle styleLightYellowStrong = workbook.createCellStyle();
        styleLightYellowStrong.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleLightYellowStrong
                .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleLightYellowStrong.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleLightYellowStrong.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleLightYellowStrong.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleLightYellowStrong.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleLightYellowStrong
                .setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        styleLightYellowStrong.setFillPattern(CellStyle.SOLID_FOREGROUND);
        HSSFFont fontStrong = workbook.createFont();
        fontStrong.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleLightYellowStrong.setFont(fontStrong);
        styleLightYellowStrong.setWrapText(true);
        return styleLightYellowStrong;
    }

    /**
     * LightYellow背景颜色，红色字体
     * 
     * @param workbook
     * @return
     */
    public static HSSFCellStyle setStyleLightYellowRed(HSSFWorkbook workbook) {
        HSSFCellStyle styleLightYellowRed = workbook.createCellStyle();
        styleLightYellowRed.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleLightYellowRed.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleLightYellowRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleLightYellowRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleLightYellowRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleLightYellowRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleLightYellowRed.setFillForegroundColor(IndexedColors.LIGHT_YELLOW
                .getIndex());
        styleLightYellowRed.setFillPattern(CellStyle.SOLID_FOREGROUND);
        HSSFFont fontRed = workbook.createFont();
        fontRed.setColor(IndexedColors.RED.getIndex());
        styleLightYellowRed.setFont(fontRed);
        styleLightYellowRed.setWrapText(true);
        return styleLightYellowRed;
    }

}