Link to home
Create AccountLog in
Avatar of rwheeler23
rwheeler23Flag 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);

        }
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

            xlWorkSheetDetail = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheetDist = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


Both are the same worksheet - hence the overwriting of cells headings.
I guess one of those should be get_Item(2)

            xlWorkSheetAAAAAA = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheetBBBBBB = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);

Avatar of rwheeler23

ASKER

What do you think of this? This works and then I have another method that fills the two worksheets with data using the global variables in Model. What is the purpose of misValue?

        public static Boolean CreateExcelSpreadsheet()
        {
            object misValue = System.Reflection.Missing.Value;

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

            xlApp.Visible = false;

            /* Create the new workbook */
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
            /* Create and populate columns headings for distribution worksheet */
            Excel.Worksheet xlWorkSheetDist = xlWorkBook.ActiveSheet  as Excel.Worksheet;
            xlWorkSheetDist.Name = "Distributions";
            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";

            /* Create and populate columns headings for detail worksheet */
            Excel.Worksheet xlWorkSheetDetail = xlWorkBook.Sheets.Add(misValue, misValue, 1, misValue) as Excel.Worksheet;
            xlWorkSheetDetail.Name = "Detail";
            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";

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

            return (true);

        }
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer