Excel导入导出数据库_.NET_编程开发_程序员俱乐部

　　有时候我们会遇到这样的需求，需要向数据库导入Excel，或者从数据库中导出数据存为Excel，尤其是在做报表的时候，或者需要从其他系统导入数据，一般都是以Excel文件形式存在的。正好这几天在做这样的事，我整理一下这两种操作的大概做法。以后遇到这样的问题就不用怕了！！！

1.数据从数据库中导出存为Excel：

 private void btnInput_Click(object sender, EventArgs e)
        {
            DataGridViewToExcel("采集信息");
        }
 
        private void DataGridViewToExcel( string filename)
        {
            using (OperationClient proxy = new OperationClient())//引用了服务代理
            {
                try
                {
                    SaveFileDialog dlg = new SaveFileDialog();
                    dlg.Filter = "Execl files (*.xls)|*.xls";
                    dlg.FilterIndex = 0;
                    dlg.RestoreDirectory = true;
                    dlg.Title = "保存为Excel文件";
                    dlg.FileName = filename;
                    if (dlg.ShowDialog() == DialogResult.OK)
                    {
                        Stream myStream;
                        myStream = dlg.OpenFile();
                        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                        string colHeaders = "", ls_item = "";
                        //写入列标题    
                        colHeaders += "身份证号" + "\t";
                        colHeaders += "是否采集";
                        sw.WriteLine(colHeaders);
                        //写入列内容
                        //这里的proxy.Output()函数是调用定义好的WCF服务中的，它返回值是一个集合，而每个元素都是一条记录（多个字段）
                        foreach (var s in proxy.Output())
                        {
                            ls_item += s.ID + "\t";
                            ls_item += s.statu;
                            sw.WriteLine(ls_item);
                            ls_item = "";
                        }
                        sw.Close();
                        myStream.Close();
                        MessageBox.Show("导出[" + filename + "]成功", "提示");
 
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
        }

2.Excel的数据导入到数据库中

private void btnOutput_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "表格文件 (*.xls)|*.xls";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                InExcelData(openFileDialog.FileName);
            }
            MessageBox.Show("导入[" + openFileDialog.FileName + "]成功", "提示");
        }
         private bool InExcelData(string filePath)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
 
                OleDbConnection con = new OleDbConnection(strConn);
                con.Open();
                string[] names = GetExcelSheetNames(con);//GetExcelSheetNames(filePath);
                if (names != null)
                {
                    if (names.Length > 0)
                    {
                        foreach (string name in names)
                        {
                            OleDbCommand cmd = con.CreateCommand();
                            cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName$]要如此格式
                            OleDbDataReader odr = cmd.ExecuteReader();
 
                            while (odr.Read())
                            {
 
                                 if (odr[0].ToString() != "")
                                {
                                    if (odr[0].ToString() == "身份证号")//过滤列头 按你的实际Excel文件
                                        continue;
　　　　　　　　　　　　　　　　　　        //以下内容视自己的需要改动，要知道这里odr就是一个集合了，这里用循环是把集合里面每个元素读出来，一个元素就是一条记录（多个字段），

                                      //一般我们都会把一条记录定义成一个数据契约，方便传输    
                                      using (OperationClient proxy = new OperationClient())
                                    {
                                        Excel excel=new Excel(){ID = odr[0].ToString(),statu = odr[1].ToString()};
                                        proxy.InExcels(excel); //服务那边定义成一个数据契约类型的集合，例如InExcels（Excel excel）                                    }
                                }
                            }
                            odr.Close();
                            
                        }
                    }
                }
                con.Close();
                return true;
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        public static string[] GetExcelSheetNames(OleDbConnection con)
        {
            try
            {
                DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
                var sheet = new string[dt.Rows.Count];
                for (int i = 0, j = dt.Rows.Count; i < j; i++)
                {
                    sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                }
                return sheet;
            }
            catch
            {
                return null;
            }
        }