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;//初始化設置控件為不可用
}
/// lt;summarygt;
/// 該方法打開一個Excel文件
/// lt;/summarygt;
/// lt;param name="sender"gt;lt;/paramgt;
/// lt;param name="e"gt;lt;/paramgt;
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;
}
//得到控件的數(shù)據(jù)源
dt = GetExcelData(excelFilePath);
//在顯示控件中顯示數(shù)據(jù)
ShowDataGridView();
//設置轉換格式的控件可用
this.btnChange.Enabled = true;
}
/// lt;summarygt;
///該方法將選擇的EXCEL文件轉換成TXT文檔
/// lt;/summarygt;
/// lt;param name="sender"gt;lt;/paramgt;
/// lt;param name="e"gt;lt;/paramgt;
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;
//選擇創(chuàng)建文件的文件夾
if (saveTxtFile.ShowDialog() == DialogResult.OK)
{
txtFilePath = saveTxtFile.FileName; //獲取選擇的文件路徑
}
else
{
return;
}
//將DataTable中的文件寫入到txt文檔中
Cursor.Current = Cursors.WaitCursor; //設置鼠標狀態(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 gt; 3)
{
string[] tempstr = new string[11];
//設置固定的值
tempstr[0] = "\"" + "檢測設備資產(chǎn)標簽" + "\"" + ",";
tempstr[1] = "\"" + "設備名稱" + "\"" + ",";
tempstr[3] = "\"" + "規(guī)格型號" + "\"" + ",";
tempstr[5] = "\"" + "設備編號" + "\"" + ",";
tempstr[7] = "\"" + "使用部門" + "\"" + ",";
tempstr[9] = "\"" + "固定資產(chǎn)編號" + "\"" + ",";
//標簽2的格式寫入Txt文檔
for(int rows = 0; rows lt; dt.Rows.Count; rows++)
{
for (int cols = 0; cols lt; 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 lt; 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 lt; dt.Rows.Count; rows++)
{
for (int cols = 0; cols lt; 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";
}
}
//將本行數(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 lt; dt.Columns.Count; i++)
{
tempstr[i] = "";
}
}
}
//將數(shù)據(jù)寫入文檔
swTxtFile.Write("end");
swTxtFile.Flush();
swTxtFile.Close();
fsTxtFile.Close();
//重新設置鼠標格式
Cursor.Current = Cursors.Default;
MessageBox.Show("文件轉換成功!", "提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// lt;summarygt;
/// 獲取Excel文件中的數(shù)據(jù)
/// lt;/summarygt;
/// lt;param name="path"gt;Excel文件的路徑lt;/paramgt;
/// lt;returnsgt;DataTable:將Excel文件的數(shù)據(jù)加載到DataTable中l(wèi)t;/returnsgt;
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();
}
//設置查詢命令
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 gt; 0)
{
return ds.Tables["excelTable"];
}
else
{
MessageBox.Show("沒有讀到Excel表中的數(shù)據(jù)!", "錯誤",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
/// lt;summarygt;
/// 將選擇的excel表中的數(shù)據(jù)現(xiàn)在DataGridView中
/// lt;/summarygt;
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;
//設置顯示控件的數(shù)據(jù)源
dgvShow.DataSource = dt;
}
}
}
