读取excel到datatable 读去txt文件datatable 写入txt

1、读取excel到datatable

提示：Microsoft.ACE.OLEDB.12.0  这个东东还有一个office2003 对应的  我这个程序是系统装了office 2007 2010应该也没有问题

还有用vs开发的时候 目标平台 要改为 x86

 /// <param name="filePath">文件路径</param>
        /// <param name="sheetName">要去文件中那个sheet的数据</param>
        /// <returns></returns>
        public static DataTable LoadDataFromExcel(string ExcelPath, string sheetName)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [" + sheetName + "$]";//可是更改Sheet名称，比如sheet2，等等   

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, sheetName);
                OleConn.Close();
                return OleDsExcle.Tables[sheetName];
            }
            catch (Exception err)
            {
                //MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                //     MessageBoxButtons.OK, MessageBoxIcon.Information);
                //Application.Exit();
                return null;
            }
        }

2、读去txt文件datatable 

提示：读取出的数据如果有些数据是乱码 说明是编码方式不匹配

 /// <param name="list">存储txt文件的数据LIst</param>
        /// <param name="Leng">该文件中字段的个数</param>
        /// <returns></returns>
        public static DataTable GetDatatableFromTxt(string filePath, int Leng)
        {
            try
            {
                List<string[]> list = ReadInfoFromFile(filePath);

                if (list != null)
                {
                    DataTable dt = new DataTable();
                    for (int i = 0; i < Leng; i++)
                    {
                        dt.Columns.Add("Col" + i);
                    }

                    foreach (string[] strArray in list)
                    {
                        ***************自己的处理代码，我想返回一个datatable，这样就可以把这个方法提炼出来*******
                        if (strArray.Length != Leng)
                        {
                            continue;
                        }
                        //创建一个新的基于创建dt的datarow
                        DataRow dr = dt.NewRow();
                        if (!string.IsNullOrEmpty(strArray[0]))
                        {
                            for (int i = 0; i < Leng; i++)
                            {
                                dr[i] = strArray[i];
                            }
                            dt.Rows.Add(dr);
                        }

                    }
                    dt.Rows.RemoveAt(0);
                    return dt;
                }
                else
                {
                    //MessageBox.Show("获取数据表失败!失败原因：找不到文件" + filePath, "提示信息",
                    //                   MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //Application.Exit();
                    return null;
                }

            }
            catch (Exception err)
            {
                //MessageBox.Show("获取数据表失败!失败原因：" + err.Message, "提示信息",
                //                         MessageBoxButtons.OK, MessageBoxIcon.Information);
                //Application.Exit();
                return null;
            }
        }

 

 

       ///<summary>       
        /// 这个函数把文件的每一行读入list       
        /// </summary>       
        /// <param name="filePath"></param>       
        /// <returns></returns>       
        public static List<string[]> ReadInfoFromFile(string filePath)
        {
            if (File.Exists(filePath))
            {
                List<string[]> list = new List<string[]>();
                // 打开文件时 一定要注意编码 也许你的那个文件并不是GBK编码的               
                using (StreamReader sr = new StreamReader(filePath, System.Text.Encoding.UTF8))
                {
                    while (!sr.EndOfStream) //读到结尾退出                   
                    {
                        string temp = sr.ReadLine();

                        //将每一行拆分，分隔符就是char 数组中的字符                       
                        string[] strArray = temp.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);

                        //将拆分好的string[] 存入list

                        list.Add(strArray);
                    }
                }
                return list;
            }
            return null;
        }

3、写入txt

  /// <summary>       
        /// 这个函数把list中的每一行写入文件      
        /// /// </summary>       
        /// <param name="filePath"></param>       
        /// <param name="list"></param>       
        public static void WriteInfoTofile(string filePath, List<string[]> list)
        {
            // 打开文件时 一定要注意编码 也许你的那个文件并不是GBK编码的          
            using (StreamWriter sw = new StreamWriter(filePath, false, Encoding.Default))
            {
                //一个string[] 是一行  ，一行中以tab键分隔 字段              
                foreach (string[] strArray in list)
                {
                    string line = string.Empty;
                    foreach (string temp in strArray)
                    {
                        if (!string.IsNullOrEmpty(temp))
                        {
                            line += temp;
                            line += "\t";
                        }
                    }
                    sw.WriteLine(line);
                }
            }
        }