Axolot Data

Posted: **Mon Apr 15, 2013 9:48 am**

In XLSReadWriteII v4 I used TXLSColumn.Assign() to copy column formatting from a format sheet to the target sheet. In v5 the TXLSColumn doesn't have any Assign method.

What is the best way to copy column formatting? Can I do it using TXc12Column and TXLSColumns.CopyHitList somehow? Looks like some of the Assign methods I might use are simply copying pointers, so I could mess things up by using them.

I'm a bit unsure how to port this from v4, and any help would be appreciated.

This is what my routine for v4 looks like:

Code: Select all

procedure TdmExcel.CopyColFormats(nMaxColIdx: Integer);
var
  colFormat: TXLSColumn;
  colRender: TXLSColumn;
  i: Integer;
begin
  if not Assigned(FormatSheet) then Exit;

  for i := 0 to nMaxColIdx do
  begin
    colFormat := FormatSheet.Columns[i];
    if Assigned(colFormat) then
    begin
      RenderSheet.Columns.AddIfNone(i, 1);
      colRender := RenderSheet.Columns[i];
      colRender.Assign(colFormat); // Compiler error on this line.
    end;
  end;
end;

Posted: **Wed Apr 17, 2013 7:45 am**

Hello

Sorry, you can't do that. In version 5 the columns are organized in a different way which make it more complicated to implement an Assign method.

Posted: **Thu Apr 18, 2013 7:47 am**

Yes, it looked like it would be hard. So I took a more manual approach, and ended up with two routines that gave the desired result.

EDIT: Or, actually I'm having AV trouble in the real app (not the test app). Looks like BeginEdit/EndEdit does not function like the usual BeginUpdate/EndUpdate, so I removed the BeginEdit/EndEdit stuff, but still AV.
I would love to use CmdFormat, but looking at DoApplyReplace it seems to be strictly cell formatting. Any plans to make CmdFormat handle column and row formatting?

Code: Select all

type
  THackFormattedObj = class(TXLSFormattedObj);

procedure CopyFormat(src, dest: TXLSFormattedObj);
var
  hackDest: THackFormattedObj;
  srcCol, destCol: TXLSColumn;
  srcRow, DestRow: TXLSRow;
begin
  if not src.IsFormatted then Exit;
  if (src.ClassType <> dest.ClassType) then Exit;

  hackDest := THackFormattedObj(dest);
  hackDest.FStyles.XFEditor.BeginEdit(hackDest.FXF);
  try
    // TXLSFormattedObj
    dest.Protection := src.Protection;
    dest.HorizAlignment := src.HorizAlignment;
    dest.VertAlignment := src.VertAlignment;
    dest.Indent := src.Indent;
    dest.Rotation := src.Rotation;
    dest.WrapText := src.WrapText;
    dest.ShrinkToFit := src.ShrinkToFit;
    dest.FillPatternForeColor := src.FillPatternForeColor;
    dest.FillPatternBackColor := src.FillPatternBackColor;
    dest.CellColorRGB := src.CellColorRGB;
    dest.FillPatternPattern := src.FillPatternPattern;
    dest.NumberFormat := src.NumberFormat;
    dest.BorderTopColor := src.BorderTopColor;
    dest.BorderTopStyle := src.BorderTopStyle;
    dest.BorderLeftColor := src.BorderLeftColor;
    dest.BorderLeftStyle := src.BorderLeftStyle;
    dest.BorderRightColor := src.BorderRightColor;
    dest.BorderRightStyle := src.BorderRightStyle;
    dest.BorderBottomColor := src.BorderBottomColor;
    dest.BorderBottomStyle := src.BorderBottomStyle;
    dest.BorderDiagColor := src.BorderDiagColor;
    dest.BorderDiagStyle := src.BorderDiagStyle;
    dest.FontName := src.FontName;
    dest.FontCharset := src.FontCharset;
    dest.FontFamily := src.FontFamily;
    dest.FontColor := src.FontColor;
    dest.FontSize := src.FontSize;
    dest.FontStyle := src.FontStyle;
    dest.FontSubSuperScript := src.FontSubSuperScript;
    dest.FontUnderline := src.FontUnderline;

    // TXLSColumn
    if src is TXLSColumn then
    begin
      srcCol := TXLSColumn(src);
      destCol := TXLSColumn(dest);
      destCol.CharWidth := srcCol.CharWidth;
      destCol.Hidden := srcCol.Hidden;
      destCol.Width := srcCol.Width;
      destCol.OutlineLevel := srcCol.OutlineLevel;
      destCol.CollapsedOutline := srcCol.CollapsedOutline;
    end;

    // TXLSRow
    if src is TXLSRow then
    begin
      srcRow := TXLSRow(src);
      destRow := TXLSRow(dest);
      destRow.Height := srcRow.Height;
      destRow.Hidden := srcRow.Hidden;
      destRow.OutlineLevel := srcRow.OutlineLevel;
      destRow.CollapsedOutline := srcRow.CollapsedOutline;
    end;

  finally
    hackDest.FStyles.XFEditor.EndEdit;
  end;
end;

procedure CopyCellFormat(srcSheet: TXLSWorksheet; nColSrc, nRowSrc: Integer;
  destSheet: TXLSWorksheet; nColDest, nRowDest: Integer);
begin
  if srcSheet.CellType[nColSrc, nRowSrc] = xctNone then
    srcSheet.AsBlank[nColSrc, nRowSrc] := True;
  if destSheet.CellType[nColDest, nRowDest] = xctNone then
    destSheet.AsBlank[nColDest, nRowDest] := True;
  CopyFormat(srcSheet.Cell[nColSrc, nRowSrc], destSheet.Cell[nColDest, nRowDest]);
end;

Axolot Data

How to copy column format to other sheet

How to copy column format to other sheet

Re: How to copy column format to other sheet

Re: How to copy column format to other sheet