Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

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

Usage

From source file:cn.lhfei.fu.service.impl.TeacherServiceImplTest.java

License:Apache License

@Test
public void updateTitle() throws Exception {
    String filePath = "src\\test\\resource\\excel\\??? - 2014-10-31???.xlsx";
    String teacherId = "";
    String teacherTitle = "";

    InputStream inp = new FileInputStream(filePath);

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);/*from  www.j av  a2  s .  co m*/

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell teacherIdCell = null;
    Cell teacherTitleCell = null;

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);
        teacherIdCell = row.getCell(1);
        teacherTitleCell = row.getCell(8);

        teacherId = teacherIdCell.getStringCellValue();

        if (teacherId != null && teacherId.startsWith("S")) {
            teacherId = teacherId.replaceAll("S", "");
        }

        teacherTitle = teacherTitleCell.getStringCellValue();

        log.info("ID: {}, Title: {}", teacherId, teacherTitle);

        teacherService.updateTeacherTitle(teacherId, teacherTitle);
    }

}

From source file:cn.lhfei.fu.service.impl.ThesisDataBuildFactory.java

License:Apache License

@Override
public boolean importDataByExcel(String filePath, Map<String, Object> params) throws Exception {

    Date currentDate = new Date();
    List<ThesisBase> thesisList = new ArrayList<ThesisBase>();

    InputStream inp = new FileInputStream(filePath);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);/* w w  w . j a v  a  2s .c  om*/

    int totalRows = sheet.getPhysicalNumberOfRows();

    Cell classCell = null;
    Cell studentNameCell = null;
    Cell studentIdCell = null;

    String className = "";
    String studentId = "";
    String studentName = "";

    String desc = "?";
    Integer degree = (Integer) params.get("degree");

    for (int i = 1; i < totalRows; i++) {
        Row row = sheet.getRow(i);

        classCell = row.getCell(0);
        studentNameCell = row.getCell(1);
        studentIdCell = row.getCell(2);

        className = classCell.getStringCellValue();
        studentName = studentNameCell.getStringCellValue();
        studentId = studentIdCell.getStringCellValue();

        ThesisBase model = new ThesisBase();
        model.setClassName(className);
        model.setStudentName(studentName);
        model.setStudentId(studentId);
        model.setDegree(degree);
        model.setDesc(desc);
        model.setActionType("" + OperationTypeEnum.PLSC.getCode());

        model.setOperationTime(currentDate);
        model.setCreateTime(currentDate);
        model.setModifyTime(currentDate);

        thesisList.add(model);
    }

    log.info("Batch size: ", thesisList.size());

    for (ThesisBase thesis : thesisList) {

        thesisBaseService.save(thesis);
    }

    return false;
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * ??ExcelTitle/*from   w ww  .  j  a  v  a2 s.  c  o m*/
 *
 * @param excelFile
 * @param sheetName sheet???
 * @return
 */
public static List<String> scanExcelTitles(File excelFile, String... sheetName) {
    List<String> titles = new ArrayList<>();
    try {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile));

        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        Row row = sheet.getRow(0);
        if (row != null) {
            int i = 0;
            while (true) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    break;
                }
                titles.add(cell.getStringCellValue());
                i++;
            }
        }
    } catch (Exception e) {
        logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error");
        throw new RuntimeException(e);
    }
    return titles;
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * @param workbook /*w ww. j a v a  2 s. c o  m*/
 * @param fieldNames ??
 * @param sheetName ???
 * @return
 */
private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) {
    String[] strKey = fieldNames.split(",");
    List<Map<String, String>> listMap = new ArrayList<>();
    int i = 1;
    try {
        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        while (true) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            Map<String, String> map = new HashMap<String, String>();
            map.put("rowid", String.valueOf(row.getRowNum()));
            for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) {
                Cell cell;
                cell = row.getCell(keyIndex);
                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // ?cell?Date
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // Date?CellDate
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        // 
                        else {
                            // ??Cell
                            Integer num = new Integer((int) cell.getNumericCellValue());
                            cellValue = String.valueOf(num);
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        cellValue = " ";
                    }
                }
                map.put(strKey[keyIndex], cellValue);
            }
            listMap.add(map);
            i++;
        }
    } catch (Exception e) {
        logger.debug("?" + i + "??");
        throw new RuntimeException(e);
    }
    return listMap;
}

