Example usage for org.apache.poi.ss.usermodel Cell setCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellValue

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell setCellValue.

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetSimType(FileNames fileName, SheetName sheetName) throws IOException {
    String filePath = FilePath(fileName);
    String simType = null;//from w  w  w . j a v a  2  s .  c  om
    try {
        String sheet = sheetName.toString();
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            sheet = Name[1];
        }
        SetStatusForSimTypSheets(sheet, fileName);
        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        XSSFSheet workSheet = workBook.getSheet(sheet);
        for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Row currentRow = workSheet.getRow(i);
            int type = 1;
            Cell cell = currentRow.getCell(1);
            try {
                type = cell.getCellType();
                System.out.println(type);
                System.out.println(workSheet.getRow(i).getCell(1).toString());
                System.out.println(cell.getStringCellValue().toLowerCase());
                if (type == 1 && cell.getStringCellValue().toLowerCase()
                        .equals(Status.UNUSED.toString().toLowerCase())) {
                    cell.setCellValue(Status.INUSE.toString());
                    simType = currentRow.getCell(0).getStringCellValue();
                    break;
                }
            }

            catch (Exception e) {
                if (type != 1) {
                    cell = currentRow.createCell(1);
                    cell.setCellValue(Status.UNUSED.toString());
                }
            }
        }
    } catch (Exception ex) {
        Log.error(ex.toString());
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
    return simType;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public void SetStatusForSimTypSheets(String sheet, FileNames fileName) throws IOException {
    int statusColumn;
    XSSFSheet workSheet = null;/*from   ww  w . ja  va 2s .  c om*/
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = GetWorkBook(fileName);
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        if (workBook.getSheetName(i).toLowerCase().contains(sheet.toLowerCase())) {
            workSheet = workBook.getSheetAt(i);
            break;
        }
    }

    try {
        for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Row statusRow = workSheet.getRow(i);
            for (int ct = 0; ct < statusRow.getPhysicalNumberOfCells(); ct++) {
                System.out.println(statusRow.getCell(ct));
            }
            int numOfCells = statusRow.getPhysicalNumberOfCells();
            if (workSheet.getRow(i).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                    .contains(Status.STATUS.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.UNUSED.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.INUSE.toString())
                    || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                            .contains(Status.USED.toString())) {
                statusColumn = numOfCells - 1;
            } else {
                statusColumn = numOfCells;
                Cell statusCell = workSheet.getRow(i).createCell(statusColumn);
                statusCell.setCellValue(Status.UNUSED.toString());
                continue;
            }
            System.out.println(statusRow.getCell(statusColumn));
            try {
                if (statusRow.getCell(statusColumn) == null) {

                    Cell cell = row.createCell(statusColumn);
                    cell.setCellValue(Status.UNUSED.toString());
                }
            } catch (Exception ex) {
                row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
            }
        }
    } catch (Exception ex) {
        Log.error(ex.toString());
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString)
        throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    String sheet = sheetName.toString();
    int rowIndex = -1;

    try {/*from www  .  ja  va2s.c  o m*/
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            if (Name[0].contains("IMEI")) {
                sheet = Name[1] + " " + Name[0];
            } else
                sheet = Name[1];
        }
        worksheet = workBook.getSheet(sheet);
        System.out.println(worksheet.getSheetName());
        Iterator<Row> rowIterator = worksheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String
                        .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) {
                    rowIndex = row.getRowNum();
                    break;
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equals(searchString))
                        rowIndex = row.getRowNum();
                    break;
                }
            }
        }
        if (rowIndex != -1) {
            boolean flag = false;
            Row searchStringRow = worksheet.getRow(rowIndex);
            System.out.println(searchStringRow.getPhysicalNumberOfCells());
            Iterator<Cell> statusChangeCell = searchStringRow.cellIterator();
            while (statusChangeCell.hasNext()) {
                Cell statusCell = statusChangeCell.next();
                if (statusCell.getStringCellValue().toLowerCase()
                        .equals(Status.INUSE.toString().toLowerCase())) {
                    statusCell.setCellValue(Status.USED.toString());
                    flag = true;
                }
            }
            if (flag == false)
                Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath
                        + ", Sheet: " + sheet);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetIMEINumber(FileNames fileName, SheetName workSheet) throws IOException {
    String filePath = FilePath(fileName);
    String sheet = null;//from   www.  j  a v  a  2s .c om
    String tempSheet = null;
    String imei = "";
    int statusColumn = -1;
    try {
        int numOfWorkSheets = 0;

        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        numOfWorkSheets = workBook.getNumberOfSheets();
        for (int count = 0; count < numOfWorkSheets; count++) {
            if (workSheet.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
                sheet = workBook.getSheetName(count);
                break;
            }
        }
        worksheet = workBook.getSheet(sheet);
        if (sheet.equals("iPhone 5C")) {
            tempSheet = sheet;
            sheet = "iPhone 4S";
        }

        switch (sheet) {
        case "4G IMEI":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(2);
                if (row.getCell(5).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf(cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(5).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
            return imei;

        case "3G":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                if (row.getCell(4).toString().equals(Status.UNUSED.toString())) {
                    imei = String.valueOf(row.getCell(2).getNumericCellValue());
                    worksheet.getRow(i).getCell(4).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "Jetpack":
            statusColumn = CreateStatusColumn(filePath, sheet);
            if (statusColumn == -1) {
                statusColumn = 3;
            }

            for (int i = 2; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(1);
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "iPhone 4S":
            if (tempSheet.contains("5C"))
                statusColumn = CreateStatusColumn(filePath, tempSheet);
            if (statusColumn == -1) {
                Cell cell = worksheet.getRow(0).createCell(4);
                cell.setCellValue(Status.STATUS.toString());
                statusColumn = 4;
            }
            for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(0);
                if (row.getCell(statusColumn).getCellType() == Cell.CELL_TYPE_BLANK) {
                    row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
                }
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    if (imei != "" || imei != null)
                        row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
        }
    }

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

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    return imei;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public AccountDetails GetAccountDetails(FileNames fileName, SheetName worksheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, worksheetName.toString());
    AccountDetails accountDetails = new AccountDetails();

    try {/*  w  w w.  j  a  v  a2  s . c  o  m*/
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("MTN")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Password")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("SSN")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                accountDetails.MTN = String
                        .valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                accountDetails.Password = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                accountDetails.SSN = String
                        .valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

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

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (accountDetails == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return accountDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
//Inputs IDType; State
public CustomerDetails GetCustomerDetails(IdType idType) throws IOException, NullPointerException {
    CustomerDetails customerDetails = new CustomerDetails();
    String filePath = FilePath(FileNames.GlobalTestData);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    worksheet = workBook.getSheet("Customer");
    System.out.println(worksheet.getSheetName());
    String IDType = "Driver's License";
    if (idType.toString().equals("USPASSPORT")) {
        IDType = "Passport";
    } else if (idType.toString().equals("STATEID")) {
        IDType = "STATE ID";

    }//  w  w  w  .j av a  2s . co  m

    try {
        int statusColumn = -1;

        for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
            row = worksheet.getRow(i);

            if (i == 0) {
                int numOfCells = row.getPhysicalNumberOfCells();
                if (worksheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                        .contains(Status.STATUS.toString())) {
                    statusColumn = numOfCells - 1;
                } else {
                    statusColumn = numOfCells;
                    Cell statusCell = row.createCell(statusColumn);
                    statusCell.setCellValue(Status.STATUS.toString());
                    continue;
                }
            }

            if (row.getCell(statusColumn) == null) {

                Cell cell = row.createCell(statusColumn);
                cell.setCellValue(Status.UNUSED.toString());
            }

            if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())
                    && row.getCell(12).toString().equals(IDType)) {
                customerDetails.FirstName = String.valueOf(row.getCell(0));
                customerDetails.LastName = String.valueOf(row.getCell(1));
                customerDetails.Address1 = String.valueOf(row.getCell(2));
                customerDetails.City = String.valueOf(row.getCell(4));
                customerDetails.State = String.valueOf(row.getCell(5));
                System.out.println(customerDetails.State.toString());
                if (row.getCell(6).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.Zip = String.valueOf((int) row.getCell(6).getNumericCellValue());
                if (row.getCell(7).getCellType() != Cell.CELL_TYPE_BLANK) {
                    System.out.println(row.getCell(7).getCellType());
                    customerDetails.PhNum = String.valueOf((int) row.getCell(7).getNumericCellValue());
                }
                if (row.getCell(8).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.EMail = String.valueOf(row.getCell(8));
                if (row.getCell(9).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayMonth = String.valueOf(row.getCell(9));
                if (row.getCell(10).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayDay = String.valueOf((int) row.getCell(10).getNumericCellValue());
                if (row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.BirthdayYear = String.valueOf((int) row.getCell(11).getNumericCellValue());
                if (row.getCell(12).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDType = String.valueOf(row.getCell(12));
                if (row.getCell(13).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDState = String.valueOf(row.getCell(13));
                if (row.getCell(14).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDNumber = String.valueOf(row.getCell(14));
                if (row.getCell(15).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDExpirationMonth = String.valueOf(row.getCell(15));
                if (row.getCell(16).getCellType() != Cell.CELL_TYPE_BLANK)
                    customerDetails.IDExpirationYear = String
                            .valueOf((int) row.getCell(16).getNumericCellValue());
                if (row.getCell(17).getCellType() != Cell.CELL_TYPE_BLANK)
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                if (customerDetails != null)
                    break;
            }
        }
    }

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

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (customerDetails == null) {
        System.out.println("FAILED to get customer details ");
    }

    return customerDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public int CreateStatusColumn(String filePath, String workSheetName) throws IOException {
    //String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet worksheet = GetSheetFromWorkBook(workBook, workSheetName);
    int statusColumn = -1;
    try {/*from w  ww.ja  va2 s . co  m*/
        for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
            row = worksheet.getRow(i);
            if (i == 0) {
                int numOfCells = row.getPhysicalNumberOfCells();
                if (worksheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase()
                        .contains(Status.STATUS.toString())) {
                    statusColumn = numOfCells - 1;
                } else {
                    statusColumn = numOfCells;
                    Cell statusCell = row.createCell(statusColumn);
                    statusCell.setCellValue(Status.STATUS.toString());
                    continue;
                }
            }
            if (row.getCell(statusColumn) == null) {

                Cell cell = row.createCell(statusColumn);
                cell.setCellValue(Status.UNUSED.toString());
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return statusColumn;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public NPANXX GetNumberPortData(FileNames fileName, SheetName workSheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, workSheetName.toString());
    NPANXX npaNXX = new NPANXX();

    try {//  w w w.  ja  v  a  2 s .c  o  m
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("NGP")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Location")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("NPANXX")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                npaNXX.NGP = String.valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                npaNXX.Location = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                npaNXX.NPANXX = String.valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

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

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (npaNXX == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return npaNXX;
}

From source file:com.crimelab.service.ChemistryServiceImpl.java

@Override
public Workbook create(Chemistry chemistry, HttpSession session) {
    Workbook wb = null;//from   w w w .  j  av  a 2  s .c o  m
    try {

        //            date = sdf.parse(chemistry.getTimeDateReceived());
        //            sdf.applyPattern("M-dd-yyyy hh:mm:ss a");
        //            dateformat = sdf.format(date);
        InputStream inp = session.getServletContext()
                .getResourceAsStream("/WEB-INF/templates/DefaultDrugs.xls");
        wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        Cell examType = findCell(sheet, "$examType");
        Cell reportNo = findCell(sheet, "$reportNo");
        Cell caseType = findCell(sheet, "$caseType");
        Cell suspects = findCell(sheet, "$suspects");
        Cell victims = findCell(sheet, "$victims");
        Cell timeDateReceived = findCell(sheet, "$timeDateReceived");
        Cell requestingParty = findCell(sheet, "$requestingParty");
        Cell specimenSubmitted = findCell(sheet, "$specimenSubmitted");
        Cell purposeOfLabExam = findCell(sheet, "$purposeOfLabExam");
        Cell findings = findCell(sheet, "$findings");
        Cell conclusions = findCell(sheet, "$conclusions");
        Cell remarks = findCell(sheet, "$remarks");
        Cell timeDateCompleted = findCell(sheet, "$timeDateCompleted");
        Cell examinerName = findCell(sheet, "$examinerName");
        Cell examinerRank = findCell(sheet, "$examinerRank");
        Cell examinerPosition = findCell(sheet, "$examinerPosition");
        Cell appName = findCell(sheet, "$appName");
        Cell appRank = findCell(sheet, "$appRank");
        Cell appPosition = findCell(sheet, "$appPosition");
        Cell notedName = findCell(sheet, "$notedName");
        Cell notedRank = findCell(sheet, "$notedRank");
        Cell notedPosition = findCell(sheet, "$notedPosition");
        Cell subscribed = findCell(sheet, "$subscribed");
        Cell subscribedName = findCell(sheet, "$subscribedName");
        Cell subscribedRank = findCell(sheet, "$subscribedRank");
        Cell subscribedPosition = findCell(sheet, "$subscribedPosition");

        examType.setCellValue(chemistry.getExamType());
        reportNo.setCellValue(chemistry.getReportNo());
        caseType.setCellValue(chemistry.getCaseType());
        suspects.setCellValue(chemistry.getSuspects());
        victims.setCellValue(chemistry.getVictims());
        timeDateReceived.setCellValue(chemistry.getTimeDateReceived());
        requestingParty.setCellValue(chemistry.getRequestingParty());
        specimenSubmitted.setCellValue(chemistry.getSpecimenSubmitted());
        purposeOfLabExam.setCellValue(chemistry.getPurposeOfLabExam());
        findings.setCellValue(chemistry.getFindings());
        conclusions.setCellValue(chemistry.getConclusions());
        remarks.setCellValue(chemistry.getRemarks());
        timeDateCompleted.setCellValue(chemistry.getTimeDateCompleted());
        examinerName.setCellValue(chemistry.getExaminerName());
        examinerRank.setCellValue(chemistry.getExaminerRank());
        examinerPosition.setCellValue(chemistry.getExaminerPosition());
        appName.setCellValue(chemistry.getAppName());
        appRank.setCellValue(chemistry.getAppRank());
        appPosition.setCellValue(chemistry.getAppPosition());
        notedName.setCellValue(chemistry.getNotedName());
        notedRank.setCellValue(chemistry.getNotedRank());
        notedPosition.setCellValue(chemistry.getNotedPosition());
        subscribed.setCellValue(chemistry.getSubscribed());
        subscribedName.setCellValue(chemistry.getSubscribedName());
        subscribedRank.setCellValue(chemistry.getSubscribedRank());
        subscribedPosition.setCellValue(chemistry.getSubscribedPosition());

        chemistryDAO.chemistryInfo(chemistry);

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

    }
    return wb;
}

From source file:com.crimelab.service.ChemistryServiceImpl.java

@Override
public Workbook createMonthlyReport(HttpSession session, String month) {
    Workbook wb = null;/*  w ww  . j  av  a 2s.  c o  m*/

    try {
        InputStream inp = session.getServletContext()
                .getResourceAsStream("/WEB-INF/templates/DrugMonthlyReport.xls");
        wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        CellStyle cs1 = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        CellStyle bl = wb.createCellStyle();
        CellStyle br = wb.createCellStyle();
        CellStyle bt = wb.createCellStyle();
        CellStyle bb = wb.createCellStyle();
        CellStyle stf = wb.createCellStyle();
        cs1.setWrapText(true);
        cs2.setAlignment(ALIGN_CENTER);
        bt.setBorderTop(BORDER_THIN);
        bb.setBorderBottom(BORDER_THIN);
        bl.setBorderLeft(BORDER_THIN);
        br.setBorderRight(BORDER_THIN);
        stf.setShrinkToFit(true);

        Row intro1 = sheet.createRow(7);
        Cell in1 = intro1.createCell(0);//.setCellValue("DRUG INVETORY COVERED PERIOD JANUARY-DECEMBER CY-" +month.split("-")[0]);
        in1.setCellValue("DRUG INVENTORY COVERED PERIOD JANUARY-DECEMBER CY-" + month.split("-")[0]);
        in1.setCellStyle(cs1);
        in1.setCellStyle(cs2);

        Row intro2 = sheet.createRow(9);
        Cell in2 = intro2.createCell(0);//.setCellValue("SUMMARY OF SEIZED/SURRENDERED/RECOVERED OF DRUG EVIDENCES FROM NEGATION OPERATIONS FROM LAW ENFORCEMENTS, PHARMACEUTICAL COMPANIES AND SIMILAR ESTABLISHMENTS FOR THE MONTH OF "+month);
        in2.setCellValue(
                "SUMMARY OF SEIZED/SURRENDERED/RECOVERED OF DRUG EVIDENCES FROM NEGATION OPERATIONS FROM LAW ENFORCEMENTS, PHARMACEUTICAL COMPANIES AND SIMILAR ESTABLISHMENTS FOR THE MONTH OF "
                        + month);
        in2.setCellStyle(cs1);
        in2.setCellStyle(cs2);
        in2.setCellStyle(stf);

        int ctr = 12; //initial
        Row row = sheet.createRow(ctr);
        month = month.split("-")[1];

        //System.out.println("GAC " + chemistryDAO.getAllChemistry(month).isEmpty());
        for (Chemistry chemistry : chemistryDAO.getAllChemistry(month)) {
            //System.out.println("Test " + chemistry.getTimeDateReceived());
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(chemistry.getTimeDateReceived());
            cell0.setCellStyle(bt);
            cell0.setCellStyle(bb);
            cell0.setCellStyle(bl);
            cell0.setCellStyle(br);

            Cell cell1 = row.createCell(1);//.setCellValue(chemistry.getReportNo());
            cell1.setCellValue(chemistry.getReportNo());
            cell1.setCellStyle(bt);
            cell1.setCellStyle(bb);
            cell1.setCellStyle(bl);
            cell1.setCellStyle(br);

            Cell cell2 = row.createCell(2);//.setCellValue(chemistry.getRequestingParty());
            cell2.setCellValue(chemistry.getRequestingParty());
            cell2.setCellStyle(bt);
            cell2.setCellStyle(bb);
            cell2.setCellStyle(bl);
            cell2.setCellStyle(br);

            Cell cell3 = row.createCell(3);//.setCellValue(chemistry.getDescriptionOfEvidence());
            cell3.setCellValue(chemistry.getDescriptionOfEvidence());
            cell3.setCellStyle(bt);
            cell3.setCellStyle(bb);
            cell3.setCellStyle(bl);
            cell3.setCellStyle(br);

            Cell cell4 = row.createCell(4);//.setCellValue(chemistry.getSpecimenWeight());
            cell4.setCellValue(chemistry.getSpecimenWeight());
            cell4.setCellStyle(bt);
            cell4.setCellStyle(bb);
            cell4.setCellStyle(bl);
            cell4.setCellStyle(br);

            Cell cell5 = row.createCell(5);//.setCellValue(chemistry.getCustody());
            cell5.setCellValue(chemistry.getCustody());
            cell5.setCellStyle(bt);
            cell5.setCellStyle(bb);
            cell5.setCellStyle(bl);
            cell5.setCellStyle(br);

            Cell cell6 = row.createCell(6);//.setCellValue(chemistry.getSuspects());
            cell6.setCellValue(chemistry.getSuspects());
            cell6.setCellStyle(bt);
            cell6.setCellStyle(bb);
            cell6.setCellStyle(bl);
            cell6.setCellStyle(br);

            Cell cell7 = row.createCell(7);//.setCellValue(chemistry.getTypeOfOperation());
            cell7.setCellValue(chemistry.getTypeOfOperation());
            cell7.setCellStyle(bt);
            cell7.setCellStyle(bb);
            cell7.setCellStyle(bl);
            cell7.setCellStyle(br);

            Cell cell8 = row.createCell(8);//.setCellValue(chemistry.getPlaceOfOperation());
            cell8.setCellValue(chemistry.getPlaceOfOperation());
            cell8.setCellStyle(bt);
            cell8.setCellStyle(bb);
            cell8.setCellStyle(bl);
            cell8.setCellStyle(br);

            row = sheet.createRow(ctr += 1);
        }

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

    return wb;
}