Excel 2013 with error by PaneType=ptFrozen

[rolfschlueter]

Hi,

I'm using Excel 2013 (15.0.4737.1003) and newly I get an error in xlsx-files, if I'm coding 'PaneType := ptFrozen'.
When I try to open the xlsx, Excel shows the message, that the file would be corrupt. Then Excel repairs the file and opens it, but there's no freezing.
I've older xlsx-files, produced in January 2015, which could be read in January by Excel 2013, but now there's the same mysterious behaviour: error!

With older Excel versions (e.g. Excel 2007 or 2010) the problem doesn't exists.
I've tried various versions of XLSReadWriteII5, from 5.20.28 to 5.20.54, and get always the same error.

So I think Microsoft had made an update to Excel 2013 someday in the last 6 months, which has an important effect for freezing panes. Can you help, please?

Best regards
Rolf

[larsa]

Hello

This is fixed in update 5.20.55

[rolfschlueter]

thank's a lot for your quick response, but the error still exists

rolf

[larsa]

Hello

Please post a complete code sample that reproduces the error.

[rolfschlueter]

Hi,

here is a code sample which I've compiled with Delphi2010, using XLSReadWriteII5 version 5.20.55:

Code: Select all

procedure TForm1.TestXls;
var   iRow: Integer;
      xls:  TXLSReadWriteII5;

  procedure ShowLines(const iPos, iQuantity: Integer; const sEan, sTitle, sDesc, sText1, sText2, sText3: String; const fPrice: Double);
  begin
      Inc(iRow);
      xls.CmdFormat.Clear;
      xls.CmdFormat.Font.Name := 'Arial';
      xls.CmdFormat.Font.Size := 8;
      xls.CmdFormat.Border.Style := cbsThin;
      XLS.CmdFormat.Border.Preset(cbspOutlineAndInside);
      xls.CmdFormat.Apply(0,iRow,8,iRow);
      xls.CmdFormat.Border.Style := cbsMedium;
      XLS.CmdFormat.Border.Side[cbsLeft] := True;
      XLS.CmdFormat.Border.Side[cbsRight] := True;
      xls.CmdFormat.Apply(0,iRow,0,iRow);
      xls.CmdFormat.Apply(1,iRow,1,iRow);
      xls.CmdFormat.Apply(2,iRow,3,iRow);
      xls.CmdFormat.Apply(4,iRow,5,iRow);
      xls.CmdFormat.Apply(6,iRow,8,iRow);

      xls[0].AsString[0,iRow]    := IntToStr(iPos);
      xls[0].AsString[1,iRow]    := sEan;
      xls[0].AsString[2,iRow]    := sTitle;
      xls[0].AsString[3,iRow]    := sDesc;
      xls[0].AsString[6,iRow]    := sText1;
      xls[0].AsString[7,iRow]    := sText2;
      xls[0].AsString[8,iRow]    := sText3;

      xls.CmdFormat.Alignment.Horizontal  := chaRight;
      xls.CmdFormat.Number.Format := '#,###,##0';
      xls.CmdFormat.Apply(4,iRow,4,iRow);
      xls[0].AsInteger[4,iRow] := iQuantity;
      xls.CmdFormat.Number.Format := '#,###,##0.00 [$EUR]';
      xls.CmdFormat.Apply(5,iRow,5,iRow);
      xls[0].AsFloat[5,iRow] := iQuantity * fPrice;
   end;

