sql server 與 Excel 匯入匯出

來源：http://blog.csdn.net/qygaojiansheng/archive/2009/04/26/4126364.aspx

 

如果表已存在，SQL語句為：

insert into aa select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]

 

其中，aa是表名，D:\OutData.xls是excel的全路徑 sheet1後必須加上$

 

如果表不存在，SQL語句為：

SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]

 

其中，aa是表名，D:\OutData.xls是excel的全路徑 sheet1後必須加上$

 

可能會發生的異常：

 

 

如果發生“連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。

無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。”異常可能是excel檔案未關閉.

 

 

如果發生“不能將值 NULL 插入列 'Grade'，表 'student.dbo.StuGrade'；列不允許有空值。INSERT 失敗。

語句已終止。”異常，則可能是excel檔案與資料庫表中的欄位不匹配

 

 

以上操作的是office 2003,如果要操作office 2007則需採用如下方式

 

如果表已存在，SQL語句為：

insert into aa select * from OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',

'Data Source=D:\OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]

 

其中，aa是表名，D:\OutData.xls是excel的全路徑 sheet1後必須加上$

 

如果表不存在，SQL語句為：

SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',

'Data Source=D:\OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]

 

其中，aa是表名，D:\OutData.xls是excel的全路徑 sheet1後必須加上$

 

 

如果發生“連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報錯。提供者未給出有關錯誤的任何資訊。

無法初始化連結的伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源對象。”異常可能是excel檔案未關閉.

 

 

如果發生“不能將值 NULL 插入列 'Grade'，表 'student.dbo.StuGrade'；列不允許有空值。INSERT 失敗。

語句已終止。”異常，則可能是excel檔案與資料庫表中的欄位不匹配

以上操作的是office 2003,如果要操作office 2007則需採用如下方式

 

 

另外，還要對一些功能進行配置：

1、開啟SQL Server 2005介面區配置器，選擇“功能的介面區配置器”，選中“啟用OPENROWSET或OPENDATASOURCE支援”，點擊確定。

2、在C:\WINDOWS目錄下將temp檔案夾的安全選項卡中，在使用者或組名稱中，選擇“SQLServer2005ReportingServicesWebServiceUser$PC17$MSSQLSERVER(PC17/SQLServer2005ReportingServicesWebServiceUser$PC17$MSSQLSERVER”使用者，將此使用者的寫入，修改許可權選中。點擊確定。（設定它是因為將此將excel檔案讀入SQL資料庫時，是在C:\WINDOWS\temp下建立了一個臨時檔案，所以需要將此檔案夾的SQLServer2005使用權限設定為可寫入的。如果使用的是系統管理員帳戶，則需要不需此項設定。因為管理員有讀寫的許可權。）

 

 

匯出

使用insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=YES;DATABASE=C:\\Documents and Settings\\Administrator\\案頭\\export2.xls',[sheet1$]) select * from StuGrade可以將資料匯出至excel2003中，但前提必須是表已經存在，欄位名都已有且與表對應。而使用下面的自動建立檔案和表頭，又會發生異常，插不進去。目前看來只能一條一條插。

解決這個問題可以先建立一個excel檔案並添加表頭，可以使用下面的語句：

string filePath = "C:\\Documents and Settings\\Administrator\\案頭\\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;

            //開始寫入每列的標題

            foreach (DataColumn dc in ds.Tables[0].Columns)

            {

                colIndex++;

                xlsheet.Cells[RowIndex, colIndex] = dc.Caption;

            }

            xlbook.Saved = true;

            xlbook.SaveCopyAs(filePath);      //建立檔案

 

使用這個方法必須添加“using Microsoft.Office.Interop.Excel;” 引用

這樣使用上面那個SQL語句即可實現。

 

我在與office2007導的時候，將MICROSOFT.JET.OLEDB.4.0和Excel 5.0換成了MICROSOFT.ACE.OLEDB.12.0和Excel 12.0，將表名換成excel2003的表，這樣只能匯出一行，而且還會發生異常，這個問題還有待解決。

 

使用insert into opendatasource('microsoft.jet.oledb.4.0',

'Data source=D:\export.xls;Extended Properties=Excel 5.0')...[Sheet1$]

(欄位名) VALUES (對應值)也可以實現匯入。前提必須是表已經存在，欄位名都已有且與表對應。而且一次只能匯入一條，必須是office2003，換成office2007則可以匯入，但會發生異常。

 

 

 

通常匯入與匯出用一條SQL語句不太實用，因為用一條SQL語句限制太多，所以大多數情況下是一條記錄一條記錄寫入資料庫中，使用一條一條匯入資料庫的方法如下。其原理是將excel檔案當作資料表來用：

 

 

 

 

 

 

 

 

匯入

代碼為：

string strExcelFileName = @"D:\OutData.xls";  //excel檔案

string strSheetName = "sheet1";           //工作表名

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'";

//連接字串

string strExcel = "select * from [" + strSheetName + "$] ";//SQL語句

 

//定義存放的資料表

DataSet ds = new DataSet();              

 

//串連資料來源

OleDbConnection conn = new OleDbConnection(strConn);

 

conn.Open();

 

//適配到資料來源

OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);

adapter.Fill(ds, strSheetName + "$");

conn.Close();

 

//   一般的情況下.   Excel     表格的第一行是列名

dataGridView1.DataSource = ds.Tables["res"];  //將資料和dataGridView綁定

 

 

匯出

方法1：從DataSet向excel中匯出資料

string filePath = "C:\\Documents and Settings\\Administrator\\案頭\\export4.xls"；  //匯出的檔案名稱和路徑

string ReportName=”aaaaa”;      //匯出時給檔案加上檔案頭

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];

Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]);

range.MergeCells = true;

 

//定義儲存格中存放文本的樣式

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;

//開始寫入每列的標題

foreach (DataColumn dc in ds.Tables[0].Columns)

{

      colIndex++;

      xlsheet.Cells[RowIndex, colIndex] = dc.Caption;

}

 

//開始寫入內容

int RowCount = ds.Tables[0].Rows.Count;//行數

for (int i = 0; i < RowCount; i++)

{

     RowIndex++;

     int ColCount = ds.Tables[0].Columns.Count;//列數

     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();

 

 

 

方法2：從DataGridView中向excel匯出資料：

SqlConnection conn = new SqlConnection("Server=.;Database=student;Integrated Security=true");

conn.Open();

SqlCommand comm = new SqlCommand("select * from StuGrade where StuID='0000000'", conn);     //StuGrade是表名，StuID是欄位名

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;

//定義匯出的路徑

string Path = "C:\\Documents and Settings\\Administrator\\案頭";

 

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, null, null, null);

myExcel.Quit();

 

理論上來說，從DataSet中匯出和從DataGridView中匯出執行的速度應該是一樣的，但是，從我數次的實驗來看，從DataGridView中匯出資料比從DataSet中快的多，在資料量為300條記錄時，用DataGridView比DataSet快2倍左右，這我目前還不知道為什麼。這種方法，只適合少量的資料，如果資料量過大，則時間開銷會很大。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/qygaojiansheng/archive/2009/04/26/4126364.aspx