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

}
}