Excel corrupted

[Eisert_Christian]

Hello,

We have the following problem:
by Creating and filling an Excel file with TMS Flexcel Studio it will be damage
and can not be repaired.

A snippet of the right file "workbook.xml" from the Excel file before editing with Flexcel:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="4" rupBuild="9303"/>
<workbookPr defaultThemeVersion="124226"/><bookViews><workbookView xWindow="0" yWindow="0" windowWidth="19200" windowHeight="10785"/></bookViews>
<sheets><sheet name="RM 997" sheetId="3" r:id="rId1"/></sheets><calcPr calcId="0"/></workbook>

A snippet of the corrupt file "workbook.xml" from the Excel file after editing with Flexcel:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" rupBuild="4506" lastEdited="4" lowestEdited="4" />
<workbookPr defaultThemeVersion="124226" /><bookViews><workbookView xWindow="0" yWindow="0" windowWidth="19200" windowHeight="10785" /></bookViews>
<sheets><sheet name="RM 997" sheetId="3" r:id="flId1" /></sheets>
<xr:revisionPtr revIDLastSave = "0" documentId = "10_ncr:8100000_{2DB04D48-B75D-47CA-B49A-7D110058603D}" xr6:coauthVersionLast = "34" xr6:coauthVersionMax = "34" xr10:uidLastSave = "{00000000-0000-0000-0000-000000000000}" /></workbook>

The difference is:

<xr:revisionPtr revIDLastSave = "0" documentId = "10_ncr:8100000_{2DB04D48-B75D-47CA-B49A-7D110058603D}" xr6:coauthVersionLast = "34" xr6:coauthVersionMax = "34" xr10:uidLastSave = "{00000000-0000-0000-0000-000000000000}" /></workbook>

Why is this sentence written?

Function to create, write and save file:

function TZertifikat.ZertifikatNachExcel(aErneutErstellen: Boolean; var aExcelDateiPfad: String): Boolean;
var
  tmpxls: TXlsFile;
  tmpZeileFrei, tmpSpaltenIndex: integer;
  XF, i, y, j: integer;
  Zelle: TCellValue;
  AdresseZelle: TCellAddress;
  tmpLieferdatum: string;
  tmpExcelFilePfad: string;
  tmpVorlageGefunden: Boolean;
  Ini: TInifile;
  gefunden: Boolean;
  ListeChargenNr: TSTringList;
  SpChargenNr: Integer;
  ChNrVorhanden: Boolean;
  tmpProdDatum: TDate;
  tmpString: string;
  tmpWertTyp: Integer;
  tmpWertReal: Double;
  tmpWertBool: Boolean;
  tmpWertString: string;
  tmpWertAnaNr700: Double;
  tmpWertAnaNr701: Double;
  tmpSpalteAnaNr700: Integer;
  tmpSpalteAnaNr701: Integer;
  addrAnaNr700: TCellAddress;
  addrAnaNr701: TCellAddress;
  tmpSpaltenName700: string;
  tmpSpaltenName701: string;
  okWeiter: Boolean;
  ChargenNrSchonDa: Boolean;
  LieferscheinNrSchonDa: Boolean;
  tmpCHNrInExcel: TDateTime;
  ms: TMemoryStream;
  tmpPfad: string;
