C# VS2010 EXCEL转TXT

一、步骤

1、定位到所需工作表。（修改sheetTableName的内容，就可定位不同工作表）

这部分参考牟乃夏老师的《ArcGIS Engine 地理信息系统开发教程》中的7.5 添加X、Y数据部分的代码。

sheetTableName == "Sheet1$"

 2、定位工作表中的每一个单元格。

                    int a = dt.Rows.Count;
                    foreach (DataRow row1 in dt.Rows)
                    {
                        //存储用字符串
                        string ii = "";
                        for (int i = 0; i < row1.ItemArray.Length; i++)
                        {
                            // row1[i] 即为每个单元格的内容
                        }
                    }

3、将每个单元格中的内容拼接为行字符串。（这里设置的每个单元格用逗号隔开）

ii += row1[i] + "" + ",";//行字符串拼接

4、将拼接后的行字符串存储到List<String>

saveE.Add(ii.Substring(0, ii.Length - 1));//Substring(0, ii.Length - 1))去除最后一个逗号

5、将List转为TXT导出。

二、数据测试

1、Excel数据

 2、转换为TXT后数据

 三、完整代码

/// <summary>
        /// Excel格式转TxT格式
        /// </summary>
        /// <param name="ExcelPath">Excel路径</param>
        /// <param name="outPath">txt格式输出路径</param>
        public void ExcelToTxT(string ExcelPath, string outPath)
        {
            OleDbConnection m_conDBConnection;
            List<String> saveE = new List<string>();
            string strDBName = ExcelPath;
            StringBuilder strConnect = new StringBuilder();
            string extension = System.IO.Path.GetExtension(strDBName);
            switch (extension)
            {
                case ".xls":
                    //当为Excel03格式时
                    strConnect.Append(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", strDBName));
                    strConnect.Append("Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
                    break;
                case ".xlsx":
                    //当为Excel07格式时
                    strConnect.Append(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", strDBName));
                    strConnect.Append("Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'");
                    break;
                default:
                    break;
            }
            if (strConnect.ToString() == string.Empty)
            {
                MessageBox.Show("打开Excel格式不支持！");
            }
            //cmbExcelSheets.Items.Clear();
            m_conDBConnection = new OleDbConnection();
            m_conDBConnection.ConnectionString = strConnect.ToString();
            m_conDBConnection.Open();
            //获取Excel中sheet列表
            DataTable dtTable = m_conDBConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new System.Object[] { null, null, null, "TABLE" });

            if (dtTable == null)
            {

                MessageBox.Show("未能找到有效的Sheet表", "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            这里获取到excel中的每一个表单名     
            foreach (System.Data.DataRow row in dtTable.Rows)
            {
                string strTableName = row["TABLE_NAME"].ToString();
                string sheetTableName = strTableName;
                if (sheetTableName == "Sheet1$")
                {
                    DataTable dt = new DataTable();
                    //获取全部数据

                    //构造数据库操作变量，利用sql查询返回DataTable形式
                    OleDbCommand m_cmdCommand = new OleDbCommand();
                    m_cmdCommand.Connection = m_conDBConnection;
                    m_cmdCommand.CommandText = String.Format("select * from [{0}]", sheetTableName);
                    m_cmdCommand.CommandType = CommandType.Text;
                    //进行数据查询
                    using (OleDbDataAdapter m_dtrAdapter = new OleDbDataAdapter(m_cmdCommand))
                    {
                        DataSet objDs = new DataSet();
                        m_dtrAdapter.Fill(objDs);
                        dt = objDs.Tables[0];
                    }

                    //dt = QueryBySql(String.Format("select * from [{0}]", sheetTableName));
                    int a = dt.Rows.Count;
                    foreach (DataRow row1 in dt.Rows)
                    {
                        //存储用字符串
                        string ii = "";
                        for (int i = 0; i < row1.ItemArray.Length; i++)
                        {
                            ii += row1[i] + "" + ",";//行字符串拼接
                        }
                        saveE.Add(ii.Substring(0, ii.Length - 1));
                    }
                }
                ListToTxt(saveE, outPath);//list转TXT，ListToTxt请链接查看
            }
        }

ListToTxt的链接如下：C# VS2010 将List转换为TXT文件_HPUJQT的博客-CSDN博客