0

I am facing an issue regarding excel writing .Below is the problem - I have a requirement for which I have multiple excel sheets and for each excel file there could be multiple sheets .So, I need to write in a single excel with multiple individual sheets ex- Lets say I have 3 excel file File1.xlsx with Sheet1, File2.xlsx with Sheet2 and File 3 with multiple sheet Sheet3 and Sheet4.In each sheet the data is similar like 3 Columns (Name,EmployeeID,MobNo) So, the output would be Individual file Result.xlsx with multiple sheets Sheet1,Sheet2,Sheet3 & Sheet4.

Please share the code without any third party tools like Spire.xl.I need to do this programatically.Below is the code -

        string inputPathFirstExcel = 
        @"C:\MyData\ExcelWritingApp\File1.xlsx";
        string inputPathSecondExcel = @"C:\\ExcelWritingApp\File2.xlsx";

        string[] ColumnData = { };
        Excel.Application xlApp = null;
        Excel.Workbook xlWorkBook = null;
        Excel.Worksheet xlWorkSheet = null;
        Excel.Range xlrange = null;



        xlApp = new Excel.Application();
        xlApp.Visible = false;
        xlWorkBook = xlApp.Workbooks.Open(inputPathFirstExcel);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
        xlrange = xlWorkSheet.UsedRange;
        object missing = Type.Missing;
        Excel.Application oXL = new Excel.Application();
        oXL.Visible = false;

        Excel.Workbook oWB = oXL.Workbooks.Add(missing);

        Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
            oSheet.Name = "Sheet1";

            Microsoft.Office.Interop.Excel.Range xlRange = xlWorkSheet.UsedRange;
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Employee");
            dt.Columns.Add("MobNo");

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            for (int i = 1; i <= colCount; i++)
            {
                oSheet.Cells[1, i] = dt.Columns[i - 1].ToString().ToUpper() + "\t";
            }

            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {

                    oSheet.Cells[i + 2, j] = Convert.ToString((xlRange.Cells[i + 2, j] as Excel.Range).Value2);
                }
            }

            string fileName = @"C:\Result.xlsx";
            oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                missing, missing, missing, missing,
                Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
Rahul Sharma
  • 87
  • 1
  • 9
  • Does your code not work? If not, then please describe the issues you are facing. – Dan Wilson Apr 20 '19 at 17:50
  • Hi Dan, I am not sure how to implement because my current code just write individual files but I want to write multiple not like repeating same code – Rahul Sharma Apr 20 '19 at 18:25
  • One way to figure out how to do anything with interop is to open Excel, record a macro, do the stuff you're trying to do, and then look at the macro. Macros are recorded as VBA code, and the interop code will mirror it closely. – Scott Hannen Apr 20 '19 at 19:28

0 Answers0