Releasing browser based XLStylesTool (Silverlight version)

The positive feedback on the original Windows Forms XLStylesTool has been poring in from all over the world: US, Canada, UK, Australia, Thailand, Amman (Jordan), India and even Gibraltar! It definitely makes me want to do more for the users. I had a dream to build browser based app for a couple months and it is finally here.

UPDATE [4/28/12]: Silverlight version of the XLStylesTool has been upgraded to Silverlight 5 and moved to Windows Azure: Silverlight 5 XLStylesTool on Windows Azure. See this new blog post where I go over changes.

Advantages of the web browser based version that are obvious (and not so obvious):

  • This XLStylesTool version is delivered in your favorite web browser (I tried IE8, IE9, FireFox and Chrome). You are guaranteed to get the latest cleanup algorithm on each use. I periodically optimize application code based on user feedback and the latest technology updates. If you have WinForms app you’ll have to check this site for upgrades on your own.
  • Because it is Silverlight app all processing is happening on your machine.  The file that you select for analysis and fixing never leaves your PC and as a result the processing time is identical to the original WinForms version. Try it: load the app in the browser, then pull out network cable or turn off Wi-Fi. This app will continue working for as long as you keep the browser window open.
  • All you need to run the app is Silverlight browser plug-in from Microsoft. Chances are you already have it and if you don’t you will be prompted to download it (file size is less than 6MB)  and it will install in seconds.
  • Finally Mac users should be able to run the application. I got couple users from the Mac owner community that had to depend on their friends with PCs. Not anymore.
  • I simplified user interface in this iteration. This version is more explicit about restriciting processing to just *.xlsx or *.xlsm files and will warn you if you choose any other unsupported file format (e.g., *.xls or *.xlsb) by accident. Also, you will get the most stable version of the file with user feedback generated options already preset for you in the app code.  Just drag your Excel file onto the form, hit ‘Process File’ button, watch file properties notification area show the results of the cleanup and choose where to save the fixed file. That is it. You can overwrite the same file you selected for scanning. Make sure you save *.xlsx with *.xlsx extension and *.xlsm with *.xlsm if you do “save as” with new file name or you won’t be able to open the file until you change the file extention to match the file contents.

Please leave feedback on this version.  Because of the ease of delivery, guaranteed updates, cross-browser, cross-platform nature of this approach I plan to focus on maintaining just this version. Your feedback will determine the outcome of this plan.  Also, the amazing technology that made it possible to keep all processing on the client and do it fast (I built this app in about a week working 2-4 hours in the evenings), Silverlight*, is in danger of being rejected as the fututre of the web app developement by Microsoft.  Your feedback on this application will help me decide if it makes sense to invest time into this technolgy going forward. The industry apparently is leaning towards HTML5 and JavaScript. HTML5 version of the XLStylesTool wouldn’t be able to run exclusively on your machine making it slower to process a file and a whole lot less secure with the reliance on the server side processing.

NOTE: couple IE users reported that sometimes link above returns an error. Close the browser (don’t hit refresh button; it won’t help) and next new session will work fine. The hosting site does something with cookies that don’t refresh right in IE once per day. I’m checking other hosting options. Any advice in this area is appreciated.

*Special thanks go to Mike Krueger and David Pierson for developing Silverlight 4 port of critical for this application .Net libraries. Your work shows that the internet through knowledge sharing makes the world so much better to live, work and play in.

This entry was posted in Computers and Internet. Bookmark the permalink.

