none
How to Merged all the file Excel on Specific Folder RRS feed

  • Question

  • Hi All,

    I want to ask favor, how I can have these result as my output.

    so I have this background, I have one directory with path like this :

    D:\Testing

    inside of that directory, was contain 2 zip files, example :1.zip and 2.zip

    each of the zip files, contains 2 excel files.

    1.zip contains -> A.xlsx & B.xlsx

    2.zip contains -> C.xlsx & D.xlsx

    so far, I have manage to unzip the zip files when I tried to run my apps.

    as results:

    D:\Testing\1\A.xlsx and B.xlsx

    D:\Testing\2\C.xlsx and C.xlsx

    every excel file have same format fields. and luckily I have help from Deepak at this forum how to merge those excel files and create a 1 new files which contains the merge result. here is my reference.

    here the code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    
    
    
    namespace ConsoleApplication7
    {
        class Program
        {
            static void Main(string[] args)
            {
                MergeExcel.DoMerge(new string[]
               {
                    @"C:\Users\Administrator\Desktop\demo1.xlsx",
                    @"C:\Users\Administrator\Desktop\demo2.xlsx"
               },
                   @"C:\Users\Administrator\Desktop\demo3.xlsx", "E", 2);
    
            }
        }
    
        public class MergeExcel
        {
            Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    
            Excel.Workbook bookDest = null;
            Excel.Worksheet sheetDest = null;
    
            Excel.Workbook bookSource = null;
            Excel.Worksheet sheetSource = null;
            string[] _sourceFiles = null;
            string _destFile = string.Empty;
            string _columnEnd = string.Empty;
            int _headerRowCount = 0;
            int _currentRowCount = 0;
            public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
            {
    
                bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
                sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
                sheetDest.Name = "Data";
                _sourceFiles = sourceFiles;
                _destFile = destFile;
                _columnEnd = columnEnd;
                _headerRowCount = headerRowCount;
            }
    
            void OpenBook(string fileName)
            {
                bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
            }
    
            void CloseBook()
            {
                bookSource.Close(false, Missing.Value, Missing.Value);
            }
    
            void CopyHeader()
            {
                Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
                range.Copy(sheetDest.get_Range("A1", Missing.Value));
                _currentRowCount += _headerRowCount;
            }
    
            void CopyData()
            {
                int sheetRowCount = sheetSource.UsedRange.Rows.Count;
                Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
                range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
                _currentRowCount += range.Rows.Count;
            }
    
            void Save()
            {
                bookDest.Saved = true;
                bookDest.SaveCopyAs(_destFile);
            }
    
            void Quit()
            {
                app.Quit();
            }
            void DoMerge()
            {
    
                bool b = false;
                foreach (string strFile in _sourceFiles)
                {
                    OpenBook(strFile);
                    if (b == false)
                    {
                        CopyHeader();
                        b = true;
                    }
                    CopyData();
                    CloseBook();
                }
                Save();
                Quit();
            }
            public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
            {
                new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
            }
        }
    }

    but the problems is, on this code, a hardcode was added, cause this is just sample.

    in this part:

    MergeExcel.DoMerge(new string[]
               {
                    @"C:\Users\Administrator\Desktop\demo1.xlsx",
                    @"C:\Users\Administrator\Desktop\demo2.xlsx"
               },
                   @"C:\Users\Administrator\Desktop\demo3.xlsx", "E", 2);
    

    so, i have modified the code because in my case, I have more then just 10 excel files on each folder.

    so I'm added:

    DirectoryInfo f = new DirectoryInfo(extractDirPath);
    
                            foreach (string d in Directory.GetDirectories(extractDirPath))
                            {
                               
                                DirectoryInfo sf = new DirectoryInfo(d);
                                string[] entries = Directory.GetFileSystemEntries(d, "*.xlsx", SearchOption.AllDirectories);
                                string result = string.Join(",", entries.Select(s => "@" + "\"" + s + "\""));
                                //File.AppendAllText(Path.Combine(extractDirPath, "list1.txt"), result + Environment.NewLine);
                                string mergeFiles = result + Environment.NewLine;
                                Form1.MergeExcel.DoMerge(new string[]
                                    {
                                        mergeFiles
                                    }, @"D:\\testing\un\demo.xlsx", "E", 2);
    
                                
                            }

    instead to replace the Deepak code.

    but, it's not working, because when I try to running the code, error was occurred:

    Additional information: Sorry, we couldn't find @"D:\testing\un\1\1\1.xlsx",@"D:\testing\un\1\1\2.xlsx"

    can anyone help me how to handle this?

    I will appreciated.

    Thank You,

    Bambang

    Monday, April 4, 2016 7:39 AM

