java POI excel导出,并合并单元格设置宽度高度

最近在网上学习了java POI excel导出

整了一个例子，代码也简单，创建行、列、设置宽度高度、合并列

下面贴出我滴代码：

public class Total{
    private Integer payWay;
    private Float amount;
    private String creatTime;
    private String endTime;

    /**
     * 省略 get/set
     */
}

package com.excel;
import java.io.FileOutputStream;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;

public class TestExportExcel {

    private final static  String  PAY_WAY1 = "现金";

    private final static  String PAY_WAY2 = "刷卡";

    private final static  String PAY_WAY3 = "微信";

    private final static  String PAY_WAY4 = "支付宝";

    private final static String PAY_WAY5 = "转帐";

    private final static String PAY_WAY46 = "其他";

    private final static String NUMBER = "序号";

    private final static String PAY_TYPE = "缴费方式";

    private final static String PAY_AMOUNT = "缴费金额(元)";

    private final static String REMARKS  = "备注";

    public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;

    public static final int UNIT_OFFSET_LENGTH = 7;

    public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };

    @Test
    public void test(){
            TestExportExcel t = new TestExportExcel();
        try {
            t.testExport();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void testExport() throws Exception  {

        HSSFWorkbook wb = new HSSFWorkbook();
        /**
         * 创建table工作薄
         */
        HSSFSheet sheet = wb.createSheet("缴费汇总表");
        String data = "[{\"payWay\":1,\"endTime\":\"2017-09\",\"creatTime\":\"2017-08\",\"amount\":408.5},{\"payWay\":3,\"endTime\":\"2017-09\",\"creatTime\":\"2017-08\",\"amount\":520}]";
        JSONArray jsonArray = JSON.parseArray(data);
        List<Total> totalList = JSON.parseArray(jsonArray+"", Total.class);

        HSSFRow row;
        HSSFCell cell;

        int maixRowNumb = totalList.size()+4;
        for(int rowNumb = 0; rowNumb < maixRowNumb; rowNumb++) {
            /**
             * 开始创建表格行
             */
            row = sheet.createRow(rowNumb);

            for(int columnNumb = 0; columnNumb < 4; columnNumb++) {
                /**
                 * 创建行单元格
                 */
                cell = row.createCell(columnNumb);
                HSSFCellStyle cellStyle = wb.createCellStyle();
                //第一列
                if( rowNumb == 0 ) {
                    cell.setCellValue("缴费汇总表");
                }else if( rowNumb == 1 ) {//第二列
                    if( columnNumb == 3 ){
                        cell.setCellValue("所属时间:2017-08至2017-09");
                    }
                }else if( rowNumb == 2 ){//第三列
                    switch (columnNumb){
                        case 0:
                            cell.setCellValue(TestExportExcel.NUMBER);
                            break;
                        case 1:
                            cell.setCellValue(TestExportExcel.PAY_TYPE);
                            break;
                        case 2:
                            cell.setCellValue(TestExportExcel.PAY_AMOUNT);
                            break;
                        case 3:
                            cell.setCellValue(TestExportExcel.REMARKS);
                            break;
                    }
                }else if( rowNumb < maixRowNumb-2 ){
                    Total Total = totalList.get(rowNumb-3);
                    if( columnNumb == 0 ){
                        /**
                         * 序号
                         */
                        int index = rowNumb-2;
                        cell.setCellValue(index);
                    }else if( columnNumb == 1 ){
                        switch (Total.getPayWay()){
                            case 1:
                                cell.setCellValue(TestExportExcel.PAY_WAY1);
                                break;
                            case 2:
                                cell.setCellValue(TestExportExcel.PAY_WAY2);
                                break;
                            case 3:
                                cell.setCellValue(TestExportExcel.PAY_WAY3);
                                break;
                            case 4:
                                cell.setCellValue(TestExportExcel.PAY_WAY4);
                                break;
                            case 5:
                                cell.setCellValue(TestExportExcel.PAY_WAY5);
                                break;
                            case 6:
                                cell.setCellValue(TestExportExcel.PAY_WAY46);
                                break;
                        }
                    }else if( columnNumb == 2 ){
                        cell.setCellValue(Total.getAmount());
                    }

                }else if( rowNumb < maixRowNumb-1 ){
                    if( columnNumb == 1 ){
                        cell.setCellValue("合计");
                    }else if( columnNumb == 2){
                        cell.setCellValue(928.5);
                    }
                }
                if( rowNumb < maixRowNumb-1){
                    /**
                     * 设置内容水平居中
                     */
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                }
                /**
                 * 设置内容垂直居中
                 */
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                cell.setCellStyle(cellStyle);
            }
        }

        //创建表格之后设置行高
        for(int i = 0; i < maixRowNumb; i++) {
            //合并单元格
            if( i == 0 ){
                //参数：起始行号，终止行号， 起始列号，终止列号
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            } else if( i == 1 ){
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 4));
            }else{
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
            }
            /**
             * 设置行的高度
             */
            row = sheet.getRow(i);
            row.setHeightInPoints(30);
        }
        //设置列宽
        for(int j = 0; j < maixRowNumb; j++) {
            switch (j){
                case 0:
                case 1:
                case 2:
                    sheet.setColumnWidth(j, TestExportExcel.pixelWidth(100));
                    break;
                case 3:
                    sheet.setColumnWidth(j, TestExportExcel.pixelWidth(200));
                    break;
            }

        }
        wb.write(new FileOutputStream("E:\\table6.xls"));
    }

    /**
     * 宽像素
     * @param pxs
     * @return
     */
    public static short pixelWidth(int pxs) {
        short width = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
        width += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
        return width;
    }
}