Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum) 

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java

License:Open Source License

public int getColumnCount() {
    if (csize == 0) {
        short maxcsize = 0;
        for (int i = 0; i < 50; i++)//test 10 rows
        {//w  w  w .ja  v a 2s .  c o  m
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                HSSFCell cell;
                short j = 0;
                short skipped = 0;
                for (; j < 100; j++)//test at least 100 columns (to overcome empty columns)
                {
                    cell = row.getCell(j);
                    if (cell != null) {
                        maxcsize++;
                        maxcsize += skipped;
                        skipped = 0;
                    } else {
                        skipped++;
                    }
                }
                while (j > 0 && (cell = row.getCell(j)) != null)//add if there are even more
                {
                    maxcsize++;
                    j++;
                }
                if (csize < maxcsize) {
                    csize = maxcsize;
                }
                maxcsize = 0;
            }
        }
    }
    return csize;
}

---

From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java

License:Open Source License

public Object getValueAt(int r, int c) {
    if (r == -1) {
        r = 0;/*  ww w.jav a2s . c o  m*/
    } else if (useHeaderRow) {
        r++;
    }
    HSSFRow row = sheet.getRow(r);
    if (row != null) {
        HSSFCell cell = row.getCell((short) c);
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                Number d = new Double(cell.getNumericCellValue());
                if (((int) d.doubleValue()) == Math.ceil(d.doubleValue())) {
                    d = new Integer(d.intValue());
                }
                return d;
            //               case HSSFCell.CELL_TYPE_NUMERIC:
            //               return cell.getStringCellValue();   

            default:
                return cell.getStringCellValue();
            }
        }
    }
    return null;
}

---

From source file:com.siteview.ecc.report.xls.JRXlsExporter.java

License:Open Source License

protected void createMergeRegion(JRExporterGridCell gridCell, int colIndex, int rowIndex,
        HSSFCellStyle cellStyle) {//from w  w w  .  j  a  v  a2  s .co m
    int rowSpan = isCollapseRowSpan ? 1 : gridCell.getRowSpan();
    if (gridCell.getColSpan() > 1 || rowSpan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, (rowIndex + rowSpan - 1), colIndex,
                (colIndex + gridCell.getColSpan() - 1)));

        for (int i = 0; i < rowSpan; i++) {
            HSSFRow spanRow = sheet.getRow(rowIndex + i);
            if (spanRow == null) {
                spanRow = sheet.createRow(rowIndex + i);
            }
            for (int j = 0; j < gridCell.getColSpan(); j++) {
                HSSFCell spanCell = spanRow.getCell((colIndex + j));
                if (spanCell == null) {
                    spanCell = spanRow.createCell((colIndex + j));
                }
                spanCell.setCellStyle(cellStyle);
            }
        }
    }
}

---

From source file:com.siva.javamultithreading.ExcelUtil.java

public static void copyRow(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow,
        HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        HSSFCell oldCell = srcRow.getCell(j);
        HSSFCell newCell = destRow.getCell(j);
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }//from   w w w  .j ava2  s .c  om
            copyCell(newWorkbook, oldCell, newCell, styleMap);
        }
    }
}

---

From source file:com.smanempat.controller.ControllerClassification.java

private void showXLS(JTextField txtFileDirectory, JTable tablePreview)
        throws FileNotFoundException, IOException {
    DefaultTableModel tableModel = new DefaultTableModel();
    File fileName = new File(txtFileDirectory.getText());
    FileInputStream inputStream = new FileInputStream(fileName);
    HSSFWorkbook workBook = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = workBook.getSheetAt(0);

    int rowValue = sheet.getLastRowNum() + 1;
    int colValue = sheet.getRow(0).getLastCellNum();
    String[][] data = new String[rowValue][colValue];
    String[] colName = new String[colValue];
    for (int i = 0; i < rowValue; i++) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < colValue; j++) {
            HSSFCell cell = row.getCell(j);
            int type = cell.getCellType();
            Object returnCellValue = null;
            if (type == 0) {
                returnCellValue = cell.getNumericCellValue();
            } else if (type == 1) {
                returnCellValue = cell.getStringCellValue();
            }//from  w ww. j  a va2  s . com

            data[i][j] = returnCellValue.toString();
        }
    }

    for (int i = 0; i < colValue; i++) {
        colName[i] = data[0][i];
    }

    tableModel = new DefaultTableModel(data, colName);
    tablePreview.setModel(tableModel);
    tableModel.removeRow(0);
}

---

From source file:com.softtek.mdm.web.admin.IndexController.java

