Avatar of rwheeler23
rwheeler23
Flag for United States of America

asked on 

Using C# to create and write to an Excel spreadsheet with multiple worksheets.

I was reusing some C# code that creates and writes to an Excel spreadsheet. In this case the spreadsheet is going to consist of two worksheets. When I execute the code below, which only creates the column headings, the columns headings for the second worksheet overwrites the ones for the first worksheet and the second worksheet is not created.

        public static Boolean CreateExcelSpreadsheet()
        {
            /* Initialize the Excel application object */
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            /* Check whether Excel is installed */
            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed! Please install Excel or attempt a repair.");
                return (false);
            }

            /* Create the new workbook */
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheetDetail;
            Excel.Worksheet xlWorkSheetDist;

            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);

            /* Write content to workbook using column headings */
            xlWorkSheetDetail = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheetDist = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            xlWorkSheetDetail.Cells[1, 1] = "Transaction Type";
            xlWorkSheetDetail.Cells[1, 2] = "Transaction Date";
            xlWorkSheetDetail.Cells[1, 3] = "Chk Nbr/Bldg";
            xlWorkSheetDetail.Cells[1, 4] = "EE Nbr/Dept";
            xlWorkSheetDetail.Cells[1, 5] = "Record ID";
            xlWorkSheetDetail.Cells[1, 6] = "Doc Amount";

            xlWorkSheetDist.Cells[1, 1] = "Transaction Type";
            xlWorkSheetDist.Cells[1, 2] = "Transaction Date";
            xlWorkSheetDist.Cells[1, 3] = "Chk Nbr/Bldg";
            xlWorkSheetDist.Cells[1, 4] = "EE Nbr/Dept";
            xlWorkSheetDist.Cells[1, 5] = "Record ID";
            xlWorkSheetDist.Cells[1, 6] = "Acct Description";
            xlWorkSheetDist.Cells[1, 7] = "GL Account";
            xlWorkSheetDist.Cells[1, 8] = "Debit Amount";
            xlWorkSheetDist.Cells[1, 9] = "Credit Amount";

            Model.xlApp = xlApp;
            Model.xlWorkBook = xlWorkBook;
            Model.xlWorkSheetDetail = xlWorkSheetDetail;
            Model.xlWorkSheetDist = xlWorkSheetDist;
            Model.misValue = misValue;

            return (true);

        }
C#

Avatar of undefined
Last Comment
AndyAinscow

8/22/2022 - Mon