/** * Performs a deep copy of the specified cell, handling the cell format * * @param cell the cell to copy */ private WritableCell deepCopyCell(Cell cell) { WritableCell c = shallowCopyCell(cell); if (c == null) { return(c); } if (c is ReadFormulaRecord) { ReadFormulaRecord rfr = (ReadFormulaRecord)c; bool crossSheetReference = !rfr.handleImportedCellReferences (fromSheet.getWorkbook(), fromSheet.getWorkbook(), workbookSettings); if (crossSheetReference) { try { //logger.warn("Formula " + rfr.getFormula() + // " in cell " + // CellReferenceHelper.getCellReference(cell.getColumn(), // cell.getRow()) + // " cannot be imported because it references another " + // " sheet from the source workbook"); } catch (FormulaException e) { //logger.warn("Formula in cell " + // CellReferenceHelper.getCellReference(cell.getColumn(), // cell.getRow()) + // " cannot be imported: " + e.Message); } // Create a new error formula and add it instead c = new Formula(cell.getColumn(), cell.getRow(), "\"ERROR\""); } } // Copy the cell format CellFormat cf = c.getCellFormat(); int index = ((XFRecord)cf).getXFIndex(); WritableCellFormat wcf = null; if (!xfRecords.ContainsKey(index)) { wcf = copyCellFormat(cf); } else { wcf = xfRecords[index]; } c.setCellFormat(wcf); return(c); }
public WritableSheetCopier(WritableSheet f, WritableSheet t) { fromSheet = (WritableSheetImpl)f; toSheet = (WritableSheetImpl)t; workbookSettings = toSheet.getWorkbook().getSettings(); chartOnly = false; }
/** * Adds the format information to the shared records. Performs the necessary * checks (and clones) to ensure that the formats are not shared. * Called from setCellDetails and setCellFormat */ private void addCellFormat() { // Check to see if the format is one of the shared Workbook defaults. If // so, then get hold of the Workbook's specific instance Styles styles = sheet.getWorkbook().getStyles(); format = styles.getFormat(format); try { if (!format.isInitialized()) { formattingRecords.addStyle(format); } } catch (NumFormatRecordsException e) { //logger.warn("Maximum number of format records exceeded. Using default format."); format = styles.getNormalStyle(); } }
/** * Overrides the method in the base class to add this to the Workbook's * list of maintained formulas * * @param fr the formatting records * @param ss the shared strings used within the workbook * @param s the sheet this is being added to */ public override void setCellDetails(FormattingRecords fr, SharedStrings ss, WritableSheetImpl s) { base.setCellDetails(fr, ss, s); s.getWorkbook().addRCIRCell(this); }
/** * Called when the cell is added to the worksheet. Overrides the * method in the base class in order to get a handle to the * WorkbookSettings so that this formula may be initialized * * @param fr the formatting records * @param ss the shared strings used within the workbook * @param s the sheet this is being added to */ public override void setCellDetails(FormattingRecords fr, SharedStrings ss, WritableSheetImpl s) { base.setCellDetails(fr, ss, s); initialize(s.getWorkbookSettings(), s.getWorkbook(), s.getWorkbook()); s.getWorkbook().addRCIRCell(this); }
public SheetCopier(Sheet f, WritableSheet t) { fromSheet = (SheetImpl)f; toSheet = (WritableSheetImpl)t; workbookSettings = toSheet.getWorkbook().getSettings(); chartOnly = false; }
/** * Copies a sheet from a read-only version to the writable version. * Performs shallow copies */ public void copySheet() { shallowCopyCells(); // Copy the column formats foreach (ColumnInfoRecord cv in fromColumnFormats) { toColumnFormats.Add(cv); } // Copy the merged cells Range[] merged = fromMergedCells.getMergedCells(); for (int i = 0; i < merged.Length; i++) { toMergedCells.add(new SheetRangeImpl((SheetRangeImpl)merged[i], toSheet)); } try { RowRecord row = null; RowRecord newRow = null; for (int i = 0; i < fromRows.Length; i++) { row = fromRows[i]; if (row != null && (!row.isDefaultHeight() || row.isCollapsed())) { newRow = toSheet.getRowRecord(i); newRow.setRowDetails(row.getRowHeight(), row.matchesDefaultFontHeight(), row.isCollapsed(), row.getOutlineLevel(), row.getGroupStart(), row.getStyle()); } } } catch (RowsExceededException e) { // Handle the rows exceeded exception - this cannot occur since // the sheet we are copying from will have a valid number of rows Assert.verify(false); } // Copy the horizontal page breaks toRowBreaks = new ArrayList(fromRowBreaks); // Copy the vertical page breaks toColumnBreaks = new ArrayList(fromColumnBreaks); // Copy the data validations if (fromDataValidation != null) { toDataValidation = new DataValidation (fromDataValidation, toSheet.getWorkbook(), toSheet.getWorkbook(), toSheet.getWorkbook().getSettings()); } // Copy the charts sheetWriter.setCharts(fromSheet.getCharts()); // Copy the drawings foreach (object o in fromDrawings) { if (o is CSharpJExcel.Jxl.Biff.Drawing.Drawing) { WritableImage wi = new WritableImage ((CSharpJExcel.Jxl.Biff.Drawing.Drawing)o, toSheet.getWorkbook().getDrawingGroup()); toDrawings.Add(wi); toImages.Add(wi); } // Not necessary to copy the comments, as they will be handled by // the deep copy of the individual cells } // Copy the workspace options sheetWriter.setWorkspaceOptions(fromWorkspaceOptions); // Copy the environment specific print record if (fromPLSRecord != null) { toPLSRecord = new PLSRecord(fromPLSRecord); } // Copy the button property set if (fromButtonPropertySet != null) { toButtonPropertySet = new ButtonPropertySetRecord(fromButtonPropertySet); } // Copy the hyperlinks foreach (WritableHyperlink hyperlink in fromHyperlinks) { WritableHyperlink hr = new WritableHyperlink(hyperlink, toSheet); toHyperlinks.Add(hr); } }
/** * Writes out this sheet. First writes out the standard sheet * information then writes out each row in turn. * Once all the rows have been written out, it retrospectively adjusts * the offset references in the file * * @exception IOException */ public void write() { Assert.verify(rows != null); // This worksheet consists of just one chart, so write it and return if (chartOnly) { drawingWriter.write(outputFile); return; } BOFRecord bof = new BOFRecord(BOFRecord.sheet); outputFile.write(bof); // Compute the number of blocks of 32 rows that will be needed int numBlocks = numRows / 32; if (numRows - numBlocks * 32 != 0) { numBlocks++; } int indexPos = outputFile.getPos(); // Write the index record out now in order to serve as a place holder // The bof passed in is the bof of the workbook, not this sheet IndexRecord indexRecord = new IndexRecord(0, numRows, numBlocks); outputFile.write(indexRecord); if (settings.getAutomaticFormulaCalculation()) { CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.automatic); outputFile.write(cmr); } else { CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.manual); outputFile.write(cmr); } CalcCountRecord ccr = new CalcCountRecord(0x64); outputFile.write(ccr); RefModeRecord rmr = new RefModeRecord(); outputFile.write(rmr); IterationRecord itr = new IterationRecord(false); outputFile.write(itr); DeltaRecord dtr = new DeltaRecord(0.001); outputFile.write(dtr); SaveRecalcRecord srr = new SaveRecalcRecord(settings.getRecalculateFormulasBeforeSave()); outputFile.write(srr); PrintHeadersRecord phr = new PrintHeadersRecord(settings.getPrintHeaders()); outputFile.write(phr); PrintGridLinesRecord pglr = new PrintGridLinesRecord(settings.getPrintGridLines()); outputFile.write(pglr); GridSetRecord gsr = new GridSetRecord(true); outputFile.write(gsr); GuttersRecord gutr = new GuttersRecord(); gutr.setMaxColumnOutline(maxColumnOutlineLevel + 1); gutr.setMaxRowOutline(maxRowOutlineLevel + 1); outputFile.write(gutr); DefaultRowHeightRecord drhr = new DefaultRowHeightRecord (settings.getDefaultRowHeight(), settings.getDefaultRowHeight() != SheetSettings.DEFAULT_DEFAULT_ROW_HEIGHT); outputFile.write(drhr); if (maxRowOutlineLevel > 0) { workspaceOptions.setRowOutlines(true); } if (maxColumnOutlineLevel > 0) { workspaceOptions.setColumnOutlines(true); } workspaceOptions.setFitToPages(settings.getFitToPages()); outputFile.write(workspaceOptions); if (rowBreaks.Count > 0) { int[] rb = new int[rowBreaks.Count]; for (int i = 0; i < rb.Length; i++) { rb[i] = (int)rowBreaks[i]; } HorizontalPageBreaksRecord hpbr = new HorizontalPageBreaksRecord(rb); outputFile.write(hpbr); } if (columnBreaks.Count > 0) { int[] rb = new int[columnBreaks.Count]; for (int i = 0; i < rb.Length; i++) { rb[i] = (int)columnBreaks[i]; } VerticalPageBreaksRecord hpbr = new VerticalPageBreaksRecord(rb); outputFile.write(hpbr); } HeaderRecord header = new HeaderRecord(settings.getHeader().ToString()); outputFile.write(header); FooterRecord footer = new FooterRecord(settings.getFooter().ToString()); outputFile.write(footer); HorizontalCentreRecord hcr = new HorizontalCentreRecord(settings.isHorizontalCentre()); outputFile.write(hcr); VerticalCentreRecord vcr = new VerticalCentreRecord(settings.isVerticalCentre()); outputFile.write(vcr); // Write out the margins if they don't equal the default if (settings.getLeftMargin() != settings.getDefaultWidthMargin()) { MarginRecord mr = new LeftMarginRecord(settings.getLeftMargin()); outputFile.write(mr); } if (settings.getRightMargin() != settings.getDefaultWidthMargin()) { MarginRecord mr = new RightMarginRecord(settings.getRightMargin()); outputFile.write(mr); } if (settings.getTopMargin() != settings.getDefaultHeightMargin()) { MarginRecord mr = new TopMarginRecord(settings.getTopMargin()); outputFile.write(mr); } if (settings.getBottomMargin() != settings.getDefaultHeightMargin()) { MarginRecord mr = new BottomMarginRecord(settings.getBottomMargin()); outputFile.write(mr); } if (plsRecord != null) { outputFile.write(plsRecord); } SetupRecord setup = new SetupRecord(settings); outputFile.write(setup); if (settings.isProtected()) { ProtectRecord pr = new ProtectRecord(settings.isProtected()); outputFile.write(pr); ScenarioProtectRecord spr = new ScenarioProtectRecord(settings.isProtected()); outputFile.write(spr); ObjectProtectRecord opr = new ObjectProtectRecord(settings.isProtected()); outputFile.write(opr); if (settings.getPassword() != null) { PasswordRecord pw = new PasswordRecord(settings.getPassword()); outputFile.write(pw); } else if (settings.getPasswordHash() != 0) { PasswordRecord pw = new PasswordRecord(settings.getPasswordHash()); outputFile.write(pw); } } indexRecord.setDataStartPosition(outputFile.getPos()); DefaultColumnWidth dcw = new DefaultColumnWidth(settings.getDefaultColumnWidth()); outputFile.write(dcw); // Get a handle to the normal styles WritableCellFormat normalStyle = sheet.getWorkbook().getStyles().getNormalStyle(); WritableCellFormat defaultDateFormat = sheet.getWorkbook().getStyles().getDefaultDateFormat(); // Write out all the column formats foreach (ColumnInfoRecord cir in columnFormats) { // Writing out the column info with index 0x100 causes excel to crash if (cir.getColumn() < 0x100) { outputFile.write(cir); } XFRecord xfr = cir.getCellFormat(); if (xfr != normalStyle && cir.getColumn() < 0x100) { // Make this the format for every cell in the column Cell[] cells = getColumn(cir.getColumn()); for (int i = 0; i < cells.Length; i++) { if (cells[i] != null && (cells[i].getCellFormat() == normalStyle || cells[i].getCellFormat() == defaultDateFormat)) { // The cell has no overriding format specified, so // set it to the column default ((WritableCell)cells[i]).setCellFormat(xfr); } } } } // Write out the auto filter if (autoFilter != null) { autoFilter.write(outputFile); } DimensionRecord dr = new DimensionRecord(numRows, numCols); outputFile.write(dr); // Write out all the rows, in blocks of 32 for (int block = 0; block < numBlocks; block++) { DBCellRecord dbcell = new DBCellRecord(outputFile.getPos()); int blockRows = System.Math.Min(32, numRows - block * 32); bool firstRow = true; // First write out all the row records for (int i = block * 32; i < block * 32 + blockRows; i++) { if (rows[i] != null) { rows[i].write(outputFile); if (firstRow) { dbcell.setCellOffset(outputFile.getPos()); firstRow = false; } } } // Now write out all the cells for (int i = block * 32; i < block * 32 + blockRows; i++) { if (rows[i] != null) { dbcell.addCellRowPosition(outputFile.getPos()); rows[i].writeCells(outputFile); } } // Now set the current file position in the index record indexRecord.addBlockPosition(outputFile.getPos()); // Set the position of the file pointer and write out the DBCell // record dbcell.setPosition(outputFile.getPos()); outputFile.write(dbcell); } // Do the drawings and charts if enabled if (!workbookSettings.getDrawingsDisabled()) { drawingWriter.write(outputFile); } Window2Record w2r = new Window2Record(settings); outputFile.write(w2r); // Handle the frozen panes if (settings.getHorizontalFreeze() != 0 || settings.getVerticalFreeze() != 0) { PaneRecord pr = new PaneRecord(settings.getHorizontalFreeze(), settings.getVerticalFreeze()); outputFile.write(pr); // Handle the selection record. First, there will always be a top left SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0); outputFile.write(sr); // Top right if (settings.getHorizontalFreeze() != 0) { sr = new SelectionRecord(SelectionRecord.upperRight, settings.getHorizontalFreeze(), 0); outputFile.write(sr); } // Bottom left if (settings.getVerticalFreeze() != 0) { sr = new SelectionRecord(SelectionRecord.lowerLeft, 0, settings.getVerticalFreeze()); outputFile.write(sr); } // Bottom right if (settings.getHorizontalFreeze() != 0 && settings.getVerticalFreeze() != 0) { sr = new SelectionRecord(SelectionRecord.lowerRight, settings.getHorizontalFreeze(), settings.getVerticalFreeze()); outputFile.write(sr); } Weird1Record w1r = new Weird1Record(); outputFile.write(w1r); } else { // No frozen panes - just write out the selection record for the // whole sheet SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0); outputFile.write(sr); } // Handle the zoom factor if (settings.getZoomFactor() != 100) { SCLRecord sclr = new SCLRecord(settings.getZoomFactor()); outputFile.write(sclr); } // Now write out all the merged cells mergedCells.write(outputFile); // Write out all the hyperlinks foreach (WritableHyperlink hlr in hyperlinks) { outputFile.write(hlr); } if (buttonPropertySet != null) { outputFile.write(buttonPropertySet); } // Write out the data validations if (dataValidation != null || validatedCells.Count > 0) { writeDataValidation(); } // Write out the conditional formats if (conditionalFormats != null && conditionalFormats.Count > 0) { foreach (ConditionalFormat cf in conditionalFormats) { cf.write(outputFile); } } EOFRecord eof = new EOFRecord(); outputFile.write(eof); // Now the various cross reference offsets have been calculated, // retrospectively set the values in the output file outputFile.setData(indexRecord.getData(), indexPos + 4); }
/** * Copies a sheet from a read-only version to the writable version. * Performs shallow copies */ public void copySheet() { shallowCopyCells(); // Copy the column info records CSharpJExcel.Jxl.Read.Biff.ColumnInfoRecord[] readCirs = fromSheet.getColumnInfos(); for (int i = 0; i < readCirs.Length; i++) { CSharpJExcel.Jxl.Read.Biff.ColumnInfoRecord rcir = readCirs[i]; for (int j = rcir.getStartColumn(); j <= rcir.getEndColumn(); j++) { ColumnInfoRecord cir = new ColumnInfoRecord(rcir, j, formatRecords); cir.setHidden(rcir.getHidden()); columnFormats.Add(cir); } } // Copy the hyperlinks Hyperlink[] hls = fromSheet.getHyperlinks(); for (int i = 0; i < hls.Length; i++) { WritableHyperlink hr = new WritableHyperlink (hls[i], toSheet); hyperlinks.Add(hr); } // Copy the merged cells Range[] merged = fromSheet.getMergedCells(); for (int i = 0; i < merged.Length; i++) { mergedCells.add(new SheetRangeImpl((SheetRangeImpl)merged[i], toSheet)); } // Copy the row properties try { CSharpJExcel.Jxl.Read.Biff.RowRecord[] rowprops = fromSheet.getRowProperties(); for (int i = 0; i < rowprops.Length; i++) { RowRecord rr = toSheet.getRowRecord(rowprops[i].getRowNumber()); XFRecord format = rowprops[i].hasDefaultFormat() ? formatRecords.getXFRecord(rowprops[i].getXFIndex()) : null; rr.setRowDetails(rowprops[i].getRowHeight(), rowprops[i].matchesDefaultFontHeight(), rowprops[i].isCollapsed(), rowprops[i].getOutlineLevel(), rowprops[i].getGroupStart(), format); numRows = System.Math.Max(numRows, rowprops[i].getRowNumber() + 1); } } catch (RowsExceededException e) { // Handle the rows exceeded exception - this cannot occur since // the sheet we are copying from will have a valid number of rows Assert.verify(false); } // Copy the headers and footers // sheetWriter.setHeader(new HeaderRecord(si.getHeader())); // sheetWriter.setFooter(new FooterRecord(si.getFooter())); // Copy the page breaks int[] rowbreaks = fromSheet.getRowPageBreaks(); if (rowbreaks != null) { for (int i = 0; i < rowbreaks.Length; i++) { rowBreaks.Add(rowbreaks[i]); } } int[] columnbreaks = fromSheet.getColumnPageBreaks(); if (columnbreaks != null) { for (int i = 0; i < columnbreaks.Length; i++) { columnBreaks.Add(columnbreaks[i]); } } // Copy the charts sheetWriter.setCharts(fromSheet.getCharts()); // Copy the drawings DrawingGroupObject[] dr = fromSheet.getDrawings(); for (int i = 0; i < dr.Length; i++) { if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Drawing) { WritableImage wi = new WritableImage (dr[i], toSheet.getWorkbook().getDrawingGroup()); drawings.Add(wi); images.Add(wi); } else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Comment) { CSharpJExcel.Jxl.Biff.Drawing.Comment c = new CSharpJExcel.Jxl.Biff.Drawing.Comment(dr[i], toSheet.getWorkbook().getDrawingGroup(), workbookSettings); drawings.Add(c); // Set up the reference on the cell value CellValue cv = (CellValue)toSheet.getWritableCell(c.getColumn(), c.getRow()); Assert.verify(cv.getCellFeatures() != null); cv.getWritableCellFeatures().setCommentDrawing(c); } else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.Button) { CSharpJExcel.Jxl.Biff.Drawing.Button b = new CSharpJExcel.Jxl.Biff.Drawing.Button (dr[i], toSheet.getWorkbook().getDrawingGroup(), workbookSettings); drawings.Add(b); } else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.ComboBox) { CSharpJExcel.Jxl.Biff.Drawing.ComboBox cb = new CSharpJExcel.Jxl.Biff.Drawing.ComboBox (dr[i], toSheet.getWorkbook().getDrawingGroup(), workbookSettings); drawings.Add(cb); } else if (dr[i] is CSharpJExcel.Jxl.Biff.Drawing.CheckBox) { CSharpJExcel.Jxl.Biff.Drawing.CheckBox cb = new CSharpJExcel.Jxl.Biff.Drawing.CheckBox (dr[i], toSheet.getWorkbook().getDrawingGroup(), workbookSettings); drawings.Add(cb); } } // Copy the data validations DataValidation rdv = fromSheet.getDataValidation(); if (rdv != null) { dataValidation = new DataValidation(rdv, toSheet.getWorkbook(), toSheet.getWorkbook(), workbookSettings); uint objid = dataValidation.getComboBoxObjectId(); if (objid != 0) { comboBox = (ComboBox)drawings[(int)objid]; } } // Copy the conditional formats ConditionalFormat[] cf = fromSheet.getConditionalFormats(); if (cf.Length > 0) { for (int i = 0; i < cf.Length; i++) { conditionalFormats.Add(cf[i]); } } // Get the autofilter autoFilter = fromSheet.getAutoFilter(); // Copy the workspace options sheetWriter.setWorkspaceOptions(fromSheet.getWorkspaceOptions()); // Set a flag to indicate if it contains a chart only if (fromSheet.getSheetBof().isChart()) { chartOnly = true; sheetWriter.setChartOnly(); } // Copy the environment specific print record if (fromSheet.getPLS() != null) { if (fromSheet.getWorkbookBof().isBiff7()) { //logger.warn("Cannot copy Biff7 print settings record - ignoring"); } else { plsRecord = new PLSRecord(fromSheet.getPLS()); } } // Copy the button property set if (fromSheet.getButtonPropertySet() != null) { buttonPropertySet = new ButtonPropertySetRecord (fromSheet.getButtonPropertySet()); } // Copy the outline levels maxRowOutlineLevel = fromSheet.getMaxRowOutlineLevel(); maxColumnOutlineLevel = fromSheet.getMaxColumnOutlineLevel(); }