List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;/*from w w w . jav a 2 s . c om*/ String ruthPath = null; try { if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) { // Se crea el libro XSSFWorkbook libro = new XSSFWorkbook(); // Se crea una hoja dentro del libro XSSFSheet sheetD = libro.createSheet(); //Obtener lista de alumnos del curso List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso()); //Leer datos y colocarlos en la hoja int f = 0; //Guardar datos en celda for (SieniAlumno alumno : alumnosEval) { // Se crea una fila dentro de la hoja XSSFRow fila = sheetD.createRow(f); f++; // Se crea las celdas dentro de la fila XSSFCell celdaCarnet = fila.createCell((short) 0); XSSFCell celdaAlumno = fila.createCell((short) 1); XSSFCell celdaNota = fila.createCell((short) 2); //Colocar valor en celda celdaCarnet.setCellValue(alumno.getAlCarnet()); celdaAlumno.setCellValue(alumno.getNombreCompleto()); celdaNota.setCellValue((double) 0.00); } //Encabezados desde plantilla InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx"); StreamedContent plantillaXLS = new DefaultStreamedContent(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx"); XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream()); XSSFSheet sheetP = plantilla.getSheetAt(0); //Filas que ocupa el encabezado de plantilla int encabezado = 3; //Quitar encabezado y desplazar Datos sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado); //Copiar contenido de plantilla a la hoja del reporte int inicio = 0; for (int row = 0; row < encabezado; row++) { copyRow(sheetP, sheetD, row, inicio); inicio++; } //Combinar las columnas al igual que la plantilla for (int m = 0; m < sheetP.getNumMergedRegions(); m++) { CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy(); sheetD.addMergedRegion(cellRangeAddress); } //Evaluacion XSSFCell celdaEval = sheetD.getRow(0).getCell(1); celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre()); // Se salva el libro. FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx"); libro.write(elFichero); elFichero.close(); //Leer libro para descarga FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx")); filePlantilla = new DefaultStreamedContent(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx"); } else { new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR); } } catch (Exception exc) { new ValidationPojo().printMsj( "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath, FacesMessage.SEVERITY_ERROR); } return filePlantilla; }
From source file:tan.jam.jsf.OrignalFileDownloadBean.java
public void downloadHandler(ActionEvent e) { System.out.println("download click handler"); try {//from ww w . ja v a2 s. co m InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/demo/file/Table.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(stream)); XSSFSheet sheet = wb.getSheetAt(0); wb.setForceFormulaRecalculation(true); /* */ String name; String acc; HashSet OnlineTransferToAccounts = new HashSet(); int RowForOnlineTransferT0 = 105; int RowForRecurringDailyPayment = 105; List<String> LenderDescription = new ArrayList<>(); List<String> LenderDate = new ArrayList<>(); List<Double> LenderAmount = new ArrayList<>(); List<Double> UniqueDailypaymentkeywordAmount = new ArrayList<>(); List<String> UniqueDailypaymentkeywordDate = new ArrayList<>(); List<String> UniqueDailypaymentkeywordDescription = new ArrayList<>(); for (int a = 0; a < companies.size(); a++) { Company c = companies.get(a); name = c.getCompanyName(); acc = c.getAccountNumber(); String month = c.getDate().toLowerCase(); int R = 3; if (month.startsWith(("jan"))) { R = 3; } else if (month.startsWith("feb")) { R = 4; } else if (month.startsWith("mar")) { R = 5; } else if (month.startsWith("apr")) { R = 6; } else if (month.startsWith("may")) { R = 7; } else if (month.startsWith("jun")) { R = 8; } else if (month.startsWith("jul")) { R = 9; } else if (month.startsWith("aug")) { R = 10; } else if (month.startsWith("sep")) { R = 11; } else if (month.startsWith("oct")) { R = 12; } else if (month.startsWith("nov")) { R = 13; } else if (month.startsWith("dec")) { R = 14; } else if (month.startsWith("02") | month.startsWith("2")) { R = 4; } else if (month.startsWith("03") | month.startsWith("3")) { R = 5; } else if (month.startsWith("04") | month.startsWith("4")) { R = 6; } else if (month.startsWith("05") | month.startsWith("5")) { R = 7; } else if (month.startsWith("06") | month.startsWith("6")) { R = 8; } else if (month.startsWith("07") | month.startsWith("7")) { R = 9; } else if (month.startsWith("08") | month.startsWith("8")) { R = 10; } else if (month.startsWith("09") | month.startsWith("9")) { R = 11; } else if (month.startsWith("10") | month.startsWith("10")) { R = 12; } else if (month.toUpperCase().startsWith("11")) { R = 13; } else if (month.toUpperCase().startsWith("12")) { R = 14; } ////%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%//// sheet.getRow(1).getCell(1).setCellValue("Bank Account**" + acc); if (a == 0) { sheet.getRow(1).getCell(7).setCellValue(name + "**" + acc); sheet.getRow(1).getCell(12).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(7) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); // int Mov=Shifting.FindShift(wb, sheet); sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(13).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues()); // int Onl=Shifting.Online(wb, sheet); } else { int Decision = 0; String Name = name + "**" + acc; for (int s = 0; s < Shifting.FindShift(wb, sheet) + 1; s++) { if (sheet.getRow(1).getCell(7 + s).getStringCellValue().equals(Name)) { Decision = 1; sheet.getRow(R).getCell(7 + s) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); } } if (Decision == 0) { int Mov = Shifting.FindShift(wb, sheet); Shifting.InsertColumn(wb, sheet); sheet.getRow(1).getCell(8 + Mov).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(8 + Mov) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); } int selectcolumn = Shifting.FindShift(wb, sheet) + 12; if (sheet.getRow(1).getCell(selectcolumn).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn).getStringCellValue().equals(Name)) { } else if (sheet.getRow(1).getCell(selectcolumn + 6).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 6; } else if (sheet.getRow(1).getCell(selectcolumn + 12).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 12; } else if (sheet.getRow(1).getCell(selectcolumn + 18).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 18; } else if (sheet.getRow(1).getCell(selectcolumn + 24).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 24; } else if (sheet.getRow(1).getCell(selectcolumn + 30).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 30; } else if (sheet.getRow(1).getCell(selectcolumn + 36).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 36; } else if (sheet.getRow(1).getCell(selectcolumn + 42).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 42; } sheet.getRow(1).getCell(selectcolumn).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(selectcolumn).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(selectcolumn + 1).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(selectcolumn + 2).setCellValue(c.getNoOfNegativeValues()); } /* // int Mov=Shifting.FindShift(wb, sheet); sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(13).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues()); // int Onl=Shifting.Online(wb, sheet); */ ///%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%///// for (int b = 0; b < c.getOnlineTransferToDate().size(); b++) { sheet.getRow(RowForOnlineTransferT0).getCell(9) .setCellValue(c.getOnlineTransferToDate().get(b)); sheet.getRow(RowForOnlineTransferT0).getCell(10) .setCellValue(c.getOnlineTransferToDescription().get(b)); sheet.getRow(RowForOnlineTransferT0).getCell(12) .setCellValue(c.getOnlineTransferToAmount().get(b)); RowForOnlineTransferT0++; } for (int L = 0; L < c.getKeywordAmount().size(); L++) { int index = 0; if (LenderDescription.contains(c.getKeywordDescription().get(L))) { index = LenderDescription.indexOf(c.getKeywordDescription().get(L)); LenderDate.set(index, c.getKeywordDate().get(L)); LenderAmount.set(index, c.getKeywordAmount().get(L)); } else { LenderDescription.add(c.getKeywordDescription().get(L)); LenderDate.add(c.getKeywordDate().get(L)); LenderAmount.add(c.getKeywordAmount().get(L)); } } for (int TT = 0; TT < c.getOnlineTransferToCheckNoDuplicateRemoved().size(); TT++) { OnlineTransferToAccounts.add(c.getOnlineTransferToCheckNoDuplicateRemoved().get(TT)); } if (!name.equals("BlackHills")) { for (int U = 0; U < c.getDailypaymentKeywordDescription().size(); U++) { sheet.getRow(RowForRecurringDailyPayment).getCell(14) .setCellValue(c.getDailypaymentKeywordDate().get(U)); sheet.getRow(RowForRecurringDailyPayment).getCell(15) .setCellValue(c.getDailypaymentKeywordDescription().get(U)); sheet.getRow(RowForRecurringDailyPayment).getCell(17) .setCellValue(c.getDailypaymentKeywordAmount().get(U)); RowForRecurringDailyPayment++; if (!UniqueDailypaymentkeywordDescription .contains(c.getDailypaymentKeywordDescription().get(U))) { UniqueDailypaymentkeywordAmount.add(c.getDailypaymentKeywordAmount().get(U)); UniqueDailypaymentkeywordDate.add(c.getDailypaymentKeywordDate().get(U)); UniqueDailypaymentkeywordDescription.add(c.getDailypaymentKeywordDescription().get(U)); } } } } for (int LL = 0; LL < LenderDescription.size(); LL++) if (25 + LL < 31) { { // if(25+LL > 29) { //Shifting.InsertRow(wb, sheet,25+LL,26+LL); } sheet.getRow(LL + 25).getCell(1).setCellValue(LenderDate.get(LL)); sheet.getRow(LL + 25).getCell(2).setCellValue(LenderDescription.get(LL)); sheet.getRow(LL + 25).getCell(5).setCellValue(LenderAmount.get(LL)); } } List<String> OnlineTransferToUniqueAccounts = new ArrayList<>(OnlineTransferToAccounts); for (int TT = 0; TT < OnlineTransferToUniqueAccounts.size(); TT++) { int Mov = Shifting.FindShift(wb, sheet); Shifting.InsertColumn(wb, sheet); sheet.getRow(1).getCell(8 + Mov).setCellValue("**" + OnlineTransferToUniqueAccounts.get(TT)); } //************************** Daily Payment portion ******************************************// for (int D = 0; D < UniqueDailypaymentkeywordDescription.size(); D++) { for (int r = 25; r < 31; r++) { if (sheet.getRow(r).getCell(2).getCellType() == 3) { sheet.getRow(r).getCell(1).setCellValue(UniqueDailypaymentkeywordDate.get(D)); sheet.getRow(r).getCell(2).setCellValue(UniqueDailypaymentkeywordDescription.get(D)); sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D)); sheet.getRow(r).getCell(0).setCellValue("OPEN"); break; } else if (sheet.getRow(r).getCell(2).getStringCellValue() .equals(UniqueDailypaymentkeywordDescription.get(D))) { sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D)); sheet.getRow(r).getCell(0).setCellValue("OPEN"); break; } } } Shifting.InsertFormulas(wb, sheet); Shifting.MergeCells(wb, sheet); // Write output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xlsx"); wb.write(fileOut); fileOut.close(); InputStream inp = new FileInputStream("workbook.xlsx"); file = new DefaultStreamedContent(inp, "application/vnd.ms-excel", "Table_downloaded.xlsx"); } catch (IOException ex) { Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:temp.ExcelReader.java
public static void readFromXLSXFile(File file) { try {/*from w w w . ja va2s. c o m*/ XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file)); int sheetCount = workbook.getNumberOfSheets(); System.out.println(sheetCount); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowItertor = sheet.iterator(); while (rowItertor.hasNext()) { Row row = rowItertor.next(); for (int i = 0; i < row.getLastCellNum(); i++) { System.out.print(row.getCell(i) + "|"); //this you won't miss any cells! right way to do } System.out.println(""); } } catch (IOException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Test.LeerExcelXlsx.java
public static void main(String args[]) throws IOException { FileInputStream file = new FileInputStream( new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\Libro1.xlsx")); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /*/*from w w w .j a v a 2 s . c om*/ * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda while (rowIterator.hasNext()) { row = rowIterator.next(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { System.out.print(celda.getDateCellValue()); } else { System.out.print(celda.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); break; } } System.out.println(""); } // cerramos el libro excel workbook.close(); }
From source file:test.WriteXLSX.java
public static void main(String[] args) throws IOException { XSSFWorkbook workbook; try (FileInputStream fis = new FileInputStream(new File("D:/desk/test.xlsx"))) { workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row1 = sheet.createRow(0); XSSFCell r1c1 = row1.createCell(0); r1c1.setCellValue("Emd Id"); XSSFCell r1c2 = row1.createCell(1); r1c2.setCellValue("NAME"); XSSFCell r1c3 = row1.createCell(2); r1c3.setCellValue("AGE"); XSSFRow row2 = sheet.createRow(1); XSSFCell r2c1 = row2.createCell(0); r2c1.setCellValue("1"); XSSFCell r2c2 = row2.createCell(1); r2c2.setCellValue("Ram"); XSSFCell r2c3 = row2.createCell(2); r2c3.setCellValue("20"); XSSFRow row3 = sheet.createRow(2); XSSFCell r3c1 = row3.createCell(0); r3c1.setCellValue("2"); XSSFCell r3c2 = row3.createCell(1); r3c2.setCellValue("Shyam"); XSSFCell r3c3 = row3.createCell(2); r3c3.setCellValue("25"); }/*from w ww . j a v a 2 s.c om*/ try (FileOutputStream fos = new FileOutputStream(new File("D:\\desk\\test2.xlsx"))) { workbook.write(fos); } System.out.println("Done"); }
From source file:testpoi.GenerateDailyExcel.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {//from w w w.ja v a 2 s . c o m file1 = new FileInputStream(new File(path + "all.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Name"); cell = row.createCell(2); cell.setCellValue("Guardian's Name"); cell = row.createCell(3); cell.setCellValue("Relation"); cell = row.createCell(4); cell.setCellValue("AgeYrs"); cell = row.createCell(5); cell.setCellValue("Gender"); cell = row.createCell(6); cell.setCellValue("Address"); cell = row.createCell(7); cell.setCellValue("City"); cell = row.createCell(8); cell.setCellValue("State"); rowCnt = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.GenerateDailyExcelPickingRowsSequentially.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {// w w w . j av a2 s.co m file1 = new FileInputStream(new File(path + "all.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Name"); cell = row.createCell(2); cell.setCellValue("Guardian's Name"); cell = row.createCell(3); cell.setCellValue("Relation"); cell = row.createCell(4); cell.setCellValue("AgeYrs"); cell = row.createCell(5); cell.setCellValue("Gender"); cell = row.createCell(6); cell.setCellValue("Address"); cell = row.createCell(7); cell.setCellValue("City"); cell = row.createCell(8); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.Department.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {//from w ww .j a v a 2 s .co m file1 = new FileInputStream(new File(path + "all.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Patient Type"); cell = row.createCell(2); cell.setCellValue("CR No."); cell = row.createCell(3); cell.setCellValue("Name"); cell = row.createCell(4); cell.setCellValue("Guardian's Name"); cell = row.createCell(5); cell.setCellValue("Relation"); cell = row.createCell(6); cell.setCellValue("AgeYrs"); cell = row.createCell(7); cell.setCellValue("Gender"); cell = row.createCell(8); cell.setCellValue("Address"); cell = row.createCell(9); cell.setCellValue("City"); cell = row.createCell(10); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; /************************ TO SET AT EVERY RUN **************************/ crNo = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 203, true)); deptts.add(new Department("Surgery", 113, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); /***********************************************************************/ generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.OldDepttSheet.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {/*from w ww . ja v a2 s . c om*/ file1 = new FileInputStream(new File(path + "new.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); fileOldIn = new FileInputStream(new File(path + "old.xlsx")); workbookOld = new XSSFWorkbook(fileOldIn); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Patient Type"); cell = row.createCell(2); cell.setCellValue("CR No."); cell = row.createCell(3); cell.setCellValue("Name"); cell = row.createCell(4); cell.setCellValue("Guardian's Name"); cell = row.createCell(5); cell.setCellValue("Relation"); cell = row.createCell(6); cell.setCellValue("AgeYrs"); cell = row.createCell(7); cell.setCellValue("Gender"); cell = row.createCell(8); cell.setCellValue("Address"); cell = row.createCell(9); cell.setCellValue("City"); cell = row.createCell(10); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; /************************ TO SET AT EVERY RUN **************************/ crNo = 575; deptts = new ArrayList<>(); /* New */ deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 48, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); /* Old */ deptts.add(new Department("Medicine", 15, false)); deptts.add(new Department("Surgery", 13, false)); deptts.add(new Department("Obs & Gynae", 12, false)); deptts.add(new Department("Paediatrics", 9, false)); deptts.add(new Department("Orthopaedics", 11, false)); deptts.add(new Department("Ophthalmology", 16, false)); deptts.add(new Department("ENT", 6, false)); deptts.add(new Department("Dental", 8, false)); // Casualty is only new /***********************************************************************/ //Fill depttToOldSheetsMap Iterator<XSSFSheet> oldSheetsIter = workbookOld.iterator(); //Skip 1st sheet which contains all old patients oldSheetsIter.next(); depttToOldSheetsMap = new HashMap<>(); while (oldSheetsIter.hasNext()) { XSSFSheet oldSheet = oldSheetsIter.next(); depttToOldSheetsMap.put(oldSheet.getSheetName(), new OldDepttSheet(oldSheet)); } try { generateRows(); } catch (IllegalArgumentException e) { System.err.println(e.getMessage()); e.printStackTrace(); } try { FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.OPDDataTransfer.java
License:Open Source License
public static void main(String args[]) { try {//from w ww . j a va 2 s.c o m String dateFolder = "2.3.13"; FileInputStream file = new FileInputStream( new File("C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\" + dateFolder + "\\" + dateFolder + ".xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Connection conn = connectToDatabaseHMS(); assert (conn != null); Connection conn2 = connectToDatabaseHMSSecurity(); assert (conn2 != null); try { conn.setAutoCommit(false); conn2.setAutoCommit(false); } catch (SQLException sqle) { System.err.println("Could not set autocommit to false"); sqle.printStackTrace(); } //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); //Skip the 1st row rowIterator.next(); //set time time = Time.valueOf("09:00:00"); //set entry number default to 1. entryNumber = 1; // //set entry crNo // entryCrNo = 1; Timestamp timestamp = new Timestamp(date.getTime() + time.getTime() + 19800000/*for IST*/); System.out.println(timestamp.toString()); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, get values of each column Iterator<Cell> cellIterator = row.cellIterator(); // Cell cell = cellIterator.next(); // int crNo; // if(cell.getCellType()==0) // crNo = (int)(cell.getNumericCellValue()); // else // { // System.out.println ("crNo cell value: "+cell.getStringCellValue()); // crNo = (int)Integer.parseInt(cell.getStringCellValue().trim()); // } Cell cell = cellIterator.next(); String dept; if (cell.getCellType() == 1) dept = cell.getStringCellValue(); else { int no = (int) cell.getNumericCellValue(); dept = no + ""; System.out.println(dept); } cell = cellIterator.next(); String type = cell.getStringCellValue(); cell = cellIterator.next(); int crNo; if (cell.getCellType() == 0) crNo = (int) (cell.getNumericCellValue()); else { System.out.println("crNo cell value: " + cell.getStringCellValue()); crNo = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String guardian = cell.getStringCellValue(); cell = cellIterator.next(); String rel = cell.getStringCellValue(); cell = cellIterator.next(); System.out.println("\n cell.getCellType() :" + cell.getCellType()); int ageYrs = 0; if (cell.getCellType() == 0) ageYrs = (int) (cell.getNumericCellValue()); else { System.out.println("age cell value: " + cell.getStringCellValue()); ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String gender = cell.getStringCellValue(); cell = cellIterator.next(); String add = cell.getStringCellValue(); cell = cellIterator.next(); String city = cell.getStringCellValue(); cell = cellIterator.next(); String state = cell.getStringCellValue(); int deptID = getDeptID(dept); assert (deptID != 0); int stateID = getStateID(state); assert (stateID != 0); int drID = getDrID(deptID); assert (drID != 0); // if drID = 0 that means a dept. has been entered which doesn't have a doctor String loginUserName = getLoginUserName(); String userID = getLoginUserID(loginUserName); long OPDNo = Long.parseLong(OPDDATE + entryNumber); boolean queryExecuted = true; if (type.equals("New")) // As only New patients must be registered and have a CR generated { try { String insertSql = "INSERT INTO Reg " + "(Regno, Name, Fname, Relation, AgeY, Sex, Address1, City, State, Department, Date)" + "VALUES(" + crNo + ",'" + name + "','" + guardian + "','" + rel + "'," + ageYrs + ",'" + gender + "','" + add + "','" + city + "'," + stateID + "," + deptID + ",'" + date + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in Reg gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into Reg...!!"); ex.printStackTrace(); } } try { String insertSql = "INSERT INTO OPD " + "(OPDNo, CrNo, PatientType, DepartmentId, DrId, Date, Time, LoginUserName, IsActive)" + "VALUES(" + OPDNo + "," + crNo + ",'" + type + "'," + deptID + "," + drID + ",'" + date + "','" + time + "','" + loginUserName + "','" + true + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in OPD gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into OPD...!!"); ex.printStackTrace(); } try { String updateSql = "UPDATE aspnet_Users " + "SET LastActivityDate='" + timestamp + "'" + "WHERE UserId='" + userID + "'"; System.out.println(updateSql); Statement st = conn2.createStatement(); int val = st.executeUpdate(updateSql); System.out.println("One row in aspnet_Users gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot update timestamp in aspnet_Users...!!"); ex.printStackTrace(); } if (!queryExecuted) //if insertion to any table fails, rollback. try { conn.rollback(); conn2.rollback(); break; // and run program again at any error } catch (SQLException ex) { ex.printStackTrace(); } else try { conn.commit(); conn2.commit(); entryNumber++; // entryCrNo++; time = new Time(time.getTime() + 50000);//add 50 seconds timestamp = new Timestamp(timestamp.getTime() + 50000); } catch (SQLException ex) { ex.printStackTrace(); } } file.close(); if (conn != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn.close(); System.out.println("HMS Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } if (conn2 != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn2.close(); System.out.println("HMS_Security Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }