C#中如何實(shí)現(xiàn)EXCEL轉(zhuǎn)換成TXT文檔

這篇文章主要為大家展示了“C#中如何實(shí)現(xiàn)EXCEL轉(zhuǎn)換成TXT文檔”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“C#中如何實(shí)現(xiàn)EXCEL轉(zhuǎn)換成TXT文檔”這篇文章吧。

C#數(shù)據(jù)轉(zhuǎn)換前excel中的數(shù)據(jù)格式如下:

設(shè)備名稱 規(guī)格型號(hào) 設(shè)備編號(hào)  使用部門 固定資產(chǎn)編號(hào)

電腦1 IBM5660 10001 管理部 100010001

電腦2 IBM5661 10002 研發(fā)部 100010002

電腦3 IBM5662 10003 管理部 100010003

C#數(shù)據(jù)轉(zhuǎn)換到TXT文檔的格式:

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦1","規(guī)格型號(hào)","IBM5660","設(shè)備編號(hào)","10001","使用部門","管理部","固定資產(chǎn)編號(hào)","100010001"

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦2","規(guī)格型號(hào)","IBM5661","設(shè)備編號(hào)","10002","使用部門","研發(fā)部","固定資產(chǎn)編號(hào)","100010002"

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦3","規(guī)格型號(hào)","IBM5662","設(shè)備編號(hào)","10003","使用部門","管理部","固定資產(chǎn)編號(hào)","100010003"
end

頁(yè)面設(shè)計(jì)代碼:

