C#实现EXCEL数据到TXT文档的转换

 using System; 
 using System.Collections.Generic; 
 using System.ComponentModel; 
 using System.Data; 
 using System.Data.OleDb; 
 using System.Drawing; 
 using System.Text; 
 using System.Windows.Forms; 
 using System.IO; 

  
 namespace ExcelToTxt 
 { 
     public partial class Form1 : Form 
     { 
         private DataTable dt; //存储EXCLE中的数据 

         public Form1() 
         { 
             InitializeComponent(); 
             this.btnChange.Enabled = false;//初始化设置控件为不可用 
         } 

  
         /// <summary> 
         /// 该方法打开一个Excel文件 
         /// </summary> 
         /// <param name="sender"></param> 
         /// <param name="e"></param> 
         private void btnSelect_Click(object sender, EventArgs e) 
         { 
             string excelFilePath = ""; //存储打开的文件的路径 

             OpenFileDialog selectFile = new OpenFileDialog(); 

             //选择打开的文件设置 
             selectFile.Filter = "Excel(*.xls)|*.xls"; 
             selectFile.FilterIndex = 1; 
             selectFile.DefaultExt = "xls"; 
             selectFile.AddExtension = true; 
             selectFile.RestoreDirectory = true; 
             selectFile.Multiselect = false; 

             //选择文件 
             if (selectFile.ShowDialog() == DialogResult.OK) 
             { 
                 excelFilePath = selectFile.FileName;//获取选择的文件路径 
             } 
             else 
             { 
                 return; 
             } 

             //得到控件的数据源 
             dt = GetExcelData(excelFilePath); 

             //在显示控件中显示数据 
             ShowDataGridView(); 

             //设置转换格式的控件可用 
             this.btnChange.Enabled = true; 
         } 

  
         /// <summary> 
         ///该方法将选择的EXCEL文件转换成TXT文档  
         /// </summary> 
         /// <param name="sender"></param> 
         /// <param name="e"></param> 
         private void btnChange_Click(object sender, EventArgs e) 
         { 
             string txtFilePath = "";//存储选择的TXT文档的文件名 
             SaveFileDialog saveTxtFile = new SaveFileDialog(); 

             //选择保存的文件设置 
             saveTxtFile.Filter = "Text(.txt)|*.txt"; 
             saveTxtFile.FilterIndex = 1; 
             saveTxtFile.DefaultExt = "txt"; 
             saveTxtFile.AddExtension = true; 
             saveTxtFile.RestoreDirectory = true; 
             saveTxtFile.OverwritePrompt = true; 

             //选择创建文件的文件夹 
             if (saveTxtFile.ShowDialog() == DialogResult.OK) 
             { 
                 txtFilePath = saveTxtFile.FileName; //获取选择的文件路径 
             } 
             else 
             { 
                 return; 
             } 

             //将DataTable中的文件写入到txt文档中 
             Cursor.Current = Cursors.WaitCursor; //设置鼠标状态 
             int dtcols = dt.Columns.Count; 
             StringBuilder sbtxtdata = new StringBuilder(); ;  //临时存储从dt中读出的每一条数据 

  
             //先创建一个新的TXT文档 
             FileStream fsTxtFile = new FileStream(txtFilePath, FileMode.CreateNew, FileAccess.Write); 
             StreamWriter swTxtFile = new StreamWriter(fsTxtFile, Encoding.GetEncoding("gb2312") ); 

             if (dtcols > 3) 
             { 
                 string[] tempstr = new string[11]; 

                 //设置固定的值 
                 tempstr[0] = "\"" + "检测设备资产标签" + "\"" + ","; 
                 tempstr[1] = "\"" + "设备名称" + "\"" + ","; 
                 tempstr[3] = "\"" + "规格型号" + "\"" + ","; 
                 tempstr[5] = "\"" + "设备编号" + "\"" + ","; 
                 tempstr[7] = "\"" + "使用部门" + "\"" + ","; 
                 tempstr[9] = "\"" + "固定资产编号" + "\"" + ",";  

                 //标签2的格式写入Txt文档 
                 for(int rows = 0; rows < dt.Rows.Count; rows++) 
                 { 
                     for (int cols = 0; cols < dt.Columns.Count; cols++) 
                     { 
                         int tempindex = 2*(cols+1); 
                         tempstr[tempindex] = "\"" + dt.Rows[rows][cols].ToString() + "\""; 
                     } 

                     tempstr[2] = tempstr[2] + ","; 
                     tempstr[4] = tempstr[4] + ","; 
                     tempstr[6] = tempstr[6] + ","; 
                     tempstr[8] = tempstr[8] + ","; 
                     tempstr[10] = tempstr[10] + "\r\n"; 

                     //将本行数据写入缓冲区 
                     foreach (string str in tempstr) 
                     { 
                         sbtxtdata.Append(str); 
                     } 
                     swTxtFile.Write(sbtxtdata); 

                     //清空本行中的数据 
                     sbtxtdata.Remove(0, sbtxtdata.Length); 

                     //将数组中新添加的数据清空 
                     for (int i = 0; i < dt.Columns.Count; i++) 
                     { 
                         int tempindex = 2*(i+1); 
                         tempstr[tempindex] = ""; 
                     } 
                 } 
             } 
             else 
             { 
                 string[] tempstr = new string[5]; 
                 //标签0或1的格式写入Txt文档 
                 for (int rows = 0; rows < dt.Rows.Count; rows++) 
                 { 
                     for (int cols = 0; cols < dt.Columns.Count; cols++) 
                     { 
                         string temp = "";//临时存储当前时间 

                         if (cols == 0) 
                         { 
                             tempstr[0] = "\"" + dt.Rows[rows][cols] + "\"" + ","; 
                         } 
                         else if (cols == 1) 
                         { 
                             temp = dt.Rows[rows][cols].ToString(); 
                             tempstr[1] = "\"" + temp.Substring(0, 4) + "\"" + ","; //截取年 
                             tempstr[2] = "\"" + temp.Substring(4, 2) + "\"" + ","; //截取月 
                             tempstr[3] = "\"" + temp.Substring(6, 2) + "\"" + ","; //截取日 
                         } 
                         else if (cols == 2) 
                         { 
                             tempstr[4] = "\"" + dt.Rows[rows][cols] + "\"" + "\r\n"; 
                         } 
                     } 

                     //将本行数据写入缓冲区 
                     foreach (string str in tempstr) 
                     { 
                         sbtxtdata.Append(str); 
                     } 
                     swTxtFile.Write(sbtxtdata); 

                     //清空本行中的数据 
                     sbtxtdata.Remove(0, sbtxtdata.Length); 

                     //将数组中新添加的数据清空 
                     for (int i = 0; i < dt.Columns.Count; i++) 
                     { 
                         tempstr[i] = ""; 
                     } 
                 } 
             } 

             //将数据写入文档 
             swTxtFile.Write("end"); 
             swTxtFile.Flush(); 
             swTxtFile.Close(); 
             fsTxtFile.Close(); 

             //重新设置鼠标格式 
             Cursor.Current = Cursors.Default; 
             MessageBox.Show("文件转换成功!", "提示", 
                     MessageBoxButtons.OK,  MessageBoxIcon.Information); 
         } 

  
         /// <summary> 
         /// 获取Excel文件中的数据 
         /// </summary> 
         /// <param name="path">Excel文件的路径</param> 
         /// <returns>DataTable:将Excel文件的数据加载到DataTable中</returns> 
         private DataTable GetExcelData(string path) 
         { 
             //连接字符串确定 
             string excelstr = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + path + " ;"  
                         + " Extended Properties = Excel 8.0;"; 

             OleDbConnection excelConn = new OleDbConnection(excelstr); 

             //打开数据源连接 
             try 
             { 
                 if (excelConn.State == ConnectionState.Closed) 
                 { 
                     excelConn.Open(); 
                 } 
             } 
             catch (Exception ex) 
             { 
                 MessageBox.Show("打开数据源连接失败！", "错误",  
                         MessageBoxButtons.OK, MessageBoxIcon.Error); 
                 Application.Exit(); 
             } 
             finally 
             { 
                 if(excelConn.State == ConnectionState.Open) 
                 excelConn.Close(); 
             } 

             //设置查询命令 
             OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn); 
             DataSet ds = new DataSet(); 

             //执行该查询EXCEL表的命令 
             try 
             { 
                 myCommand.Fill(ds, "excelTable"); 
             } 
             catch (Exception ex) 
             { 
                 MessageBox.Show("该Excel文件的工作表的名字不是[Sheet1$]!", "错误",  
                                       MessageBoxButtons.OK, MessageBoxIcon.Error); 
                 Application.Exit(); 
             } 
             finally 
             { 
                 if (excelConn.State == ConnectionState.Closed) 
                 { 
                     excelConn.Close(); 
                 } 
             } 

             //判断DataTable中是否有数据 
             if (ds.Tables["excelTable"].Rows.Count > 0) 
             { 
                 return ds.Tables["excelTable"]; 
             } 
             else 
             { 
                 MessageBox.Show("没有读到Excel表中的数据!", "错误",  
                                         MessageBoxButtons.OK, MessageBoxIcon.Error); 
                 return null; 
             } 
         } 

  
         /// <summary> 
         /// 将选择的excel表中的数据现在DataGridView中 
         /// </summary> 
         private void ShowDataGridView() 
         { 
             //设置显示控件的样式 
             this.dgvShow.DefaultCellStyle.BackColor = Color.Beige; 
             this.dgvShow.DefaultCellStyle.Font = new Font("Tahoma", 12); 

             DataGridViewCellStyle highlightCellStyle = new DataGridViewCellStyle(); 
             highlightCellStyle.BackColor = Color.Red; 

             DataGridViewCellStyle currencyCellStyle = new DataGridViewCellStyle(); 
             currencyCellStyle.Format = "C"; 
             currencyCellStyle.ForeColor = Color.Green; 

             //设置显示控件的数据源 
             dgvShow.DataSource = dt; 
         } 

     } 
 }