List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
From source file:br.sp.telesul.service.ExportServiceImpl.java
@Override public List<Funcionario> readExcelDocument() { try {// w w w . j a v a 2s . com List<Funcionario> funcionariosExcel = new ArrayList<>(); ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); // FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx")); Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx")); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int row = nextRow.getRowNum(); // System.out.println("Row start" + row); Iterator<Cell> cellIterator = nextRow.cellIterator(); Funcionario f = new Funcionario(); Formacao fm = new Formacao(); Idioma id = new Idioma(); int column = 0; while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); column = columnIndex; // System.out.println("Valor" + getCellValue(nextCell)); // System.out.println("Index: " + columnIndex); if (row > 0) { switch (columnIndex) { case 1: f.setArea((String) getCellValue(nextCell)); break; case 2: Date dt = new Date(); if (!getCellValue(nextCell).toString().isEmpty()) { try { dt = DateUtil.getJavaDate((Double) getCellValue(nextCell)); } catch (ClassCastException cce) { SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy"); dt = formatter.parse((String) getCellValue((nextCell))); } ; } f.setDtAdmissao(dt); break; case 3: f.setCargo((String) getCellValue(nextCell)); break; case 4: f.setNome((String) getCellValue(nextCell)); break; case 5: f.setGestor((String) getCellValue(nextCell)); break; case 9: fm.setNivel((String) getCellValue(nextCell)); break; case 10: fm.setCurso((String) getCellValue(nextCell)); break; case 11: fm.setInstituicaoo((String) getCellValue(nextCell)); break; case 12: String typeEnum = (String) getCellValue(nextCell); if (!typeEnum.isEmpty()) { id.setNome(Language.valueOf(typeEnum.trim())); } break; case 13: String typeEnumNivel = (String) getCellValue(nextCell); if (!typeEnumNivel.isEmpty()) { id.setNivel(Nivel.valueOf(typeEnumNivel.trim())); } break; } } } List<Formacao> listFm = new ArrayList<>(); listFm.add(fm); f.setFormacoes(listFm); List<Idioma> listId = new ArrayList<>(); listId.add(id); f.setIdiomas(listId); if (row > 0) { funcionariosExcel.add(f); } } wb.close(); // fl.close(); // for (Funcionario fc : funcionariosExcel) { // System.out.println(fc.getNome()); // } return funcionariosExcel; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsxSpreadsheet(File spreadsheet) { /*/* w w w .ja v a 2 s . c o m*/ Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { XSSFRow row1; currentSpreadsheetFile = new XSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row1 = (XSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row1.cellIterator(); while (cellIterator.hasNext()) { String cellValue = ""; Cell cell = cellIterator.next(); switch (cell.getCellTypeEnum()) { default: cellValue = cell.getCellFormula(); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case BLANK: break; case STRING: break; } values.add(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + " \t\t "); } System.out.println(); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } return values; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;/*from w w w . j av a 2 s . c o m*/
DataTable = new JTable();
myEntries = new ArrayList<>();
File filecheck = new File(csvFile);
if (filecheck.isAbsolute()) {
String[] left_right_side_of_dot = csvFile.split("\\.");
String file_extension = left_right_side_of_dot[left_right_side_of_dot.length - 1];
switch (file_extension) {
case "xls":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception e) {
System.out.println("Error occurred while reading XLS file: " + e.toString());
}
break;
case "xlsx":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception ex) {
System.out.print("Exception during XLSX import: " + ex.toString());
}
break;
case "csv":
try {
CSVFileReader = new CSVReader(new FileReader(DataFile), ',', '"', '\0');
myEntries = CSVFileReader.readAll();
} catch (Exception e) {
}
}
columnnames = (String[]) myEntries.get(0);
DefaultTableModel tableModel = new DefaultTableModel(columnnames, myEntries.size() - 1);
rowcount = tableModel.getRowCount();
this.number_of_records = rowcount;
for (int x = 0; x < rowcount + 1; x++) {
int columnnumber = 0;
if (x > 0) {
for (String thiscellvalue : (String[]) myEntries.get(x)) {
tableModel.setValueAt(thiscellvalue, x - 1, columnnumber);
columnnumber++;
}
}
}
DataTable = new JTable(tableModel);
int number_of_rows = DataTable.getRowCount();
if (number_of_rows < 20) {
DataTable.setPreferredScrollableViewportSize(
new Dimension(1200, number_of_rows * DataTable.getRowHeight()));
}
} else {
columnnames[0] = "Stored URL List:" + csvFile;
DefaultTableModel tableModel = new DefaultTableModel(columnnames, 0);
DataTable = new JTable(tableModel);
DataTable.getColumnModel().getColumn(0).setPreferredWidth(200);
DataTable.setPreferredScrollableViewportSize(new Dimension(20, 0));
}
}
From source file:bs.compra.proceso.ActualizarPreciosCostoBean.java
public void procesarArchivoExcel() { try {// w w w . j a va 2s . co m FileInputStream file = new FileInputStream(new File(pathArchivoExcel)); String[] split = pathArchivoExcel.split("\\."); String extension = split[split.length - 1].toLowerCase(); if (extension.equals("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else if (extension.equals("xls")) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else { JsfUtil.addErrorMessage( "Formato de archivo incorrecto. El archivo debe tener extensin xls o xlsx"); } } catch (IOException ex) { Logger.getLogger(ActualizarPreciosCostoBean.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:businessCharts.readExcell.java
public void initExcel(String fileName) throws Exception { fis = new FileInputStream(new File("C:\\Users\\Public\\Documents\\Dashboard_External\\" + fileName)); workbook = new XSSFWorkbook(fis); }
From source file:CDatos.Excel.java
/** * Constructor/*from ww w .ja v a 2 s . co m*/ * @param path * @param hoja */ public Excel(String path, int hoja) { this.hoja = hoja; try { //Se crea el FileInputStream a partir del path this.file = new FileInputStream(new File(path)); //Se crea el workbook a partir del FileInputStream this.workbook = new XSSFWorkbook(file); } catch (IOException e) { System.out.println(e.getMessage()); } }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcelTest.java
License:Apache License
private String buildProjektstrukturPlan(Szenario szenario) throws IOException { ZipOutputBuilder zipBuilder = new ZipOutputBuilder(); AnwenderloesungRenderingContainer container = new AnwenderloesungRenderingContainer("model", szenario, new SzenarioUserData(), Arrays.asList("de"), true, true, true, true); projektstrukturplanGeneratorExcel.addProjektstrukturPlan("workbreakdownstructure", container, zipBuilder, new LocalizationEngine(projektstrukturplanGeneratorExcel.translationRepository, "model", "de")); byte[] result = zipBuilder.getResult(); ZipInputStream zipInputStream = new ZipInputStream(new ByteArrayInputStream(result)); ZipEntry nextEntry;/*from ww w . ja va 2s . com*/ while ((nextEntry = zipInputStream.getNextEntry()) != null) { assertEquals(nextEntry.getName(), "workbreakdownstructure/de/Workbreakdownstructure_de.xlsx"); byte[] byteArray = zipUtil.readZipEntry(zipInputStream); java.io.FileOutputStream fileOutputStream = new java.io.FileOutputStream( "target/projektstrukturplan.xlsx"); fileOutputStream.write(byteArray); fileOutputStream.close(); assertNotNull(byteArray); XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(byteArray)); XSSFSheet sheet = wb.getSheetAt(0); ByteArrayOutputStream out2 = new ByteArrayOutputStream(); PrintStream out = new PrintStream(out2); for (Row row : sheet) { out.print("|"); // assertTrue("" + row.getLastCellNum(), row.getLastCellNum() < 3); for (Cell cell : row) { out.print(cell.getStringCellValue()); out.print("|"); } out.println(""); } return new String(out2.toByteArray()); } throw new AssertionError("workbook not found"); }
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException/*from w w w. j av a2 s . c o m*/ * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:cht.Parser.java
License:Apache License
public static void main(String[] args) throws IOException { // TODO get from google drive boolean isUnicode = false; boolean isRemoveInputFileOnComplete = false; int rowNum;/* w ww. j a va 2 s . com*/ int colNum; Gson gson = new GsonBuilder().setPrettyPrinting().create(); Properties prop = new Properties(); try { prop.load(new FileInputStream("config.txt")); } catch (IOException ex) { ex.printStackTrace(); } String inputFilePath = prop.getProperty("inputFile"); String outputDirectory = prop.getProperty("outputDirectory"); System.out.println(outputDirectory); // optional String unicode = prop.getProperty("unicode"); String removeInputFileOnComplete = prop.getProperty("removeInputFileOnComplete"); inputFilePath = inputFilePath.trim(); outputDirectory = outputDirectory.trim(); if (unicode != null) { isUnicode = Boolean.parseBoolean(unicode.trim()); } if (removeInputFileOnComplete != null) { isRemoveInputFileOnComplete = Boolean.parseBoolean(removeInputFileOnComplete.trim()); } Writer out = null; FileInputStream in = null; final String newLine = System.getProperty("line.separator").toString(); final String separator = File.separator; try { in = new FileInputStream(inputFilePath); Workbook workbook = new XSSFWorkbook(in); Sheet sheet = workbook.getSheetAt(0); rowNum = sheet.getLastRowNum() + 1; colNum = sheet.getRow(0).getPhysicalNumberOfCells(); for (int j = 1; j < colNum; ++j) { String outputFilename = sheet.getRow(0).getCell(j).getStringCellValue(); // guess directory int slash = outputFilename.indexOf('/'); if (slash != -1) { // has directory outputFilename = outputFilename.substring(0, slash) + separator + outputFilename.substring(slash + 1); } String outputPath = FilenameUtils.concat(outputDirectory, outputFilename); System.out.println("--Writing " + outputPath); out = new OutputStreamWriter(new FileOutputStream(outputPath), "UTF-8"); TreeMap<String, Object> map = new TreeMap<String, Object>(); for (int i = 1; i < rowNum; i++) { try { String key = sheet.getRow(i).getCell(0).getStringCellValue(); //String value = ""; Cell tmp = sheet.getRow(i).getCell(j); if (tmp != null) { // not empty string! value = sheet.getRow(i).getCell(j).getStringCellValue(); } if (!key.equals("") && !key.startsWith("#") && !key.startsWith(".")) { value = isUnicode ? StringEscapeUtils.escapeJava(value) : value; int firstdot = key.indexOf("."); String keyName, keyAttribute; if (firstdot > 0) {// a.b.c.d keyName = key.substring(0, firstdot); // a keyAttribute = key.substring(firstdot + 1); // b.c.d TreeMap oldhash = null; Object old = null; if (map.get(keyName) != null) { old = map.get(keyName); if (old instanceof TreeMap == false) { System.out.println("different type of key:" + key); continue; } oldhash = (TreeMap) old; } else { oldhash = new TreeMap(); } int firstdot2 = keyAttribute.indexOf("."); String rootName, childName; if (firstdot2 > 0) {// c, d.f --> d, f rootName = keyAttribute.substring(0, firstdot2); childName = keyAttribute.substring(firstdot2 + 1); } else {// c, d -> d, null rootName = keyAttribute; childName = null; } TreeMap<String, Object> object = myPut(oldhash, rootName, childName); map.put(keyName, object); } else {// c, d -> d, null keyName = key; keyAttribute = null; // simple string mode map.put(key, value); } } } catch (Exception e) { // just ingore empty rows } } String json = gson.toJson(map); // output json out.write(json + newLine); out.close(); } in.close(); System.out.println("\n---Complete!---"); System.out.println("Read input file from " + inputFilePath); System.out.println(colNum - 1 + " output files ate generated at " + outputDirectory); System.out.println(rowNum + " records are generated for each output file."); System.out.println("output file is ecoded as unicode? " + (isUnicode ? "yes" : "no")); if (isRemoveInputFileOnComplete) { File input = new File(inputFilePath); input.deleteOnExit(); System.out.println("Deleted " + inputFilePath); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (in != null) { in.close(); } } }
From source file:clases.excel.EXCELreader.java
private void prepareList(String path) { excelString = ""; listaEXCEL = new ArrayList<>(); int sw = 0;//0 no,1 si, 2 ready XSSFWorkbook workbook;/*from www .j a v a 2 s .co m*/ try { //Create Workbook instance holding reference to .xlsx file workbook = new XSSFWorkbook(new FileInputStream(new File(path))); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String[] linea = new String[parametros]; Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); // if (rowcont2 ==16) { // excelString += ++rowcont + "***"; int rowcont = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //JOptionPane.showMessageDialog(null, "Tipo *" + cell.getCellType() + "*"); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue();//String.format("%d", d.intValue()) excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; case Cell.CELL_TYPE_STRING: // JOptionPane.showMessageDialog(null, "String *" + cell.getStringCellValue().trim() + "*"); if (sw == 0) { if (cell.getStringCellValue().trim().equals(init_detalle)) { sw = 1; } } //leyendo tabla if (sw == 2) { excelString += cell.getStringCellValue() + "\t"; linea[rowcont++] = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BLANK: //si estaba leyendo tabla, pero encuentra vacio // termina de leer la tabla if (rowcont == 0 && sw == 2) { sw = 4; } break; case Cell.CELL_TYPE_FORMULA: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue(); excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; } //si ya es 1 termina el ciclo, para iniciar el detalle // en la siguiente linea if (sw == 1) { sw = 2; break; } //si ya no leera mas tabla, termina de leer la row if (sw == 4) { break; } } //guarda si esta leyendo tabla if (sw == 2 && linea[0] != null) { listaEXCEL.add(linea); excelString += "\n"; } //termino de leer la tabla, cierra todo if (sw == 4) { break; } // } } int as = 0; } catch (Exception e) { e.printStackTrace(); } }