none
Accessing Excel form inside project RRS feed

  • Question

  • Hello

    I added an Excel form to the project and now it is part of the file list in the Solution Explorer. The name of the form is Grids.xlsx. How do i access the data in it - i do not need to display it - only to read the data.

    The reason that i try to do it this way - i want to build the EXE with the excel data in it and not to attach additional XLSX file in the installation process. and since the data in in excel sheet is FIXED (changed randomly by the programmer only), it should be part of the project.

    any suggestions?



    Jack Sun

    Thursday, October 6, 2016 5:54 AM

Answers

  • Hi JackSun,

    According to your description, do you created a Windows Forms Application project or VSTO project? If Windows Froms project, since this is the forum to discuss questions and feedback for Visual Studio Tools for Office (VSTO), I suggest that you could post your question on MSDN forum for Windows Forms:

    https://social.msdn.microsoft.com/Forums/windows/en-US/home?category=windowsforms

    As far as I know that the .NET Framework can encapsulate files as part of a compiled assembly. These files are known as embedded resources. These resources are completely separate from the .resources and .resx files that are associated with the assembly. You can access these resources at run time through the Assembly class of the System.Reflection namespace. 

    Assembly asm = Assembly.GetExecutingAssembly();
    string file = string.Format("{0}.writedFile.xlsx", asm.GetName().Name);
    Stream fileStream = asm.GetManifestResourceStream(file);
    SaveStreamToFile(@"D:\EmbeddedFile\Temp.xlsx", fileStream);  //<--here is where to save to disk
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"D:\EmbeddedFile\Temp.xlsx");
    if (xlWorkBook == null)
    {
    MessageBox.Show("Error: Unable to open Excel file.");
    return;
    }
    else
    {
    MessageBox.Show("You could read data from Excel file");
    }
    xlApp.Visible = false;
    xlApp.Quit();
    xlWorkBook = null;
    xlApp = null;
    
    public void SaveStreamToFile(string fileFullPath, Stream stream)
    {
        if (stream.Length == 0) return;
    
        // Create a FileStream object to write a stream to a file
        using (FileStream fileStream = System.IO.File.Create(fileFullPath, (int)stream.Length))
        {
            // Fill the bytes[] array with the stream data
            byte[] bytesInStream = new byte[stream.Length];
            stream.Read(bytesInStream, 0, (int)bytesInStream.Length);
    
            // Use FileStream object to write to the specified file
            fileStream.Write(bytesInStream, 0, bytesInStream.Length);
        }
    }


    For more information, please refer to How to embed and access resources by using Visual C#


    In addition, you could use Microsoft.Office.Interop.Excel or ADO.Net to retrieve the data, refer to below link:

    How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

    Excel 2013 Primary Interop Assembly Class Library

    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Friday, October 14, 2016 9:38 AM
    • Marked as answer by David_JunFeng Monday, October 17, 2016 2:36 PM
    Friday, October 7, 2016 2:28 AM