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