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

作者：yangshun2008 2009-08-13 16:44:45

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

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

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

電腦1 IBM5660 10001 管理部 100010001

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

電腦3 IBM5662 10003 管理部 100010003

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

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

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

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

頁面設(shè)計代碼:

namespace ExcelToTxt  
{  
    partial class Form1  
    {  
        /// <summary> 
        /// 必需的設(shè)計器變量。  
        /// </summary> 
        private System.ComponentModel.IContainer components = null;  
 
        /// <summary> 
        /// 清理所有正在使用的資源。  
        /// </summary> 
        /// <param name="disposing">如果應(yīng)釋放托管資源，為 true；否則為 false。</param> 
        protected override void Dispose(bool disposing)  
        {  
            if (disposing && (components != null))  
            {  
                components.Dispose();  
            }  
            base.Dispose(disposing);  
        }  
 
        #region Windows 窗體設(shè)計器生成的代碼  
 
        /// <summary> 
        /// 設(shè)計器支持所需的方法 - 不要  
        /// 使用代碼編輯器修改此方法的內(nèi)容。  
        /// </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 = "轉(zhuǎn)換為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 = "文件轉(zhuǎn)換";  
            ((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#數(shù)據(jù)轉(zhuǎn)換實現(xiàn)代碼:

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中的數(shù)據(jù)  
 
        public Form1()  
        {  
            InitializeComponent();  
            this.btnChange.Enabled = false;//初始化設(shè)置控件為不可用  
        }  
 
 
        /// <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();  
              
            //選擇打開的文件設(shè)置  
            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;  
            }  
 
            //得到控件的數(shù)據(jù)源  
            dt = GetExcelData(excelFilePath);  
 
            //在顯示控件中顯示數(shù)據(jù)  
            ShowDataGridView();  
 
            //設(shè)置轉(zhuǎn)換格式的控件可用  
            this.btnChange.Enabled = true;  
        }  
 
 
        /// <summary>  
        ///該方法將選擇的EXCEL文件轉(zhuǎn)換成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();  
 
            //選擇保存的文件設(shè)置  
            saveTxtFile.Filter = "Text(.txt)|*.txt";  
            saveTxtFile.FilterIndex = 1;  
            saveTxtFile.DefaultExt = "txt";  
            saveTxtFile.AddExtension = true;  
            saveTxtFile.RestoreDirectory = true;  
            saveTxtFile.OverwritePrompt = true;  
 
            //選擇創(chuàng)建文件的文件夾  
            if (saveTxtFile.ShowDialog() == DialogResult.OK)  
            {  
                txtFilePath = saveTxtFile.FileName; //獲取選擇的文件路徑  
            }  
            else 
            {  
                return;  
            }  
 
            //將DataTable中的文件寫入到txt文檔中  
            Cursor.Current = Cursors.WaitCursor; //設(shè)置鼠標(biāo)狀態(tài)  
            int dtcols = dt.Columns.Count;  
            StringBuilder sbtxtdata = new StringBuilder(); ;  //臨時存儲從dt中讀出的每一條數(shù)據(jù)  
 
 
            //先創(chuàng)建一個新的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];  
                  
                //設(shè)置固定的值  
                tempstr[0] = "\"" + "檢測設(shè)備資產(chǎn)標(biāo)簽" + "\"" + ",";  
                tempstr[1] = "\"" + "設(shè)備名稱" + "\"" + ",";  
                tempstr[3] = "\"" + "規(guī)格型號" + "\"" + ",";  
                tempstr[5] = "\"" + "設(shè)備編號" + "\"" + ",";  
                tempstr[7] = "\"" + "使用部門" + "\"" + ",";  
                tempstr[9] = "\"" + "固定資產(chǎn)編號" + "\"" + ",";   
                  
                //標(biāo)簽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";  
 
                    //將本行數(shù)據(jù)寫入緩沖區(qū)  
                    foreach (string str in tempstr)  
                    {  
                        sbtxtdata.Append(str);  
                    }  
                    swTxtFile.Write(sbtxtdata);  
                      
