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

      }  
  }