这篇文章主要为大家展示了“C#中如何实现EXCEL转换成TXT文档”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“C#中如何实现EXCEL转换成TXT文档”这篇文章吧。
C#数据转换前excel中的数据格式如下:
设备名称 规格型号 设备编号 使用部门 固定资产编号
电脑1 IBM5660 10001 管理部 100010001
电脑2 IBM5661 10002 研发部 100010002
电脑3 IBM5662 10003 管理部 100010003
C#数据转换到TXT文档的格式:
"检测设备资产标签","设备名称","电脑1","规格型号","IBM5660","设备编号","10001","使用部门","管理部","固定资产编号","100010001"
"检测设备资产标签","设备名称","电脑2","规格型号","IBM5661","设备编号","10002","使用部门","研发部","固定资产编号","100010002"
"检测设备资产标签","设备名称","电脑3","规格型号","IBM5662","设备编号","10003","使用部门","管理部","固定资产编号","100010003"
end
页面设计代码:
namespace ExcelToTxt { partial class Form1 { /// <summary> /// 必需的设计器变量。 /// </summary> private System.ComponentModel.IContainer components = null; /// <summary> /// 清理所有正在使用的资源。 /// </summary> /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows 窗体设计器生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要 /// 使用代码编辑器修改此方法的内容。 /// </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 = "转换为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 = "文件转换"; ((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#数据转换实现代码:
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; } } }
以上是“C#中如何实现EXCEL转换成TXT文档”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/209327.html