package ted.spreadsheet; import java.io.*; import java.util.*; import org.apache.poi.hssf.usermodel.*; //RecordGenerator; /** * Utility class that has various functions to create a Excel Spreadsheet */ public class WbPOI3 { private String filename = "Workbook.xls"; private HSSFWorkbook wb = new HSSFWorkbook(); private String delimit = "_"; private boolean Created = true; private int curRow = 0; private int currentSheet = 0; //constructor public WbPOI3() { wb = new HSSFWorkbook(); Created = true; } private void buildCols( int NumORow, short NumOCol, String colVals[][], String Name) { HSSFRow row; HSSFSheet sheet; short i; int r; short HeadRows = (short) curRow; sheet = getSheet(); //Make heading row -if provided if (!Name.equals("")) { row = sheet.createRow(HeadRows++); row.createCell((short) 0).setCellValue(Name); } //set each columns max width for (i = 0; i < NumOCol; i++) { setColMaxWidth(i, sheet, colVals[i]); } //loop through each column for (r = 0; r < NumORow; r++) { //create each row row = sheet.createRow((short) (r + HeadRows)); //loop through each column in the row for (i = 0; i < NumOCol; i++) { //check to make sure col-i has a row r if (colVals[i].length > r) { row.createCell(i).setCellValue(colVals[i][r]); } } } System.out.println("R= " + Short.MAX_VALUE); curRow = HeadRows + r; } private void buildRows(String rowVals[][], String Name) { HSSFRow row; HSSFSheet sheet; int NumORow = rowVals.length; int len, r; short i; short HeadRows = (short) curRow; sheet = getSheet(); //Make heading row -if provided if (!Name.equals("")) { row = sheet.createRow(HeadRows++); row.createCell((short) 0).setCellValue(Name); } for (r = 0; r < NumORow; r++) { //create each row row = sheet.createRow((short) (r + HeadRows)); //loop through each column in the row len = rowVals[r].length; for (i = 0; i < len; i++) { row.createCell(i).setCellValue(rowVals[r][i]); if (sheet.getColumnWidth(i) < (rowVals[r][i].length() * 256) + 256) sheet.setColumnWidth(i, (short) ((rowVals[r][i].length() * 256) + 256)); } } curRow = HeadRows + r; } public boolean created() { return Created; } /** * Insert the method's description here. * Creation date: (10/13/2004 5:10:57 PM) * @return java.lang.String * @param cell org.apache.poi.hssf.usermodel.HSSFCell */ private String getCellValue(HSSFCell cell) { int CellType; CellType = cell.getCellType(); switch(CellType) { case cell.CELL_TYPE_BLANK: return ""; case cell.CELL_TYPE_NUMERIC: return String.valueOf(cell.getNumericCellValue()); case cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return ""; } } /** * Insert the method's description here. * Creation date: (10/13/2004 4:36:08 PM) * @return int */ public int getCurrentSheet() { return currentSheet; } /** * Insert the method's description here. * Creation date: (10/13/2004 4:42:42 PM) * @return java.lang.String[][] */ public String[][] getRows() { HSSFRow row; HSSFCell cell; HSSFSheet sheet = wb.getSheetAt(currentSheet); short rowNum; int lastCol; short colNum; int lastRow = sheet.getLastRowNum(); String [][] rowVals = new String[lastRow + 1][]; for(rowNum = 0;rowNum <= lastRow;rowNum++) { row = sheet.getRow(rowNum); lastCol = row.getLastCellNum(); rowVals[rowNum] = new String[lastCol]; for(colNum = 0;colNum < lastCol;colNum++) { cell = row.getCell(colNum); rowVals[rowNum][colNum] = getCellValue(cell); } } return rowVals; } private HSSFSheet getSheet() { HSSFSheet sheet; int SheetNum; SheetNum = currentSheet; if (SheetNum == 0) { sheet = wb.createSheet("New Sheet"); } else { sheet = wb.getSheetAt(SheetNum); } return sheet; } public boolean newSheet(String shName) { if (!Created) return false; wb.createSheet(shName); currentSheet = wb.getNumberOfSheets() - 1; curRow = 0; return true; } /** * Insert the method's description here. * Creation date: (10/13/2004 4:08:36 PM) */ public void openFile(String fileName) { FileInputStream f; try { f = new FileInputStream(fileName); wb = new HSSFWorkbook(f); } catch (Exception e) { Created = false; return; } Created = true; curRow = 0; } //method searches through a string array and finds the widest one //it then sets the column to the width private static void setColMaxWidth( short ColNum, HSSFSheet curSheet, String s[]) { int i, max, len; len = s.length; max = 0; //find longest for (i = 0; i < len; i++) { if (s[i].length() > max) { max = s[i].length(); } } //set column width curSheet.setColumnWidth((short) ColNum, (short) ((max * 256) + 256)); } public boolean setCols(String colVals[][]) { return setCols(colVals, ""); } public boolean setCols(String colVals[][], String name) { if (!Created) return false; //find max rows int NumORow = 0, i; short len; len = (short) colVals.length; for (i = 0; i < len; i++) { if (colVals[i].length > NumORow) { NumORow = colVals[i].length; } } buildCols(NumORow, len, colVals, name); return true; } public boolean setCols(String param[]) { return setCols(param, ""); } public boolean setCols(String param[], String Heading) { int len; if (!Created) return false; String colVals[][]; colVals = new String[param.length][]; short NumOCol = 0; short i; int NumORow = 0; //Split up Columns and find Number of Columns len = param.length; while (NumOCol < len) { colVals[NumOCol] = splitString(param[NumOCol], delimit); NumOCol++; } //find max number of rows for (i = 0; i < NumOCol; i++) { //check each column to see how many rows it has if (NumORow < colVals[i].length) { NumORow = colVals[i].length; } } // *************Build the workbook******************** buildCols(NumORow, NumOCol, colVals, Heading); return true; } /** * Insert the method's description here. * Creation date: (10/13/2004 4:36:08 PM) * @param newCurrentSheet int */ public boolean setCurrentSheet(int newCurrentSheet) { if(newCurrentSheet < wb.getNumberOfSheets()) { currentSheet = newCurrentSheet; return true; } else { return false; } } public boolean setDelimiter(String d) { if (d.length() > 0 && d.length() < 4) { delimit = d; return true; } else { return false; } } public boolean setRows(String rowVals[][]) { return setRows(rowVals, ""); } public boolean setRows(String rowVals[][], String Name) { if (!Created) return false; buildRows(rowVals, Name); return true; } public boolean setRows(String param[], String Heading) { if (!Created) return false; int NumORow = 0, len; String rowVals[][]; rowVals = new String[param.length][]; short i; //Split up Columns and find Number of Columns len = param.length; while (NumORow < len) { rowVals[NumORow] = splitString(param[NumORow], delimit); NumORow++; } // ****************build the workbook*************** buildRows(rowVals, Heading); return true; } private String[] splitString(String s, String delimit) { String word; String sa[]; int wordNum = 0, wordNum2 = 0; boolean foundEnd = false; int pos = 0; //find number of entries do { pos = s.indexOf(delimit, pos + 1); if (pos != -1) wordNum++; } while (pos != -1); sa = new String[wordNum]; wordNum2 = 0; pos = 0; do { sa[wordNum2] = s.substring(pos, s.indexOf(delimit, pos + 1)); pos = (s.indexOf(delimit, pos)) + 1; wordNum2++; } while (wordNum2 < wordNum); return sa; } public boolean writeWB(OutputStream os) { if (!Created) return false; try { wb.write(os); } catch (IOException ie) { return false; } return true; } }