Một số hàm Java chuyển đổi CSV thành EXCEL. | A some Java Function convert CSV to EXCEL.

Đây là một số hàm chuyển đổi tập tinh CSV thành EXCEL trong một ứng dụng hỗ trợ mình thực hiện cho dự án, do không quan trong nên viết đại cho xong, vì vây có S chuối, nhưng để tham khảo thì cũng tam được. Bạn nào thấy hữu ích thì cức cut dán về sử dụng ^^!

Convert CSV to XLS function:

    private void convert_CSV_XLS(String path, String delimiter) throws IOException {
        ArrayList arList=null;
        ArrayList al=null;
        //String fName = "test.csv";
        String fName = path;
        String thisLine;
        int count=0;
       
        FileInputStream fis = new FileInputStream(fName);
        DataInputStream myInput = new DataInputStream(fis);
       
        int i=0;
        arList = new ArrayList();
        while ((thisLine = myInput.readLine()) != null)
        {
            al = new ArrayList();
            //String strar[] = thisLine.split(",");
            String strar[] = thisLine.split(delimiter);
            al.addAll(Arrays.asList(strar));
            arList.add(al);
            //System.out.println();
            i++;
        }

        try
        {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("Report_01_21");
            for(int k=0;k<arList.size();k++)
            {
                ArrayList ardata = (ArrayList)arList.get(k);
                HSSFRow row = sheet.createRow((short) 0+k);
                for(int p=0;p<ardata.size();p++)
                {

                    HSSFCell cell = row.createCell((short) p);
                    String data = ardata.get(p).toString();
                    if(data.startsWith("=")) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data=data.replaceAll("\"", "");
                        data=data.replaceAll("=", "");
                        cell.setCellValue(data);
                    }else if(data.startsWith("\"")) {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }else {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(data);
                    }
                    //*/
                    // cell.setCellValue(ardata.get(p).toString());
                }
               
                System.out.println();
            }
           
            //FileOutputStream fileOut = new FileOutputStream("test.xls");
            FileOutputStream fileOut = new FileOutputStream(path);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated");
        } catch ( Exception ex ) {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Error warning",JOptionPane.ERROR_MESSAGE);
            ex.printStackTrace();
        } //main method ends
    }