From source file:cn.poi.api.example.ExcelExample.java

License:Open Source License

public static void ReadExcel(String excel, String brandcode)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    List<BrandConfigCommand> list = new ArrayList<>();
    InputStream inp = resourceLoader.getResource(excel).getInputStream();
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(4);/*from w ww. j av a2s.  c om*/
    Row tempRow = null;
    for (int i = 1; i < sheet.getLastRowNum(); i++) {
        BrandConfigCommand brandConfigCommand = new BrandConfigCommand();
        String[] array = new String[5];
        Row row = sheet.getRow(i);
        if (StringUtils.isEmpty(row.getCell(4).toString())) {
            continue;
        }
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j == 0 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(0).getStringCellValue();
                continue;
            } else if (j == 0) {
                tempRow = row;
            }
            if (j == 1 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(j).getStringCellValue();
                continue;
            }
            array[j] = row.getCell(j).getStringCellValue();
            System.out.print(row.getCell(j).toString() + "    ");

        }

        brandConfigCommand.setStoreCode(array[0]);
        brandConfigCommand.setStoreDate(array[1]);
        brandConfigCommand.setProvice(array[2]);
        brandConfigCommand.setCity(array[3]);
        brandConfigCommand.setArea(array[4]);
        brandConfigCommand.setArea_name(array[2] + array[3] + array[4]);
        list.add(brandConfigCommand);
        System.out.println();
    }

    List<String> listStr = new ArrayList<>();
    for (BrandConfigCommand brandConfigCommand : list) {
        String str = sqlStart + brandConfigCommand.getArea_name() + sqlEnd + provice
                + brandConfigCommand.getProvice() + city + brandConfigCommand.getCity() + area
                + brandConfigCommand.getArea() + code + brandConfigCommand.getStoreCode() + brand_code
                + brandcode + "'";
        listStr.add(str);
    }
    System.out.println(JSON.toJSONString(listStr));

}

From source file:co.com.runt.runistac.logica.ReporteLogica.java

public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos)
        throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook(plantilla);
    XSSFSheet mySheet = wb.getSheetAt(0);
    for (int i = 0; i < mySheet.getLastRowNum(); i++) {
        Row row = mySheet.getRow(i);
        if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) {
            for (String key : parametros.keySet()) {
                String valor = row.getCell(0).getStringCellValue();
                valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key));
                row.getCell(0).setCellValue(valor);
            }//from   ww w.  jav a  2  s .  co  m
        }
    }

    int rows = mySheet.getLastRowNum();
    int i = 0;
    Row base = mySheet.getRow(rows);
    CellStyle[] cs = null;
    if (!datos.isEmpty()) {
        int cant = datos.get(0).length;
        cs = new CellStyle[cant];
        for (int j = 0; j < cant; j++) {
            cs[j] = base.getCell(j).getCellStyle();
        }
    }
    for (Object[] o : datos) {
        Row row = mySheet.createRow(rows + i);
        for (int j = 0; j < o.length; j++) {
            Cell c = row.createCell(j);
            String value = "";
            if (o[j] != null) {
                if (o[j] instanceof String) {
                    value = (String) o[j];
                    c.setCellValue(value);
                } else if (o[j] instanceof Integer) {//integer
                    c.setCellValue((Integer) o[j]);
                } else if (o[j] instanceof Double) {
                    c.setCellValue((Double) o[j]);
                } else if (o[j] instanceof Float) {
                    c.setCellValue((Float) o[j]);
                } else if (o[j] instanceof BigDecimal) {
                    c.setCellValue(((BigDecimal) o[j]).doubleValue());
                } else if (o[j] instanceof Date) {
                    c.setCellValue(((Date) o[j]));
                } else if (o[j] instanceof BigInteger) {
                    c.setCellValue(((BigInteger) o[j]).intValue());
                } else {
                    c.setCellValue(o[j].toString());
                    System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass());
                }
            }
            c.setCellStyle(cs[j]);
        }
        i++;
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);

    return baos.toByteArray();
}

