package com.kaishengit.excel;

import com.kaishengit.pojo.ShopDTO;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel上传： 针对单元格数据类型转换数据格式
 * 
 *
 */
public class ExcelUtils {

    public static List<Ticket> readTickets(MultipartFile file, Integer matterId) throws Exception {
        if(file == null || file.getSize() == 0)
            return null;
        if(matterId == null)
            return null;
        List<Ticket> tickets = new ArrayList<Ticket>();
        // 文件名
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(".xlsx")||fileName.endsWith(".xls")) {
            // 获取Excel文件对象
            Workbook wb = WorkbookFactory.create(file.getInputStream());
            // 获取文件指定工作表，默认第一个
            Sheet sheet = wb.getSheetAt(0);
            // 遍历行数(行记录，从0开始,首行记录数+1)
            w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                // 每一行对应一个list
                List<String> list = new ArrayList<String>();
                // 创建一个行对象
                Row row = sheet.getRow(i);
                if (row == null || row.getLastCellNum() == -1) {
                    break w;
                }
                // 遍历一行中的每个字段
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 为每一个字段创建一个单元格对象
                    Cell cell = row.getCell((short) (j));
                    if (cell == null) {
                        break w;
                    }
                    // 获取cell数据
                    String data = getCellType(cell);
                    list.add(data);
                }
                Ticket ticket = new Ticket();
                ticket.setSncode(list.get(0));// 卡券券码
                ticket.setMatterId(matterId);
                ticket.setAllotFlag(Boolflag.FALSE);
                ticket.setTakeFlag(Boolflag.FALSE);
                tickets.add(ticket);
            }
            wb.close();
        }
        return tickets;
    }
    
    public static List<ShopDTO> readLotteriers(MultipartFile file) throws Exception {
        if(file == null || file.getSize() == 0)
            return null;
        List<ShopDTO> lotteriers = new ArrayList<ShopDTO>();
        // 文件名
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(".xlsx")||fileName.endsWith(".xls")) {
            // 获取Excel文件对象
            Workbook wb = WorkbookFactory.create(file.getInputStream());
            // 获取文件指定工作表，默认第一个
            Sheet sheet = wb.getSheetAt(0);
            // 遍历行数(行记录，从0开始,首行记录数+1)
            w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                // 每一行对应一个list
                List<String> list = new ArrayList<String>();
                // 创建一个行对象
                Row row = sheet.getRow(i);
                if (row == null || row.getLastCellNum() == -1) {
                    break w;
                }
                // 遍历一行中的每个字段
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 为每一个字段创建一个单元格对象
                    Cell cell = row.getCell((short) (j));
                    if (cell == null) {
                        break w;
                    }
                    // 获取cell数据
                    String data = getCellType(cell);
                    list.add(data);
                }
                /*Lotterier lotterier = new Lotterier();
                lotterier.setAccount(list.get(0));// 用户账号
                lotterier.setAllotFlag(Boolflag.FALSE);
                lotteriers.add(lotterier);*/
                ShopDTO shopDTO = new ShopDTO();
                shopDTO.setShopId(Integer.valueOf(list.get(0)));
                shopDTO.setShopName(list.get(1));
                shopDTO.setAddress(list.get(2));
                lotteriers.add(shopDTO);
                System.out.println("--------------------------");
                System.out.println(list.get(0));
                System.out.println(list.get(1));
                System.out.println(list.get(2));

            }
            wb.close();
        }
        return lotteriers;
    }

    /**
     * EXCEL中的CELL值转换
     * 
     * @param cell
     * @return
     */
    public static String getCellType(Cell cell) {
        String result = null;
        switch (cell.getCellType()) {
        case XSSFCell.CELL_TYPE_NUMERIC: // 数字类型，日期类型
            result = dealNum(cell);
            break;
        case XSSFCell.CELL_TYPE_STRING: // 字符串类型
            result = cell.getStringCellValue();
            result = dealStr(result);
            break;
        case XSSFCell.CELL_TYPE_FORMULA: // 公式
            result = cell.getCellFormula();
            break;
        default:
            result = "";
            break;
        }
        return result;
    }

    /**
     * 处理数值类型
     * 
     * @param cell
     * @return
     */
    public static String dealNum(Cell cell) {
        String str = "";
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            Date cellDate = cell.getDateCellValue();
            //str = TimeUtils.formatAll(cellDate);
        } else {
            str = new DecimalFormat("0").format(cell.getNumericCellValue());
        }
        return str;
    }

    /**
     * 处理字符串
     * 
     * @param str
     * @return
     */
    public static String dealStr(String str) {
        if (str.trim().equals("") || str.trim().length() < 0) {
            str = "";
        }
        return str;
    }
    
    public static byte[] createExcel(List<Coupon> coupons, String sheetName){
        byte[] byteArray = null;
        // 1.创建一个workbook，对应一个Excel文件
        Workbook wb = new XSSFWorkbook();
        // 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        Sheet sheet = null;
        if (StringUtils.isBlank(sheetName)) {
            sheet = wb.createSheet("XXX表");
        } else {
            sheet = wb.createSheet(sheetName);
        }
        // 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
        Row row = sheet.createRow((int) 0);
        // 4.创建单元格，设置值表头，设置表头居中
        CellStyle style = wb.createCellStyle();
        // 居中格式
        style.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置表头
        Cell cell = row.createCell(0);
        cell.setCellValue("卡券号码");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("卡券密码");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("主题码");
        cell.setCellStyle(style);
        // 循环将数据写入Excel
        for (int i = 0; i < coupons.size(); i++) {
            Coupon coupon = coupons.get(i);
            row = sheet.createRow((int) i + 1);
            // 创建单元格，设置值
            row.createCell(0).setCellValue(coupon.getCouponCode());
            row.createCell(1).setCellValue(coupon.getCouponPwd());
            row.createCell(2).setCellValue(coupon.getThemeCode());
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
            byteArray = os.toByteArray();
        } catch (IOException e1) {
            e1.printStackTrace();
        }finally {
            try {
                wb.close();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return byteArray;
    }
}