none
Read active range cell that has value using visual studio 2015 RRS feed

  • Question

  • Hi All,

    I have this excel files like this :

    and these,

    both files has header, and start on the exact cell. and have same sheet (both was on sheet 1)

    it start from A4, and ended on cell C, my question is, how I can merge those 2 excel file, and read cell that have value, example on first excel, it start from A4:C6 and next files was start from A4:C7.

    im not using VBA or macro, im using visual studio 2015, with C#. for  these need, im using winform. 

    My Goal was create new excel file, such as merge.xlsx, contain the merge result from those 2 files in one sheet.

    Im using MS Office Excel 2013.

    does anyone can help me?

    Thank you.

    Best Regards,

    Bambang

    Friday, April 1, 2016 3:16 AM

Answers

  • Hi, BambangHere I want to suggest you that if your purpose is to merge worksheet then no need to use winform. You can also merge worksheets by creating Console Application. Here I have posted a code that working as per your requirement. kindly check it.

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

    Please set the reference property first like below otherwise you will get error.

    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.

    Friday, April 1, 2016 8:22 AM
    Moderator

All replies

  • Hi, BambangHere I want to suggest you that if your purpose is to merge worksheet then no need to use winform. You can also merge worksheets by creating Console Application. Here I have posted a code that working as per your requirement. kindly check it.

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

    Please set the reference property first like below otherwise you will get error.

    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.

    Friday, April 1, 2016 8:22 AM
    Moderator
  • Hi Deepak,

    I'm sorry, the code was great!

    it solved my problem. :D

    thank you very much for your help.

    Best Regards,

    Bambang

    Friday, April 1, 2016 9:13 AM
  • Hi Deepak,

    Thank you for your help earlier, but I have some problem when I tried to modify your code.

    at your code there's a pieces of code like this:

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

    and I tried to modifiy it because it that example of yours, it has 2 excel file, so in my casehow about if there's many excel files? so I tried to combine it, here's my modification:

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

    so basically I want to list all the excel files in one folder and add a separator "," and try to combine all of the files, but when I tried to run it, it gave me an error.

    the error was said :

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

    when I check to that path, all the files was right there, the are exist.

    can you help me, Deepak?

    Thank You,

    Best Regards,

    Bambang

    Monday, April 4, 2016 7:24 AM
  • Hi, Bambang Priantoro

    >> It has 2 excel file, so in my case how about if there's many excel files?

    I have checked that the code suggested by me. it can able to merge number of excel
    files. it is not limited to only 2 files.

    >>Here I think you can check first by passing the file names manually instead of
    passing by your code. if it will work then you can check your code to find
    the problem.

    Regards

    Deepak



    Monday, April 4, 2016 8:10 AM
    Moderator
  • Hi Deepak,

    your code working just fine, it awesome solution.

    but, can you  help me how to add more then just several excel files into that code?

    I have tried add:

    var mergeFiles, but it seems not working properly.

    did i do something wrong?

    Best Regards,

    Bambang

    Monday, April 4, 2016 8:18 AM
  • Hi Deepak,

    could you explain to me, if I want to copy only the data.

    example(from pic above):

    the header that starts from A4:C4 was not include when it merge. could you show me the code?

    Thank You,

    Bambang 

    Tuesday, April 5, 2016 10:35 AM
  • Hi, Bambang Priantoro

    in the above code provided by be please check the Copydata() and copyheader().

    you will understand after see the code that header only taken from the first file and in second time it will not copy header.

    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 12, 2016 9:46 AM
    Moderator