package com.util;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class POIUtil {

    /**
     * 读入excel2007文件
     *
     * @param file
     * @throws IOException
     */
    public static List<String[]> readExcel(String fileName, byte[] bytes, int sheetNum) throws IOException {

        // 获取excel文件的io流
        InputStream is = new ByteArrayInputStream(bytes);
        // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
        Workbook workbook =  new XSSFWorkbook(is);;
        // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if (workbook != null) {
            // for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            // 获得当前sheet工作表
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // if (sheet == null) {
            // continue;
            // }
            // 获得当前sheet的开始行
            int firstRowNum = sheet.getFirstRowNum();
            // 获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            // 循环除了第一行的所有行
            for (int rowNum = firstRowNum + 0; rowNum <= lastRowNum; rowNum++) {
                // 获得当前行
                Row row = sheet.getRow(rowNum);
                if (row == null || row.getPhysicalNumberOfCells()==0) {
                    continue;
                }
                // 获得当前行的开始列
                int firstCellNum = row.getFirstCellNum();
                // 获得当前行的列数
                int lastCellNum = row.getPhysicalNumberOfCells();
                String[] cells = new String[row.getPhysicalNumberOfCells()];
                // 循环当前行
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    cells[cellNum] = getCellValue(cell);
                }
                list.add(cells);
            }
            // }
            workbook.close();
        }

        return list;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static Map<String, byte[]> getExcelPictures(String fileName, byte[] bytes, int sheetNum) throws IOException {
        Map<String, byte[]> map = new HashMap<String, byte[]>();

        // 获取excel文件的io流
        InputStream is = new ByteArrayInputStream(bytes);

        // 获得Workbook工作薄对象
        Workbook workbook =  new XSSFWorkbook(is);;

        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(sheetNum);

        List<POIXMLDocumentPart> list = sheet.getRelations();


        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    byte[] data = picture.getPictureData().getData();
                    map.put(key, data);
                }
            }
        }
        return map;
    }

}