Menu
I have some tabular data that I'd like to turn into an Excel table.
Software available:
Information about the data:
Searching online gives many results, and I'm confused whether I should use OleDb, ADO RecordSets, or something else. Some of these technologies seem like overkill for my scenario, and some seem like they might be obsolete.
What is the very simplest way to do this?
Edit: this is a one-time script I intend to run from my attended desktop.
674
Automation of an Excel file allows us to doing various operations from C#. We can automate an Excel file from C# in two ways. Using Excel Object Model in one way and another way is using Microsoft Jet Engine to connect Excel from CSharp.
On the Home tab, in the Styles group, click Format as Table. Click the table style that you want to use. Tips: Auto Preview - Excel will automatically format your data range or table with a preview of any style you select, but will only apply that style if you press Enter or click with the mouse to confirm it.
To add a blank table, select the cells you want included in the table and click Insert > Table. To format existing data as a table by using the default table style, do this: Select the cells containing the data. Click Home > Table > Format as Table.
Some things for your consideration...
If this is a client side solution, there is nothing wrong with using Interops. If this is a server side solution, Don't use Interops. Good alternative is OpenXML SDK from Microsoft if you don't want 3rd party solution. It's free. I believe the latest one has similar object model that Excel has. It's a lot faster, A LOT, in generating the workbook vs going the interops way which can bog down your server.
54
Avoid using COM interop at all costs. Use a third-party API. Really. In fact, if you're doing this server-side, you virtually have to. There are plenty of free options. I highly recommend using EPPlus, but there are also enterprise-level solutions available. I've used EPPlus a fair amount, and it works great. Unlike interop, it allows you to generate Excel files without requiring Excel to be installed on the machine, which means you also don't have to worry about COM objects sticking around as background processes. Even with proper object disposal, the Excel processes don't always end.
http://epplus.codeplex.com/releases/view/42439
I know you said you want to avoid third-party libraries, but they really are the way to go. Microsoft does not recommend automating Office. It's really not meant to be automated anyway.
http://support.microsoft.com/kb/257757
However, you may want to reconsider inserting "a couple million rows" into a single spreadsheet.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