begin
  Result := False;
  try
    aExcelDateiPfad := '';
    // Zertifikat-Versand als Excel-File per EMail prüfen
    if ExcelAlsEmail then
    begin
      // Excel-Vorlage im Kundenartikel vorhanden?
      tmpExcelFilePfad := '';
      tmpExcelFilePfad := PfadExcelDatei;
      tmpVorlageGefunden := False;
      tmpVorlageGefunden := FileExists(tmpExcelFilePfad);
      if tmpVorlageGefunden then
      begin
        try
          ms := TMemoryStream.Create;
          ms.LoadFromFile(tmpExcelFilePfad);
          ms.position := 0;
          tmpxls := TXlsFile.Create(true);
          tmpxls.Open(ms);

          try
            if tmpxls.SheetCount > 0 then
            begin
              for y := 1 to tmpxls.SheetCount do
              begin
                tmpxls.ActiveSheet := y;
                // INI-Datei erzeugen und speichern
                ExcelIni := TStringList.Create;
                Excelini.Clear;
                Excelini.Text := ExcelZuordnungIni;
                ExcelIni.SaveToFile(ExtractFilepath(Application.ExeName) + 'TmpExcelIni.ini');
                ini := TIniFile.Create(ExtractFilepath(Application.ExeName) + 'TmpExcelIni.ini');

                //-------
                //Neue Zeile Finden :
                tmpZeileFrei := 4;
                try
                  tmpZeileFrei := ini.ReadInteger('Zuordnung' + IntToStr(y), 'Startzeile', 4);
                except
                  tmpZeileFrei := 4;
                end;
                gefunden := false;
                while not gefunden do
                begin
                  tmpZeileFrei := tmpZeileFrei + 1;
                  if (tmpxls.GetCellValue(tmpZeileFrei, 1).IsEmpty) then
                    gefunden := true;
                end;

                //Alle bisherigen Chargennummern suchen
                SpChargenNr := 9999;
                ListeChargenNr := TStringList.Create;
                ini.ReadSection('Zuordnung', ListeChargenNr);
                for i := 1 to ListeChargenNr.Count do
                begin
                  if UPPERCASE(ini.ReadString('Zuordnung' + IntToStr(y), ListeChargenNr[i - 1], '')) =
                    'CHARGENNUMMER' then
                  begin
                    SpChargenNr := i;
                    break;
                  end;
                end;
                ListeChargenNr.Clear;
                ListeChargenNr.Free;

                ChNrVorhanden := false;
                if SpChargenNr <> 9999 then
                begin
                  for i := tmpZeileFrei - 1 downto 1 do
                  begin
                    if (tmpxls.GetCellValue(i, SpChargenNr) = ChargenNr.AsString) and (ChargenNr.AsString <> '') then
                    begin
                      ChNrVorhanden := true;
                    end;
                  end;
                end;

                if (aErneutErstellen = True) then
                begin
                  okWeiter := True;
                end;
                if okWeiter then
                begin
                  //IN EXCEL SCHREIBEN
                  for i := 1 to 500 do
                  begin
                    if ini.ValueExists('Zuordnung' + IntToStr(y), inttostr(i)) then
                    begin
                      tmpString := '';
                      tmpString := ini.ReadString('Zuordnung' + IntToStr(y), inttostr(i), '');

                      //leeres Feld
                      if (tmpString = '') or (Uppercase(tmpString) = 'N') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, '')
                      else if (Uppercase(tmpString) = 'DATUM') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, date)
                      else if (Uppercase(tmpString) = 'DATUM_GEMAHLEN') then
                      begin
                        tmpProdDatum := Produktionsdatum;
                        if tmpProdDatum > 0 then
                          tmpxls.SetCellValue(tmpZeileFrei, i, DateToStr(tmpProdDatum))
                        else
                          tmpxls.SetCellValue(tmpZeileFrei, i, '');
                      end
                      else if (Uppercase(tmpString) = 'DATUM_GELIEFERT') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, DateToStr(Lieferdatum.AsDate))
                      else if (Uppercase(tmpString) = 'CHARGENNUMMER') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, ChargenNr.AsString)
                      else if (Uppercase(tmpString) = 'ARTIKELNUMMER') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, ArtikelNr.AsString)
                      else if (Uppercase(tmpString) = 'ARTIKELNAME') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, ArtikelName.AsString)
                      else if (Uppercase(tmpString) = 'LIEFERSCHEINNUMMER') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, LieferscheinNr)
                      else if (Uppercase(tmpString) = 'KUNDENBESTELLNUMMER') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, KuBestellNr.AsString)
                      else if (Uppercase(tmpString) = 'VERLADEDATUM') then
                        tmpxls.SetCellValue(tmpZeileFrei, i, DateToStr(Verladedatum))
                      else if (Uppercase(tmpString) = 'P/L') then
        //                xls.SetCellValue(tmpZeileFrei, i, TFormula.Create('=' + tmpSpaltenName700 + IntToStr(tmpZeileFrei) + '/N' + tmpSpaltenName701 + IntToStr(tmpZeileFrei)))
                        tmpxls.SetCellValue(tmpZeileFrei, i, TFormula.Create('=M' + IntToStr(tmpZeileFrei) + '/ N' + IntToStr(tmpZeileFrei)))
                      else if (Uppercase(tmpString) = 'MHD') then
                      begin
                        tmpProdDatum := Produktionsdatum;
                        tmpProdDatum := IncMonth(tmpProdDatum, MHDMonate);
                        tmpxls.SetCellValue(tmpZeileFrei, i, DateToStr(tmpProdDatum))
                      end
                      else if (Uppercase(tmpString) = 'AUSGEBLENDET') then
                      begin
                        if ExcelTyp = 0 then
                          tmpxls.SetColHidden(i, true)
                        else if ExcelTyp = 1 then
                          tmpxls.SetColHidden(i, true);
                      end
                      else if IstInteger(tmpString) then
                      begin
                        tmpquery.Close;
                        tmpquery.SQL.Clear;
                        tmpquery.SQL.Add('SELECT Typ,WertReal,WertBool,WertString FROM ZertifikateAna');
                        tmpquery.SQL.Add('WHERE AnaNr=' + tmpString);
                        tmpquery.SQL.Add('AND ID_Zertifikat=' + Inttostr(FID.AsInteger));
                        tmpquery.Open;
                        if tmpquery.RecordCount = 0 then
                        begin
                          if ExcelTyp = 0 then
                            tmpxls.SetCellValue(tmpZeileFrei, i, '')
                          else if ExcelTyp = 1 then
                            tmpxls.SetColHidden(i, true);
                        end
                        else
                        begin
                          tmpWertReal := 0.0;
                          tmpWertAnaNr700 := 0.0;
                          tmpWertAnaNr701 := 0.0;
                          tmpWertBool := False;
                          tmpWertString := '';
                          tmpWertTyp := tmpquery.FieldByName('Typ').AsInteger;
                          tmpWertReal := tmpquery.FieldByName('WertReal').AsFloat;
                          tmpWertBool := tmpquery.FieldByName('WertBool').AsBoolean;
                          tmpWertString := tmpquery.FieldByName('WertString').AsString;
                          if tmpString = '700' then
                          begin
                            tmpSpaltenName700 := Dialog_ExcelDefinator.GetSpaltenName(i);
                          end;
                          if tmpString = '701' then
                          begin
                            tmpSpaltenName701 := Dialog_ExcelDefinator.GetSpaltenName(i);
                          end;
                          if ExcelTyp = 1 then
                            tmpxls.SetColHidden(i, false);
                          case tmpWertTyp of
                            0: tmpxls.SetCellValue(tmpZeileFrei, i, tmpWertReal);
                            1:
                              begin
                                if tmpWertBool then
                                  tmpxls.SetCellValue(tmpZeileFrei, i, 'JA')
                                else
                                  tmpxls.SetCellValue(tmpZeileFrei, i, 'Nein')
                              end;
                          else
                            tmpxls.SetCellValue(tmpZeileFrei, i, tmpWertString);
                          end;
                        end;
                        tmpquery.Close;
                      end
                        //Text
                      else
                        tmpxls.SetCellValue(tmpZeileFrei, i, tmpString);
                    end;
                  end;

                  tmpxls.Save(ms);
                  ms.SaveToFile(tmpExcelFilePfad);
                  ini.Free;
                  DeleteFile(PWideChar(ExtractFilepath(Application.ExeName) + 'TmpExcelIni.ini'));
                  ExcelIni.Free;
                end;
              end;
            end;
          finally
            FreeAndNil(tmpxls);
          end;
        finally
          ms.Free;
        end;
        if okWeiter then
        begin
        if (tmpExcelFilePfad <> '') then
          begin
            aExcelDateiPfad := tmpExcelFilePfad;
          end;
        end;
      end;
    end;
    Result := okWeiter;
  except
    on e:exception do
      BtbFehler('ZertifikatNachExcel', e);
  end;
