package com.util;

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

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

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

public class ExcelUtil {

    /**

     * 判断指定的单元格是否是合并单元格

     *

     * @param sheet

     * @param row    行下标

     * @param column 列下标

     * @return

     */

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

        int sheetMergeCount = sheet.getNumMergedRegions();

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

            CellRangeAddress range = sheet.getMergedRegion(i);

            int firstColumn = range.getFirstColumn();

            int lastColumn = range.getLastColumn();

            int firstRow = range.getFirstRow();

            int lastRow = range.getLastRow();

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

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

                    return true;

                }

            }

        }

        return false;

    }

    /**

     * 获取合并单元格的值

     *

     * @param sheet

     * @param row

     * @param column

     * @return

     */

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {

        int sheetMergeCount = sheet.getNumMergedRegions();

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

            CellRangeAddress ca = sheet.getMergedRegion(i);

            int firstColumn = ca.getFirstColumn();

            int lastColumn = ca.getLastColumn();

            int firstRow = ca.getFirstRow();

            int lastRow = ca.getLastRow();

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

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

                    Row fRow = sheet.getRow(firstRow);

                    Cell fCell = fRow.getCell(firstColumn);

                    fCell.setCellType(CellType.STRING);

                    return getCellValue(fCell);

                }

            }

        }

        return "";

    }

    /**

     * 获取单元格的值

     *

     * @param cell

     * @return

     */

    public static String getCellValue(Cell cell) {

        if (cell == null) {

            return "";

        }

        cell.setCellType(CellType.STRING);

        if (cell == null) return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue().trim();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue()).trim();

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

            return cell.getCellFormula().trim();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            return String.valueOf(cell.getNumericCellValue()).trim();

        }

        return "";

    }

    public static List<List<String>> getValues(String fileUrl, int sheetNum) {

        List<List<String>> values = new ArrayList<List<String>>();

        try {

            File file = new File(fileUrl);

            InputStream is = null;

            try {

                is = new FileInputStream(file);

            } catch (FileNotFoundException e) {

                e.printStackTrace();

            }

            Workbook workbook = null;

            Sheet sheet = null;

            workbook = WorkbookFactory.create(is);

            int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页

//            for(int s=0;s<sheetCount;s++){

            sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet

            int rowNum = sheet.getLastRowNum();//有多少行

            for (int i = 1; i <= rowNum; i++) {

                Row row = sheet.getRow(i);//第i行

                if (row == null) {//过滤空行

                    continue;

                }

                List<String> list = new ArrayList<>();

                int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了

                //                for (int j = 0; j < row.getLastCellNum(); j++) {//第j列://+1是因为最后一列是空 也算进去

                for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去

                    Cell cell = row.getCell(j);

                    String cellValue;

                    boolean isMerge = false;

                    if (cell != null) {

                        isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());

                    }

                    //判断是否具有合并单元格

                    if (isMerge) {

                        cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());

                    } else {

                        cellValue = getCellValue(cell);

                    }

                    list.add(cellValue);

                }

                values.add(list);

            }

//            }

            System.out.println(values);

        } catch (Exception e) {

            e.printStackTrace();

        }

        return values;

    }

}

 @Test

    public void testReadOne() {

        String fileUrl = "E:\\officedh\\cai\\docs\\design\\云彩游戏列表.xlsx";

        List<List<String>> values = ExcelUtil.getValues(fileUrl,1);

        String firstValue = "";

        String secondValue = "";

        int firstId = 0;

        int secondId = 0;

        List<Game> games = new ArrayList<>();

        List<GameGroup> gameGroups = new ArrayList<>();

        Timestamp timestamp = new Timestamp(System.currentTimeMillis());

        for (List<String> value : values) {

            for (int i = 0; i < value.size(); i++) {

                String v = value.get(i);

                switch (i) {

                    case 0:

                        if (!v.equals(firstValue)) {

                            firstId++;

                            secondId = 0;

                            firstValue = v;

//                                插入PlayGroup{firstId,firstValue}

                            GameGroup group = new GameGroup();

                            String gameGroupId = String.format("%02d", firstId);

                            group.setGameGroupId(Long.valueOf(gameGroupId));

//                                group.setTags(Long.valueOf(id));

                            group.setCreateTime(timestamp);

                            group.setName(firstValue);

                            group.setDescription("");

                            group.setSn("");

                            gameGroups.add(group);

                        }

                        break;

                    case 1:

                        if (!v.equals(secondValue)) {

                            secondId++;

                            secondValue = v;

                            //插入PlaySection{secondId,secondValue}

                            Game game = new Game();

                            String gameGroupId = String.format("%02d", firstId);

                            String gameId = String.format("%02d%02d", firstId, secondId);

                            game.setGameGroupId(Long.valueOf(gameGroupId));

                            game.setGameId(Long.valueOf(gameId));

                            game.setName(secondValue);

//                                game.setGameType(rank);//游戏类型，1：数字型；2：字符型；3：其他型

//                                game.setBeginTime(rank);//开奖时间

//                                game.setEndTime(rank);//闭奖时间

//                                game.setDescription(rank);//

//                                game.setDuration(rank);//开奖间隔，单位：秒

//                                game.setLockTime(rank);//封存时间，单位：秒

//                                game.setElements(rank);//号码

//                                game.setLogo(rank);//LOGO

//                                game.setSn(rank);

//                                game.setStatus(rank);

//                                game.setTags(rank);

                            game.setCreateTime(timestamp);

                            games.add(game);

                        }

                        break;

                }

            }

        }

        gameRepository.save(games);

        gameGroupRepository.save(gameGroups);

    }