001package bradleyross.library.helpers; 002 003import java.lang.Integer; 004import java.io.BufferedReader; 005import java.io.File; 006import java.io.FileReader; 007import java.io.FileWriter; 008import java.io.StringReader; 009import java.sql.ResultSet; 010import java.sql.ResultSetMetaData; 011import java.sql.SQLException; 012import java.text.SimpleDateFormat; 013/** 014 * Generates an Excel XML workbook. 015 * 016 * @author Bradley Ross 017 * @see bradleyross.library.helpers.tests.TestSpreadsheetHelpers 018 */ 019public class SpreadsheetHelpers 020{ 021 /** 022 * Running count of the number of rows in the current spreadsheet. 023 */ 024 private int numRows = 0; 025 /** 026 * Number of columns in the current spreadsheet. 027 */ 028 private int numCols = 0; 029 /** 030 * Controls amount of diagnostic messages to be generated. 031 * @see #setDebugLevel(int) 032 * @see #getDebugLevel() 033 */ 034 private int debugLevel = 0; 035 /** 036 * Set amount of diagnostic messages to be printed. 037 * 038 * <p>A value of 0 indicates that a normal level diagnostic messages are 039 * to be printed. Increased 040 * values increase the quantity of diagnostic messages, while negative 041 * numbers reduce the amount of messages.</p> 042 * 043 * @param level Desired level of diagnostic messages. 044 */ 045 public void setDebugLevel (int level) 046 { 047 debugLevel = level; 048 } 049 /** 050 * Returns value of debugLevel 051 * @return Value of debugLevel 052 * @see #debugLevel 053 * @see #setDebugLevel(int) 054 */ 055 public int getDebugLevel() 056 { 057 return debugLevel; 058 } 059 /** 060 * This method constructs a new file containing the 061 * contents of a String object. 062 * 063 * @param data String containing information to be moved to file 064 * @param fileName Name of file to be generated 065 */ 066 public void stringToFile(String data, String fileName) 067 { 068 try 069 { 070 File outputFile = new File(fileName); 071 FileWriter outputWriter = new FileWriter(outputFile); 072 outputWriter.write(data); 073 outputWriter.close(); 074 } 075 catch (java.io.IOException e) 076 { 077 System.out.println("Error in SpreadsheetHelpers.stringToFile"); 078 e.printStackTrace(System.out); 079 } 080 } 081 /** 082 * Prints the contents of a result set. 083 * @param rs ResultSet to be printed 084 */ 085 public void listResultSet(java.sql.ResultSet rs) 086 throws SQLException 087 { 088 ResultSetMetaData meta; 089 if (rs == null) 090 { 091 System.out.println("Error in listResultSet"); 092 System.out.println("ResultSet has null value"); 093 } 094 try 095 { 096 meta = rs.getMetaData(); 097 System.out.print(meta.getColumnCount()); 098 System.out.println(" columns"); 099 for (int i=1; i <= meta.getColumnCount(); i++) 100 { 101 System.out.print(meta.getColumnLabel(i)); 102 System.out.print(" "); 103 System.out.print(meta.getColumnTypeName(i)); 104 System.out.println(" "); 105 } 106 System.out.println("***"); 107 while (rs.next()) 108 { 109 System.out.println("*** New record"); 110 for (int i=1; i <= meta.getColumnCount(); i++) 111 { System.out.println(rs.getString(i)); } 112 } 113 } 114 catch (SQLException ex1) 115 { 116 System.out.println("*** Error in listResultSet"); 117 System.out.println("SQLException"); 118 ex1.printStackTrace(System.out); 119 throw ex1; 120 } 121 catch (Exception ex) 122 { 123 System.out.println("*** Error in listResultSet"); 124 System.out.println("Unknown Exception"); 125 ex.printStackTrace(System.out); 126 } 127 } 128 129 private void addText(StringBuffer working, String text) 130 { 131 working.append(text); 132 working.append(" \r\n"); 133 } 134 /** 135 * Returns the character string that should appear at the start of the 136 * workbook. 137 * 138 * <p>Some of the literal values in the listing below should probably be 139 * replaced with calculated values, particularly the date and time 140 * of the creation of the document.</p> 141 * 142 * @return Text for start of workbook 143 */ 144 public String startWorkbook() 145 { 146 StringBuffer working = new StringBuffer(); 147 addText(working, "<?xml version=\"1.0\"?> "); 148 addText(working, "<Workbook "); 149 addText(working, "xmlns=\"urn:schemas-microsoft-com:office" 150 .concat(":spreadsheet\" ")); 151 addText(working, "xmlns:o=\"urn:schemas-microsoft-com:office:office\" "); 152 addText(working, "xmlns:x=\"urn:schemas-microsoft-com:office:excel\" "); 153 addText(working, "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" "); 154 addText(working, "xmlns:html=\"http://www.w3.org/TR/REC-html40\"> "); 155 addText(working, "<DocumentProperties "); 156 addText(working, " xmlns=\"urn:schemas-microsoft-com:office:office\"> "); 157 addText(working, "<Author>bross</Author> "); 158 addText(working, "<LastAuthor>bross</LastAuthor> "); 159 addText(working, "<Created>2005-11-08T19:50:13Z</Created> "); 160 addText(working, "<Company></Company> "); 161 addText(working, "<Version>10.4219</Version> "); 162 addText(working, "</DocumentProperties> "); 163 addText(working, "<OfficeDocumentSettings "); 164 addText(working, "xmlns=\"urn:schemas-microsoft-com:office:office\"> "); 165 addText(working, "<DownloadComponents/> "); 166 addText(working, "<LocationOfComponents HRef=\"file:///\\\"/> "); 167 addText(working, "</OfficeDocumentSettings> "); 168 addText(working, "<ExcelWorkbook "); 169 addText(working, "xmlns=\"urn:schemas-microsoft-com:office:excel\"> "); 170 addText(working, "<WindowHeight>7680</WindowHeight> "); 171 addText(working, "<WindowWidth>11355</WindowWidth> "); 172 addText(working, "<WindowTopX>120</WindowTopX> "); 173 addText(working, "<WindowTopY>90</WindowTopY> "); 174 addText(working, "<ProtectStructure>False</ProtectStructure> "); 175 addText(working, "<ProtectWindows>False</ProtectWindows> "); 176 addText(working, "</ExcelWorkbook> "); 177 addText(working, "<Styles> "); 178 addText(working, "<Style ss:ID=\"Default\" ss:Name=\"Normal\"> "); 179 addText(working, "<Alignment ss:Vertical=\"Bottom\"/> "); 180 addText(working, "<Borders/> "); 181 addText(working, "<Font/> "); 182 addText(working, "<Interior/> "); 183 addText(working, "<NumberFormat/> "); 184 addText(working, "<Protection/> "); 185 addText(working, "</Style> "); 186 addText(working, "<Style ss:ID=\"s21\"> "); 187 addText(working, "<NumberFormat ss:Format=\"Fixed\"/> "); 188 addText(working, "</Style> "); 189 /* Style s23 if for the column names and text entries */ 190 addText(working, "<Style ss:ID=\"s23\"> "); 191 addText(working, "<Alignment ss:Vertical=\"Bottom\" " 192 .concat("ss:WrapText=\"1\"/> ")); 193 addText(working, "<NumberFormat ss:Format=\"@\"/> "); 194 addText(working, "</Style> "); 195 /* Style s24 is for dates */ 196 addText(working, "<Style ss:ID=\"s24\"> "); 197 addText(working, "<Alignment ss:Vertical=\"Bottom\" " 198 .concat("ss:WrapText=\"1\"/> ")); 199 addText(working, "<NumberFormat ss:Format=\"m/d/yyyy;@\"/> "); 200 addText(working, "</Style> "); 201 addText(working, "</Styles> "); 202 return new String(working); 203 } 204 /** 205 * Generate the XML text to appear at the start of a spreadsheet within the 206 * workbook. 207 * 208 * @param worksheetName Name of spreadsheet 209 * @param numRows Number of rows in spreadsheet 210 * @param numCols Number of columns in spreadsheet 211 * @return XML text for start of spreadsheet 212 */ 213 private String startWorksheet(String worksheetName, int numRows, int numCols) 214 { 215 StringBuffer working =new StringBuffer(); 216 addText(working, "<Worksheet ss:Name=\"" 217 .concat(worksheetName) 218 .concat("\"> ")); 219 /* 220 ** The ExpandedColumnCount and ExpandedRowCount 221 ** have to be set to actual dimensions of the 222 ** spreadsheet. 223 */ 224 addText(working, "<Table ss:ExpandedColumnCount=\"" 225 .concat(Integer.toString(numCols)) 226 .concat("\" ")); 227 addText(working, "ss:ExpandedRowCount=\"" 228 .concat(Integer.toString(numRows + 1)) 229 .concat("\" x:FullColumns=\"1\" ")); 230 addText(working, "x:FullRows=\"1\"> "); 231 return new String(working); 232 } 233 /** 234 * Generate the XML text to appear at the end of a spreadsheet 235 * within the workbook. 236 * 237 * @return XML code to end spreadsheet 238 */ 239 private String endWorksheet() 240 { 241 StringBuffer working = new StringBuffer(); 242 addText(working, "</Table>"); 243 addText(working, "</Worksheet>"); 244 return new String(working); 245 } 246 /** 247 * Generate header line for spreadsheet. 248 * 249 * <p>Overriding this method should allow the contents of the header line 250 * to be changed as desired.</p> 251 * <p>This method is normally called from the newWorksheet methods.</p> 252 * @param rs Result Set from SQL query 253 * @param meta ResultSetMetaData from SQL query 254 * @param columns XML statements specifying formatting 255 * of columns 256 */ 257 protected String generateWorksheetHeader(ResultSet rs, 258 ResultSetMetaData meta, String columns) throws SQLException 259 { 260 StringBuffer working = new StringBuffer(); 261 if (columns != null) 262 { addText(working, columns); } 263 // String colTypes[]; 264 String colNames[]; 265 // int typeCodes[]; 266 try 267 { 268 numCols = meta.getColumnCount(); 269 // colTypes = new String[numCols+1]; 270 colNames = new String[numCols+1]; 271 addText(working, "<Row> "); 272 for (int i = 1; i <= numCols; i++) 273 { 274 colNames[i] = meta.getColumnLabel(i); 275 if (colNames[i] == null) 276 { 277 addText(working, "<Cell ss:StyleID=\"s23\" /> "); 278 } 279 else if (colNames[i].trim().length() == 0) 280 { 281 addText(working, "<Cell ss:StyleID=\"s23\" /> "); 282 } 283 else 284 { 285 addText(working, "<Cell ss:StyleID=\"s23\"> " 286 .concat("<Data ss:Type=\"String\">") 287 .concat(colNames[i]) 288 .concat("</Data>") 289 .concat("</Cell> ")); 290 } 291 } 292 addText(working, "</Row>"); 293 } 294 catch (SQLException e) 295 { 296 e.printStackTrace(System.out); 297 } 298 return new String(working); 299 } 300 /** 301 * Use the contents of a ResultSet to create an Excel spreadsheet. 302 * @param worksheetName Name to be used for Excel spreadsheet. 303 * @param rs ResultSet object to be used. 304 * @return XML source for Excel spreadsheet 305 * @see #newWorksheet(String, ResultSet, String) 306 */ 307 public String newWorksheet(String worksheetName, ResultSet rs) 308 throws SQLException 309 { 310 return newWorksheet(worksheetName, rs, 311 (String) null); 312 } 313 /** 314 * Use the contents of a ResultSet to create an Excel spreadsheet. 315 * 316 * This version allows the width of the columns to be set by the 317 * calling program. 318 * @param worksheetName Name to be used for Excel spreadsheet 319 * @param rs ResultSet object to be used 320 * @param columns String to be inserted into spreadsheet to define 321 * widths of columns 322 * @return XML source for Excel spreadsheet 323 */ 324 public String newWorksheet(String worksheetName, ResultSet rs, 325 String columns) throws SQLException 326 { return newWorksheet(worksheetName, rs, columns, -1); } 327 /** 328 * Use the contents of a ResultSet to create an Excel worksheet. 329 * 330 * This version allows the width of the columns to be set by the 331 * calling program. 332 * 333 * @see #newWorksheet(String, ResultSet) 334 * @see #newWorksheet(String, ResultSet, String) 335 * 336 * @param worksheetName Name to be used for Excel worksheet 337 * @param rs ResultSet object to be used 338 * @param columns String to be inserted into spreadsheet to define 339 * widths of columns 340 * @param breakColumn This is the number of the column that will be 341 * used for bursting the report 342 * @return XML source for Excel spreadsheet 343 */ 344 public String newWorksheet(String worksheetName, ResultSet rs, 345 String columns, int breakColumn) throws SQLException 346 { 347 boolean firstRow = true; 348 String burstValue = null; 349 String oldBurstValue = null; 350 numRows = 0; 351 numCols = 0; 352 if (breakColumn > 0) 353 { 354 System.out.println("Breaking on column " + 355 Integer.toString(breakColumn)); 356 } 357 /* 358 * working is the central part of the current worksheet 359 * 360 * bigWorking will be a concatenation of all of the 361 * spreadsheets in the workbook 362 */ 363 StringBuffer working = null; 364 StringBuffer bigWorking = null; 365 working = new StringBuffer(); 366 bigWorking = new StringBuffer(); 367 if (debugLevel > 0) 368 { 369 System.out.println("Processing result set started at " 370 .concat(new java.util.Date().toString())); 371 } 372 String colTypes[]; 373 String colNames[]; 374 int typeCodes[]; 375 ResultSetMetaData meta = null; 376 try 377 { 378 meta = rs.getMetaData(); 379 numCols = meta.getColumnCount(); 380 colTypes = new String[numCols+1]; 381 colNames = new String[numCols+1]; 382 typeCodes = new int[numCols+1]; 383 for (int i = 1; i <= numCols; i++) 384 { 385 colNames[i] = meta.getColumnLabel(i); 386 typeCodes[i] = meta.getColumnType(i); 387 /* 388 ** There are approximately 30 different 389 ** codes for column types 390 */ 391 switch (typeCodes[i]) 392 { 393 case java.sql.Types.CHAR: 394 colTypes[i] = new String ("CHAR"); 395 break; 396 case java.sql.Types.DATE: 397 colTypes[i] = new String ("DATE"); 398 break; 399 case java.sql.Types.DECIMAL: 400 colTypes[i] = new String ("DECIMAL"); 401 break; 402 case java.sql.Types.DOUBLE: 403 colTypes[i] = new String ("DOUBLE"); 404 break; 405 case java.sql.Types.FLOAT: 406 colTypes[i] = new String ("FLOAT"); 407 break; 408 case java.sql.Types.INTEGER: 409 colTypes[i] = new String ("INTEGER"); 410 break; 411 case java.sql.Types.NUMERIC: 412 colTypes[i] = new String("NUMERIC"); 413 break; 414 case java.sql.Types.OTHER: 415 colTypes[i] = new String ("OTHER"); 416 break; 417 case java.sql.Types.REAL: 418 colTypes[i] = new String("REAL"); 419 break; 420 case java.sql.Types.SMALLINT: 421 colTypes[i] = new String ("SMALLINT"); 422 break; 423 case java.sql.Types.TIME: 424 colTypes[i] = new String ("TIME"); 425 break; 426 case java.sql.Types.TIMESTAMP: 427 colTypes[i] = new String ("TIMESTAMP"); 428 break; 429 case java.sql.Types.TINYINT: 430 colTypes[i] = new String ("TINYINT"); 431 break; 432 case java.sql.Types.VARCHAR: 433 colTypes[i] = new String ("VARCHAR"); 434 break; 435 default: 436 colTypes[i] = new String ("UNKNOWN"); 437 break; 438 } 439 System.out.println(colNames[i].concat(" ") 440 .concat(colTypes[i])); 441 } // End of loop for determining types of columns 442 System.out.flush(); 443 /* 444 ** 445 ** By using the condition 446 ** (rs.next() && numRows < 500) 447 ** you limit yourself to the 448 ** first 2000 rows or the size of 449 ** the ResultSet, whichever is smaller 450 ** 451 ** This allows repeated testing of the 452 ** Java code. 453 */ 454 while (rs.next()) 455 // while (rs.next() && numRows < 500) 456 { 457 numRows = numRows + 1; 458 if (numRows % 1000 == 0 && numRows > 10) 459 { 460 System.out.println(Integer.toString(numRows) 461 .concat(" records processed at ") 462 .concat(new java.util.Date().toString())); 463 } 464 /* 465 ** Create new spreadsheet if value of break 466 ** column changes. 467 */ 468 if ((breakColumn > 0) && firstRow) 469 { 470 burstValue = rs.getString(breakColumn); 471 oldBurstValue = rs.getString(breakColumn); 472 firstRow = false; 473 } 474 else if ((breakColumn > 0) && !firstRow) 475 { 476 burstValue = rs.getString(breakColumn); 477 if (!burstValue.equals(oldBurstValue)) 478 { 479 System.out.println(Integer.toString(numRows) + 480 " rows for " + oldBurstValue); 481 bigWorking 482 .append(startWorksheet(oldBurstValue, numRows, numCols)) 483 .append(generateWorksheetHeader(rs, meta, columns)) 484 .append(new String(working)) 485 .append(endWorksheet(numRows, numCols)); 486 working = new StringBuffer(); 487 oldBurstValue = burstValue; 488 numRows = 1; 489 } 490 } 491 addText(working, "<Row>"); 492 for (int i = 1; i <= numCols; i++) 493 { 494 switch (typeCodes[i]) 495 { 496 case java.sql.Types.CHAR: 497 case java.sql.Types.VARCHAR: 498 if (rs.getString(i) == null) 499 { 500 addText(working, "<Cell ss:StyleID=\"s23\" />"); 501 } 502 else if (rs.getString(i).trim().length() == 0) 503 { 504 addText(working, "<Cell ss:StyleID=\"s23\" />"); 505 } 506 else 507 { 508 addText(working, "<Cell ss:StyleID=\"s23\">" 509 .concat("<Data ss:Type=\"String\">") 510 .concat(rs.getString(i)) 511 .concat("</Data>") 512 .concat("</Cell> ")); 513 } 514 break; 515 /* 516 ** Changed from float to double on 05-April-2006 517 */ 518 case java.sql.Types.DOUBLE: 519 case java.sql.Types.FLOAT: 520 case java.sql.Types.NUMERIC: 521 addText(working, "<Cell><Data ss:Type=\"Number\">" 522 .concat(Double.toString(rs.getDouble(i))) 523 .concat("</Data>") 524 .concat("</Cell> ")); 525 break; 526 case java.sql.Types.DATE: 527 case java.sql.Types.TIMESTAMP: 528 if (rs.getDate(i) == null) 529 { 530 addText(working, "<Cell ss:StyleID=\"s24\" />"); 531 } 532 else 533 { 534 SimpleDateFormat formatter = 535 new SimpleDateFormat("yyyy-MM-dd"); 536 addText(working, "<Cell ss:StyleID=\"s24\">" 537 .concat("<Data ss:Type=\"DateTime\">") 538 .concat(formatter.format(rs.getDate(i))) 539 .concat("T00:00:00.000") 540 .concat("</Data>") 541 .concat("</Cell> ")); 542 } 543 break; 544 default: 545 if (rs.getString(i) == null) 546 { 547 addText(working, "<Cell />"); 548 } 549 else if (rs.getString(i).trim().length() == 0) 550 { 551 addText(working, "<Cell />"); 552 } 553 else 554 { 555 addText(working, "<Cell><Data ss:Type=\"String\">" 556 .concat(rs.getString(i)) 557 .concat("</Data>") 558 .concat("</Cell> ")); 559 } 560 break; 561 } // End of switch statement 562 } 563 addText(working, "</Row>"); 564 } // end of loop for columns in row 565 /* 566 ** This is the end of the loop for processing the rows from 567 ** the result set. 568 */ 569 System.out.println("Processing result set finished at " 570 .concat(new java.util.Date().toString())); 571 if (breakColumn > 0) 572 { 573 System.out.println("There were " 574 + Integer.toString(numRows) + " rows for " + 575 burstValue); 576 } 577 else 578 { 579 System.out.println("There were " 580 .concat(Integer.toString(numRows)).concat(" rows.")); 581 } 582 } // end of try block 583 catch (SQLException e) 584 { 585 System.out.flush(); 586 System.out.println("SQLException exception handler"); 587 System.out.println(Integer.toString(numRows).concat(" records")); 588 System.out.println(e.getMessage()); 589 e.printStackTrace(System.out); 590 throw e; 591 } 592 catch (Exception e) 593 { 594 System.out.flush(); 595 System.out.println("Default exception handler"); 596 System.out.println(Integer.toString(numRows).concat(" records")); 597 e.printStackTrace(System.out); 598 } 599 String tempName = null; 600 if (breakColumn > 0) 601 { tempName = burstValue; } 602 else 603 { tempName = worksheetName; } 604 return new String(bigWorking 605 .append(startWorksheet(tempName, numRows, numCols)) 606 .append(generateWorksheetHeader(rs, meta, columns)) 607 .append(new String(working)) 608 .append(endWorksheet(numRows, numCols))); 609 } 610 /** 611 * Use the contents of a file to create an Excel worksheet. 612 * @param worksheetName Name of worksheet to be created. 613 * @param inputFile Name of file to be read 614 * @return XML string containing Excel worksheet 615 */ 616 public String newWorksheet(String worksheetName, java.io.File inputFile) 617 { 618 String results = null; 619 try 620 { 621 BufferedReader in = new BufferedReader(new FileReader(inputFile)); 622 results = newWorksheet(worksheetName, in); 623 } 624 catch (java.io.IOException e) 625 { 626 System.out.println("*** Error in SpreadsheetHelpers.newWorksheet"); 627 System.out.println("(java.lang.String, java.io.File)"); 628 System.out.println("java.io.IOException"); 629 e.printStackTrace(System.out); 630 } 631 return results; 632 } 633 /** 634 * Use the contents of a String object to create an Excel spreadsheet. 635 * @param worksheetName Name of spreadsheet to be created. 636 * @param input String object to be read 637 * @return XML string containing Excel spreadsheet 638 */ 639 public String newWorksheet(String worksheetName, String input) 640 { 641 String results = null; 642 BufferedReader in = null; 643 in = new BufferedReader(new StringReader(input)); 644 results = newWorksheet(worksheetName, in); 645 return results; 646 } 647 /** 648 * Use java.io.Reader object to create an Excel worksheet. 649 * @param worksheetName Name of worksheet to be created. 650 * @param in String object to be read 651 * @return XML string containing Excel worksheet 652 */ 653 public String newWorksheet(String worksheetName, java.io.BufferedReader in) 654 { 655 numRows = 1; 656 StringBuffer working = new StringBuffer(); 657 addText(working, "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" " 658 .concat("ss:Width=\"350.0\" />")); 659 try 660 { 661 numRows = 0; 662 String newLine = null; 663 String modifiedLine = null; 664 while ((newLine = in.readLine()) != null) 665 { 666 modifiedLine = newLine.replaceAll("&", "&") 667 .replaceAll("\'", "'") 668 .replaceAll("\"", """) 669 .replaceAll("<", "<") 670 .replaceAll(">", ">"); 671 addText (working, "<Row><Cell ss:StyleID=\"s23\">" 672 .concat("<Data ss:Type=\"String\">") 673 .concat (modifiedLine) 674 .concat ("</Data></Cell></Row>")); 675 numRows = numRows + 1; 676 } 677 } 678 catch (java.io.IOException e) 679 { 680 System.out.println("*** Error in SpreadsheetHelpers.newWorksheet"); 681 System.out.println("(java.lang.String, java.io.Reader)"); 682 System.out.println("java.io.IOException"); 683 e.printStackTrace(System.out); 684 } 685 return new String(startWorksheet(worksheetName, numRows, 1) 686 .concat(new String(working)) 687 .concat(endWorksheet())); 688 } 689 /** 690 * Generates the ending string for an Excel Workbook in XML format. 691 * @return Ending string for workbook. 692 */ 693 public String endWorkbook() 694 { 695 StringBuffer working = new StringBuffer(); 696 addText(working, "</Workbook>"); 697 return new String(working); 698 } 699 /* 700 ** This version of endWorksheet is for use with the results 701 ** of the SQL Query and enables autofiltering 702 */ 703 private String endWorksheet(int numRows, int numCols) 704 { 705 StringBuffer working = new StringBuffer(); 706 addText(working, "</Table> "); 707 addText(working, "<WorksheetOptions "); 708 addText(working, "xmlns=\"urn:schemas-microsoft-com:office:excel\"> "); 709 addText(working, "<Selected/> "); 710 addText(working, "<FreezePanes /> "); 711 addText(working, "<SplitHorizontal>1</SplitHorizontal> "); 712 addText(working, "<TopRowBottomPane>1</TopRowBottomPane> "); 713 addText(working, "<ActivePane>2</ActivePane> "); 714 addText(working, "<Panes> "); 715 addText(working, "<Pane> "); 716 addText(working, "<Number>2</Number> "); 717 addText(working, "</Pane> "); 718 addText(working, "<Pane> "); 719 addText(working, "<Number>3</Number> "); 720 addText(working, "</Pane> "); 721 addText(working, "</Panes> "); 722 addText(working, "<ProtectObjects>False</ProtectObjects> "); 723 addText(working, "<ProtectScenarios>False</ProtectScenarios> "); 724 addText(working, "</WorksheetOptions> "); 725 addText(working, "<AutoFilter x:Range=\"R1C1:R" 726 .concat(Integer.toString(numRows + 1)) 727 .concat("C") 728 .concat(Integer.toString(numCols)) 729 .concat("\" xmlns=\"urn:schemas-microsoft-com:office:excel\" /> ")); 730 addText(working, "</Worksheet>"); 731 return new String(working); 732 } 733}