Convert CSV to XLS 001:

    private void convert_CSV_XLS_001(String path, String delimiter) throws IOException {
        ArrayList arList=null;
        ArrayList al=null;
        //String fName = "test.csv";
        String fName = path;
        String thisLine;
        int count=0;
       
        FileInputStream fis = new FileInputStream(fName);
        DataInputStream myInput = new DataInputStream(fis);
       
        int i=0;
        arList = new ArrayList();
        while ((thisLine = myInput.readLine()) != null)
        {
            al = new ArrayList();
            //String strar[] = thisLine.split(",");
            String strar[] = thisLine.split(delimiter);
            al.addAll(Arrays.asList(strar));
            arList.add(al);
            //System.out.println();
            i++;
        }

        try
        {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("Batch_Transmission_Summary_Contact_Sheet");
           
            CellStyle style_border = hwb.createCellStyle();
            style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);
           
            CellStyle style_font_01 = hwb.createCellStyle();
            HSSFFont font_1 = hwb.createFont();
            font_1.setFontHeightInPoints((short)14);
            font_1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style_font_01.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style_font_01.setFont(font_1);     
           
            CellStyle style_font_02 = hwb.createCellStyle();
            //style_font_02.setFillBackgroundColor(HSSFColor.RED.index);
            style_font_02.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            style_font_02.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style_font_02.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderLeft(HSSFCellStyle.BORDER_THIN);           
            style_font_02.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font_2 = hwb.createFont();
            font_2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style_font_02.setFont(font_2);    
           
            CellStyle style_font_03 = hwb.createCellStyle();
            HSSFFont font_3 = hwb.createFont();
            font_3.setColor(HSSFColor.RED.index);
            font_3.setItalic(true);
            style_font_03.setFont(font_3);              
           
            CellStyle style_font_04 = hwb.createCellStyle();
            HSSFFont font_4 = hwb.createFont();
            //font_4.setColor(HSSFColor.RED.index);
            font_4.setItalic(true);
            style_font_04.setFont(font_4);               
           
            CellStyle style_font_05 = hwb.createCellStyle();
            style_font_05.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderLeft(HSSFCellStyle.BORDER_THIN);           
            style_font_05.setAlignment(HSSFCellStyle.ALIGN_CENTER);                
           
            for(int k=0;k<arList.size();k++)
            {
                ArrayList ardata = (ArrayList)arList.get(k);
                HSSFRow row = sheet.createRow((short) 0+k);
                for(int p=0;p<ardata.size();p++)
                {

                    HSSFCell cell = row.createCell((short) p);
                    String data = ardata.get(p).toString();
                    if(data.startsWith("=")) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data=data.replaceAll("\"", "");
                        data=data.replaceAll("=", "");
                        cell.setCellValue(data);
                    }else if(data.startsWith("\"")) {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }else {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(data);
                    }
                    //*/
                    // cell.setCellValue(ardata.get(p).toString());
                   
                    //*/
                    // cell.setCellValue(ardata.get(p).toString());
                    if (k == 0) {
                        cell.setCellStyle(style_font_02);
                        //cell.setCellStyle(style_border);
                    }
                   
                    if (k > 0) {
                        //cell.setCellStyle(style_font_02);                       
                        cell.setCellStyle(style_border);
                    }                   
                   
                   
                   
                   
                }
               
                System.out.println();
            }      
           
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(5);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);             
            sheet.autoSizeColumn(8); 
            sheet.autoSizeColumn(9); 
            sheet.autoSizeColumn(10); 
            sheet.autoSizeColumn(11);             
            sheet.autoSizeColumn(12);                         
            sheet.autoSizeColumn(13); 
            sheet.autoSizeColumn(14); 
            sheet.autoSizeColumn(15); 
            sheet.autoSizeColumn(16);             
           
           
            //FileOutputStream fileOut = new FileOutputStream("test.xls");
            FileOutputStream fileOut = new FileOutputStream(path);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated");
        } catch ( Exception ex ) {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Error warning",JOptionPane.ERROR_MESSAGE);
            ex.printStackTrace();
        } //main method ends
    }    