Answers

  • Hi Deepak,

    heres my whole code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    using System.IO.Compression;
    using System.Collections;
    using System.Diagnostics;
    using System.Threading;
    using Office = Microsoft.Office.Core;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
    
    namespace simpleApps
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            public void button1_Click(object sender, EventArgs e)
            {
                FolderBrowserDialog fbd = new FolderBrowserDialog();
                DialogResult result = fbd.ShowDialog();
    
                string[] files = Directory.GetFiles(fbd.SelectedPath);
                textBox1.Text = fbd.SelectedPath.ToString(); 
    
                
    
            }
    
            public void button2_Click(object sender, EventArgs e)
            {
                FolderBrowserDialog fbd = new FolderBrowserDialog();
                DialogResult result = fbd.ShowDialog();
    
                string[] files = Directory.GetFiles(fbd.SelectedPath);
                textBox2.Text = fbd.SelectedPath.ToString();
                
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                string sourceDirSelected = @"" + textBox1.Text;
                string desDirSelected = @"" + textBox2.Text;
                string itung = "none";
                int count = 1;
                if (radioButton1.Checked == true & radioButton2.Checked == false)
                {
                    //MessageBox.Show("ok");
    
                    //string sourceDirSelected = @"" + textBox1.Text;
                    //string desDirSelected = @"" + textBox2.Text;
                    //string itung = "none";
                    //int count = 1;
                    // this code for access selected directory (source directory)
                    // and return as list all the files with specific extension (*.zip) on item variable
                    DirectoryInfo di = new DirectoryInfo(sourceDirSelected);
                    foreach (var item in di.GetFiles("*.zip"))
                    {
                        using (FileStream zip2Open = new FileStream(item.FullName, FileMode.Open))
                        {
                            string destDirCreated = @"" + desDirSelected + "\\" + Path.GetFileNameWithoutExtension(item.FullName);
                            if (Directory.Exists(destDirCreated))
                            {
                                MessageBox.Show("Directory " + "-'" + Path.GetFileNameWithoutExtension(item.Name) + "'-" + " already exist, can not proceed the process!");
                                continue;
                            }
                            else
                            {
                                using (ZipArchive archive = new ZipArchive(zip2Open, ZipArchiveMode.Update))
                                {
                                    TopAgain:
                                    foreach (ZipArchiveEntry entry in archive.Entries)
                                    {
                                        //validation and remove file GNPS0001.check
                                        if (entry.Name.Equals("GNPS0001.check"))
                                        {
                                            entry.Delete();
                                            goto TopAgain;
                                        }
                                        string fileAda = Path.GetFileName((entry.FullName).Substring(10, 6));
                                        DirectoryInfo dirInfo = Directory.CreateDirectory(destDirCreated);
                                        if (File.Exists(Path.Combine(destDirCreated, (Path.GetFileName(entry.FullName).Substring(10, 6)) + ".txt")))
                                        {
                                            entry.ExtractToFile(Path.Combine(destDirCreated, (Path.GetFileName(string.Format("{0}({1})", itung, count++)))) + ".txt");
                                        }
                                        else
                                        {
                                            entry.ExtractToFile(Path.Combine(destDirCreated, (Path.GetFileName((entry.FullName).Substring(10, 6)) + ".txt")));
                                            itung = fileAda;
    
                                        }
                                    }
                                    count = 1;
                                }
                                //create awk file
                                string awk = "BEGIN { FIELDWIDTHS = " +
                                   "\"8 1 3 1 1 5 8 6 1 8 12 1 5 2 1 1 1 14 40 40 9 3 9 1 1 5 9 2 9 9 1 9 9 14 9 2 2 2 2 5 19 9 9 2 4 4 1 2 9 1 1 5 1 9 20 4 1 1 16 16 12 1 9 1 7 9 9 1 7 1 1 3 9 1 20 20 10 20 20 8 1 4 1 1 3 9 4 1 1 3 9 4 1 1 3 9 7 7 7 7 7 7 7 7 7 7 323\"" +
                                   "\r\n" + "OFS = " + "\",\"" +
                                   "\r\n" + "}" +
                                   "\r\n" + "$3~/D10/ {" +
                                   "\r\n" + "if($14!=60){" +
                                   "\r\n" + "print $1,$3,$5,$6,$7,$8,$9,$10,$12,$14,$15,$16,$17,$18,$21,$22,$23,$48,$52,$66,$67,$82,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95" +
                                   "\r\n" + "}" +
                                   "\r\n" + "}";
                                System.IO.File.WriteAllText(desDirSelected + "\\Command.awk", awk);
                                //create cmd file
                                string cmd = "@echo off" +
                                   "\r\n" + "echo " + "\"%time%\"" +
                                   "\r\n" + "pushd " + "\"%~dp0\"" +
                                   "\r\n" + "for /r %%I in (*.txt) do C:\\Progra~2\\GnuWin32\\bin\\awk -f Command.awk %%~fI >> test_1Week.csv" +
                                   "\r\n" + "echo " + "\"%time%\"";
                                System.IO.File.WriteAllText(desDirSelected + "\\Trigger.cmd", cmd);
                            }
                        }
                    }
                    Process proc = null;
                    try
                    {
                        string batDir = string.Format(@"" + desDirSelected + "\\");
                        proc = new Process();
                        proc.StartInfo.WorkingDirectory = batDir;
                        proc.StartInfo.FileName = "Trigger.cmd";
                        proc.StartInfo.CreateNoWindow = false;
                        proc.Start();
                        proc.WaitForExit();
                        
                    }
                    catch (Exception ex)
                    {
                        //MessageBox.Show(ex.StackTrace.ToString());
                        
                    }
    
                    File.Delete(desDirSelected + "\\Command.awk");
                    File.Delete(desDirSelected + "\\Trigger.cmd");
                    string[] subdirectoryEntries = Directory.GetDirectories(desDirSelected);
                    DirectoryInfo fol = new DirectoryInfo(desDirSelected);
                    foreach (DirectoryInfo dir in fol.GetDirectories())
                    {
                        dir.Delete(true);
                    }
                    MessageBox.Show("All the process was done");
                    //Application.Exit();
                }
                //if condition was select excel as sources
                else if (radioButton1.Checked == false & radioButton2.Checked == true)
                {
                    //MessageBox.Show("You are select *.xlsx as source, select directory");
                    //string sourceZipFiles = @"D:\testing";
    
                    DirectoryInfo di = new DirectoryInfo(sourceDirSelected);
                    foreach (var item in di.GetFiles("*.zip"))
                    {
    
                        string zipFilesPath = Path.GetFullPath(item.FullName);
                        string extractDirPath = @"" + desDirSelected + "\\" + Path.GetFileNameWithoutExtension(item.FullName);
                        if (Directory.Exists(extractDirPath))
                        {
                            MessageBox.Show("Directory " + "-'" + Path.GetFileNameWithoutExtension(item.Name) + "'-" + " already exist, can not proceed the process!");
                            continue;
                        }
                        else
                        {
                            ZipFile.ExtractToDirectory(zipFilesPath, extractDirPath);
                            DirectoryInfo f = new DirectoryInfo(extractDirPath);
    
                            foreach (string d in Directory.GetDirectories(extractDirPath))
                            {
                               
                                DirectoryInfo sf = new DirectoryInfo(d);
                                string[] entries = Directory.GetFileSystemEntries(d, "*.xlsx", SearchOption.AllDirectories);
                                Form1.MergeExcel.DoMerge(entries, extractDirPath + "\\merge.xlsx", "Q", 8);
                                if (Directory.Exists(sourceDirSelected + "\\mergeFilesFolder\\"))
                                {
                                    MessageBox.Show("Directory exists.");
                                    if (File.Exists(desDirSelected + "\\mergeFilesFolder\\" + "merge.xlsx"))
                                    {
                                        MessageBox.Show("file exist");
                                    }
                                    else
                                    {
                                        MessageBox.Show("file doesn't exist");
                                    }
                                }
                                else
                                {
                                    MessageBox.Show("Directory does not exist.");
                                    System.IO.Directory.CreateDirectory(sourceDirSelected + "\\mergeFilesFolder\\");
    
                                }
    
                            } 
                            
    
                        }
                        //int hitung = 1;
                        //string fileNameOnly = Path.GetFileNameWithoutExtension(extractDirPath + "\\merge.xlsx");
                        //string extension = Path.GetExtension(extractDirPath + "\\merge.xlsx");
                        //string path = Path.GetDirectoryName(extractDirPath + "\\merge.xlsx");
                        //string newFullPath = extractDirPath + "\\merge.xlsx";
    
                        //while (File.Exists(newFullPath))
                        //{
                        //    string tempFileName = string.Format("{0}({1})", fileNameOnly, hitung++);
                        //    newFullPath = Path.Combine(path, tempFileName + extension);
                        //}
                        //System.IO.File.Move(extractDirPath + "\\merge.xlsx", desDirSelected + "\\" + "MergesFilesFolder")
                        //File.Delete(extractDirPath);
    
                    }
                }
                else
                {
                    MessageBox.Show("You doesn't choose Zip Files or Excel Files as source");
                }
                System.Windows.Forms.Application.Exit();
                System.Environment.Exit(1);
            }
            
    
            public class MergeExcel
            {
                Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    
                Excel.Workbook bookDest = null;
                Excel.Worksheet sheetDest = null;
    
                Excel.Workbook bookSource = null;
                Excel.Worksheet sheetSource = null;
                string[] _sourceFiles = null;
                string _destFile = string.Empty;
                string _columnEnd = string.Empty;
                int _headerRowCount = 0;
                int _currentRowCount = 0;
                public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
                {
    
                    bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
                    sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
                    sheetDest.Name = "Merge";
                    _sourceFiles = sourceFiles;
                    _destFile = destFile;
                    _columnEnd = columnEnd;
                    _headerRowCount = headerRowCount;
                }
    
                void OpenBook(string fileName)
                {
                    bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
                }
    
                void CloseBook()
                {
                    bookSource.Close(false, Missing.Value, Missing.Value);
                }
    
                void CopyHeader()
                {
                    Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
                    range.Copy(sheetDest.get_Range("A1", Missing.Value));
                    _currentRowCount += _headerRowCount;
                }
    
                void CopyData()
                {
                    int sheetRowCount = sheetSource.UsedRange.Rows.Count;
                    Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
                    range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
                    _currentRowCount += range.Rows.Count;
                }
    
                void Save()
                {
                    bookDest.Saved = true;
                    bookDest.SaveCopyAs(_destFile);
                }
    
                void Quit()
                {
                    app.Quit();
                }
                void DoMerge()
                {
    
                    bool b = false;
                    foreach (string strFile in _sourceFiles)
                    {
                        OpenBook(strFile);
                        if (b == false)
                        {
                            CopyHeader();
                            b = true;
                        }
                        CopyData();
                        CloseBook();
                    }
                    Save();
                    Quit();
                }
                public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
                {
                    new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
                }
    
    
    
                
            }
    
            
    
            private void radioButton1_CheckedChanged(object sender, EventArgs e)
            {
    
            }
        }
    }
    

    in this code not only merge, but also unzip files. and do the filtering and running a cmd script.

    Best Regards,

    Bambang

    Monday, April 4, 2016 12:03 PM

