Merge two xlsx files into one using Asp.Net with C#

Question

User1324761900 posted

Hi Guys,

I like to merge/combine two/multiple .xlsx files into one .xls file using ASP.Net Application, I don't want c# console application. 

Please share the code. 

I already have console Application code, but i like to import input file using FileUpload in Asp.net.

 class Program
    {
        static void Main(string[] args)
        {
            MergeExcel.DoMerge(new string[]   
            {  
                @"C:\Users\vvijayak\test1.xlsx",   
                @"C:\Users\vvijayak\test2.xlsx"   
            },
                @"C:\Users\vvijayak\result.xlsx", "J", 2);  
        }
    }

    public class MergeExcel
    {
        Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        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.Workbook)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();
        }
    }

Answer 1

User-219423983 posted

Hi vinovasu,

You could first save the FileUpload files in your serer by using FileUpload.SaveAs Method.

https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.fileupload.saveas(v=vs.110).aspx

Then you could use your code above to generate a new excel in your server. If you want to send the result file to users, you could refer to the following code to achieve it. 

           Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=a.xlsx");
            string filename = (Server.MapPath("~/Uploads/") + "result.xlsx");
            Response.TransmitFile(filename);
            Response.End();

Besides, I suggest you could first create a new folder to save your download files and the result files in your project. In your code above, you save the file to “C:\Users\vvijayak\” and it would work when you run it as administrator. However, when you use the same code in IIS, maybe it doesn’t has the permission to save the files to “C:\Users\vvijayak\”, so saving the files to a folder under the same application would be better than saving to “C:\Users\vvijayak\”.

I hope it’s useful to you.

Best Regards,

Weibo Zhang