List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:tan.jam.jsf.OrignalFileDownloadBean.java
public void downloadHandler(ActionEvent e) { System.out.println("download click handler"); try {//from w w w . j a v a 2s. c o 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: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 ww w. j a va2 s. c o m try (FileOutputStream fos = new FileOutputStream(new File("D:\\desk\\test2.xlsx"))) { workbook.write(fos); } System.out.println("Done"); }
From source file:testpoi.FlatFilesInFolderToExcel.java
License:Open Source License
public static void main(String args[]) { XSSFWorkbook workbook = new XSSFWorkbook(); sheet = workbook.createSheet("Read files"); //Create a new row in current sheet Row row = sheet.createRow(0);// w w w. j a va2s.c o m //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("File Contents"); rowCount = 1; processAllFiles(folder); try { FileOutputStream out = new FileOutputStream(new File("D:\\new.xlsx")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
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 .java 2 s . c om*/ 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. ja v a 2s .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; 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 w w . jav a2 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("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 w w . j a va 2 s . c o m*/ 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_.Department.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {/* www .ja va 2 s.com*/ 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 = 1050; 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", 20, 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", 42, 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:Tools.CompareDrotVSRoster.java
public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException { File xlsxFile = new File(leaveXlsxRoster); try {//from ww w .j av a 2s . c om FileInputStream fis = new FileInputStream(xlsxFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; // List<String> keyList = new ArrayList<>(); //keep track info of each column while (it.hasNext()) { //keyList.add(it.next().getStringCellValue()); it.next(); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); rowNum++; } for (int i = 1; i < rowNum; i++) { Row row = mySheet.getRow(i); foregroundColorSetUpV1(row, myWorkBook); //check each row and update foreground color } fis.close(); FileOutputStream output; String targetFile = null; String[] names = leaveXlsxRoster.split("\\."); if (!names[0].equals(leaveXlsxRoster)) { //doesn't contain surfix targetFile = names[0] + "COLORED.xlsx"; } else { targetFile = leaveXlsxRoster + "COLORED.xlsx"; } output = new FileOutputStream(targetFile); myWorkBook.write(output); output.close(); //myWorkBook.write(output); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { // full ssn roster System.out.println("CompareDrotVsRoster.java: Unsupported."); JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info! " + "CompareDrotVsRoster.java: Title V2 is not supported"); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } JOptionPane.showMessageDialog(null, "The leave roster is colored successfully. Please check *COLORED.xlsx."); // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); } }
From source file:Tools.PostProcessing.java
private Map<String, Integer> generateXLSXforS1(String XLSXfileName) throws FileNotFoundException, IOException { Map<String, Integer> statusMap = null; File myFile = new File(XLSXfileName); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); List<String> errorList = new ArrayList<>(); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList numCell++;// w w w. j a v a2 s . c o m } if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { // System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded."); // int globalCount = 1; DataFormatter df = new DataFormatter(); statusMap = new TreeMap<>(); while (rowIterator.hasNext()) { lineCount++; Row row = rowIterator.next(); Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2); Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2); String fullSSN = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell)); colorDeletedLeaves(fullSSN, ctrlNumCell, myWorkBook, statusMap); } // output to a new xlsx file fis.close(); FileOutputStream output; String targetFile = null; if (XLSXfileName.contains(".xlsx")) { targetFile = XLSXfileName.replace(".xlsx", "_forS1.xlsx"); } else { targetFile = XLSXfileName + "_forS1.xlsx"; } output = new FileOutputStream(targetFile); myWorkBook.write(output); output.close(); } else { JOptionPane.showMessageDialog(null, "XLSX file format is incorrect! Must be full SSN format"); } } return statusMap; }