none
Connecting to Excel to insert and update using OLEDB - ACE RRS feed

  • Question

  • I have connected to the excel sheet in c# using the below packages installed in my servers to do exchange the data with MS office products to non-MS office products

    • 2007 Office System Driver: Data Connectivity Components
    • Microsoft Access Database Engine 2016 Redistributable

    Could you please let me know where can i find the updated or latest version of Office System Driver: Data Connectivity Component?


    SomaSundaram R

    Friday, January 12, 2018 11:01 AM

Answers

  • Hello SomaSundaram R,

    2007 Office System Driver: Data Connectivity Components is actually AccessDatabaseEngine 2007 and Microsoft Access Database Engine 2016 Redistributable is actually its latest version.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SomaSundaram R Tuesday, January 16, 2018 4:27 AM
    Monday, January 15, 2018 6:59 AM

All replies

  • Hello,

    I would highly recommend not using OleDb or Excel automation but instead use a Open XML or a library that makes using Open XML easy such as this library, SpreadSheetLight or a paid library Gembox spreadsheet.

    Both libraries do not require Excel to be installed.

    Here is one simple example using SpreadSheetLight

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using SpreadsheetLight;
    
    namespace ConsoleApp
    {
        class Program
        {
            static void Main(string[] args)
            {
                // SpreadsheetLight works on the idea of a currently selected worksheet.
                // If no worksheet name is provided on opening an existing spreadsheet,
                // the first available worksheet is selected.
                SLDocument sl = new SLDocument("ModifyExistingSpreadsheetOriginal.xlsx", "Sheet2");
    
                sl.SetCellValue("E6", "Let's party!!!!111!!!1");
    
                sl.SelectWorksheet("Sheet3");
                sl.SetCellValue("E6", "Before anyone calls the popo!");
    
                sl.AddWorksheet("DanceFloor");
                sl.SetCellValue("B4", "Who let the dogs out?");
                sl.SetCellValue("B5", "Woof!");
    
                sl.SaveAs("ModifyExistingSpreadsheetModified.xlsx");
    
                Console.WriteLine("End of program");
                Console.ReadLine();
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 11:12 AM
  • Thanks for your response.Yeah that is a good idea. But could you please let me know where can i find the updated or latest version of Office System Driver: Data Connectivity Component?


    SomaSundaram R

    Friday, January 12, 2018 11:17 AM
  • You can look at the links in this page yet when I tried to visit them the site was reported as down. Same trying from Google too. No matter, the other thing you will need to consider is the proper version and is it 32 or 64 bit. If you like I can move your question to the "Excel for developers" forum, they may know where to find the drivers.

    http://blog.danbrust.net/2013/10/10/installing-microsoft-office-data-connectivity-components/#.WlijNq6nGHs


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, January 12, 2018 12:01 PM
  • Could you please move it to excel for developers?

    SomaSundaram R

    Friday, January 12, 2018 1:57 PM
  • Could you explain exchange data and are the servers web servers.  If so, in one case I had to connect to an Access database on a webserver.  I ended up using PHP and using an ODBC connection.  You could do the same thing from C# since it has ODBC support.  I wouldn't recommend connection to Excel on a webserver.  Excel has to run under a user account.  However, you can also make an ODBC connection to an Excel workbook and use SQL to get/put data.

    One more thing when I was doing experiments.  When I tried to get 32-bit Excel running on 64-bit IIS it wouldn't work even though I tagged it as a 32-bit app.  It ran on IIS on 32-bit IIS.  I installed 32-bit Apache and it worked when Apache ran under a user account.

    I've experimented with EPPlus.  It uses OpenXML.  It provides an easy method to generate Excel workbooks.  I was using it to generate an Excel workbook on a webserver.  There is a version for .NET Core 2.0.  I was running on Linux. You can easily create graphs and format data.  I didn't end up using it since the client wanted a pdf of the key graphs and pivot tables.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using OfficeOpenXml;
    using OfficeOpenXml.Drawing;
    using System.Drawing;
    
    namespace EPPlusInAction
    {
    	class Sample1
    	{
    		// This is the Second Release of the Article:
    		// http://www.codeproject.com/Articles/680421/Create-Read-Edit-Advance-Excel-2007-2010-Report-in
    		// Sample 1 shows you the following:
    		/*
    		 * 1. Creating a New Workbook
    		 * 2. Creating a New Worksheet
    		 * 3. Accessing Cells by Row Index and Column Index
    		 * 4. Accessing Cells by its Address
    		 * 5. Inserting Values in Cells
    		 * 6. Setting Relative Formula Reference
    		 * 7. Adding Excel Function SUBTOTAL()
    		 * 8. Formatting the Style of a Range of Cells
    		 * 9. Setting Number Format for a Range of Cells
    		 * 10. Enabling Filter Feature of MS Excel
    		 * 11. Enabling 'AutoFit' of Cells
    		 * 12. Setting Header and Footer of the Worksheet
    		 * 13. Setting Printer Properties
    		 * 14. Setting Page Layout
    		 * 15. Setting Custom Property
    		 */
    		// You will find the published article about above topics here:
    		// http://www.codeproject.com/Tips/681412/Insert-Access-Format-Filter-Setting-Formula-Header
    		public static string RunSample1(DirectoryInfo outputDir)
    		{
                string outPath = Path.Combine(outputDir.FullName, "Sample1.xlsx");
    			FileInfo newFile = new FileInfo(outPath);
    			// If any file exists in this directory having name 'Sample1.xlsx', then delete it
    			if (newFile.Exists)
    			{
    				newFile.Delete(); // ensures we create a new workbook
    				newFile = new FileInfo(outPath);
    			}
    			using (ExcelPackage package = new ExcelPackage(newFile))
    			{
    				// Add a worksheet to the empty workbook
    				ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventry");
                    //var shape = worksheet.Drawings.AddShape("txtDesc", eShapeStyle.Line);
                    //shape.SetSize(100, 0);
                    //shape.SetPosition(10, 200);
                    //shape.Fill.Style = eFillStyle.SolidFill;
                    //shape.Fill.Color = Color.DarkSlateGray;
                    //shape.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
                    //shape.Border.Width = 5;
                    //shape.Border.Fill.Color = Color.Black;
                    // Add the headers
                    // Note: Accessing cells by row index and column index
                    worksheet.Cells[1, 1].Value = "ID";
    				worksheet.Cells[1, 2].Value = "Product";
    				worksheet.Cells[1, 3].Value = "Quantity";
    				worksheet.Cells[1, 4].Value = "Price";
    				worksheet.Cells[1, 5].Value = "Value";
    
    				// Add some items...
    				// Inserting values in the first row for: ID, Product, Quantity & Price respectively
    				// Note: Accessing cells by its address
    				worksheet.Cells["A2"].Value = 12001;
    				worksheet.Cells["B2"].Value = "Nails";
    				worksheet.Cells["C2"].Value = 37;
    				worksheet.Cells["D2"].Value = 3.99;
    
    				// Inserting values in the second row for the same...
    				worksheet.Cells["A3"].Value = 12002;
    				worksheet.Cells["B3"].Value = "Hammer";
    				worksheet.Cells["C3"].Value = 5;
    				worksheet.Cells["D3"].Value = 12.10;
    
    				// Inserting values in the third row for the same...
    				worksheet.Cells["A4"].Value = 12003;
    				worksheet.Cells["B4"].Value = "Saw";
    				worksheet.Cells["C4"].Value = 12;
    				worksheet.Cells["D4"].Value = 15.37;
    
    				// Note: we didn't do anything with the column 'Value'
    				// Actually, the value of the column 'Value' will be the product of 'Qauntity' and 'Price'
    				// So, we need to add a formula in the cells under column 'Value'
    				// Adding formula to the column 'Value'
    				worksheet.Cells["E2:E4"].Formula = "C2*D2";
    				// Note: We take the advantage of the one feature of MS Excel i.e. Relative Formula Reference
    				// For further reading about RELATIVE REFERENCE, click the link below:
    				// http://office.microsoft.com/en-in/excel-help/switch-between-relative-absolute-and-mixed-references-HP010342940.aspx
    
    				// Formatting style of the header
    				using (var range = worksheet.Cells[1, 1, 1, 5])
    				{
    					// Setting bold font
    					range.Style.Font.Bold = true;
    					// Setting fill type solid
    					range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
    					// Setting background color dark blue
    					range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
    					// Setting font color
    					range.Style.Font.Color.SetColor(Color.White);
    				}
    
    				// Formatting the footer row
    				// Setting top border of the footer row
    				worksheet.Cells["A5:E5"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
    				// Setting font bold of the footer row
    				worksheet.Cells["A5:E5"].Style.Font.Bold = true;
    
    				// Now, we want to show the Sub total in the footer row for the 'Quantity', 'Price' and 'Value' column
    				// Seeting formula for the footer row...
    				worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9, {0})", new ExcelAddress(2, 3, 4, 3).Address);
    				// Note: SUBTOTAL() is a Excel Function. If you don't know about this function, read this:
    				// http://office.microsoft.com/en-in/excel-help/subtotal-function-HP010062463.aspx
    
    				 //Now we need to format the values, as the values here, some are string, some are double, some are int
    				 //Setting Number Format...
    				 //Setting integer format for the column 'Quantity' and Setting decimal format for the column 'Price' and 'Value'
    				worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";	// Setting integer format
    				worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";	// Setting decimal format
    				// Here number format is the excel number format, if you don't know, please click and read:
    				// http://office.microsoft.com/en-in/excel-help/create-a-custom-number-format-HP010342372.aspx
    
    				// Now we enabling filter features of Excel in the cells
    				// If you don't know Excel Filtering, please click and read:
    				// http://office.microsoft.com/en-001/excel-help/filter-data-in-a-range-or-table-HP010073941.aspx
    				// Creating an Auto Filter for the range
    				worksheet.Cells["A1:E4"].AutoFilter = true;
    				// Setting text format for the column 'Product', as it will helps you during filtering
    				worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";
    
    				// Setting AutoFit for all cells
    				worksheet.Cells.AutoFitColumns(0);
    
    				// Lets set the header text
    				worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventry";
    
    				// Add the page number to the right of the footer + total number of pages
    				worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
    
    				// Add the sheet name to center of the footer
    				worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
    
    				// Add the filepath to the left of the footer
    				worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
    
    				// At the time of printing, when page page breaks, then the header will come in the next page by enabling this settings...
    				worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:1"];
    				worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:E"];
    
    				// Change the sheet view to show it in page layout mode
    				worksheet.View.PageLayoutView = true;
    
    				// Setting some document properties
    				package.Workbook.Properties.Title = "Invertory";
    				package.Workbook.Properties.Author = "Debopam Pal";
    				package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";
    
    				// set some extended property values
    				package.Workbook.Properties.Company = "AdventureWorks Inc.";
    
    				// set some custom property values
    				package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
    				package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
    				// save our new workbook and we are done!
    				package.Save();
    			}
    
    			return newFile.FullName;
    		}
    	}
    }


    • Edited by mogulman52 Friday, January 12, 2018 3:49 PM
    Friday, January 12, 2018 3:36 PM
  • Hello SomaSundaram R,

    2007 Office System Driver: Data Connectivity Components is actually AccessDatabaseEngine 2007 and Microsoft Access Database Engine 2016 Redistributable is actually its latest version.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SomaSundaram R Tuesday, January 16, 2018 4:27 AM
    Monday, January 15, 2018 6:59 AM
  • Dear Terry,

    Happy to see your response. Thanks for the clue.

    In that case it is sufficient to have only "Microsoft Access Database Engine 2016 Redistributable" to connect with an excel in my machine right? but when i uninstall "2007 Office System Driver: Data Connectivity Components" i got an error and could not connect to excel anymore. The re-installation of 2016 made it works again.

    Thanks a lot!!

     


    SomaSundaram R


    • Edited by SomaSundaram R Tuesday, January 16, 2018 4:32 AM spelling mistake
    Tuesday, January 16, 2018 4:27 AM