How to create Excel Sheets from a List

Question

Greetings all,

I am currently working on a program that interfaces with my Access DB.
The program is a front-end GUI, to retrieve information from the DB via SQL queries.

What I am attempting:
A user clicks a button, which will then generate an inventory list from the DB and dump it all to an Excel file.

What I have so far:
Currently, I have a functional portion of the code that works. I use the button click event to trigger the calls for connecting to the DB, performing the query, creating an Excel book and sheet, and dumping the gathered data to that sheet.

Where I want to go:
I need to generate sheets in the workbook based on a list of names that will be supplied when the user clicks the button.
I am trying to figure out how to generate the sheets, and name them, according to the List that will be passed with the call.

The Code Portions:

private void btnTEST_Click(object sender, EventArgs e)
        {
            var timeStamp = DateTime.Now.ToString("yyyy-MMM-dd");
            string.Format("{0:yyyy-MMM-dd}", timeStamp);
            string categoryName = "Units";
            string modelName = "Bolt-Gun";
            string typeName = "Source";
            string q_SOURCE = @"SELECT tblOwnedInventory.Qty, tblOwnedInventory.Description, tblOwnedInventory.SerialNumber, tblOwnedInventory.PartNumber, tblModel.Model, tblCategory.CategoryName, tblEquipmentType.Type, tblStatus.Status, tblOwnedInventory.Notes
                                FROM tblModel INNER JOIN(tblCategory INNER JOIN(tblEquipmentType INNER JOIN(tblStatus INNER JOIN tblOwnedInventory ON tblStatus.StatusID = tblOwnedInventory.Status) ON tblEquipmentType.TypeID = tblOwnedInventory.Type) ON tblCategory.CategoryID = tblOwnedInventory.Category) ON tblModel.ModelID = tblOwnedInventory.Model
                                WHERE(((tblOwnedInventory.Qty) > 0) AND((tblModel.Model) = @modelName) AND((tblCategory.CategoryName) = @categoryName) AND((tblEquipmentType.Type) = @typeName))
                                ORDER BY tblOwnedInventory.PartNumber; ";
            connection.Open();
            DataTable dt = new DataTable();         // create a DataTable that can be passed to TestingClass
            OleDbCommand command = new OleDbCommand(q_SOURCE, connection);      // create a Command to process the sql command, and pass to TestingClass
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);       // create a DataAdapter that can be passed to TestingClass
            command.Parameters.AddWithValue("@modelName", SqlDbType.VarChar);   // Assign the command variables to the sql string
            command.Parameters["@modelName"].Value = modelName;
            command.Parameters.AddWithValue("@categoryName", SqlDbType.VarChar);
            command.Parameters["@categoryName"].Value = categoryName;
            command.Parameters.AddWithValue("@typeName", SqlDbType.VarChar);
            command.Parameters["@typeName"].Value = typeName;
            Excel.Application Excel = new Excel.Application();                  // create an instance of Excel Application
            Excel.Workbook wBook = Excel.Workbooks.Add();                       // create an Excel Workbook

            Excel.Worksheet oSheet = Excel.ActiveSheet;                         // create a sheet and make it active

            //Excel.Worksheet wsGL4K = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsG2Cables = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsG2Parts = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsG1Parts = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsGGun1 = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsAGCables = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsAccessories = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsHardware = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;
            //Excel.Worksheet wsBoltParts = workBook.Sheets.Add(m, m, 1, m) as Excel.Worksheet;

            TestingClass(wBook, oSheet, categoryName, modelName, typeName, q_SOURCE, dt, dataAdapter);     // pass all of the information to TestingClass to be processed from DB and written to Excel sheet
            oSheet.SaveAs(@"L:\EoM Reports\EoM-Inventory Reports\AUTOGEN\FIR-SOURCE -- " + timeStamp + ".xlsx");    // save the Excel file
            Excel.Quit();           // exit the Excel Application
            connection.Close();     // close the sql connection to the DB
            MessageBox.Show("Report Generated");
        }