begin
   xls := TXLSReadWriteII5.Create(nil);
   try
      // clear component
      xls[0].ClearWorksheet;

      // global font size
      xls.Font.Size := 8;

      // options for printing
      xls[0].PrintSettings.Options   := [];
      xls[0].PrintSettings.FitWidth  := 1;
      xls[0].PrintSettings.FitHeight := 0;
      xls[0].WorkspaceOptions := [woShowAutoBreaks,woRowSumsBelow,woColSumsRight,woFitToPage,woOutlineSymbols];
      xls[0].PrintSettings.HeaderFooter.OddHeader := '&L&12&BInvoice No. 12345678&B&R&8from &D-&T';
      xls[0].PrintSettings.HeaderFooter.OddFooter := '&L&I&8Dummy-Company&R&8page &P from &N';

      xls[0].Pane.PaneType := ptFrozen;
      xls[0].Pane.SplitRowY := 6;

      // repeat first 6 lines on each page of print
      xls[0].PrintSettings.PrintTitles(-1,-1,0,6-1);

      // column size
      xls[0].Columns[0].PixelWidth := 150;
      xls[0].Columns[1].PixelWidth := 120;
      xls[0].Columns[2].PixelWidth := 220;
      xls[0].Columns[3].PixelWidth := 220;
      xls[0].Columns[4].PixelWidth := 60;
      xls[0].Columns[5].PixelWidth := 100;
      xls[0].Columns[6].PixelWidth := 100;
      xls[0].Columns[7].PixelWidth := 110;
      xls[0].Columns[8].PixelWidth := 110;

      // start format
      xls.CmdFormat.BeginEdit(xls[0]);

      xls.Font.Name := 'Arial';

      // title
      iRow := 0;
      xls.CmdFormat.Clear;
      xls.CmdFormat.Font.Name := 'Arial';
      xls.CmdFormat.Font.Style := [xfsBold];
      xls.CmdFormat.Font.Size := 14;
      xls.CmdFormat.Apply(0,iRow,0,iRow);
      xls[0].AsString[0,iRow] := 'MY INVOICE';

      // parameters
      Inc(iRow, 2);
      xls.CmdFormat.Font.Size := 12;
      xls.CmdFormat.Apply(0,iRow,1,iRow+1);
      xls[0].AsString[0,iRow] := 'Invoice no:';
      xls[0].AsString[1,iRow] := '12345678';
      Inc(iRow);
      xls[0].AsString[0,iRow] := 'Date:';
      xls[0].AsString[1,iRow] := FormatDateTime('dd.mm.yyyy',Date);

      // column titles
      Inc(iRow, 2);
      xls.CmdFormat.Font.Size := 8;
      xls.CmdFormat.Alignment.Horizontal := chaLeft;
      xls.CmdFormat.Fill.BackgroundColor.RGB := clSilver;
      xls.CmdFormat.Border.Style := cbsMedium;
      XLS.CmdFormat.Border.Preset(cbspOutlineAndInside);
      xls.CmdFormat.Apply(0,iRow,8,iRow);
      xls.CmdFormat.Border.Style := cbsThin;
      XLS.CmdFormat.Border.Preset(cbspInside);
      xls.CmdFormat.Apply(2,iRow,3,iRow);
      xls.CmdFormat.Apply(4,iRow,5,iRow);
      xls.CmdFormat.Apply(6,iRow,8,iRow);
      xls[0].AsString[0,iRow] := 'Pos';
      xls[0].AsString[1,iRow] := 'EAN';
      xls[0].AsString[2,iRow] := 'Title';
      xls[0].AsString[3,iRow] := 'Description';
      xls[0].AsString[6,iRow] := 'Text1';
      xls[0].AsString[7,iRow] := 'Text2';
      xls[0].AsString[8,iRow] := 'Text3';
      xls.CmdFormat.Alignment.Horizontal := chaRight;
      xls.CmdFormat.Apply(4,iRow,5,iRow);
      xls[0].AsString[4,iRow] := 'Quantity';
      xls[0].AsString[5,iRow] := 'Price';

      // lines
      ShowLines(1, 11, '1111111111111', 'Title 1', 'Description 1', 'Text1 1', 'Text2 1', 'Text3 1', 1.23);
      ShowLines(2, 12, '2222222222222', 'Title 2', 'Description 2', 'Text1 2', 'Text2 2', 'Text3 2', 2.23);
      ShowLines(3, 13, '3333333333333', 'Title 3', 'Description 3', 'Text1 3', 'Text2 3', 'Text3 3', 3.23);

      // summary
      Inc(iRow);
      xls.CmdFormat.Clear;
      xls.CmdFormat.Font.Name := 'Arial';
      xls.CmdFormat.Font.Size := 8;
      xls.CmdFormat.Alignment.Horizontal := chaLeft;
      xls.CmdFormat.Border.Style := cbsMedium;
      xls.CmdFormat.Border.Side[cbsTop] := True;
      xls.CmdFormat.Apply(0,iRow,8,iRow);
      XLS.CmdFormat.Border.Side[cbsLeft] := True;
      XLS.CmdFormat.Border.Side[cbsRight] := True;
      xls.CmdFormat.Border.Style := cbsDouble;
      xls.CmdFormat.Border.Side[cbsBottom] := True;
      xls.CmdFormat.Apply(0,iRow,0,iRow);
      xls[0].AsString[0,iRow] := 'Summe';
      xls.CmdFormat.Alignment.Horizontal := chaRight;
      xls.CmdFormat.Number.Format := '#,###,##0';
      xls.CmdFormat.Apply(4,iRow,4,iRow);
      xls[0].AsFormula[4,iRow] := 'SUM(E7:E'+IntToStr(iRow)+')';
      xls.CmdFormat.Number.Format := '#,###,##0.00 [$EUR]';
      xls.CmdFormat.Apply(5,iRow,5,iRow);
      xls[0].AsFormula[5,iRow] := 'SUM(F7:F'+IntToStr(iRow)+')';

      xls.Calculate;

      xls.Filename := 'Invoice.xlsx';
      xls.Write;

   finally
      xls.Free;
   end;
end;

Best regards
Rolf

[larsa]

Hello

Use Pane.TopRow instead on pane.SplitRowY. SplitRowY is used when creating split panes.

[rolfschlueter]

Yes, it works!
Thank you for quick support.

Rolf