From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java

public static List<Producto> getProductosFromXls(byte[] bytes) {
    List<Producto> productos = new ArrayList<>();
    try {/*w ww. j a v a  2 s  .  c  o m*/
        HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));
        HSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Producto producto = new Producto();
            Row row = rowIterator.next();
            String nombre = row.getCell(0).getStringCellValue();
            producto.setNombre(nombre);
            String referencia = getString(row, 1);
            producto.setReferencia(referencia);
            if (row.getCell(2) != null) {
                String nombreMarca = row.getCell(2).getStringCellValue();
                if (nombreMarca != null && nombreMarca.trim().length() > 0) {
                    Marca marca = new Marca();
                    marca.setNombre(nombreMarca);
                    producto.setMarca(marca);
                }
            }
            if (row.getCell(3) != null) {
                String imagen = row.getCell(3).getStringCellValue();
                producto.setImg(imagen);
            }
            BigDecimal precio = null;
            try {
                String precioString = getString(row, 4);
                precio = new BigDecimal(precioString);
                producto.setPrecio(precio);
            } catch (Exception e) {
                continue;
            }
            if (row.getCell(5) != null) {
                BigDecimal precioOferta = null;
                try {
                    String precioString = getString(row, 5);
                    precioOferta = new BigDecimal(precioString);
                    producto.setPrecioOferta(precioOferta);
                } catch (Exception e) {
                }
            }

            if (producto.getPrecioOferta() == null) {
                producto.setPrecioOferta(new BigDecimal("0"));
            }

            if (row.getCell(6) != null) {
                String descripcion = row.getCell(6).getStringCellValue();
                producto.setDescripcion(descripcion);
            }

            if (row.getCell(7) != null) {
                String especificaciones = row.getCell(7).getStringCellValue();
                producto.setEspecificaciones(especificaciones);
            }
            if (row.getCell(8) != null) {
                String nombreSeccion = row.getCell(8).getStringCellValue();
                if (nombreSeccion != null && nombreSeccion.trim().length() > 0) {
                    String[] split = nombreSeccion.split("-");
                    if (split.length == 2) {
                        Seccion seccion = new Seccion();
                        seccion.setNombre(split[0]);
                        Subseccion subseccion = new Subseccion();
                        subseccion.setNombre(split[1]);
                        subseccion.setSeccion(seccion);
                        producto.setSubseccion(subseccion);
                    }
                }
            }
            productos.add(producto);
        }

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

From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java

private static String getString(Row row, int i) {
    Cell cell = row.getCell(i);
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        DecimalFormat format = new DecimalFormat("#");
        return format.format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    }//w w w. j  av a 2  s.co m
    return null;
}

From source file:CODIGOS.EditaPlanilha.java