public static void TestingClass(Excel.Workbook wBook, Excel.Worksheet oSheet, string category, string model, string type, string sql, DataTable dt, OleDbDataAdapter da)
        {
            object m = Type.Missing;
            object c = wBook.Worksheets[wBook.Worksheets.Count];
            OleDbConnection conn = Connections.openConnection();        // create a connection to the DB
            da.Fill(dt);                                                // fill the DataAdapter with the data from the DataTable obtained by the sql query
            //  loop through the DataTable to get the total number of columns
            for (var i = 0; i < dt.Columns.Count; i++)
            {   // write the column names from the DB table to the Excel Sheet
                oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
            }

            for (var i = 0; i < dt.Rows.Count; i++)         // loop through the data to count the total number of rows containing data
            {
                for (var j = 0; j < dt.Columns.Count; j++)  // loop through the data to count the total number of columns
                {
                    oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];     // write the data from the DB table to the Excel Sheet
                }
            }
            sheetFormatting(oSheet, model, category);
            var TES01 = wBook.Worksheets.Add(m, c, 1, m);   // add a sheet after the last sheet
            TES01.Name = "TEST01";
        }

        public static void sheetFormatting(Excel.Worksheet oSheet, string model, string category)
        {
            Excel.Range totalRange;         // create a variable to store the total range of the sheet & its data
            oSheet.get_Range("A1:I1").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.Cells[1, 1] = "Qty";
            oSheet.Cells[1, 2] = "Description";
            oSheet.Cells[1, 3] = "S/N";
            oSheet.Cells[1, 4] = "P/N";
            oSheet.Cells[1, 5] = "Model";
            oSheet.Cells[1, 6] = "Category";
            oSheet.Cells[1, 7] = "Type";
            oSheet.Cells[1, 8] = "Status";
            oSheet.Cells[1, 9] = "Notes";
            oSheet.get_Range("A1:I1").Font.Bold = true;  // set contents of headers to BOLD
            // column center alignment for range
            oSheet.get_Range("A1:A1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("C1:C1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("D1:D1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("E1:E1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("F1:F1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("G1:G1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            oSheet.get_Range("H1:H1000").Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            Excel.Range RngA = oSheet.Range["A1"];
            Excel.Range RngB = oSheet.Range["A1"].End[Excel.XlDirection.xlToRight].End[Excel.XlDirection.xlDown];
            Excel.Range tableRange = oSheet.Range[RngA, RngB];
            tableRange.Select();
            // format the data into a Table
            tableRange.Worksheet.ListObjects.AddEx(SourceType: Excel.XlListObjectSourceType.xlSrcRange, Source: tableRange, XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
            // force columns to auto-fit contents
            totalRange = oSheet.get_Range("A1:I1000");
            totalRange.Columns.AutoFit();
            Excel.Range firstCell = oSheet.Range["A1"];
            // name the Sheet according to the Model & Category
            oSheet.Name = (model + "_" + category);
            firstCell.Select();
        }

I can add a sheet, by utilizing MSDN's method:
newWorksheet = (Excel.Worksheet)this.Application.Worksheets.Add();

But I need to figure out how to generate the sheets from the list and name them accordingly.

Any help would be greatly appreciated.
Regards.

Answer 1

If open to an alternate solution using a free third party library known as SpreadSheetLight which eliminates many issues with using Interop style coding. Only downside is this works with .xlsx, not .xls.

See the following

public static (bool success, Exception exception) CreateWithSheets(string fileName, List<string> nameList, Dictionary<string, string> headerDictionary)  
{  
    try  
    {  
        using (var document = new SLDocument())  
        {  
            SLStyle headerStyle = document.CreateStyle();  
            headerStyle.Font.Bold = true;  
            headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;  
  
  
            foreach (var name in nameList.Where(name => !SheetExists(document, name)))  
            {  
                document.AddWorksheet(name);  
                document.SelectWorksheet(name);  
  
                foreach (var kvp in headerDictionary)  
                {  
                    document.SetCellValue(kvp.Key, kvp.Value);  
                }  
                 
  
                document.SetRowStyle(1, headerStyle);  
  
            }  
  
            document.DeleteWorksheet("Sheet1");  
            document.SaveAs(fileName);  
  
            return (true, null);  
        }  
    }  
    catch (Exception exception)  
    {  
        return (false, exception);  
    }  
}  

Form code

private void CreateButton_Click(object sender, EventArgs e)  
{  
    string fileName = "Months.xlsx";  
      
    List<string> sheetNameList = Enumerable.Range(1, 12).  
        Select((index) => DateTimeFormatInfo.CurrentInfo.GetMonthName(index)).ToList();  
  
    Dictionary<string, string> headerDictionary = new Dictionary<string, string>()  
    {  
        {"A1","Qty" },  
        {"B1","Description" },  
        {"C1","S/N" },  
        {"D1","P/N" },  
        {"E1","Model" },  
        {"F1","Category" },  
        {"G1","Type" },  
        {"H1","Status" },  
        {"I1","Notes" }  
    };  
      
    var (success, exception) = ExcelHelpers.CreateWithSheets(fileName, sheetNameList, headerDictionary);  
    MessageBox.Show(success ? "Done" : exception.Message);  
}  

The above code does most of the work except for importing a DataTable which can be done using the following and adding a parameter to the above for a DataTable populated from your database

document.ImportDataTable(1,  
    SLConvert.ToColumnIndex("A"), table, true);  

I have not matched all of your formatting yet that is easy using SLStyle (see simple example) and alignment via code this code sample.

See my code samples

Answer 2

But I need to figure out how to generate the sheets from the list and name them accordingly.

Do you mean to produce multiple worksheets?

In fact, there is no clever way, just use loops to do it

        static void MyMethod()  
        {  
            Excel.Application Excel = null;  
            Workbook wb = null;  
            try  
            {  
                Excel = new Excel.Application();  
  
                wb = Excel.Workbooks.Add();  
                var collection = new Worksheet[20];  
  
                for (var i = 19; i >= 0; i--)  
                {  
                    collection[i] = (Worksheet)wb.Worksheets.Add();  
                    collection[i].Name = String.Format("test{0}", i + 1);  
                }  
  
                var thisWorksheet = collection[9];  
                var thisRange = thisWorksheet.Range["A1"];  
                thisRange.Value = "Hello World";  
  
                wb.SaveAs(@"C:\...\2.xlsx");  
            }  
            catch (Exception ex) { Console.WriteLine(ex); }  
            finally  
            {  
                wb.Close();  
                Excel.Quit();  
            }  
        }  

The following code in your sheetFormatting method seems redundant, the TestingClass is used to do this work.

             oSheet.Cells[1, 1] = "Qty";  
             oSheet.Cells[1, 2] = "Description";  
             oSheet.Cells[1, 3] = "S/N";  
             oSheet.Cells[1, 4] = "P/N";  
             oSheet.Cells[1, 5] = "Model";  
             oSheet.Cells[1, 6] = "Category";  
             oSheet.Cells[1, 7] = "Type";  
             oSheet.Cells[1, 8] = "Status";  
             oSheet.Cells[1, 9] = "Notes";  

I'm not sure if I understood what you mean correctly, if not, please let me know.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.