Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.mycompany.mavenproject1.MainExecutor.java

public static void main(String[] args) throws Exception {
    FileInputStream fio = new FileInputStream(new File(pathToInput));

    //Read/*from  w  w w .  j a v a  2 s . c om*/
    HSSFWorkbook inputExcel = new HSSFWorkbook(fio);

    // Write 
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");

    LinkedHashSet<String> players = getAllPlayerNames(inputExcel);
    System.out.println(players.size());
    Iterator<String> playersIterator = players.iterator();

    Map<String, Object[]> data = getNewWorkbookData(inputExcel, playersIterator);
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        System.out.println("com.mycompany.mavenproject1.MainExecutor.main");
        System.out.println(rownum);
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(new File(pathToModel1));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.mycompany.mavenproject1.ragaiproject.ExcelExport.java

public void writeSheet() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    HSSFSheet s1 = (HSSFSheet) wb.createSheet("Sheet 1");

    Row row = s1.createRow((short) 0);
    Cell cell = row.createCell(0);/*from www  .  j  av  a2s  . c om*/
    cell.setCellValue(1);
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream("workbook.xls");
        try {
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
        }

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

}

From source file:com.mycompany.mavenproject1.ragaiproject.PDFManipulation.java

public void convert(List<PDDocument> pdfList, List<String> selectedFields, String fileName) {

    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    HSSFSheet s1 = (HSSFSheet) wb.createSheet("Sheet 1");

    Row header = s1.createRow((short) 0);

    //initialize column headers
    for (int i = 0; i < selectedFields.size(); i++) {
        Cell headerCell = header.createCell(i);
        headerCell.setCellValue(selectedFields.get(i));
    }//w w  w.  j  a va2 s .co  m

    //for(int i = 0; i < selectedFields.size();i++){ //fills out row
    //Cell dataCell = data.createCell(i);

    for (int y = 0; y < pdfList.size(); y++) {
        PDDocumentCatalog docCatalog = pdfList.get(y).getDocumentCatalog();
        PDAcroForm acroForm = docCatalog.getAcroForm();
        java.util.List<PDField> fields = acroForm.getFields();
        Row data = s1.createRow((short) y + 1);
        for (int i = 0; i < selectedFields.size(); i++) {
            Cell dataCell = data.createCell(i);
            for (PDField field : fields) {
                System.out.println("Field Value: " + field.getValueAsString());
                if (field.getPartialName().equals(selectedFields.get(i))) {

                    dataCell.setCellValue(field.getValueAsString());
                }

            }
        }

        /* for(int j = 0; j < this.fieldLabelPairs.size();j++){
        if(this.fieldLabelPairs.get(j).getLabel().equals(selectedFields.get(i))){
            dataCell.setCellValue(this.fieldLabelPairs.get(j).getValue());
                    
        }
        }*/
    }

    //}
    /*for (int i = 0; i < selectedFields.size(); i++){
        Row data = s1.createRow(i+1);
                
        for(int j = 0; j< this.fieldLabelPairs.length; j++){
       Cell dataCell  = data.createCell(i);
       if(this.fieldLabelPairs[j].getLabel().equals(selectedFields.get(i))){
           dataCell.setCellValue(this.fieldLabelPairs[j].getValue());
       }
               
               
    }
    }*/

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(fileName + ".xls");
        try {
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelExport.class.getName()).log(Level.SEVERE, null, ex);
        }

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

}

From source file:com.netxforge.netxstudio.server.logic.reporting.BaseComponentReportingLogic.java

License:Open Source License

public void createHeaderStructure(HSSFSheet sheet) {

    CellStyle baseStyle = this.getWorkBook().createCellStyle();
    baseStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    baseStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    baseStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    baseStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    baseStyle.setAlignment(CellStyle.ALIGN_LEFT);

    CellStyle typeStyle = this.getWorkBook().createCellStyle();
    typeStyle.cloneStyleFrom(baseStyle);

    Font typeFont = getWorkBook().createFont();
    typeFont.setFontHeightInPoints((short) 24);
    typeStyle.setFont(typeFont);//from ww  w.  jav a  2  s . c o  m

    HSSFRow typeRow = sheet.createRow(0);
    typeCell = typeRow.createCell(0);
    typeCell.setCellValue("<Service Type>");
    typeCell.setCellStyle(typeStyle);

    for (int i = 1; i < HEADER_CELL_SIZE; i++) {
        typeRow.createCell(i).setCellStyle(typeStyle);
    }

    CellStyle titleStyle = this.getWorkBook().createCellStyle();
    titleStyle.cloneStyleFrom(baseStyle);

    Font titleFont = getWorkBook().createFont();
    titleFont.setFontHeightInPoints((short) 16);
    titleStyle.setFont(titleFont);

    HSSFRow titleRow = sheet.createRow(1);
    titleCell = titleRow.createCell(0);
    titleCell.setCellValue("<Report title>");
    titleCell.setCellStyle(titleStyle);

    for (int i = 1; i < HEADER_CELL_SIZE; i++) {
        titleRow.createCell(i).setCellStyle(titleStyle);
    }

    HSSFRow periodRow = sheet.createRow(2);
    periodCell = periodRow.createCell(0);
    periodCell.setCellValue("<Period>");
    periodCell.setCellStyle(titleStyle);

    for (int i = 1; i < HEADER_CELL_SIZE; i++) {
        periodRow.createCell(i).setCellStyle(typeStyle);
    }

    // Merge
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, HEADER_CELL_SIZE - 1));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, HEADER_CELL_SIZE - 1));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, HEADER_CELL_SIZE - 1));

}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