                    //清空本行中的數(shù)據(jù)  
                    sbtxtdata.Remove(0, sbtxtdata.Length);  
 
                    //將數(shù)組中新添加的數(shù)據(jù)清空  
                    for (int i = 0; i < dt.Columns.Count; i++)  
                    {  
                        int tempindex = 2*(i+1);  
                        tempstr[tempindex] = "";  
                    }  
                }  
            }  
            else 
            {  
                string[] tempstr = new string[5];  
                //標(biāo)簽0或1的格式寫入Txt文檔  
                for (int rows = 0; rows < dt.Rows.Count; rows++)  
                {  
                    for (int cols = 0; cols < dt.Columns.Count; cols++)  
                    {  
                        string temp = "";//臨時存儲當(dāng)前時間  
 
                        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";  
                        }  
                    }  
 
                    //將本行數(shù)據(jù)寫入緩沖區(qū)  
                    foreach (string str in tempstr)  
                    {  
                        sbtxtdata.Append(str);  
                    }  
                    swTxtFile.Write(sbtxtdata);  
 
                    //清空本行中的數(shù)據(jù)  
                    sbtxtdata.Remove(0, sbtxtdata.Length);  
 
                    //將數(shù)組中新添加的數(shù)據(jù)清空  
                    for (int i = 0; i < dt.Columns.Count; i++)  
                    {  
                        tempstr[i] = "";  
                    }  
                }  
            }  
 
            //將數(shù)據(jù)寫入文檔  
            swTxtFile.Write("end");  
            swTxtFile.Flush();  
            swTxtFile.Close();  
            fsTxtFile.Close();  
 
            //重新設(shè)置鼠標(biāo)格式  
            Cursor.Current = Cursors.Default;  
            MessageBox.Show("文件轉(zhuǎn)換成功!", "提示",  
                    MessageBoxButtons.OK,  MessageBoxIcon.Information);  
        }  
 
 
        /// <summary>  
        /// 獲取Excel文件中的數(shù)據(jù)  
        /// </summary>  
        /// <param name="path">Excel文件的路徑</param>  
        /// <returns>DataTable:將Excel文件的數(shù)據(jù)加載到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);  
 
            //打開數(shù)據(jù)源連接  
            try 
            {  
                if (excelConn.State == ConnectionState.Closed)  
                {  
                    excelConn.Open();  
                }  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show("打開數(shù)據(jù)源連接失敗！", "錯誤",   
                        MessageBoxButtons.OK, MessageBoxIcon.Error);  
                Application.Exit();  
            }  
            finally 
            {  
                if(excelConn.State == ConnectionState.Open)  
                excelConn.Close();  
            }  
 
            //設(shè)置查詢命令  
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);  
            DataSet ds = new DataSet();  
              
            //執(zhí)行該查詢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中是否有數(shù)據(jù)  
            if (ds.Tables["excelTable"].Rows.Count > 0)  
            {  
                return ds.Tables["excelTable"];  
            }  
            else 
            {  
                MessageBox.Show("沒有讀到Excel表中的數(shù)據(jù)!", "錯誤",   
                                        MessageBoxButtons.OK, MessageBoxIcon.Error);  
                return null;  
            }  
        }  
 
 
        /// <summary>  
        /// 將選擇的excel表中的數(shù)據(jù)現(xiàn)在DataGridView中  
        /// </summary>  
        private void ShowDataGridView()  
        {  
            //設(shè)置顯示控件的樣式  
            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;  
 
            //設(shè)置顯示控件的數(shù)據(jù)源  
            dgvShow.DataSource = dt;  
        }  
           
    }  
}

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

【編輯推薦】

C#反射方法學(xué)習(xí)總結(jié)
淺談C#測量cpu性能
C#遠程計算機的一些理論知識
淺析C# Static修飾
C#轉(zhuǎn)換農(nóng)歷的簡單方法

責(zé)任編輯：彭凡來源： CSDN

C#數(shù)據(jù)轉(zhuǎn)換