Geekly Articles each Day
📜 ⬆️ ⬇️

Script for express recovery Excel files after damage

This note is intended for those who, when trying to open an Excel file, get an error message of the form:


In my case, with such an error, the xlsx-file (below 1.xlsx) was opened, restored with the help of R-Saver after a virus attack similar to “Petya”.

After unpacking the contents of the 1.xlsx file into the "\ 1" folder, the following errors were displayed via the context menu:


It turned out that these service files are of zero size. I did a similar procedure with a valid 2.xlsx file and copied from its "\ 2" folder non-zero [Content_Types] .xml and .rels files over the empty "\ 1" files. Then added the contents of the folder "\ 1" to the .zip archive and renamed it to 3.xlsx. As a result, the 3.xlsx file has already opened with correct data, albeit with a warning:
')

To automate the procedures performed above, a vbscript script was developed, distributed “As Is”.

Script source code ST1_XLSX_FIXER_v1
option explicit Const THIS_SCRIPT_NAME = "ST1_XLSX_FIXER_v1.vbs" Const SUBDIR_XLS_SRC = "ST1_XLSX_FIXER_DATA_v1" Const SUBDIR_OUT = "ST1_XLSX_FIXED" Const RES_SUFFIX = "_fixed_ST1_v1" Dim fso: Set fso = CreateObject("Scripting.FileSystemObject") '    if WScript.ScriptName = THIS_SCRIPT_NAME then if WScript.Arguments.Count > 0 then Dim fname for each fname in WScript.Arguments if fso.GetExtensionName(fname) = "xls" then WScript.Echo "  Excel 2003   (.xls)  " else FixCorruptedExcel fname end if next else WScript.Echo "    xlsx-  " end if end if Set fso = Nothing Sub FixCorruptedExcel(fpath) Dim out_dir: out_dir = fso.GetParentFolderName(fpath) & "\" & SUBDIR_OUT if Trim(out_dir) <> "" then '   If not fso.FolderExists(out_dir) Then fso.CreateFolder(out_dir) end if End If 'c  xlsx-   .zip Dim extract_dir: extract_dir = out_dir & "\" & fso.GetBaseName(fpath) Dim fpath_zip: fpath_zip = extract_dir & ".zip" fso.CopyFile fpath, fpath_zip '  Dim fpath_fixed: fpath_fixed = extract_dir & RES_SUFFIX & ".xlsx" if fso.FileExists(fpath_fixed) then fso.DeleteFile fpath_fixed ' zip UnzipFile fpath_zip, extract_dir ' zip- fso.DeleteFile fpath_zip '     Dim script_path: script_path = fso.GetParentFolderName(Wscript.ScriptFullName) fso.CopyFolder script_path & "\" & SUBDIR_XLS_SRC, extract_dir ' zip CreateEmptyZipFile fpath_zip ' extract_dir Dim shell: set shell = CreateObject("Shell.Application") Dim extract_dir_obj: set extract_dir_obj = fso.GetFolder(extract_dir) shell.NameSpace(fpath_zip).CopyHere shell.NameSpace(extract_dir).Items do until shell.namespace(fpath_zip).items.count = shell.namespace(extract_dir).items.count wscript.sleep 1000 loop 'zip -> xlsx fso.MoveFile fpath_zip, fpath_fixed ' unzip- fso.DeleteFolder extract_dir, true WScript.Echo " : " & vbCrLf & fpath_fixed Set shell = Nothing end sub sub UnzipFile(fpath_zip, extract_dir) '    If not fso.FolderExists(extract_dir) Then fso.CreateFolder(extract_dir) End If ' xlsx -     "  ..." Dim shell: set shell = CreateObject("Shell.Application") Dim sub_files: set sub_files = shell.NameSpace(fpath_zip).items Const FOF_SILENT = &H4& Const FOF_RENAMEONCOLLISION = &H8& Const FOF_NOCONFIRMATION = &H10& Const FOF_ALLOWUNDO = &H40& Const FOF_FILESONLY = &H80& Const FOF_SIMPLEPROGRESS = &H100& Const FOF_NOCONFIRMMKDIR = &H200& Const FOF_NOERRORUI = &H400& Const FOF_NOCOPYSECURITYATTRIBS = &H800& Const FOF_NORECURSION = &H1000& Const FOF_NO_CONNECTED_ELEMENTS = &H2000& Dim args: args = FOF_SILENT + FOF_NOCONFIRMATION + FOF_NOERRORUI shell.NameSpace(extract_dir).CopyHere sub_files, args Set shell = Nothing end sub sub CreateEmptyZipFile(fname) if fso.FileExists(fname) then WScript.Echo " " & fname & "  ", vbCritical, WScript.ScriptFullName end if Const ForWriting = 2 Dim fp: set fp = fso.OpenTextFile(fname, ForWriting, True) fp.Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0)) fp.Close end sub


In addition to the script in the archive attached folder ST1_XLSX_FIXER_DATA_v1, where are the reference files for substitution. You can modify its contents in order to extend the scope of the script to other variants of broken files. For example, add there the variants of zero files found by you.

For the script to work:

  1. Download and unzip the archive ST1_XSLX_FIXER_v1.zip to any folder
  2. Left-click to transfer one or more xlsx files to the ST1_XLSX_FIXER_v1.vbs script
  3. The processing of each file will begin:

  4. After successful processing of each file, a message of the form is displayed:



The principle of the script:

  1. Keeps the input file unchanged
  2. Creates a subfolder of ST1_XLSX_FIXED
  3. Creates a renamed zip copy of xlsx in ST1_XLSX_FIXED
  4. Extract zip to a folder and copy over it ST1_XLSX_FIXER_DATA_v1
  5. Archives the resulting folder to zip and renames the resulting file to xlsx

Conclusion

These experiments do not claim to common use, use the proposed solution at your own risk. For my part, I plan to conduct a wider experiment and finalize the script based on the results. The current explicit limitation is that the script does not analyze the size of the replaced files when copying from ST1_XLSX_FIXER_DATA_v1, therefore, it is not able to determine which service files were empty and require their replacement. Most likely, this method is applicable if the service files are lost, and not the worksheets from "\ 1 \ xl \ worksheets".

Also, the script is not suitable for files with the xls extension, created in versions of Excel 2003 and earlier, because it uses a different data storage format.

Source: https://habr.com/ru/post/332660/

More articles:


All Articles