By Ivan Pena
According to Ralph Grabowski’s CAD Manager’s Guidebook, CAD drafters spend about 50% of their day working on other things than drafting, like word processing and working with spreadsheets. These spreadsheets are used to make area calculations, balance budgets and work out bills of materials (BOM), schedules and quantities sheets. In the case of BOMs and quantities sheets, this data is often pasted into MicroStation, and since it is important data, its handling must be efficient and reliable.

As far as I know, there are two ways to go about importing spreadsheets in MicroStation: pasting a spreadsheet into a design file using the MicroStation’s “Paste” command and pasting a spreadsheet using Axiom’s Microsoft Office Importer. So, using the good old scientific method, I decided to find out for myself the advantages and disadvantages of each. The following article is the recounting of an experiment I performed to test the pasting capabilities of Microsoft Office Importer’s against MicroStation’s built-in capabilities.

The specimens: two innocent (yet gnarly) spreadsheets
Spreadsheets in CAD vary widely when it comes to size, formatting and complexity. For this experiment, I used two different spreadsheets. The first is a large spreadsheet with 716 rows and 11 columns. The second is a spreadsheet with almost every conceivable formatting applied to regions of it.

Spreadsheets used for the experiment: a large, 716 rows by 11 columns behemoth and a spreadsheet with cells formatted in various ways

Test #1
I opened up the large spreadsheet in Microsoft Excel and selected all of its contents. I hit <Ctrl+C> on my keyboard (to copy) and switched to my open MicroStation file. Once in MicroStation, I pasted the large spreadsheet three different ways:

1. Using the standard “Paste” command <Ctrl-V>: The spreadsheet did not completely import. It only pasted a fraction of the original spreadsheet. Also, each character was imported as a separate text element. See the result of this paste below:


Using MicroStation’s “Paste” command, the spreadsheet was not wholly imported and each character was imported as a separate text element.

2. Selecting from the “Edit” menu “Paste Special | Linked Microsoft Excel Worksheet”: This paste yielded the same results – the spreadsheet was only partially imported and each character came in as a text element.


Using MicroStation’s “Paste Special | Linked Microsoft Excel Worksheet” command, the spreadsheet was not wholly imported and each character was imported as a separate text element.

3. Selecting Microsoft Office Importer from the Axiom pull-down menu. I then clicked on Microsoft Office Importer’s “Paste” icon. Lastly, I accepted the origin of the paste. A few seconds later, I had imported the spreadsheet in its entirety. Also, each word was imported as a single text element (not a separate text element for each individual character). And all the text in multi-word spreadsheet cells were grouped in text nodes.


The text in the spreadsheet imported with Microsoft Office Importer was pasted as text elements, grouped per cell.

Test #1 conclusion
When importing very large spreadsheets, Microsoft Office Importer has the advantage over MicroStation’s native tools.

Test #2
I opened up the heavily formatted spreadsheet in Microsoft Excel and selected all of its contents. See image below.

I hit <Ctrl+C> on my keyboard (to copy) and switched to my open design file. Once in MicroStation, I pasted the large spreadsheet three ways:

1. Standard “Paste” command <Ctrl-V>: After pasting the spreadsheet in MicroStation, it did not look like the Excel spreadsheet at all. The spreadsheet appeared to be squashed, and it was missing data. Also, each the text was imported as individual characters.


The spreadsheet imported using the “Paste” command has serious flaws like: 1) text imported as individual characters, 2) formatting does not look at all like original spreadsheet and 3) data was omitted from the paste.

2. Selecting from the “Edit” menu “Paste Special | Linked Microsoft Excel Worksheet”:


The spreadsheet imported using the “Paste Special | Linked Microsoft Excel Worksheet” command has serious flaws like: 1) text imported as individual characters, 2) formatting does not look at all like original spreadsheet and 3) data was omitted from the paste.

The results were similar to the first pasting. The formatting for most of the lines and text was not the same as in Excel, so the spreadsheet did not look much like the original. It was also missing data.

3. Using Microsoft Office Importer:

Using Microsoft Office Importer, I imported this spreadsheet with no problem. All formatting was kept and the intended default fonts were used. Also, all the data from the spreadsheet was imported.



The Microsoft Office Importer paste imported the entire spreadsheet and looks exactly like the source spreadsheet.

Test #2 conclusion
Microsoft Office Importer offers more superior and advanced formatting capabilities than MicroStation’s native tools.

Summary
Out of the two tests I conducted, Microsoft Office Importer proved itself able to handle the importing of Excel spreadsheets better than MicroStation alone. Microsoft Office Importer handled a huge spreadsheet in one paste, and it maintained the formatting of a complex spreadsheet upon import. Knowing this, drafters can now re-allocate more time to actual design work and not waste time importing spreadsheets section by section.