C # API: Generate and read Excel files

Source: Internet
Author: User

We want to provide users with some data. We think that Excel files are better for readability of users (persons rather than machines.

Compared with csv, xml, and other files, Excel supports automatic filtering, window locking, background color, foreground color, Font, grid, and so on...

The business logic is not complex, and the file content and format are relatively fixed. Therefore, we decided to use C # to create these files directly.

 

As a result, I first came to this link: C # Excel Tutorial

It contains the following sample code for these topics. The example is very detailed and can be compiled and run directly.

  • How to create Excel file in C #
  • How to open an Excel file in C #
  • How to read an 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 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 OLEDB
  • How to insert data to Excel file using OLEDB
  • How to update data in Excel file using OLEDB
  • How to export databse to excel file
  • How to export DataGridView to excel file

 

To understand the above Code, you need to understand the Excel object model. You can refer to the following link in msdn.

Excel Object Model Overview

 

This section describes four 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 easy to understand. The key lies in the fourth object: Range.

At first, I thought that changing the font color format of some cells should be done through the Cell object.

After reading it, we found that these operations are actually completed through Range.

It also includes and cells, setting grid lines, and so on. Even reading and setting the value of a cell can be done through Range,

Therefore, when we operate excel, we usually deal with Range objects, but seldom use cells and other objects.

If you have any requirements and do not know how to implement them,

We recommend that you first go to the Range object and check the Members, Methods, and Properties documents of the Range object on msdn.

 

Another thing to add is:

Not only C #, but also Excel APIs in other languages, which directly simulate and package Excel object models, but Java and Ruby.

That is to say, the Excel API in other languages will also have the four main objects above, and they will do almost the same thing in the same way.

 

 

Next I want to write about Missing. Value.

The code for the example of creating an Excel table is reproduced as follows:

In this Code, many function calls pass this parameter:

Object misValue = System. Reflection. Missing. Value;

For more information, see the example above Missing Class,

Simply put:

When I want to call methods in some dll using the default values of some parameters, it is not correct to ignore these parameters in the method call,

It is not correct to pass these parameters with null,

In this case, we can assign values to these parameters to inform the runtime environment of Missing. Value. Use the default Value of this parameter to run it for me.

 

You need to pay attention to this Code:

Pay attention to the cleanup of Application, Workbook, and Worksheet objects.

Save the stored file, close the close file, and the quit file. Finally, all of them should be release.

 

C # code  
  1. Using System;
  2. Using System. Windows. Forms;
  3. Using Excel = Microsoft. Office. Interop. Excel;
  4. Namespace WindowsApplication1
  5. {
  6. Public partial class Form1: Form
  7. {
  8. Public Form1 ()
  9. {
  10. InitializeComponent ();
  11. }
  12. Private void button#click (object sender, EventArgs e)
  13. {
  14. Excel. Application xlApp;
  15. Excel. Workbook xlWorkBook;
  16. Excel. Worksheet xlWorkSheet;
  17. Object misValue = System. Reflection. Missing. Value;
  18. XlApp = new Excel. ApplicationClass ();
  19. XlWorkBook = xlApp. Workbooks. Add (misValue );
  20. XlWorkSheet = (Excel. Worksheet) xlWorkBook. Worksheets. get_Item (1 );
  21. XlWorkSheet. Cells [1, 1] = "http://csharp.net-informations.com ";
  22. XlWorkBook. SaveAs ("csharp-Excel.xls", Excel. XlFileFormat. xlWorkbookNormal, misValue, Excel. XlSaveAsAccessMode. xlExclusive, misValue, misValue );
  23. XlWorkBook. Close (true, misValue, misValue );
  24. XlApp. Quit ();
  25. ReleaseObject (xlWorkSheet );
  26. ReleaseObject (xlWorkBook );
  27. ReleaseObject (xlApp );
  28. MessageBox. Show ("Excel file created, you can find the file c: \ csharp-Excel.xls ");
  29. }
  30. Private void releaseObject (object obj)
  31. {
  32. Try
  33. {
  34. System. Runtime. InteropServices. Marshal. ReleaseComObject (obj );
  35. Obj = null;
  36. }
  37. Catch (Exception ex)
  38. {
  39. Obj = null;
  40. MessageBox. Show ("Exception Occured while releasing object" + ex. ToString ());
  41. }
  42. Finally
  43. {
  44. GC. Collect ();
  45. }
  46. }
  47. }
  48. }

 

 

 

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

Take the background color as an example. First, we can write the following code:

 

C # code  
  1. Excel. Range chartRange;
  2. ChartRange = xlWorkSheet. get_Range ("a1", "e4 ");
  3. ChartRange. Interior. Color = 255;

 

However, this 255 is not easy to remember. For example, if the blue number is 16777164, this is even worse.

So we can set the color in this way and use ColorTranslator to convert it:

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

 

Another method is to set the Color instead of the ColorIndex, as shown in the following figure:

C # code  
  1. ChartRange. Interior. ColorIndex = 3;

 

For the ColorIndex color table, see the following two links:

Color Palette and the 56 Excel ColorIndex Colors

Excel Color Palette and Color Index change using VBA

The first connection is particularly detailed,

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.