Convert CSV to XLS 002:

    private void convert_CSV_XLS_report_Hand_Off(String path, String delimiter) throws IOException {
       
        ArrayList arList=null;
        ArrayList al=null;
        //String fName = "test.csv";
        String fName = path;
        String thisLine;
        int count=0;
       
        FileInputStream fis = new FileInputStream(fName);
        DataInputStream myInput = new DataInputStream(fis);
       
        int i=0;
        arList = new ArrayList();
        boolean check_appID = false;
        int tmp_nr = 1;
        int tmp_nr_01 = 1;
       
        int tmp_page = 1;
        while ((thisLine = myInput.readLine()) != null)
        {
            al = new ArrayList();
            //String strar[] = thisLine.split(",");
            String strar[] = thisLine.split(delimiter, -1);

            System.out.println("Line " + i + " : " + strar[2]);
           
            if (i >= 1)
            {
//                if (!"1".equals(strar[0])) {
//                    check_appID = true;
//                }
//               
//                if (check_appID) {
                    strar[0] = Integer.toString(tmp_nr_01);
                   
                    StringBuilder tmp = new StringBuilder(strar[2]);
                    tmp = tmp.replace(0, 7, String.format("%07d", tmp_nr));
                    strar[2] = tmp.toString();
                   
                    if (strar[5].equals("Supp")) {
                        tmp_nr--;
                        StringBuilder tmp_01 = new StringBuilder(strar[2]);
                        tmp_01 = tmp_01.replace(0, 7, String.format("%07d", tmp_nr));                       
                        strar[2] = tmp_01.toString();
                    }                   
                   
                    if (strar[10].equals("MISSING") || strar[10].equals("Scan only")) {
                        strar[2] = "";
                    }
                    else {
                        tmp_nr++;
                    }
                   
                    System.out.println("Status: " + strar[10]);                   
                   
//                }
                   
                tmp_nr_01++;
            }
           
            System.out.println("Size start[]: " + strar.length);
           
            al.addAll(Arrays.asList(strar));
            arList.add(al);
            System.out.println();
            i++;
           

        }
       

       
        String strar_tmp_00[] = "                                ".split(delimiter);
        String strar_tmp_01[] = "            Summary                                 ".split(delimiter);
       
        String tmp_02 = "            Complete    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"Complete\")                            ";
        String strar_tmp_02[] = tmp_02.split(delimiter);
       
        String tmp_03 = "            Incomplete    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"Incomplete\")                            ";
        String strar_tmp_03[] = tmp_03.split(delimiter);
       
        String tmp_04 = "            Missing    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"Missing\")                            ";
        String strar_tmp_04[] = tmp_04.split(delimiter);
       
        String tmp_05 = "            Scan only    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"Scan only\")                            ";
        String strar_tmp_05[] = tmp_05.split(delimiter);
       
        String tmp_06 = "            Return    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"Return\")                            ";
        String strar_tmp_06[] = tmp_06.split(delimiter);
       
        String tmp_07 = "            None    =COUNTIF(K$1:K$" + Integer.toString(i) + ",\"None\")                            ";
        String strar_tmp_07[] = tmp_07.split(delimiter);
       
        String tmp_08 = "            Total    =SUM(E$" + Integer.toString(i+4) + ":E$" + Integer.toString(i+9) + ")                            ";
        String strar_tmp_08[] = tmp_08.split(delimiter);
       
        String strar_tmp_09[] = "                                ".split(delimiter);
        String strar_tmp_10[] = "            Representative    Acknowledgement                   ".split(delimiter);
        String strar_tmp_11[] = "                 Name    Signature                  ".split(delimiter);
        String strar_tmp_12[] = "            Citibank CI    Nguyen Oanh                   ".split(delimiter);
        String strar_tmp_13[] = "            Partner Delivery    Duong Tri                   ".split(delimiter);

        ArrayList alal_tmp = null;
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_00));
        arList.add(alal_tmp);       
        arList.add(alal_tmp);       
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_01));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_02));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_03));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_04));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_05));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_06));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_07));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_08));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_09));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_10));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_11));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_12));
        arList.add(alal_tmp);
       
        alal_tmp = new ArrayList();
        alal_tmp.addAll(Arrays.asList(strar_tmp_13));
        arList.add(alal_tmp);
       
        try
        {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("Handoff_Summary_Sheet");

            CellStyle style_border = hwb.createCellStyle();
            style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style_border.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            style_border.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
           
            for(int k=0;k<arList.size();k++)
            {
                ArrayList ardata = (ArrayList)arList.get(k);
                HSSFRow row = sheet.createRow((short) 0+k);
                for(int p=0;p<ardata.size();p++)
                {

                    HSSFCell cell = row.createCell((short) p);
                   
                    String data = ardata.get(p).toString();
                    if(data.startsWith("=")) {
                        cell.setCellType(Cell.CELL_TYPE_FORMULA);
                        //data=data.replaceAll("\"", "");
                        data=data.replaceAll("=", "");
                        //cell.setCellValue(data);
                        String StrFormula = data;
                        cell.setCellFormula(StrFormula);
                    }else if(data.startsWith("\"")) {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }else {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }
                    //*/----------------------------------------------
                   
                    if (k <= i) {
                        cell.setCellStyle(style_border);                     
                    }
                   
                    if (!"".equals(data) && k > i) {
                        cell.setCellStyle(style_border);
                    }
                   
                    if (k == arList.size()-2) {
                        row.setHeightInPoints(50);
                       
                    }
                   
                    if (k == arList.size()-1) {
                        int end_row = arList.size() - 1;
                        sheet.addMergedRegion(new CellRangeAddress(end_row - 12, end_row - 12, 3, 4));
                        sheet.addMergedRegion(new CellRangeAddress(end_row - 3, end_row - 3, 4, 7));
                        sheet.addMergedRegion(new CellRangeAddress(end_row - 2, end_row - 2, 5, 7));
                        sheet.addMergedRegion(new CellRangeAddress(end_row - 1, end_row - 1, 5, 7));
                        sheet.addMergedRegion(new CellRangeAddress(end_row, end_row, 5, 7));
                       
                        row.setHeightInPoints(50);
                       
                    }
                   
                   
                }
               
                System.out.println();
            }
           
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(5);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);             
            sheet.autoSizeColumn(8); 
            sheet.autoSizeColumn(9);             
            sheet.autoSizeColumn(10);                         
           
            //FileOutputStream fileOut = new FileOutputStream("test.xls");
            FileOutputStream fileOut = new FileOutputStream(path);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated");
        } catch ( Exception ex ) {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Error warning",JOptionPane.ERROR_MESSAGE);
            ex.printStackTrace();
        } //main method ends
       
    }

