List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:include.excel_import.XlsInfo.java
License:Open Source License
public String getColumnType(String columnName, String sheetName) { if (columnTypes.containsKey(sheetName + "-" + columnName)) { return (String) columnTypes.get(sheetName + "-" + columnName); }//from ww w . ja v a 2 s.c om HSSFSheet sheet = getSheetForSheetName(sheetName); int index = getColumnsName(sheetName).indexOf(columnName); getColumnType_types.clear(); for (int i = 1; i < getRowCount(sheetName); i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell((short) index); //debug!! if (index == -1) { System.err.println("getColumnType: index==-1"); } // end of if () if (cell == null) { System.err.println("getColumnType:cell==null"); } // end of if () String celltype = getCellDataType(cell); if (!getColumnType_types.containsKey(celltype)) { //? getColumnType_types.put(celltype, new Integer(1)); } else { getColumnType_types.put(celltype, new Integer(((Integer) getColumnType_types.get(celltype)).intValue() + 1)); } // end of else row = null; cell = null; celltype = null; } // end of for () Set set = getColumnType_types.keySet(); Iterator it = set.iterator(); Integer max = new Integer(0); String realtype = "BLANK"; int flag = 0; while (it.hasNext()) { String key = (String) it.next(); if (flag == 0) { max = (Integer) getColumnType_types.get(key);//max? realtype = key; flag++; } else if (max.compareTo((Integer) getColumnType_types.get(key)) < 0) { max = (Integer) getColumnType_types.get(key); realtype = key; } //key=null; } // end of while () columnTypes.put(sheetName + "-" + columnName, realtype); sheet = null; set = null; it = null; max = null; //System.gc(); return realtype; }
From source file:include.excel_import.XlsValidator.java
License:Open Source License
/** * ???? /* w w w .java 2s.c o m*/ * @return true: */ private boolean validateTitle(HSSFSheet sheet, String sheetName) { HSSFRow row = sheet.getRow((short) 0); if (row == null) { // message.append(",SHEET"); return false; } Iterator cells = row.cellIterator(); int size = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); size++; } for (int j = 0; j < size - 1; j++) { HSSFCell cell = row.getCell((short) j); if (cell == null) { return false; } else { if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) { message.append(""/*sheetName*/).append(""); message.append(j + 1).append("?<br>"); return false; } if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { message.append(""/*sheetName*/).append("").append(j + 1).append("<br>"); return false; } } } return true; }
From source file:include.excel_import.XlsValidator.java
License:Open Source License
public boolean validateContent() { 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); for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) { HSSFCell cell = row.getCell((short) n); if (cell == null) { message.append(""/*sheetName*/).append("("); message.append(n + 1).append(":"); message.append(j + 1).append(")("); message.append(""/*sheetName*/).append(""); message.append(rowCount).append("??)<br>"); return false; }/*from w w w. j a v a2 s . c o m*/ } } } return true; }
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. ja v a2 s . com 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;/*from w w w. j a v a 2 s . co m*/ 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.toegepaste.www.service.ProjectServiceImpl.java
public IngelezenFile getExcelScores(InputStream fs) { ArrayList<String> lijstNr = new ArrayList<String>(); ArrayList<String> lijstNaam = new ArrayList<String>(); ArrayList<String> lijstScore = new ArrayList<String>(); IngelezenFile file = null;/*w w w.ja v a2 s. c o m*/ //String fileContent; //Part filePart; try { //FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Jeroen\\Desktop\\resultaten.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet worksheet = workbook.getSheet("Blad1"); HSSFRow row1 = worksheet.getRow(0); HSSFCell cel = worksheet.getRow(3).getCell((short) 1); cel.setCellType(Cell.CELL_TYPE_STRING); String klas = worksheet.getRow(0).getCell((short) 1).getStringCellValue(); String vak = worksheet.getRow(1).getCell((short) 1).getStringCellValue(); String test = worksheet.getRow(2).getCell((short) 1).getStringCellValue(); String totaalPunt = cel.getStringCellValue(); int i = 6; // deze lus werkt niet als je regels weg doet uit een excel file, dus minder dan 3 studenten kan niet, meer wel! while (worksheet.getRow(i) != null && worksheet.getRow(i).getCell((short) 1) != null) { HSSFCell scorecel = worksheet.getRow(i).getCell((short) 2); scorecel.setCellType(Cell.CELL_TYPE_STRING); HSSFCell naamcel = worksheet.getRow(i).getCell((short) 1); naamcel.setCellType(Cell.CELL_TYPE_STRING); HSSFCell nrcel = worksheet.getRow(i).getCell((short) 0); nrcel.setCellType(Cell.CELL_TYPE_STRING); lijstNr.add(worksheet.getRow(i).getCell((short) 0).getStringCellValue()); lijstNaam.add(worksheet.getRow(i).getCell((short) 1).getStringCellValue()); lijstScore.add(worksheet.getRow(i).getCell((short) 2).getStringCellValue()); i++; } file = new IngelezenFile(klas, vak, test, Integer.parseInt(totaalPunt), lijstNr, lijstNaam, lijstScore); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //return lijst; // upload scores insertTest(file); return file; }
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 ww.j a va 2 s. 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/*from ww w. j a v a 2 s . c om*/ * * @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// www .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//from ww w . j a v a 2s . com * * @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(); }