package test.job.day1130;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {

    private File createExcelFile(String path,String fileName)throws Exception{

        InputStream in = null;

        OutputStream out = null;

        File excelFile = createNewFile(path,fileName);

        //System.out.println(excelFile.getName());

        //模版

        File templateFile = new File(path+"/template","template.xls");

        if(!templateFile.exists())

            throw new Exception("模版文件不存在");

        //System.out.println(templateFile.getName());

        try{

            in = new BufferedInputStream(new FileInputStream(templateFile),1024);

            out = new BufferedOutputStream(new FileOutputStream(excelFile),1024);

            byte[] buffer = new byte[1024];

            int len;

            while((len=in.read(buffer)) != -1){

                out.write(buffer,0,len);

                out.flush();

            }

        }finally{

            if(in != null)

                in.close();

            if(out != null)

                out.close();

        }

        return excelFile;

    }

    /*初始化excel文件*/

    private void  initExcelFile(File excelFile,String prefix)throws Exception{

        InputStream is = null;

        OutputStream out = null;

        HSSFWorkbook workbook = null;

        HSSFSheet sheet = null;

        is = new FileInputStream(excelFile);

        workbook = new HSSFWorkbook(is);

        String suffix = "";

        //获取第一个sheet

        sheet = workbook.getSheetAt(0);

        if(sheet != null){

            //写数据

            for(int i=0;i<399;i++){

                HSSFRow row = sheet.createRow(i);

                HSSFCell cell = row.createCell(0);

                if(i == 0){

                    cell.setCellValue("帐号");

                    cell = row.createCell(1);

                    cell.setCellValue("密码");

                    continue;

                }

                if(i < 10){

                    suffix = "00" + i;

                }

                else if(i < 100){

                    suffix = "0" + i;

                }

                else{

                    suffix = i + "";

                }

                cell.setCellValue(prefix + suffix);

                cell = row.createCell(1);

                cell.setCellValue("000000");

            }

            out = new FileOutputStream(excelFile);

            workbook.write(out);

        }

        out.flush();

        out.close();

    }

    private File createNewFile(String path,String fileName)throws Exception{

        File newFile = new File(path,fileName);

        if(!newFile.exists())

            newFile.createNewFile();

        return newFile;

    }

    public static void main(String[] args)throws Exception{        

        String path = "d:/excelFiles";

        String fileName = "";

        String prefix = "";

        String tmpStr = "";

        //char[] charArr = {'A','B','C','D','E','F','G','H','I','J'};

        char[] charArr = {'O','P','Q'};

        long t0 = System.currentTimeMillis();

        for(int i=0;i<charArr.length;i++){

            for(int j=0;j<100;j++){

                if(j<10){

                    tmpStr = "0" + j;

                }else{

                    tmpStr = "" + j;

                }

                prefix = charArr[i] + tmpStr;

                fileName = "file" + prefix + ".xls";

                ExcelUtil eu = new ExcelUtil();

                System.out.println("正在创建 " + fileName + "文件..");

                File f = eu.createExcelFile(path,fileName);

                eu.initExcelFile(f,prefix);

            }

        }

        long t1 = System.currentTimeMillis();

        System.out.println("耗时：" + (t1-t0)/1000 + "秒钟");

//        String fileName = "file000.xls";

//        ExcelUtil eu = new ExcelUtil();

//        File f = eu.createExcelFile(path,fileName);

//        eu.initExcelFile(f,"a00");

    }

}