namespace ExcelToTxt  {      partial class Form1      {          /// <summary>         /// 必需的設(shè)計(jì)器變量。          /// </summary>         private System.ComponentModel.IContainer components = null;           /// <summary>         /// 清理所有正在使用的資源。          /// </summary>         /// <param name="disposing">如果應(yīng)釋放托管資源，為 true；否則為 false。</param>         protected override void Dispose(bool disposing)          {              if (disposing && (components != null))              {                  components.Dispose();              }              base.Dispose(disposing);          }           #region Windows 窗體設(shè)計(jì)器生成的代碼           /// <summary>         /// 設(shè)計(jì)器支持所需的方法 - 不要          /// 使用代碼編輯器修改此方法的內(nèi)容。          /// </summary>         private void InitializeComponent()          {              this.dgvShow = new System.Windows.Forms.DataGridView();              this.btnSelect = new System.Windows.Forms.Button();              this.btnChange = new System.Windows.Forms.Button();              ((System.ComponentModel.ISupportInitialize)(this.dgvShow)).BeginInit();              this.SuspendLayout();              //               // dgvShow              //               this.dgvShow.AllowUserToAddRows = false;              this.dgvShow.AllowUserToDeleteRows = false;              this.dgvShow.AllowUserToResizeRows = false;              this.dgvShow.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;              this.dgvShow.Dock = System.Windows.Forms.DockStyle.Top;              this.dgvShow.Location = new System.Drawing.Point(0, 0);              this.dgvShow.Name = "dgvShow";              this.dgvShow.RowTemplate.Height = 23;              this.dgvShow.Size = new System.Drawing.Size(885, 600);              this.dgvShow.TabIndex = 0;              //               // btnSelect              //               this.btnSelect.Location = new System.Drawing.Point(202, 611);              this.btnSelect.Name = "btnSelect";              this.btnSelect.Size = new System.Drawing.Size(148, 23);              this.btnSelect.TabIndex = 1;              this.btnSelect.Text = "選擇excel文件";              this.btnSelect.UseVisualStyleBackColor = true;              this.btnSelect.Click += new System.EventHandler(this.btnSelect_Click);              //               // btnChange              //               this.btnChange.Location = new System.Drawing.Point(403, 611);              this.btnChange.Name = "btnChange";              this.btnChange.Size = new System.Drawing.Size(152, 23);              this.btnChange.TabIndex = 2;              this.btnChange.Text = "轉(zhuǎn)換為txt文檔";              this.btnChange.UseVisualStyleBackColor = true;              this.btnChange.Click += new System.EventHandler(this.btnChange_Click);              //               // Form1              //               this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);              this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;              this.ClientSize = new System.Drawing.Size(885, 646);              this.Controls.Add(this.btnChange);              this.Controls.Add(this.btnSelect);              this.Controls.Add(this.dgvShow);              this.Name = "Form1";              this.Text = "文件轉(zhuǎn)換";              ((System.ComponentModel.ISupportInitialize)(this.dgvShow)).EndInit();              this.ResumeLayout(false);           }           #endregion           private System.Windows.Forms.DataGridView dgvShow;          private System.Windows.Forms.Button btnSelect;          private System.Windows.Forms.Button btnChange;      }  }

C#數(shù)據(jù)轉(zhuǎn)換實(shí)現(xiàn)代碼:

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; //存儲(chǔ)EXCLE中的數(shù)據(jù)           public Form1()          {              InitializeComponent();              this.btnChange.Enabled = false;//初始化設(shè)置控件為不可用          }            /// <summary>          /// 該方法打開一個(gè)Excel文件          /// </summary>          /// <param name="sender"></param>          /// <param name="e"></param>          private void btnSelect_Click(object sender, EventArgs e)          {              string excelFilePath = ""; //存儲(chǔ)打開的文件的路徑                            OpenFileDialog selectFile = new OpenFileDialog();                            //選擇打開的文件設(shè)置              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;              }               //得到控件的數(shù)據(jù)源              dt = GetExcelData(excelFilePath);               //在顯示控件中顯示數(shù)據(jù)              ShowDataGridView();               //設(shè)置轉(zhuǎn)換格式的控件可用              this.btnChange.Enabled = true;          }            /// <summary>          ///該方法將選擇的EXCEL文件轉(zhuǎn)換成TXT文檔           /// </summary>          /// <param name="sender"></param>          /// <param name="e"></param>          private void btnChange_Click(object sender, EventArgs e)          {              string txtFilePath = "";//存儲(chǔ)選擇的TXT文檔的文件名              SaveFileDialog saveTxtFile = new SaveFileDialog();               //選擇保存的文件設(shè)置              saveTxtFile.Filter = "Text(.txt)|*.txt";              saveTxtFile.FilterIndex = 1;              saveTxtFile.DefaultExt = "txt";              saveTxtFile.AddExtension = true;              saveTxtFile.RestoreDirectory = true;              saveTxtFile.OverwritePrompt = true;               //選擇創(chuàng)建文件的文件夾              if (saveTxtFile.ShowDialog() == DialogResult.OK)              {                  txtFilePath = saveTxtFile.FileName; //獲取選擇的文件路徑              }              else             {                  return;              }               //將DataTable中的文件寫入到txt文檔中              Cursor.Current = Cursors.WaitCursor; //設(shè)置鼠標(biāo)狀態(tài)              int dtcols = dt.Columns.Count;              StringBuilder sbtxtdata = new StringBuilder(); ;  //臨時(shí)存儲(chǔ)從dt中讀出的每一條數(shù)據(jù)                //先創(chuàng)建一個(gè)新的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];                                    //設(shè)置固定的值                  tempstr[0] = "\"" + "檢測(cè)設(shè)備資產(chǎn)標(biāo)簽" + "\"" + ",";                  tempstr[1] = "\"" + "設(shè)備名稱" + "\"" + ",";                  tempstr[3] = "\"" + "規(guī)格型號(hào)" + "\"" + ",";                  tempstr[5] = "\"" + "設(shè)備編號(hào)" + "\"" + ",";                  tempstr[7] = "\"" + "使用部門" + "\"" + ",";                  tempstr[9] = "\"" + "固定資產(chǎn)編號(hào)" + "\"" + ",";                                     //標(biāo)簽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";                       //將本行數(shù)據(jù)寫入緩沖區(qū)                      foreach (string str in tempstr)                      {                          sbtxtdata.Append(str);                      }                      swTxtFile.Write(sbtxtdata);                                            //清空本行中的數(shù)據(jù)                      sbtxtdata.Remove(0, sbtxtdata.Length);                       //將數(shù)組中新添加的數(shù)據(jù)清空                      for (int i = 0; i < dt.Columns.Count; i++)                      {                          int tempindex = 2*(i+1);                          tempstr[tempindex] = "";                      }                  }              }              else             {                  string[] tempstr = new string[5];                  //標(biāo)簽0或1的格式寫入Txt文檔                  for (int rows = 0; rows < dt.Rows.Count; rows++)                  {                      for (int cols = 0; cols < dt.Columns.Count; cols++)                      {                          string temp = "";//臨時(shí)存儲(chǔ)當(dāng)前時(shí)間                           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";                          }                      }                       //將本行數(shù)據(jù)寫入緩沖區(qū)                      foreach (string str in tempstr)                      {                          sbtxtdata.Append(str);                      }                      swTxtFile.Write(sbtxtdata);                       //清空本行中的數(shù)據(jù)                      sbtxtdata.Remove(0, sbtxtdata.Length);                       //將數(shù)組中新添加的數(shù)據(jù)清空                      for (int i = 0; i < dt.Columns.Count; i++)                      {                          tempstr[i] = "";                      }                  }              }               //將數(shù)據(jù)寫入文檔              swTxtFile.Write("end");              swTxtFile.Flush();              swTxtFile.Close();              fsTxtFile.Close();               //重新設(shè)置鼠標(biāo)格式              Cursor.Current = Cursors.Default;              MessageBox.Show("文件轉(zhuǎn)換成功!", "提示",                      MessageBoxButtons.OK,  MessageBoxIcon.Information);          }            /// <summary>          /// 獲取Excel文件中的數(shù)據(jù)          /// </summary>          /// <param name="path">Excel文件的路徑</param>          /// <returns>DataTable:將Excel文件的數(shù)據(jù)加載到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);               //打開數(shù)據(jù)源連接              try             {                  if (excelConn.State == ConnectionState.Closed)                  {                      excelConn.Open();                  }              }              catch (Exception ex)              {                  MessageBox.Show("打開數(shù)據(jù)源連接失??！", "錯(cuò)誤",                           MessageBoxButtons.OK, MessageBoxIcon.Error);                  Application.Exit();              }              finally             {                  if(excelConn.State == ConnectionState.Open)                  excelConn.Close();              }               //設(shè)置查詢命令              OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);              DataSet ds = new DataSet();                            //執(zhí)行該查詢EXCEL表的命令              try             {                  myCommand.Fill(ds, "excelTable");              }              catch (Exception ex)              {                  MessageBox.Show("該Excel文件的工作表的名字不是[Sheet1$]!", "錯(cuò)誤",                                         MessageBoxButtons.OK, MessageBoxIcon.Error);                  Application.Exit();              }              finally             {                  if (excelConn.State == ConnectionState.Closed)                  {                      excelConn.Close();                  }              }               //判斷DataTable中是否有數(shù)據(jù)              if (ds.Tables["excelTable"].Rows.Count > 0)              {                  return ds.Tables["excelTable"];              }              else             {                  MessageBox.Show("沒有讀到Excel表中的數(shù)據(jù)!", "錯(cuò)誤",                                           MessageBoxButtons.OK, MessageBoxIcon.Error);                  return null;              }          }            /// <summary>          /// 將選擇的excel表中的數(shù)據(jù)現(xiàn)在DataGridView中          /// </summary>          private void ShowDataGridView()          {              //設(shè)置顯示控件的樣式              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;               //設(shè)置顯示控件的數(shù)據(jù)源              dgvShow.DataSource = dt;          }                 }  }

以上是“C#中如何實(shí)現(xiàn)EXCEL轉(zhuǎn)換成TXT文檔”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！