import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

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.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.util.CellRangeAddress;

import com.google.gson.JsonArray;

import com.google.gson.JsonObject;

public class ExcelUtil {

     public static HSSFWorkbook getHSSFWorkbook(String sheetName, JsonArray title,

            String[][] values, HSSFWorkbook wb) {

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

        if (wb == null) {

            wb = new HSSFWorkbook();

        }

        // -个sheet限制存65536条，此处仅存60000

        System.out.println(values.length);

        final int sheetNum = (int) Math.ceil((float) values.length / 60000);

        for (int n = 1; n <= sheetNum; n++) {

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

            final HSSFSheet sheet = wb.createSheet(sheetName + "_" + n);

            System.out.println("sheetName" + sheetName + "_" + n);

            sheet.setDefaultColumnWidth(12);

            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

            HSSFRow row;

            // 第四步，创建单元格，并设置值表头样式

            final HSSFCellStyle headerStyle = wb.createCellStyle();

            headerStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中

            headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中

            final Font fontStyle = wb.createFont(); // 字体样式

            fontStyle.setBoldweight(Font.BOLDWEIGHT_BOLD); // 加粗

            fontStyle.setFontName("黑体"); // 字体

            fontStyle.setFontHeightInPoints((short) 11); // 大小

            // 将字体样式添加到单元格样式中

            headerStyle.setFont(fontStyle);

            // 边框

            headerStyle.setBorderBottom(CellStyle.BORDER_THIN);

            headerStyle.setBorderLeft(CellStyle.BORDER_THIN);

            headerStyle.setBorderRight(CellStyle.BORDER_THIN);

            headerStyle.setBorderTop(CellStyle.BORDER_THIN);

            // 普通单元格样式,边框,水平居中

            final HSSFCellStyle cellStyle = wb.createCellStyle();

            cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中

            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);

            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);

            cellStyle.setBorderRight(CellStyle.BORDER_THIN);

            // cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

            final HSSFDataFormat df = wb.createDataFormat();  // 此处设置数据格式

            cellStyle.setDataFormat(df.getFormat("#,#0.0")); // 小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00"));

            // 声明列对象

            HSSFCell cell = null;

            // 创建标题

            JsonArray cellArray = new JsonArray();

            JsonObject object = new JsonObject();

            JsonObject temobj = new JsonObject();

            int rowSkip = 0, cellSkip = 0;

            String tempCell = "";

            for (int rowId = 0; rowId < title.size(); rowId++) {

                row = sheet.createRow(rowId);

                object = title.get(rowId).getAsJsonObject();

                cellArray = object.get("row").getAsJsonArray();

                // colId为excel列索引，cellId为行标题值的数组索引，cellId遇到当前单元格已使用时，填充至下一个可使用的单元格

                for (int colId = 0, cellId = 0; cellId < cellArray.size(); colId++) {

                    cell = row.createCell(colId);

                    cell.setCellStyle(headerStyle);

                    if (isMergedRegion(sheet, rowId, colId)) {

                        continue;

                    }

                    temobj = cellArray.get(cellId).getAsJsonObject();

                    tempCell = temobj.get("cellvalue").toString().replace("\"", "");

                    try {

                        // System.out.println(tempCell);

                        tempCell = new String(tempCell.getBytes("UTF-8"), "ISO-8859-1");

                        // System.out.println(tempCell);

                        tempCell = new String(tempCell.getBytes("ISO-8859-1"), "UTF-8");

                        // System.out.println(tempCell);

                    } catch (final Exception e) {

                        e.printStackTrace();

                    }

                    cell.setCellValue(tempCell);

                    // System.out.println(cell);

                    // System.out.println(cell.getStringCellValue());

                    cell.setCellStyle(headerStyle);

                    cellId++;

                    // 合并单元格

                    rowSkip = temobj.get("rowspan").getAsInt();

                    cellSkip = temobj.get("colspan").getAsInt();

                    // System.out.println(rowSkip + "=skip==" + cellSkip);

                    if (rowSkip > 1 && rowSkip-- > 0 || cellSkip > 1 && cellSkip-- > 0) {// 用于起始行列计算时需减1

                        final CellRangeAddress cra = new CellRangeAddress(rowId, rowId + rowSkip,

                                colId, colId + cellSkip); // 起始行, 终止行, 起始列, 终止列 // 终止行,

                        sheet.addMergedRegion(cra);

                    }

                }

            }

            // 创建内容 -个sheet只能存65536条

            for (int i = 0; i < 60000 && i < values.length - (n - 1) * 60000; i++) {

                row = sheet.createRow(title.size() + i);

                for (int j = 0; j < values[i].length; j++) {

                    // 将内容按顺序赋给对应的列对象

                    cell = row.createCell(j);

                    cell.setCellValue(values[(n - 1) * 60000 + i][j]);

                    cell.setCellStyle(cellStyle);

                }

            }

        }

        return wb;

    }

    public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {

        final int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {

            final CellRangeAddress range = sheet.getMergedRegion(i);

            final int firstColumn = range.getFirstColumn();

            final int lastColumn = range.getLastColumn();

            final int firstRow = range.getFirstRow();

            final int lastRow = range.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {

                    return true;

                }

            }

        }

        return false;

    }

}