SQL Server and Excel Import and Export

Source: http://blog.csdn.net/qygaojiansheng/archive/2009/04/26/4126364.aspx

 

If the table already exists, the SQL statement is:

Insert into AA select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = D: \ outdata.xls; extended properties = Excel 8.0 ')... [sheet1 $]

 

Here, AA is the table name, and D: \ outdata.xls is the full path of Sheet 1 in Excel, which must be added with $

 

If the table does not exist, the SQL statement is:

Select * into AA from OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = D: \ outdata.xls; extended properties = Excel 8.0 ')... [sheet1 $]

 

Here, AA is the table name, and D: \ outdata.xls is the full path of Sheet 1 in Excel, which must be added with $

 

Possible exceptions:

 

 

If an error is reported for the ole db access interface "Microsoft. Jet. oledb.4.0" of "linked server" (null. ProvideProgramNo information about the error is provided.

Unable to initialize the data source object for the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null ." The exception may be that the Excel file is not closed.

 

 

If "null values cannot be inserted into the 'grade 'column, the 'Student. DBO. stugrade' Column cannot have null values. Insert failed.

The statement has been terminated ." Exception. the Excel file does not match the fields in the database table.

 

 

The above operations are Office 2003. If you want to operate Office 2007, use the following method:

 

If the table already exists, the SQL statement is:

Insert into AA select * From OpenDataSource ('Microsoft. Ace. oledb.12.0 ',

'Data source = D: \ outdata.xls; extended properties = Excel 12.0 ')... [sheet1 $]

 

Here, AA is the table name, and D: \ outdata.xls is the full path of Sheet 1 in Excel, which must be added with $

 

If the table does not exist, the SQL statement is:

Select * into AA from OpenDataSource ('Microsoft. Ace. oledb.12.0 ',

'Data source = D: \ outdata.xls; extended properties = Excel 12.0 ')... [sheet1 $]

 

Here, AA is the table name, and D: \ outdata.xls is the full path of Sheet 1 in Excel, which must be added with $

 

 

If an error is reported for the ole db access interface "Microsoft. Jet. oledb.4.0" of "linked server" (null. The provider does not provide any information about the error.

Unable to initialize the data source object for the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null ." The exception may be that the Excel file is not closed.

 

 

If "null values cannot be inserted into the 'grade 'column, the 'Student. DBO. stugrade' Column cannot have null values. Insert failed.

The statement has been terminated ." Exception. the Excel file does not match the fields in the database table.

The above operations are Office 2003. If you want to operate Office 2007, use the following method:

 

 

In addition, you need to configure some features:

1. Open the SQL Server 2005 peripheral application configurator, select "peripheral application configurator of the function", select "enable OpenRowSet or OpenDataSource support", and click "OK.

2. In the C: \ Windows Directory, add the Security Tab Of the temp folder to the user or group name, select the "sqlserver2005reportingserviceswebserviceuser $ pc17 $ MSSQLServer (pc17/sqlserver2005reportingserviceswebserviceuser $ pc17 $ MSSQLServer" user to write and modify permissions for this user. Click OK. (Set it because a temporary file is created under c: \ windows \ temp when the Excel file is read into the SQL database, therefore, you need to set the sqlserver2005 permission for this folder to writable. This setting is not required if you are using an administrator account. Because the Administrator has read and write permissions .)

 

 

Export

USE insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 5.0; HDR = yes; IMEX = yes; database = c: \ Documents and Settings \ Administrator \ Desktop \ export2.xls', [sheet1 $]) select * From stugrade can export data to excel2003, but the premise must be that the table already exists and the field names already exist and correspond to the table. Using the following Automatic File Creation and header will cause an exception and cannot be inserted. Currently, you can insert only one entry.

To solve this problem, you can first create an Excel file and add the header. You can use the following statement:

String filepath = "C: \ Documents and Settings \ Administrator \ Desktop \ export3.xls ";

Sqlconnection conn = new sqlconnection ("Server =.; database = student; Integrated Security = true ");

Conn. open ();

Sqlcommand comm = new sqlcommand ("select * From stugrade", Conn );

Sqldataadapter da = new sqldataadapter (Comm );

Dataset DS = new dataset ();

Da. Fill (DS, "stugrade ");

Microsoft. Office. InterOP. Excel. _ application xlapp = new applicationclass ();

Workbook xlbook = xlapp. workbooks. Add (true );

Worksheet xlsheet = (worksheet) xlbook. worksheets [1];

Int colindex = 0;

Int rowindex = 1;

// Start writing the title of each column

Foreach (datacolumn DC in DS. Tables [0]. columns)

{

Colindex ++;

Xlsheet. cells [rowindex, colindex] = Dc. Caption;

}

Xlbook. Saved = true;

Xlbook. savecopyas (filepath); // create a file

 

To use this method, you must add the reference "using Microsoft. Office. InterOP. Excel ;".

In this way, use the preceding SQL statement.

 

When exporting data to offline. jet. oledb.4.0 and Excel 5.0 are replaced with Microsoft. ace. oledb.12.0 and Excel 12.0 Replace the table name with the table name of excel2003, so that only one row can be exported, and an exception occurs. This problem remains to be resolved.

 

USE insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = D: \ export.xls; extended properties = Excel 5.0 ')... [sheet1 $]

(Field name) values (corresponding value) can also be imported. The premise must be that the table already exists and the field names already exist and correspond to the table. You can only import one entry at a time. The value must be office2003 and can be imported with office2007, but an exception occurs.

 

 

 

It is usually not practical to use an SQL statement for import and export. Because there are too many restrictions on using an SQL statement, a record is usually written into the database, use the following method to import data to a database. The principle is to use an Excel file as a data table:

 

 

 

 

 

 

 

 

Import

CodeIs:

String strexcelfilename = @ "D: \ outdata.xls"; // Excel File

String strsheetname = "sheet1"; // worksheet name

String strconn = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + strexcelfilename + "; extended properties = 'excel 8.0; HDR = no; IMEX = 1 '";

// Connection string

String strexcel = "select * from [" + strsheetname + "$]"; // SQL statement

 

// Define the stored data table

Dataset DS = new dataset ();

 

// Connect to the data source

Oledbconnection conn = new oledbconnection (strconn );

 

Conn. open ();

 

// Adapt to the data source

Oledbdataadapter adapter = new oledbdataadapter (strexcel, Conn );

Adapter. Fill (DS, strsheetname + "$ ");

Conn. Close ();

 

// In general, the first row of the. Excel table is the column name.

Datagridview1.datasource = Ds. Tables ["res"]; // bind data to the datagridview

 

 

Export

Method 1: export data from dataset to excel

String filepath = "C: \ Documents and Settings \ Administrator \ Desktop \ export4.xls"; // exported file name and Path

String reportname = "AAAAA"; // Add a file header to the file during export

Sqlconnection conn = new sqlconnection ("Server =.; database = student; Integrated Security = true"); // define a connection

Conn. open ();

Sqlcommand comm = new sqlcommand ("select * From stugrade", Conn );

Sqldataadapter da = new sqldataadapter (Comm );

Dataset DS = new dataset ();

Da. Fill (DS, "stugrade ");

Microsoft. Office. InterOP. Excel. _ application xlapp = new applicationclass ();

Workbook xlbook = xlapp. workbooks. Add (true );

Worksheet xlsheet = (worksheet) xlbook. worksheets [1];

Range = xlsheet. get_range (xlapp. cells [1, 1], xlapp. cells [1, DS. Tables [0]. Columns. Count]);

Range. mergecells = true;

 

// Define the style for storing text in Cells

Xlapp. activecell. formular1c1 = reportname;

Xlapp. activecell. Font. size = 20;

Xlapp. activecell. Font. Bold = true;

Xlapp. activecell. horizontalalignment = Microsoft. Office. InterOP. Excel. constants. xlcenter;

 

 

Int colindex = 0;

Int rowindex = 2;

// Start writing the title of each column

Foreach (datacolumn DC in DS. Tables [0]. columns)

{

Colindex ++;

Xlsheet. cells [rowindex, colindex] = Dc. Caption;

}

 

// Start writing content

Int rowcount = Ds. Tables [0]. Rows. Count; // number of rows

For (INT I = 0; I <rowcount; I ++)

{

Rowindex ++;

Int colcount = Ds. Tables [0]. Columns. Count; // Number of Columns

For (colindex = 1; colindex <= colcount; colindex ++)

{

Xlsheet. cells [rowindex, colindex] = Ds. Tables [0]. Rows [I] [colindex-1]; // DG [I, colindex-1];

Xlsheet. cells. columnwidth = 10;

}

}

Xlbook. Saved = true;

Xlbook. savecopyas (filepath );

Xlapp. Quit ();

GC. Collect ();

 

 

 

Method 2: export data from the datagridview to excel:

Sqlconnection conn = new sqlconnection ("Server =.; database = student; Integrated Security = true ");

Conn. open ();

Sqlcommand comm = new sqlcommand ("select * From stugrade where stuid = '000000'", Conn); // stugrade indicates the table name and stuid indicates the field name.

Sqldataadapter da = new sqldataadapter (Comm );

Dataset DS = new dataset ();

Da. Fill (DS, "stugrade ");

Microsoft. Office. InterOP. Excel. Application myexcel = new Microsoft. Office. InterOP. Excel. Application ();

Myexcel. Visible = false;

// Define the export path

String Path = "C :\\ Documents and Settings \ Administrator \ Desktop ";

 

Myexcel. application. workbooks. Add (true );

Myexcel. Caption = "abcdefghe ";

Int colunm = 1;

 

For (INT I = 0; I <Ds. Tables [0]. Columns. Count; I ++)

{

Myexcel. cells [1, colunm ++] = Ds. Tables [0]. Columns [I]. Caption;

}

 

For (INT I = 0; I <Ds. Tables [0]. Rows. Count; I ++)

{

Colunm = 1;

For (Int J = 0; j <Ds. Tables [0]. Columns. Count; j ++)

{

Myexcel. cells [I + 2, colunm ++] = Ds. Tables [0]. Rows [I] [J];

}

}

 

Myexcel. activeworkbook. saveas (path, Microsoft. office. interOP. excel. xlfileformat. xlexcel7, null, null, false, false, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, null, null );

Myexcel. Quit ();

 

Theoretically, the export speed from dataset is the same as that from the datagridview. However, from my experiments, exporting data from the datagridview is much faster than exporting data from the dataset. When the data volume is 300 records, using the datagridview is about twice faster than dataset. I do not know why. This method is only suitable for a small amount of data. If the data volume is too large, the time overhead will be very large.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/qygaojiansheng/archive/2009/04/26/4126364.aspx