Provides routine for saving workbook file

SummarizeFoTResults.java (text/plain), 9.87 KB, created by David Wilt on 2013-11-21 16:37:40 UTC

(hide)

Description: Provides routine for saving workbook file

Filename:

MIME Type:

Creator: David Wilt

Created: 2013-11-21 16:37:40 UTC

Size: 9.87 KB

patch

obsolete

>/** David L. Wilt (dwilt@iotatx.com) >SummarizeFoTResults.java >Nov 15, 2013 > * > */ >package sample; > >import java.io.*; > >import javax.swing.JFileChooser; > >import org.apache.poi.openxml4j.exceptions.InvalidFormatException; >import org.apache.poi.openxml4j.opc.OPCPackage; >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.CellStyle; >import org.apache.poi.ss.usermodel.DataFormat; >import org.apache.poi.ss.usermodel.Row; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.xssf.usermodel.XSSFSheet; >//import org.apache.poi.xssf.usermodel.XSSFTable; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; > >/** > * @author david > * > */ >public class SummarizeFoTResults { > // Variable > static {System.setProperty("java.awt.headless", "false");} > String sumFileNm = null; > File sumFile = null; > XSSFWorkbook wbSum = null; > String sumSheetNm = "Combinations"; //TODO: Change sheet name > int iNumMetrics = 24; // Current number of metrics reported for each comb > int iStartCombRow = 9; //Start row for comb metrics on comb page > int iStartSumRow; //row for first new comb added to wb > int iNewCombs; // Number of new combinations added > int iStartSumCol; //Starting column on Summary sheet to accept Comb metrics > //TODO: Automate discovery of number metrics and start row > int iCombMetricsCol = 1; //Column for comb metrics TODO: Implement auto discovery > int iCombMetricsHeadersCol = 0; > int iNumRowCells = 0; > //Format strings > String fmtPercent = "0.00%"; > String fmtInteger = "#,##0"; > String fmtDouble = "#,##0.00"; > String fmtDollar = "$#,##0.00"; > /** > * @param args > * @throws IOException > * @throws InvalidFormatException > */ > > public static void main(String[] Args) throws InvalidFormatException, IOException{ > File fSum = null; > int nFactors = 4; > SummarizeFoTResults cSFOT = new SummarizeFoTResults(); > cSFOT.initSummarize(fSum, nFactors); > } > public void initSummarize(File fSum, int nFactors) throws InvalidFormatException, IOException { > // TODO: Testing- Select summary file > > // Number of cells in newly added row, which need comb metrics. Add one for Comb # col > iNumRowCells = (nFactors == 0) ? 5 : nFactors + 1; > // Create summary rows > // Read Summary workbook > XSSFSheet wsSumSheet = PrepforUpdate(fSum); > if (wsSumSheet == null) > return; > // Create new cells for comb metrics > CreateNewCells(wsSumSheet); > // > CopyCombMetrics(wsSumSheet); > FormatSumSheet(); > writeFile(wbSum, sumFileNm); > > UserOutput.main("Combination Metrics added to Summary File " + sumFileNm); > System.exit(0); > } > > public XSSFSheet PrepforUpdate(File fSum) throws InvalidFormatException, IOException{ > // first row of result metrics on comb sheets > if (fSum == null){ //Using in test mode - Select summary file > FileSelector clFS = new FileSelector(); > sumFileNm = clFS.selectFile("xlsx"); > sumFile = new File(sumFileNm);} > /** > final JFileChooser fc = new JFileChooser(); > fc.setVisible(true); > //In response to a button click: > int returnVal = fc.showOpenDialog(null); > File selFile = null; > if (returnVal == JFileChooser.APPROVE_OPTION) { > sumFile= fc.getSelectedFile(); > sumFileNm = sumFile.getAbsolutePath(); > } > */ > else{ > sumFile = fSum; > sumFileNm = sumFile.getAbsolutePath();} > > if (!sumFile.isFile()){ > UserOutput.main("Summary File not found. Cancelling results consolidation."); > return null;} > else > UserOutput.main("Summary File found. Beginning results consolidation."); > > wbSum = getWorkBook(sumFile); > XSSFSheet sumSheet = wbSum.getSheet(sumSheetNm); > //Determine start row: Look for first short row > //iStartSumRow= 1; // Row for first new comb TODO: pass value from WriteExcel routine > iStartSumRow = CalculateStartRow(sumSheet); > return sumSheet; > } > > private int CalculateStartRow(XSSFSheet sumSheet){ > // Identify the first row to begin adding comb metrics > int iStartRow = 1; > for (int iRow = 0; iRow < sumSheet.getLastRowNum(); iRow++){ > if (sumSheet.getRow(iRow).getLastCellNum() > iNumRowCells) > continue; > else > // If no rows, start at 1: Row 0 takes headers > iStartRow = (iRow > 0) ? iRow : 1; > } > UserOutput.main("Start Row " + iStartRow); > return iStartRow; > > } > private static XSSFWorkbook getWorkBook(File f) throws FileNotFoundException, > IOException, InvalidFormatException { > //System.out.println("WorkBook reading " + f.getAbsolutePath()); > InputStream is_Sum = new FileInputStream(f); > OPCPackage pkg = OPCPackage.open(is_Sum); > XSSFWorkbook wb = new XSSFWorkbook(pkg); > System.out.println("Workbook " + f.getName().toString()); > is_Sum.close(); > return wb; > } > // Creates cells on Summary Sheet to hold combination metrics > > public void CreateNewCells(Sheet wsSumSheet){ > Row rSumRow = null; > int iFirstRow = iStartSumRow; > // Calculate number of new combs > iNewCombs = wsSumSheet.getLastRowNum() - iStartSumRow + 1; //Row count starts at 0 > // Iterate over combinations > // Add headers if writing for first time > if (iStartSumRow == 1) {// first time > // Fill Header Row > //Use any comb sheet > rSumRow = wsSumSheet.getRow(0); > iStartSumCol = rSumRow.getLastCellNum(); > InsertCells(rSumRow); > FillRows2(wsSumSheet.getRow(0), wbSum.getSheetAt(1), iCombMetricsHeadersCol); > } > for (int iSumRow = iFirstRow; iSumRow < (iFirstRow + iNewCombs); iSumRow++){ > // get row > rSumRow = wsSumSheet.getRow(iSumRow); > iStartSumCol = rSumRow.getLastCellNum(); > InsertCells(rSumRow); > } > } > public void InsertCells(Row rSumRow){ > for (int iSumCol = iStartSumCol; iSumCol < (iStartSumCol + iNumMetrics); iSumCol++ ){ > rSumRow.createCell(iSumCol); > } > System.out.println("Number of cells in row " + rSumRow.getRowNum() +"=" + rSumRow.getLastCellNum()); > } > > public void CopyCombMetrics(Sheet wsSumSheet){ > // Identify first new comb sheet (= iStarSumtRow) > Sheet wsCombSheet = null; > int iFirstCombSheet = iStartSumRow; > // Cycle through new comb sheets - copy metrics and styles to comb sheet > for (int iCombSheet = iFirstCombSheet; iCombSheet < (iFirstCombSheet + iNewCombs); iCombSheet++){ > wsCombSheet = wbSum.getSheetAt(iCombSheet); > UserOutput.main(" Processing Comb Sheet " + wsCombSheet.getSheetName()); > Row rSumSheetRow = wsSumSheet.getRow(iCombSheet); // Counters match for row and comb sheet numbers > // Cycle through Sum sheet cells, copying value from Comb Sheet > FillRows(rSumSheetRow, wsCombSheet, iCombMetricsCol); > } > } > public void FillRows2(Row rSumSheetRow, Sheet wsCombSheet, int iSourceCol){ > int jCombRow = iStartCombRow; > for (Cell cell : rSumSheetRow){ > if (cell.getCellType() == Cell.CELL_TYPE_BLANK){ > cell.setCellValue(wsCombSheet.getRow(jCombRow).getCell(iSourceCol).getRichStringCellValue()); > > //System.out.println("Filling column # " + cell.getColumnIndex() + " from Row " + jCombRow); > //System.out.println("Cell value " + cell.getRichStringCellValue()); > jCombRow ++; > } > } > } > > public void FillRows(Row rSumSheetRow, Sheet wsCombSheet, int iSourceCol){ > Cell curSumCell = null; > String strVal = null; //Comb metric (saved as strings > for (int iColSum = iStartSumCol, jCombRow = iStartCombRow; > iColSum < (rSumSheetRow.getLastCellNum()); iColSum ++, jCombRow++){ > //System.out.println("Filling column # " + iColSum + " from Row " + jCombRow); > curSumCell = rSumSheetRow.getCell(iColSum); > curSumCell.setCellType(Cell.CELL_TYPE_NUMERIC); > //String strValue = wsCombSheet.getRow(jCombRow).getCell(iSourceCol).getStringCellValue(); > // Set format for sumcell based on comb metric (string) value > curSumCell.setCellValue(wsCombSheet.getRow(jCombRow).getCell(iSourceCol).getStringCellValue()); > // Format cell based on comb value contents (e.g., $, %) > formatCell(curSumCell); > // Value may have been changed to accommodate format > strVal = curSumCell.getStringCellValue(); > // Remove all non-numeric characters, except "." > strVal = strVal.replaceAll("[^\\d.]", ""); > // Set sum sheet value as double - This allows sorting and math functions > curSumCell.setCellValue(Double.parseDouble(strVal)); > } > } > > public Cell formatCell(Cell cell){ > CellStyle style; > String selFmt = null; > String cellVal = cell.getStringCellValue(); > DataFormat format = wbSum.createDataFormat(); > style = wbSum.createCellStyle(); > //cellVal = cell.getStringCellValue(); > if (cellVal.contains("$")) > selFmt = fmtDollar; > else if (cellVal.contains("%")){ > selFmt = fmtPercent; > //Multiply by 0.01 for correct percentage value > double curVal = Double.parseDouble(cellVal.substring(0,cellVal.length()-1)); > curVal = curVal*.01; > cell.setCellValue(String.valueOf(curVal)); > //System.out.println("Cell value " + cell.getStringCellValue()); > } > else if (cellVal.contains(".")) > selFmt = fmtDouble; > else selFmt = fmtInteger; > > style.setDataFormat(format.getFormat(selFmt)); > cell.setCellStyle(style); > //System.out.println("Cell Style " + cell.getCellStyle().toString()); > > return cell; > } > public void FormatSumSheet(){ > System.setProperty("java.awt.headless", "true"); > Row rSumSheetHrdrRow = wbSum.getSheetAt(0).getRow(0); > CellStyle cAlign = wbSum.createCellStyle(); > org.apache.poi.ss.usermodel.Font cfont = wbSum.createFont(); > cfont.setBoldweight(cfont.BOLDWEIGHT_BOLD); > cAlign.setAlignment(CellStyle.ALIGN_CENTER); > cAlign.setFont(cfont); > for (int iCol=0; iCol < rSumSheetHrdrRow.getLastCellNum(); iCol++){ > rSumSheetHrdrRow.getCell(iCol).setCellStyle(cAlign); > wbSum.getSheetAt(0).autoSizeColumn(iCol); > } > System.setProperty("java.awt.headless", "false"); > } > > public void writeFile(Workbook wb, String fName) throws IOException{ > // Write the output to a file > //TODO: Consolidate > FileOutputStream fileOut = new FileOutputStream(fName); > wb.write(fileOut); > fileOut.close(); > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]