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;
}
}
}
上一篇: php 运算符与表达式详细介绍
下一篇: php判断是否为ajax请求的方法