import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.Cell;
import jxl.Sheet;
import jxl.write.Number;

/**
 * jxl只支持 xls 格式的Excel，不支持 xlsx 格式
 */
public class JxlExcelUtil {

    /**
     * 創(chuàng)建Excel
     * @param excelFilePath
     */
    public void CreateExcel(String excelFilePath) {
        try {
            // 打開文件
            WritableWorkbook book = Workbook.createWorkbook(new File(excelFilePath));
            // 判斷sheet(0)是否已經(jīng)存在胶哲，若存在泵琳，則刪除
            if (book.getSheet("第一頁") != null) {
                book.removeSheet(0);
            }
            // 生成名為“第一頁”的工作表，參數(shù)0表示這是第一頁
            WritableSheet sheet = book.createSheet("第一頁", 0);
            // 在Label對象的構(gòu)造子中指名單元格位置是第一列第一行(0,0)
            // 以及單元格內(nèi)容為test
            Label label = new Label(0, 0, "test");
            // 將定義好的單元格添加到工作表中
            sheet.addCell(label);

            Number number = new Number(1, 0, 555.12541);
            sheet.addCell(number);
            // 寫入數(shù)據(jù)并關(guān)閉文件
            book.write();
            book.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    /**
     * 指定讀取單個(gè)Excel
     * @param exeFilePath
     */
    public void ReadExcel(String exeFilePath) {
        try {
            Workbook book = Workbook.getWorkbook(new File(exeFilePath));
            // 獲得第一個(gè)工作表對象
            Sheet sheet = book.getSheet(0);
            // 得到第一列第一行的單元格
            Cell cell1 = sheet.getCell(0, 0);
            String result = cell1.getContents();
            System.out.println(result);
            book.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    /**
     * 修改Excel內(nèi)容
     * @param exeFilePath
     */
    public void UpdateExcel(String exeFilePath){
        try {
            // Excel獲得文件
            Workbook wb = Workbook.getWorkbook(new File(exeFilePath));
            // 打開一個(gè)文件的副本已艰，并且指定數(shù)據(jù)寫回到原文件
            WritableWorkbook book = Workbook.createWorkbook(new File(exeFilePath),
                    wb);
            // 添加一個(gè)工作表
//            WritableSheet sheet = book.createSheet("第3頁", 2);
//            若”第二頁“已經(jīng)存在，在直接getSheet即可
            WritableSheet sheet = book.getSheet("3.實(shí)際結(jié)果");

            sheet.addCell(new Label(0, 0, "TestJet"));
            book.write();
            book.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    /**
     * 循環(huán)讀取Excel內(nèi)容
     * @param excelFilePath
     * */
    public StringBuilder WhileReadExcel(String excelFilePath){
        String apiurl = null;
        StringBuilder apiUrl = new StringBuilder();

        try {
            Workbook book = Workbook.getWorkbook(new File(excelFilePath));
            // 獲得第一個(gè)工作表對象
            Sheet sheet = book.getSheet(0);
            // 得到第一列第一行的單元格
            int columnum = sheet.getColumns();// 得到列數(shù)
            int rownum = sheet.getRows();// 得到行數(shù)
//            System.out.println("總列數(shù)：" + columnum);
//            System.out.println("總行數(shù)：" + rownum);
            for(int i=1;i<rownum;i++)// 循環(huán)進(jìn)行讀寫
            {
                Cell cellBaseUrl = sheet.getCell(0,i);
                String baseUrl = cellBaseUrl.getContents();

                Cell cellCity = sheet.getCell(1,i);
                String city = cellCity.getContents();

                Cell cellLanguage = sheet.getCell(2,i);
                String language = cellLanguage.getContents();

                apiurl = baseUrl + city + "&language=" + language + "&unit=c" + "\n";
//                System.out.print(apiurl);

                // 直接String apiurl, print(apiurl)是可以全部循環(huán)值；
                // 但是后面的return時(shí)手幢，如果break,則返回第一個(gè)值；若continue忱详，則返回循環(huán)的最后一個(gè)值
                // 必須使用數(shù)組的形式围来，后面的return才可以返回所有的值
                apiUrl.append(apiurl);

//                break;
//                continue;

//                獲取sheet中所有內(nèi)容
//                for (int j = 0; j < columnum; j++) {
//                    Cell cellN = sheet.getCell(j, i);
//                    String result = cellN.getContents();
//                    System.out.print(result);
//                    System.out.print("\t");
//                }
//                System.out.println();
            }
            book.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }return apiUrl;
    }


    /**
     * 完全讀取 1張excel表 數(shù)據(jù)到 另一張excel表中
     * @param excelFilePath
     * @param excelFilePathNew
     */
    public void WriteExcel1ToExcel2(String excelFilePath,String excelFilePathNew){
        try {
            Workbook book = Workbook.getWorkbook(new File(excelFilePath));
            Sheet sheet = book.getSheet(0);

            WritableWorkbook book2 = Workbook.createWorkbook(new File(excelFilePathNew));
            WritableSheet sheet2 = book2.createSheet("第一頁", 0);

            int columnum = sheet.getColumns();
            int rownum = sheet.getRows();
            for (int i = 0; i < rownum; i++)
            {
                for (int j = 0; j < columnum; j++)
                {
                    Cell cellN = sheet.getCell(j, i);
                    String result ;
                    result = cellN.getContents();
                    System.out.print(result);
                    System.out.print("\t");
                    Label label = new Label(j, i, result);
                    sheet2.addCell(label);
                }
                System.out.println();
            }
            book2.write();
            book2.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    /**
     * 完全讀取 excel表sheet1 數(shù)據(jù)到 該excel表sheet2 中
     * @param excelFilePath
     */
    public void WriteSheet1ToSheet2(String excelFilePath){
        try {
            Workbook wb = Workbook.getWorkbook(new File(excelFilePath));
            Sheet sheet1 = wb.getSheet(1);
            WritableWorkbook book = Workbook.createWorkbook(new File(excelFilePath),
                    wb);
            if (book.getSheet("3.實(shí)際結(jié)果") != null || book.getSheet("4.比對結(jié)果") != null) {
                book.removeSheet(2);
                book.removeSheet(3);
                }
            WritableSheet sheet2 = book.createSheet("3.實(shí)際結(jié)果", 2);
            WritableSheet sheet3 = book.createSheet("4.比對結(jié)果", 3);
            int columNum = sheet1.getColumns();
            for (int j=0;j<columNum;j++){
                Cell cell1 = sheet1.getCell(j,0);
                String result1 = cell1.getContents();
                Label label = new Label(j,0,result1);
                sheet2.addCell(label);
            }

            for (int y=0;y<columNum;y++){
                Cell cell1 = sheet1.getCell(y,0);
                String result1 = cell1.getContents();
                Label label = new Label(y,0,result1);
                sheet3.addCell(label);
            }

            // 整表sheet數(shù)據(jù)復(fù)制
//            int columNum = sheet0.getColumns();
//            int rownum = sheet0.getRows();
//            for (int i = 0; i < rownum; i++){
//                for (int j = 0; j < columNum; j++) {
//                    Cell cellN = sheet0.getCell(j, i);
//                    String result = cellN.getContents();
//                    System.out.print(result);
//                    System.out.print("\t");
//                    Label label = new Label(j, i, result);
//                    sheet.addCell(label);
//                }
//                System.out.println();
//            }
            book.write();
            book.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }


    public static void main(String[] args) {
        String excelFilePath = "src/main/java/data/testXinZhi.xls";
//        String excelFilePathNew = "src/main/java/data/test.xls";

        JxlExcelUtil excel = new JxlExcelUtil();
//        excel.CreateExcel();
//        excel.ReadExcel();
//        excel.UpdateExcel();
//        excel.WhileReadExcel(excelFilePath);
//        excel.WriteExcel1ToExcel2(excelFilePath,excelFilePathNew);
        excel.WriteSheet1ToSheet2(excelFilePath);
    }
}