Convert CSV to XLS 003:

    private void convert_CSV_XLS_report_01_21(String path, String delimiter) throws IOException {
        ArrayList arList=null;
        ArrayList al=null;

        String fName = path;
        String thisLine;
        int count=0;
       
        int i=0;
        arList = new ArrayList();       
       
        //------------------------
        FileInputStream fis_header;
        String tmp_name = fName.substring(fName.lastIndexOf("\\")+1,fName.lastIndexOf("\\")+4);
        System.out.println(tmp_name);
       
        if ("01.".equals(tmp_name)) {
            fis_header = new FileInputStream("P:\\Production_Administration\\SPS\\IDEAS\\Progress\\Project\\Citi_Credit_Initiation\\Tool_Support\\header_01.csv");
        } else {
            fis_header = new FileInputStream("P:\\Production_Administration\\SPS\\IDEAS\\Progress\\Project\\Citi_Credit_Initiation\\Tool_Support\\header_21.csv");
        }
       
        BufferedReader myInput_header = new BufferedReader(new InputStreamReader(fis_header, "UTF8"));       

        while ((thisLine = myInput_header.readLine()) != null)
        {
            al = new ArrayList();
            //String strar[] = thisLine.split(",");
            String strar[] = thisLine.split(delimiter, -1);
           
            al.addAll(Arrays.asList(strar));
            arList.add(al);
            System.out.println();
            i++;
        }
       
        //-----------------------       
        FileInputStream fis = new FileInputStream(fName);
        BufferedReader myInput = new BufferedReader(new InputStreamReader(fis, "UTF8"));       

        boolean tmp_bl_apID = false;
        int page = 1;
        while ((thisLine = myInput.readLine()) != null)
        {
            al = new ArrayList();
            //String strar[] = thisLine.split(",");
            String strar[] = thisLine.split(delimiter, -1);
           
            if (i == 18)
            {
                if (!strar[0].equals("1")) {
                    tmp_bl_apID = true;
                }
                               
            }
           
            if (tmp_bl_apID) {
                strar[0] = Integer.toString(page);
                page++;
            }

            System.out.println("Line " + i + " : " + strar[0]);
           
            al.addAll(Arrays.asList(strar));
            arList.add(al);
            System.out.println();
            i++;
           
        }

        try
        {
            HSSFWorkbook hwb = new HSSFWorkbook();
            HSSFSheet sheet = hwb.createSheet("Report_01_21");
           
            CellStyle style_border = hwb.createCellStyle();
            style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);
           
            CellStyle style_font_01 = hwb.createCellStyle();
            HSSFFont font_1 = hwb.createFont();
            font_1.setFontHeightInPoints((short)14);
            font_1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style_font_01.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style_font_01.setFont(font_1);     
           
            CellStyle style_font_02 = hwb.createCellStyle();
            style_font_02.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_font_02.setBorderLeft(HSSFCellStyle.BORDER_THIN);           
            style_font_02.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font_2 = hwb.createFont();
            font_2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style_font_02.setFont(font_2);    
           
            CellStyle style_font_03 = hwb.createCellStyle();
            HSSFFont font_3 = hwb.createFont();
            font_3.setColor(HSSFColor.RED.index);
            font_3.setItalic(true);
            style_font_03.setFont(font_3);              
           
            CellStyle style_font_04 = hwb.createCellStyle();
            HSSFFont font_4 = hwb.createFont();
            //font_4.setColor(HSSFColor.RED.index);
            font_4.setItalic(true);
            style_font_04.setFont(font_4);               
           
            CellStyle style_font_05 = hwb.createCellStyle();
            style_font_05.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style_font_05.setBorderLeft(HSSFCellStyle.BORDER_THIN);           
            style_font_05.setAlignment(HSSFCellStyle.ALIGN_CENTER);
           
            for(int k=0;k<arList.size();k++)
            {
                ArrayList ardata = (ArrayList)arList.get(k);
                HSSFRow row = sheet.createRow((short) 0+k);
                for(int p=0;p<ardata.size();p++)
                {
                   

                    HSSFCell cell = row.createCell((short) p);
                    String data = ardata.get(p).toString();
                   
                    if (k == 0 && p == 0) {
                        data = data.substring(1);
                    }                   
                   
                    if(data.startsWith("=")) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data=data.replaceAll("\"", "");
                        data=data.replaceAll("=", "");
                        cell.setCellValue(data);
                    }else if(data.startsWith("\"")) {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }else {
                        data=data.replaceAll("\"", "");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data);
                    }
                   
                    //*/
                    // cell.setCellValue(ardata.get(p).toString());
                    if (k == 0 && p == 0) {
                        cell.setCellStyle(style_font_01);
                    }
                   
                    if (k == 1 || k == 4 || k == 5 || k == 6 || k == 15) {
                        cell.setCellStyle(style_font_03);
                    }
                   
                    //if (k == 2 || k == 3) {
                        //cell.setCellStyle(style_font_04);
                    //}                   
                   
                    if (k == 17) {
                        cell.setCellStyle(style_font_02);                       

                    }                   
                   
                    if (k > 17) {
                        if (p == 0 || p == 1) {
                            cell.setCellStyle(style_font_05);
                        } else {
                            cell.setCellStyle(style_border);
                        }
                    }                  
                   
                }
               
                System.out.println();
               
            }
           

           
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
           
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));
            sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 6));
            sheet.addMergedRegion(new CellRangeAddress(5, 5, 2, 6));
            sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 6));
           
            sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 6));
            sheet.addMergedRegion(new CellRangeAddress(8, 8, 3, 6));           
            sheet.addMergedRegion(new CellRangeAddress(9, 9, 3, 6));
            sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 6));           
            sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 6));
            sheet.addMergedRegion(new CellRangeAddress(12, 12, 3, 6));
            sheet.addMergedRegion(new CellRangeAddress(13, 13, 3, 6));
            sheet.addMergedRegion(new CellRangeAddress(14, 14, 3, 6));           
           
            sheet.addMergedRegion(new CellRangeAddress(15, 15, 2, 6));

            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(5);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);           
           
            //FileOutputStream fileOut = new FileOutputStream("test.xls");
            FileOutputStream fileOut = new FileOutputStream(path);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated");
        } catch ( Exception ex ) {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Error warning",JOptionPane.ERROR_MESSAGE);
            ex.printStackTrace();
        } //main method ends
    }