C # database data import and export series 3 export the database to an Excel file

 

In daily projects, it is very common to import data in Excel, Word, txt, and other formats to the database. Here I will make a summary

 

This section describes how to import SQL Server, Oracle database, and WinForm into SQL Server and Oracle database.

 

Import and export the database here, which is applicable to both SQL Server and Oracle.

 

If you use ADO. Net to connect to the Oracle database, you need to add "System. Data. OracleClient" in the reference. Other aspects are the same as connecting to the SQL Server database.

 

SqlConnection cn = new SqlConnection ();

OracleConnection oraleCn = new OracleConnection ();

 

If you use persistence layer frameworks such as Ibatis, the only difference is the difference in database connection statements. The following are two examples:

 

Oracle: Data Source = 192.168.0.11/Contact; User ID = system; Password = ss; Unicode = True

 

SQL Server: Data Source = Contact; Server = localhost; uid = sa; pwd = ss

 

 

 

Continue with the previous article.

 

2. Use an object set to export an Excel file

 

First, an ExcelHelper class downloaded from the Internet.

 

The basic idea is to first obtain the attribute to be displayed from the passed attribute name information based on reflection.

 

Then retrieve its value based on the property

 

 

Using System;

Using System. Collections. Generic;

Using System. Web;

Using System. Reflection;

Using System. Text;

 

Namespace Common. Classes

{

Public class ExcelHelper

{/**/

/// <Summary>

/// Export a group of objects to EXCEL

/// </Summary>

/// <Typeparam name = "T"> type of the object to be exported </typeparam>

/// <Param name = "objList"> A group of objects </param>

/// <Param name = "FileName"> name of the exported file </param>

/// <Param name = "columnInfo"> column name Information </param>

Public static void ExExcel <T> (List <T> objList, string FileName, Dictionary <string, string> columnInfo, string FileType)

{

ExExcel (objList, FileName, columnInfo, FileType, null );

}

/**/

/// <Summary>

/// Export a group of objects to EXCEL

/// </Summary>

/// <Typeparam name = "T"> type of the object to be exported </typeparam>

/// <Param name = "objList"> A group of objects </param>

/// <Param name = "FileName"> name of the exported file </param>

/// <Param name = "columnInfo"> column name Information </param>

/// <Param name = "other"> append other content </param>

Public static void ExExcel <T> (List <T> objList, string FileName, Dictionary <string, string> columnInfo, string FileType, string other)

{

If (columnInfo. Count = 0)

{

Return;

}

If (objList. Count = 0)

{

Return;

}

// Generate EXCEL HTML

String excelStr = "";

 

Type myType = objList [0]. GetType ();

// Obtain the attribute to be displayed from the passed attribute name information based on reflection

List <PropertyInfo> myPro = new List <PropertyInfo> ();

Foreach (string cName in columnInfo. Keys)

{

PropertyInfo p = myType. GetProperty (cName );

If (p! = Null)

{

MyPro. Add (p );

ExcelStr + = columnInfo [cName] + "\ t ";

}

}

// Ends if no available attributes are found.

If (myPro. Count = 0)

{

Return;

}

ExcelStr + = "\ n ";

 

Foreach (T obj in objList)

{

Foreach (PropertyInfo p in myPro)

{

// This is to process the time format

If (p. Name = "CBirthday ")

{

String str = p. GetValue (obj, null). ToString ();

String strs = (str = "0001-1-1 0:00:00 ")? "": Str;

If (strs = "")

{

ExcelStr + = strs + "\ t ";

}

Else

{

ExcelStr + = Convert. ToDateTime (strs). tow.datestring () + "\ t ";

}

}

Else

{

ExcelStr + = p. GetValue (obj, null) + "\ t ";

}

}

ExcelStr + = "\ n ";

}

If (! String. IsNullOrEmpty (other ))

{

ExcelStr + = other;

}

// Output EXCEL

HttpResponse rs = System. Web. HttpContext. Current. Response;

Rs. Clear ();

Rs. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312 ");

Rs. AppendHeader ("Content-Disposition", "attachment; filename =" + System. Web. HttpUtility. UrlEncode (FileName, Encoding. UTF8 ));

Rs. ContentType = FileType;

Rs. Write (excelStr );

Rs. End ();

}

 

}

}

The default time in the database is "0001-1-1 0:00:00", which is certainly not easy to display on the interface. In order to adapt to the specific situation in the program, I have simply processed the "CBirthday" column.

 

Next, let's take a look at the call method.

 

 

Public class ContactPersonExport

{

Public string CPName {get; set ;}

Public string CPSex {get; set ;}

Public string CPBirthday {get; set ;}

}

Contact humans.

Then you can call it in the method. The specific value of the contactList set is written by everyone.

 

 

List <ContactPersonExportDomain> contactList = (List <ContactPersonExportDomain>) Helper. ContactExport (). exportdataworkflow Excel ();

Dictionary <string, string> columnInfo = new Dictionary <string, string> ();

ColumnInfo. Add ("CPName", "contact name ");

ColumnInfo. Add ("CPSex", "gender ");

ColumnInfo. Add ("CPBirthday", "Birthday ");

String FileName = "test.xls ";

String FileType = "application/ms-excel" // MIME type of Excel

ExcelHelper. ExExcel <ContactPersonExportDomain> (contactList, FileName, columnInfo, FileType );

 

From the cpcpc Column