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#数据转换实现代码:
复制代码 代码如下:
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
usingSystem.IO;
namespaceExcelToTxt
{
publicpartialclassForm1:Form
{
privateDataTabledt;//存储EXCLE中的数据
publicForm1()
{
InitializeComponent();
this.btnChange.Enabled=false;//初始化设置控件为不可用
}
///<summary>
///该方法打开一个Excel文件
///</summary>
///<paramname="sender"></param>
///<paramname="e"></param>
privatevoidbtnSelect_Click(objectsender,EventArgse)
{
stringexcelFilePath="";//存储打开的文件的路径
OpenFileDialogselectFile=newOpenFileDialog();
//选择打开的文件设置
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>
///<paramname="sender"></param>
///<paramname="e"></param>
privatevoidbtnChange_Click(objectsender,EventArgse)
{
stringtxtFilePath="";//存储选择的TXT文档的文件名
SaveFileDialogsaveTxtFile=newSaveFileDialog();
//选择保存的文件设置
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;//设置鼠标状态
intdtcols=dt.Columns.Count;
StringBuildersbtxtdata=newStringBuilder();;//临时存储从dt中读出的每一条数据
//先创建一个新的TXT文档
FileStreamfsTxtFile=newFileStream(txtFilePath,FileMode.CreateNew,FileAccess.Write);
StreamWriterswTxtFile=newStreamWriter(fsTxtFile,Encoding.GetEncoding("gb2312"));
if(dtcols>3)
{
string[]tempstr=newstring[11];
//设置固定的值
tempstr[0]="""+"检测设备资产标签"+"""+",";
tempstr[1]="""+"设备名称"+"""+",";
tempstr[3]="""+"规格型号"+"""+",";
tempstr[5]="""+"设备编号"+"""+",";
tempstr[7]="""+"使用部门"+"""+",";
tempstr[9]="""+"固定资产编号"+"""+",";
//标签2的格式写入Txt文档
for(introws=0;rows<dt.Rows.Count;rows++)
{
for(intcols=0;cols<dt.Columns.Count;cols++)
{
inttempindex=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]+"rn";
//将本行数据写入缓冲区
foreach(stringstrintempstr)
{
sbtxtdata.Append(str);
}
swTxtFile.Write(sbtxtdata);
//清空本行中的数据
sbtxtdata.Remove(0,sbtxtdata.Length);
//将数组中新添加的数据清空
for(inti=0;i<dt.Columns.Count;i++)
{
inttempindex=2*(i+1);
tempstr[tempindex]="";
}
}
}
else
{
string[]tempstr=newstring[5];
//标签0或1的格式写入Txt文档
for(introws=0;rows<dt.Rows.Count;rows++)
{
for(intcols=0;cols<dt.Columns.Count;cols++)
{
stringtemp="";//临时存储当前时间
if(cols==0)
{
tempstr[0]="""+dt.Rows[rows][cols]+"""+",";
}
elseif(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)+"""+",";//截取日
}
elseif(cols==2)
{
tempstr[4]="""+dt.Rows[rows][cols]+"""+"rn";
}
}
//将本行数据写入缓冲区
foreach(stringstrintempstr)
{
sbtxtdata.Append(str);
}
swTxtFile.Write(sbtxtdata);
//清空本行中的数据
sbtxtdata.Remove(0,sbtxtdata.Length);
//将数组中新添加的数据清空
for(inti=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>
///<paramname="path">Excel文件的路径</param>
///<returns>DataTable:将Excel文件的数据加载到DataTable中</returns>
privateDataTableGetExcelData(stringpath)
{
//连接字符串确定
stringexcelstr="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+path+";"
+"ExtendedProperties=Excel8.0;";
OleDbConnectionexcelConn=newOleDbConnection(excelstr);
//打开数据源连接
try
{
if(excelConn.State==ConnectionState.Closed)
{
excelConn.Open();
}
}
catch(Exceptionex)
{
MessageBox.Show("打开数据源连接失败！","错误",
MessageBoxButtons.OK,MessageBoxIcon.Error);
Application.Exit();
}
finally
{
if(excelConn.State==ConnectionState.Open)
excelConn.Close();
}
//设置查询命令
OleDbDataAdaptermyCommand=newOleDbDataAdapter("SELECT*FROM[Sheet1$]",excelConn);
DataSetds=newDataSet();
//执行该查询EXCEL表的命令
try
{
myCommand.Fill(ds,"excelTable");
}
catch(Exceptionex)
{
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)
{
returnds.Tables["excelTable"];
}
else
{
MessageBox.Show("没有读到Excel表中的数据!","错误",
MessageBoxButtons.OK,MessageBoxIcon.Error);
returnnull;
}
}
///<summary>
///将选择的excel表中的数据现在DataGridView中
///</summary>
privatevoidShowDataGridView()
{
//设置显示控件的样式
this.dgvShow.DefaultCellStyle.BackColor=Color.Beige;
this.dgvShow.DefaultCellStyle.Font=newFont("Tahoma",12);
DataGridViewCellStylehighlightCellStyle=newDataGridViewCellStyle();
highlightCellStyle.BackColor=Color.Red;
DataGridViewCellStylecurrencyCellStyle=newDataGridViewCellStyle();
currencyCellStyle.Format="C";
currencyCellStyle.ForeColor=Color.Green;
//设置显示控件的数据源
dgvShow.DataSource=dt;
}
}
}