All replies

  • Hi, Bambang Priantoro

    >>Can you able to provide whole code I want to make a test with it?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 4, 2016 9:25 AM
    Moderator
  • Hi, Bambang Priantoro

    At first view of your code I find that you are using variable to store file name. In my last suggestion there is an array to store filenames.

    if all the file names in one variable then it will not able to search the file.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 4, 2016 9:29 AM
    Moderator
  • Hi Deepak,

    Thank you.

    the code was successfully running as I expected.

    Best Regards,

    Bambang

    Monday, April 4, 2016 10:08 AM
  • Hi Deepak,

    heres my whole code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    using System.IO.Compression;
    using System.Collections;
    using System.Diagnostics;
    using System.Threading;
    using Office = Microsoft.Office.Core;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
    
    namespace simpleApps
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            public void button1_Click(object sender, EventArgs e)
            {
                FolderBrowserDialog fbd = new FolderBrowserDialog();
                DialogResult result = fbd.ShowDialog();
    
                string[] files = Directory.GetFiles(fbd.SelectedPath);
                textBox1.Text = fbd.SelectedPath.ToString(); 
    
                
    
            }
    
            public void button2_Click(object sender, EventArgs e)
            {
                FolderBrowserDialog fbd = new FolderBrowserDialog();
                DialogResult result = fbd.ShowDialog();
    
                string[] files = Directory.GetFiles(fbd.SelectedPath);
                textBox2.Text = fbd.SelectedPath.ToString();
                
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                string sourceDirSelected = @"" + textBox1.Text;
                string desDirSelected = @"" + textBox2.Text;
                string itung = "none";
                int count = 1;
                if (radioButton1.Checked == true & radioButton2.Checked == false)
                {
                    //MessageBox.Show("ok");
    
                    //string sourceDirSelected = @"" + textBox1.Text;
                    //string desDirSelected = @"" + textBox2.Text;
                    //string itung = "none";
                    //int count = 1;
                    // this code for access selected directory (source directory)
                    // and return as list all the files with specific extension (*.zip) on item variable
                    DirectoryInfo di = new DirectoryInfo(sourceDirSelected);
                    foreach (var item in di.GetFiles("*.zip"))
                    {
                        using (FileStream zip2Open = new FileStream(item.FullName, FileMode.Open))
                        {
                            string destDirCreated = @"" + desDirSelected + "\\" + Path.GetFileNameWithoutExtension(item.FullName);
                            if (Directory.Exists(destDirCreated))
                            {
                                MessageBox.Show("Directory " + "-'" + Path.GetFileNameWithoutExtension(item.Name) + "'-" + " already exist, can not proceed the process!");
                                continue;
                            }
                            else
                            {
                                using (ZipArchive archive = new ZipArchive(zip2Open, ZipArchiveMode.Update))
                                {
                                    TopAgain:
                                    foreach (ZipArchiveEntry entry in archive.Entries)
                                    {
                                        //validation and remove file GNPS0001.check
                                        if (entry.Name.Equals("GNPS0001.check"))
                                        {
                                            entry.Delete();
                                            goto TopAgain;
                                        }
                                        string fileAda = Path.GetFileName((entry.FullName).Substring(10, 6));
                                        DirectoryInfo dirInfo = Directory.CreateDirectory(destDirCreated);
                                        if (File.Exists(Path.Combine(destDirCreated, (Path.GetFileName(entry.FullName).Substring(10, 6)) + ".txt")))
                                        {
                                            entry.ExtractToFile(Path.Combine(destDirCreated, (Path.GetFileName(string.Format("{0}({1})", itung, count++)))) + ".txt");
                                        }
                                        else
                                        {
                                            entry.ExtractToFile(Path.Combine(destDirCreated, (Path.GetFileName((entry.FullName).Substring(10, 6)) + ".txt")));
                                            itung = fileAda;
    
                                        }
                                    }
                                    count = 1;
                                }
                                //create awk file
                                string awk = "BEGIN { FIELDWIDTHS = " +
                                   "\"8 1 3 1 1 5 8 6 1 8 12 1 5 2 1 1 1 14 40 40 9 3 9 1 1 5 9 2 9 9 1 9 9 14 9 2 2 2 2 5 19 9 9 2 4 4 1 2 9 1 1 5 1 9 20 4 1 1 16 16 12 1 9 1 7 9 9 1 7 1 1 3 9 1 20 20 10 20 20 8 1 4 1 1 3 9 4 1 1 3 9 4 1 1 3 9 7 7 7 7 7 7 7 7 7 7 323\"" +
                                   "\r\n" + "OFS = " + "\",\"" +
                                   "\r\n" + "}" +
                                   "\r\n" + "$3~/D10/ {" +
                                   "\r\n" + "if($14!=60){" +
                                   "\r\n" + "print $1,$3,$5,$6,$7,$8,$9,$10,$12,$14,$15,$16,$17,$18,$21,$22,$23,$48,$52,$66,$67,$82,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95" +
                                   "\r\n" + "}" +
                                   "\r\n" + "}";
                                System.IO.File.WriteAllText(desDirSelected + "\\Command.awk", awk);
                                //create cmd file
                                string cmd = "@echo off" +
                                   "\r\n" + "echo " + "\"%time%\"" +
                                   "\r\n" + "pushd " + "\"%~dp0\"" +
                                   "\r\n" + "for /r %%I in (*.txt) do C:\\Progra~2\\GnuWin32\\bin\\awk -f Command.awk %%~fI >> test_1Week.csv" +
                                   "\r\n" + "echo " + "\"%time%\"";
                                System.IO.File.WriteAllText(desDirSelected + "\\Trigger.cmd", cmd);
                            }
                        }
                    }
                    Process proc = null;
                    try
                    {
                        string batDir = string.Format(@"" + desDirSelected + "\\");
                        proc = new Process();
                        proc.StartInfo.WorkingDirectory = batDir;
                        proc.StartInfo.FileName = "Trigger.cmd";
                        proc.StartInfo.CreateNoWindow = false;
                        proc.Start();
                        proc.WaitForExit();
                        
                    }
                    catch (Exception ex)
                    {
                        //MessageBox.Show(ex.StackTrace.ToString());
                        
                    }
    
                    File.Delete(desDirSelected + "\\Command.awk");
                    File.Delete(desDirSelected + "\\Trigger.cmd");
                    string[] subdirectoryEntries = Directory.GetDirectories(desDirSelected);
                    DirectoryInfo fol = new DirectoryInfo(desDirSelected);
                    foreach (DirectoryInfo dir in fol.GetDirectories())
                    {
                        dir.Delete(true);
                    }
                    MessageBox.Show("All the process was done");
                    //Application.Exit();
                }
                //if condition was select excel as sources
                else if (radioButton1.Checked == false & radioButton2.Checked == true)
                {
                    //MessageBox.Show("You are select *.xlsx as source, select directory");
                    //string sourceZipFiles = @"D:\testing";
    
                    DirectoryInfo di = new DirectoryInfo(sourceDirSelected);
                    foreach (var item in di.GetFiles("*.zip"))
                    {
    
                        string zipFilesPath = Path.GetFullPath(item.FullName);
                        string extractDirPath = @"" + desDirSelected + "\\" + Path.GetFileNameWithoutExtension(item.FullName);
                        if (Directory.Exists(extractDirPath))
                        {
                            MessageBox.Show("Directory " + "-'" + Path.GetFileNameWithoutExtension(item.Name) + "'-" + " already exist, can not proceed the process!");
                            continue;
                        }
                        else
                        {
                            ZipFile.ExtractToDirectory(zipFilesPath, extractDirPath);
                            DirectoryInfo f = new DirectoryInfo(extractDirPath);
    
                            foreach (string d in Directory.GetDirectories(extractDirPath))
                            {
                               
                                DirectoryInfo sf = new DirectoryInfo(d);
                                string[] entries = Directory.GetFileSystemEntries(d, "*.xlsx", SearchOption.AllDirectories);
                                Form1.MergeExcel.DoMerge(entries, extractDirPath + "\\merge.xlsx", "Q", 8);
                                if (Directory.Exists(sourceDirSelected + "\\mergeFilesFolder\\"))
                                {
                                    MessageBox.Show("Directory exists.");
                                    if (File.Exists(desDirSelected + "\\mergeFilesFolder\\" + "merge.xlsx"))
                                    {
                                        MessageBox.Show("file exist");
                                    }
                                    else
                                    {
                                        MessageBox.Show("file doesn't exist");
                                    }
                                }
                                else
                                {
                                    MessageBox.Show("Directory does not exist.");
                                    System.IO.Directory.CreateDirectory(sourceDirSelected + "\\mergeFilesFolder\\");
    
                                }
    
                            } 
                            
    
                        }
                        //int hitung = 1;
                        //string fileNameOnly = Path.GetFileNameWithoutExtension(extractDirPath + "\\merge.xlsx");
                        //string extension = Path.GetExtension(extractDirPath + "\\merge.xlsx");
                        //string path = Path.GetDirectoryName(extractDirPath + "\\merge.xlsx");
                        //string newFullPath = extractDirPath + "\\merge.xlsx";
    
                        //while (File.Exists(newFullPath))
                        //{
                        //    string tempFileName = string.Format("{0}({1})", fileNameOnly, hitung++);
                        //    newFullPath = Path.Combine(path, tempFileName + extension);
                        //}
                        //System.IO.File.Move(extractDirPath + "\\merge.xlsx", desDirSelected + "\\" + "MergesFilesFolder")
                        //File.Delete(extractDirPath);
    
                    }
                }
                else
                {
                    MessageBox.Show("You doesn't choose Zip Files or Excel Files as source");
                }
                System.Windows.Forms.Application.Exit();
                System.Environment.Exit(1);
            }
            
    
            public class MergeExcel
            {
                Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    
                Excel.Workbook bookDest = null;
                Excel.Worksheet sheetDest = null;
    
                Excel.Workbook bookSource = null;
                Excel.Worksheet sheetSource = null;
                string[] _sourceFiles = null;
                string _destFile = string.Empty;
                string _columnEnd = string.Empty;
                int _headerRowCount = 0;
                int _currentRowCount = 0;
                public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
                {
    
                    bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
                    sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
                    sheetDest.Name = "Merge";
                    _sourceFiles = sourceFiles;
                    _destFile = destFile;
                    _columnEnd = columnEnd;
                    _headerRowCount = headerRowCount;
                }
    
                void OpenBook(string fileName)
                {
                    bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
                }
    
                void CloseBook()
                {
                    bookSource.Close(false, Missing.Value, Missing.Value);
                }
    
                void CopyHeader()
                {
                    Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
                    range.Copy(sheetDest.get_Range("A1", Missing.Value));
                    _currentRowCount += _headerRowCount;
                }
    
                void CopyData()
                {
                    int sheetRowCount = sheetSource.UsedRange.Rows.Count;
                    Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
                    range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
                    _currentRowCount += range.Rows.Count;
                }
    
                void Save()
                {
                    bookDest.Saved = true;
                    bookDest.SaveCopyAs(_destFile);
                }
    
                void Quit()
                {
                    app.Quit();
                }
                void DoMerge()
                {
    
                    bool b = false;
                    foreach (string strFile in _sourceFiles)
                    {
                        OpenBook(strFile);
                        if (b == false)
                        {
                            CopyHeader();
                            b = true;
                        }
                        CopyData();
                        CloseBook();
                    }
                    Save();
                    Quit();
                }
                public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
                {
                    new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
                }
    
    
    
                
            }
    
            
    
            private void radioButton1_CheckedChanged(object sender, EventArgs e)
            {
    
            }
        }
    }
    

    in this code not only merge, but also unzip files. and do the filtering and running a cmd script.

    Best Regards,

    Bambang

    Monday, April 4, 2016 12:03 PM
  • Hi, Bambang Priantoro

    >>it is glad to know that your issue is solved and now your code is working correctly. Thank you for sharing your code that might help other community members that are facing the same issue like you.

    >>I would like to suggest you to click on "Mark as Answer" on code posted by you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 5, 2016 2:16 AM
    Moderator
  • Hi Deepak,

    Thank you for your help.

    Cheers,

    Bambang

    Tuesday, April 5, 2016 2:43 AM
  • In addition to excel interop, you can also try this solution without ms office, check following code snippet:

    Workbook newbook = new Workbook();
    newbook.Version = ExcelVersion.Version2010;
    newbook.Worksheets.Clear();
    Workbook tempbook = new Workbook();
    string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
    for (int i = 0; i < excelFiles.Length; i++)
    {
        tempbook.LoadFromFile(excelFiles[i]);
        foreach (Worksheet sheet in tempbook.Worksheets)
        {
            newbook.Worksheets.AddCopy(sheet);
        }
    }
    newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
    Check further information for how to merge excel files into one.


    Tuesday, April 5, 2016 8:34 AM
  • Hi Deepak,

    could you help me again, if I want the outout was *.csv file not *.xlsx, could you help me how to do that?

    i'll appreciate much.

    Thank you,

    Bambang

    Tuesday, April 5, 2016 11:02 AM
  • Hi, Bambang Priantoro

    >>Do you mean you want to merge your data and save in to .csv file instead of .xlsx file?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 7, 2016 9:43 AM
    Moderator
  • Hi Deepak,

    Yes, that right.

    Best Regards,

    Bambang

    Thursday, April 7, 2016 9:54 AM
  • Hi, Bambang Priantoro

    >> if you have my old posted code then you just need to make new excel file and save it as .csv format then change the destination file name to this new created .csv file then your data will be save in new .csv file.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 7, 2016 9:57 AM
    Moderator