none
Need Help i get error message on using excel; can help me take a look thanks RRS feed

  • Question

  • I get error message on below code.did i missing insert anything. the purpose i create this project is want excel file can convert to txt file using c# code

    using Excel;

      // Reading Excel file format; *.xlsx
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();

     // Reading Excel file ('97-2003 format; *.xls)
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();






    • Edited by christing Wednesday, August 14, 2019 8:08 AM
    Wednesday, August 14, 2019 6:31 AM

All replies

  • Hi,

    try my code:

    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace _4.Need_Help_i_get_error_message
    {
        class Program
        {
            static void Main(string[] args)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                app.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook wbk = app.Workbooks.Open(@"D:\Student.xlsx");
                wbk.SaveAs(@"D:\g.txt", XlFileFormat.xlUnicodeText);
                wbk = null;
                app.Quit();
                Console.WriteLine("Successful export!");
                Console.ReadKey();
    
            }
        }
    }
    

    Best Regards,

    Alex



    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.

    Wednesday, August 14, 2019 8:36 AM
    Moderator
  • @Alex thank you take a time to look

    i create a new class file and using the code you provide to me. but i am still get error on this code

    using Microsoft.Office.Interop.Excel;

    Did i need install anything about MS OFFICE.

     Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

     Microsoft.Office.Interop.Excel.Workbook wbk = app.Workbooks.Open(@"D:\Student.xlsx");

    wbk.SaveAs(@"D:\g.txt", XlFileFormat.xlUnicodeText);

    may i ask is this code still maintain use on my form 1.

      private void getExcelData(string file)
            {

                if (file.EndsWith(".xlsx"))
                {
                    // Reading Excel file format; *.xlsx
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();
                }

                if (file.EndsWith(".xls"))
                {
                    // Reading Excel file ('97-2003 format; *.xls)
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader =                     ExcelReaderFactory.CreateBinaryReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();
                }

    • Edited by christing Wednesday, August 14, 2019 9:39 AM
    Wednesday, August 14, 2019 9:33 AM
  • Hi,

    add the library assembly reference to your project,see the following link:

    https://stackoverflow.com/questions/17822184/cant-get-microsoft-office-interop-reference-to-work

    Best Regards,

    Alex


    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.


    Wednesday, August 14, 2019 9:40 AM
    Moderator
  • Looks like you are using a third party product which means you need to install it as per the link below

    https://www.nuget.org/packages/ExcelDataReader/


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, August 14, 2019 9:50 AM
    Moderator
  • @alex thank link you send to me. May i ask is above code you provide for me using on my project program.cs file izzit. 

    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace _4.Need_Help_i_get_error_message
    {
        class Program
        {
            static void Main(string[] args)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                app.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook wbk = app.Workbooks.Open(@"D:\Student.xlsx");
                wbk.SaveAs(@"D:\g.txt", XlFileFormat.xlUnicodeText);
                wbk = null;
                app.Quit();
                Console.WriteLine("Successful export!");
                Console.ReadKey();
    
            }
        }
    }

    Thursday, August 15, 2019 8:14 AM
  • Hi,

    Yes,convert D:\Student.xlsx to D:\g.txt

    Best Regards,

    Alex


    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.

    Thursday, August 15, 2019 8:24 AM
    Moderator
  • @Alex may i ask 

    this is the code i create for program. did i need change a code on my program may i ask is i need edit some code on my program.

    DataSet result = new DataSet();
            private void btnconvert_Click(object sender, EventArgs e)
            {
                string fileName = "";
                fileName = OutPutFile.Text;

                if (fileName == "")
                {
                    MessageBox.Show("Enter Valid file name");
                    return;
                }

                converTotxt(FileSheet.SelectedIndex);
                //converToCSV(comboBox1.SelectedIndex);

            }

     private void getExcelData(string file)
            {

                 if (file.EndsWith(".xlsx"))
                {
                    // Reading from a binary Excel file (format; *.xlsx)
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();
                }

                if (file.EndsWith(".xls"))
                {
                    // Reading from a binary Excel file ('97-2003 format; *.xls)
                    FileStream stream = File.Open(file, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    result = excelReader.AsDataSet();
                    excelReader.Close();
                }


                List<string> items = new List<string>();
                for (int i = 0; i < result.Tables.Count; i++)
                    items.Add(result.Tables[i].TableName.ToString());
                FileSheet.DataSource = items;
            }

     private void converToCSV(int ind)
            {
         
                string a = "";
                int row_no = 0;

                while (row_no < result.Tables[ind].Rows.Count)
                {
                    for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
                    {
                        a += result.Tables[ind].Rows[row_no][i].ToString() + ",";
                    }
                    row_no++;
                    a += "\n";
                }
                string output = locationFile.Text + "\\" + OutPutFile.Text + ".csv";
                StreamWriter csv = new StreamWriter(@output, false);
                csv.Write(a);
                csv.Close();

                MessageBox.Show("File converted succussfully");

                //clear textbox when convert file succussfully
                InputExcel.Text = "";
                locationFile.Text = "";
                OutPutFile.Text = "";
                FileSheet.DataSource = null;

                return;
            }

            //txt
            private void converTotxt(int ind)
            {
                string B = "";
                int row_no = 0;

                while (row_no < result.Tables[ind].Rows.Count)
                {
                    for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
                    {
                        B += result.Tables[ind].Rows[row_no][i].ToString() + ",";
                    }
                    row_no++;
                    B += "\n";
                }

                //location file
                string output = locationFile.Text + "\\" + OutPutFile.Text + ".txt";
                StreamWriter csv = new StreamWriter(@output, false);
                csv.Write(B);
                csv.Close();

                MessageBox.Show("File converted succussfully");

                //clear textbox when convert succussfully
                InputExcel.Text = "";
                locationFile.Text = "";
                OutPutFile.Text = "";
                FileSheet.DataSource = null;

                return;
            }



    • Edited by christing Friday, August 16, 2019 4:43 AM
    Friday, August 16, 2019 1:58 AM
  • Hi,

    Your method is to convert the excel data to a datatable and then import the text file, and you use the three-party dll.You can use my method:

       public void ExceltoText(string ExcelPath,string TextPath)
            {
                Microsoft.Office.Interop.Excel.Application app =new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                app.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook wbk = app.Workbooks.Open(ExcelPath);
                wbk.SaveAs(TextPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlUnicodeText);
                wbk = null;
                app.Quit();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                ExceltoText(@"D:\test.xlsx", @"D:\test.txt");
            }

    Best Regards,

    Alex


    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.

    Monday, August 19, 2019 7:04 AM
    Moderator