List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:dias.m20150711_get_armband_data.java
public Matrix m20150711_get_armband_data() { eedouble = 0;/*from w ww .j ava2s .com*/ gsrdouble = 0; phys_actdouble = 0; sleepdouble = 0; try { FileInputStream file = new FileInputStream(new File(DIAS.bodymediaFileUrl)); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); int s = 0; int i = 0; int j = 0; int kx = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); i++; s = 0; while (cellIterator.hasNext()) { j++; org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: armband_data.set(i, j, cell.getNumericCellValue()); if (kx == 28) armband_data_with_time.set(i, 5, cell.getNumericCellValue()); // Heat-Flux Average if (kx == 27) armband_data_with_time.set(i, 4, cell.getNumericCellValue()); //Sleep Classification if (kx == 26) armband_data_with_time.set(i, 3, cell.getNumericCellValue()); //Activity Class if (kx == 25) armband_data_with_time.set(i, 2, cell.getNumericCellValue()); //Distance if (kx == 24) armband_data_with_time.set(i, 1, cell.getNumericCellValue()); //Speed if (kx == 23) armband_data_with_time.set(i, 0, cell.getNumericCellValue()); //MET 's kx++; break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equals("NAN")) { if (s == 0) { i--; s = 1; } } break; } } kx = 0; j = 0; } file.close(); s = 0; eedouble = 0; gsrdouble = 0; sleepdouble = 0; phys_actdouble = 0; eedouble = armband_data.get(7164, 18); gsrdouble = armband_data.get(7164, 14); sleepdouble = armband_data.get(7164, 16); phys_actdouble = armband_data.get(7164, 17); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return armband_data; }
From source file:Documentos.ClaseAlmacenGeneral.java
public void crearExcel() { try {/*from w w w . j a v a2 s. c om*/ // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idProducto, nombre, cantidad,UnidadExistencia,minStock where tipoProducto=2 from producto"); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); String minStock = String.valueOf(resultado.getInt("minStock")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + UnidadExistencia + " - " + minStock); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, UnidadExistencia); creandoCelda(wb, fila, 4, minStock); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:Documentos.ClaseAlmacenProducto.java
public void crearExcel() { try {/*from w w w .java 2 s .com*/ // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos existentes-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Precio de Venta", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Ganancia", true, true); createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idproducto,nombre,format(cantidad,0) as cantidad,preciocompra,precioVenta,concat('$ ',format(((precioventa-preciocompra)*cantidad),2)) as ganancia, UnidadExistencia from producto where tipoProducto = 3;"); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String precioVenta = String.valueOf(resultado.getString("precioVenta")); String ganancia = String.valueOf(resultado.getString("ganancia")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); //String Image = String.valueOf(resultado.getBlob("Image")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + precioVenta + " - " + ganancia + " - " + UnidadExistencia); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, precioVenta); creandoCelda(wb, fila, 4, ganancia); creandoCelda(wb, fila, 5, UnidadExistencia); //creandoCelda(wb, fila, 5, Image); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Productos" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:Documentos.ClaseAlmacenXLS.java
public void crearExcel() { try {/* w w w . ja v a 2 s . c om*/ // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idProducto, nombre, cantidad,UnidadExistencia,minStock from producto where tipoProducto=2 "); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); String minStock = String.valueOf(resultado.getInt("minStock")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + UnidadExistencia + " - " + minStock); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, UnidadExistencia); creandoCelda(wb, fila, 4, minStock); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:domain.Excel.java
public static void leerArchivoSesion(String archivoSesion) { List sheetData = new ArrayList(); FileInputStream fis = null; try {/*from w w w. ja va 2 s. c o m*/ fis = new FileInputStream(archivoSesion); 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 (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } } } showExelData(sheetData); }
From source file:edms.core.Config.java
License:Open Source License
public static void convertToXls(InputStream inStream, java.io.File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try {/*from ww w .ja v a2 s . com*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inStream); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:edu.duke.cabig.c3pr.web.study.tabs.StudyEligibilityChecklistTab.java
License:BSD License
public void parseCadsrFile(Study study, POIFSFileSystem pfs, String name) { List<InclusionEligibilityCriteria> incList = null; List<ExclusionEligibilityCriteria> excList = null; if (study.getEpochByName(name) != null) { incList = study.getEpochByName(name).getInclusionEligibilityCriteria(); excList = study.getEpochByName(name).getExclusionEligibilityCriteria(); } else {/* w w w. j a v a2 s . c o m*/ return; } try { HSSFWorkbook wb = new HSSFWorkbook(pfs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow currentRow; HSSFCell currentCell; InclusionEligibilityCriteria inc = null; ExclusionEligibilityCriteria exc = null; Iterator rowIter = sheet.rowIterator(); // iterating over the entire file while (rowIter.hasNext()) { currentRow = (HSSFRow) rowIter.next(); // inclusion section if (currentRow.getCell((short) 0) != null && currentRow.getCell((short) 0).getCellType() == HSSFCell.CELL_TYPE_STRING && currentRow .getCell((short) 0).getRichStringCellValue().toString().startsWith(INCLUSION)) { // iterating over the inclusion rows HSSFRow innerCurrentRow; while (rowIter.hasNext()) { innerCurrentRow = (HSSFRow) rowIter.next(); if (innerCurrentRow.getCell((short) 0) != null && innerCurrentRow.getCell((short) 0).getCellType() == HSSFCell.CELL_TYPE_STRING && innerCurrentRow.getCell((short) 0).getRichStringCellValue().toString() .startsWith(EXCLUSION)) { currentRow = innerCurrentRow; break; } currentCell = innerCurrentRow.getCell((short) 3); if (currentCell != null && currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING && currentCell.getRichStringCellValue().toString().length() > 0) { // create the new inc and populate the list if (inc != null) { incList.add(inc); } inc = new InclusionEligibilityCriteria(); inc.setQuestionText(currentCell.getRichStringCellValue().toString()); } else { // get the answers currentCell = innerCurrentRow.getCell((short) 15); if (currentCell != null && currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING && currentCell.getRichStringCellValue().toString() .equalsIgnoreCase(NOT_APPLICABLE)) { if (inc != null) { inc.setNotApplicableIndicator(true); } } } } // adding the last criteria to the list incList.add(inc); } // end of inclusion if // exclusion section if (currentRow.getCell((short) 0) != null && currentRow.getCell((short) 0).getCellType() == HSSFCell.CELL_TYPE_STRING && currentRow .getCell((short) 0).getRichStringCellValue().toString().startsWith(EXCLUSION)) { // iterating over the exclusion rows HSSFRow innerCurrentRow; while (rowIter.hasNext()) { innerCurrentRow = (HSSFRow) rowIter.next(); // This if loop is not needed as we only have 1 inc section and 1 exc // section // however leaving it in place as it is harmless and will be useful if we // ever have repeated inc/exc sections in the input file if (innerCurrentRow.getCell((short) 0) != null && innerCurrentRow.getCell((short) 0).getCellType() == HSSFCell.CELL_TYPE_STRING && innerCurrentRow.getCell((short) 0).getRichStringCellValue().toString() .startsWith(INCLUSION)) { currentRow = innerCurrentRow; break; } currentCell = innerCurrentRow.getCell((short) 3); if (currentCell != null && currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING && currentCell.getRichStringCellValue().toString().length() > 0) { // create the new exc and populate the list if (exc != null) { excList.add(exc); } exc = new ExclusionEligibilityCriteria(); exc.setQuestionText(currentCell.getRichStringCellValue().toString()); } else { // get the answers currentCell = innerCurrentRow.getCell((short) 15); if (currentCell != null && currentCell.getRichStringCellValue() != null && currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING && currentCell .getRichStringCellValue().toString().equalsIgnoreCase(NOT_APPLICABLE)) { if (exc != null) { exc.setNotApplicableIndicator(true); } } } } // adding the last criteria to the list excList.add(exc); } // end of exclusion if } // end if while loop that iterates over the entire file. } catch (IOException ioe) { log.error(ioe.getMessage()); } }
From source file:edu.ku.brc.specify.tasks.subpane.wb.ConfigureXLS.java
License:Open Source License
@Override protected void nonInteractiveConfig() { try {/*from w w w .j av a 2 s.co m*/ InputStream input = new FileInputStream(externalFile); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); // Calculate the number of rows and columns colInfo = new Vector<ImportColumnInfo>(16); Hashtable<Integer, Boolean> colTracker = new Hashtable<Integer, Boolean>(); boolean firstRow = true; int col = 0; colTracker.clear(); Vector<Integer> badHeads = new Vector<Integer>(); Vector<Integer> emptyCols = new Vector<Integer>(); checkHeadsAndCols(sheet, badHeads, emptyCols); if (firstRowHasHeaders && badHeads.size() > 0) { status = ConfigureExternalDataIFace.Status.Error; showBadHeadingsMsg(badHeads, null, getResourceString("Error")); return; } // Iterate over each row in the sheet @SuppressWarnings("unchecked") Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); if (firstRow || numRows == 1) { // Iterate over each cell in the row and print out the cell's content int colNum = 0; int maxSize = Math.max(row.getPhysicalNumberOfCells(), row.getLastCellNum()); while (colNum < maxSize) { if (emptyCols.indexOf(new Integer(colNum)) == -1) { ImportColumnInfo.ColumnType disciplinee = ImportColumnInfo.ColumnType.Integer; String value = null; boolean skip = false; HSSFCell cell = row.getCell(colNum); if (cell == null) { //assuming numRows == 1 or not firstRowHasHeaders. //the call to checkHeadsAndCols would have already blank headers. value = ""; disciplinee = ImportColumnInfo.ColumnType.String; } else switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: double numeric = cell.getNumericCellValue(); value = Double.toString(numeric); disciplinee = ImportColumnInfo.ColumnType.Double; break; case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString richVal = cell.getRichStringCellValue(); value = richVal.getString().trim(); disciplinee = ImportColumnInfo.ColumnType.String; break; case HSSFCell.CELL_TYPE_BLANK: value = ""; disciplinee = ImportColumnInfo.ColumnType.String; break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean bool = cell.getBooleanCellValue(); value = Boolean.toString(bool); disciplinee = ImportColumnInfo.ColumnType.Boolean; break; default: skip = true; break; } if (numRows == 1 && !skip) { colInfo.get(col).setData(value); col++; } else if (!skip) { if (firstRowHasHeaders) { colInfo.add(new ImportColumnInfo(colNum, disciplinee, value, value, null, null, null)); colTracker.put(col, true); } else { String colName = getResourceString("DEFAULT_COLUMN_NAME") + " " + (colNum + 1); colInfo.add(new ImportColumnInfo(colNum, disciplinee, colName, colName, null, null, null)); colTracker.put(colNum, true); } numCols++; } } colNum++; } firstRow = false; } numRows++; } Collections.sort(colInfo); readMappings(fs); status = Status.Valid; } catch (IOException ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(ConfigureXLS.class, ex); status = Status.Error; } }
From source file:edu.ku.brc.specify.tasks.subpane.wb.DataImportDialog.java
License:Open Source License
/** * Parses the given import xls file according to the users selection and creates/updates the * Preview table, showing the user how the import options effect the way the data will be * imported into the spreadsheet.//from w w w.j a va 2 s .com * * @param table - the table to display the data * @return JTable - the table to display the data */ private JTable setXLSTableData(final JTable table) { int numRows = 0; int numCols = 0; String[] headers = {}; Vector<Vector<String>> tableDataVector = new Vector<Vector<String>>(); Vector<String> rowData = new Vector<String>(); Vector<String> headerVector = new Vector<String>(); DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); try { log.debug("setXLSTableData - file - " + configXLS.getFile().toString()); InputStream input = new FileInputStream(configXLS.getFile()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Vector<Integer> badHeads = new Vector<Integer>(); Vector<Integer> emptyCols = new Vector<Integer>(); ((ConfigureXLS) config).checkHeadsAndCols(sheet, badHeads, emptyCols); if (badHeads.size() > 0 && doesFirstRowHaveHeaders) { if (table != null) { ((ConfigureXLS) config).showBadHeadingsMsg(badHeads, emptyCols, getTitle()); } this.doesFirstRowHaveHeaders = false; try { ignoreActions = true; this.containsHeaders.setSelected(false); } finally { ignoreActions = false; } if (table != null) { return table; } } boolean firstRow = true; //quick fix to prevent ".0" at end of catalog numbers etc NumberFormat nf = NumberFormat.getInstance(); nf.setMinimumFractionDigits(0); nf.setMaximumFractionDigits(20); nf.setGroupingUsed(false); //gets rid of commas int maxCols = 0; // Iterate over each row in the sheet Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { numCols = 0; rowData = new Vector<String>(); HSSFRow row = (HSSFRow) rows.next(); //log.debug(row.getLastCellNum()+" "+row.getPhysicalNumberOfCells()); int maxSize = Math.max(row.getPhysicalNumberOfCells(), row.getLastCellNum()); if (maxSize > maxCols) { maxCols = maxSize; } while (numCols < maxSize) { if (emptyCols.indexOf(new Integer(numCols)) == -1) { HSSFCell cell = row.getCell(numCols); String value = null; // if cell is blank, set value to "" if (cell == null) { value = ""; } else { int type = cell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_NUMERIC: // The best I can do at this point in the app is to guess if a // cell is a date. // Handle dates carefully while using HSSF. Excel stores all // dates as numbers, internally. // The only way to distinguish a date is by the formatting of // the cell. (If you // have ever formatted a cell containing a date in Excel, you // will know what I mean.) // Therefore, for a cell containing a date, cell.getCellType() // will return // HSSFCell.CELL_TYPE_NUMERIC. However, you can use a utility // function, // HSSFDateUtil.isCellDateFormatted(cell), to check if the cell // can be a date. // This function checks the format against a few internal // formats to decide the issue, // but by its very nature it is prone to false negatives. if (HSSFDateUtil.isCellDateFormatted(cell)) { value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); //value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { double numeric = cell.getNumericCellValue(); value = nf.format(numeric); } break; case HSSFCell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: value = UIRegistry.getResourceString("WB_FORMULA_IMPORT_NO_PREVIEW"); break; default: value = ""; log.error("unsuported cell type"); break; } } if (firstRow && doesFirstRowHaveHeaders) { checkUserColInfo(value, numCols); } if (isUserCol(numCols)) { rowData.add(value.toString()); } } numCols++; } if (doesFirstRowHaveHeaders && firstRow) { headerVector = rowData; headers = new String[rowData.size()]; } else if (!doesFirstRowHaveHeaders && firstRow) { //headers = createDummyHeaders(rowData.size()); tableDataVector.add(rowData); } else { tableDataVector.add(rowData); } firstRow = false; numRows++; } maxCols -= emptyCols.size(); if (!doesFirstRowHaveHeaders) { headerVector = createDummyHeadersAsVector(maxCols); headers = new String[maxCols]; } for (int i = 0; i < headerVector.size(); i++) { headers[i] = headerVector.elementAt(i); } printArray(headers); String[][] tableData = new String[tableDataVector.size()][maxCols]; for (int i = 0; i < tableDataVector.size(); i++) { Vector<String> v = tableDataVector.get(i); for (int j = 0; j < v.size(); j++) { tableData[i][j] = v.get(j).toString(); } } if (checkForErrors(headers, tableData)) { errorPanel.showDataImportStatusPanel(true); } else { errorPanel.showDataImportStatusPanel(false); } if ((doesFirstRowHaveHeaders ? numRows - 1 : numRows) > WorkbenchTask.MAX_ROWS) { hasTooManyRows = true; showTooManyRowsErrorDialog(); } else { hasTooManyRows = false; } log.debug(headers); log.debug(tableData); model = new PreviewTableModel(headers, tableData); JTable result = null; if (table == null) { result = new JTable(); result.setColumnSelectionAllowed(false); result.setRowSelectionAllowed(false); result.setCellSelectionEnabled(false); result.getTableHeader().setReorderingAllowed(false); result.setPreferredScrollableViewportSize(new Dimension(500, 100)); result.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); } else { result = table; } result.setModel(model); result.setDefaultRenderer(String.class, new BiColorTableCellRenderer(false)); model.fireTableDataChanged(); model.fireTableStructureChanged(); return result; } catch (Exception ex) { UIRegistry.displayErrorDlgLocalized(UIRegistry.getResourceString("WB_ERROR_READING_IMPORT_FILE")); if (table != null) { String[] columnNames = {}; String[][] blankData = { {} }; model = new PreviewTableModel(columnNames, blankData); table.setModel(model); table.setColumnSelectionAllowed(false); table.setRowSelectionAllowed(false); table.setCellSelectionEnabled(false); table.getTableHeader().setReorderingAllowed(false); table.setPreferredScrollableViewportSize(new Dimension(500, 100)); table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); table.setDefaultRenderer(String.class, new BiColorTableCellRenderer(false)); model.fireTableDataChanged(); model.fireTableStructureChanged(); return table; } //log.error("Error attempting to parse input xls file:" + ex); //ex.printStackTrace(); } return null; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
public DataImportIFace.Status getData(final Workbench workbench) { if (config.getStatus() == ConfigureExternalDataIFace.Status.Valid) { DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat"); try {/*from w w w . j a v a 2s .c o m*/ InputStream input = new FileInputStream(config.getFile()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); int numRows = 0; // Calculate the number of rows and columns Set<WorkbenchTemplateMappingItem> wbtmiSet = workbench.getWorkbenchTemplate() .getWorkbenchTemplateMappingItems(); Vector<WorkbenchTemplateMappingItem> wbtmiList = new Vector<WorkbenchTemplateMappingItem>(); NumberFormat nf = NumberFormat.getInstance(); nf.setMinimumFractionDigits(0); nf.setMaximumFractionDigits(20); nf.setGroupingUsed(false); //gets rid of commas NumberFormat nfGeoCoord = NumberFormat.getInstance(); nfGeoCoord.setMinimumFractionDigits(0); nfGeoCoord.setMaximumFractionDigits( LatLonConverter.DECIMAL_SIZES[LatLonConverter.FORMAT.DDDDDD.ordinal()]); nfGeoCoord.setGroupingUsed(false); //gets rid of commas char decSep = new DecimalFormatSymbols().getDecimalSeparator(); wbtmiList.addAll(wbtmiSet); Collections.sort(wbtmiList); this.truncations.clear(); Vector<HSSFHyperlink> activeHyperlinks = new Vector<HSSFHyperlink>(); // Iterate over each row in the sheet Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); if (numRows == 0 && config.getFirstRowHasHeaders()) { numRows++; getSystemCols(row); continue; } WorkbenchRow wbRow = workbench.addRow(); for (WorkbenchTemplateMappingItem wbtmi : wbtmiList) { int cellNum = wbtmi.getOrigImportColumnIndex().intValue(); if (cellNum == -1) { if (wbtmi.getViewOrder() != null) { cellNum = wbtmi.getViewOrder().intValue(); if (cellNum == -1) { continue; } } } HSSFCell cell = row.getCell(cellNum); if (cell == null) { continue; } int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_FORMULA) { type = cell.getCachedFormulaResultType(); } String value = ""; boolean skip = false; switch (type) { case HSSFCell.CELL_TYPE_NUMERIC: { if (HSSFDateUtil.isCellDateFormatted(cell)) { //even if WorkbenchTask.getDataType(wbtmi) is not Calendar or Date. Hmmmm. value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { Class<?> classObj = WorkbenchTask.getDataType(wbtmi); if (classObj.equals(Integer.class)) { double numeric = cell.getNumericCellValue(); value = Integer.toString((int) numeric); } else if (classObj.equals(Calendar.class) || classObj.equals(Date.class)) { Date d = cell.getDateCellValue(); if (d != null) { value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue()); } else { value = null; } } else { double numeric = cell.getNumericCellValue(); value = nf.format(numeric); if (isGeoCoordinate(wbtmi)) { int sepInx = value.indexOf(decSep); if (sepInx > -1 && value.substring(sepInx).length() > nfGeoCoord .getMaximumFractionDigits()) { String value2 = nfGeoCoord.format(numeric); int maxlen = wbtmi.getFieldName().startsWith("latitude") ? nfGeoCoord.getMaximumFractionDigits() + 3 : nfGeoCoord.getMaximumFractionDigits() + 4; if (numeric < 0) { maxlen++; } //System.out.println(value + " " + trackTrunc(value, numRows, wbtmi.getViewOrder(), wbtmi.getCaption(), // maxlen) + " " + value2); value = value2; } } } } break; } case HSSFCell.CELL_TYPE_STRING: HSSFHyperlink hl = checkHyperlinks(cell, activeHyperlinks); if (hl == null /*|| (hl != null && hl.getType() == HSSFHyperlink.LINK_EMAIL)*/) { value = cell.getRichStringCellValue().getString(); } else { //value = hl.getAddress(); value = hl.getLabel(); } break; case HSSFCell.CELL_TYPE_BLANK: value = ""; type = HSSFCell.CELL_TYPE_STRING; break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean bool = cell.getBooleanCellValue(); value = Boolean.toString(bool); break; default: skip = true; break; } if (!skip && value != null && !value.trim().equals("")) { wbRow.setData(truncateIfNecessary(value, numRows, wbtmi), wbtmi.getViewOrder(), true); } } addImageInfo(row, wbRow); addGeoInfo(row, wbRow); numRows++; } if (activeHyperlinks.size() > 0) { log.warn("Hyperlinks vector not empty after import. Overlapping hyperlink ranges?"); } return status = this.truncations.size() == 0 && this.messages.size() == 0 ? DataImportIFace.Status.Valid : DataImportIFace.Status.Modified; } catch (Exception ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSImport.class, ex); log.error(ex); } } return status = DataImportIFace.Status.Error; }