private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists,
        HttpServletResponse response) {/*from ww  w .j  a v a2s  .  co m*/

    OutputStream out = null;
    try {
        HSSFWorkbook workbook = new HSSFWorkbook(); //   
        HSSFSheet sheet = workbook.createSheet(sheetName); //   
        //   
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);
        //sheet??getColumnTopStyle()/getStyle()? - ?  - ?  
        HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//??  
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(sheetName);
        //   
        int columnNum = headNames.length;
        HSSFRow rowRowName = sheet.createRow(2); // 2?()  
        // sheet?  
        for (int n = 0; n < columnNum; n++) {
            HSSFCell cellRowName = rowRowName.createCell(n); //?  
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //??  
            HSSFRichTextString text = new HSSFRichTextString(headNames[n]);
            cellRowName.setCellValue(text); //?  
            cellRowName.setCellStyle(columnTopStyle); //??  
        }
        //?sheet?  
        HSSFDataFormat format = workbook.createDataFormat();
        short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss");
        for (int i = 0; i < lists.size(); i++) {
            HSSFRow row = sheet.createRow(i + 3);//  
            OrganizationModel obj = lists.get(i);//???  
            /*row.createCell(0).setCellValue(obj.getOrgType());*/
            row.createCell(0).setCellValue(obj.getName());
            row.createCell(1).setCellValue(obj.getCreateName());
            row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers());
            row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices());
            row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount());
            row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers());
            HSSFCell cell = row.createCell(6);
            cell.setCellValue(obj.getCreateTime());
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(formatDate);
            cell.setCellStyle(cellStyle);
        }
        //??  
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //?  
                currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256));
        }
        if (workbook != null) {
            try {
                String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13)
                        + ".xls";
                String headStr = "attachment; filename=\"" + fileName + "\"";
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", headStr);
                out = response.getOutputStream();
                workbook.write(out);
            } catch (IOException e) {
                logger.error(e.getMessage());
            } finally {
                if (out != null) {
                    out.close();
                }
            }
        }
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}

---

From source file:com.sunesoft.ancon.core.saleContract.application.UploadSaleContracts.java

@Override
protected UniqueResult<SaleContractDto> convertRow(HSSFRow row, int rowNum, int colNum) {
    try {//ww  w. ja v  a2s  .co m

        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);

        SaleContractDto dto = new SaleContractDto();
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(0)))) {
            row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);//?? ?
            dto.setNum(getCellFormatValue(row.getCell(0)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(1)))) {
            /*  // ?
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setCellStyle(cellStyle);*/

            //                row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);//?? ?
            dto.setName(getCellFormatValue(row.getCell(1)));
        }

        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(2)))) {
            String str = getCellFormatValue(row.getCell(2));
            int count = 0;
            for (int i = 0; i < str.length(); i++) {
                if (!Character.isDigit(str.charAt(i))) {
                    if (str.contains(".")) {
                        count++;
                        if (count > 1) {
                            dto.setContractMoney(BigDecimal.valueOf(0));
                            break;
                        }
                        continue;
                    }
                    dto.setContractMoney(BigDecimal.valueOf(0));
                    break;
                }

            }
            if (dto.getContractMoney() == null)
                dto.setContractMoney(BigDecimal
                        .valueOf(Double.valueOf(getCellFormatValue(row.getCell(2)).trim())).movePointLeft(4));
        } else {
            dto.setContractMoney(BigDecimal.valueOf(0));
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(3)))) {
            dto.setJudgeMoney(dto.getContractMoney());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(4)))) {
            dto.setJudgeStatus(getCellFormatValue(row.getCell(4)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(5)))) {
            dto.setJudgeTime(DateHelper.parse(getCellFormatValue(row.getCell(5)), "yyyy-MM-dd"));
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(6)))) {
            dto.setBranchCompany(getCellFormatValue(row.getCell(6)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(7)))) {
            dto.setJiaFangName(getCellFormatValue(row.getCell(7)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(8)))) {
            dto.setContractType(getCellFormatValue(row.getCell(8)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(9)))) {
            dto.setBidNotice(getCellFormatValue(row.getCell(9)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(10)))) {
            dto.setConstructLicense(getCellFormatValue(row.getCell(10)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(11)))) {
            dto.setFinishCheck(getCellFormatValue(row.getCell(11)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(12)))) {
            dto.setProjectSettlement(getCellFormatValue(row.getCell(12)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(13)))) {
            dto.setContractBeginTime(
                    DateHelper.parse(getCellFormatValue(row.getCell(13)).trim(), "yyyy-MM-dd"));
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(14)))) {

            dto.setProjectStartTime(DateHelper.parse(getCellFormatValue(row.getCell(14)).trim(), "yyyy-MM-dd"));
        } else {

            dto.setProjectStartTime(DateHelper.parse((year + "-01-01"), "yyyy-MM-dd"));
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(15)))) {
            dto.setContractEndTime(DateHelper.parse(getCellFormatValue(row.getCell(15)).trim(), "yyyy-MM-dd"));
        } else {
            dto.setContractEndTime(DateHelper.parse((year + "-12-31"), "yyyy-MM-dd"));
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(16)))) {
            dto.setProjectMajor(getCellFormatValue(row.getCell(16)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(17)))) {
            dto.setContractStatus(getCellFormatValue(row.getCell(17)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(18)))) {
            dto.setContractIsReturn(getCellFormatValue(row.getCell(18)).trim());
        }
        if (!StringUtils.isNullOrWhiteSpace(getCellFormatValue(row.getCell(19)))) {
            dto.setRemark(getCellFormatValue(row.getCell(19)).trim());
        } else {
            dto.setRemark(getCellFormatValue(row.getCell(19)).trim());
        }

        return new UniqueResult<SaleContractDto>(dto);
    } catch (Exception ex) {
        return new UniqueResult<SaleContractDto>("" + rowNum + ",???");
    }
}

