Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from w w w  . jav  a 2s . c o m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet + ".xlsx");
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(FunctiosDates.getDateActual());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean createSpreadsheetTS(String pathSheet, String nameSheet, TestPlanTSBean testPlan)
        throws Exception {

    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from   w w  w  .j a va 2s  .  co m
    String sheetTI = pathSheet + "\\" + nameSheet;
    nameSheet = nameSheet.replace("xlsx", "xlsm");
    ;
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File("C:\\FastPlan\\sheets\\TS_NEW.xlsm");
    logger.info("Realizando cpia da planilha");
    boolean existInList = false;

    List<TesteCaseTSBean> testCasesAutomatizados = new ArrayList<TesteCaseTSBean>();

    //run macro
    String cmd = "C:\\FastPlan\\runMacro.vbs";
    Runtime.getRuntime().exec("cmd /c" + cmd);

    Thread.sleep(2000);

    copySheet(sourceStheet, destinationSheet);
    logger.info("Planilha copiada");
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);

    XSSFSheet sheetTS = workbook.getSheetAt(0);
    XSSFCellStyle estilo = workbook.createCellStyle();

    //        workbook.setSheetName(workbook.getSheetIndex(sheetTS), FunctiosDates.dateToString(FunctiosDates.getDateActual(), "yyyy-MM-dd-HH-mm-ss"));
    int linha = 2;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell release = row.getCell(1);
    Cell prj = row.getCell(2);
    Cell fase = row.getCell(3);
    Cell testPhase = row.getCell(4);
    Cell testScriptName = row.getCell(5);
    Cell testScriptDescription = row.getCell(6);
    Cell stepNo = row.getCell(7);
    Cell stepDescription = row.getCell(8);
    Cell expectedResults = row.getCell(9);
    Cell product = row.getCell(10);
    Cell dataPlanejada = row.getCell(11);
    Cell qtdSteps = row.getCell(12);
    Cell complexidade = row.getCell(13);
    Cell automatizado = row.getCell(14);

    logger.info("Inserindo dados do plano");
    descriptionPlan.setCellValue(testPlan.getName());
    release.setCellValue(testPlan.getRelease());

    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);

        logger.info("Inserindo dados dos TCs");
        System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - " + testPlan.getSti()
                + " - " + "row:" + linha);
        prj.setCellValue(testPlan.getSti());
        fase.setCellValue(testPlan.getCrFase());
        testPhase.setCellValue(testPlan.getTestPhase());
        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        qtdSteps.setCellValue(testPlan.getTestCase().get(i).getListStep().size());
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        //set colors 
        if (i % 2 == 0) {

            System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - entrou");
            //                setColorCells(new Cell[]{descriptionPlan, release, prj, fase, testPhase, testScriptName, testScriptDescription, stepNo,
            //                    stepDescription, expectedResults, product, dataPlanejada, qtdSteps, complexidade}, workbook);

            XSSFCellStyle styleColor = (XSSFCellStyle) product.getCellStyle();
            styleColor.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);
            product.setCellStyle(styleColor);

        }

        for (int j = 0; j < testPlan.getTestCase().get(i).getListStep().size(); j++) {

            row = sheetTS.getRow(linha);
            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);
            //                
            //                stepNo.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getNomeStep());
            logger.info("Inserindo dados dos Steps");
            stepNo.setCellValue(j + 1);
            stepDescription.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getDescStep());
            expectedResults.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getResultadoStep());

            linha = linha + 1;

            row = sheetTS.getRow(linha);

            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);

        }

        linha = linha + 1;
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);
        logger.info("Dados inseridos na planilha");

        if (testPlan.getTestCase().get(i).isAutomatizado()) {

            for (int j = 0; j < testCasesAutomatizados.size(); j++) {
                if (testPlan.getTestCase().get(i).equals(testCasesAutomatizados.get(j).getTestScriptName())) {
                    existInList = true;
                }
            }
            if (!existInList) {
                testCasesAutomatizados.add(testPlan.getTestCase().get(i));
                existInList = true;
            }
        }

    }

    ExtraiPlanilha extraiPlanilha = new ExtraiPlanilha();
    extraiPlanilha.exportTStoTI(testCasesAutomatizados, sheetTI);

    logger.info("Preparando para salvar planilha");
    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    logger.info("Fim mtodo - new FileOutputStream(destinationSheet) ");
    logger.info("Tentando gravar na planilha.");
    workbook.write(fileOut);
    logger.info("Fim mtodo - workbook.write(fileOut)");
    fileOut.close();
    fileSheet.close();
    sucess = true;
    logger.info("Planilha gerada.");
    return sucess;

}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*w  w  w. j  a  va 2 s. co  m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);

    descriptionPlan.setCellValue(testPlan.getName());
    prj.setCellValue(testPlan.getSti());
    fase.setCellValue(testPlan.getCrFase());
    testPhase.setCellValue(testPlan.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);
    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        stepNo.setCellValue(testPlan.getTestCase().get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(testPlan.getTestCase().get(i).getStepDescription());
        expectedResults.setCellValue(testPlan.getTestCase().get(i).getExpectedResults());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        linha = linha + 2;

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean updateTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/* w  ww .j a v a  2 s. c o  m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);

    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(testCase.getDataPlanejada());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public List<TesteCaseTSBean> readSheet(String pathSheetFull) throws FileNotFoundException, IOException {

    List<TesteCaseTSBean> listTS = new ArrayList<TesteCaseTSBean>();

    FileInputStream fileSheet = new FileInputStream(new File(pathSheetFull));
    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    int linha = 1;

    Row row = sheetTS.getRow(linha);//from w  ww . java 2  s  .  co  m

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    String descPlan = null;
    String project = null;
    String phase = null;

    if (!testScriptName.equals("")) {
        descPlan = descriptionPlan.getStringCellValue();
        project = prj.getStringCellValue();
        phase = testPhase.getStringCellValue();
    }

    while (!testScriptName.getStringCellValue().equals("") && testScriptName != null) {

        testCase = new TesteCaseTSBean();

        testCase.setTestScriptName(testScriptName.getStringCellValue());
        testCase.setTestScriptDescription(testScriptDescription.getStringCellValue());
        testCase.setStepDescription(stepDescription.getStringCellValue());
        testCase.setExpectedResults(expectedResults.getStringCellValue());
        testCase.setProduct(product.getStringCellValue());
        //            testCase.setDataPlanejada(dataPlanejada.getDateCellValue());
        testCase.setFase(fase.getStringCellValue());
        testCase.setTestPlan(descPlan);
        testCase.setSTIPRJ(project);
        testCase.setTestPhase(phase);
        testCase.setComplexidade(complexidade.getStringCellValue());
        testCase.setAutomatizado(automatizado.getBooleanCellValue());
        listTS.add(testCase);

        linha = linha + 2;

        row = sheetTS.getRow(linha);
        descriptionPlan = row.getCell(0);
        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    return listTS;
}

From source file:com.accounting.accountMBean.DifferentAccReports.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//  w  w  w  .  jav  a  2s .com
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES AGEING REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.mbeans.SmsController.java

public void handleFileUpload(FileUploadEvent event) {
    //        FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    //        FacesContext.getCurrentInstance().addMessage(null, message);
    //        System.out.println("entering into excel upload ");
    if (!PhaseId.INVOKE_APPLICATION.equals(event.getPhaseId())) {
        event.setPhaseId(PhaseId.INVOKE_APPLICATION);
        event.queue();//w  w w  .ja  v a 2s.co  m
    } else {
        //do stuff here, #{ngoPhotoBean.description} is set
        System.out.println("smsMessage " + smsMessage);
        if (smsMessage.isEmpty() || smsMessage.length() < 5) {
            System.out.println("Please write sms message first.");
            HelperUtil.showErrorMessage("Please write sms message first.");
        } else {
            try {

                UploadedFile uploadedFile = (UploadedFile) event.getFile();
                String filename = uploadedFile.getFileName();
                //            System.out.println("file name is " + filename);
                InputStream input = uploadedFile.getInputstream();

                if (filename.endsWith("xlsx")) {

                    XSSFWorkbook wb = new XSSFWorkbook(input);
                    XSSFSheet sheet = wb.getSheetAt(0);
                    Iterator rows = sheet.rowIterator();
                    setupSmsInfoData(rows);
                } else if (filename.endsWith("xls")) {

                    HSSFWorkbook wb = new HSSFWorkbook(input);
                    HSSFSheet sheet = wb.getSheetAt(0);
                    Iterator rows = sheet.rowIterator();
                    setupSmsInfoData(rows);
                }

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

        checkBalance();
    }

}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSXOfStock(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*w w  w. ja  v  a2  s  .  co m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("STOCK VALUE WITH VALUES");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/* ww w  . ja va  2 s  .  co m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(pageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.adobe.acs.commons.data.Spreadsheet.java

License:Apache License

/**
 * Parse out the input file synchronously for easier unit test validation
 *
 * @return List of files that will be imported, including any renditions
 * @throws IOException if the file couldn't be read
 *//*from w w  w. j  a  va 2 s. co  m*/
private void parseInputFile(InputStream file) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook(file);

    final XSSFSheet sheet = workbook.getSheetAt(0);
    rowCount = sheet.getLastRowNum();
    final Iterator<Row> rows = sheet.rowIterator();

    Row firstRow = rows.next();
    headerRow = readRow(firstRow).stream().map(v -> v != null ? convertHeaderName(v.toString()) : null)
            .collect(Collectors.toList());
    headerTypes = readRow(firstRow).stream().map(Variant::toString)
            .collect(Collectors.toMap(this::convertHeaderName, this::detectTypeFromName, this::upgradeToArray));

    Iterable<Row> remainingRows = () -> rows;
    dataRows = StreamSupport.stream(remainingRows.spliterator(), false).map(this::buildRow)
            .filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
}