public static void edita(String diretorio, String arquivo, int linha, int coluna, String texto) {
    try {// ww  w.ja  v a  2  s  . c o  m
        File dir = new File(diretorio);
        File file = new File(dir, arquivo + ".xlsx");
        InputStream inp = new FileInputStream(file);
        try {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(linha);//LINHA
            Cell cell = row.getCell(0);
            cell = row.createCell(coluna);//COLUNA
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(texto);
            try (FileOutputStream fileOut = new FileOutputStream(file)) {
                wb.write(fileOut);
            }
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex);
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.aan.girsang.client.ui.master.barang.BarangPanel.java

private void exportExcel(List<Barang> dataList) throws IOException {
    if (dataList != null && !dataList.isEmpty()) {
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet();
        HSSFSheet worksheet = workBook.createSheet("Sheet 0");
        // Nama Field
        Row judul = sheet.createRow((short) 0);
        Cell cell = judul.createCell((short) 0);
        cell.setCellValue("This is a test of merging");
        HSSFRow headingRow = sheet.createRow((short) 2);
        headingRow.createCell((short) 0).setCellValue("ID");
        headingRow.createCell((short) 1).setCellValue("BARCODE 1");
        headingRow.createCell((short) 2).setCellValue("BARCODE 2");
        headingRow.createCell((short) 3).setCellValue("NAMA BARANG");
        headingRow.createCell((short) 4).setCellValue("GOLONGAN");
        headingRow.createCell((short) 5).setCellValue("SAT. JUAL");
        headingRow.createCell((short) 6).setCellValue("ST. TOKO");
        headingRow.createCell((short) 7).setCellValue("ST. GUDANG");
        headingRow.createCell((short) 8).setCellValue("SAT. BELI");
        headingRow.createCell((short) 9).setCellValue("ISI PEM.");
        headingRow.createCell((short) 10).setCellValue("HRG PEM.");
        headingRow.createCell((short) 11).setCellValue("HRG NORMAL");
        headingRow.createCell((short) 12).setCellValue("HRG MEMBER");
        headingRow.createCell((short) 13).setCellValue("JUAL");
        int panjang = headingRow.getLastCellNum() - 1;
        short rowNo = 3;

        sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                panjang //last column  (0-based)
        ));//from w  w  w .  j  a va2 s. c  o  m
        CellStyle styleData = workBook.createCellStyle();
        styleData.setBorderBottom(CellStyle.BORDER_THIN);
        styleData.setBorderRight(CellStyle.BORDER_THIN);
        styleData.setBorderLeft(CellStyle.BORDER_THIN);
        for (Barang b : dataList) {
            HSSFRow row = sheet.createRow(rowNo);
            String jual;
            if (b.getJual() == true) {
                jual = "Jual";
            } else {
                jual = "Tidak";
            }
            row.createCell((short) 0).setCellValue(b.getPlu());
            row.createCell((short) 1).setCellValue(b.getBarcode1());
            row.createCell((short) 2).setCellValue(b.getBarcode2());
            row.createCell((short) 3).setCellValue(b.getNamaBarang());
            row.createCell((short) 4).setCellValue(b.getGolonganBarang().getGolonganBarang());
            row.createCell((short) 5).setCellValue(b.getSatuan());
            row.createCell((short) 6).setCellValue(b.getStokToko());
            row.createCell((short) 7).setCellValue(b.getStokGudang());
            row.createCell((short) 8).setCellValue(b.getSatuanPembelian());
            row.createCell((short) 9).setCellValue(b.getIsiPembelian());
            row.createCell((short) 10).setCellValue(TextComponentUtils.formatNumber(b.getHargaBeli()));
            row.createCell((short) 11).setCellValue(TextComponentUtils.formatNumber(b.getHargaNormal()));
            row.createCell((short) 12).setCellValue(TextComponentUtils.formatNumber(b.getHargaMember()));
            row.createCell((short) 13).setCellValue(jual);
            for (int i = 0; i <= 13; i++) {
                row.getCell((short) i).setCellStyle(styleData);
            }
            rowNo++;
        }
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        Font font = workBook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //style judul
        CellStyle styleTitle = workBook.createCellStyle();
        styleTitle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleTitle.setFont(font);
        judul.getCell(0).setCellStyle(styleTitle);

        //judul field
        CellStyle styleHeading = workBook.createCellStyle();
        styleHeading.setFont(font);
        styleHeading.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleHeading.setBorderBottom(CellStyle.BORDER_THIN);
        styleHeading.setBorderTop(CellStyle.BORDER_THIN);
        styleHeading.setBorderRight(CellStyle.BORDER_THIN);
        styleHeading.setBorderLeft(CellStyle.BORDER_THIN);
        for (int i = 0; i < headingRow.getLastCellNum(); i++) {//For each cell in the row 
            headingRow.getCell(i).setCellStyle(styleHeading);//Set the style
        }
        String file = "D:/Student_detais.xls";
        try {
            try (FileOutputStream fos = new FileOutputStream(file)) {
                workBook.write(fos);
            }
            JOptionPane.showMessageDialog(null, "Sukses");
        } catch (FileNotFoundException e) {
            System.out.println("Invalid directory or file not found");
        } catch (IOException e) {
            System.out.println("Error occurred while writting excel file to directory");
        }
    }
}