List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
public Vector getTableContent(int sheet_number, int startIndex, int endIndex) { HSSFSheet sheet = book.getSheetAt(sheet_number); Vector v = new Vector(); getRowData(v, sheet.getRow(0)); for (int i = startIndex; i <= endIndex; i++) { getRowData(v, sheet.getRow(i));/*from w ww .j ava2s. com*/ } return v; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static HSSFRow getRow(HSSFSheet sheet, int row) { HSSFRow hssfRow = sheet.getRow(row); return hssfRow; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static boolean getIsBold(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static boolean getIsItalic(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getItalic(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static short getFontColor(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getColor(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static HSSFCell createCell(HSSFSheet sheet, int row, short col) { if (sheet == null) { return null; }//from ww w . j a va 2 s . com HSSFRow rowc = sheet.getRow(row); if (rowc == null) { rowc = createRow(sheet, row); } if (rowc != null) { HSSFCell cell = rowc.getCell(col); if (cell != null) { return cell; } return rowc.createCell(col); } return null; }
From source file:gov.nih.nci.ncicb.cadsr.cdebrowser.process.GetExcelDownload.java
License:BSD License
public void generateExcelFile(String filename, DBUtil dbUtil) throws Exception { Connection cn = null;//from w ww . j a va2s . c om Statement st = null; ResultSet rs = null; PrintWriter pw = null; String where = ""; DataElementSearchBean desb = null; DESearchQueryBuilder deSearch = null; String source = null; HSSFWorkbook wb = null; FileOutputStream fileOut = null; source = getStringInfo("src"); String RAI = ""; try { RAI = "'" + CaDSRUtil.getNciRegistryId() + "'"; } catch (IOException e) { RAI = DEFAULT_RAI; } try { //String dataSource = getStringInfo("SBREXT_DSN"); //cn = dbUtil.getConnection(); -- Commented for JBoss deployment //ApplicationParameters ap = ApplicationParameters.getInstance("cdebrowser"); dbUtil.getOracleConnectionFromContainer(); //getConnectionFromContainer(); went back to original call cn = dbUtil.getConnection(); st = cn.createStatement(); if ("deSearch".equals(source) || "deSearchPrior".equals(source)) { desb = (DataElementSearchBean) getInfoObject("desb"); deSearch = (DESearchQueryBuilder) getInfoObject(ProcessConstants.DE_SEARCH_QUERY_BUILDER); where = deSearch.getXMLQueryStmt(); } else if ("cdeCart".equals(source) || "cdeCartPrior".equals(source)) { HttpServletRequest myRequest = (HttpServletRequest) getInfoObject("HTTPRequest"); HttpSession userSession = myRequest.getSession(false); CDECart cart = (CDECart) userSession.getAttribute(CaDSRConstants.CDE_CART); Collection items = cart.getDataElements(); CDECartItem item = null; boolean firstOne = true; StringBuffer whereBuffer = new StringBuffer(""); Iterator itemsIt = items.iterator(); while (itemsIt.hasNext()) { item = (CDECartItem) itemsIt.next(); if (firstOne) { whereBuffer.append("'" + item.getId() + "'"); firstOne = false; } else { whereBuffer.append(",'" + item.getId() + "'"); } } where = whereBuffer.toString(); } else { throw new Exception("No result set to download"); } String sqlStmt = "SELECT DE_EXCEL_GENERATOR_VIEW.*," + RAI + " as \"RAI\" FROM DE_EXCEL_GENERATOR_VIEW " + "WHERE DE_IDSEQ IN " + " ( " + where + " ) "; //+" ORDER BY PREFERRED_NAME "; rs = st.executeQuery(sqlStmt); List colInfo = this.initColumnInfo(source); wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); int rowNumber = 0; HSSFCellStyle boldCellStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldCellStyle.setFont(font); boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL); // Create a row and put the column header in it HSSFRow row = sheet.createRow(rowNumber++); short col = 0; for (int i = 0; i < colInfo.size(); i++) { ColumnInfo currCol = (ColumnInfo) colInfo.get(i); if (currCol.type.indexOf("Array") >= 0) { for (int nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) { ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI); HSSFCell cell = row.createCell(col++); cell.setCellValue(currCol.displayName + nestedCol.displayName); cell.setCellStyle(boldCellStyle); } } else { HSSFCell cell = row.createCell(col++); cell.setCellValue(currCol.displayName); cell.setCellStyle(boldCellStyle); } } int maxRowNumber = 0; while (rs.next()) { row = sheet.createRow(rowNumber); col = 0; for (int i = 0; i < colInfo.size(); i++) { ColumnInfo currCol = (ColumnInfo) colInfo.get(i); if (currCol.type.indexOf("Array") >= 0) { ARRAY array = null; if (currCol.type.equalsIgnoreCase("Array")) { array = ((OracleResultSet) rs).getARRAY(currCol.rsColumnName); } else if (currCol.type.equalsIgnoreCase("StructArray")) { STRUCT struct = ((OracleResultSet) rs).getSTRUCT(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); array = (ARRAY) valueStruct[currCol.rsIndex]; } if ((array != null) && (array.length() != 0)) { ResultSet nestedRs = array.getResultSet(); int nestedRowNumber = 0; while (nestedRs.next()) { row = sheet.getRow(rowNumber + nestedRowNumber); if (row == null) { row = sheet.createRow(rowNumber + nestedRowNumber); maxRowNumber = rowNumber + nestedRowNumber; } STRUCT valueStruct = null; // STRUCT valueStruct = (STRUCT) nestedRs.getObject(2); try { valueStruct = (STRUCT) nestedRs.getObject(2); } catch (SQLException sqlEx) { //sqlEx.printStackTrace(); } if (valueStruct != null) { Datum[] valueDatum = valueStruct.getOracleAttributes(); for (short nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) { ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI); HSSFCell cell = row.createCell((short) (col + nestedI)); if (nestedCol.rsSubIndex < 0) { if (valueDatum[nestedCol.rsIndex] != null) { if (nestedCol.type.equalsIgnoreCase("Number")) { cell.setCellValue( ((NUMBER) valueDatum[nestedCol.rsIndex]).floatValue()); } else if (nestedCol.type.equalsIgnoreCase("Date")) { cell.setCellValue(((DATE) valueDatum[nestedCol.rsIndex]) .dateValue().toString()); } else { String stringCellValue = ((CHAR) valueDatum[nestedCol.rsIndex]) .stringValue(); cell.setCellValue(StringUtils .updateDataForSpecialCharacters(stringCellValue)); // cell.setCellValue( // ((CHAR) valueDatum[nestedCol.rsIndex]).stringValue()); } } } else { STRUCT nestedStruct = (STRUCT) valueDatum[nestedCol.rsIndex]; Datum[] nestedDatum = nestedStruct.getOracleAttributes(); if (nestedCol.type.equalsIgnoreCase("Number")) { //changed the conversion from stringValue from floatValue 07/11/2007 to fix GF7664 Prerna cell.setCellValue( ((NUMBER) nestedDatum[nestedCol.rsSubIndex]).stringValue()); } else if (nestedCol.type.equalsIgnoreCase("String")) { String stringCellValue = ((CHAR) nestedDatum[nestedCol.rsSubIndex]) .toString(); cell.setCellValue(StringUtils .updateDataForSpecialCharacters(stringCellValue)); // cell.setCellValue( // ((CHAR) nestedDatum[nestedCol.rsSubIndex]).toString()); } } } } nestedRowNumber++; } } col += currCol.nestedColumns.size(); } else if (currCol.type.equalsIgnoreCase("Struct")) { STRUCT struct = ((OracleResultSet) rs).getSTRUCT(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); HSSFCell cell = row.createCell(col++); cell.setCellValue( StringUtils.updateDataForSpecialCharacters((String) valueStruct[currCol.rsIndex])); } else { row = sheet.getRow(rowNumber); HSSFCell cell = row.createCell(col++); // Changed the way date is displayed in Excel in 4.0 String columnName = ((ColumnInfo) colInfo.get(i)).rsColumnName; if (currCol.type.equalsIgnoreCase("Date")) { cell.setCellValue( (rs.getDate(columnName) != null) ? (rs.getDate(columnName)).toString() : ""); } else { /* if (columnName.equals("RAI")) { if (rowNumber == 1) cell.setCellValue(RAI); else cell.setCellValue(""); } else { */ cell.setCellValue(StringUtils.updateDataForSpecialCharacters(rs.getString(columnName))); //} } } } if (maxRowNumber > rowNumber) rowNumber = maxRowNumber + 2; else rowNumber += 2; } fileOut = new FileOutputStream(filename); wb.write(fileOut); } catch (Exception ex) { log.error("Exception caught in Generate Excel File", ex); ex.printStackTrace(); throw ex; } finally { try { if (rs != null) { rs.close(); } if (st != null) { st.close(); } if (cn != null) { cn.close(); // Uncommented for JBoss deployment } if (fileOut != null) { fileOut.close(); } } catch (Exception e) { log.debug("Unable to perform clean up due to the following error ", e); } } }
From source file:gov.nih.nci.ncicb.cadsr.common.downloads.impl.GetExcelDownloadImpl.java
License:BSD License
private void generateDataRow(int rowNumber, HSSFSheet sheet, List colInfo, ResultSet rs) { try {//from w w w.ja v a 2s . c o m int maxRowNumber = 0; HSSFRow row; while (rs.next()) { row = sheet.createRow(rowNumber); short col = 0; for (int i = 0; i < colInfo.size(); i++) { ColumnInfo currCol = (ColumnInfo) colInfo.get(i); if (currCol.type.indexOf("Array") >= 0) { Array array = null; if (currCol.type.equalsIgnoreCase("Array")) { array = rs.getArray(currCol.rsColumnName); } else if (currCol.type.equalsIgnoreCase("StructArray")) { Struct struct = (Struct) rs.getObject(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); array = (Array) valueStruct[currCol.rsIndex]; } if (array != null) { ResultSet nestedRs = array.getResultSet(); int nestedRowNumber = 0; while (nestedRs.next()) { row = sheet.getRow(rowNumber + nestedRowNumber); if (row == null) { row = sheet.createRow(rowNumber + nestedRowNumber); maxRowNumber = rowNumber + nestedRowNumber; } Struct valueStruct = (Struct) nestedRs.getObject(2); Object[] valueDatum = valueStruct.getAttributes(); for (short nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) { ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI); HSSFCell cell = row.createCell((short) (col + nestedI)); if (nestedCol.rsSubIndex < 0) { if (valueDatum[nestedCol.rsIndex] != null) { if (nestedCol.type.equalsIgnoreCase("Number")) { cell.setCellValue( ((Number) valueDatum[nestedCol.rsIndex]).floatValue()); } else if (nestedCol.type.equalsIgnoreCase("Date")) { cell.setCellValue( ((Date) valueDatum[nestedCol.rsIndex]).toString()); } else { cell.setCellValue((String) valueDatum[nestedCol.rsIndex]); } } } else { Struct nestedStruct = (Struct) valueDatum[nestedCol.rsIndex]; Object[] nestedDatum = nestedStruct.getAttributes(); if (nestedCol.type.equalsIgnoreCase("Number")) { //changed the conversion from stringValue from floatValue 07/11/2007 to fix GF7664 Prerna cell.setCellValue( ((Number) nestedDatum[nestedCol.rsSubIndex]).doubleValue()); } else if (nestedCol.type.equalsIgnoreCase("String")) { cell.setCellValue((nestedDatum[nestedCol.rsSubIndex]).toString()); } } } nestedRowNumber++; } } col += currCol.nestedColumns.size(); } else if (currCol.type.equalsIgnoreCase("Struct")) { Struct struct = (Struct) rs.getObject(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); HSSFCell cell = row.createCell(col++); cell.setCellValue((String) valueStruct[currCol.rsIndex]); } else { row = sheet.getRow(rowNumber); HSSFCell cell = row.createCell(col++); // Changed the way date is displayed in Excel in 4.0 String columnName = ((ColumnInfo) colInfo.get(i)).rsColumnName; if (currCol.type.equalsIgnoreCase("Date")) { cell.setCellValue( (rs.getDate(columnName) != null) ? (rs.getDate(columnName)).toString() : ""); } else { cell.setCellValue(rs.getString(columnName)); } } } if (maxRowNumber > rowNumber) rowNumber = maxRowNumber + 2; else rowNumber += 2; } } catch (Exception e) { log.error("unable to generate excel data ", e); } }
From source file:gr.abiss.calipso.domain.ExcelFile.java
License:Open Source License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;/*from w w w .j a v a 2 s . c o 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 ==================================== 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 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(null); } } if (isEmptyRow) { break; } rows.add(rowData); } }
From source file:gtu._work.etc.TestCaseExcelMakerUI.java
License:Open Source License
private void initGUI() { try {/*from w w w. ja v a 2 s . co m*/ BorderLayout thisLayout = new BorderLayout(); setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE); getContentPane().setLayout(thisLayout); { jTabbedPane1 = new JTabbedPane(); getContentPane().add(jTabbedPane1, BorderLayout.CENTER); { jPanel1 = new JPanel(); BorderLayout jPanel1Layout = new BorderLayout(); jPanel1.setLayout(jPanel1Layout); jTabbedPane1.addTab("jPanel1", null, jPanel1, null); { jScrollPane1 = new JScrollPane(); jPanel1.add(jScrollPane1, BorderLayout.CENTER); jScrollPane1.setPreferredSize(new java.awt.Dimension(573, 364)); { DefaultTableModel model = JTableUtil.createModel(false, "??", "??", "1", "2"); model.addRow(new Object[] { "", "", "", "" }); jTable1 = new JTable(); JTableUtil.defaultSetting(jTable1); jScrollPane1.setViewportView(jTable1); jTable1.setModel(model); jTable1.addMouseListener(new MouseAdapter() { public void mouseClicked(MouseEvent evt) { System.out.println("jTable1.mouseClicked, event=" + evt); List<JMenuItem> menuList = JTableUtil.newInstance(jTable1) .getDefaultJMenuItems(); JPopupMenuUtil.newInstance(jTable1).addJMenuItem(menuList).applyEvent(evt) .show(); } }); } } } final HSSFWorkbook workBook = readFile(); { jPanel2 = new JPanel(); jTabbedPane1.addTab("jPanel2", null, jPanel2, null); { executeBtn = new JButton(); jPanel2.add(executeBtn); executeBtn.setText("execute"); executeBtn.setPreferredSize(new java.awt.Dimension(117, 45)); executeBtn.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent evt) { File outputDir = JCommonUtil._jFileChooser_selectDirectoryOnly(); if (outputDir == null) { JCommonUtil._jOptionPane_showMessageDialog_error("dir is not correct!"); return; } HSSFSheet sheet = workBook.getSheetAt(0); HSSFCell cell_RC = sheet.getRow(0).getCell(1); HSSFCell cell_model = sheet.getRow(1).getCell(1); HSSFCell cell_model_Chn = sheet.getRow(1).getCell(5); HSSFCell cell_Controller = sheet.getRow(4).getCell(0); HSSFCell cell_Controller_persent = sheet.getRow(4).getCell(4); HSSFCell cell_Controller_test = sheet.getRow(4).getCell(7); HSSFCell cell_Controller_pic = sheet.getRow(4).getCell(8); HSSFCell cell_Service = sheet.getRow(5).getCell(0); HSSFCell cell_Service_persent = sheet.getRow(5).getCell(4); HSSFCell cell_Service_test = sheet.getRow(5).getCell(7); HSSFCell cell_Service_pic = sheet.getRow(5).getCell(8); DefaultTableModel model = JTableUtil.newInstance(jTable1).getModel(); for (int ii = 0; ii < model.getRowCount(); ii++) { String opName = (String) model.getValueAt(ii, 0); String opChName = (String) model.getValueAt(ii, 1); String persent1 = (String) model.getValueAt(ii, 2); String persent2 = (String) model.getValueAt(ii, 3); String operation = opName.substring(0, 2).toUpperCase(); opName = opName.replaceAll("[-_]", "").toLowerCase(); opName = opName.substring(0, 1).toUpperCase() + opName.substring(1); String modelName = "FNM_" + operation + "_FR_" + opName.toUpperCase().substring(2); String fileName = "FNM_" + operation + "_FR_" + opName.toUpperCase().substring(2); cell_RC.setCellValue(operation); cell_model.setCellValue(modelName); cell_model_Chn.setCellValue(opChName); cell_Controller.setCellValue(opName + "Controller"); cell_Controller_persent.setCellValue(persent1); cell_Controller_test.setCellValue(opName + "ControllerTest"); cell_Controller_pic.setCellValue(opName + "ControllerReport.jpg"); cell_Service.setCellValue(opName + "ServiceImpl"); cell_Service_persent.setCellValue(persent2); cell_Service_test.setCellValue(opName + "ServiceImplTest"); cell_Service_pic.setCellValue(opName + "ServiceImplReport.jpg"); try { ExcelUtil.getInstance().writeExcel(new File(outputDir, fileName + ".xls"), workBook); } catch (Exception e) { e.printStackTrace(); } } } }); } } } pack(); this.setSize(594, 431); loadInitExcel(); } catch (Exception e) { //add your error handling code here e.printStackTrace(); } }