List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:Algorithm.Method2.java
public String[] MethodTest(String Path) { StringBuffer keyword1 = new StringBuffer(); try {//from w w w . j a va 2s . c om /** * ??excel * src * map? * result */ String srcFilePath = Path; String mapFilePath = "??.xlsx"; String newFilePath = Path.replace("???.xlsx", "method2.xlsx"); FileInputStream srcFile = new FileInputStream(new File(srcFilePath)); FileInputStream mapFile = new FileInputStream(new File(mapFilePath)); XSSFWorkbook srcWorkbook = new XSSFWorkbook(srcFile); XSSFWorkbook mapWorkbook = new XSSFWorkbook(mapFile); XSSFWorkbook newWorkbook = new XSSFWorkbook(); XSSFSheet srcSheet = srcWorkbook.getSheetAt(0); XSSFSheet mapSheet = mapWorkbook.getSheetAt(0); XSSFSheet newSheet = newWorkbook.createSheet("test"); Iterator<Row> srcRowIterator = srcSheet.iterator(); Iterator<Row> mapRowIterator = mapSheet.iterator(); //map.xlst???mapList ArrayList mapList = new ArrayList(); while (mapRowIterator.hasNext()) { Row row = mapRowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String test = cell.getStringCellValue(); mapList.add(test); } } //???mapArray,? int mapListLength = mapList.size(); String[] mapArray = (String[]) mapList.toArray(new String[mapListLength]); //??mapResult,mapArray?src //,mapArray[i]src,mapResult[i]1?0 int[] mapResult = new int[mapListLength]; for (int i = 0; i < mapListLength; i++) { mapResult[i] = 0; } //?src.xlsx,?mapArray while (srcRowIterator.hasNext()) { Row row = srcRowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String test = cell.getStringCellValue().trim(); for (int i = 0; i < mapListLength; i++) { if (test.equals(mapArray[i])) mapResult[i] = 1; } } } //?mapResult,mapArrayresult int newRowNum = 0; for (int i = mapListLength - 1; i >= 0; i--) { if (mapResult[i] == 1) { Row newRow = newSheet.createRow(newRowNum++); Cell newCell = newRow.createCell(0); String test = mapArray[i]; keyword1.append(test + " "); newCell.setCellValue(test); } } srcFile.close(); mapFile.close(); //FileOutputStream fileOut = new FileOutputStream(new File(newFilePath)); // newWorkbook.write(fileOut); // fileOut.close(); // System.out.print("method2"); } catch (Exception e) { System.out.println(e); } return keyword1.toString().split(" "); }
From source file:Algorithm.SegmentationAndNounFilter.java
public String[] SegmentationNounFilter(String filepath, String filename) { try {/*from w w w . ja va2 s .c o m*/ String mingciPath = filepath.replace(filename, "???.xlsx"); ICTCLAS50 testICTCLAS50 = new ICTCLAS50(); String argu = "."; if (testICTCLAS50.ICTCLAS_Init(argu.getBytes("GB2312")) == false) { System.out.println("Init Fail"); } else { System.out.println("Init Succeed!"); } StringBuffer input = new StringBuffer(); FileInputStream file = new FileInputStream(new File(filepath)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet1 = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Row rowNew =sheetNew.createRow(rowNumNew++); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // Cell cellNew =rowNew.createCell(cellNumNew++); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: break; case Cell.CELL_TYPE_STRING: input.append(cell.getStringCellValue()); } } //System.out.println(""); } //? byte nativeBytes[] = testICTCLAS50.ICTCLAS_ParagraphProcess(input.toString().getBytes("GB2312"), 0, 1); String nativeStr = new String(nativeBytes, 0, nativeBytes.length, "GB2312"); //xlsx XSSFWorkbook workbookNew = new XSSFWorkbook(); XSSFSheet sheetNew = workbookNew.createSheet("test"); int rowNum = 0; // WritableWorkbook book = Workbook.createWorkbook(new File("n.xls")); // WritableSheet sheet = book.createSheet("num1", 0); Scanner in = new Scanner(nativeStr); int i = 0;// while (in.hasNext()) { String ss = in.next(); Pattern pattern = Pattern.compile("(.+?)/n.*"); Matcher matcher = pattern.matcher(ss); if (matcher.find() && matcher.group(1).length() > 1 && !isDigit(matcher.group(1))) { //label = new jxl.write.Label(0, i, matcher.group(1));//? //sheet.addCell(label); Row rowNew = sheetNew.createRow(rowNum++); Cell cellNew = rowNew.createCell(0); cellNew.setCellValue(matcher.group(1)); //i++; } } // book.write(); // book.close(); FileOutputStream fileOut = new FileOutputStream(new File(mingciPath));//new file workbookNew.write(fileOut); fileOut.close(); //?? file.close(); testICTCLAS50.ICTCLAS_Exit(); } catch (Exception ex) { } return null; }
From source file:amazonreviews.AmazonReviews.java
public static void main(String[] args) throws IOException { // TODO Auto-generated method stub //new AmazonReviews.filea("B00I8BIBCW"); String s1 = "B002RL9CYK"; reviews = new ArrayList<String>(); reviewsDate = new ArrayList<String>(); reviewsStar = new ArrayList<String>(); XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Employee Data"); //This data needs to be written (Object[]) Map<String, Object[]> data = new TreeMap<String, Object[]>(); data.put("0", new Object[] { "Review Text", "Review Date", "Review Stars" }); for (int i = 1; i <= 100; i++) { GetReviews(s1, Integer.toString(i)); }//from w w w.j av a2 s . c om for (int i = 0; i < reviews.size(); i++) { data.put(Integer.toString(i + 1), new Object[] { reviews.get(i), reviewsDate.get(i), reviewsStar.get(i) }); } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { XSSFRow row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(name + ".xlsx")); workbook.write(out); out.close(); System.out.println("howtodoinjava_demo.xlsx written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } }
From source file:app.DCReportsTab.java
License:Open Source License
private void exportButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportButtonActionPerformed //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Employee Data"); //This data needs to be written (Object[]) Map<String, Object[]> data = new TreeMap<String, Object[]>(); PreparedStatement pst;// ww w.j a v a2 s.c o m Connection con; String drNumber = ""; String dateToday = ""; String outlet = ""; String quantity = ""; String deliveryDate = ""; String totalAmount = ""; TableModel model = deliveryReceiptsTable.getModel(); //Table model if (model.getRowCount() != 0) { Row row = sheet.createRow(2); Row headerRow = sheet.createRow(0); //Create row at line 0 for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name } for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row for (int cols = 0; cols < deliveryReceiptsTable.getColumnCount(); cols++) { //For each table column row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value } //Set the row to the next one in the sequence row = sheet.createRow((rows + 3)); } try { //Write the workbook in file system java.util.Date date = new java.util.Date(); FileOutputStream out = new FileOutputStream( new File("C:/Users/Jenelle/Desktop/Report" + date.getTime() + ".xlsx")); workbook.write(out); out.close(); System.out.println("Written successfully on disk."); JOptionPane.showMessageDialog(null, "Export success!", "Success", JOptionPane.PLAIN_MESSAGE); } catch (Exception e) { e.printStackTrace(); } } else { JOptionPane.showMessageDialog(null, "Please input data to table.", "Error", JOptionPane.ERROR_MESSAGE); } }
From source file:application.ExportTool.java
private void convertToExcel(ResultSet rs) throws SQLException, FileNotFoundException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("employe db"); XSSFRow row = sheet.createRow(0);//from w ww .ja va2 s . c o m // XSSFCell cell; // rowhead.createCell((short) 0).setCellValue("Roll No"); // rowhead.createCell((short) 0).setCellValue("Roll No"); // PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; ResultSetMetaData meta = rs.getMetaData(); int numberOfColumns = meta.getColumnCount(); // String dataHeaders = "\"" + meta.getColumnName(1) + "\"" ; int i = 1; for (i = 0; i < numberOfColumns; i++) { System.out.println(meta.getColumnName(i + 1).toString()); row.createCell(i).setCellValue(meta.getColumnName(i + 1).toString()); } int index = 1; int j = 1; while (rs.next()) { row = sheet.createRow(index); for (j = 0; j < numberOfColumns; j++) { System.out.println(rs.getString(j + 1)); row.createCell(j).setCellValue(rs.getString(j + 1)); } j = 1; index++; } try { FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx")); wb.write(out); out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("Data is saved in excel file."); }
From source file:application.ExportTool.java
public void convertToExcel(TableView tv, ObservableList<ObservableList> data, String filepath) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("employe db"); XSSFRow row = sheet.createRow(0);//from w w w. j av a2 s. c om int numberOfColumns = tv.getColumns().size(); int numberOfRows = tv.getItems().size(); TableColumn col; for (int i = 0; i < numberOfColumns; i++) { col = (TableColumn) tv.getColumns().get(i); System.out.println("dddd" + col.getText() + "pppp"); row.createCell(i).setCellValue(col.getText()); } int index = 1; int j = 0; for (ObservableList<String> tmp : data) { row = sheet.createRow(index); for (String cell : tmp) { System.out.println("YYYYY" + cell.toString()); //String callstring = new String(cell.toString()); row.createCell(j).setCellValue(cell.toString()); j++; } j = 0; index++; } FileOutputStream out = new FileOutputStream(new File(filepath)); wb.write(out); out.close(); }
From source file:at.mukprojects.exclycore.dao.ExclyCoreWriterTest.java
License:Open Source License
/** * Tests the XLSXWriter functions.//from w ww .j ava 2 s . c o m */ @Test public void testXLSXWriter() throws Exception { XSSFWorkbook workbook = writer.createWorkbook(outputFile, false); workbook.createSheet("Sheet New"); writer.closeWorkbook(); assertTrue(outputTestFile.exists()); workbook = writer.createWorkbook(outputFile, true); workbook.createSheet("Sheet Add"); writer.closeWorkbook(); assertTrue(outputTestFile.exists()); FileInputStream inputStream = new FileInputStream(outputTestFile); workbook = new XSSFWorkbook(inputStream); assertTrue(workbook.getNumberOfSheets() == 2); workbook.close(); }
From source file:automatedhgl.AutomatedHGL.java
public static void main(String[] args) { try {//from ww w. ja va 2 s. c om FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME)); //create workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(excelFile); //get first desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //create workbook instance to output excel file XSSFWorkbook workbookHGL = new XSSFWorkbook(); //create sheet in output excel file XSSFSheet sheetHGL = workbookHGL.createSheet("HGL"); //iterate through each row one by one Iterator<Row> rowiterator = sheet.iterator(); while (rowiterator.hasNext()) { Row row = rowiterator.next(); //for each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (row.getRowNum() > 7 && count < 23) //to filter column headings { //check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: count++; //assign get value to correct variable if (count == 1) { InletStr = cell.getNumericCellValue(); } else if (count == 2) { OutWSE = cell.getNumericCellValue(); } System.out.print(cell.getNumericCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_STRING: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getStringCellValue();}*/ System.out.print(cell.getStringCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_FORMULA: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/ System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") "); break; } } else { count = 0; //reset the count at the end of the row } } System.out.println("return"); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Output Excel file XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Datatypes in Java"); Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 }, { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 }, { "String", "Non-Primitive", "No fixed size" } }; int rowNum = 0; System.out.println("Creating excel"); for (Object[] datatype : datatypes) { Row row = sheet.createRow(rowNum++); int colNum = 0; for (Object field : datatype) { Cell cell = row.createCell(colNum++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } try { FileOutputStream outputStream = new FileOutputStream(FILE_NAME); workbook.write(outputStream); workbook.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.print(InletStr + " "); System.out.print(OutWSE + " "); System.out.println("HGL Done"); }
From source file:binaryproject.graphics.Juego.java
public void escribirExcel() throws FileNotFoundException, IOException, InvalidFormatException { long exitTime = System.currentTimeMillis(); if (player != null) { // Actualiza el archivo de jugadores con la informacion que habia antes de cerrarse actualizarAchivoJugadoreS();// w w w . java 2s . c o m // Actualiza el archivo del registro de sesiones EscritorDArchivos writer = new EscritorDArchivos(rutaArchivos + "logs.txt", true); String justAnotherLogForAnExistingPlayer = ""; if (hasPlayerPastLogs(player.getNombre())) { ArrayList<Integer> logHistoryIndex = getPlayerSesionDataIndexes(player.getNombre()); boolean[] loggedQuestions = new boolean[3]; int i = 0; while (!loggedQuestions[0] && i < logHistoryIndex.size()) { int puntaje = Integer.parseInt(playerLogs.get(logHistoryIndex.get(i))[11]); if (puntaje > 3) { loggedQuestions[0] = true; } i++; } i = 0; while (!loggedQuestions[1] && i < logHistoryIndex.size()) { int puntaje = Integer.parseInt(playerLogs.get(logHistoryIndex.get(i))[21]); if (puntaje > 3) { loggedQuestions[1] = true; } i++; } i = 0; while (!loggedQuestions[2] && i < logHistoryIndex.size()) { int puntaje = Integer.parseInt(playerLogs.get(logHistoryIndex.get(i))[31]); if (puntaje > 3) { loggedQuestions[2] = true; } i++; } justAnotherLogForAnExistingPlayer += player.getID() + ";" + player.getNombre() + ";" + (playerLogs.size() + 1) + ";" + player.getLastTimeLogin() + ";" + exitTime + ";" + (exitTime - player.getLastTimeLogin()) + ";"; if (!loggedQuestions[0]) { justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(1); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(1) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(0)[0] + ";" + player.getTiempos().get(0)[1] + ";" + player.getTiempos().get(0)[2] + ";"; } else { justAnotherLogForAnExistingPlayer += "0;0;0;0;0;0;"; justAnotherLogForAnExistingPlayer += "0;0;0;"; } //Agregar STN1 long STN = 0; if (logHistoryIndex.size() != 0 && !loggedQuestions[0]) { for (int j = 0; j < logHistoryIndex.size(); j++) { STN += Long.parseLong(playerLogs.get(logHistoryIndex.get(j))[14]); } STN += player.getTiempos().get(0)[2]; if (STN != 0) { player.changeWritenState(0, true); } } justAnotherLogForAnExistingPlayer += STN + ";"; if (!loggedQuestions[1]) { justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(2); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(2) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(1)[0] + ";" + player.getTiempos().get(1)[1] + ";" + player.getTiempos().get(1)[2] + ";"; } else { justAnotherLogForAnExistingPlayer += "0;0;0;0;0;0;"; } //Agregar SNT2 STN = 0; if (logHistoryIndex.size() != 0 && !loggedQuestions[1]) { for (int j = 0; j < logHistoryIndex.size(); j++) { STN += Long.parseLong(playerLogs.get(logHistoryIndex.get(j))[24]); } STN += player.getTiempos().get(1)[2]; if (STN != 0) { player.changeWritenState(1, true); } } justAnotherLogForAnExistingPlayer += STN + ";"; if (!loggedQuestions[2]) { justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(3); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(3) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(2)[0] + ";" + player.getTiempos().get(2)[1] + ";" + player.getTiempos().get(2)[2] + ";"; } else { justAnotherLogForAnExistingPlayer += "0;0;0;0;0;0;"; } //Agregar SNT3 STN = 0; if (logHistoryIndex.size() != 0 && !loggedQuestions[2]) { for (int j = 0; j < logHistoryIndex.size(); j++) { STN += Long.parseLong(playerLogs.get(logHistoryIndex.get(j))[34]); } STN += player.getTiempos().get(2)[2]; if (STN != 0) { player.changeWritenState(2, true); } } justAnotherLogForAnExistingPlayer += STN + ";"; //Cargar el CT Total justAnotherLogForAnExistingPlayer += player.getScore() + ";"; long spentTime = 0; for (int j = 0; j < logHistoryIndex.size(); j++) { spentTime += Long.parseLong(playerLogs.get(logHistoryIndex.get(j))[5]); } justAnotherLogForAnExistingPlayer += (spentTime + (exitTime - player.getLastTimeLogin())); } else { justAnotherLogForAnExistingPlayer += player.getID() + ";" + player.getNombre() + ";" + (playerLogs.size() + 1) + ";" + player.getLastTimeLogin() + ";" + exitTime + ";" + (exitTime - player.getLastTimeLogin()) + ";"; //Terminar de rellenar con el resto de informacion de toda la sesion justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(1); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(1) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(0)[0] + ";" + player.getTiempos().get(0)[1] + ";" + player.getTiempos().get(0)[2] + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(0)[2] + ";"; //STN1 justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(2); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(2) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(1)[0] + ";" + player.getTiempos().get(1)[1] + ";" + player.getTiempos().get(1)[2] + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(1)[2] + ";"; //STN2 justAnotherLogForAnExistingPlayer += getCorrectQuestionsIndividually(3); justAnotherLogForAnExistingPlayer += getAmmountOfCorrectQuestions(3) + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(2)[0] + ";" + player.getTiempos().get(2)[1] + ";" + player.getTiempos().get(2)[2] + ";"; justAnotherLogForAnExistingPlayer += player.getTiempos().get(2)[2] + ";"; //STN3 justAnotherLogForAnExistingPlayer += player.getScore() + ";"; justAnotherLogForAnExistingPlayer += exitTime - player.getLastTimeLogin(); } writer.escribir(justAnotherLogForAnExistingPlayer); writer.cerrar(); //////////////////////////////////////////////////////////////////// ///////////Pasar la infrmacion a archivo excel////////////////////// //////////////////////////////////////////////////////////////////// LectorDArchivos reader = new LectorDArchivos(rutaArchivos + "logs.txt"); XSSFWorkbook libro = new XSSFWorkbook(); XSSFSheet hoja = libro.createSheet("Test"); XSSFRow row; String[] informacion; String cellValue; int rowNumber = 0; reader.leerLinea(); while (reader.getLineaActual() != null) { informacion = reader.getLineaActual().split(";"); row = hoja.createRow(rowNumber); for (int i = 0; i < informacion.length; i++) { cellValue = informacion[i]; row.createCell(i).setCellValue(cellValue); } reader.leerLinea(); rowNumber++; } new File(rutaArchivos + "estadisticas.xls").delete(); FileOutputStream archivoFinal = new FileOutputStream(new File(rutaArchivos + "estadisticas.xls")); libro.write(archivoFinal); archivoFinal.close(); } }
From source file:br.com.sose.utils.BigGridDemo_temp.java
License:Apache License
public static void main(String[] args) throws Exception { // Step 1. Create a template file. Setup sheets and workbook-level objects such as // cell styles, number formats, etc. XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Big Grid"); Map<String, XSSFCellStyle> styles = createStyles(wb); //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); //save the template FileOutputStream os = new FileOutputStream("template.xlsx"); wb.write(os);//from ww w. j a va2s .c om os.close(); //Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); //Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream("big-grid.xlsx"); substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); out.close(); }