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上傳： 針對單元格數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)格式
 * 
 *
 */
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());
            // 獲取文件指定工作表，默認(rèn)第一個
            Sheet sheet = wb.getSheetAt(0);
            // 遍歷行數(shù)(行記錄，從0開始,首行記錄數(shù)+1)
            w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                // 每一行對應(yīng)一個list
                List<String> list = new ArrayList<String>();
                // 創(chuàng)建一個行對象
                Row row = sheet.getRow(i);
                if (row == null || row.getLastCellNum() == -1) {
                    break w;
                }
                // 遍歷一行中的每個字段
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 為每一個字段創(chuàng)建一個單元格對象
                    Cell cell = row.getCell((short) (j));
                    if (cell == null) {
                        break w;
                    }
                    // 獲取cell數(shù)據(jù)
                    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());
            // 獲取文件指定工作表，默認(rèn)第一個
            Sheet sheet = wb.getSheetAt(0);
            // 遍歷行數(shù)(行記錄，從0開始,首行記錄數(shù)+1)
            w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                // 每一行對應(yīng)一個list
                List<String> list = new ArrayList<String>();
                // 創(chuàng)建一個行對象
                Row row = sheet.getRow(i);
                if (row == null || row.getLastCellNum() == -1) {
                    break w;
                }
                // 遍歷一行中的每個字段
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // 為每一個字段創(chuàng)建一個單元格對象
                    Cell cell = row.getCell((short) (j));
                    if (cell == null) {
                        break w;
                    }
                    // 獲取cell數(shù)據(jù)
                    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值轉(zhuǎn)換
     * 
     * @param cell
     * @return
     */
    public static String getCellType(Cell cell) {
        String result = null;
        switch (cell.getCellType()) {
        case XSSFCell.CELL_TYPE_NUMERIC: // 數(shù)字類型，日期類型
            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;
    }

    /**
     * 處理數(shù)值類型
     * 
     * @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.創(chuàng)建一個workbook，對應(yīng)一個Excel文件
        Workbook wb = new XSSFWorkbook();
        // 2.在workbook中添加一個sheet，對應(yīng)Excel中的一個sheet
        Sheet sheet = null;
        if (StringUtils.isBlank(sheetName)) {
            sheet = wb.createSheet("XXX表");
        } else {
            sheet = wb.createSheet(sheetName);
        }
        // 3.在sheet中添加表頭第0行，老版本poi對excel行數(shù)列數(shù)有限制short
        Row row = sheet.createRow((int) 0);
        // 4.創(chuàng)建單元格，設(shè)置值表頭，設(shè)置表頭居中
        CellStyle style = wb.createCellStyle();
        // 居中格式
        style.setAlignment(CellStyle.ALIGN_CENTER);

        // 設(shè)置表頭
        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);
        // 循環(huán)將數(shù)據(jù)寫入Excel
        for (int i = 0; i < coupons.size(); i++) {
            Coupon coupon = coupons.get(i);
            row = sheet.createRow((int) i + 1);
            // 創(chuàng)建單元格，設(shè)置值
            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;
    }
}