Excel OpenXML

I have this habit of capturing screenshots and saving them in Excel for later discussion. Excel seems to retain the format much better than Word and I can share it as one file with the tabs arranged sequentially and yea I use Snagit too, but for the sake of illustration, let use Excel.

Let’s assume you have a Similar Excel file with lots of images and would like to get to it quickly. On windows…

  • Go to the folder where you have the Excel workbook.
  • Make a copy of the Original file (just copy, right click and paste into the same folder, we will rename it in a bit)
  • Change the extension of the file to .Zip (You may have to follow the Instruction here to show/hide extensions)
  • Right Click and “Extract All” to a folder
  • Now open the new folder where you unzipped the contents and navigate to xl\media sub folder.
  • All the images should be here

The same principle works for Powerpoint files also. What is this Magic? Since Office 2007, Microsoft has adopted the OpenXML Standard. All of the assets that form part of an Excel Workbook is actually a compressed Folder.

The OpenXML SDK is pretty cool from a programming standpoint. Creating Office files using a layer of abstraction without  having to work with XML or if you so desire to work with XML directly. Checkout “DocumentFormat.OpenXml” in Visual Studio and wikipedia here.

I had to get here for a different purpose, that is to identify a bug in a piece of code, but that’s for another article to follow.

Advertisements

Excel – Insert a Tick / Check Mark

Ever wanted to insert a Tick Mark or other symbols into Excel. If you go to the Insert Ribbon Menu and look at the far right, you will see the Symbol button. Click this to see a list of symbols. The Tick / Check mark symbols are located under Bullets/Stars. The expanded view below show multiple variants when available.

Apply foreground color and you have it…

screen-shot-2016-09-27-at-9-44-57-am

Windows implements similar behavior using a dingbat font called wingdings.

Excel Slowness – Refreshing External data

We recently ran into a performance issue. An Excel 2013 workbook executing a query against a Oracle database. Refreshing this worksheet took several minutes at a rate of 100+ records every few seconds. This behavior only exhibited itself only on one machine. If the Query is set to execute in the background, a status as shown below show up in the Status bar.

Fig 1.0

I used Process Monitor to troubleshoot this performance issue. I reset all the filters and started the capture. I refreshed the Excel Worksheet for a couple of seconds, so that I had enough events to work with. I right clicked on the process Name column and filtered to view events generated by “EXCEL.EXE”. This showed that there was lot of write activity to a particular sql.log file. The file was too big to view in Notepad++ or Notepad, using the good old DOS Type command with More gave me a glimpse of the Trace Events.

img02

Process Explorer showed that the handle to sql.log was being held by a service host and not Excel. This confirmed my suspicion that it was a process outside of Excel (system wide) that had Debug or Tracing was enabled. Since the worksheet was using a DNS, the next steps was to go to ODBC Data Source Administration console (Control Panel). The Tracing Tab showed that there was a session level trace enabled. Stopping the Trace resolved the Performance Issue.

img03

Process Monitor and Process Explorer are important tools IMO.