Excel文件读写操作(一)

        /// <summary>
        /// 使用OLEDB对excel文件进行读取，将Excel文件作为数据源
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private DataSet ExcelToDataSet(string fileName)
        {
            //定义连接字符串
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" + "data source=" + fileName;

            //定义连接对象
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                //打开连接
                conn.Open();
                string strExcel = "";
                //数据适配器
                OleDbDataAdapter myCommand = null;
                //数据集
                DataSet ds = null;

                //通过索引来获取工作表的集合
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //获取工作表名
                string tableName = schemaTable.Rows[0][2].ToString().Trim();
                //查询语句
                strExcel = "select * from [" + tableName + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                //填充数据
                myCommand.Fill(ds, "table1");
                conn.Close();
                return ds;
            }
            catch
            {
                MessageBox.Show("你选择的文件不是标准的Excel文件");
                return null;
            }
        }

        /// <summary>
        /// 将datagridview的数据以文件流的形式写入Excel文件
        /// </summary>
        /// <param name="dgv"></param>
        public void DataGridViewToExcel(DataGridView dgv)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.CheckFileExists = false;
            dlg.CheckPathExists = false;
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = false;
            dlg.Title = "保存为Excel文件";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = "";
                try
                {
                    //写入列标题  
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            columnTitle += "\t";
                        }
                        columnTitle += dgv.Columns[i].HeaderText;
                    }
                    sw.WriteLine(columnTitle);

                    //写入列内容  
                    for (int j = 0; j < dgv.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int k = 0; k < dgv.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                columnValue += "\t";
                            }
                            if (dgv.Rows[j].Cells[k].Value == null)
                                columnValue += "";
                            else
                                columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                    MessageBox.Show("数据导出成功!", "提示", MessageBoxButtons.OK);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }