List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:poi.hssf.view.SVTableCellEditor.java
License:Apache License
/** * Gets the tableCellEditorComponent attribute of the SVTableCellEditor object * * @return The tableCellEditorComponent value *///from w ww .j ava2 s .c o m public Component getTableCellEditorComponent(JTable table, Object value, boolean isSelected, int row, int column) { System.out.println("GetTableCellEditorComponent"); HSSFCell cell = (HSSFCell) value; if (cell != null) { HSSFCellStyle style = cell.getCellStyle(); HSSFFont f = wb.getFontAt(style.getFontIndex()); boolean isbold = f.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL; boolean isitalics = f.getItalic(); int fontstyle = Font.PLAIN; if (isbold) fontstyle = Font.BOLD; if (isitalics) fontstyle = fontstyle | Font.ITALIC; int fontheight = f.getFontHeightInPoints(); if (fontheight == 9) fontheight = 10; //fix for stupid ol Windows Font font = new Font(f.getFontName(), fontstyle, fontheight); editor.setFont(font); if (style.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) { editor.setBackground(getAWTColor(style.getFillForegroundColor(), white)); } else editor.setBackground(white); editor.setForeground(getAWTColor(f.getColor(), black)); //Set the value that is rendered for the cell switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: editor.setText(""); break; case HSSFCell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { editor.setText("true"); } else { editor.setText("false"); } break; case HSSFCell.CELL_TYPE_NUMERIC: editor.setText(Double.toString(cell.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: editor.setText(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_FORMULA: default: editor.setText("?"); } switch (style.getAlignment()) { case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_JUSTIFY: case HSSFCellStyle.ALIGN_FILL: editor.setHorizontalAlignment(SwingConstants.LEFT); break; case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: editor.setHorizontalAlignment(SwingConstants.CENTER); break; case HSSFCellStyle.ALIGN_GENERAL: case HSSFCellStyle.ALIGN_RIGHT: editor.setHorizontalAlignment(SwingConstants.RIGHT); break; default: editor.setHorizontalAlignment(SwingConstants.LEFT); break; } } return editor; }
From source file:poi.hssf.view.SVTableCellRenderer.java
License:Apache License
public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) { boolean isBorderSet = false; //If the JTables default cell renderer has been setup correctly the //value will be the HSSFCell that we are trying to render HSSFCell c = (HSSFCell) value; if (c != null) { HSSFCellStyle s = c.getCellStyle(); HSSFFont f = wb.getFontAt(s.getFontIndex()); setFont(SVTableUtils.makeFont(f)); if (s.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) { setBackground(SVTableUtils.getAWTColor(s.getFillForegroundColor(), SVTableUtils.white)); } else// w ww .j a v a 2s . c o m setBackground(SVTableUtils.white); setForeground(SVTableUtils.getAWTColor(f.getColor(), SVTableUtils.black)); cellBorder.setBorder(SVTableUtils.getAWTColor(s.getTopBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getRightBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getBottomBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getLeftBorderColor(), SVTableUtils.black), s.getBorderTop(), s.getBorderRight(), s.getBorderBottom(), s.getBorderLeft(), hasFocus); setBorder(cellBorder); isBorderSet = true; //Set the value that is rendered for the cell switch (c.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: setValue(""); break; case HSSFCell.CELL_TYPE_BOOLEAN: if (c.getBooleanCellValue()) { setValue("true"); } else { setValue("false"); } break; case HSSFCell.CELL_TYPE_NUMERIC: short format = s.getDataFormat(); double numericValue = c.getNumericCellValue(); if (cellFormatter.useRedColor(format, numericValue)) setForeground(Color.red); else setForeground(null); setValue(cellFormatter.format(format, c.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: setValue(c.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_FORMULA: default: setValue("?"); } //Set the text alignment of the cell switch (s.getAlignment()) { case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_JUSTIFY: case HSSFCellStyle.ALIGN_FILL: setHorizontalAlignment(SwingConstants.LEFT); break; case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: setHorizontalAlignment(SwingConstants.CENTER); break; case HSSFCellStyle.ALIGN_GENERAL: case HSSFCellStyle.ALIGN_RIGHT: setHorizontalAlignment(SwingConstants.RIGHT); break; default: setHorizontalAlignment(SwingConstants.LEFT); break; } } else { setValue(""); setBackground(SVTableUtils.white); } if (hasFocus) { if (!isBorderSet) { //This is the border to paint when there is no border //and the cell has focus cellBorder.setBorder(SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, isSelected); setBorder(cellBorder); } if (table.isCellEditable(row, column)) { setForeground(UIManager.getColor("Table.focusCellForeground")); setBackground(UIManager.getColor("Table.focusCellBackground")); } } else if (!isBorderSet) { setBorder(noFocusBorder); } // ---- begin optimization to avoid painting background ---- Color back = getBackground(); boolean colorMatch = (back != null) && (back.equals(table.getBackground())) && table.isOpaque(); setOpaque(!colorMatch); // ---- end optimization to aviod painting background ---- return this; }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main//from w w w .ja v a 2 s .c om * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:POI.Sheet.java
/** * ??/*from w ww.ja v a2s . c o m*/ * * @param index ? * @return ? * @see HSSFRow * @see HSSFCell */ public ArrayList<String> getRowAt(int index) { HSSFRow row = sheet.getRow(index); ArrayList<String> cells = new ArrayList<String>(); int i = row.getFirstCellNum(); while (i < this.getColumnSize()) { HSSFCell cell = row.getCell(i++); if (cell == null) { cells.add(""); } else { Object val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: val = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: val = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); default: val = cell.getRichStringCellValue(); } cells.add(String.valueOf(val)); } } return cells; }
From source file:POS.migrate.Excel_to_db_inventory_items.java
public static List<Excel_to_db_inventory_items> showExcelData(List sheetData, String path) { FileInputStream fis;//from ww w .j a va 2s. c o m List<Excel_to_db_inventory_items> datas = new ArrayList(); try { fis = new FileInputStream(path); int r = 0; int r_set = 1; int id = 0; for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); int size = list.size(); String[] record = new String[12]; int record_size = 0; for (int j = 0; j < list.size(); j++) { CellReference cellReference = new CellReference("B3"); HSSFCell cell = (HSSFCell) list.get(j); HSSFDataFormatter hdf = new HSSFDataFormatter(); String data = ""; if (j >= 12) { break; } if (j == 1 || j == 4 || j == 5) { try { data = "" + cell.getNumericCellValue(); } catch (Exception e) { data = "" + cell.getStringCellValue(); } } else { try { data = "" + cell.getStringCellValue(); } catch (Exception e) { data = "" + cell.getNumericCellValue(); } } record[record_size] = data; // System.out.print(data + " | "); record_size++; } String qty = record[0]; String item_code = record[1]; String barcode = record[2]; String description = record[3]; String cost = record[4]; String selling_price = record[5]; String category = record[6]; String classification = record[7]; String sub_classification = record[8]; String brand = record[9]; String model = record[10]; String unit = record[11]; System.out.println("model:" + model); System.out.println("unit:" + unit); Excel_to_db_inventory_items encoded = new Excel_to_db_inventory_items(qty, item_code, barcode, description, cost, selling_price, category, classification, sub_classification, brand, model, unit); if (record[0] != null) { datas.add(encoded); System.out.println(""); } } } catch (FileNotFoundException ex) { Logger.getLogger(Excel_to_db_inventory_items.class.getName()).log(Level.SEVERE, null, ex); } return datas; }
From source file:POS.test2.pisps_items.java
public static List<items> showItems() { String path = "C:\\\\Users\\\\Guinness\\\\Documents\\\\Projects\\\\Algorithm\\\\pisps records\\\\items.xls"; FileInputStream fis = null;/*from ww w . ja va 2 s . co m*/ List sheetData = new ArrayList(); try { fis = new FileInputStream(path); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<items> datas = new ArrayList(); try { fis = new FileInputStream(path); int r = 0; int r_set = 1; String prev_no = ""; for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); int size = list.size(); List<String> record = new ArrayList(); for (int j = 0; j < list.size(); j++) { HSSFCell cell = (HSSFCell) list.get(j); HSSFDataFormatter hdf = new HSSFDataFormatter(); String data = ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mydata = cell.getStringCellValue(); data = data + "" + mydata + ""; record.add(data); } else { data = data + cell.getNumericCellValue() + ""; record.add(data); } } String[] aw = new String[size]; int jj = 0; for (String s : record) { aw[jj] = s; jj++; } String item_code = FitIn.fmt_woc(aw[7]); String description = aw[1]; String category = aw[2]; String classification = aw[3]; String sub_classification = aw[4]; String brand = aw[5]; String model = aw[6]; items item = new items(item_code, description, category, classification, sub_classification, brand, model); datas.add(item); } return datas; } catch (FileNotFoundException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } return datas; }
From source file:POS.test2.pisps_items.java
public static List<cost> showCost() { String path = "C:\\\\Users\\\\Guinness\\\\Documents\\\\Projects\\\\Algorithm\\\\pisps records\\\\cost.xls"; FileInputStream fis = null;/*from w w w .ja va 2s . c o m*/ List sheetData = new ArrayList(); try { fis = new FileInputStream(path); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { JOptionPane.showMessageDialog(null, "Unsupported Format"); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<cost> datas = new ArrayList(); try { fis = new FileInputStream(path); int r = 0; int r_set = 1; String prev_no = ""; for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); int size = list.size(); List<String> record = new ArrayList(); for (int j = 0; j < list.size(); j++) { HSSFCell cell = (HSSFCell) list.get(j); HSSFDataFormatter hdf = new HSSFDataFormatter(); String data = ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mydata = cell.getStringCellValue(); data = data + "" + mydata + ""; record.add(data); } else { data = data + cell.getNumericCellValue() + ""; record.add(data); } } String[] aw = new String[size]; int jj = 0; for (String s : record) { aw[jj] = s; jj++; } String item_code = FitIn.fmt_woc(aw[0]); double cost = FitIn.toDouble(aw[1]); double price = FitIn.toDouble(aw[2]); cost cos = new cost(item_code, cost, price); datas.add(cos); } return datas; } catch (FileNotFoundException ex) { Logger.getLogger(pisps_items.class.getName()).log(Level.SEVERE, null, ex); } return datas; }
From source file:projet_muticriteres.Fenetre.java
private void jMenuItem1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jMenuItem1ActionPerformed final JFileChooser fc = new JFileChooser(new File(".")); FileNameExtensionFilter filtre = new FileNameExtensionFilter("Fichier Excel", "xlsx", "xls"); fc.setFileFilter(filtre);//from w ww .j a va 2 s.com File fichier; if (fc.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) { fichier = fc.getSelectedFile(); try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fichier.getCanonicalPath())); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; double totalLigne = 0.0; double totalGeneral = 0.0; int numLigne = 1; int ligne = 0; int colonne = 0; int k; row = sheet.getRow(0); while (sheet.getRow(ligne) != null) { row = sheet.getRow(ligne); colonne = 0; k = 0; if (ligne == 0) { colonne = 1; while (row.getCell(colonne) != null) { cell = row.getCell(colonne); tabCriteres.add(cell.getStringCellValue()); colonne++; } } else { while (row.getCell(colonne) != null) { cell = row.getCell(colonne); if (colonne == 0) { action = new Action(cell.getStringCellValue(), tabCriteres.size()); } else { action.ajouterNote((int) cell.getNumericCellValue(), colonne - 1); } colonne++; } tabActions.add(action); } ligne++; } fs.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } /*for (int i=0; i<tabCriteres.size(); i++){ System.out.println("critere "+i+" : "+tabCriteres.get(i)); } for (int i=0; i<tabActions.size(); i++){ System.out.println("action "+i+" : "+tabActions.get(i).getNom()); for (int j=0; j<tabCriteres.size();j++){ System.out.println("note"+j+":"+tabActions.get(i).getNote(j)); } }*/ titreColonne = new String[tabCriteres.size() + 1]; donnees = new Object[tabActions.size()][tabCriteres.size() + 1]; titreColonne[0] = ""; for (int i = 0; i < tabCriteres.size(); i++) { titreColonne[i + 1] = tabCriteres.get(i); } for (int i = 0; i < tabActions.size(); i++) { donnees[i][0] = tabActions.get(i).getNom(); } for (int j = 0; j < tabActions.size(); j++) { for (int k = 1; k < tabCriteres.size() + 1; k++) { donnees[j][k] = tabActions.get(j).getNote(k - 1); } } model = new DefaultTableModel(donnees, titreColonne); jTable1.setModel(model); // this.getContentPane().add(new JScrollPane(tab)); //this.add(tab); //jPanel2.add(label10); //jButton6.setLabel("buton"); //jPanel2.add(jButton6); jTable1.setVisible(true); //jPanel2.repaint(); for (int i = 0; i < tabCriteres.size() + 1; i++) { System.out.println("critere " + i + " : " + titreColonne[i]); } for (int i = 0; i < tabActions.size(); i++) { System.out.println("action " + i + " : " + donnees[i][0]); for (int j = 1; j < tabCriteres.size() + 1; j++) { System.out.println("note" + j + ":" + donnees[i][j]); } } } }
From source file:qa.dataset.java
public void readfile() { try {/*ww w .j av a 2 s.co m*/ InputStream input = new BufferedInputStream(new FileInputStream("E:/Tugas Akhir Ali/dataset.xls")); POIFSFileSystem filesystem = new POIFSFileSystem(input); HSSFWorkbook workbook = new HSSFWorkbook(filesystem); HSSFSheet sheet = workbook.getSheetAt(0); String a; String b; MaxentTagger tagger = new MaxentTagger( "E:/Tugas Akhir Ali/stanford-postagger-2011-04-20/models/left3words-wsj-0-18.tagger"); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); System.out.println("\n"); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) System.out.print(cell.getNumericCellValue() + " "); else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) System.out.print(cell.getStringCellValue() + " "); else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("Blank"); else System.out.print("Unknown"); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {/*from w ww . jav a 2 s.c o m*/ HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }