自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

C#數(shù)據(jù)轉(zhuǎn)換實(shí)現(xiàn)EXCEL到TXT文檔

作者：yangshun2008
開發(fā) 后端
今天寫了一 EXCEL數(shù)據(jù)按一定格式轉(zhuǎn)換到TXT文檔的C#數(shù)據(jù)轉(zhuǎn)換.在對(duì)EXCEL表的讀取采用了.NET提供的OLEDB技術(shù),對(duì)字符串的連接使用了StringBuilder類。

C#數(shù)據(jù)轉(zhuǎn)換前excel中的數(shù)據(jù)格式如下:

設(shè)備名稱 規(guī)格型號(hào) 設(shè)備編號(hào)  使用部門 固定資產(chǎn)編號(hào)

電腦1 IBM5660 10001 管理部 100010001

電腦2 IBM5661 10002 研發(fā)部 100010002

電腦3 IBM5662 10003 管理部 100010003

C#數(shù)據(jù)轉(zhuǎn)換到TXT文檔的格式:

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦1","規(guī)格型號(hào)","IBM5660","設(shè)備編號(hào)","10001","使用部門","管理部","固定資產(chǎn)編號(hào)","100010001"

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦2","規(guī)格型號(hào)","IBM5661","設(shè)備編號(hào)","10002","使用部門","研發(fā)部","固定資產(chǎn)編號(hào)","100010002"

"檢測(cè)設(shè)備資產(chǎn)標(biāo)簽","設(shè)備名稱","電腦3","規(guī)格型號(hào)","IBM5662","設(shè)備編號(hào)","10003","使用部門","管理部","固定資產(chǎn)編號(hào)","100010003"
end


頁面設(shè)計(jì)代碼:

  1. namespace ExcelToTxt  
  2. {  
  3.     partial class Form1  
  4.     {  
  5.         /// <summary> 
  6.         /// 必需的設(shè)計(jì)器變量。  
  7.         /// </summary> 
  8.         private System.ComponentModel.IContainer components = null;  
  9.  
  10.         /// <summary> 
  11.         /// 清理所有正在使用的資源。  
  12.         /// </summary> 
  13.         /// <param name="disposing">如果應(yīng)釋放托管資源，為 true；否則為 false。</param> 
  14.         protected override void Dispose(bool disposing)  
  15.         {  
  16.             if (disposing && (components != null))  
  17.             {  
  18.                 components.Dispose();  
  19.             }  
  20.             base.Dispose(disposing);  
  21.         }  
  22.  
  23.         #region Windows 窗體設(shè)計(jì)器生成的代碼  
  24.  
  25.         /// <summary> 
  26.         /// 設(shè)計(jì)器支持所需的方法 - 不要  
  27.         /// 使用代碼編輯器修改此方法的內(nèi)容。  
  28.         /// </summary> 
  29.         private void InitializeComponent()  
  30.         {  
  31.             this.dgvShow = new System.Windows.Forms.DataGridView();  
  32.             this.btnSelect = new System.Windows.Forms.Button();  
  33.             this.btnChange = new System.Windows.Forms.Button();  
  34.             ((System.ComponentModel.ISupportInitialize)(this.dgvShow)).BeginInit();  
  35.             this.SuspendLayout();  
  36.             //   
  37.             // dgvShow  
  38.             //   
  39.             this.dgvShow.AllowUserToAddRows = false;  
  40.             this.dgvShow.AllowUserToDeleteRows = false;  
  41.             this.dgvShow.AllowUserToResizeRows = false;  
  42.             this.dgvShow.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;  
  43.             this.dgvShow.Dock = System.Windows.Forms.DockStyle.Top;  
  44.             this.dgvShow.Location = new System.Drawing.Point(0, 0);  
  45.             this.dgvShow.Name = "dgvShow";  
  46.             this.dgvShow.RowTemplate.Height = 23;  
  47.             this.dgvShow.Size = new System.Drawing.Size(885, 600);  
  48.             this.dgvShow.TabIndex = 0;  
  49.             //   
  50.             // btnSelect  
  51.             //   
  52.             this.btnSelect.Location = new System.Drawing.Point(202, 611);  
  53.             this.btnSelect.Name = "btnSelect";  
  54.             this.btnSelect.Size = new System.Drawing.Size(148, 23);  
  55.             this.btnSelect.TabIndex = 1;  
  56.             this.btnSelect.Text = "選擇excel文件";  
  57.             this.btnSelect.UseVisualStyleBackColor = true;  
  58.             this.btnSelect.Click += new System.EventHandler(this.btnSelect_Click);  
  59.             //   
  60.             // btnChange  
  61.             //   
  62.             this.btnChange.Location = new System.Drawing.Point(403, 611);  
  63.             this.btnChange.Name = "btnChange";  
  64.             this.btnChange.Size = new System.Drawing.Size(152, 23);  
  65.             this.btnChange.TabIndex = 2;  
  66.             this.btnChange.Text = "轉(zhuǎn)換為txt文檔";  
  67.             this.btnChange.UseVisualStyleBackColor = true;  
  68.             this.btnChange.Click += new System.EventHandler(this.btnChange_Click);  
  69.             //   
  70.             // Form1  
  71.             //   
  72.             this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);  
  73.             this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;  
  74.             this.ClientSize = new System.Drawing.Size(885, 646);  
  75.             this.Controls.Add(this.btnChange);  
  76.             this.Controls.Add(this.btnSelect);  
  77.             this.Controls.Add(this.dgvShow);  
  78.             this.Name = "Form1";  
  79.             this.Text = "文件轉(zhuǎn)換";  
  80.             ((System.ComponentModel.ISupportInitialize)(this.dgvShow)).EndInit();  
  81.             this.ResumeLayout(false);  
  82.  
  83.         }  
  84.  
  85.         #endregion  
  86.  
  87.         private System.Windows.Forms.DataGridView dgvShow;  
  88.         private System.Windows.Forms.Button btnSelect;  
  89.         private System.Windows.Forms.Button btnChange;  
  90.     }  
  91. } 

C#數(shù)據(jù)轉(zhuǎn)換實(shí)現(xiàn)代碼:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Data.OleDb;  
  6. using System.Drawing;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.IO;  
  10.  
  11.  
  12. namespace ExcelToTxt  
  13. {  
  14.     public partial class Form1 : Form  
  15.     {  
  16.         private DataTable dt; //存儲(chǔ)EXCLE中的數(shù)據(jù)  
  17.  
  18.         public Form1()  
  19.         {  
  20.             InitializeComponent();  
  21.             this.btnChange.Enabled = false;//初始化設(shè)置控件為不可用  
  22.         }  
  23.  
  24.  
  25.         /// <summary>  
  26.         /// 該方法打開一個(gè)Excel文件  
  27.         /// </summary>  
  28.         /// <param name="sender"></param>  
  29.         /// <param name="e"></param>  
  30.         private void btnSelect_Click(object sender, EventArgs e)  
  31.         {  
  32.             string excelFilePath = ""; //存儲(chǔ)打開的文件的路徑  
  33.               
  34.             OpenFileDialog selectFile = new OpenFileDialog();  
  35.               
  36.             //選擇打開的文件設(shè)置  
  37.             selectFile.Filter = "Excel(*.xls)|*.xls";  
  38.             selectFile.FilterIndex = 1;  
  39.             selectFile.DefaultExt = "xls";  
  40.             selectFile.AddExtension = true;  
  41.             selectFile.RestoreDirectory = true;  
  42.             selectFile.Multiselect = false;  
  43.               
  44.             //選擇文件  
  45.             if (selectFile.ShowDialog() == DialogResult.OK)  
  46.             {  
  47.                 excelFilePath = selectFile.FileName;//獲取選擇的文件路徑  
  48.             }  
  49.             else 
  50.             {  
  51.                 return;  
  52.             }  
  53.  
  54.             //得到控件的數(shù)據(jù)源  
  55.             dt = GetExcelData(excelFilePath);  
  56.  
  57.             //在顯示控件中顯示數(shù)據(jù)  
  58.             ShowDataGridView();  
  59.  
  60.             //設(shè)置轉(zhuǎn)換格式的控件可用  
  61.             this.btnChange.Enabled = true;  
  62.         }  
  63.  
  64.  
  65.         /// <summary>  
  66.         ///該方法將選擇的EXCEL文件轉(zhuǎn)換成TXT文檔   
  67.         /// </summary>  
  68.         /// <param name="sender"></param>  
  69.         /// <param name="e"></param>  
  70.         private void btnChange_Click(object sender, EventArgs e)  
  71.         {  
  72.             string txtFilePath = "";//存儲(chǔ)選擇的TXT文檔的文件名  
  73.             SaveFileDialog saveTxtFile = new SaveFileDialog();  
  74.  
  75.             //選擇保存的文件設(shè)置  
  76.             saveTxtFile.Filter = "Text(.txt)|*.txt";  
  77.             saveTxtFile.FilterIndex = 1;  
  78.             saveTxtFile.DefaultExt = "txt";  
  79.             saveTxtFile.AddExtension = true;  
  80.             saveTxtFile.RestoreDirectory = true;  
  81.             saveTxtFile.OverwritePrompt = true;  
  82.  
  83.             //選擇創(chuàng)建文件的文件夾  
  84.             if (saveTxtFile.ShowDialog() == DialogResult.OK)  
  85.             {  
  86.                 txtFilePath = saveTxtFile.FileName; //獲取選擇的文件路徑  
  87.             }  
  88.             else 
  89.             {  
  90.                 return;  
  91.             }  
  92.  
  93.             //將DataTable中的文件寫入到txt文檔中  
  94.             Cursor.Current = Cursors.WaitCursor; //設(shè)置鼠標(biāo)狀態(tài)  
  95.             int dtcols = dt.Columns.Count;  
  96.             StringBuilder sbtxtdata = new StringBuilder(); ;  //臨時(shí)存儲(chǔ)從dt中讀出的每一條數(shù)據(jù)  
  97.  
  98.  
  99.             //先創(chuàng)建一個(gè)新的TXT文檔  
  100.             FileStream fsTxtFile = new FileStream(txtFilePath, FileMode.CreateNew, FileAccess.Write);  
  101.             StreamWriter swTxtFile = new StreamWriter(fsTxtFile, Encoding.GetEncoding("gb2312") );  
  102.  
  103.             if (dtcols > 3)  
  104.             {  
  105.                 string[] tempstr = new string[11];  
  106.                   
  107.                 //設(shè)置固定的值  
  108.                 tempstr[0] = "\"" + "檢測(cè)設(shè)備資產(chǎn)標(biāo)簽" + "\"" + ",";  
  109.                 tempstr[1] = "\"" + "設(shè)備名稱" + "\"" + ",";  
  110.                 tempstr[3] = "\"" + "規(guī)格型號(hào)" + "\"" + ",";  
  111.                 tempstr[5] = "\"" + "設(shè)備編號(hào)" + "\"" + ",";  
  112.                 tempstr[7] = "\"" + "使用部門" + "\"" + ",";  
  113.                 tempstr[9] = "\"" + "固定資產(chǎn)編號(hào)" + "\"" + ",";   
  114.                   
  115.                 //標(biāo)簽2的格式寫入Txt文檔  
  116.                 for(int rows = 0; rows < dt.Rows.Count; rows++)  
  117.                 {  
  118.                     for (int cols = 0; cols < dt.Columns.Count; cols++)  
  119.                     {  
  120.                         int tempindex = 2*(cols+1);  
  121.                         tempstr[tempindex] = "\"" + dt.Rows[rows][cols].ToString() + "\"";  
  122.                     }  
  123.  
  124.                     tempstr[2] = tempstr[2] + ",";  
  125.                     tempstr[4] = tempstr[4] + ",";  
  126.                     tempstr[6] = tempstr[6] + ",";  
  127.                     tempstr[8] = tempstr[8] + ",";  
  128.                     tempstr[10] = tempstr[10] + "\r\n";  
  129.  
  130.                     //將本行數(shù)據(jù)寫入緩沖區(qū)  
  131.                     foreach (string str in tempstr)  
  132.                     {  
  133.                         sbtxtdata.Append(str);  
  134.                     }  
  135.                     swTxtFile.Write(sbtxtdata);  
  136.                       
  137.                     //清空本行中的數(shù)據(jù)  
  138.                     sbtxtdata.Remove(0, sbtxtdata.Length);  
  139.  
  140.                     //將數(shù)組中新添加的數(shù)據(jù)清空  
  141.                     for (int i = 0; i < dt.Columns.Count; i++)  
  142.                     {  
  143.                         int tempindex = 2*(i+1);  
  144.                         tempstr[tempindex] = "";  
  145.                     }  
  146.                 }  
  147.             }  
  148.             else 
  149.             {  
  150.                 string[] tempstr = new string[5];  
  151.                 //標(biāo)簽0或1的格式寫入Txt文檔  
  152.                 for (int rows = 0; rows < dt.Rows.Count; rows++)  
  153.                 {  
  154.                     for (int cols = 0; cols < dt.Columns.Count; cols++)  
  155.                     {  
  156.                         string temp = "";//臨時(shí)存儲(chǔ)當(dāng)前時(shí)間  
  157.  
  158.                         if (cols == 0)  
  159.                         {  
  160.                             tempstr[0] = "\"" + dt.Rows[rows][cols] + "\"" + ",";  
  161.                         }  
  162.                         else if (cols == 1)  
  163.                         {  
  164.                             temp = dt.Rows[rows][cols].ToString();  
  165.                             tempstr[1] = "\"" + temp.Substring(0, 4) + "\"" + ","; //截取年  
  166.                             tempstr[2] = "\"" + temp.Substring(4, 2) + "\"" + ","; //截取月  
  167.                             tempstr[3] = "\"" + temp.Substring(6, 2) + "\"" + ","; //截取日  
  168.                         }  
  169.                         else if (cols == 2)  
  170.                         {  
  171.                             tempstr[4] = "\"" + dt.Rows[rows][cols] + "\"" + "\r\n";  
  172.                         }  
  173.                     }  
  174.  
  175.                     //將本行數(shù)據(jù)寫入緩沖區(qū)  
  176.                     foreach (string str in tempstr)  
  177.                     {  
  178.                         sbtxtdata.Append(str);  
  179.                     }  
  180.                     swTxtFile.Write(sbtxtdata);  
  181.  
  182.                     //清空本行中的數(shù)據(jù)  
  183.                     sbtxtdata.Remove(0, sbtxtdata.Length);  
  184.  
  185.                     //將數(shù)組中新添加的數(shù)據(jù)清空  
  186.                     for (int i = 0; i < dt.Columns.Count; i++)  
  187.                     {  
  188.                         tempstr[i] = "";  
  189.                     }  
  190.                 }  
  191.             }  
  192.  
  193.             //將數(shù)據(jù)寫入文檔  
  194.             swTxtFile.Write("end");  
  195.             swTxtFile.Flush();  
  196.             swTxtFile.Close();  
  197.             fsTxtFile.Close();  
  198.  
  199.             //重新設(shè)置鼠標(biāo)格式  
  200.             Cursor.Current = Cursors.Default;  
  201.             MessageBox.Show("文件轉(zhuǎn)換成功!", "提示",  
  202.                     MessageBoxButtons.OK,  MessageBoxIcon.Information);  
  203.         }  
  204.  
  205.  
  206.         /// <summary>  
  207.         /// 獲取Excel文件中的數(shù)據(jù)  
  208.         /// </summary>  
  209.         /// <param name="path">Excel文件的路徑</param>  
  210.         /// <returns>DataTable:將Excel文件的數(shù)據(jù)加載到DataTable中</returns>  
  211.         private DataTable GetExcelData(string path)  
  212.         {  
  213.             //連接字符串確定  
  214.             string excelstr = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + path + " ;"   
  215.                         + " Extended Properties = Excel 8.0;";  
  216.               
  217.             OleDbConnection excelConn = new OleDbConnection(excelstr);  
  218.  
  219.             //打開數(shù)據(jù)源連接  
  220.             try 
  221.             {  
  222.                 if (excelConn.State == ConnectionState.Closed)  
  223.                 {  
  224.                     excelConn.Open();  
  225.                 }  
  226.             }  
  227.             catch (Exception ex)  
  228.             {  
  229.                 MessageBox.Show("打開數(shù)據(jù)源連接失??！", "錯(cuò)誤",   
  230.                         MessageBoxButtons.OK, MessageBoxIcon.Error);  
  231.                 Application.Exit();  
  232.             }  
  233.             finally 
  234.             {  
  235.                 if(excelConn.State == ConnectionState.Open)  
  236.                 excelConn.Close();  
  237.             }  
  238.  
  239.             //設(shè)置查詢命令  
  240.             OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);  
  241.             DataSet ds = new DataSet();  
  242.               
  243.             //執(zhí)行該查詢EXCEL表的命令  
  244.             try 
  245.             {  
  246.                 myCommand.Fill(ds, "excelTable");  
  247.             }  
  248.             catch (Exception ex)  
  249.             {  
  250.                 MessageBox.Show("該Excel文件的工作表的名字不是[Sheet1$]!", "錯(cuò)誤",   
  251.                                       MessageBoxButtons.OK, MessageBoxIcon.Error);  
  252.                 Application.Exit();  
  253.             }  
  254.             finally 
  255.             {  
  256.                 if (excelConn.State == ConnectionState.Closed)  
  257.                 {  
  258.                     excelConn.Close();  
  259.                 }  
  260.             }  
  261.  
  262.             //判斷DataTable中是否有數(shù)據(jù)  
  263.             if (ds.Tables["excelTable"].Rows.Count > 0)  
  264.             {  
  265.                 return ds.Tables["excelTable"];  
  266.             }  
  267.             else 
  268.             {  
  269.                 MessageBox.Show("沒有讀到Excel表中的數(shù)據(jù)!", "錯(cuò)誤",   
  270.                                         MessageBoxButtons.OK, MessageBoxIcon.Error);  
  271.                 return null;  
  272.             }  
  273.         }  
  274.  
  275.  
  276.         /// <summary>  
  277.         /// 將選擇的excel表中的數(shù)據(jù)現(xiàn)在DataGridView中  
  278.         /// </summary>  
  279.         private void ShowDataGridView()  
  280.         {  
  281.             //設(shè)置顯示控件的樣式  
  282.             this.dgvShow.DefaultCellStyle.BackColor = Color.Beige;  
  283.             this.dgvShow.DefaultCellStyle.Font = new Font("Tahoma", 12);  
  284.  
  285.             DataGridViewCellStyle highlightCellStyle = new DataGridViewCellStyle();  
  286.             highlightCellStyle.BackColor = Color.Red;  
  287.  
  288.             DataGridViewCellStyle currencyCellStyle = new DataGridViewCellStyle();  
  289.             currencyCellStyle.Format = "C";  
  290.             currencyCellStyle.ForeColor = Color.Green;  
  291.  
  292.             //設(shè)置顯示控件的數(shù)據(jù)源  
  293.             dgvShow.DataSource = dt;  
  294.         }  
  295.            
  296.     }  
  297. } 

