C#中如何实现EXCEL转换成TXT文档
这篇文章主要为大家展示了“C#中如何实现EXCEL转换成TXT文档”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“C#中如何实现EXCEL转换成TXT文档”这篇文章吧。
创新互联公司是一家专业提供吉安企业网站建设,专注与成都网站制作、成都网站建设、H5网站设计、小程序制作等业务。10年已为吉安众多企业、政府机构等服务。创新互联专业网站制作公司优惠进行中。
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 { /// /// 必需的设计器变量。 /// private System.ComponentModel.IContainer components = null; /// /// 清理所有正在使用的资源。 /// /// 如果应释放托管资源，为 true；否则为 false。 protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows 窗体设计器生成的代码 /// /// 设计器支持所需的方法 - 不要 /// 使用代码编辑器修改此方法的内容。 /// 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;//初始化设置控件为不可用 } /// /// 该方法打开一个Excel文件 /// /// /// 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; } /// ///该方法将选择的EXCEL文件转换成TXT文档 /// /// /// 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); } /// /// 获取Excel文件中的数据 /// /// Excel文件的路径 /// DataTable:将Excel文件的数据加载到DataTable中 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; } } /// /// 将选择的excel表中的数据现在DataGridView中 /// 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文档”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！
文章题目：C#中如何实现EXCEL转换成TXT文档
分享URL：http://hbruida.cn/article/jdpdds.html