public boolean createTotalCells(List totalCellIndexes, HSSFSheet sheet, HSSFCellStyle[] totalStyle,
        Short totalStyleHeight, int rowStart, int cellStart, int rowCurrent) {
    HSSFRow row;/*from  w w  w.  j a v a 2  s.  c  o m*/
    HSSFCell cell;
    int rowTotal;
    if (rowCurrent == rowStart)
        rowCurrent++;
    rowTotal = rowCurrent - 1;
    row = sheet.createRow(rowCurrent);
    row.setHeight(totalStyleHeight);

    for (Iterator it = totalCellIndexes.iterator(); it.hasNext();) {
        Integer cellIndex = (Integer) it.next();
        cell = row.createCell(cellIndex);
        String columnLetter = CellReference.convertNumToColString(cellIndex);
        cell.setCellFormula("SUM(" + columnLetter + (rowStart + 1) + ":" + columnLetter + (rowTotal + 1) + ")");
        cell.setCellStyle(totalStyle[cellIndex]);
    }

    return true;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printClaim(List<PrintClaimPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;/*w  w w  .  ja  v a  2  s.  c  o  m*/

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "print_claim.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (PrintClaimPojo pcp : listPojo) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, pcp.getTin());
        createCell(row, cellCurrent++, style, pcp.getMfo());
        createCell(row, cellCurrent++, style, pcp.getChet());
        createCell(row, cellCurrent++, style, pcp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(pcp.getPaymentDate()));
        createCell(row, cellCurrent++, style, pcp.getPaymentSum());
        createCell(row, cellCurrent++, style, pcp.getPaymentDetails());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 6); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printRegister(List<PrintRegisterPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;//from  ww w  . j  a  v a  2 s.co  m

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "print_register.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (PrintRegisterPojo prp : listPojo) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, prp.getInvoiceNum());
        createCell(row, cellCurrent++, style, prp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(prp.getPaymentDate()));
        createCell(row, cellCurrent++, style, prp.getTin());
        createCell(row, cellCurrent++, style, prp.getName());
        createCell(row, cellCurrent++, style, prp.getPaymentSum());

    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 5); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printClaimRegister(List<PrintClaimRegisterPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;//from   w ww .j  a  v a  2s  .c  o  m

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "print_claim_register.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (PrintClaimRegisterPojo pcrp : listPojo) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, pcrp.getInvoiceNum());
        createCell(row, cellCurrent++, style, pcrp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(pcrp.getPaymentDate()));
        createCell(row, cellCurrent++, style, pcrp.getTin());
        createCell(row, cellCurrent++, style, pcrp.getName());
        createCell(row, cellCurrent++, style, pcrp.getPaymentSum());

    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 5); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printPaymentManual(BankStatement bs) throws Exception {
    HSSFWorkbook workbook = null;// ww  w .  j  a v a2s  . c  om

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "payment_manual.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFRow row = sheet.getRow(0);
    row.getCell(1).setCellValue(bs.getId());

    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum();
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    row = sheet.createRow(3);

    createCell(row, 1, style, "");
    createCell(row, 2, style, bs.getMfo());
    createCell(row, 3, style, bs.getChet());
    createCell(row, 4, style, bs.getPaymentNum());
    createCell(row, 5, style, dateFormat.format(bs.getPaymentDate()));
    createCell(row, 6, style, "");
    createCell(row, 7, style, bs.getTin());

    return workbook;
}

From source file:com.nkapps.billing.services.ReportPrintServiceImpl.java

@Override
public HSSFWorkbook printClick(List<ReportClickListPojo> listPojos) throws Exception {
    HSSFWorkbook workbook = null;//from   ww w.ja  va  2  s . co  m

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "report_click.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (ReportClickListPojo rclp : listPojos) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, dateFormat.format(rclp.getOperationDate()));
        createCell(row, cellCurrent++, style, dateFormat.format(rclp.getBsPaymentDate()));
        createCell(row, cellCurrent++, style, rclp.getBsPaymentNum());
        createCell(row, cellCurrent++, style, rclp.getBsPaymentSum());
        createCell(row, cellCurrent++, style, rclp.getClickPaymentSum());
        createCell(row, cellCurrent++, style, rclp.getDiffSum());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 4); // for 
    totalCellIndexes.add(CELL_START + 5); // for 
    totalCellIndexes.add(CELL_START + 6); // for 
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}