Using C # to write to Excel

One. COM Interop

First we need to understand what is COM Interop, a service that allows. NET framework objects to communicate with COM objects. Visual Studio. NET changes the way developers create and run applications by introducing managed code concepts that target the common language runtime. But it also makes it inherently more specific to programming with Windows-based APIs and COM objects. Visual Studio. NET enables managed objects and unmanaged objects to work together in a process called interoperability (interoperability), often referred to as interop.

Two. Excel com namespace Microsoft.Office.Interop.Excel key: When you save Excel, use SaveAs () or SaveCopyAs () to create a new file, It is then saved Application.Quit () because it is unmanaged, the object is not disposed, and the process is not guaranteed to exit completely.  1) You can use KillProcess (), but it is possible to mistakenly terminate other Excel processes 2) to set the application instance to null. Implementation process: 1. Here, the operation of the Excel table is encapsulated in a class called Exceloperator, which has the following methods. There are, of course, other assistive methods that will unfold when implemented.

1 classExceloperator2 {3   //constructor, column name as input parameter4          PublicExceloperator (list<string>_columnheader)5         {6              This. ColumnHeader =_columnheader;7         }8   //open an Excel table header9          Public voidOpenexcel ();Ten   //Enter a row record value One          Public BOOLExporttoexcel<t> (ienumerable<t>_records); A   //Save and close -          Public BOOLsaveandclose (); - }   

2. Open Excel table in the background: Generate a new Excel process---> Cancel confirmation Dialog---> Add a workbook to the process---> Gets the worksheet of the workbook (by default a new workbook has a worksheet)---> Formatting table. The specific code is as follows:

1  Public voidOpenexcel ()2         {3Excelprocess =Newexcelnamespace.application ();4             if(Excelprocess = =NULL)Throw NewException ("Excel application is NULL");5                 Try6                 {7Excelprocess.visible =false;8   //Cancel the prompt, you can directly save the modified table, important9Excelprocess.displayalerts =false;TenExcelprocess.alertbeforeoverwriting =false; OneWorkbook =excelProcess.Workbooks.Add (); Aworksheet = Workbook. Sheets.get_item (1); -Worksheet. Name =FileName; -                     if(Worksheet = =NULL)Throw NewException ("Excel WorkSheet is NULL"); the   //Auto-Populate header or other content operations - Initialworksheet (); -   //Formatting Excel Forms - Formatworksheet (); +                      This. Flagisopen =true; -                 } +                 Catch(Exception e) A                 { at                    ...... -                 } -}

You can notice that the above method also has two sub-methods, the following two sub-methods of implementation of the process is briefly described below:

1         /// <summary>2         ///Populate Excel Header names3         /// </summary>4         Virtual protected voidInitialworksheet ()5         {6StartRow =1;7StartColumn =1;8              This. Worksheet. Cells[startrow, StartColumn]. Select ();9             foreach(stringHeaderinch  This. ColumnHeader)Ten             { One                  This. ExcelProcess.ActiveCell.Value =header; A                  This. excelprocess.activecell.offset[0,1]. Select (); -             } -startrow++; the         } -  -         /// <summary> -         ///Formatting Tables +         /// </summary> -         Virtual protected voidFormatworksheet () +         { A             //Center Horizontally at              This. worksheet. Cells.horizontalalignment =ExcelNameSpace.XlVAlign.xlVAlignCenter; -             //Set column Name box color -             varHCell = worksheet. cells[1, This. Columnheader.count]; -              This. excelprocess.range["A1", HCell]. Interior.ColorIndex = -; -              This. excelprocess.range["A1", HCell]. Borders.LineStyle =1; -             This. excelprocess.range["A1", HCell]. Borders[excelnamespace.xlbordersindex.xledgeleft]. Weight =ExcelNameSpace.XlBorderWeight.xlThin; in           ...... -}

In the Initialworksheet () method, you can see that there are two global variables StartRow and StartColumn, which are used to record the location of the next valid input.

3. Insert row record, I am here to realize the function of BULK INSERT record. For ease of use, a record class was first written, containing a list<string> member to record a single row. Then pass in a list<record> parameter. In fact, simple point, direct transmission of a list<string> parameter, each time a separate record is also possible. But for practice. The specific code is as follows:

1         /// <summary>2         ///adding records using the Record collection3         /// </summary>4         Virtual  Public BOOLExporttoexcel<t> (ienumerable<t> _records)whereT:record5         {6             Try7             {8                  This. Worksheet. Cells[startrow, StartColumn]. Select ();9                 foreach(varRecordinch_records)Ten                 { One                      This. Worksheet. Cells[startrow, StartColumn]. Select (); A                     foreach(varIteminchrecord) -                     { -                          This. ExcelProcess.ActiveCell.Value =item; the                          This. excelprocess.activecell.offset[0,1]. Select (); -                     } -startrow++; -                 } +                 return true; -             } +             Catch(Exception e) A             { at                ...... -             } -}

4. Save and Close:

1          Public BOOLsaveandclose ()2         {
Save a shared workbook in a saved-as way, using Generatefileurl () to generate a file storage path3              This. Workbook. SaveAs (Generatefileurl ());4              This. Workbook. Close ();5              This. Flagisopen =false;
Close Process6              This. Excelprocess.quit ();
Guaranteed release of unmanaged objects7System.Runtime.InteropServices.Marshal.ReleaseComObject ( This. excelprocess);8              This. excelprocess =NULL;9             return true;Ten}

The 5.ExcelOperator class also contains properties such as FileName, FilePath (file Store path), and is implemented on your own.

Using C # to write to Excel