本文來自yangshun2008的CSDN博客文章《C#實(shí)現(xiàn)EXCEL數(shù)據(jù)轉(zhuǎn)換到TXT文檔》

【編輯推薦】

  1. C#反射方法學(xué)習(xí)總結(jié)
  2. 淺談C#測(cè)量cpu性能
  3. C#遠(yuǎn)程計(jì)算機(jī)的一些理論知識(shí)
  4. 淺析C# Static修飾
  5. C#轉(zhuǎn)換農(nóng)歷的簡(jiǎn)單方法
責(zé)任編輯：彭凡 來源： CSDN
C#數(shù)據(jù)轉(zhuǎn)換
相關(guān)推薦

2009-08-12 18:29:06

C#讀取TXT文件

2009-08-13 10:15:50

C#讀取Excel

2009-08-26 11:32:37

C#打印文檔

2009-08-11 14:51:47

C#讀取Excel中數(shù)

2009-08-28 10:55:16

C#實(shí)現(xiàn)轉(zhuǎn)換十六進(jìn)制

2009-08-12 16:39:50

C#向Excel插入數(shù)

2011-11-29 13:53:34

ADO.NET

2009-08-13 10:27:28

C#讀取Excel數(shù)據(jù)

2009-09-01 13:59:01

C#操作Excel

2009-08-13 09:32:00

C#讀取TXT文件

2009-08-12 16:26:30

C#讀取XML文檔

2009-09-04 13:55:04

C#文檔自動(dòng)化

2009-09-01 14:18:09

C#打開Excel文檔

2009-07-31 13:48:09

C#時(shí)間轉(zhuǎn)換

2009-08-10 13:49:50

C# txt文件

2009-08-26 13:22:24

C#打印程序

2009-08-24 09:55:26

C#接口轉(zhuǎn)換

2009-08-11 15:22:57

C#讀Excel

2009-09-11 12:41:41

C#類型轉(zhuǎn)換

2009-08-07 12:57:03

C#讀取Excel
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)