Excel导入数据到数据库(Sql2005 ,Access)

1.在Sql2005创建对应的表"Roll"

2.应用以下这段代码

string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
            
using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
            
{
                OleDbCommand cmd 
= new OleDbCommand("select * FROM [Sheet1$]", conn);
                conn.Open();
                
using (DbDataReader dr = cmd.ExecuteReader())
                
{
                    
string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
                    
// Bulk Copy to SQL Server 
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    
{
                        bulkCopy.DestinationTableName 
= "Roll";
                        bulkCopy.WriteToServer(dr);
                    }

                }

            }

3.Excel导入、导出数据到access,使用Com组件
    public static void ExcelImportDB()
        
{
            OleDbConnection conExcel 
= new OleDbConnection();
            
try
            
{
                ApplicationClass access 
= new ApplicationClass();

                access.Visible 
= false;
                access.OpenCurrentDatabase(Settings.Default.DBPath, 
true"");

                OpenFileDialog openFile 
= new OpenFileDialog();
                openFile.Filter 
= ("Excel 文件(*.xls)|*.xls");

                
if (openFile.ShowDialog() == DialogResult.OK)
                
{
                    access.DoCmd.TransferSpreadsheet(AcDataTransferType.acImport, AcSpreadSheetType.acSpreadsheetTypeExcel12, 
"Intergral", openFile.FileName, truenullnull);

                    access.CloseCurrentDatabase();
                    access.DoCmd.Quit(AcQuitOption.acQuitSaveAll);

                    Marshal.ReleaseComObject(access);

                    access 
= null;

                    System.Windows.Forms.MessageBox.Show(
"导入数据成功""导入数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }

            
catch (Exception ex)
            
{
                System.Windows.Forms.MessageBox.Show(ex.ToString());
            }

            
finally
            
{
                conExcel.Close();
            }

        }


        
public static void DBExportExcel()
        
{
            
try
            
{
                ApplicationClass access 
= new ApplicationClass();

                access.Visible 
= false;
                access.OpenCurrentDatabase(Settings.Default.DBPath, 
false"");

                SaveFileDialog saveFile 
= new SaveFileDialog();
                saveFile.Filter 
= ("Excel 文件(*.xls)|*.xls");
                
if (saveFile.ShowDialog() == DialogResult.OK)
                
{
                    access.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel9, 
"Intergral", saveFile.FileName, truenullnull);

                    access.CloseCurrentDatabase();
                    access.DoCmd.Quit(AcQuitOption.acQuitSaveNone);

                    Marshal.ReleaseComObject(access);

                    access 
= null;

                    MessageBox.Show(
"导出数据成功""导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }

            
catch (Exception ex)
            
{
                MessageBox.Show(ex.ToString());
            }

        }
posted @ 2008-06-05 14:10  RicoRui  阅读(2114)  评论(1编辑  收藏  举报