欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#实现EXCEL数据到TXT文档的转换

程序员文章站 2024-03-05 08:07:54
c#数据转换前excel中的数据格式如下:设备名称 规格型号 设备编号  使用部门 固定资产编号电脑1 ibm5660 10001 管理部 100010001电脑...


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;  
          }  

      }  
  }