end;

Thanks in advance

Best regards

Irina Hornung

[adrian]

Hi,

The record is written because it was on the original file, so FlexCel tried to preserve it. By default FlexCel writes the record it doesn't know about at the end of the file. But different from the rest of the xlsx (or xls for that  matter) spec, this particular record causes Excel to completely crash and not even open the file in recovery mode, even when it could just ignore that record.

This record was introduced somewhere in some update of Excel 2016, and we fixed it a week later after Excel 2016 started to write it.  The problem is that originally, having this record in the wrong position just would put a yellow warning, so when we fixed it we didn't made it a big issue. But later, in another update, Excel 2016 started to completely crash if the record was in the wrong position. We had already fixed it, so we didn't noticed then.

The solution is easy, just update FlexCel to the latest version. Other solution is to save the file "tmpExcelFilePfad" with an older Excel version (2013 or 2016 before they added the record).

[Eisert_Christian]

Hi, thank you for the solutions. I'll try the second one and share my result.

[Eisert_Christian]

Hi,I have installed the current version, but the problem is still there...

The message by opening of the excel file is:
Excel was found unreadable content.
Do you want to restore the contents of this workbook?
If you trust the source of this workbook, click "Yes".

Should I use additional paramemeter for saving to the
memory stream (tmpxls.Save(ms))?

[adrian]

Hi,

First of all I would make really sure that the new version was picked up. The new version shouldn't have this problem.

The simplest way to check that Delphi is actually using the latest FlexCel is checking the global variable FlexCelVersion from your app:

http://www.tmssoftware.biz/flexcel/doc/vcl/tips/finding-out-the-flexcel-version.html

