none
C# - How read Excel File from INI file path. RRS feed

  • Question

  • Hi,

    I am doing some calculation based on my excel input.

    This Excel user can select from the particular path and then select the file.

    But, now client wants, they don't want to select excel file from the path.

    Instead of that, they will mention the file path in the INI file.

    While loading the form, first it should read the INI File and then read and convert into datatable.

    I am new to use INI File.

    Can anyone suggest how to write code to read the file from INI and convert those file into datatable

    See my current code below..

     private void btnbrowse_Click(object sender, EventArgs e)
      {
                this.openFileDialog1.Filter = "Excel file (*.xls;)|*.xlsx";
                this.openFileDialog1.Multiselect = true;
                this.openFileDialog1.Title = "Select Excel";
                DialogResult dr = this.openFileDialog1.ShowDialog();
                DataTable dataTable1 = new DataTable();
                
                if (dr == System.Windows.Forms.DialogResult.OK)
                {
                    foreach (String file in openFileDialog1.FileNames)
                    {
                        Excelname = file;
                        txtXLfile.Text = Excelname;
                    }
                }
                dtproject = getXLDatatable(Excelname, dataTable1);
    
                DataRow dr1;
    
                dr1 = dtproject.NewRow();
                dr1.ItemArray = new object[] { 0, "Select Project" };
                dtproject.Rows.InsertAt(dr1, 0);
                cmbProject.ValueMember  = "EmployeeName";
                cmbProject.DisplayMember = "EmployeeName";
                cmbProject.DataSource = dtproject;  
        }
    
        private static DataTable getXLDatatable(string XLbroname, DataTable dataTable1)
            {
               
                FileStream fstream1 = new FileStream(XLbroname, FileMode.Open);
              
                Workbook workbook1 = new Workbook(fstream1);
                Worksheet worksheet1 = workbook1.Worksheets[0];
                dataTable1.AcceptChanges();
                DataSet ds1 = new DataSet();
                DataTable dt2 = new DataTable();
                ds1.Tables.Add(dataTable1);
                dataTable1 = worksheet1.Cells.ExportDataTable(1, 0, worksheet1.Cells.MaxRow + 1, worksheet1.Cells.MaxColumn + 1);
                dataTable1.TableName = "tableXL";
                dataTable1.Columns[0].ColumnName = "Id";
                dataTable1.Columns[1].ColumnName = "EmployeeName";
                dataTable1.Columns[2].ColumnName = "Dept";
                dataTable1.Columns[3].ColumnName = "Section";
    
                dt2 = dataTable1;
                return dt2;
            }

    Inside the INI File path something like may be in the serverpath or localpath.

    Wednesday, November 22, 2017 12:13 PM

Answers

  • Thanks for your update.

    can you pls. provide some other sample code which is related to my logic..?

    Here is an example of INI file contents (section, key and key value):

    [Files]
    ExcelFilePath=C:\Users\username\Documents\Filename.xls

    Here is an example of code you can use to get the file path from the INI file:

    [DllImport("kernel32")]
    private static extern int GetPrivateProfileString(string Section, string Key, string Default, StringBuilder RetVal, int Size, string FilePath);
    
    public string GetINIFileValue(string path, string section, string key)
    {
        StringBuilder sb = new StringBuilder(4096);
        int n = GetPrivateProfileString(section, key, "", sb, 4096, path);
        if(n<1) return string.Empty;
        return sb.ToString();
    }
    
    string excelFilePath = GetINIFileValue("C:\\Users\\username\\Documents\\FileName.ini", "Files", "ExcelFilePath");


    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Thursday, November 23, 2017 6:04 AM
    • Marked as answer by Gani tpt Monday, November 27, 2017 12:42 PM
    Thursday, November 23, 2017 6:03 AM

All replies

  • Using INI files to store information is considered dated technology and is only used for legacy applications. If you absolutely must use an INI file then there are Windows API function calls that can do this for you. You can find an example below:

    https://code.msdn.microsoft.com/windowsdesktop/Reading-and-Writing-Values-85084b6a


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 22, 2017 3:24 PM
  • Thanks for your update.

    can you pls. provide some other sample code which is related to my logic..?

    Thursday, November 23, 2017 4:18 AM
  • Thanks for your update.

    can you pls. provide some other sample code which is related to my logic..?

    Here is an example of INI file contents (section, key and key value):

    [Files]
    ExcelFilePath=C:\Users\username\Documents\Filename.xls

    Here is an example of code you can use to get the file path from the INI file:

    [DllImport("kernel32")]
    private static extern int GetPrivateProfileString(string Section, string Key, string Default, StringBuilder RetVal, int Size, string FilePath);
    
    public string GetINIFileValue(string path, string section, string key)
    {
        StringBuilder sb = new StringBuilder(4096);
        int n = GetPrivateProfileString(section, key, "", sb, 4096, path);
        if(n<1) return string.Empty;
        return sb.ToString();
    }
    
    string excelFilePath = GetINIFileValue("C:\\Users\\username\\Documents\\FileName.ini", "Files", "ExcelFilePath");


    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Thursday, November 23, 2017 6:04 AM
    • Marked as answer by Gani tpt Monday, November 27, 2017 12:42 PM
    Thursday, November 23, 2017 6:03 AM
  • Thanks..

    Thanks for your update.

    I have two concerns.

    I want to keep and maintain INI file in network folder/File server folder. 
    ExcelPath file (excelfile) also keep and maintain in network folder/File server folder.
    ExcelPath : for example (\\server201\Project1\documents\projects.xlsx)

    INI File ==> still keep maintaining application running folder.

    In that case how do we mention server path for ini file and Excel file....?

    Note : Application (.EXE) is running from network folder. The reason is to keep maintain INI File in folder, user should not see the file path and admin only will maintain this file




    • Edited by Gani tpt Thursday, November 23, 2017 9:22 AM
    Thursday, November 23, 2017 6:18 AM
  • any update...
    Thursday, November 23, 2017 10:24 AM
  • Thanks..

    Thanks for your update.

    I have two concerns.

    I want to keep and maintain INI file in network folder/File server folder. 
    ExcelPath file (excelfile) also keep and maintain in network folder/File server folder.
    ExcelPath : for example (\\server201\Project1\documents\projects.xlsx)

    INI File ==> still keep maintaining application running folder.

    In that case how do we mention server path for ini file and Excel file....?

    Note : Application (.EXE) is running from network folder. The reason is to keep maintain INI File in folder, user should not see the file path and admin only will maintain this file




    If the INI file is in the same folder as the app then use My.Application.Info.DirectoryPath:

    string excelFilePath = GetINIFileValue(string.Concat(My.Application.Info.DirectoryPath, "\\FileName.ini"), "Files", "ExcelFilePath");


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, November 23, 2017 2:01 PM
  • getting error in 'My' ==> then name 'My' doesn't contain in the current context.

    My.Application.Info.DirectoryPath

    'My' is there any syntax error...?

    Friday, November 24, 2017 4:38 AM
  • Sorry, looks like that is VB specific. Try replacing that with the following:

    System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by Fei HuModerator Sunday, November 26, 2017 11:21 AM
    • Marked as answer by Gani tpt Monday, November 27, 2017 12:41 PM
    • Unmarked as answer by Gani tpt Monday, November 27, 2017 12:42 PM
    Friday, November 24, 2017 2:18 PM
  • Thank you. working...
    Monday, November 27, 2017 12:42 PM