none
How get Excel file route from textBox in Windows Forms RRS feed

  • Question

  • Hello Comunity.

    I want import an Excel file to my project in Visual Studio 2012 with OleDb. I can import the file giving complete route to the method like:

     Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Maria\Desktop\Test.xlsx");

    But I want import the file getting the pathfrom a textBox and open it clicking a button. How I can do this?

    Thanks


    PD: Sorry for bad english.

    Wednesday, February 7, 2018 6:28 PM

All replies

  • If you have created already a Windows Form application, then you just need to add a textbox on the form and then you can do the following in code to read the value entered in the TextBox.

    For example, you are setting the name of it to txtBoxPath,in that case you just need to do this:

    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtBoxPath.Text);
    Hope it Helps.


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, February 7, 2018 7:31 PM
  • try the following:

    public void OpenExcelSheet(string workBookPath)
    {
        object NullValue = System.Reflection.Missing.Value;
        Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;
        excelApp.Workbooks.Open(workBookPath);
    }
    
    private void button1_Click(object sender, EventArgs e)
    {
        string xlsFile = textBox1.Text;
        OpenExcelSheet(xlsFile);
    }
    

    You need to add a reference to Microsoft.Interop.Excel.

    wizend

    Wednesday, February 7, 2018 7:51 PM
  • So you are showing excel automation yet you indicated you wanted OleDb, which one is it?

    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

    Wednesday, February 7, 2018 9:02 PM
    Moderator
  • Since we have not heard back from you I'm going to recommend SpreadSheetLight library. 

    http://spreadsheetlight.com/downloads/samplecode/ImportDataTable.cs

    code sample (in a compact format excluding formatting we can narrow the code down to about 6 lines of code)

    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)
            {
                Random rand = new Random();
    
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("Product", typeof(string));
                dt.Columns.Add("IP Address", typeof(string));
                dt.Columns.Add("Date (UTC)", typeof(DateTime));
                dt.Columns.Add("Size (MB)", typeof(double));
                dt.Columns.Add("Cost", typeof(decimal));
    
                for (int i = 0; i < 10; ++i)
                {
                    dt.Rows.Add(string.Format("Prod{0}", rand.Next(5)),
                        string.Format("{0}.{1}.{2}.{3}", rand.Next(256), rand.Next(256), rand.Next(256), rand.Next(256)),
                        DateTime.UtcNow.AddDays(rand.NextDouble() * 20),
                        decimal.Round((decimal)(rand.NextDouble() * 500 + 200), 4),
                        decimal.Round((decimal)(rand.NextDouble() * 20 + 5), 2));
                }
    
                SLDocument sl = new SLDocument();
    
                int iStartRowIndex = 3;
                int iStartColumnIndex = 2;
    
                sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dt, true);
    
                // This part sets the style, but you might be using a template file,
                // so the styles are probably already set.
    
                SLStyle style = sl.CreateStyle();
                style.FormatCode = "yyyy/mm/dd hh:mm:ss";
                sl.SetColumnStyle(4, style);
    
                style.FormatCode = "#,##0.0000";
                sl.SetColumnStyle(5, style);
    
                style.FormatCode = "$#,##0.00";
                sl.SetColumnStyle(6, style);
    
                // The next part is optional, but it shows how you can set a table on your
                // data based on your DataTable's dimensions.
    
                // + 1 because the header row is included
                // - 1 because it's a counting thing, because the start row is counted.
                int iEndRowIndex = iStartRowIndex + dt.Rows.Count + 1 - 1;
                // - 1 because it's a counting thing, because the start column is counted.
                int iEndColumnIndex = iStartColumnIndex + dt.Columns.Count - 1;
                SLTable table = sl.CreateTable(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
                table.SetTableStyle(SLTableStyleTypeValues.Medium17);
                table.HasTotalRow = true;
                table.SetTotalRowFunction(5, SLTotalsRowFunctionValues.Sum);
                sl.InsertTable(table);
    
                sl.SaveAs("ImportDataTable.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

    Thursday, February 8, 2018 2:07 AM
    Moderator