List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue
void setCellValue(boolean value);
From source file:Report_PRCR_New_EPF_Excel_File_Generator.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {/*from w ww .jav a 2s . co m*/ DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location; if (mv.SoftwareVersion() == 1) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } else if (mv.SoftwareVersion() == 2) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "12", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "11", "location")), new File(employee_detail_file_location)); } else { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String epf_backup_year_month = year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0) / 100.0; double employer_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; double tot_contribution = 0; double employers_contribution = 0; double member_contribution = 0; double tot_earnings = 0; String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } ResultSet query = dbm.query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 "); // AND `total_pay` > 0"); while (query.next()) { if (query.getDouble("total_pay") <= 0 && dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", query.getInt("code")) != 1) { continue; } ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); d_o_code = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no) == 1) { tot_earnings = tot_earnings + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no, "total_pay")); } employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; tot_contribution = employers_contribution + member_contribution; total_member_contribution = total_member_contribution + tot_contribution; normal_days = query.getInt("normal_days"); sundays = query.getInt("sundays"); /* ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } */ no_of_days_worked = normal_days + sundays; if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } Row row = sheet.getRow(1 + count); if (row == null) { row = sheet.createRow(1 + count); } for (int k = 0; k < 16; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employers_contribution); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; case 7: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_earnings); break; case 8: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(member_status); break; case 9: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 10: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 11: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 12: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 13: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(no_of_days_worked); break; case 14: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(occupation_classification_grade); break; case 15: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(d_o_code); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:CreatePivotTable.java
License:Apache License
public static void setCellData(XSSFSheet sheet) { Row row1 = sheet.createRow(0);//from w w w. j a va2 s. c o m // Create a cell and put a value in it. Cell cell11 = row1.createCell(0); cell11.setCellValue("Names"); Cell cell12 = row1.createCell(1); cell12.setCellValue("#"); Cell cell13 = row1.createCell(2); cell13.setCellValue("%"); Cell cell14 = row1.createCell(3); cell14.setCellValue("Human"); Row row2 = sheet.createRow(1); Cell cell21 = row2.createCell(0); cell21.setCellValue("Jane"); Cell cell22 = row2.createCell(1); cell22.setCellValue(10); Cell cell23 = row2.createCell(2); cell23.setCellValue(100); Cell cell24 = row2.createCell(3); cell24.setCellValue("Yes"); Row row3 = sheet.createRow(2); Cell cell31 = row3.createCell(0); cell31.setCellValue("Tarzan"); Cell cell32 = row3.createCell(1); cell32.setCellValue(5); Cell cell33 = row3.createCell(2); cell33.setCellValue(90); Cell cell34 = row3.createCell(3); cell34.setCellValue("Yes"); Row row4 = sheet.createRow(3); Cell cell41 = row4.createCell(0); cell41.setCellValue("Terk"); Cell cell42 = row4.createCell(1); cell42.setCellValue(10); Cell cell43 = row4.createCell(2); cell43.setCellValue(90); Cell cell44 = row4.createCell(3); cell44.setCellValue("No"); }
From source file:CreateExcel.java
public static void write_excel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("version 1"); System.out.println("created"); Row row = sheet.createRow((short) 0); Cell col = row.createCell(0); col.setCellValue("FileName"); row.createCell(1).setCellValue("Boc"); System.out.println("here"); FileOutputStream fos;// ww w .ja va 2 s . c om try { fos = new FileOutputStream("C:\\Users\\aryan_000\\Desktop\\output.xlsx"); wb.write(fos); fos.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } }
From source file:aco.Utilities.java
License:Open Source License
static void writeInputDataPoints() { //the file already exists if (new File(filePath3).canRead()) { //System.out.println("File already exists.."); try {/*from ww w.j ava 2s. c o m*/ FileInputStream file = new FileInputStream(new File(filePath3)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(11); //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); Row r1 = sheet1.getRow(0); if (r1 == null) { // First cell in the row, create r1 = sheet1.createRow(0); } Cell c = r1.getCell(0); if (c == null) { c = r1.createCell(0); } c.setCellValue("VRPTW instance - " + VRPTW.instance.name + "; data point coordinates corresponding to customers' requests"); c.setCellStyle(style); Row r = sheet1.getRow(2); if (r == null) { // First cell in the row, create r = sheet1.createRow(2); } Cell c1 = r.getCell(0); if (c1 == null) { c1 = r.createCell(0); } c1.setCellValue("Point #"); c1.setCellStyle(style); c1 = r.getCell(1); if (c1 == null) { c1 = r.createCell(1); } c1.setCellValue("X Coord"); c1.setCellStyle(style); c1 = r.getCell(2); if (c1 == null) { c1 = r.createCell(2); } c1.setCellValue("Y Coord"); c1.setCellStyle(style); int size = VRPTW.instance.nodes.length; int rowIndex = 3; double x, y; for (int i = 0; i < size; i++) { x = VRPTW.instance.nodes[i].x; y = VRPTW.instance.nodes[i].y; r = sheet1.getRow(rowIndex + i); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(rowIndex + i); } c1 = r.getCell(0); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(0); } c1.setCellValue(i); c1 = r.getCell(1); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(1); } c1.setCellValue(x); c1 = r.getCell(2); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(2); } c1.setCellValue(y); } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath3)); workbook1.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } else { System.out.println("File not exists.."); } }
From source file:aco.Utilities.java
License:Open Source License
static void writeExcel(int n, int m, int result) { //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath).canRead()) { //System.out.println("File already exists.."); try {//from w w w . ja va 2s.co m FileInputStream file = new FileInputStream(new File(filePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(2); int countRows = sheet1.getLastRowNum() + 1; Row newRow = sheet1.createRow(countRows++); int cellnum = 0; Cell cell = newRow.createCell(cellnum++); cell.setCellValue(n); cell = newRow.createCell(cellnum++); cell.setCellValue(m); cell = newRow.createCell(cellnum++); cell.setCellValue(result); //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook1.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook XSSFWorkbook workbook2 = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet2 = workbook2.createSheet("Results - 51 cities"); //Iterate over data and write to sheet int rownum = 0, cellnum = 0; Row row = sheet2.createRow(rownum++); Cell cell = row.createCell(cellnum++); cell.setCellValue(n); cell = row.createCell(cellnum++); cell.setCellValue(m); cell = row.createCell(cellnum++); cell.setCellValue(result); try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook2.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } }
From source file:aco.Utilities.java
License:Open Source License
static void writeResultsExcel(int trialNumber, boolean saveIterCosts) { Row r, r1;// ww w . j a v a2 s.c o m Cell c; int index1 = 0, index2 = 0, index3 = 0, index4 = 0, index5 = 0; //int index6 = 0; //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath).canRead()) { //System.out.println("File already exists.."); try { FileInputStream file = new FileInputStream(new File(filePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); int startIndex = 0, rowIndex = 0; /*switch (VRPTW.m) { case 2: startIndex = 0; rowIndex = 4; break; case 3: startIndex = 2; rowIndex = 5; break; case 5: startIndex = 4; rowIndex = 7; break; case 7: startIndex = 6; rowIndex = 9; break; default: System.out.println("Unknown value for m"); break; }*/ //Get desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(startIndex); //for tours /*XSSFSheet sheet2 = workbook1.getSheetAt(startIndex + 1); //for number of assigned cities XSSFSheet sheet3 = workbook1.getSheetAt(startIndex + 2); //for cost of individual subtours XSSFSheet sheet4 = workbook1.getSheetAt(startIndex + 3); //for total cost of subtours XSSFSheet sheet5 = workbook1.getSheetAt(startIndex + 4); //for verbose output of total cost at each 5 iteration */ XSSFSheet sheet2 = workbook1.getSheetAt(startIndex + 1); //for verbose output of longest cost at each 5 iteration //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); //define style with bold font and blue color for font CellStyle styleBoldBlue = workbook1.createCellStyle(); font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLUE.index); styleBoldBlue.setFont(font); index1 = 133; if (!saveIterCosts) { //write only once the name of the algorithm that was run if (trialNumber == 0) { r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue( "Obtained solutions (values) after running new version (ACS MinMax global, voiajor si oras alesi simultan) with local search"); c.setCellStyle(styleBoldBlue); } //write only once the table header index1 = index1 + 3; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue("Run #"); c.setCellStyle(style); c = r.getCell(1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(1); } c.setCellValue("MinMax (cost of longest subtour)"); c.setCellStyle(style); c = r.getCell(2); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(2); } c.setCellValue("Total Cost"); c.setCellStyle(style); c = r.getCell(3); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(3); } c.setCellValue("Amplitude"); c.setCellStyle(style); //write number of run index1 = 137 + trialNumber; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue(trialNumber + 1); //write MinMax (cost of longest subtour) double longestSubtour = getLongestSubtour(); c = r.getCell(1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(1); } c.setCellValue(longestSubtour); //write total cost double totalCost = getTotalCost(); c = r.getCell(2); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(2); } c.setCellValue(totalCost); //write amplitude double amplitude = getAmplitude(); c = r.getCell(3); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(3); } c.setCellValue(amplitude); } index5 = 859; if (saveIterCosts) { //write only once the name of the algorithm that was run if (trialNumber == 0) { r = sheet2.getRow(index5); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(index5); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue( "Longest cost of subtour at each 5 iteration after running new version (ACS MinMax global, voiajor si oras alesi simultan) with local search"); c.setCellStyle(styleBoldBlue); int tempIndex = index5 + 3; r = sheet2.getRow(tempIndex); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(tempIndex); } ArrayList<Integer> iterNumber = getIterNumber(); c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue("Nr Iter"); c.setCellStyle(style); int indexTemp = 0; for (int j = 0; j < iterNumber.size(); j++) { indexTemp = tempIndex + 1 + j; r1 = sheet2.getRow(indexTemp); if (r1 == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r1 = sheet2.createRow(indexTemp); } c = r1.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r1.createCell(0); } c.setCellValue(iterNumber.get(j)); } } index5 = index5 + 3; r = sheet2.getRow(index5); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(index5); } //for each trial run save at each 5 iteration the best longest cost of a subtour so far ArrayList<Double> iterLongestCost = getIterLongestCost(); int index; //for each run write the table header cell c = r.getCell(trialNumber + 1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(trialNumber + 1); } c.setCellValue("Run " + (trialNumber + 1)); c.setCellStyle(style); for (int j = 0; j < iterLongestCost.size(); j++) { index = index5 + 1 + j; r1 = sheet2.getRow(index); if (r1 == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r1 = sheet2.createRow(index); } c = r1.getCell(trialNumber + 1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r1.createCell(trialNumber + 1); } c.setCellValue(iterLongestCost.get(j)); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook1.write(out); out.close(); int nrOfRun = trialNumber + 1; System.out.println("\nRun #" + nrOfRun + " written successfully on disk.\n"); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook System.out.println("File " + filePath + " doesn't exists.."); } }
From source file:aco.Utilities.java
License:Open Source License
static void writeParetoSet(ArrayList<Ant> bestSoFarPareto, int trial) { Row r;//from w w w.j av a2s .com Cell c; int lineNumber = 0; //filePath1 += InOut.max_iterations + " iter (ACO MinMax_vers noua).xlsx"; //System.out.println("file path=" + filePath1); //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath1).canRead()) { //System.out.println("File already exists.."); try { FileInputStream file = new FileInputStream(new File(filePath1)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(trial); //write table header cells r = sheet1.getRow(lineNumber); if (r == null) { // First cell in the row, create r = sheet1.createRow(lineNumber); } c = r.getCell(0); if (c == null) { // New cell c = r.createCell(0); } c.setCellValue("Point #"); c = r.getCell(1); if (c == null) { // New cell c = r.createCell(1); } c.setCellValue("Total tours length"); c = r.getCell(2); if (c == null) { // New cell c = r.createCell(2); } c.setCellValue("Amplitude of tours"); c = r.getCell(3); if (c == null) { // New cell c = r.createCell(3); } c.setCellValue("List with cost of subtours"); lineNumber++; for (int i = 0; i < bestSoFarPareto.size(); i++) { r = sheet1.getRow(i + lineNumber); if (r == null) { // First cell in the row, create r = sheet1.createRow(i + lineNumber); } //write point id c = r.getCell(0); if (c == null) { // New cell c = r.createCell(0, Cell.CELL_TYPE_NUMERIC); } c.setCellValue(i + 1); //write total cost and amplitude for (int indexObj = 0; indexObj < 2; indexObj++) { c = r.getCell(indexObj + 1); if (c == null) { // New cell c = r.createCell(indexObj + 1, Cell.CELL_TYPE_NUMERIC); } c.setCellValue(bestSoFarPareto.get(i).costObjectives[indexObj]); } //write cost of each individual subtour for (int j = 0; j < bestSoFarPareto.get(i).tour_lengths.size(); j++) { c = r.getCell(j + 3); if (c == null) { // New cell c = r.createCell(j + 3); } c.setCellValue(bestSoFarPareto.get(i).tour_lengths.get(j)); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath1)); workbook1.write(out); out.close(); //System.out.println("\nWritten Pareto front points successfully on disk.\n"); int nrOfRun = trial + 1; System.out.println("\nRun #" + nrOfRun + " written Pareto front points successfully on disk.\n"); } catch (Exception e) { e.printStackTrace(); } } else { System.out.println(" File " + filePath1 + " doesn't exists"); } }
From source file:aco.Utilities.java
License:Open Source License
static void writeExcelFinalSolution(int trial, double scalledValue) { Row r;/*from w w w . jav a 2 s . co m*/ Cell c; int index1 = 0; //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath5).canRead()) { //System.out.println("File already exists.."); try { FileInputStream file = new FileInputStream(new File(filePath5)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(0); //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); //define style with bold font and blue color for font CellStyle styleBoldBlue = workbook1.createCellStyle(); font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLUE.index); styleBoldBlue.setFont(font); index1 = 8; //8 //26 index1 = index1 + trial; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } int nrOfRun = trial + 1; //write trial number (Run #) c = r.getCell(15); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(15); } c.setCellValue(nrOfRun); //write number of used vehicles c = r.getCell(16); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(16); } c.setCellValue(Ants.best_so_far_ant.usedVehicles); //write total traveled distance c = r.getCell(17); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(17); } c.setCellValue(scalledValue); //write the total number of feasible solutions c = r.getCell(18); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(18); } c.setCellValue(InOut.noSolutions); //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath5)); workbook1.write(out); out.close(); System.out.println("\nRun #" + nrOfRun + " written successfully on disk.\n"); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook System.out.println("File " + filePath5 + " doesn't exists.."); } }
From source file:adams.data.io.output.ExcelSpreadSheetWriter.java
License:Open Source License
/** * Performs the actual writing. The caller must ensure that the writer gets * closed./*from www .j a va2 s. co m*/ * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; Workbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { if (getWriteOOXML()) workbook = new XSSFWorkbook(); else workbook = new HSSFWorkbook(); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; }
From source file:adams.data.io.output.ExcelStreamingSpreadSheetWriter.java
License:Open Source License
/** * Performs the actual writing. The caller must ensure that the writer gets * closed.// w w w.ja v a2 s. c om * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; SXSSFWorkbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { workbook = new SXSSFWorkbook(m_MaxRows); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; }