List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java
License:Apache License
public void setCellValue(Sheet shProcess, int nRow, int nCol, Object objValue, boolean isFormula, PoiCellStyle pCellStyle) {/*from w w w . j av a2s . c o m*/ // Logger logCurrent = // LoggerFactory.getLogger(WorkbookProcessor.class.getCanonicalName()); try { Row rw = shProcess.getRow(nRow); if (rw == null) { // logCurrent.finest("Create Row"); rw = shProcess.createRow(nRow); } Cell c = rw.getCell(nCol); if (c == null) { // logCurrent.finest("Create Cell"); c = rw.createCell(nCol); } if (isFormula) { c.setCellFormula((String) objValue); } else { if (objValue instanceof Double) { c.setCellValue((Double) objValue); } else if (objValue instanceof Integer) { c.setCellValue((Integer) objValue); } else { if (objValue instanceof Date) { c.setCellValue((Date) objValue); } else { c.setCellValue("" + objValue); } } } // *** STYLE CONFIG Since V 1.1.7 *** if (pCellStyle != null) { checkStyleConstantValues(); if (pCellStyle.getCellStyle() != null) { c.setCellStyle(pCellStyle.getCellStyle()); } else { CellStyle style = shProcess.getWorkbook().createCellStyle(); if (pCellStyle.getAlignment() != null) style.setAlignment(m_StyleConstantValues.get(pCellStyle.getAlignment())); if (pCellStyle.getBorderBottom() != null) style.setBorderBottom(m_StyleConstantValues.get(pCellStyle.getBorderBottom())); if (pCellStyle.getBorderLeft() != null) style.setBorderLeft(m_StyleConstantValues.get(pCellStyle.getBorderLeft())); if (pCellStyle.getBorderRight() != null) style.setBorderRight(m_StyleConstantValues.get(pCellStyle.getBorderRight())); if (pCellStyle.getBorderTop() != null) style.setBorderTop(m_StyleConstantValues.get(pCellStyle.getBorderTop())); if (pCellStyle.getBottomBorderColor() != null) style.setBottomBorderColor( IndexedColors.valueOf(pCellStyle.getBottomBorderColor()).getIndex()); if (pCellStyle.getDataFormat() != null) { DataFormat format = shProcess.getWorkbook().createDataFormat(); style.setDataFormat(format.getFormat(pCellStyle.getDataFormat())); } if (pCellStyle.getFillBackgroundColor() != null) style.setFillBackgroundColor( IndexedColors.valueOf(pCellStyle.getFillBackgroundColor()).getIndex()); if (pCellStyle.getFillForegroundColor() != null) style.setFillForegroundColor( IndexedColors.valueOf(pCellStyle.getFillForegroundColor()).getIndex()); if (pCellStyle.getFillPattern() != null) style.setFillPattern(m_StyleConstantValues.get(pCellStyle.getFillPattern())); // Create a new font and alter it. Font font = shProcess.getWorkbook().createFont(); if (pCellStyle.getFontBoldweight() != null) font.setBoldweight(m_StyleConstantValues.get(pCellStyle.getFontBoldweight())); if (pCellStyle.getFontColor() != null) font.setColor(IndexedColors.valueOf(pCellStyle.getFontColor()).getIndex()); if (pCellStyle.getFontHeightInPoints() != 0) font.setFontHeightInPoints(pCellStyle.getFontHeightInPoints()); if (pCellStyle.getFontName() != null) font.setFontName(pCellStyle.getFontName()); if (pCellStyle.isFontItalic()) font.setItalic(pCellStyle.isFontItalic()); if (pCellStyle.isFontStrikeout()) font.setStrikeout(pCellStyle.isFontStrikeout()); if (pCellStyle.getFontUnderline() != null) font.setUnderline(m_StyleByteConstantValues.get(pCellStyle.getFontUnderline())); if (pCellStyle.getFontTypeOffset() != null) font.setTypeOffset(m_StyleConstantValues.get(pCellStyle.getFontTypeOffset())); // Set Font style.setFont(font); if (pCellStyle.isHidden()) style.setHidden(pCellStyle.isHidden()); if (pCellStyle.getIndention() != null) style.setIndention(m_StyleConstantValues.get(pCellStyle.getIndention())); if (pCellStyle.getLeftBorderColor() != null) style.setLeftBorderColor(IndexedColors.valueOf(pCellStyle.getLeftBorderColor()).getIndex()); if (pCellStyle.isLocked()) style.setLocked(pCellStyle.isLocked()); if (pCellStyle.getRightBorderColor() != null) style.setRightBorderColor( IndexedColors.valueOf(pCellStyle.getRightBorderColor()).getIndex()); if (pCellStyle.getRotation() != 0) style.setRotation(pCellStyle.getRotation()); if (pCellStyle.getTopBorderColor() != null) style.setTopBorderColor(IndexedColors.valueOf(pCellStyle.getTopBorderColor()).getIndex()); if (pCellStyle.getVerticalAlignment() != null) style.setVerticalAlignment(m_StyleConstantValues.get(pCellStyle.getVerticalAlignment())); if (pCellStyle.isWrapText()) style.setWrapText(pCellStyle.isWrapText()); c.setCellStyle(style); pCellStyle.setCellStyle(style); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java
License:Apache License
public void findAndReplaceAll(Sheet sheet, String find, Object replace) { if (replace == null) { replace = ""; }//from www . j a va 2 s. c om int iLastRow = sheet.getLastRowNum(); for (int i1 = 0; i1 < iLastRow; i1++) { Row currentRow = sheet.getRow(i1); if (currentRow != null) { int iLastCell = currentRow.getLastCellNum(); for (int i = 0; i < iLastCell; i++) { Cell currentCell = currentRow.getCell(i); if (currentCell != null && currentCell.getCellType() == Cell.CELL_TYPE_STRING) { if (currentCell.getStringCellValue().contains(find)) { currentCell.setCellValue(currentCell.getStringCellValue().replace(find, "" + replace)); } } } } } }
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
/** * ???// w ww .j av a 2 s . c om * * @param sheet * * @throws SAXException * SAX???? */ private final void parseSheet(final Sheet sheet) throws SAXException { // ????? AttributesImpl attrImpl = new AttributesImpl(); attrImpl.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET), attrImpl); startSheet(sheet.getSheetName()); //getLastRowNum()??0???? +1? int maxRows = sheet.getLastRowNum() + 1; for (int row = 0; row < maxRows; row++) { startRow(row + 1); Row line = sheet.getRow(row); if (line != null) { for (int column = 0; column < line.getLastCellNum(); column++) { startColumn(column + 1); Cell cell = line.getCell(column); // ?trim()?????????????? String value = getCellValue(cell); fireCell(column + 1, row + 1, value); endColumn(column + 1); } } endRow(row + 1); } endSheet(sheet); // ????? getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET)); }
From source file:bo.com.offercruzmail.LectorBandejaCorreo.java
private Multipart procesarPorAdjunto(Message mensaje, Integer idUsuario) throws MessagingException { adjunto = null;/* w w w . j av a2 s . c o m*/ try { //No se pudo procesar por asunto, leer el adjunto si tiene adjunto = UtilitariosMensajes.bajarPrimerAdjunto(mensaje); } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); return FormadorMensajes.getMensajeUsuarioAyuda(); } if (adjunto == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } FileInputStream fis = null; try { Workbook libro; fis = new FileInputStream(adjunto); libro = WorkbookFactory.create(fis); Sheet hoja = libro.getSheetAt(0); Row fila = hoja.getRow(0); if (fila == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } Cell celda = fila.getCell(0); if (celda == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } String nombreEntidad = HojaExcelHelper.getValorCelda(celda).toLowerCase(); interprete = InterpretadorMensajeGenerico.getMapaObjetos().get(nombreEntidad); if (interprete == null) { return FormadorMensajes.getMensajeUsuarioAyuda(); } interprete.setLectorBandejaCorreo(this); interprete.setIdUsuario(idUsuario); interprete.setNombreEntidad(nombreEntidad); return interprete.interpretarHojaExcel(hoja); } catch (IOException | InvalidFormatException ex) { LOG.log(Level.SEVERE, "Error Leyendo adjunto", ex); return FormadorMensajes.getMensajeUsuarioAyuda(); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(LectorBandejaCorreo.class.getName()).log(Level.SEVERE, null, ex); } } } }
From source file:bouttime.fileinput.ExcelFileInput.java
License:Open Source License
/** * Input wrestlers from a MS Excel formatted file. * * @param file File to read data from/*from www .ja v a 2 s . c o m*/ * @param config Map of config parameters for column indexes, start and stop * row indexes, and the sheet number * @param dao Data access object * * @return A FileInputResult object with the results of the input operation */ private FileInputResult addWrestlersFromFile(File file, Map config, Dao dao) { Integer recordsProcessed = Integer.valueOf(0); Integer recordsAccepted = Integer.valueOf(0); Integer recordsRejected = Integer.valueOf(0); List<String> rejects = new ArrayList<String>(); try { int sheetNumber = Integer.parseInt((String) config.get("sheet")) - 1; int startRow = Integer.parseInt((String) config.get("startRow")) - 1; int endRow = Integer.parseInt((String) config.get("endRow")); int fNameCol = Integer.parseInt((String) config.get("firstName")) - 1; int lNameCol = Integer.parseInt((String) config.get("lastName")) - 1; int tNameCol = Integer.parseInt((String) config.get("teamName")) - 1; int gNameCol = Integer.parseInt((String) config.get("geo")) - 1; int classCol = Integer.parseInt((String) config.get("classification")) - 1; int divCol = Integer.parseInt((String) config.get("division")) - 1; int wtClassCol = Integer.parseInt((String) config.get("weightClass")) - 1; int actWtCol = Integer.parseInt((String) config.get("actualWeight")) - 1; int levelCol = Integer.parseInt((String) config.get("level")) - 1; int idCol = Integer.parseInt((String) config.get("serialNumber")) - 1; InputStream inp = new FileInputStream(file); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(sheetNumber); logger.info("Excel File Input configuration :" + "\n sheet=" + sheetNumber + "\n startRow=" + startRow + "\n endRow=" + endRow + "\n first=" + fNameCol + "\n last=" + lNameCol + "\n team=" + tNameCol + "\n geo=" + gNameCol + "\n class=" + classCol + "\n div=" + divCol + "\n" + "\n wtClass=" + wtClassCol + "\n actWt=" + actWtCol + "\n level=" + levelCol + "\n id=" + idCol); int i = startRow; while (i < endRow) { Wrestler w = new Wrestler(); Row row = sheet.getRow(i); if (row == null) { logger.warn("Row is null : " + i); i++; continue; } if (fNameCol >= 0) { String fName = row.getCell(fNameCol).getRichStringCellValue().getString(); // Should always have a first name, so this is a // reasonable check for the end of data. if (fName.isEmpty()) break; // found end of data w.setFirstName(fName.trim()); } if (lNameCol >= 0) { String lName = row.getCell(lNameCol).getRichStringCellValue().getString(); // Should always have a last name, so this is a // reasonable check for the end of data. if (lName.isEmpty()) break; // found end of data w.setLastName(lName.trim()); } if (divCol >= 0) { String div; if (row.getCell(divCol).getCellType() == Cell.CELL_TYPE_STRING) { div = row.getCell(divCol).getRichStringCellValue().getString(); } else { div = Long.valueOf(Double.valueOf(row.getCell(divCol).getNumericCellValue()).longValue()) .toString(); } w.setAgeDivision(div.trim()); } if (wtClassCol >= 0) { String wtClass; if (row.getCell(wtClassCol).getCellType() == Cell.CELL_TYPE_STRING) { wtClass = row.getCell(wtClassCol).getRichStringCellValue().getString(); } else { wtClass = Long .valueOf(Double.valueOf(row.getCell(wtClassCol).getNumericCellValue()).longValue()) .toString(); } w.setWeightClass(wtClass.trim()); } if (actWtCol >= 0) { String actWt; if (row.getCell(actWtCol).getCellType() == Cell.CELL_TYPE_STRING) { actWt = row.getCell(actWtCol).getRichStringCellValue().getString(); } else { actWt = Long .valueOf(Double.valueOf(row.getCell(actWtCol).getNumericCellValue()).longValue()) .toString(); } w.setActualWeight(actWt.trim()); } if (classCol >= 0) { String classification; if (row.getCell(classCol).getCellType() == Cell.CELL_TYPE_STRING) { classification = row.getCell(classCol).getRichStringCellValue().getString(); } else { classification = Long .valueOf(Double.valueOf(row.getCell(classCol).getNumericCellValue()).longValue()) .toString(); } w.setClassification(classification.trim()); } if (tNameCol >= 0) { String tName; if (row.getCell(tNameCol).getCellType() == Cell.CELL_TYPE_STRING) { tName = row.getCell(tNameCol).getRichStringCellValue().getString(); } else { tName = Long .valueOf(Double.valueOf(row.getCell(tNameCol).getNumericCellValue()).longValue()) .toString(); } w.setTeamName(tName.trim()); } if (gNameCol >= 0) { String gName; if (row.getCell(gNameCol).getCellType() == Cell.CELL_TYPE_STRING) { gName = row.getCell(gNameCol).getRichStringCellValue().getString(); } else { gName = Long .valueOf(Double.valueOf(row.getCell(gNameCol).getNumericCellValue()).longValue()) .toString(); } w.setGeo(gName.trim()); } if (idCol >= 0) { String id; if (row.getCell(idCol).getCellType() == Cell.CELL_TYPE_STRING) { id = row.getCell(idCol).getRichStringCellValue().getString(); } else { id = Long.valueOf(Double.valueOf(row.getCell(idCol).getNumericCellValue()).longValue()) .toString(); } w.setSerialNumber(id.trim()); } if (levelCol >= 0) { String level; if (row.getCell(levelCol).getCellType() == Cell.CELL_TYPE_STRING) { level = row.getCell(levelCol).getRichStringCellValue().getString(); } else { level = Long .valueOf(Double.valueOf(row.getCell(levelCol).getNumericCellValue()).longValue()) .toString(); } w.setLevel(level.trim()); } recordsProcessed++; if (dao.addWrestler(w)) { recordsAccepted++; logger.debug("Added wrestler : " + w); } else { recordsRejected++; rejects.add(String.format("%s %s", w.getFirstName(), w.getLastName())); logger.warn("Duplicate: " + w.getFirstName() + " " + w.getLastName()); } i++; } } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException ife) { JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame(); JOptionPane.showMessageDialog(mainFrame, "Error while handling the spreadsheet file.\n\n" + "This is not a file in an Excel file.", "Spreadsheet file error", JOptionPane.ERROR_MESSAGE); logger.error(ife.getLocalizedMessage() + "\n" + Arrays.toString(ife.getStackTrace())); } catch (Exception e) { JFrame mainFrame = BoutTimeApp.getApplication().getMainFrame(); JOptionPane.showMessageDialog(mainFrame, "Error while handling the spreadsheet file.\n\n" + e, "Spreadsheet file error", JOptionPane.ERROR_MESSAGE); logger.error(e.getLocalizedMessage() + "\n" + Arrays.toString(e.getStackTrace())); } return (new FileInputResult(recordsProcessed, recordsAccepted, recordsRejected, rejects)); }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException { XPath xPath = XPathFactory.newInstance().newXPath(); NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET); NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET); Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE); Sheet sheet = workbook.getSheetAt(0); for (int i = 0; i < cellValueList.getLength(); i++) { Node cellValue = cellValueList.item(i); String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent(); String type = cellValue.getAttributes().getNamedItem("type").getTextContent(); String value = cellValue.getTextContent(); CellReference cellRef = new CellReference(cellName); Row row = sheet.getRow(cellRef.getRow()); Cell cell = row.getCell(cellRef.getCol()); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellFormula(value);/*from w w w. j a v a2s .c o m*/ } else { cell.setCellValue(value); } } if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) { CellReference startCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("startRef").getTextContent()); CellReference endCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("endRef").getTextContent()); int startRowIndex = startCellRef.getRow(); int startColIndex = startCellRef.getCol(); int endColIndex = endCellRef.getCol(); CellStyle[] cellStyles = new CellStyle[endColIndex + 1]; Row firstRow = sheet.getRow(startRowIndex); for (int i = startColIndex; i <= endColIndex; i++) { cellStyles[i] = firstRow.getCell(i).getCellStyle(); } for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) { Row templeteRow = sheet.getRow(i); if (templeteRow != null) { sheet.removeRow(templeteRow); } } int rowNodeIndex = 0; for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) { Row row = sheet.createRow(i); int cellNodeIndex = 0; Node rowNode = rowNodeList.item(rowNodeIndex); NodeList rowValueNodeList = rowNode.getChildNodes(); ArrayList<Node> nodes = new ArrayList<Node>(); for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) { Node currentNode = rowValueNodeList.item(idx); if (currentNode.getNodeType() == Node.ELEMENT_NODE) { nodes.add(currentNode); } } for (int j = startColIndex; j <= endColIndex; j++) { Cell cell = row.createCell(j); Node cellNode = nodes.get(cellNodeIndex); String type = cellNode.getAttributes().getNamedItem("type").getTextContent(); String value = cellNode.getTextContent(); CellStyle cellStyle = cellStyles[j]; cell.setCellStyle(cellStyle); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula(value); } else if ("string".equals(type)) { if (value != null && value.length() > 0) { cell.setCellValue(value); } else { cell.setCellValue(""); } } else { cell.setCellValue(""); } cellNodeIndex++; } rowNodeIndex++; } } return workbook; }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook;//from w w w . j a va2 s.com if (!outputFile.exists()) { outputFile.createNewFile(); outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }
From source file:BUS.FileManager.java
private int timSBD(String MSSV, Sheet sheet) { int row = 10; String valueAtRow;//from w w w .j a v a2 s . c om do { valueAtRow = sheet.getRow(row).getCell(1).getStringCellValue(); if (valueAtRow.equals(MSSV)) return (row - 9); row++; } while (!valueAtRow.isEmpty()); return -1; }
From source file:BUS.FileManager.java
private void AddThisSheet(int SBD, Sheet sheet) throws IOException, FileNotFoundException, InvalidFormatException { LichThi lt = new LichThi(); lt.setMaMH(sheet.getRow(5).getCell(4).getStringCellValue()); lt.setTenMH(sheet.getRow(5).getCell(2).getStringCellValue()); lt.setSBD(Integer.toString(SBD)); lt.setPhong(Formatter.getInstance().FormatRoom(sheet.getRow(5).getCell(5).getStringCellValue())); lt.setNgay(Formatter.getInstance().FormatDate(sheet.getRow(4).getCell(5).getStringCellValue())); lt.setCa(this.timCaThi(Formatter.getInstance().ClassKeyToSubjectKey(lt.getMaMH()))); //Data.add(lt.toVector()); LichThiManager.getInstance().getDsLichThi().add(lt); }
From source file:BUS.FileManager.java
private String timCaThi(String maMH) throws IOException, FileNotFoundException, InvalidFormatException { Sheet sheet; if (fileLichThi.exists()) { if (fileLichThi.getName().endsWith(".xls")) sheet = (HSSFSheet) this.readXLSFile(fileLichThi); else//from w ww . j a v a 2 s . c om sheet = (XSSFSheet) this.readXLSXFile(fileLichThi); int row = 8; String valAtRow; do { valAtRow = sheet.getRow(row).getCell(1).getStringCellValue(); if (valAtRow.equals(maMH)) { return Double.toString(sheet.getRow(row).getCell(9).getNumericCellValue()); } row++; } while (!valAtRow.isEmpty()); } return ""; }