Excel导入导出

// 从 Excel 读取数据

static public DataTable ImputExcel( string TableName, string FileName)

       {

          String sConnectionString= "provider=Microsoft.Jet.OLEDB.4.0; " + "data source=" ;

          sConnectionString += FileName + ";" +

     "Extended Properties=Excel 8.0;" ;

           string sql= string .Empty;

         

 

           sql = "SELECT * FROM [" +TableName+"$]";

           OleDbConnection conn;

           try

           {

              conn = new OleDbConnection (sConnectionString);

           }

           catch ( Exception ex)

           {

               MessageBox .Show(ex.Message, " 连接字符串错误 " , MessageBoxButtons .OK, MessageBoxIcon.Error);

               return null ;

           }

           OleDbCommand cmd = new OleDbCommand (sql,conn);

           OleDbDataAdapter dataAdapter = new OleDbDataAdapter ();

           dataAdapter.SelectCommand = cmd;

           DataTable dataTable= new DataTable ();

           try

           {

               dataAdapter.Fill(dataTable);

           }

           catch ( Exception ex)

           {

               MessageBox .Show(ex.Message, " 读取 Excel 错误 " , MessageBoxButtons .OK, MessageBoxIcon.Error);

               return null ;

           }

          

           return dataTable;

       }

// 把数据导出到 Excel 中
static public void OutputExcel( DataTable OutputDataTable, string saveFileName)

       {

           bool fileSaved = false ;

           string mySql = string .Empty;

           string helpSql = string .Empty;

           string FileName = OutputDataTable.TableName;

           Excel. Application xlApp = new Excel. Application ();

          

           if (xlApp == null )

           {

               MessageBox .Show(" 无法创建 Excel 对象，可能您的机子未安装 Excel" );

               return ;

           }

 

           Excel. Workbooks workbooks = xlApp.Workbooks;

           Excel. Workbook workbook = workbooks.Add(Excel. XlWBATemplate .xlWBATWorksheet);

           Excel. Worksheet worksheet = (Excel. Worksheet )workbook.Worksheets[1]; // 取得 sheet1

           workbooks.OpenXML(saveFileName, Excel. XlSheetType .xlWorksheet, LoadOption.Upsert);

           worksheet.Name = FileName;

           // 写入字段

           for ( int i = 0; i < OutputDataTable.Columns.Count; i++)

           {

               string columnName = OutputDataTable.Columns[i].ColumnName;

               worksheet.Cells[1, i + 1] = columnName;

 

               mySql += columnName + "," ;

               helpSql += "?," ;

           }

           mySql = mySql.Substring(0, mySql.Length - 1);

           helpSql = helpSql.Substring(0, helpSql.Length - 1);

 

           string sqlInsert = "INSERT INTO [" + FileName + "$]" + "(" + mySql + ")" + " values " + "(" +helpSql+ ")" ;

           //INSERT INTO [Sheet1$] (F1, F2) values (?, ?)

           // 保存 Excel

            try

            {

                workbook.Saved = true ;

                workbook.SaveCopyAs(saveFileName);

                fileSaved = true ;

            }

            catch ( Exception ex)

            {

                fileSaved = false ;

                MessageBox .Show(" 导出文件时出错 , 文件可能正被打开！ /n" + ex.Message);

            }

            xlApp.Quit(); // 关闭 EXCEL

            GC .Collect();

            // 读取 Excel 架构

            if (fileSaved == true ) 

            {

                String sConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; " + "data source=" ;

                sConnectionString += saveFileName + ";" +

           "Extended Properties=Excel 8.0;" ;

                string sql = string .Empty;

                sql = "SELECT * FROM [" + FileName + "$]" ;

                OleDbConnection conn;

                try

                {

                    conn = new OleDbConnection (sConnectionString);

                }

                catch ( Exception ex)

                {

                    MessageBox .Show(ex.Message, " 连接字符串错误 " , MessageBoxButtons .OK, MessageBoxIcon.Error);

                    return ;

                }

                OleDbCommand cmd = new OleDbCommand (sql, conn);

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter ();

                dataAdapter.SelectCommand = cmd;

                OleDbCommand cmd2 = new OleDbCommand (sqlInsert, conn);

                dataAdapter.InsertCommand = cmd2;

                DataTable dataTable = new DataTable ();

                try

                {

                    dataAdapter.Fill(dataTable);

                }

                catch ( Exception ex)

                {

                    MessageBox .Show(ex.Message, " 读取 Excel 错误 " , MessageBoxButtons .OK, MessageBoxIcon.Error);

                    return ;

                }

                conn.Open();

                foreach ( DataRow dr in OutputDataTable.Rows)

                {

                    foreach ( DataColumn dc in dataTable.Columns)

                    {

                        cmd2.Parameters.AddWithValue(dc.ColumnName, dr[dc.ColumnName]);

                    }

                    //cmd2.ExecuteNonQuery();

                }

               

                conn.Close();

                

            }

       }