C # Excel File Import and Export

From http://www.cnblogs.com/zxz414644665/archive/2010/08/06/1794430.html

C # There are about three or four Excel operations. Today I will paste one of them for later use.

Code

//////////////////////////////////////// ///////////////////////////////////
// Purpose: Import and Export an Excel file, which must reference Microsoft Excel 11.0 Object Library
// Author: dangmy
// Date: 2007-03-09
// Version 1.0
//////////////////////////////////////// ///////////////////////////////////
Using system;
Using system. Data;
Using system. Data. oledb;

Namespace excelio
{
Public class excelio
{
Private int _ returnstatus;
Private string _ returnmessage;

///

/// Execution return status
///

Public int returnstatus
{
Get {return _ returnstatus ;}
}

///

/// Execution return information
///

Public String returnmessage
{
Get {return _ returnmessage ;}
}

Public excelio ()
{
}

///

/// Import EXCEL to Dataset
///

///Excel full path file name
/// Imported Dataset
Public dataset importexcel (string filename)
{
// Determine whether Excel is installed
Microsoft. Office. InterOP. Excel. Application xlapp = new Microsoft. Office. InterOP. Excel. applicationclass ();
If (xlapp = NULL)
{
_ Returnstatus =-1;
_ Returnmessage = "an Excel object cannot be created. Excel may not be installed on your computer ";
Return NULL;
}

// Determine whether the file is used by other processes
Microsoft. Office. InterOP. Excel. Workbook workbook;
Try
{
Workbook = xlapp. workbooks. open (filename, 0, false, 5, "", "", false, Microsoft. office. interOP. excel. xlplatform. xlwindows, "", true, false, 0, true, 1, 0 );
}
Catch
{
_ Returnstatus =-1;
_ Returnmessage = "the Excel file is open. Save and close it ";
Return NULL;
}

// Obtain all sheet names
Int n = Workbook. worksheets. count;
String [] sheetset = new string [N];
System. Collections. arraylist Al = new system. Collections. arraylist ();
For (INT I = 1; I
/// Export the datatable to excel
///
///Report Name
///Data source table
///Excel full path file name
/// Export successful?
Public bool exportexcel (string reportname, system. Data. datatable DT, string savefilename)
{
If (Dt = NULL)
{
_ Returnstatus =-1;
_ Returnmessage = "the dataset is empty! ";
Return false;
}

Bool filesaved = false;
Microsoft. Office. InterOP. Excel. Application xlapp = new Microsoft. Office. InterOP. Excel. applicationclass ();
If (xlapp = NULL)
{
_ Returnstatus =-1;
_ Returnmessage = "an Excel object cannot be created. Excel may not be installed on your computer ";
Return false;
}

Microsoft. Office. InterOP. Excel. workbooks = xlapp. workbooks;
Microsoft. Office. InterOP. Excel. Workbook workbook = workbooks. Add (Microsoft. Office. InterOP. Excel. xlwbatemplate. xlwbatworksheet );
Microsoft. Office. InterOP. Excel. worksheet = (Microsoft. Office. InterOP. Excel. worksheet) Workbook. worksheets [1]; // get sheet1
Worksheet. cells. Font. size = 10;
Microsoft. Office. InterOP. Excel. Range range;

Long totalcount = DT. Rows. count;
Long rowread = 0;
Float percent = 0;

Worksheet. cells [1, 1] = reportname;
(Microsoft. Office. InterOP. Excel. Range) worksheet. cells [1, 1]). Font. size = 12;
(Microsoft. Office. InterOP. Excel. Range) worksheet. cells [1, 1]). Font. Bold = true;

// Write Fields
For (INT I = 0; I 0)
{
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidehorizontal]. colorindex = Microsoft. Office. InterOP. Excel. xlcolorindex. xlcolorindexautomatic;
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidehorizontal]. linestyle = Microsoft. Office. InterOP. Excel. xllinestyle. xlcontinuous;
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidehorizontal]. Weight = Microsoft. Office. InterOP. Excel. xlborderweight. xlthin;
}
If (Dt. Columns. Count> 1)
{
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidevertical]. colorindex = Microsoft. Office. InterOP. Excel. xlcolorindex. xlcolorindexautomatic;
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidevertical]. linestyle = Microsoft. Office. InterOP. Excel. xllinestyle. xlcontinuous;
Range. Borders [microsoft. Office. InterOP. Excel. xlbordersindex. xlinsidevertical]. Weight = Microsoft. Office. InterOP. Excel. xlborderweight. xlthin;
}

// Save the file
If (savefilename! = "")
{
Try
{
Workbook. Saved = true;
Workbook. savecopyas (savefilename );
Filesaved = true;
}
Catch (exception ex)
{
Filesaved = false;
_ Returnstatus =-1;
_ Returnmessage = "An error occurred while exporting the file. The file may be opened! \ N "+ ex. message;
}
}
Else
{
Filesaved = false;
}

// Release the corresponding Excel Object
If (range! = NULL)
{
System. runtime. interopservices. Marshal. releasecomobject (range );
Range = NULL;
}
If (worksheet! = NULL)
{
System. runtime. interopservices. Marshal. releasecomobject (worksheet );
Worksheet = NULL;
}
If (workbook! = NULL)
{
System. runtime. interopservices. Marshal. releasecomobject (workbook );
Workbook = NULL;
}
If (workbooks! = NULL)
{
System. runtime. interopservices. Marshal. releasecomobject (workbooks );
Workbooks = NULL;
}
Xlapp. application. workbooks. Close ();
Xlapp. Quit ();
If (xlapp! = NULL)
{
System. runtime. interopservices. Marshal. releasecomobject (xlapp );
Xlapp = NULL;
}
GC. Collect ();
Return filesaved;
}
}
}