---

From source file:com.syncnapsis.utils.data.ExcelHelper.java

License:Open Source License

public static void main(String[] args) throws Exception {
    String fileName = "testdata.xls";
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("src/main/resources/" + fileName));

    HSSFSheet sheet = workbook.getSheet("Benutzer");
    HSSFRow row = sheet.getRow(0);
    Cell cell;/* w  w  w.  ja v  a  2 s .co  m*/

    for (int i = 2; i < 256; i++) {
        try {
            cell = row.getCell(i);
            if (cell == null)
                cell = row.createCell(i);
            cell.setCellFormula("IF(ISBLANK(Benutzer!A" + (i - 1) + "),\"\",Benutzer!A" + (i - 1) + ")");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    workbook.write(new FileOutputStream("src/main/resources/new.xls"));
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Laden der Spalten fr die Kontakt-Rechte
 * /*from www .java2 s .c om*/
 * @param workbook - das Workbook
 * @param key_sheet_authorities_c - der Key fr das Sheet
 */
public static void parseContactAuthorities(HSSFWorkbook workbook, String key_sheet_authorities_c) {
    HSSFSheet sheet_authorities_c = workbook.getSheet(key_sheet_authorities_c);

    // sheet_authorities_c -> Laden der Spalten fr die Kontakt-Rechte
    HSSFRow row = sheet_authorities_c.getRow(0);
    for (int i = 1; i < 255; i++) {
        if (row.getCell(i) == null)
            break;
        contactAuthorities_colToName.put(i, row.getCell(i).getStringCellValue());
    }
    logger.debug("contact-authority-columns loaded: " + contactAuthorities_colToName.size());

    String name;

    // sheet_authorities_c -> Laden der Kontakt-Rechte
    int rowNum = 2;
    while ((row = sheet_authorities_c.getRow(rowNum++)) != null) {
        try {
            name = row.getCell(0).getStringCellValue();
            contactAuthorities.put(name, new TreeMap<String, Boolean>());

            for (int i = 1; i < 255; i++) {
                if (row.getCell(i) == null)
                    break;
                contactAuthorities.get(name).put(contactAuthorities_colToName.get(i),
                        row.getCell(i).getNumericCellValue() == 1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_authorities_c + " at line " + rowNum);
        }
    }
    logger.debug("contact-authorities loaded: " + contactAuthorities.size());
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Laden der Spalten fr die Allianz-Rechte
 * /*from w w  w. jav  a2 s. c  om*/
 * @param workbook - das Workbook
 * @param key_sheet_authorities_a - der Key fr das Sheet
 */
public static void parseAllianceAuthorities(HSSFWorkbook workbook, String key_sheet_authorities_a) {
    HSSFSheet sheet_authorities_a = workbook.getSheet(key_sheet_authorities_a);

    // sheet_authorities_a -> Laden der Spalten fr die Allianz-Rechte
    HSSFRow row = sheet_authorities_a.getRow(0);
    for (int i = 3; i < 255; i++) {
        if (row.getCell(i) == null)
            break;
        allianceAuthorities_colToName.put(i, row.getCell(i).getStringCellValue());
    }
    logger.debug("alliance-authority-columns loaded: " + allianceAuthorities_colToName.size());

    String name, fullname, parent, ref, weight;

    // sheet_authorities_a -> Laden der Allianz-Rechte
    int rowNum = 2;
    while ((row = sheet_authorities_a.getRow(rowNum++)) != null) {
        try {
            name = row.getCell(0).getStringCellValue();
            if (name == null || name.isEmpty())
                break;
            fullname = row.getCell(1).getStringCellValue();
            parent = row.getCell(2).getStringCellValue();
            weight = "" + (int) row.getCell(3).getNumericCellValue();
            allianceAuthorities.put(name, new TreeMap<String, Boolean>());
            allianceMemberRanks.put(name + "_full", fullname);
            allianceMemberRanks.put(name + "_parent", parent);
            allianceMemberRanks.put(name + "_weight", weight);

            for (int i = 5; i < 255; i++) {
                if (row.getCell(i) == null)
                    break;
                allianceAuthorities.get(name).put(allianceAuthorities_colToName.get(i),
                        row.getCell(i).getNumericCellValue() == 1);
            }

            ref = row.getCell(4).getStringCellValue(); // contactauthorities
            allianceAuthorities.get(name).putAll(contactAuthorities.get(ref));
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_authorities_a + " at line " + rowNum);
        }
    }
    logger.debug("alliance-authorities loaded: " + allianceAuthorities.size());
    logger.debug("alliance-ranks loaded: " + allianceMemberRanks.size());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »