import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

/**
 * @Description: Excel工具类
 * @author: Nemo
 */
public class ExcelUtils {

    /**
     * @Description: 根据列号获取对应的excel字母排序，如：A B C ... ... AA AB ... ...
     * @param columnNum excel的列号，从1开始
     * @return
     */
    public static String getColumnName(int columnNum) {
        int first;
        int last;
        String result = "";
        if (columnNum > 256)
            columnNum = 256;
        first = columnNum / 27;
        last = columnNum - (first * 26);

        if (first > 0)
            result = String.valueOf((char) (first + 64));

        if (last > 0)
            result = result + String.valueOf((char) (last + 64));

        return result;
    }

    /**
     * 将[excel列号]换成对应的XSSFCell处理类的index(从0开始)
     *
     * @param excelColumnName
     * @return
     */
    public static int getColumnIndex(String excelColumnName) {
        int num = 0;
        int result = 0;
        excelColumnName = excelColumnName.toUpperCase();
        int length = excelColumnName.length();
        for (int i = 0; i < length; i++) {
            char ch = excelColumnName.charAt(length - i - 1);
            num = (int) (ch - 'A');
            num *= Math.pow(26, i);
            result += num;
        }
        return result;
    }

    /**
     * 将[excel行号]转换成对应的XSSFRow处理类的index（从0开始）
     *
     * @param excelRowNumber
     * @return
     */
    public static int getRowIndex(int excelRowNumber) {
        return excelRowNumber - 1;
    }

    /**
     * 根据Excel行列号获取对应的行（2017版）
     *
     * @param sheet
     * @param excelRowNumber Excel行号，如：1,2,3...
     * @return
     */
    public static XSSFRow getRow(XSSFSheet sheet, int excelRowNumber) {
        XSSFRow row = sheet.getRow(getRowIndex(excelRowNumber));
        if (row == null) {
            row = sheet.createRow(getRowIndex(excelRowNumber));
        }
        return row;
    }

    /**
     * 在给定的行号之后插入一行（2017版），复制该行的样式，并获取该行
     *
     * @param sheet
     * @param startRowNumber
     * @return
     */
    public static XSSFRow shiftAndCopyRowStyle(XSSFSheet sheet, int startRowNumber) {
        sheet.shiftRows(startRowNumber, sheet.getLastRowNum(), 1);
        XSSFRow newRow = getRow(sheet, startRowNumber + 1);
        XSSFRow lastRow = getRow(sheet, startRowNumber);
        newRow.setRowStyle(lastRow.getRowStyle());
        newRow.setHeight(lastRow.getHeight());

        // 样式复制
        for (Iterator<Cell> cellIt = lastRow.cellIterator(); cellIt.hasNext();) {
            XSSFCell lastCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = newRow.createCell(lastCell.getColumnIndex());
            XSSFCellStyle newstyle = sheet.getWorkbook().createCellStyle();
            newstyle.cloneStyleFrom(lastCell.getCellStyle());
            newCell.setCellStyle(newstyle);
        }

        // 合并单元格的处理
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == lastRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress =
                    new CellRangeAddress(newRow.getRowNum(),
                        (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }

        return newRow;
    }

    /**
     * 根据Excel行、列号获取对应的单元格（2017版）
     *
     * @param sheet
     * @param excelRowNumber Excel行号，如：1,2,3...
     * @param excelColumnName Excel列号，如：A,B...AA,AB...
     * @return
     */
    public static XSSFCell getCell(XSSFSheet sheet, int excelRowNumber, String excelColumnName) {
        return getCell(getRow(sheet, excelRowNumber), excelColumnName);
    }

    /**
     * 根据Excel列号获取对应的单元格（2017版）
     *
     * @param row
     * @param excelColumnName Excel列号，如：A,B...AA,AB...
     * @return
     */
    public static XSSFCell getCell(XSSFRow row, String excelColumnName) {
        XSSFCell cell = row.getCell(getColumnIndex(excelColumnName));
        if (cell == null) {
            cell = row.createCell(getColumnIndex(excelColumnName));
        }
        return cell;
    }

    public static void main(String[] args) {
        System.out.println(getColumnIndex("AF"));
        System.out.println(getColumnName(1));
    }

}