Hi,
Can you please help me to figure out how I can insert DataGridView values into a pre defined excel template (Into Specific Cells)? I have a DataGridView on my windows Form which is getting the values from user input. Now I would like to enable users to export the DataGridView values into an excel file (A File like attached excel file image). As far as I know I have to create the headers and add them to the code programmatically but for the DataGridView part, honestly I have no idea how I can do that? As you can see the Form (Box) is starting from B2 to K2 and end from B21 to K21 Now my question is how i can start importing values from B4 - k4 and so on? Is there any way I can format the style of the cell (like Background color or font style and size) from C#? I mean generating a form like what is looking in attached Excel programmatically.
Thanks for your time in advance.
You'll need to install Visual Studio Tools for Office (VSTO), which may have been installed with Visual Studio. The right-click on References and click Add References.... Use the .NET tab and add Microsoft.Office.Interop.Excel, but make sure you are using the correct version (eg. 11.0.0.0 for Office 2003, 12.0.0.0 for 2007 etc. (I think...)). Then you can start using it:
using Excel = Microsoft.Office.Interop.Excel;
...
Excel.Application app = new Excel.Application();
Excel.Workbook book = app.Workbooks.Open("c:\\my_template.xls", ReadOnly: true);
Excel.Worksheet sheet = book.Worksheets[0];
...
You'll have to read up a bit on the Office API. Couple things though:
- The user must have the same version of Office installed.
- VS2008 supports Office 2003/2007; VS2010 supports Office 2007/2010 etc (although you can work around that).
There are also project templates that correspond to the above versions, however I've never had a chance to use them (we're stuck at Office 2003 Standard...). If you have any specific questions, I can try to answer them.
P.S. For Office 2007/2010 workbooks (i.e. XLSX) you can unzip them and work with XML files if you like. Microsoft did release the references for those file types.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.