Geek Answers Handbook

How to merge 2 excel files into one excel file with split sheets?

I have 2 Excel files and I want to merge them into 1 file with separate sheets.

I am trying to merge with Microsoft.Office.Interop.Excel, but I do not understand how to use this?

for Yahia:

here are the methods for getting the range that I want to combine with another file:

internal object[,] GetValues(string filename) { object[,] values = new object[0, 0]; try { Workbook workBook = _excelApp.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); values = ExcelScanIntenal(workBook); workBook.Close(false, filename, null); Marshal.ReleaseComObject(workBook); } catch { } return values; } private object[,] ExcelScanIntenal(Workbook workBookIn) { object[,] valueArray = new object[0, 0]; Worksheet sheet = (Worksheet)workBookIn.Sheets[1]; Range excelRange = sheet.UsedRange; valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault); return valueArray; }

and here I want to combine the values:

  internal void AddWorksheetToExcelWorkbook(string filename, string worksheetName, object[,] valueArray) { Microsoft.Office.Interop.Excel.Application xlApp = null; Workbook xlWorkbook = null; Sheets xlSheets = null; Worksheet xlNewSheet = null; try { xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; xlWorkbook = xlApp.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); xlSheets = xlWorkbook.Sheets as Sheets; xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlNewSheet.Name = worksheetName; xlWorkbook.Save(); xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); } finally { Marshal.ReleaseComObject(xlNewSheet); Marshal.ReleaseComObject(xlSheets); Marshal.ReleaseComObject(xlWorkbook); Marshal.ReleaseComObject(xlApp); xlApp = null; } }

the problem is that xlNewSheet does not have any property that can get values. How can i add?

+1
c # excel
source share
1 answer

Not quite sure what the question is ... but for merging excel files programmatically see the starting point:

IF you need more information, then show some code and let it know that it doesn’t work ...

EDIT - as per the comment:

You can use xlNewSheet.Cells to get the range and use its Columns / Rows / Item properties to change / add whatever values ​​you want, i.e. in your case from valueArray ...

+1
source share

More articles:


All Articles