【EXCEL文档批量转换为TX文件,c#源码VS2021环境】

原代码如下,按图上布置好控件。

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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值