List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private int inferColumnType(HSSFSheet sheet, int startRow, int logicalCol) { //p("laborious inferColumnType for column=" + logicalCol + " starting from row=" + startRow); int ret = UNKNOWN; short col = (short) logicalCol; for (int i = startRow; i <= maxrows && i < sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(col); if (cell != null) { ret = this.getType(cell); if (ret != UNKNOWN) { //p("inferred type=" + DataType.toString(ret) + " from cell='" + getCellString(cell) + "'"); //p("inferred type " + DataType.toString(ret) + " from row/col " + i + "/" + col); break; }/* ww w . j a v a 2 s. co m*/ } } } return ret; }
From source file:com.beginner.core.utils.ObjectExcelRead.java
License:Apache License
/** * @param filepath //// ww w . j a v a 2s . c om * @param filename //?? * @param startrow //? * @param startcol //? * @param sheetnum //sheet * @return list */ @SuppressWarnings("deprecation") public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) { List<Object> varList = new ArrayList<Object>(); try { File target = new File(filepath, filename); FileInputStream fi = new FileInputStream(target); HSSFWorkbook wb = new HSSFWorkbook(fi); HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 0 int rowNum = sheet.getLastRowNum() + 1; //??? for (int i = startrow; i < rowNum; i++) { // PageData varpd = new PageData(); HSSFRow row = sheet.getRow(i); // int cellNum = row.getLastCellNum(); //???? for (int j = startcol; j < cellNum; j++) { // HSSFCell cell = row.getCell(Short.parseShort(j + "")); String cellValue = null; if (null != cell) { switch (cell.getCellType()) { // excel????? case 0: cellValue = String.valueOf((int) cell.getNumericCellValue()); break; case 1: cellValue = cell.getStringCellValue(); break; case 2: cellValue = cell.getNumericCellValue() + ""; // cellValue = String.valueOf(cell.getDateCellValue()); break; case 3: cellValue = ""; break; case 4: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case 5: cellValue = String.valueOf(cell.getErrorCellValue()); break; } } else { cellValue = ""; } varpd.put("var" + j, cellValue); } varList.add(varpd); } } catch (Exception e) { System.out.println(e); } return varList; }
From source file:com.beginner.core.utils.ObjectExcelView.java
License:Apache License
@SuppressWarnings("unchecked") @Override/* w w w.j av a2 s .c om*/ protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Date date = new Date(); String filename = DateUtil.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell, title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for (int i = 0; i < varCount; i++) { PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = getCell(sheet, i + 1, j); cell.setCellStyle(contentStyle); setText(cell, varstr); } } }
From source file:com.bharath.dao.SelectCourseDao.java
public String getStudentslisttoExcel(String curl) { ArrayList<String> arr = new ArrayList<String>(); ArrayList<String> arr1 = new ArrayList<String>(); ArrayList<String> arr2 = new ArrayList<String>(); ArrayList<String> arr3 = new ArrayList<String>(); try {/*from www . jav a 2s . co m*/ UserAgent userAgent = new UserAgent(); userAgent.openContent(curl); //userAgent.visit(curl); Table table = userAgent.doc.getTable( "<table class=\"datadisplaytable\" summary=\"This table displays a list of students registered for the course; summary information about each student is provided.\" width=\"100%\">"); System.out.println(table.toString()); Elements elements = table.getCol(0); for (Element element : elements) { arr.add(element.innerText()); //System.out.println(element.innerText()); } Elements elements1 = table.getCol(2); for (Element element : elements1) { arr1.add(element.innerText()); //System.out.println(element.innerText()); } Elements elements2 = table.getCol(3); for (Element element : elements2) { arr2.add(element.innerText()); //System.out.println(element.innerText()); } Elements elements3 = table.getCol(4); for (Element element : elements3) { arr3.add(element.innerText()); // System.out.println(element.innerText()); } HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet spreadSheet = workBook.createSheet("StudentDetails"); HSSFRow row; HSSFCell cell; //int colnum = 0; int rowcount = 0; //for (int i = 0; i < arr.size(); i++) { for (String str : arr) { row = spreadSheet.createRow((short) rowcount); // System.out.println(arr.get(i)); cell = row.createCell(0); cell.setCellValue(str.trim()); rowcount++; // colcount++; } rowcount = 0; for (String str : arr1) { row = spreadSheet.getRow((short) rowcount); // System.out.println(arr.get(i)); cell = row.createCell(1); cell.setCellValue(str.trim()); rowcount++; // colcount++; } rowcount = 0; for (String str : arr2) { row = spreadSheet.getRow((short) rowcount); // System.out.println(arr.get(i)); cell = row.createCell(2); cell.setCellValue(str.trim()); rowcount++; // colcount++; } rowcount = 0; for (String str : arr3) { row = spreadSheet.getRow((short) rowcount); // System.out.println(arr.get(i)); cell = row.createCell(3); cell.setCellValue(str.trim()); rowcount++; // colcount++; } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); workBook.write(outByteStream); outArray = outByteStream.toByteArray(); System.out.println(outArray.length); //fos = new FileOutputStream("student.xls"); System.out.println("Done"); //workBook.write(fos); } catch (Exception e) { System.out.println(e); } return "Done"; }
From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java
License:Apache License
public String getFileContentString(File f) { int i, j, j2, k; short k2;//from w w w . j a v a 2 s. com HSSFWorkbook wb = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; InputStream is = null; String cell_string = ""; try { is = new FileInputStream(f); POIFSFileSystem fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); for (i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); j2 = 0; for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { while ((row = sheet.getRow(j2)) == null) { j2++; } j2++; k2 = 0; for (k = 0; k < row.getPhysicalNumberOfCells(); k++) { while ((cell = row.getCell(k2)) == null) { k2++; } k2++; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: cell_string = cell_string + cell.getNumericCellValue() + "\t"; break; case HSSFCell.CELL_TYPE_STRING: cell_string = cell_string + cell.getStringCellValue() + "\t"; break; default: } } cell_string = cell_string + "\n"; } cell_string = cell_string + "\n\n\n"; } is.close(); } catch (Exception fe) { cell_string = null; if (is != null) { try { is.close(); } catch (Exception ise) { } } } return cell_string; }
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
/** * Reads the configuration parameters from the specified worksheet in the * Xcel spreadsheet. //w ww . ja v a2 s.com * * @param arglist - the arguments of the spreadsheet to read, such as file * name, sheet, and columns to read. * @param forRegistrar - a flag stating whether to use the hashKey as the key * in the properties table or the value of the IP property * @param forRemoval - whether this is being added or removed from the * properties table. * * @return - returns the Network Element Label this properties were saved under or * null. */ @SuppressWarnings("deprecation") private Hashtable<String, Properties> readConfigParameters(String[] arglist) { POIFSFileSystem fs = null; HSSFWorkbook wb = null; int xlsSheetIndex = 0; ArrayList<Object> hashKeyList = new ArrayList<Object>(); String hashKey = null; String propertyKey = null; String propertyValue = null; Hashtable<String, Properties> table = null; // verify execution syntax - make sure proper number of parameters are passed in if (arglist.length != 5) { logger.trace(PC2LogCategory.Settings, subCat, "Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); //System.out.print("Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); return table; } String xlsFileName = arglist[0]; String xlsSheetName = arglist[1]; String xlsHashKeyColumn = arglist[2]; String xlsPropertyKeyColumn = arglist[3]; String xlsPropertyValueColumn = arglist[4]; logger.trace(PC2LogCategory.Settings, subCat, "Excel File Name is: " + xlsFileName + "\nExcel Sheet Name is: " + xlsSheetName + "\nExcel Key Column is: " + xlsHashKeyColumn + "\nExcel Field Name Column is: " + xlsPropertyKeyColumn + "\nExcel Value Column is: " + xlsPropertyValueColumn); // use POI to read the excel file try { fs = new POIFSFileSystem(new FileInputStream(xlsFileName)); logger.trace(PC2LogCategory.Settings, subCat, "FS= " + fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to read the file named " + xlsFileName); } ; // read the workbook try { wb = new HSSFWorkbook(fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to create a workbook"); } ; try { xlsSheetIndex = wb.getSheetIndex(xlsSheetName); HSSFSheet sheet = wb.getSheetAt(xlsSheetIndex); HSSFRow row = null; HSSFCell cell = null; boolean formatKnown = false; table = new Hashtable<String, Properties>(); int initialRow = 0; if (sheet.getRow(0) == null) //|| sheet.getRow(0).getCell(Short.decode("0")) != null) initialRow = 1; int rows = sheet.getLastRowNum(); for (int r = initialRow; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { if (xlsHashKeyColumn != null) { cell = row.getCell(Short.decode(xlsHashKeyColumn)); // Support the original format of the settings files where the network element is in column 1 instead of zero if (!formatKnown && cell == null && xlsHashKeyColumn.equals("0")) { xlsHashKeyColumn = "1"; xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; cell = row.getCell(Short.decode(xlsHashKeyColumn)); } formatKnown = true; } else if (!formatKnown) { hashKey = SettingConstants.DUT; cell = row.getCell(Short.decode("0")); // Support the original format of the settings files where the network element is in column 1 instead of zero cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); if (cell == null || propertyKey.startsWith("Step 1")) { xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; } cell = null; formatKnown = true; } if (cell != null) { hashKey = getXlsCellStringValue(cell); } if ((hashKey != null) && (hashKey != "")) { if (!hashKeyList.contains(hashKey)) { hashKeyList.add(hashKey); table.put(hashKey, new Properties()); } } Properties p = null; if (hashKey != null) p = table.get(hashKey); if (p != null) { cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); cell = row.getCell(Short.decode(xlsPropertyValueColumn)); propertyValue = getXlsCellStringValue(cell); if (propertyKey.equals("MAC Address")) { p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); } // PCPCSII-125 // Create a colon verions of each MAC Address in the configuration files if (propertyKey.contains("MAC Address")) { String value = createMACAddressWithColons(propertyValue); p.setProperty(propertyKey + " Colon", value); } // Before putting the key/value pair into the property class, // (except for the LinkLocalAddress property), // see if it is an IP property and if so determine if the // value is an IPv6 address using the compressed form. if (propertyKey.contains(SettingConstants.IP) && propertyValue.contains(SettingConstants.IP_COMP_FORM)) { try { propertyValue = Conversion.ipv6LongForm(propertyValue); } catch (IllegalArgumentException iae) { logger.error(PC2LogCategory.Settings, subCat, hashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue + ". Detected an invalid IPv6 address."); } } if (propertyKey != null && propertyValue != null && propertyKey != "" && propertyValue != "") p.setProperty(propertyKey, propertyValue); cell = null; logger.trace(PC2LogCategory.Settings, subCat, hashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); } } } for (int q = 0; q < hashKeyList.size(); q++) { String currentHashKey = hashKeyList.get(q).toString(); Properties p = table.get(currentHashKey); addDeviceType(currentHashKey, p); // key = currentHashKey; if (currentHashKey.equals("DUT")) { String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); if (deviceType != null) { table.put(deviceType + "0", p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + deviceType + "0 to system settings."); } } else if (currentHashKey.startsWith("UE")) { String sim = p.getProperty(SettingConstants.SIMULATED); if (sim != null && (sim.equalsIgnoreCase("false") || sim.equalsIgnoreCase("no") || sim.equalsIgnoreCase("disable"))) { String pui = p.getProperty(SettingConstants.PUI); String pui2 = p.getProperty(SettingConstants.PUI2); if (pui != null) { table.put(pui, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui + " to system settings."); } if (pui2 != null && !pui2.equals(pui)) { table.put(pui2, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui2 + " to system settings."); } } } } // table = new Hashtable<String, Properties>(); // // for (int q = 0; q < hashKeyList.size(); q++) { // // String currentHashKey = hashKeyList.get(q).toString(); // logger.trace(PC2LogCategory.Settings, subCat, // "****** OK hashKey(q) = " + hashKeyList.get(q) + " ******"); // logger.trace(PC2LogCategory.Settings, subCat, // "Loop " + q); // Properties p = new Properties(); // for (int r = 0; r <= rows; r++) { // row = sheet.getRow(r); // if (row != null) { // if (xlsHashKeyColumn != null) { // cell = row.getCell(Short.decode(xlsHashKeyColumn)); // } // if (cell != null) { // hashKey = getXlsCellStringValue(cell); // } // else { // hashKey = SettingConstants.DUT; // } // if (hashKey == hashKeyList.get(q)) { // cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); // propertyKey = getXlsCellStringValue(cell); // cell = row.getCell(Short.decode(xlsPropertyValueColumn)); // propertyValue = getXlsCellStringValue(cell); // // if (propertyKey.equals("MAC Address")) { // p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); // } // // PCPCSII-125 // // Create a colon verions of each MAC Address in the configuration files // if (propertyKey.contains("MAC Address")) { // String value = createMACAddressWithColons(propertyValue); // p.setProperty(propertyKey + " Colon", value); // } // // // Before putting the key/value pair into the property class, // // (except for the LinkLocalAddress property), // // see if it is an IP property and if so determine if the // // value is an IPv6 address using the compressed form. // if (propertyKey.contains(SettingConstants.IP) && // propertyValue.contains(SettingConstants.IP_COMP_FORM)) { // try { // propertyValue = Conversion.ipv6LongForm(propertyValue); // } // catch (IllegalArgumentException iae) { // logger.error(PC2LogCategory.Settings, subCat, // currentHashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue // + ". Detected an invalid IPv6 address."); // } // // } // // p.setProperty(propertyKey, propertyValue); // logger.trace(PC2LogCategory.Settings, subCat, // currentHashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); // } // // } // } // // if (!(currentHashKey.equals("Network Element Label") || // currentHashKey.equals("Label"))){ // addDeviceType(currentHashKey, p); // table.put(currentHashKey, p); // // key = currentHashKey; // if (currentHashKey.equals("DUT")) { // String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); // if (deviceType != null) { // table.put(deviceType+"0", p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + deviceType + "0 to system settings."); // // } // } // else if (currentHashKey.startsWith("UE")) { // String sim = p.getProperty(SettingConstants.SIMULATED); // if (sim != null && // (sim.equalsIgnoreCase("false") || // sim.equalsIgnoreCase("no")|| // sim.equalsIgnoreCase("disable"))) { // String pui = p.getProperty(SettingConstants.PUI); // String pui2 = p.getProperty(SettingConstants.PUI2); // if (pui != null) { // table.put(pui, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui + " to system settings."); // // } // if (pui2 != null && !pui2.equals(pui)) { // table.put(pui2, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui2 + " to system settings."); // } // } // } // } // // } } catch (Exception e) { logger.error(PC2LogCategory.Settings, subCat, "Check xls workbook name, sheet name, and column parameters."); e.printStackTrace(); } return table; }
From source file:com.cdf.objects.ExcelData.java
public void loadData(File file) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row;/*from w w w. j av a 2 s.c om*/ HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int physicalCellCount; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { physicalCellCount = sheet.getRow(i).getPhysicalNumberOfCells(); if (physicalCellCount > cols) { cols = physicalCellCount; } } } System.out.println("Row count : " + rows); System.out.println("Coloumn count: " + cols); }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
/** * Method that load the sample detail into the HSSFWorkbook *//* www. j a v a 2 s. c o m*/ private void loadExcelList() { String rutaResumen = property.getKey(REPORT_PATH_KBIO, Bundle.conf); InputStream inputStream = getClass().getResourceAsStream(rutaResumen); try { listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(0); loadDetailSheet(sheetDetails); HSSFSheet sheetList = listBook.getSheetAt(1); HSSFSheet actsheetUsefull = listBook.getSheetAt(2); int inidatsheetList = 2; int inidatsheetUsefull = 1; String wellkbio; String platenameact = ""; Integer platevoy = 0; Integer rowusefull = 0; int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); int plates = setShipmentDetail.size() / mod; //Crear hojas usefull que necesitare dependiendo del numero de platos for (int j = 1; j < plates; j++) { listBook.cloneSheet(2); } int i = 0; for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail sampledet = shipmentDetail.getStSampleDetail(); HSSFRow actrowsheetList; //**************** sheetList ************************** // if (sheetList.getRow(i + inidatsheetList) == null) sheetList.createRow(i + inidatsheetList); actrowsheetList = sheetList.getRow(i + inidatsheetList); if (actrowsheetList.getCell(0) == null) actrowsheetList.createCell(0); if (sampledet.getSamplegid() != null) actrowsheetList.getCell(0).setCellValue( sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(sampledet.getSamplegid()) : String.valueOf(sampledet.getSamplegid()))); if (actrowsheetList.getCell(1) == null) actrowsheetList.createCell(1); actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename()); if (actrowsheetList.getCell(2) == null) actrowsheetList.createCell(2); wellkbio = sampledet.getPlateloc(); if (wellkbio.length() == 2) wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1); actrowsheetList.getCell(2).setCellValue(wellkbio); if (actrowsheetList.getCell(3) == null) actrowsheetList.createCell(3); actrowsheetList.getCell(3).setCellValue(""); //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal if (sampledet.getControltype() != null) if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B")) actrowsheetList.getCell(3).setCellValue("C"); if (sampledet.getSamplegid() == null) actrowsheetList.getCell(3).setCellValue("C"); if (actrowsheetList.getCell(4) == null) actrowsheetList.createCell(4); actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize()); // ******************** sheetUsefull *********************** if (!platenameact.equals(sampledet.getPlatename())) { platenameact = sampledet.getPlatename(); platevoy = platevoy + 1; actsheetUsefull = listBook.getSheetAt(platevoy + 1); rowusefull = inidatsheetUsefull; } HSSFRow actrowsheetUsefull; if (actsheetUsefull.getRow(rowusefull) == null) actsheetUsefull.createRow(rowusefull); actrowsheetUsefull = actsheetUsefull.getRow(rowusefull); if (actrowsheetUsefull.getCell(1) == null) actrowsheetUsefull.createCell(1); actrowsheetUsefull.getCell(1).setCellValue(wellkbio); if (actrowsheetUsefull.getCell(2) == null) actrowsheetUsefull.createCell(2); actrowsheetUsefull.getCell(2).setCellValue(i + 1); rowusefull = rowusefull + 1; i = i + 1; } } catch (IOException e) { logger.error(e.getMessage()); } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
private void loadListGrid() { String rutaResumen;//from www . j a v a 2 s .c o m int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); int plates = setShipmentDetail.size() / size; if (size == SIZE_PLATE_96) { //PlateContentList.letters=PlateContentList.letters96; rutaResumen = property.getKey(REPORT_PATH_KBIO_G96, Bundle.conf); rowsplate = 8; colsplate = 12; } else { rowsplate = 16; colsplate = 24; rutaResumen = property.getKey(REPORT_PATH_KBIO_G384, Bundle.conf); //PlateContentList.letters=PlateContentList.letters384; } InputStream inputStream = null; String platenameac = ""; int rowplatename; Integer rowItemNo; Integer rowinidatsamples = 4; Integer actualrow = 0; Integer actualcol = 1; Integer cuanhojasvoy = 1; try { inputStream = getClass().getResourceAsStream(rutaResumen); listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(0); loadDetailSheet(sheetDetails); //Crear las hojas que voy a necesitar dependiendo el numero de platos Integer cuanhojasnecesito = 1; if (size == SIZE_PLATE_96) { cuanhojasnecesito = plates / 45; if (plates % 45 != 0) cuanhojasnecesito = cuanhojasnecesito + 1; } if (size == SIZE_PLATE_384) { cuanhojasnecesito = plates / 26; if (plates % 26 != 0) cuanhojasnecesito = cuanhojasnecesito + 1; } for (int i = 1; i < cuanhojasnecesito; i++) { listBook.cloneSheet(1); } HSSFSheet sheetGridActual; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); int cuanplatosvoy = 0; int i = 0; for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail sampledetail = shipmentDetail.getStSampleDetail(); HSSFRow actrowsheetGrid; if (sheetGridActual.getRow(i) == null) sheetGridActual.createRow(i); //poner nombre del plato if (!platenameac.equals(sampledetail.getPlatename())) { platenameac = sampledetail.getPlatename(); cuanplatosvoy = cuanplatosvoy + 1; //si hay mas de 45 platos entonces hacer una nueva hoja if (cuanplatosvoy % 46 == 0 && size == SIZE_PLATE_96) { cuanhojasvoy = cuanhojasvoy + 1; actualrow = 0; actualcol = 1; cuanplatosvoy = 1; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); } //si hay mas de 26 platos entonces hacer una nueva hoja if (cuanplatosvoy % 26 == 0 && size == SIZE_PLATE_384) { cuanhojasvoy = cuanhojasvoy + 1; actualrow = 0; actualcol = 1; cuanplatosvoy = 1; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); } rowplatename = (cuanplatosvoy - 1) * (rowsplate + 2) + cuanplatosvoy; if (cuanplatosvoy > 8) { rowplatename = rowplatename + 1; } //Indica que area se va a combinar (roiwini, rowfin, colini, colfin) if (sheetGridActual.getRow(rowplatename) == null) sheetGridActual.createRow(rowplatename); actrowsheetGrid = sheetGridActual.getRow(rowplatename); if (actrowsheetGrid.getCell(1) == null) actrowsheetGrid.createCell(1); actrowsheetGrid.getCell(1).setCellValue(platenameac); if (cuanplatosvoy == 1) rowinidatsamples = 2; else if (cuanplatosvoy == 9 && size == SIZE_PLATE_96) rowinidatsamples = rowinidatsamples + rowsplate + 4; else rowinidatsamples = rowinidatsamples + rowsplate + 3; } //sacar la siguiente posicion actualrow = actualrow + 1; if (actualrow > rowsplate) { actualcol = actualcol + 1; actualrow = 1; } if (actualcol > colsplate) { actualrow = 1; actualcol = 1; } //PONER EL SAMPLEID rowItemNo = actualrow + rowinidatsamples; if (sheetGridActual.getRow(rowItemNo) == null) sheetGridActual.createRow(rowItemNo); actrowsheetGrid = sheetGridActual.getRow(rowItemNo); if (actrowsheetGrid.getCell(actualcol) == null) actrowsheetGrid.createCell(actualcol); //SI NO TIENE SAMPLEID COLOCAR "BLANK" if (sampledetail.getSamplegid() == null || (sampledetail.getControltype() != null && sampledetail.getControltype().equals("B"))) actrowsheetGrid.getCell(actualcol).setCellValue("BLANK"); else actrowsheetGrid.getCell(actualcol).setCellValue( sampledetail.getLabstudyid().getPrefix() + (sampledetail.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(sampledetail.getSamplegid()) : String.valueOf(sampledetail.getSamplegid()))); i = i + 1; } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
/** * Method that set the first sheet of the information * @param sheetDetails//from w w w .j a va 2s. c o m */ private void loadCustomerDetailSheet(HSSFSheet sheetDetails) { sheetDetails.getRow(8).getCell(2).setCellValue(property.getKey(NAME_COMPANY_SEQUENCE, Bundle.conf)); sheetDetails.getRow(8).getCell(4) .setCellValue(shipment.getStShipmentSet().getStInvestigator().getInvest_name()); sheetDetails.getRow(9).getCell(4).setCellValue(userBean.getResearcherEMail()); sheetDetails.getRow(13).getCell(2) .setCellValue(shipment.getStShipmentSet().getStInvestigator().getInvest_name()); sheetDetails.getRow(14).getCell(2).setCellValue(userBean.getResearcherEMail()); if (shipment.getTrackingNumberLocal() != null && !shipment.getTrackingNumberLocal().trim().equals("")) sheetDetails.getRow(35).getCell(2).setCellValue(shipment.getTrackingNumberLocal()); }