If indeed this variable is > 6.20, then I would need to have a look at the generated file to see what can be going wrong. Can you send me a file that shows the error to adrian@tmssoftware.com ? 

[adrian]

Sorry, I've just realized that the error you posted now is a different one. I don't know why, in this forum  only the first line of text was visible (but now I saw you can scroll to see the rest)

While in the notification email I see:

Hi,I have installed the current version, but the problem is still there...

The message by opening of the excel file is:
Excel was found unreadable content.
Do you want to restore the contents of this workbook?
If you trust the source of this workbook, click "Yes".

Should I use additional paramemeter for saving to the
memory stream (tmpxls.Save(ms))?

This seems to be a different error, and it can be caused because tmpxls.Save(ms) will save the file as xls, not xlsx. To have the file saved in xlsx you need to write:

tmpxls.Save(ms, TFileFormats.Xlsx)

When saving to a file, FlexCel can pick the correct format from the file extension, but it can't when saving to a stream. In any case, you were getting an xlsx file in your original mail, so maybe this isn't the issue.

If the update was applied and FlexCel is indeed in the latest version, it would help me a lot to see the exact file that is generated so I can say more about what is wrong with it.

[Eisert_Christian]

Thank
you for the possible solutions. Is it possible to save the Flexcel file
directly into the Excel file, without buffering/saving into the memory stream?
Maybe I can solve the problem with the saving of Flexcel file as xls file in
the memory stream also?

[adrian]

Yes, you can open and save directly, I am not sure on why you are using the memorystreams.

Instead of

         ms := TMemoryStream.Create;
          ms.LoadFromFile(tmpExcelFilePfad);
          ms.position := 0;
          tmpxls := TXlsFile.Create(true);
          tmpxls.Open(ms);

You could just do:

  tmpxls := TXlsFile.Create(tmpExcelFilePfad, true);

and instead of

                  tmpxls.Save(ms);
                  ms.SaveToFile(tmpExcelFilePfad);

do

                  tmpxls.Save(tmpExcelFilePfad);

In fact, the corruption you are seeing might be related to the way you are using the streams. When you do:

         ms := TMemoryStream.Create;
          ms.LoadFromFile(tmpExcelFilePfad);
          ms.position := 0;
          tmpxls := TXlsFile.Create(true);
          tmpxls.Open(ms);

at the end of this code, ms has the full template loaded and it is at the end position.

Now, if you do:

tmpxls.Save(ms);

This will save the file *after* the existing data in ms, since ms was at the end of position. That is, after this call ms will be:

[original template bytes][output bytes]

To make this work with streams you should first clear ms before saving into it, but it is not really needed to use memory streams at all, they are only slowing things down.

[Eisert_Christian]

Thank you for that information.

I open and write the excel file in the network over the VPN connection.

That's the reason why I did not want to keep the excel file open so long and save it in a memory stream. Does it make sense, if I save the Excel file from the network in the temporary file and if all records are written, overwrite the original file in the network?

[adrian]

The file doesn't stay open, there is no need to keep the memorystream around. When you call TXlsFile.Open what happens is:

1. FlexCel opens the file

2. Reads the contents, parse them, and load them into memory.

3. Close the file.

Once xlsfile.Open is finished, all the file is loaded into memory inside objects held by TExcelFile. You can delete the file, or do whatever you want with it, FlexCel has already opened, read it and closed it.

Same for saving: When you save FlexCel creates a file, saves all the contents it has loaded into memory, then closes the file. No file stays open after open or save.

It might be anyway depending on the implementation of the vpn that it is faster to read the whole thing into a memory stream and then read from that memorystream, but it is unlikely. More probably, you will just have the file in memory twice.

But even if you find it faster to load into a memory stream, then into the TXlsFile, once the TXlsFile.Open is done, you should free the memorystream, there is no need to have it in memory anymore.

[Eisert_Christian]

Thanks, the Excel file is no longer damaged and can be opened.
But nothing is written down. By locally this is made perfect,
but if the file is taken from one location for the other location over vpn,
not.

[adrian]

Hi,

It should work the same over vpn than over local filesystem, but if you are mounting a smb drive over vpn, beware that those can be *really* slow. If might make sense to copy the file localy with ssh from the VPN, then modify it, then copy it back to the server over ssh.

Actually it is what I do myself when building FlexCel on a remote VPN. Instead of mounting a remote drive (which is very slow and fails intermittently) I just copy the files over ssh (I use OpenVPN and the ssh is enabled as a part of OPENVPN). It is orders of magnitude faster than mounting a smb drive.

[Eisert_Christian]

Hi,
I've had the same idea and will test the implementation today.

[Eisert_Christian]

Thanks again for the your help.