C # API: Generate and read Excel files

We want to provide users with some data, think twice, we believe that the user (people, not the machine) readability, Excel file better.

Because compared to csv,xml and other files, Excel can use automatic filter, window lock, but also control background color, foreground color, font, grid and so on ...

Business logic is not complex, the content and format of the file is also relatively fixed, so people decided to take C # directly to create these files.

So a search, first came to this link: C # Excel Tutorial

It contains code examples for the following topics, which are detailed and compiled to run directly.

• How to create an Excel file in C #
• How to open a Excel file in C #
• How to read a Excel file in CSharp
• How to format an Excel file using C #
• How to insert a picture in Excel from C # App
• How to insert a background picture in Excel
• How to create an Excel Chart from C #
• How to export Excel chart from C #
• How to Excel chart in C # PictureBox
• C # Data validation input box in Excel file
• How to read from an Excel file using OLE DB
• How to insert the data to Excel file using OLE DB
• How to update the data in Excel file using OLE DB
• How to export databse to Excel file
• How to export DataGridView to Excel file

To understand the above code needs to understand the Excel object model, you can refer to the MSDN link below

Excel Object Model Overview

It introduces 4 Core objects:

• Microsoft.Office.Interop.Excel.Application

• Microsoft.Office.Interop.Excel.Workbook

• Microsoft.Office.Interop.Excel.Worksheet

• Microsoft.Office.Interop.Excel.Range

The first three objects are a good understanding, the key is the fourth object: Range.

At first I thought that changing the font color format of some cells, and so on, would need to be done through the Cell object.

After reading it, we found that these operations are all done by range.

Also includes and cells, set grid lines, and even read and set the value of a cell, can be done by range,

So basically when we're working with Excel, we're most often dealing with range objects and seldom using objects such as cells.

If there is any need not to know how to achieve,

It is recommended that you go through the Range object and look at the relevant documentation for the members,methods,properties of the Range object on MSDN.

One more thing to add:

Not only C #, but also the Excel API for other languages, is the direct simulation and packaging of the Excel object model. But Java,ruby and so on.

That is, the Excel API in other languages will have the top four main objects, and it will do almost the same thing in almost the same way.

And then I want to write about Missing.Value.

For the preceding example, the code for creating an Excel table is reproduced as follows:

In this code, many function calls pass this parameter:

Object misvalue = System.Reflection.Missing.Value;

His detailed introduction refer to this link missing Class above example,

To put it simply:

When I want to invoke some of the methods in the DLL with the default values of some parameters, it is not right to ignore them in the method call.

It's not right for us to pass NULL to these parameters.

At this point we can assign values to the Missing.Value to tell the runtime environment, using the default value of this parameter to help me run it.

There is one more thing to note about this code:

Note that the cleanup of these three objects is Application,workbook,worksheet

The saved save, the close close, the quit quit, and finally, they should all be release

C # code

2. Using System;
4. Using System.Windows.Forms;
6. Using Excel = Microsoft.Office.Interop.Excel;
10. Namespace WindowsApplication1
12. {
14. Public partial class Form1:form
16. {
18. Public Form1 ()
20. {
22. InitializeComponent ();
24. }
28. private void button1_click (object sender, EventArgs e)
30. {
32. Excel.Application xlapp;
34. Excel.Workbook Xlworkbook;
36. Excel.Worksheet Xlworksheet;
38. object misvalue = System.Reflection.Missing.Value;
42. xlapp = new Excel.applicationclass ();
44. Xlworkbook = XlApp.Workbooks.Add (Misvalue);
48. Xlworksheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item (1);
50. Xlworksheet.cells[1, 1] = "http://csharp.net-informations.com";
54. Xlworkbook.saveas ("Csharp-excel.xls", Excel.XlFileFormat.xlWorkbookNormal, Misvalue, Misvalue, Misvalue,  Misvalue, Excel.XlSaveAsAccessMode.xlExclusive, Misvalue, Misvalue, Misvalue, Misvalue, Misvalue);
56. Xlworkbook.close (True, Misvalue, Misvalue);
58. xlApp.Quit ();
62. Releaseobject (Xlworksheet);
64. Releaseobject (Xlworkbook);
66. Releaseobject (xlapp);
70. MessageBox.Show ("Excel file created, you can find the file C:\\csharp-excel.xls");
72. }
76. private void Releaseobject (object obj)
78. {
80. Try
82. {
84. System.Runtime.InteropServices.Marshal.ReleaseComObject (obj);
86. obj = null;
88. }
90. catch (Exception ex)
92. {
94. obj = null;
96. MessageBox.Show ("Exception occured while releasing object" + Ex.)  ToString ());
98. }
100. finally
102. {
104. Gc. Collect ();
106. }
108. }
110. }
112. }

Finally, I want to write about how to set the color of the font, as well as the background color of the cell.

With the background color set to red for example, we can first write a code like this:

C # code  

1. Excel.Range Chartrange;
2. Chartrange = Xlworksheet.get_range ("A1", "E4");
3. ChartRange.Interior.Color = 255;

But this 255 is very difficult to remember, such as cyan corresponding to the number is 16777164, this is more difficult to understand.

So we can set the color instead, using ColorTranslator to convert it:

C # code  

1. ChartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle (System.Drawing.Color.Red);

Finally, there is a way to set the color not to set, but to set the corresponding ColorIndex, as follows:

C # code  

1. ChartRange.Interior.ColorIndex = 3;

The ColorIndex color table references the following two connections:

Color Palette and the ColorIndex Excel Colors

Excel Color Palette and color Index change using VBA

The first connection is particularly detailed,

The second connection describes how to use VBA to generate these color tables in an Excel file, and also provides an XLS format for the color table file download.