package com.rehome.jpahefengweather.service.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    private String filePath = null;

    public void testWriteExcel(){
        Map<String, String> dataMap=new HashMap<String, String>();
        dataMap.put("BankName", "中国农业银行");
        dataMap.put("Addr", "凤凰北路28号");
        dataMap.put("Phone", "07568329823");
        List<Map> list=new ArrayList<Map>();
        list.add(dataMap);
        Map<String, String> dataMap1=new HashMap<String, String>();
        dataMap1.put("BankName", "中国工商银行");
        dataMap1.put("Addr", "凤凰北路38号");
        dataMap1.put("Phone", "07568329833");
        list.add(dataMap1);
        writeExcel(list, 3, this.filePath);

        //writeExcel();
    }

    public WriteExcel(String dir,String fileName){
        if(dir!=null&&fileName!=null){
            this.filePath = dir+File.separator+fileName;
        }
    }

    public void writeExcel(){
        try {
            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook();

            // Get first/desired sheet from the workbook
            XSSFSheet sheet = createSheet(workbook, "Sheet 1", false);

            // XSSFSheet sheet = workbook.getSheetAt(1);//Don't use this line
            // because you get Sheet index (1) is out of range (no sheets)

            //Write some information in the cells or do what you want
            XSSFRow row1 = sheet.createRow(0);
            XSSFCell r1c2 = row1.createCell(0);
            r1c2.setCellValue("NAME");
            XSSFCell r1c3 = row1.createCell(1);
            r1c3.setCellValue("AGE");


            //Save excel to HDD Drive
            File pathToFile = new File(this.filePath);
            if (!pathToFile.exists()) {
                pathToFile.createNewFile();
            }
            FileOutputStream fos = new FileOutputStream(pathToFile);
            workbook.write(fos);
            fos.close();
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
        try {
            // 获取总列数
            int columnNumCount = cloumnCount;
            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook();

            // Get first/desired sheet from the workbook
            XSSFSheet sheet = createSheet(workbook, "Sheet 1", false);

            /**
             * 删除原有数据，除了属性列
             */
            int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
            System.out.println("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }


            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < dataList.size(); j++) {
                if(j==0){
                    // 创建一行：从第二行开始，跳过属性列
                    Row row0 = sheet.createRow(j );
                    for (int k = 0; k <= columnNumCount; k++) {
                        // 在一行内循环
                        Cell first = row0.createCell(0);
                        first.setCellValue("银行名称");

                        Cell second = row0.createCell(1);
                        second.setCellValue("地址");

                        Cell third = row0.createCell(2);
                        third.setCellValue("电话");
                    }
                }
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 1);
                // 得到要插入的每一条记录
                Map dataMap = dataList.get(j);
                String name = dataMap.get("BankName").toString();
                String address = dataMap.get("Addr").toString();
                String phone = dataMap.get("Phone").toString();
                for (int k = 0; k <= columnNumCount; k++) {
                    // 在一行内循环
                    Cell first = row.createCell(0);
                    first.setCellValue(name);

                    Cell second = row.createCell(1);
                    second.setCellValue(address);

                    Cell third = row.createCell(2);
                    third.setCellValue(phone);
                }
            }


            //Save excel to HDD Drive
            File pathToFile = new File(finalXlsxPath);
            if (!pathToFile.exists()) {
                pathToFile.createNewFile();
            }
            FileOutputStream fos = new FileOutputStream(pathToFile);
            workbook.write(fos);
            fos.close();
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static XSSFSheet createSheet(XSSFWorkbook wb, String prefix, boolean isHidden) {
        XSSFSheet sheet = null;
        int count = 0;

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            String sName = wb.getSheetName(i);
            if (sName.startsWith(prefix))
                count++;
        }

        if (count > 0) {
            sheet = wb.createSheet(prefix + count);
        } else
            sheet = wb.createSheet(prefix);

        if (isHidden)
            wb.setSheetHidden(wb.getNumberOfSheets() - 1, XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);

        return sheet;
    }
}