64 Responses to Releasing browser based XLStylesTool (Silverlight version)

  1. Pingback: Sharing useful utilities | XLGeek's space

  2. Jonathan says:

    This worked really well for me. My only concern is over security — would love to have certainty that no data is being transmitted post-processing. But very easy to use, quick to load, and to process. Many thanks and much appreciation for your programming abilities.

    • XLGeek says:

      Hi Jonathan, that is the key feature of Silverlight – all processing is done on the client because the run-time has all necessary APIs, so no server interaction is required. That is why I picked Silverlight and not HTML+JavaScript or ASP.NET app. Traditional web applications require server round-trip which can get slow and (more to your point) you don’t know what happens to your file. You can easily validate that yourself: let the app load in your browser, pull network cable out and/or shut down WiFi. The app will continue to process your Excel files with the same speed as long as you have it open and cached in the browser. Alternatively you can download packet sniffer like network monitor or wire shark and inspect your network data at the packet level. You will not see a single byte from your file leave your PC. I created browser based option for ultimate in end user convenience: no installation is required, updates are automatic, Silverlight 4.0 runtime is less than 5MB one time download, you can use it in any browser and on any PC including MACs, etc… If you are still not happy with the packet level data analysis of the SL4 XLStylesTool app you can always fall back to the original WinForms version of the XLStylesTool. I recommend .Net4.0 version. It is a little faster and obviously offers 100% client side processing.

  3. Jonathan says:

    Thanks for this suggestion and quick response. Not that I want to ask you for more given the value of your contribution so far, but had you considered (or is it even possible) to have an in-Excel add in that would clean the file in situ? This might make the fix most convenient for the user and avoid re-corruption. And maybe MS would be interested in bringing the tool into the application. Thanks again!

    • XLGeek says:

      The original version of the tool was implemented as a VBA add-in in response to a critical situation. After several months of extensive research it became obvious that the only algorithm that can produce 100% stable file is Open XML based: e.g., hidden styles, system styles with low IDs are not accessible via the object model. OOXML processing is best done using the managed code and by closing of an already open file that can be read-only, which would prevent direct file update forcing “Save as” user prompt. Add-in scenario starts getting more complicated from this point on: where do you setup the threshold for the alert? If the user saves down to XL2003 it has to be below 4000, but XL2007/2010 can handle 64,000+, etc… You can easily write VBA based add-in for your personal or small org use in a matter of minutes. I can point you to necessary resources. Also, I helped a very talented developer with VSTO add-in if you are interested in managed code version. He may be open to the option to help community out.

  4. Andrew says:

    100% awesome, saved my bacon

  5. Katie says:

    Works great! Thanks!!

  6. John says:

    I have some very large files (>50 MB) and for some of them when I drag the file onto the “tool” the tab closes and reopens in IE. Is the tool just not able to handle files of this size or am I doing something else wrong?

    • XLGeek says:

      The file size limit in the Silverlight app should be the same as in the win forms: roughly 2GB which is the OS limit for 32 bit processes. Does the same thing happen when you use the button to open the same file? Excel 2007+ files are zip archives. It is possible that when the file opens in the app it goes over the 32 bit process limit when it gets uncompressed (we need to confirm that; I had users with 300+MB files in the WinForm app and everyhting worked). Is there any way for you to share this file with me? I would like to look into it. This is first reported case of this behavior, so I want to either address or document it. Thank you for heads up.

      • John says:

        Yes, the same thing is happening if using the “get excel file” button. Unfortunately I will not be able to share the file with you. I may try the win forms version to see if that works. Thanks.

    • XLGeek says:

      Since you can’t send the file please let me know if win forms tool worked for you. That would be clear indication that SL version has smaller file size ceilling. It also can be something in the file that causes the read of the package to fail. I would very much appreciate if you report your results.

      • John says:

        It seems to have worked using the win forms version. I guess there was probably some corruption in the file that was causing the sliverlight version to fail. Thanks for your help – this tool is a lifesaver.

    • XLGeek says:

      Hi John, I really appreciate your follow up – it would help other people that run into file size related issues with the SL version of my app. I am relying on the SL object model to read in Excel file and since app packages over 50MB don’t make much sense in the web based scenario I suspect that the restriction is on the Silverlight side. I’ll look into this and post an answer. Glad to hear that you were able to clean up your files.

  7. Dave says:

    Great, you should be made Saint !

  8. Pingback: XLStylesTool is Upgraded to Silverlight 5 and Moves to Windows Azure | XLGeek's space

  9. rupert says:

    I get “unrecoverable styles corruption” when i try to process my sheet😦

    • XLGeek says:

      Unfortunately last save of your file pushed custom styles limit past the max and the styles definition part is destroyed. I hope you have a backup copy. Please clean your back up file and use “Force all cell styles to ‘Normal'” option. Since you got to over 64,000 custom styles you are very close to another blow up even in your backup (assuming you have one). This high count typically means you may have bad styles IDs as well that forcing to normal would fix for you.

      • rupert says:

        Hi,
        thanks for your reply.

        yes, i’ve been bitten before many times by Excel and always make several backups of workbooks!

        I ran a copy of the workbook through your tool and checked the “Force all cell styles to ‘Normal'” option, and wow, it made a big difference to the sheet:-

        before:
        ?ActiveWorkbook.Styles.Count
        33490

        after:
        ?ActiveWorkbook.Styles.Count
        47

        That’s quite a decent reduction in styles.
        And most importantly, all the formatting is still there!

        Thanks a lot for your help!

    • XLGeek says:

      Awesome! Since you are comfortable with VBA and are technical I bet you wonder why you still see 47 styles. They are not part of the workbook. These are 47 built-in styles and they are merged into the workbook instance object on workbook open. Your workbook now has only one custom style: Normal with id=0. No corruption. I bet your file opens up faster now too. Thank you for following up and sharing your experience.

      • rupert says:

        yep, it opens faster. I wonder why just having one custom style isn’t the default behaviour for excel, and then these issues would never happen.

        Thanks again for your help.
        Rupert

  10. Lennart says:

    Great tool, saved my night!
    Only one thing: I processed an xlsm file and it turned into an xlsx file, so when I tried to open it I got a error message saying that my file may be corrupted. All I had to do was change the file format back to what it was. Could you implement that in your tool?

    • XLGeek says:

      The tool is already implemented this way. The app doesn’t change the processed file extension, the user types it in or OS assigns a default. Silverlight doesn’t allow file system access, so I have no control here. If you have known file extensions hidden in Windows Explorer then you are running the risk of not getting the correct extension in because you don’t see it. I actually covered this case in the post. Glad it worked for you and thanks for the post. It would help other people avoid similar situation.

  11. Joyce says:

    Thank you XLGeek. Now I look like a genius at work because of you. Bless you for your efforts and really helping the Excel worker bees!

  12. jbdcolley says:

    Brilliant – Thank You XLGeek – solved a headache for me🙂

  13. John says:

    Used this tool again today… would like to send you a Starbucks thank-you. Is there somewhere I could email you?

  14. Anand says:

    Thank you!! It fixed the error.

  15. Cakewalk says:

    Wow! It works like a charm! Very fast too!

  16. luis garcia says:

    Good stuff. Fixed the issue of not being able to move tabs from one worksheet onto another and the too many cell formats. Keep up the great work.

  17. Debi Miller says:

    This really helped me out of a jam when I tried it out 4/16/13. However, the issue returned and I had to re-run it twice already today (4/17). Any thoughts why that would happen and how I can get it to permanently stop? I just did a quick check making the a few small edits (adding about 500 text charaters to a field) and file size went from 981KB to 1250KB. And Styles Max ID: went from 0 to 34401 with just those small edits. Styles Node Count went from 1 to 34402.

    • XLGeek says:

      Hi Debi, did your edits involve copy/paste from another workbook that has high count of bad styles? If you continue to copy from workbooks with multiple bad styles in them you will keep on re-introducing them in the workbooks that you clean up. Please share the Office version and exact steps of the edits that you make if you determine that the copy/paste is not the cause.

      • Debi Miller says:

        I copied 2 single cells over from another spreadsheet. Both are 2010 .xlsx spreadsheets. I just checked the spreadsheet I copied from and it had:

        Styles Max ID: 34402
        Styles Node Count: 34403
        Used: 8
        Total Names: 36
        Invalid Names: 11
        Names w/External refs: 2
        Hidden: 9

        I don’t know how these spreadsheets ballooned to such huge counts to begin with, there is not a huge amount of data and/or formatting on them. But nonetheless, I’m so grateful for this tool because it is keeping me functional until I can figure out what is going on.

        Thanks for the quick response XLGeek and for creating this little tool. It saves a lot of frustration on my part!

    • XLGeek says:

      Really glad I could help. Even single cell copy/paste from the bad source workbook will bring over all bad styles into the target workbook if you do cross Excel instance copying. If you work a lot with other people that keep on re-introducing the problem you can use my command line utilities that can:
      a) clean up bad styles on the designated share as soon as they get saved
      b) tell you who edited what file and when. I.e., who keeps on re-introducing bad styles into shared workbook.

  18. Bo says:

    Hi, I am trying to download the .net 4.0 version at the App store, but when getting redirected from the IE browser to App store, I get no file to download? I am trying to download from Denmark, so maybe this is related to regional limitations? Is it possible to get the needed file elsewhere than the App store?? Best Bo Kaaber Brandt

    • XLGeek says:

      Hi Bo, can you give me your OS version: type winver at the command prompt and send me all info that comes up. If you are running Win 8.1 or Win 8 consumer preview the tool won’t work. No one is using these OSs for productive Excel work. Only for app hacking. If you are on another OS then I would love to dig in this case.

  19. Norm Pont says:

    Awsome… and that is a word I NEVER use. Seriously impressive. Thank you.

  20. Ashley says:

    Thanks so much for solving this problem for me – it worked like a charm and took 2 secs to fix my complex excel file. I’m so glad to have found your website, what a blessing, God bless you!

  21. Jane Yang says:

    Thank you very much for your tool. That you share generously. It saved me a lot of time to get rid of the annoying message “Too many formats”” Thank you!

  22. Tom Cooch says:

    1/1/14 I have used this app numerous times on my Mac with an .xlsm file without any difficulty. The last time was about 3 months ago. I tried this morning numerous times and the app cannot scan the file; it just keeps trying to process it indefinitely.

    • XLGeek says:

      This is file specific issue. Please make sure that the file is not already open in another program like Excel or that it is not marked as read only. If it still fails to process send me the file (you can clear all sensitive cell contents, but please leave the formatting). I’ll check it out for you. Reply to this message if you want to send the file over and I will get you an e-mail address.

  23. Tom Cooch says:

    I changed the file permission to read and write for everyone and still have the problem. In fact, the browser application can’t scan any of my Excel files now including the simplest ones. I downloaded the latest Silverlight plug-in and that didn’t help. I am using Safari 6.1 and OS X 10.8.5. If you are willing to have me send you the file, I would be grateful.

  24. Alex Perry says:

    I am having the same problem? I just found this tool. It sounds like it could be a life saver. I have several other files and it would make my boss very happy. I can send a file sample if necessary. If you find the problem or fix the issue please let me know.

  25. Louis Pyae Linn says:

    Hi XLGeek,
    Thanks you very much for your hard working.
    It really help alot for us and save our times also.
    Thanks you again.🙂
    I came to know your site form following article:
    http://support.microsoft.com/kb/213904
    ” This problem occurs when the workbook contains more than approximately 4,000 different combinations of cell formats in Excel 2003 or 64,000 in Excel 2007 and higher. A combination is defined as a unique set of formatting elements that are applied to a cell. A combination includes all font formatting (for example: typeface, font size, italic, bold, and underline), borders (for example: location, weight, and color), cell patterns, number formatting, alignment, and cell protection. “

  26. Arun Prasad says:

    @XLGeek – Very well done. Thanks, works like a charm. I didn’t read up fully before I rand the tool and expected it to remove all formatting, but the cleanup is just not visible – but with big reductions in file sizes. Will attest to your claim that it runs quickly, it was blindingly quick on the file I had issues reported.

    Reached your site via Microsoft support site.

    Addendum – a few posts above have complained that the tool doesn’t load/work while attempting to scan the file. This can be resolved by closing the file, I believe it doesn’t get a write-lock on the file if its already open by the user already. (and perhaps only applies to people using the Silverlight Azure link).

  27. S says:

    Came to work and found a critical model with all formatting removed, making the doc practically unworkable. Managed to recover an older version with most of my latest edits, and then found and ran your tool – MY formatting remains, but all the background noise is removed, and file is now down to about 40% of original size.
    Brilliant tool, absolute life-saver, many thanks!

  28. Thanks for great tool, working 100%. Many Thanks!

  29. Andrew G says:

    I used to run a macros that I got off the web to remove all the extra formats. My work laptop crashed and I was unable to find the macro again. Luckily, I stumbled across this cool tool. No macro needed and it worked just as fast. I ran the XLStylesTool and saved it to my bookmarks in Google Chrome. I am sure you do well in your profession, but the mighty Microsoft would be wise to throw you a bone or bone$ for creating this tool. Much appreciated!

  30. Alan Robertson says:

    Had the more than 64,000 formats problem but your application cleaned it within a matter of seconds. Amazing.

  31. XLStylesTool worked great. Such a relief to get this file fixed! Thanks

  32. KSH7 says:

    Thank you!!!!! Worked perfectly.

  33. FIFO says:

    Thanks

  34. Roffo says:

    Yeah this is excellent in case 1,000 people haven’t mentioned that yet. Thank you!

  35. Amanda says:

    This so worked!!! And it was very easy, which I appreciate because I am not tech savvy at all!

  36. WS says:

    I just discovered this tool, absolutely brilliant, solved the most irritating error in excel.
    Thank you for all the hard work, greatly appreciated.

  37. Jared says:

    What you have done here is remarkable. Thanks for hosting a highly efficient, easy-to-use application for deleting these pesky excess formats. And not only that, but it is without any security concerns that may be posed in a highly controlled enterprise enviromnet. If I may ask, what programming language did you use for the tool? What is your prior programming experience? I tried a VBA solution that would crash Excel if it got near to the internal limit for formats.

    • XLGeek says:

      I explain most of the implementation details in earlier posts. VBA uses Excel Object model and as a result it can’t access some very corrupted styles. All of my tools leverage the same patent protected algorithm. I got the patent to make sure that no one capable of reverse engineering the apps tries to profit from the unfortunate situation. I’m a professional developer.

  38. ken says:

    Hi,

    I need to xlsyles for windows 10. please sende me donwload ling program.

    Thanks,

  39. Jim Davis says:

    XLStyles Tool is showing 173 invalid names in my excel 2007 spreadsheet , but I don’t see them even after unhiding all range names.

    What would this be referring to?

    • XLGeek says:

      Could be system or programmatically generated named ranges. If you have lots of pivot tables with lots of filters you will have system created hidden named ranges. Some add-ins or viruses create hidden named ranges that match system named range naming pattern to prevent unhiding. You can remove all data from the file and send it to me. I’ll tell you for sure what it is.

  40. Rupen says:

    Thanks very much for doing this. You saved me a ton of trouble. Mac user Excel 2011.

Leave a Comment or Ask a Question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s