首页 新闻 赞助 找找看

winform访问Excel表里的数据

0
悬赏园豆:10 [已解决问题] 解决于 2013-04-26 13:52

用Winforms怎么访问EXCEL表里面的数据然后放到另外一个Excel表的格式里面;反之又怎样弄回来?求详细代码啊

333
听雨者的主页 听雨者 | 初学一级 | 园豆:44
提问于:2013-04-26 11:39
< >
分享
最佳答案
0

你这个问题其实分两部分,读Excel和写Excel,反之的意思是写出的Excel类型必须能读出来。这就决定了读和写都是标准的Excel格式。读写Excel都可以用OleDb,而且速度不慢。现成代码只有读成DataTable的,写Excel的话你可以拼接一个很长的Insert语句,然后调用OleDb。这样比用COM快。前面的前提条件是标准Excel类型,但如果你不要求标准Excel类型的话,以下写Excel代码会非常快:

 public void WriteExcel(System.Data.DataTable dt, string path)
        {
            try
            {
                long totalCount = dt.Rows.Count;
                label3.Text = "共有" + totalCount + "条数据。";
                Thread.Sleep(1000);
                //long rowRead = 0;
                //float percent = 0;
                Stopwatch sw1 = new Stopwatch();
                sw1.Start();
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    sb.Append(dt.Columns[k].ColumnName.ToString() + "\t");
                }
                sb.Append(Environment.NewLine);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //rowRead++;
                    //percent = ((float)(100 * rowRead)) / totalCount;
                    //Pbar.Maximum = (int)totalCount;
                    //Pbar.Value = (int)rowRead;
                    //label2.Text = "正在写入[" + percent.ToString("0.00") + "%]...的数据";
                    //System.Windows.Forms.Application.DoEvents();

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        sb.Append(dt.Rows[i][j].ToString() + "\t");
                    }
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                sw1.Stop();
                label2.Text = "运行时间:" + sw1.Elapsed;
                MessageBox.Show("已经生成指定Excel文件!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

接下来下是把Excel读成DataTable的示例:

    /// <summary>
    /// Excel导入辅助类
    /// </summary>
    public class ExcelImportHelper
    {
        #region 字段及属性

        private string fileName;
        /// <summary>
        /// 文件名
        /// </summary>
        public string FileName
        {
            get { return fileName; }
            private set { fileName = value; }
        }

        private string connectionString;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
            private set { connectionString = value; }
        }

        private OleDbConnection conn;
        /// <summary>
        /// OleDb连接
        /// </summary>
        public OleDbConnection Conn
        {
            get { return conn; }
            private set { conn = value; }
        }

        private List<string> sheetsName;
        /// <summary>
        /// Excel文件中所有Sheets名
        /// </summary>
        public List<string> SheetsName
        {
            get { return sheetsName; }
            private set { sheetsName = value; }
        }

        private DataTable sheetData;
        /// <summary>
        /// Sheet中的数据
        /// </summary>
        public DataTable SheetData
        {
            get { return sheetData; }
            private set { sheetData = value; }
        }
       
        #endregion

        #region 公有方法

        /// <summary>
        /// 打开一个Excel
        /// 这个方法必须第一个调用
        /// </summary>
        /// <returns>true:成功,false:失败</returns>
        public bool OpenExcel()
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                this.FileName = ofd.FileName;
                SetConnectionString();
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取Excel文件所有Sheet名
        /// </summary>
        public void GetSheetsName()
        {
            SheetsName = new List<string>();
            try
            {
                OpenConnection();
                DataTable sheetsNameTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow row in sheetsNameTable.Rows)
                {
                    SheetsName.Add(row["TABLE_NAME"].ToString());
                }
            }
            catch
            {
                ShowUnfindFile();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 用表名查询数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>DataTable</returns>
        public void QueryByTableName(string tableName)
        {
            SheetData = new DataTable();
            string queryStr = "select * from [" + tableName + "]";
            try
            {
                OpenConnection();
                OleDbDataAdapter command = new OleDbDataAdapter(queryStr, Conn);
                command.Fill(SheetData);               
            }
            catch
            {
                ShowUnfindFile();
            }
            finally
            {
                CloseConnection();               
            }
        }

        #endregion

        #region 私有方法
        /// <summary>
        /// 提示无法找到文件
        /// </summary>
        private void ShowUnfindFile()
        {
            MsgTool.ShowMessage(string.Format("无法找到{0}文件", FileName));
        }

        /// <summary>
        /// 打开OleDb连接
        /// </summary>
        private void OpenConnection()
        {
            Conn = new OleDbConnection(ConnectionString);
            Conn.Open();
        }

        /// <summary>
        /// 关闭OleDb连接
        /// </summary>
        private void CloseConnection()
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }

        /// <summary>
        /// 获得连接字符串
        /// </summary>
        private void SetConnectionString()
        {
            FileInfo file = new FileInfo(FileName);
            if (file.Exists)
            {
                switch (file.Extension)
                {
                    case ".xlsx":
                        ConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1';";
                        break;
                    case ".xls":
                        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
                        break;
                    default:
                        ConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1';";
                        break;
                }
            }
            else
            {
                throw new ApplicationException("选中的文件不存在!");
            }
        }
        #endregion
    }

收获园豆:10
呆呆蚁 | 菜鸟二级 |园豆:231 | 2013-04-26 13:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册