c# 操作excel

最近写了个操作excel的东东，在网上找了n久的资料。

现在把它整理一下，收藏....

 

1：查询excel内容

这个很容易，利用.net的oledb就可以像访问sqlserver一样访问excel中的表

连接字符串:"Provider=Microsoft.Jet.OleDb.4.0;data source=" + FilePath + ";Extended Properties=Excel 8.0;"

excel版本视自己版本填写

 

2：导出excel内容

以下是我试过最快的方法，缺点就是不能设置表的名字，只能使用默认值。

        /// <summary>
        /// 将DataTable内容导出为Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool ExportExcel(System.Data.DataTable dt)
        {
            bool isSuccess = false;
            try
            {
                string FileName = FilePath;
                FileStream objFileStream;
                StreamWriter objStreamWriter;
                StringBuilder strLine = new StringBuilder();
                objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
                objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.GetEncoding("gb2312"));

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strLine.Append(dt.Columns[i].ColumnName.ToString() + Convert.ToChar(9));
                }
                objStreamWriter.WriteLine(strLine.ToString());
                strLine.Length = 0;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        strLine.Append(dt.Rows[i][j].ToString() + Convert.ToChar(9));
                    }
                    objStreamWriter.WriteLine(strLine.ToString());
                    strLine.Length = 0;
                }
                objStreamWriter.Close();
                objFileStream.Close();
                isSuccess = true;
            }
            catch (Exception ex)
            {
                Log.WriteErr("Export", "ExportExcel", ex.Message);
            }

            return isSuccess;
        }