List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:include.excel_import.XlsValidator.java
License:Open Source License
public boolean validateValue() { for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); String sheetName = wb.getSheetName(i); int rowCount = xlsInfo.getRowCount(sheetName); for (int j = 1; j < rowCount; j++) { HSSFRow row = sheet.getRow(j); HSSFRow row1 = sheet.getRow(0);//??itemname for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) { HSSFCell cell1 = row1.getCell((short) n); String itemname = cell1.getStringCellValue(); HSSFCell cell = row.getCell((short) n); if (!validateType(cell, itemname, sheetName)) { message.append("").append(n + 1); message.append(" : ").append(j + 1); message.append("????<br>"); return false; }//from w w w .j a v a 2s. co m cell1 = null; itemname = null; cell = null; } row = null; row1 = null; } sheet = null; sheetName = null; } return true; }
From source file:info.jtrac.domain.ExcelFile.java
License:Apache License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;//w ww .j a va 2s.c om HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (Exception e) { throw new RuntimeException(e); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; int row = 0; int col = 0; columns = new ArrayList<Column>(); //========================== HEADER ==================================== // column headings are important, this routine assumes that the first // row is a header row and that reaching an empty cell means end of data r = sheet.getRow(row); while (true) { c = r.getCell((short) col); if (c == null) { break; } String value = c.getStringCellValue(); if (value == null || value.trim().length() == 0) { break; } Column column = new Column(value.trim()); columns.add(column); col++; } //============================ DATA ==================================== rows = new ArrayList<List<Cell>>(); while (true) { row++; r = sheet.getRow(row); if (r == null) { break; } List<Cell> rowData = new ArrayList<>(columns.size()); boolean isEmptyRow = true; for (col = 0; col < columns.size(); col++) { c = r.getCell((short) col); Object value = null; switch (c.getCellType()) { case (HSSFCell.CELL_TYPE_STRING): value = c.getStringCellValue(); break; case (HSSFCell.CELL_TYPE_NUMERIC): // value = c.getDateCellValue(); value = c.getNumericCellValue(); break; case (HSSFCell.CELL_TYPE_BLANK): break; default: // do nothing } if (value != null && value.toString().length() > 0) { isEmptyRow = false; rowData.add(new Cell(value)); } else { rowData.add(new Cell(null)); } } if (isEmptyRow) { break; } rows.add(rowData); } }
From source file:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java
License:Mozilla Public License
public Map<String, String> readRecord() throws ReadException { HSSFRow row = currentSheet.getRow(nextRowNumber); if (row != null) { //We have data. Map<String, String> result = new HashMap<String, String>(schema.length); //Do not use the iterator (row.cellIterator()): this will cause to skip empty cells! //Use the schema to loop over the cells for (short i = 0; i < schema.length; i++) { String fieldName = schema[i]; HSSFCell cel = row.getCell(i); if (cel != null) { String value = ""; if (cel.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // TODO: make this configurable: conversion from double to string value = cel.getNumericCellValue() + ""; } else { value = cel.getStringCellValue(); }/* w w w . j a v a 2s . co m*/ result.put(fieldName, value); } else { result.put(fieldName, ""); } } nextRowNumber++; return result; } else { return null; } }
From source file:is.idega.idegaweb.egov.fsk.business.FSKBusinessBean.java
License:Open Source License
@Override public Map importExcelFile(UploadFile file, Object coursePK, int column) { Map map = new HashMap(); try {/*from w w w . j a v a 2s .c o m*/ Course course = getCourse(coursePK); Group group = course.getGroup(); FileInputStream input = new FileInputStream(file.getRealPath()); HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); NumberFormat format = NumberFormat.getNumberInstance(); format.setGroupingUsed(false); format.setMinimumIntegerDigits(10); Collection imported = new ArrayList(); Collection alreadyImported = new ArrayList(); Collection outsideCommune = new ArrayList(); Collection outsideAgeRange = new ArrayList(); Collection invalidPersonalID = new ArrayList(); Collection noUserFound = new ArrayList(); for (int a = sheet.getFirstRowNum(); a <= sheet.getLastRowNum(); a++) { HSSFRow row = sheet.getRow(a); HSSFCell cell = row.getCell((short) (column - 1)); if (cell == null) { continue; } String personalID = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { personalID = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { personalID = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); } else { personalID = cell.getStringCellValue(); } try { personalID = format.format(format.parse(personalID.replaceAll("-", ""))); } catch (ParseException e1) { e1.printStackTrace(); continue; } if (SocialSecurityNumber.isValidSocialSecurityNumber(personalID, getDefaultLocale())) { try { User user = getUserBusiness().getUser(personalID); if (!group.hasRelationTo(((Integer) user.getPrimaryKey()).intValue())) { IWTimestamp dateOfBirth = new IWTimestamp(user.getDateOfBirth()); dateOfBirth.setMonth(1); dateOfBirth.setDay(1); Age age = new Age(dateOfBirth.getDate()); if (age.getYears(course.getStartDate()) < 6 || age.getYears(course.getStartDate()) > 18) { outsideAgeRange.add(user); continue; } if (!getUserBusiness().isCitizenOfDefaultCommune(user)) { outsideCommune.add(user); continue; } group.addGroup(user); imported.add(user); } else { alreadyImported.add(user); } } catch (FinderException e) { noUserFound.add(personalID); } } else { invalidPersonalID.add(personalID); } } map.put(FSKConstants.REGISTRATION_CODE_REGISTERED, imported); map.put(FSKConstants.REGISTRATION_CODE_ALREADY_REGISTERED, alreadyImported); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_COMMUNE, outsideCommune); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_AGE_RANGE, outsideAgeRange); map.put(FSKConstants.REGISTRATION_CODE_INVALID_PERSONAL_ID, invalidPersonalID); map.put(FSKConstants.REGISTRATION_CODE_NO_USER_FOUND, noUserFound); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * export// w w w.j a v a2 s. co m * * @param fullFileName * @param populatedTemplateFileName * @param shippingInformation * @throws XlsUploadException * @throws Exception */ public void export(String fullFileName, String populatedTemplateFileName, ShippingInformation shippingInformation) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(populatedTemplateFileName)); HSSFWorkbook workbook = null; try { // Now extract the workbook workbook = new HSSFWorkbook(fs); } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } HSSFSheet sheet = null; int nbSheetsInFile = workbook.getNumberOfSheets(); int nbSheetsInInfo = DBTools.GetNumberOfContainers(shippingInformation) - 1; int nbSheetsToDelete = nbSheetsInFile - nbSheetsInInfo; int i; // Create Additional Sheets if needed if (nbSheetsToDelete > 0) { for (i = nbSheetsInFile - 1; i >= nbSheetsInFile - nbSheetsToDelete; i--) { // workbook.removeSheetAt(i); } } // Populate Sheet int currentSheetNumber = -1; HSSFRow row = null; HSSFCell cell = null; try { DiffractionPlan3Service difPlanService = (DiffractionPlan3Service) ejb3ServiceLocator .getLocalService(DiffractionPlan3Service.class); for (int d = 0; d < shippingInformation.getListDewars().size(); d++) { // Dewar DewarInformation dewar = shippingInformation.getListDewars().get(d); // Container for (int c = 0; c < dewar.getListContainers().size(); c++) { currentSheetNumber++; sheet = workbook.getSheetAt(currentSheetNumber); ContainerInformation container = dewar.getListContainers().get(c); // Populate Courrier row = sheet.getRow(courrierNameRow); cell = row.getCell(courrierNameCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue( new HSSFRichTextString(shippingInformation.getShipping().getDeliveryAgentAgentName())); row = sheet.getRow(trackingNumberRow); cell = row.getCell(trackingNumberCol); cell.setCellValue( new HSSFRichTextString(shippingInformation.getShipping().getDeliveryAgentAgentCode())); row = sheet.getRow(shippingDateRow); cell = row.getCell(shippingDateCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(shippingInformation.getShipping().getDeliveryAgentShippingDate()); // Populate Puck row = sheet.getRow(puckRow); cell = row.getCell(puckCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(container.getContainer().getCode())); // Populate Dewar row = sheet.getRow(dewarRow); cell = row.getCell(dewarCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dewar.getDewar().getCode())); // Sample for (int s = 0; s < container.getListSamples().size(); s++) { SampleInformation sample = container.getListSamples().get(s); Crystal3VO crystal = sample.getCrystal(); Protein3VO protein = sample.getProtein(); DiffractionPlan3VO diffractionPlan = null; // DiffractionPlanLightValue diffractionPlan = sample.getSample().getDiffractionPlan(); if (sample.getSample().getDiffractionPlanVOId() != null) diffractionPlan = difPlanService.findByPk(sample.getSample().getDiffractionPlanVOId(), false, false); else if (diffractionPlan == null) diffractionPlan = difPlanService.findByPk(crystal.getDiffractionPlanVOId(), false, false); int currentRow = dataRow + s; // Try to extract Sample Location Integer locationIncontainer = null; try { String _locationInContainer = sample.getSample().getLocation(); locationIncontainer = Integer.parseInt(_locationInContainer); } catch (Exception e) { } if (locationIncontainer != null && locationIncontainer <= Constants.BASKET_SAMPLE_CAPACITY) { currentRow = dataRow + locationIncontainer - 1; } // Protein acronym - SpaceGroup row = sheet.getRow(currentRow); cell = row.getCell(proteinAcronymCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue( new HSSFRichTextString(protein.getAcronym() + " - " + crystal.getSpaceGroup())); // Sample Name row = sheet.getRow(currentRow); cell = row.getCell(sampleNameCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getName())); // Pin Barcode row = sheet.getRow(currentRow); cell = row.getCell(pinBarCodeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getCode())); // Pre-observed resolution row = sheet.getRow(currentRow); cell = row.getCell(preObsResolutionCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getObservedResolution() != null) cell.setCellValue(diffractionPlan.getObservedResolution()); else cell.setCellValue(new HSSFRichTextString("")); // Needed resolution row = sheet.getRow(currentRow); cell = row.getCell(neededResolutionCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getMinimalResolution() != null) cell.setCellValue(diffractionPlan.getMinimalResolution()); else cell.setCellValue(new HSSFRichTextString("")); // Preferred beam diameter row = sheet.getRow(currentRow); cell = row.getCell(preferredBeamCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null) { if (diffractionPlan.getPreferredBeamDiameter() != null) cell.setCellValue(diffractionPlan.getPreferredBeamDiameter()); } else cell.setCellValue(new HSSFRichTextString("")); // Experiment Type row = sheet.getRow(currentRow); cell = row.getCell(experimentTypeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null && diffractionPlan.getExperimentKind() != null) cell.setCellValue(new HSSFRichTextString(diffractionPlan.getExperimentKind())); else cell.setCellValue(new HSSFRichTextString(Constants.LIST_EXPERIMENT_KIND[0])); // // // Anomalous Scatterer // row = sheet.getRow(currentRow); // cell = row.getCell(anomalousScattererCol); // cell.setCellType(HSSFCell.CELL_TYPE_STRING); // if (crystal.getAnomalousScatterers().length>0) { // AnomalousScattererLightValue an = crystal.getAnomalousScatterers()[0]; // cell.setCellValue(new HSSFRichTextString(an.getElement())); // } // else // cell.setCellValue(new HSSFRichTextString("")); // Unit Cell a row = sheet.getRow(currentRow); cell = row.getCell(unitCellACol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellA() != null) ? crystal.getCellA() : 0); // Unit Cell b row = sheet.getRow(currentRow); cell = row.getCell(unitCellBCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellB() != null) ? crystal.getCellB() : 0); // Unit Cell c row = sheet.getRow(currentRow); cell = row.getCell(unitCellCCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellC() != null) ? crystal.getCellC() : 0); // Unit Cell alpha row = sheet.getRow(currentRow); cell = row.getCell(unitCellAlphaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellAlpha() != null) ? crystal.getCellAlpha() : 0); // Unit Cell beta row = sheet.getRow(currentRow); cell = row.getCell(unitCellBetaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellBeta() != null) ? crystal.getCellBeta() : 0); // Unit Cell gamma row = sheet.getRow(currentRow); cell = row.getCell(unitCellGammaCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((crystal.getCellGamma() != null) ? crystal.getCellGamma() : 0); // LoopType // row = sheet.getRow(currentRow); // cell = row.getCell(loopTypeCol); // cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellValue(new HSSFRichTextString(sample.getSample().getLoopType())); // HolderLength // row = sheet.getRow(currentRow); // cell = row.getCell(holderLengthCol); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue(sample.getSample().getHolderLength()); // SMILES row = sheet.getRow(currentRow); cell = row.getCell(smilesCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getSmiles())); // min osc width row = sheet.getRow(currentRow); cell = row.getCell(minOscWidthCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (diffractionPlan != null) { if (diffractionPlan.getMinOscWidth() != null) cell.setCellValue(diffractionPlan.getMinOscWidth()); } else cell.setCellValue(new HSSFRichTextString("")); // Comments row = sheet.getRow(currentRow); cell = row.getCell(commentsCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(sample.getSample().getComments())); } } } } catch (Exception e) { e.printStackTrace(); } // Save Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.close(); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * PopulateExistingShipment//from ww w. j av a 2 s .c o m * * @param templateFileName * @param populatedTemplateFileName * @param shippingId * @throws XlsUploadException * @throws Exception */ public void populateExistingShipment(String templateFileName, String populatedTemplateFileName, int shippingId) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileName)); HSSFWorkbook workbook = null; ShippingInformation shippingInformation = DBTools.getShippingInformation(shippingId); try { // Now extract the workbook workbook = new HSSFWorkbook(fs); int nbDewars = shippingInformation.getListDewars().size(); int nbSheetsForDewar = 6; int nbSheetstoCopy = (nbDewars == 0) ? 0 : (nbDewars * nbSheetsForDewar) - 1; // Copy right number of sheets = 1 per dewar for (int d = 1; d <= nbSheetstoCopy; d++) { workbook.cloneSheet(0); } // Populate Sheet for (int dew = 0; dew < nbDewars; dew++) { int sheetStart = (dew == 0) ? 0 : (dew * nbSheetsForDewar); int sheetStop = ((dew + 1) * nbSheetsForDewar) - 1; int puckNumber = 1; for (int s = sheetStart; s <= sheetStop; s++) { String dewarCode = shippingInformation.getListDewars().get(dew).dewar.getCode(); if (dewarCode == null || dewarCode.trim().equalsIgnoreCase("")) dewarCode = Integer.toString(dew); String puckCode = "Puck" + puckNumber; // Populate workbook.setSheetName(s, dewarCode + "_" + puckNumber); HSSFSheet sheet = workbook.getSheetAt(s); sheet.setProtect(false); // Dewar Code HSSFRow row = sheet.getRow(dewarRow); if (row == null) row = sheet.createRow(dewarRow); HSSFCell cell = row.getCell(dewarCol); if (cell == null) cell = row.createCell(dewarCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dewarCode)); // Puck Code row = sheet.getRow(puckRow); if (row == null) row = sheet.createRow(puckRow); cell = row.getCell(puckCol); if (cell == null) cell = row.createCell(puckCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(puckCode)); // LabelCode row = sheet.getRow(idLabelRow); if (row == null) row = sheet.createRow(idLabelRow); cell = row.getCell(idLabelCol); if (cell == null) cell = row.createCell(idLabelCol); cell.setCellValue(new HSSFRichTextString(ProposalAndShippingLabel)); // ProposalId Integer proposalId = shippingInformation.getShipping().getProposalVOId(); row = sheet.getRow(value1IdRow); if (row == null) row = sheet.createRow(value1IdRow); cell = row.getCell(value1IdCol); if (cell == null) cell = row.createCell(value1IdCol); cell.setCellValue(proposalId); // ShippingId row = sheet.getRow(value2IdRow); if (row == null) row = sheet.createRow(value2IdRow); cell = row.getCell(value2IdCol); if (cell == null) cell = row.createCell(value2IdCol); cell.setCellValue(shippingId); // Courrier Name String courrierName = shippingInformation.getShipping().getDeliveryAgentAgentName(); row = sheet.getRow(courrierNameRow); if (row == null) row = sheet.createRow(courrierNameRow); cell = row.getCell(courrierNameCol); if (cell == null) cell = row.createCell(courrierNameCol); cell.setCellValue(new HSSFRichTextString(courrierName)); // Tracking Number String trackingNumber = shippingInformation.getShipping().getDeliveryAgentAgentCode(); row = sheet.getRow(trackingNumberRow); if (row == null) row = sheet.createRow(trackingNumberRow); cell = row.getCell(trackingNumberCol); if (cell == null) cell = row.createCell(trackingNumberCol); cell.setCellValue(new HSSFRichTextString(trackingNumber)); // Shipping Date Date _shippingDate = shippingInformation.getShipping().getDeliveryAgentShippingDate(); String shippingDate = ""; if (_shippingDate != null) shippingDate = _shippingDate.getDay() + "/" + _shippingDate.getMonth() + "/" + (_shippingDate.getYear() + 1900); row = sheet.getRow(shippingDateRow); if (row == null) row = sheet.createRow(shippingDateRow); cell = row.getCell(shippingDateCol); if (cell == null) cell = row.createCell(shippingDateCol); cell.setCellValue(new HSSFRichTextString(shippingDate)); sheet.setProtect(true); puckNumber++; } } } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } // ave Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.flush(); fout.close(); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * populate// w w w .j av a 2 s . c om * * @param templateFileName * @param populatedTemplateFileName * @param listProteins * @param dmCodesinSC * @throws XlsUploadException * @throws Exception */ public void populate(String templateFileName, String populatedTemplateFileName, List listProteins, String[][] dmCodesinSC) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileName)); HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(fs); // Now extract the workbook } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } Protein3Service proteinService = (Protein3Service) Ejb3ServiceLocator.getInstance() .getLocalService(Protein3Service.class); HSSFSheet sheet = null; for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { sheet = workbook.getSheetAt(sheetNum); Iterator it = listProteins.iterator(); int currentRow = this.proteinAcronymRow; List<String> listProtein = new ArrayList<String>(); while (it.hasNext()) { Protein3VO protein = (Protein3VO) it.next(); // protein = proteinService.loadEager(protein); Crystal3VO[] crystals = protein.getCrystals(); // Retrieve Xtals for SpaceGroup for (int c = 0; c < crystals.length; c++) { String acronym = protein.getAcronym(); Crystal3VO xtal = crystals[c]; // Replace database empty values by 'Undefined' if (xtal.getSpaceGroup() != null && !xtal.getSpaceGroup().equals("")) { acronym += Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR + xtal.getSpaceGroup(); } else { acronym += Constants.PROTEIN_ACRONYM_SPACE_GROUP_SEPARATOR + "Undefined"; } // Add to list (but don't duplicate) if (!listProtein.contains(acronym)) { listProtein.add(acronym); // Populate Acronym - SpaceGroup try { HSSFRow row = sheet.getRow(currentRow); if (row == null) row = sheet.createRow(currentRow); HSSFCell cell = row.getCell(proteinAcronymCol); if (cell == null) cell = row.createCell(proteinAcronymCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(acronym); currentRow++; } catch (Exception e) { e.printStackTrace(); } } } } } // Populate DM Codes if (dmCodesinSC != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { sheet = workbook.getSheetAt(sheetNum); int basketLocation = sheetNum + 1; for (int sampleLocation = 0; sampleLocation < Constants.BASKET_SAMPLE_CAPACITY; sampleLocation++) { int rowNumber = dataRow + sampleLocation; String dmCode = dmCodesinSC[sheetNum + 1][sampleLocation + 1]; HSSFRow row = sheet.getRow(rowNumber); if (row == null) row = sheet.createRow(rowNumber); HSSFCell cell = row.getCell(pinBarCodeCol); if (cell == null) cell = row.createCell(pinBarCodeCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dmCode)); } } } // Save Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.flush(); fout.close(); }
From source file:it.cnr.ibimet.bikeclimate.backingbeans.FotovoltaicDataBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w. j a v a 2 s . c om HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //Create header for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); header.getCell(i).setCellValue(columns.get(i).getHeader()); } //create data table for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { header = sheet.getRow(i); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { header.getCell(j).setCellValue(dati.get(i).get(columns.get(j).getProperty())); } } }
From source file:it.cnr.ibimet.bikeclimate.backingbeans.FotovoltaicDataBean.java
public void postProcessXLSStat(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from ww w .j a v a 2s . c o m*/ HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //Create header for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); header.getCell(i).setCellValue(columnsStat.get(i).getHeader()); } //create data table for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { header = sheet.getRow(i); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { header.getCell(j).setCellValue(datiStat.get(i).get(columnsStat.get(j).getProperty())); } } }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static List<String[]> getCells(HSSFSheet sheet, Short[] columnAbsolutePositions, boolean looseConstraint) { List<String[]> cells = new ArrayList<String[]>(); for (int rowAbsolutePosition = sheet.getFirstRowNum() + 1; rowAbsolutePosition <= sheet .getLastRowNum(); rowAbsolutePosition++) { HSSFRow row = sheet.getRow(rowAbsolutePosition); if (row == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem"); }/*from w ww. ja v a 2s . c om*/ continue; } String cellStringValues[] = new String[5]; for (int columnRelativePosition = 0; columnRelativePosition < cellStringValues.length; columnRelativePosition++) { HSSFCell cell = row.getCell(columnAbsolutePositions[columnRelativePosition]); String cellStringValue = cell != null ? cell.getStringCellValue() : null; if (cellStringValue != null && cellStringValue.trim().equals("")) { cellStringValue = null; } cellStringValues[columnRelativePosition] = cellStringValue; } if (cellStringValues[0] == null && cellStringValues[1] == null && cellStringValues[2] == null && cellStringValues[3] == null && cellStringValues[4] == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem, it's {null, null, null, null, null}"); } } else { cells.add(cellStringValues); } } dumpCells(cells); validateCells(cells, looseConstraint); return cells; }