Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:controller.DAORequest.java

public void saveRequest() {

    XSSFSheet sheet = workbook.getSheetAt(0);
    sheet.getRow(0).createCell(10).setCellValue("Id solicitante");
    sheet.getRow(0).createCell(11).setCellValue("Nombre solicitante");
    sheet.getRow(0).createCell(12).setCellValue("Estado de solicitud");
    sheet.getRow(0).createCell(13).setCellValue("Num de Resolucion");
    int rowI = 1;
    for (Object o : School.getInstance().selectAllRequests()) {

        Request r = (Request) o;
        Row row = sheet.createRow(rowI);
        ///*from  w ww.  j  av a 2 s .  c  o  m*/
        Cell cellDate = row.createCell(0);
        cellDate.setCellValue(r.getDate());
        CellStyle styleCreationDate = workbook.createCellStyle();
        XSSFDataFormat dfCreationDate = workbook.createDataFormat();
        styleCreationDate.setDataFormat(dfCreationDate.getFormat("d/m/yy"));
        cellDate.setCellStyle(styleCreationDate);
        //
        Cell cellCarnet = row.createCell(1);
        cellCarnet.setCellValue(r.getAffected().getId());
        CellStyle styleCreationInt = workbook.createCellStyle();
        XSSFDataFormat dfCreationInt = workbook.createDataFormat();

        Cell cellName = row.createCell(2);
        cellName.setCellValue(r.getAffected().getName());
        Cell cellEmail = row.createCell(3);
        cellEmail.setCellValue(r.getAffected().getEmail());
        Cell cellPhone = row.createCell(4);
        cellPhone.setCellValue(r.getAffected().getPhone());
        Cell cellPeriod = row.createCell(5);
        cellPeriod.setCellValue(r.getGroup().getPeriod());
        Cell cellCourse = row.createCell(6);
        cellCourse.setCellValue(r.getGroup().getCourse().getCode());
        Cell cellNumGroup = row.createCell(7);
        cellNumGroup.setCellType(Cell.CELL_TYPE_NUMERIC);
        cellNumGroup.setCellValue(r.getGroup().getNumber());
        Cell cellInc = row.createCell(8);
        cellInc.setCellValue(transformInconsistencieToSpanish(r.getInconsistencie()));
        Cell cellDescription = row.createCell(9);
        cellDescription.setCellValue(r.getDescription());
        Cell cellIdReq = row.createCell(10);
        cellIdReq.setCellValue(r.getRequester().getId());
        Cell cellNameReq = row.createCell(11);
        cellNameReq.setCellValue(r.getRequester().getName());
        Cell cellReqState = row.createCell(12);
        cellReqState.setCellValue(transformReqStatetoSpanish(r.getRequestState()));
        if (r.getRequestState() == ERequestState.PROCESSED) {
            Cell cellNumReso = row.createCell(13);
            cellNumReso.setCellValue(Integer.toString(r.getResolution().getId()));
        }

        rowI++;

    }

    // Save to excel file 
    try {

        FileOutputStream out = new FileOutputStream(new File("src//files//DatosFormulario.xlsx"));

        workbook.write(out);

        workbook.close();
        out.close();
        saveResolution();

    } catch (FileNotFoundException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:controller.DAORequest.java

public void saveResolution() {
    XSSFWorkbook reqWB = new XSSFWorkbook();
    XSSFSheet sheet = reqWB.createSheet();
    Row rowZ = sheet.createRow(0);
    sheet.getRow(0).createCell(0).setCellValue("Id");
    sheet.getRow(0).createCell(1).setCellValue("Attention");
    sheet.getRow(0).createCell(2).setCellValue("Title");
    sheet.getRow(0).createCell(3).setCellValue("Intro");
    sheet.getRow(0).createCell(4).setCellValue("Result");
    sheet.getRow(0).createCell(5).setCellValue("Resolve");
    sheet.getRow(0).createCell(6).setCellValue("Notify");
    sheet.getRow(0).createCell(7).setCellValue("Considerations");
    ArrayList<Resolution> resolutions = new ArrayList<>();
    for (Object o : School.getInstance().selectAllRequests()) {
        Request r = (Request) o;
        if (r.getResolution() != null) {
            resolutions.add(r.getResolution());

        }//from   ww  w  . j  a v a 2  s.c  om
    }

    int rowI = 1;
    for (Resolution r : resolutions) {
        Row row = sheet.createRow(rowI);
        //
        Cell cellId = row.createCell(0);
        cellId.setCellValue(r.getId());
        Cell cellAttention = row.createCell(1);
        cellAttention.setCellValue(r.getAttention());
        Cell cellTitle = row.createCell(2);
        cellTitle.setCellValue(r.getTitle());
        Cell cellIntro = row.createCell(3);
        cellIntro.setCellValue(r.getIntro());
        Cell cellResult = row.createCell(4);
        cellResult.setCellValue(r.getResult());
        Cell cellResolve = row.createCell(5);
        cellResolve.setCellValue(r.getResolve());
        Cell cellNotify = row.createCell(6);
        cellNotify.setCellValue(r.getNotify());
        Cell cellCons = row.createCell(7);
        cellCons.setCellValue(r.getConsider());
        rowI++;

    }

    // Save to excel file 
    try {

        FileOutputStream out = new FileOutputStream(new File("src//files//DatosResolucion.xlsx"));

        reqWB.write(out);

        reqWB.close();
        out.close();

    } catch (FileNotFoundException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {

        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:controller.ExcelConsultant.java

static void logResult(ArrayList<Result> result, File outputFile, String name) {

    try {/*from   w  ww. jav a 2s  . c  o  m*/

        FileInputStream fis;
        fis = new FileInputStream(outputFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet sheet = myWorkBook.getSheetAt(0);

        int nRow = sheet.getPhysicalNumberOfRows();

        if (nRow == 0) {
            sheet.createRow(nRow);

            sheet.getRow(nRow).createCell(0).setCellValue("Timestamp");
            sheet.getRow(nRow).createCell(1).setCellValue("Name");
            sheet.getRow(nRow).createCell(2).setCellValue("Trade Name");
            sheet.getRow(nRow).createCell(3).setCellValue("Number");
            sheet.getRow(nRow).createCell(4).setCellValue("Person");
            sheet.getRow(nRow).createCell(5).setCellValue("Notes");

            nRow++;
        }

        for (Result res : result) {

            sheet.createRow(nRow);

            Date date = new Date();
            sheet.getRow(nRow).createCell(0).setCellValue(date.toString());
            sheet.getRow(nRow).createCell(1).setCellValue(name);
            sheet.getRow(nRow).createCell(2).setCellValue(res.getTradeName());
            sheet.getRow(nRow).createCell(3).setCellValue(res.getNumber());
            sheet.getRow(nRow).createCell(4).setCellValue(res.getPerson());
            sheet.getRow(nRow).createCell(5).setCellValue(res.getNotes());

            nRow++;
        }
        //important to close InputStream
        fis.close();
        //Open FileOutputStream to write updates
        FileOutputStream output_file = new FileOutputStream(outputFile);
        //write changes
        myWorkBook.write(output_file);
        //close the stream
        output_file.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

public String writeXlsx(List<Match> matches, CourtScheduleInfo info, String filepath) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet;
    sheet = null;/* ww  w . jav a 2s . c om*/
    rowNumber = 0;
    int cellNumber = 0;
    String lastConf = "";

    // print out master schedule
    Collections.sort(matches, Match.timeComparator);
    sheet = initializePage(workbook, "Master");
    for (Match match : matches) {
        rowNumber++;
        printMatch(sheet.createRow(rowNumber), match, info);
    }

    // print out individual conferences
    rowNumber = 0;
    Collections.sort(matches, Match.conferenceComparator);

    for (Match match : matches) {
        if (!match.getConference().equals(lastConf)) {
            sheet = initializePage(workbook, match.getConference());
        }

        rowNumber++;
        printMatch(sheet.createRow(rowNumber), match, info);
        lastConf = match.getConference();
    }

    Scanner input = new Scanner(System.in);
    String reply = "";
    boolean continueInput = true;
    StackTraceElement[] stackTraceE = new StackTraceElement[100];
    do {
        try {
            FileOutputStream out = new FileOutputStream(new File(filepath));
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully to " + filepath + ".");
            continueInput = false;

        } catch (FileNotFoundException e) {
            stackTraceE = e.getStackTrace();
            System.out.println("An output file already exists and is open.");
            System.out.println("To overwrite: close it, then hit 'enter'.");
            System.out.println("To create a new output file: enter new name or path.");
            System.out.println("To quit: type 'q'.");
            reply = input.nextLine();
            if (reply.regionMatches(true, 0, "q", 0, 1))
                return null;
            else if (reply.equals(""))
                continue;
            else
                filepath = reply;

        } catch (IOException e) {
            stackTraceE = e.getStackTrace();
            System.out.println(
                    "Output error.  Please enter new name or path for this output file, or 'q' to quit: ");
            reply = input.nextLine();
            if (reply.regionMatches(true, 0, "q", 0, 1))
                return null;
            else
                filepath = reply;
        }
    } while (continueInput);
    return filepath;
}

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

private XSSFSheet initializePage(XSSFWorkbook workbook, String conference) {
    // Create a new sheet with titles and headings for each new conference
    XSSFSheet sheet = workbook.createSheet(conference);
    rowNumber = 0;/*from   w  w  w . ja va2  s  . c o  m*/
    Row header = sheet.createRow(rowNumber);

    // Set sheet to Landscape so all columns will fit on one page
    XSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setOrientation(PrintOrientation.LANDSCAPE);

    // Column widths determined by specific sizes of heading strings (further down)
    sheet.setColumnWidth(0, 7424);
    sheet.setColumnWidth(1, 1024);
    sheet.setColumnWidth(2, 7424);
    sheet.setColumnWidth(3, 3072);
    sheet.setColumnWidth(4, 2816);
    sheet.setColumnWidth(5, 2816);
    sheet.setColumnWidth(6, 2403);
    sheet.setColumnWidth(7, 1792);

    header.createCell(0).setCellValue("THE COURTS");

    header.createCell(2).setCellValue("Game Schedule");
    rowNumber = rowNumber + 2;

    header = sheet.createRow(rowNumber);
    header.createCell(0).setCellValue("Conference " + conference);
    rowNumber = rowNumber + 2;

    header = sheet.createRow(rowNumber);
    header.createCell(0).setCellValue("TEAM");
    header.createCell(1).setCellValue(" ");
    header.createCell(2).setCellValue("OPPONENT");
    header.createCell(3).setCellValue("CONFERENCE");
    header.createCell(4).setCellValue("DAY");
    header.createCell(5).setCellValue("DATE");
    header.createCell(6).setCellValue("TIME");
    header.createCell(7).setCellValue("COURT");

    return sheet;
}

From source file:coverageqc.functions.MyExcelGenerator.java

public void excelHeadingCreator(String specifiedsheet, String headingLine) {
    XSSFSheet currentSheet = workbookcopy.createSheet(specifiedsheet);
    XSSFRow currentRow = currentSheet.createRow(0);

    XSSFCellStyle cellStyle;//from   w w w .j  a va 2s.co  m

    // String[] originalHeadingsArray = headingLine.split("\t");
    this.originalHeadingsArray = headingLine.split("\t");
    ;

    HashMap<String, Integer> headings = new HashMap<String, Integer>();
    for (int x = 0; x < originalHeadingsArray.length; x++) {
        headings.put(originalHeadingsArray[x].substring(0, originalHeadingsArray[x].indexOf("_")), x);
    }
    this.setRearrangedHashMap(headings);
    this.originalHeadings = headings;

    //the headers for the first three columns
    for (int x = 0; x < 3; x++) {
        Cell cell = currentRow.createCell(x);
        cellStyle = getDefaultCellStyle(currentRow, Color.WHITE);
        if (x == 0 || x == 1) {

            cell.setCellStyle(cellStyle);
            if (x == 0) {
                cell.setCellValue("Tom's Interpretation");
                //cell.setCellValue("Fellow1's Interpretation");
            } else {
                cell.setCellValue("Christina's Interpretation");
                //cell.setCellValue("Fellow2's Interpretation");
            }

        } else if (x == 2) {
            cell.setCellStyle(cellStyle);
            cell.setCellValue("Attending Pathologist Interpretation");
        }
    }

    for (int x = 0; x < originalHeadingsArray.length; x++) {

        //plus three because have three header columns
        Cell cell = currentRow.createCell(x + 3);
        cellStyle = getDefaultCellStyle(currentRow, Color.WHITE);
        // cell.setCellStyle(cellStyle);
        if (this.originalHeadingsArray[this.tsvRearrangeConversion.get(x)].contains("Gene_")
                || this.originalHeadingsArray[this.tsvRearrangeConversion.get(x)].contains("Variant_")
                || this.originalHeadingsArray[this.tsvRearrangeConversion.get(x)].contains("Chr_"))
        //  ||originalHeadingsArray[x].contains("Coordinate_")
        //   ||originalHeadingsArray[x].contains("Type_"))
        //  ||originalHeadingsArray[x-3].contains("Genotype_")
        // ||originalHeadingsArray[x-3].contains("Coordinate_")
        //  ||originalHeadingsArray[x-3].contains("Filters_"))
        {
            cellStyle.setRotation((short) 0);
        } else {
            cellStyle.setRotation((short) 90);
        }

        cell.setCellStyle(cellStyle);
        //cell.setCellValue(originalHeadingsArray[x-3]);
        // this.tsvRearrangeConversion.get(x)
        cell.setCellValue(this.originalHeadingsArray[this.tsvRearrangeConversion.get(x)]);
        //cell.setCellValue(headinsArray[headingsConversion.get(x-3)]);

    } //end for loop

    //return currentRow;
}

From source file:cv_extractor.DocReader.java

protected static void generateExcel(String directory, String fileName) {
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Email Address");

    //Create CellStyle for header (First row)
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    Font font = sheet.getWorkbook().createFont();
    font.setBold(true);/*  ww w .j  a  v  a2  s.c  o m*/
    font.setFontHeightInPoints((short) 16);
    cellStyle.setFont(font);

    //Create first row (Header)
    Row header = sheet.createRow(0);

    //Creating a cell (Only one required, for single attribute E-mail)
    Cell cellTitle = header.createCell(1);
    cellTitle.setCellStyle(cellStyle);
    cellTitle.setCellValue("E-Mail");

    //Counter to use while creating further rows, starting from row 1
    int rowNum = 1;

    //Iterate over data and write to sheet   
    Iterator<String> iterator = data.iterator();

    while (iterator.hasNext()) {
        //Create a row for each E-mail string
        Row row = sheet.createRow(rowNum++);

        //Create a cell to put the E-mail string
        Cell cell = row.createCell(1);

        //Put value in the cell
        cell.setCellValue(iterator.next());
    }

    try {
        //Write the workbook in file system

        if (fileName.equals("")) {
            fileName = "Email List";
        }

        FileOutputStream out = new FileOutputStream(new File(directory + "/" + fileName + ".xlsx"));
        workbook.write(out);
        out.close();

        JOptionPane.showMessageDialog(null, "File generated !");

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:cz.lbenda.dataman.db.ExportTableData.java

License:Apache License

/** Write rows to XLSX file
 * @param sqlQueryRows rows//from  www  .j av a  2s .  c o m
 * @param sheetName name of sheet where is data write
 * @param outputStream stream where are data write */
public static void writeSqlQueryRowsToXLSX(SQLQueryRows sqlQueryRows, String sheetName,
        OutputStream outputStream) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow headerRow = sheet.createRow(0);
    int c = 0;
    for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
        XSSFCell cell = headerRow.createCell(c);
        cell.setCellValue(columnDesc.getName());
        c++;
    }
    int r = 1;
    for (RowDesc row : sqlQueryRows.getRows()) {
        XSSFRow xlsxRow = sheet.createRow(r);
        c = 0;
        for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) {
            XSSFCell cell = xlsxRow.createCell(c);
            cell.setCellValue(row.getColumnValueStr(columnDesc));
            c++;
        }
        r++;
    }
    wb.write(outputStream);
}

From source file:Dao.XlsBillDao.java

public void GenFullXLS(String pono, String relpath) {
    try {/*from w  w w  . jav  a 2s . c  o m*/
        //0.Declare Variables for Sheet
        //DB Variable
        //            pono = "WO/2015/2005";
        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //1.Get Connection and Fetch Data
        ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>();
        WorkDao wdao1 = new WorkDao();
        wi1 = wdao1.getWOItem(pono);

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet("WorkOrder Detail");

        //   spreadsheet.protectSheet("kandarpCBA");
        //        spreadsheet.setColumnWidth(0, 255);
        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);
        //Set font into style

        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);

        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("WORK ORDER NO : " + pono);
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("LINE_NO");
        cell.setCellStyle(xstyle);
        cell = row.createCell(1);
        cell.setCellValue("ITEM_ID");
        cell.setCellStyle(xstyle);
        cell = row.createCell(2);
        cell.setCellValue("DESCRIPTION");
        cell.setCellStyle(xstyle);
        cell = row.createCell(3);
        cell.setCellValue("UOM");
        cell.setCellStyle(xstyle);
        cell = row.createCell(4);
        cell.setCellValue("QTY");
        cell.setCellStyle(xstyle);
        cell = row.createCell(5);
        cell.setCellValue("RATE");
        cell.setCellStyle(xstyle);
        cell = row.createCell(6);
        cell.setCellValue("NOTE");
        cell.setCellStyle(xstyle);

        int i = 2;
        for (WorkItemBean w : wi1) {
            row = spreadsheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(w.getLINE_NO());
            cell = row.createCell(1);
            cell.setCellValue(w.getITEM_ID());
            cell = row.createCell(2);
            cell.setCellValue(w.getITEM_DESC());
            cell = row.createCell(3);
            cell.setCellValue(w.getUOM());
            cell = row.createCell(4);
            cell.setCellValue(w.getQTY());
            cell = row.createCell(5);
            cell.setCellValue(w.getRATE());
            cell = row.createCell(6);
            cell.setCellValue(w.getCMT());
            i++;
        }

        //Export to Excel
        //            FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx"));
        FileOutputStream out = new FileOutputStream(
                new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx"));
        workbook.write(out);
        out.close();
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx",
                new Object[] { relpath, pono.replace("/", "-") });
    } catch (FileNotFoundException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    }
}

From source file:Dao.XlsBillDao.java

public void GenXLS(String pono, String relpath) {
    try {/*  www  .  j ava  2s.com*/
        //0.Declare Variables for Sheet
        //DB Variable
        //            pono = "WO/2015/2005";
        String sql;
        Connection con;
        PreparedStatement ps;
        ResultSet rs;
        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //1.Get Connection and Fetch Data
        con = DBmanager.GetConnection();
        sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n"
                + "                mtl_system_items.description, cba_wo_item.uom,\n"
                + "                cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n"
                + "                cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task,"
                + "                cba_wo_item.po_no\n" + "           FROM cba_wo_item, mtl_system_items\n"
                + "          WHERE (    (cba_wo_item.item_id = mtl_system_items.segment1)\n"
                + "                 AND (mtl_system_items.organization_id = 0)\n"
                + "                 AND (cba_wo_item.po_no = '" + pono + "')\n" + "                )\n"
                + "       ORDER BY cba_wo_item.line_no";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet("WorkOrder Detail");

        //spreadsheet.protectSheet("kandarpCBA");
        //spreadsheet.setColumnWidth(0, 255);
        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);
        //Set font into style
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);
        //        borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        //        borderStyle.setFillPattern(CellStyle.ALIGN_FILL);

        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("WORK ORDER NO : " + pono
                + " Note : If WO is with project information,each bill item should have project and task");
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("LINE_NO");
        cell.setCellStyle(xstyle);
        cell = row.createCell(1);
        cell.setCellValue("ITEM_ID");
        cell.setCellStyle(xstyle);
        cell = row.createCell(2);
        cell.setCellValue("DESCRIPTION");
        cell.setCellStyle(xstyle);
        cell = row.createCell(3);
        cell.setCellValue("UOM");
        cell.setCellStyle(xstyle);
        cell = row.createCell(4);
        cell.setCellValue("QTY");
        cell.setCellStyle(xstyle);
        cell = row.createCell(5);
        cell.setCellValue("RATE");
        cell.setCellStyle(xstyle);
        cell = row.createCell(6);
        cell.setCellValue("WO NOTE");
        cell.setCellStyle(xstyle);
        cell = row.createCell(7);
        cell.setCellValue("PLANT");
        cell.setCellStyle(xstyle);
        cell = row.createCell(8);
        cell.setCellValue("COST CENTER");
        cell.setCellStyle(xstyle);
        cell = row.createCell(9);
        cell.setCellValue("PROJECT");
        cell.setCellStyle(xstyle);
        cell = row.createCell(10);
        cell.setCellValue("TASK");
        cell.setCellStyle(xstyle);
        cell = row.createCell(11);
        cell.setCellValue("HERE ADD NOTE");
        cell.setCellStyle(xstyle);

        //Itrate or Database data and write
        int i = 2;
        while (rs.next()) {
            row = spreadsheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(rs.getString(1));
            cell = row.createCell(1);
            cell.setCellValue(rs.getString(2));
            cell = row.createCell(2);
            cell.setCellValue(rs.getString(3));
            cell = row.createCell(3);
            cell.setCellValue(rs.getString(4));
            cell = row.createCell(4);
            cell.setCellValue(rs.getString(6));
            cell = row.createCell(5);
            cell.setCellValue(rs.getString(5));
            cell = row.createCell(6);
            cell.setCellValue("");
            cell = row.createCell(7);
            cell.setCellValue(rs.getString(7));
            cell = row.createCell(8);
            cell.setCellValue(rs.getString(8));
            cell = row.createCell(9);
            cell.setCellValue(rs.getString(9));
            cell = row.createCell(10);
            cell.setCellValue(rs.getString(10));
            cell = row.createCell(11);
            cell.setCellValue("");
            i++;
        }

        //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL
        XSSFRow row2;
        XSSFCell cell2;
        XSSFSheet ccsheet = workbook.createSheet("Cost Center");
        row2 = ccsheet.createRow(0);
        cell2 = row2.createCell(0);
        cell2.setCellValue("Cost Center name and code. Please enter only code in excel");
        cell2.setCellStyle(borderStyle);
        ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

        row2 = ccsheet.createRow(1);
        cell2 = row2.createCell(0);
        cell2.setCellValue("CODE");
        cell2.setCellStyle(xstyle);

        cell2 = row2.createCell(1);
        cell2.setCellValue("NAME");
        cell2.setCellStyle(xstyle);

        con = DBmanager.GetConnection();
        sql = "select cc,plant from cba_cc_mst";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        int i2 = 2;
        while (rs.next()) {
            row2 = ccsheet.createRow(i2);
            cell2 = row2.createCell(0);
            cell2.setCellValue(rs.getString(1));
            cell2 = row2.createCell(1);
            cell2.setCellValue(rs.getString(2));
            i2++;
        }

        //THIRD SHEET
        //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL
        XSSFRow row3;
        XSSFCell cell3;
        XSSFSheet plantsheet = workbook.createSheet("Plant Center");
        row3 = plantsheet.createRow(0);
        cell3 = row3.createCell(0);
        cell3.setCellValue("Plant Center name and code. Please enter only code in excel");
        cell3.setCellStyle(borderStyle);
        plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

        row3 = plantsheet.createRow(1);
        cell3 = row3.createCell(0);
        cell3.setCellValue("CODE");
        cell3.setCellStyle(xstyle);

        cell3 = row3.createCell(1);
        cell3.setCellValue("NAME");
        cell3.setCellStyle(xstyle);

        con = DBmanager.GetConnection();
        sql = "select cc,plant from cba_plant_mst";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        int i3 = 2;
        while (rs.next()) {
            row3 = plantsheet.createRow(i3);
            cell3 = row3.createCell(0);
            cell3.setCellValue(rs.getString(1));
            cell3 = row3.createCell(1);
            cell3.setCellValue(rs.getString(2));
            i3++;
        }
        //SHEET 3 HEADER
        //row1
        XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task");
        XSSFRow row4 = spreadsheet4.createRow(0);
        XSSFCell cell4 = row4.createCell(0);
        cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet");
        spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        //row2  
        row4 = spreadsheet4.createRow(1);
        cell4 = row4.createCell(0);
        cell4.setCellValue("PROJECT CODE");
        cell4.setCellStyle(xstyle);
        cell4 = row4.createCell(1);
        cell4.setCellValue("PROJECT NAME");
        cell4.setCellStyle(xstyle);
        cell4 = row4.createCell(2);
        cell4.setCellValue("TASK CODE");
        cell4.setCellStyle(xstyle);
        //SHEET 3 DATA
        int j = 2;
        ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123");
        for (WorkItemBean w : wi1) {
            row4 = spreadsheet4.createRow(j);
            cell4 = row4.createCell(0);
            cell4.setCellValue(w.getPROJ());
            cell4 = row4.createCell(1);
            cell4.setCellValue(w.getPROJ_NAME());
            cell4 = row4.createCell(2);
            cell4.setCellValue(w.getTASK());
            j++;
        }

        //Export to Excel
        //            FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx"));
        FileOutputStream out = new FileOutputStream(
                new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx"));
        workbook.write(out);
        out.close();

        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx",
                new Object[] { relpath, pono.replace("/", "-") });
    } catch (SQLException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    }
}