List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:controller.application.employee.ViewEmployeController.java
private void toExcel(ArrayList<ToExcelEmployee> lst, ArrayList<RFIDTimestamp> times, File file, Timestamp from, Timestamp to) throws IOException { try {/*from ww w .j a va2 s .c o m*/ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Total arbetad tid " + file.getName()); XSSFRow row = spreadsheet.createRow(0); XSSFCell cell; cell = row.createCell(0); cell.setCellValue("Frnamn:"); cell = row.createCell(1); cell.setCellValue("Efternamn:"); cell = row.createCell(2); cell.setCellValue("Anstllningsnummer:"); cell = row.createCell(3); cell.setCellValue("Individuell tid (h) from.: " + (from.toString() + " tom.: " + to.toString()) .replaceAll("(\\s)((\\p{Digit}{2}:){2}\\p{Digit}{2})\\..", "")); Double totAllTime = 0.0; for (int i = 1; i <= lst.size(); i++) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(lst.get(i - 1).surname); cell = row.createCell(1); cell.setCellValue(lst.get(i - 1).lastname); cell = row.createCell(2); cell.setCellValue(lst.get(i - 1).id); cell = row.createCell(3); cell.setCellValue(lst.get(i - 1).time); totAllTime += lst.get(i - 1).time; if (i == lst.size()) { row = spreadsheet.createRow(i + 1); cell = row.createCell(3); cell.setCellValue("Sammanstllning:"); row = spreadsheet.createRow(i + 2); cell = row.createCell(3); cell.setCellValue(totAllTime); } } XSSFSheet spreadsheetTimes = workbook.createSheet("Tider"); row = spreadsheetTimes.createRow(0); cell = row.createCell(0); cell.setCellValue("RFID"); cell = row.createCell(1); cell.setCellValue("IN/UT"); cell = row.createCell(2); cell.setCellValue("Datum/Tid"); for (int i = 1; i <= times.size(); i++) { row = spreadsheetTimes.createRow(i); cell = row.createCell(0); cell.setCellValue(times.get(i - 1).getRFID().toString()); cell = row.createCell(1); cell.setCellValue(times.get(i - 1).getInOut()); cell = row.createCell(2); cell.setCellValue(times.get(i - 1).getTime()); } for (int k = 0; k < spreadsheet.getRow(0).getLastCellNum(); k++) { spreadsheet.autoSizeColumn(k); } for (int j = 0; j < spreadsheetTimes.getRow(0).getLastCellNum(); j++) { spreadsheetTimes.autoSizeColumn(j); } try (FileOutputStream out = new FileOutputStream(file)) { workbook.write(out); } System.out.println(file.getName() + " written successfully"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:Controller.Sonstiges.ExcelController.java
private void makeTitleOfPage() throws IOException { this.sheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 13)); this.row = this.sheet.createRow(0); XSSFCell cell2 = this.row.createCell(0); String title = "Inprotuc Datenbank | Informationen zur Personen \nSuchkriterien: "; ArrayList<String> array2 = this.model.getQueryInfo(); ArrayList<String> array = this.deleteEmptyValueOArray(array2); String info = ""; if (array.size() == 2) { info = array.get(0) + " / " + array.get(1) + "."; }//from w w w. ja va2 s. com if (array.size() == 4) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + "."; } if (array.size() == 6) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + ", "; info = info + array.get(4) + "/" + array.get(5) + "."; } cell2.setCellValue(title + info); CellStyle cellStyle = this.wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); // font Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); }
From source file:cz.lbenda.dataman.db.ExportTableData.java
License:Apache License
/** Write rows to XLSX file * @param sqlQueryRows rows//from w w w . java 2 s.c o m * @param sheetName name of sheet where is data write * @param outputStream stream where are data write */ public static void writeSqlQueryRowsToXLSX(SQLQueryRows sqlQueryRows, String sheetName, OutputStream outputStream) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow headerRow = sheet.createRow(0); int c = 0; for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) { XSSFCell cell = headerRow.createCell(c); cell.setCellValue(columnDesc.getName()); c++; } int r = 1; for (RowDesc row : sqlQueryRows.getRows()) { XSSFRow xlsxRow = sheet.createRow(r); c = 0; for (ColumnDesc columnDesc : sqlQueryRows.getMetaData().getColumns()) { XSSFCell cell = xlsxRow.createCell(c); cell.setCellValue(row.getColumnValueStr(columnDesc)); c++; } r++; } wb.write(outputStream); }
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {//from w ww .jav a 2s.co m //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>(); WorkDao wdao1 = new WorkDao(); wi1 = wdao1.getWOItem(pono); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); // spreadsheet.protectSheet("kandarpCBA"); // spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("NOTE"); cell.setCellStyle(xstyle); int i = 2; for (WorkItemBean w : wi1) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(w.getLINE_NO()); cell = row.createCell(1); cell.setCellValue(w.getITEM_ID()); cell = row.createCell(2); cell.setCellValue(w.getITEM_DESC()); cell = row.createCell(3); cell.setCellValue(w.getUOM()); cell = row.createCell(4); cell.setCellValue(w.getQTY()); cell = row.createCell(5); cell.setCellValue(w.getRATE()); cell = row.createCell(6); cell.setCellValue(w.getCMT()); i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsBillDao.java
public void GenXLS(String pono, String relpath) { try {//from w w w. jav a 2 s . co m //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; String sql; Connection con; PreparedStatement ps; ResultSet rs; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data con = DBmanager.GetConnection(); sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n" + " mtl_system_items.description, cba_wo_item.uom,\n" + " cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n" + " cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task," + " cba_wo_item.po_no\n" + " FROM cba_wo_item, mtl_system_items\n" + " WHERE ( (cba_wo_item.item_id = mtl_system_items.segment1)\n" + " AND (mtl_system_items.organization_id = 0)\n" + " AND (cba_wo_item.po_no = '" + pono + "')\n" + " )\n" + " ORDER BY cba_wo_item.line_no"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); //spreadsheet.protectSheet("kandarpCBA"); //spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); // borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex()); // borderStyle.setFillPattern(CellStyle.ALIGN_FILL); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono + " Note : If WO is with project information,each bill item should have project and task"); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("WO NOTE"); cell.setCellStyle(xstyle); cell = row.createCell(7); cell.setCellValue("PLANT"); cell.setCellStyle(xstyle); cell = row.createCell(8); cell.setCellValue("COST CENTER"); cell.setCellStyle(xstyle); cell = row.createCell(9); cell.setCellValue("PROJECT"); cell.setCellStyle(xstyle); cell = row.createCell(10); cell.setCellValue("TASK"); cell.setCellStyle(xstyle); cell = row.createCell(11); cell.setCellValue("HERE ADD NOTE"); cell.setCellStyle(xstyle); //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(rs.getString(1)); cell = row.createCell(1); cell.setCellValue(rs.getString(2)); cell = row.createCell(2); cell.setCellValue(rs.getString(3)); cell = row.createCell(3); cell.setCellValue(rs.getString(4)); cell = row.createCell(4); cell.setCellValue(rs.getString(6)); cell = row.createCell(5); cell.setCellValue(rs.getString(5)); cell = row.createCell(6); cell.setCellValue(""); cell = row.createCell(7); cell.setCellValue(rs.getString(7)); cell = row.createCell(8); cell.setCellValue(rs.getString(8)); cell = row.createCell(9); cell.setCellValue(rs.getString(9)); cell = row.createCell(10); cell.setCellValue(rs.getString(10)); cell = row.createCell(11); cell.setCellValue(""); i++; } //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row2; XSSFCell cell2; XSSFSheet ccsheet = workbook.createSheet("Cost Center"); row2 = ccsheet.createRow(0); cell2 = row2.createCell(0); cell2.setCellValue("Cost Center name and code. Please enter only code in excel"); cell2.setCellStyle(borderStyle); ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row2 = ccsheet.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("NAME"); cell2.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_cc_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i2 = 2; while (rs.next()) { row2 = ccsheet.createRow(i2); cell2 = row2.createCell(0); cell2.setCellValue(rs.getString(1)); cell2 = row2.createCell(1); cell2.setCellValue(rs.getString(2)); i2++; } //THIRD SHEET //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row3; XSSFCell cell3; XSSFSheet plantsheet = workbook.createSheet("Plant Center"); row3 = plantsheet.createRow(0); cell3 = row3.createCell(0); cell3.setCellValue("Plant Center name and code. Please enter only code in excel"); cell3.setCellStyle(borderStyle); plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row3 = plantsheet.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("NAME"); cell3.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_plant_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i3 = 2; while (rs.next()) { row3 = plantsheet.createRow(i3); cell3 = row3.createCell(0); cell3.setCellValue(rs.getString(1)); cell3 = row3.createCell(1); cell3.setCellValue(rs.getString(2)); i3++; } //SHEET 3 HEADER //row1 XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task"); XSSFRow row4 = spreadsheet4.createRow(0); XSSFCell cell4 = row4.createCell(0); cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row4 = spreadsheet4.createRow(1); cell4 = row4.createCell(0); cell4.setCellValue("PROJECT CODE"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(1); cell4.setCellValue("PROJECT NAME"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(2); cell4.setCellValue("TASK CODE"); cell4.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123"); for (WorkItemBean w : wi1) { row4 = spreadsheet4.createRow(j); cell4 = row4.createCell(0); cell4.setCellValue(w.getPROJ()); cell4 = row4.createCell(1); cell4.setCellValue(w.getPROJ_NAME()); cell4 = row4.createCell(2); cell4.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (SQLException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsWoDao.java
public void GenXLS(String orgId, String relpath) { try {/*from ww w . jav a 2 s . c o m*/ //2.Create WorkBook and Sheet XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet1 = workbook.createSheet("WorkOrder Detail"); XSSFSheet spreadsheet2 = workbook.createSheet("Plant Master"); XSSFSheet spreadsheet3 = workbook.createSheet("Project and Task Master"); //style XSSFFont xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); XSSFCellStyle xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //SHEET 1 HEADER //1row XSSFRow row1 = spreadsheet1.createRow(0); XSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Note : If you are not sure " + "about plant,project,task please " + "leave it blank." + "It can be add when release bill"); spreadsheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //2row row1 = spreadsheet1.createRow(1); cell1 = row1.createCell(0); cell1.setCellValue("Create Work Order Template"); cell1.setCellStyle(borderStyle); spreadsheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); //3row row1 = spreadsheet1.createRow(2); cell1 = row1.createCell(0); cell1.setCellValue("ITEM_NO"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(1); cell1.setCellValue("RATE"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(2); cell1.setCellValue("PLANT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(3); cell1.setCellValue("PROJECT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(4); cell1.setCellValue("TASK"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(5); cell1.setCellValue("ADDITIONAL NOTE"); cell1.setCellStyle(xstyle); //SHEET 2 HEADER //row1 XSSFRow row2 = spreadsheet2.createRow(0); XSSFCell cell2 = row2.createCell(0); cell2.setCellValue("Note : Please copy plant code and paste into 1 sheet"); spreadsheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row2 = spreadsheet2.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("PLANT_CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("PLANT NAME"); cell2.setCellStyle(xstyle); //SHEET 2 DATA int i = 2; Map<String, String> plant = Dropdown.LoadPlantMst(); for (Map.Entry<String, String> entry : plant.entrySet()) { row2 = spreadsheet2.createRow(i); cell2 = row2.createCell(0); cell2.setCellValue(entry.getKey()); cell2 = row2.createCell(1); cell2.setCellValue(entry.getValue()); i++; } //SHEET 3 HEADER //row1 XSSFRow row3 = spreadsheet3.createRow(0); XSSFCell cell3 = row3.createCell(0); cell3.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row3 = spreadsheet3.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("PROJECT CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("PROJECT NAME"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(2); cell3.setCellValue("TASK CODE"); cell3.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst(orgId); for (WorkItemBean w : wi1) { row3 = spreadsheet3.createRow(j); cell3 = row3.createCell(0); cell3.setCellValue(w.getPROJ()); cell3 = row3.createCell(1); cell3.setCellValue(w.getPROJ_NAME()); cell3 = row3.createCell(2); cell3.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); // FileOutputStream out = new FileOutputStream(new File(relpath + "uxls//" + "WO_Creation_Template" + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(relpath)); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:data.control.dataSheet.java
private void newPatient(Patient newPatient) { XSSFRow newRow;//from w w w. java 2s. c o m // creating a new row if (firstSheet.getRow(newPatient.getId()) == null) { newRow = firstSheet.createRow(newPatient.getId()); } else { newRow = firstSheet.getRow(newPatient.getId()); } // creating a new cell XSSFCell cell = newRow.getCell(0); if (cell == null) { cell = newRow.createCell(0); } // adding the patient ID cell.setCellValue(newPatient.getId()); // adding the patient Name // creating a new cell cell = newRow.getCell(1); if (cell == null) { cell = newRow.createCell(1); } cell.setCellValue(newPatient.getName()); // adding BPM if (!newPatient.getBPMArray().isEmpty()) { int cellID = 2; for (Iterator itr = newPatient.getBPMArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding Tempreature if (!newPatient.getTempArray().isEmpty()) { int cellID = 7; for (Iterator itr = newPatient.getTempArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding the patient blood type cell = newRow.getCell(12); if (cell == null) { cell = newRow.createCell(12); } cell.setCellValue(newPatient.getBloodType()); // adding the patient sex cell = newRow.getCell(13); if (cell == null) { cell = newRow.createCell(13); } cell.setCellValue(newPatient.getSex()); // adding the patient age cell = newRow.getCell(14); if (cell == null) { cell = newRow.createCell(14); } cell.setCellValue(newPatient.getAge()); // setting up the date format XSSFCellStyle cellStyle = theWorkbook.createCellStyle(); CreationHelper createHelper = theWorkbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy hh:mm:ss")); // adding the patient dateAdded cell = newRow.getCell(15); if (cell == null) { cell = newRow.createCell(15); } if (newPatient.getDateCreated() != null) { cell.setCellValue(newPatient.getDateCreated()); } else { cell.setCellValue(new Date()); } cell.setCellStyle(cellStyle); // adding the date modified if (newPatient.getLastModified() != null) { cell = newRow.getCell(16); if (cell == null) { cell = newRow.createCell(16); } cell.setCellValue(newPatient.getLastModified()); cell.setCellStyle(cellStyle); } // adding the lastAlarm if (newPatient.getLastAlarmed() != null) { cell = newRow.getCell(17); if (cell == null) { cell = newRow.createCell(17); } cell.setCellValue(newPatient.getLastAlarmed()); cell.setCellStyle(cellStyle); } }
From source file:data.pkg.ReadWriteExcelFile.java
public static void writeXLSXFile(String fileName, Data data) throws IOException { String excelFileName = fileName;//name of excel file String sheetName = "Sheet1";//name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); //iterating r number of rows for (int r = 0; r < data.getLength(); r++) { XSSFRow row = sheet.createRow(r); //iterating c number of columns for (int c = 0; c < 2; c++) { if (r == 0 && c == 0) { XSSFCell cell = row.createCell(c); cell.setCellValue("Deslocamento"); }/* ww w . j a v a2 s .c o m*/ if (r == 0 && c == 1) { XSSFCell cell = row.createCell(c); cell.setCellValue("Fora"); } if (r > 0 && c == 0) { XSSFCell cell = row.createCell(c); cell.setCellValue(data.getDeslocamento(r - 1)); } if (r > 0 && c == 1) { XSSFCell cell = row.createCell(c); cell.setCellValue(data.getForca(r - 1)); } } } FileOutputStream fileOut = new FileOutputStream(excelFileName); //write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private void fillStations(XSSFSheet sheetStations, FormulaEvaluator evaluator) throws SQLException { LinkedHashSet<String> se = getStationExtra(); XSSFRow row = sheetStations.getRow(0); int j = 0;/*from w w w. j a v a 2s . c om*/ for (String e : se) { if (e != null && !e.isEmpty()) { XSSFCell cell = row.getCell(11 + j); if (cell == null) cell = row.createCell(11 + j); cell.setCellValue(e); j++; } } String sql = "Select * from " + MyDBI.delimitL("Station") + " ORDER BY " + MyDBI.delimitL("Serial") + " ASC"; ResultSet rs = DBKernel.getResultSet(sql, false); if (rs != null && rs.first()) { int rownum = 1; do { row = sheetStations.getRow(rownum); if (row == null) row = sheetStations.createRow(rownum); rownum++; XSSFCell cell; if (rs.getObject("Serial") != null) { cell = row.createCell(0); cell.setCellValue(rs.getString("Serial")); } else if (rs.getObject("ID") != null) { cell = row.createCell(0); cell.setCellValue(rs.getString("ID")); } if (rs.getObject("Name") != null) { cell = row.createCell(1); cell.setCellValue(rs.getString("Name")); } if (rs.getObject("Strasse") != null) { cell = row.createCell(2); cell.setCellValue(rs.getString("Strasse")); } if (rs.getObject("Hausnummer") != null) { cell = row.createCell(3); cell.setCellValue(rs.getString("Hausnummer")); } if (rs.getObject("PLZ") != null) { cell = row.createCell(4); cell.setCellValue(rs.getString("PLZ")); } if (rs.getObject("Ort") != null) { cell = row.createCell(5); cell.setCellValue(rs.getString("Ort")); } if (rs.getObject("District") != null) { cell = row.createCell(6); cell.setCellValue(rs.getString("District")); } if (rs.getObject("Bundesland") != null) { cell = row.createCell(7); cell.setCellValue(rs.getString("Bundesland")); } if (rs.getObject("Land") != null) { cell = row.createCell(8); cell.setCellValue(rs.getString("Land")); } if (rs.getObject("Betriebsart") != null) { cell = row.createCell(9); cell.setCellValue(rs.getString("Betriebsart")); } //cell = row.getCell(10); evaluator.evaluateFormulaCell(cell); fillExtraFields("Station", rs.getObject("ID"), row, se, 11); /* if (rs.getObject("ID") != null) { sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='Station' AND " + MyDBI.delimitL("id") + "=" + rs.getInt("ID"); ResultSet rs2 = DBKernel.getResultSet(sql, false); if (rs2 != null && rs2.first()) { do { String s = rs2.getString("attribute"); j=0; for (String e : se) { if (s.equals(e)) { cell = row.getCell(11+j); if (cell == null) cell = row.createCell(11+j); cell.setCellValue(rs2.getString("value")); break; } j++; } } while (rs2.next()); } } */ } while (rs.next()); } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private void fillLookup(XSSFWorkbook workbook, XSSFSheet sheetLookup) throws SQLException { String sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Sampling'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; ResultSet rs = DBKernel.getResultSet(sql, false); int rownum = 1; if (rs != null && rs.first()) { do {//from w w w. j a va 2 s.co m XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } Name reference = workbook.createName(); reference.setNameName("Sampling"); String referenceString = sheetLookup.getSheetName() + "!$A$2:$A$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='TypeOfBusiness'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(1); if (cell == null) cell = row.createCell(1); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("ToB"); referenceString = sheetLookup.getSheetName() + "!$B$2:$B$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Treatment'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(2); if (cell == null) cell = row.createCell(2); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Treatment"); referenceString = sheetLookup.getSheetName() + "!$C$2:$C$" + (rownum); reference.setRefersToFormula(referenceString); sql = "Select * from " + MyDBI.delimitL("LookUps") + " WHERE " + MyDBI.delimitL("type") + "='Units'" + " ORDER BY " + MyDBI.delimitL("value") + " ASC"; rs = DBKernel.getResultSet(sql, false); rownum = 1; if (rs != null && rs.first()) { do { XSSFRow row = sheetLookup.getRow(rownum); if (row == null) row = sheetLookup.createRow(rownum); XSSFCell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellValue(rs.getString("value")); rownum++; } while (rs.next()); } reference = workbook.createName(); reference.setNameName("Units"); referenceString = sheetLookup.getSheetName() + "!$D$2:$D$" + (rownum); reference.setRefersToFormula(referenceString); }