原代码如下,按图上布置好控件。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlTypes;
using System.Threading;
namespace exceltotxt
{
public partial class Form1 : Form
{
static bool _isVerbose = false;//显示转换信息。
static bool _isAllian = false;//对齐列打印。
public Form1()
{
InitializeComponent();
// Control.CheckForIllegalCrossThreadCalls = false; //加载时 取消跨线程检查
}
private void button1_Click(object sender, EventArgs e)
{
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = folderBrowserDialog1.SelectedPath;
textBox3.Text = folderBrowserDialog1.SelectedPath;
}
}
private void button2_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
DirPathAll(textBox1.Text);
//MessageBox.Show("搜索完毕!");
//label2.Text = "搜索Excel文件完毕!";
int i=0;
while(i < listBox1.Items.Count)
{
if( listBox1.Items[i].ToString().Contains("\\~$"))//删除留下的临时文件。
{
listBox1.Items.RemoveAt(i);
i--;
}
i++;
}
label3.Text = "共搜索到当前目录及子目录下有Excel文件" + listBox1.Items.Count.ToString() + "个。";
}
//搜索指定目录的所有文件
private void DirPathAll(string dirpath)
{
//本目录下的文件
//var paths = Directory.GetFiles(dirpath, "*.*", SearchOption.AllDirectories).Where(s => s.EndsWith(".xls") || s.EndsWith(".xlsx"));
var paths = Directory.GetFiles(dirpath, "*.xls?");
foreach (var path in paths)
{
listBox1.Items.Add(path);
Application.DoEvents();
}
//所有子目录下的文件
var files = Directory.GetDirectories(dirpath); //获取所有子目录
foreach (var dir in files)
{
DirectoryInfo testdir = new DirectoryInfo(dir);
if (testdir.Attributes.ToString().Contains("System"))
continue;
//listBox1.Items.Add(dir);
var filepath = Directory.GetFiles(dir);
//foreach (var path in filepath) //每个子目录下的文件
//{
// Application.DoEvents();
// listBox1.Items.Add(path );
//}
DirPathAll(dir);
}
//textBox2.Text = listBox1.Items.Count.ToString();
}
// 获得字段的实际最大长度
//static int GetMaxLength(DataTable dt, string captionName)
//{
// DataColumn maxLengthColumn = new DataColumn();
// maxLengthColumn.ColumnName = "MaxLength";
// maxLengthColumn.Expression = String.Format("len(convert({0},'System.String'))", captionName);
// dt.Columns.Add(maxLengthColumn);
// object maxLength = dt.Compute("max(MaxLength)", "true");
// if (maxLength == DBNull.Value)
// {
// return 0;
// }
// dt.Columns.Remove(maxLengthColumn);
// return Convert.ToInt32(maxLength);
//}
//循环取文件。
void loopDir( string outDir)
{
for(int i=0, imax = listBox1.Items.Count; i<imax; i++)
//foreach (var file in listBox1.Items)
{
//string srcPath = file.ToString();
string srcPath = listBox1.Items[i].ToString();
string dstPath = outDir;
int m = i * 100 / (imax - 1);
backgroundWorker1.ReportProgress(m, i);
//jinxinzhongLabel.Text ="转换"+ (i+1) + "个文件,完成:" + m + "%.";
this.Invoke(new Action(() => { jinxinzhongLabel.Text = "转换" + (i + 1) + "个文件,完成:" + m + "%."; }));
// Console.WriteLine(srcPath + " " + dstPath);
convertExcelToTxt(srcPath, dstPath);
}
}
//excel转为txt
void convertExcelToTxt(string inputFile, string outputPath)
{
if (Path.GetExtension(inputFile).ToLower() != ".xls" && Path.GetExtension(inputFile).ToLower() != ".xlsx")
{
return;
}
outputPath=Path.GetDirectoryName(inputFile).Replace(textBox1.Text, outputPath);
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
string newFileNameNoExt = Path.GetFileNameWithoutExtension(inputFile);
string newFileNoExt = outputPath + "\\" + newFileNameNoExt;
string newFile = newFileNoExt + ".txt";
//Console.WriteLine("Convert file[{0}] to [{1}]", inputFile, newFile);
var conn = new OleDbConnection();
conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source={0}" +
";Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\"", inputFile);
try
{
conn.Open();
}
catch (Exception ex)
{
//错误处理
//listBox3.Items.Add(inputFile +" : "+ ex.Message);
Invoke(new Action(() => { listBox3.Items.Add(inputFile + " : " + ex.Message); }));
return;
}
//listBox2.Items.Add(newFile);
this.Invoke(new Action(() => { listBox2.Items.Add(newFile); }));
Application.DoEvents();
DataTable sheetTb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
FileStream fs = new FileStream(newFileNoExt + ".txt", FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(fs);
foreach (DataRow sheet in sheetTb.Rows)
{
string tableName = sheet["TABLE_NAME"].ToString();
string sql = String.Format("select * from [{0}]", tableName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
var ds = new DataSet();
da.Fill(ds);
var tb1 = ds.Tables[0];
if (tb1.Rows.Count == 0)
{
continue; // 空表
}
if (tb1.Rows.Count == 1 && tb1.Columns.Count == 1)
{
if (tb1.Rows[0][0] == DBNull.Value)
{
continue; // 空表
}
}
//FileStream fs = new FileStream(newFileNoExt + "_" + tableName.Trim('$') + ".txt", FileMode.OpenOrCreate);
// StreamWriter sw = new StreamWriter(fs);
int[] colMaxLen = new int[tb1.Columns.Count];
//对齐列打印。
if (_isAllian)
{
for (int i = 0; i < tb1.Columns.Count; ++i)
{
colMaxLen[i] = 0;
for (int j = 0; j < tb1.Rows.Count; ++j)
{
string s = tb1.Rows[j][i].ToString();
int len = System.Text.Encoding.Default.GetBytes(s).Length;
if (len > colMaxLen[i])
{
colMaxLen[i] = len;
}
}
}
}
foreach (DataRow row in tb1.Rows)
{
for (int j = 0; j < tb1.Columns.Count; ++j)
{
DataColumn col = tb1.Columns[j];
string content = row[j].ToString();
bool hasYinhao = false;
if (-1 != content.IndexOf("\r") || -1 != content.IndexOf("\n"))
{
hasYinhao = true;
}
string fmt;
//对齐列打印。
if (_isAllian)//对齐列打印。
{
int realLen = colMaxLen[j] - (System.Text.Encoding.Default.GetBytes(content).Length - content.Length);
// "{0,-10}"\t
fmt = String.Format("{0}{1}0,-{2}{3}{4}{5}", hasYinhao ? "\"" : "",
"{", realLen, "}", hasYinhao ? "\"" : "", j + 1 == tb1.Columns.Count ? "" : "\t");
}
else
{
// "{0}"\t
fmt = String.Format("{0}{1}0{2}{3}{4}", hasYinhao ? "\"" : "",
"{", "}", hasYinhao ? "\"" : "", j + 1 == tb1.Columns.Count ? "" : "\t");
}
sw.Write(fmt, row[j]);
//显示转换信息
if (_isVerbose)//显示转换信息
{
Console.Write(fmt, row[j]);
}
}
sw.WriteLine();
//显示转换信息
if (_isVerbose)
{
// Console.WriteLine();
}
}
sw.WriteLine("\r\n以上是 \" "+tableName.Trim('$')+" \" 表");
//sw.Close();
}
sw.Close();
conn.Close();
}
private void button4_Click(object sender, EventArgs e)
{
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
textBox3.Text = folderBrowserDialog1.SelectedPath;
}
}
private void button3_Click(object sender, EventArgs e)
{
progressBar1.Minimum = 0;
progressBar1.Maximum = 100;
progressBar1.Visible = true;
progressBar1.Show();
listBox2.Items.Clear();
//jinxinzhongLabel.Text = "转换中......";
backgroundWorker1.RunWorkerAsync();
//loopDir(textBox3.Text);
//jinxinzhongLabel.Text = "转换导出成功!";
//MessageBox.Show("转换导出成功完成!");
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
loopDir(textBox3.Text);
}
private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
this.progressBar1.Value = e.ProgressPercentage;
this.progressBar1.Show();
}
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
jinxinzhongLabel.Text += "导出成功!";
label2.Text += listBox3.Items.Count.ToString()+"个文件";
}
private void 说明ToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show("用于Excel文件批量转换为文本文件(保持其原有目录结构)。");
}
private void 关于ToolStripMenuItem_Click(object sender, EventArgs e)
{
MessageBox.Show("ExcelToTxt 1.1 Excel文件批量转换为文本文件(保持其原有目录结构),QQ:3174235.");
}
private void contextMenuStrip1_Opening(object sender, CancelEventArgs e)
{
string str="";
ListBox whichcontrol_name =(ListBox) (sender as ContextMenuStrip).SourceControl;
// ListBox listBox = (ListBox) whichcontrol_name.c;
foreach (string item in whichcontrol_name.SelectedItems)
str += item+"\r\n";
Clipboard.SetData(DataFormats.Text, str);
}
}
}