how to convert dataset to list

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 908 Views - Last Post: 27 November 2013 - 07:25 AM Rate Topic: -----

#1 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

how to convert dataset to list

Posted 26 November 2013 - 03:31 AM

Hi,

I have code to read excel file and save them into dataset.
Now I need to read 3 excel files like this and I need to compare the message ID in the 3 excel files,for the common message ID.

I am attaching a image to show how my data in dataset looks like

Is it better to convert the table of message ID to list<t> before doing it?

code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace ConsoleApplication31
{
    class Program
    {
        
        private DataSet GetExcelData(string ExcelPath, string ExcelFileName)
        {
            string Provider = string.Empty;
            string ExtendedProperties = string.Empty;
            List<string> listSheetNames = new List<string>();
            DataSet dataSet = new DataSet();
            OleDbConnection connection = null;
            try
            {
                // for 97-03 Excel file
                if (Path.GetExtension(ExcelFileName).Equals(".xls"))
                {
                    Provider = "Microsoft.Jet.OLEDB.4.0";
                    ExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";
                }
                // for 2007 Excel file
                else if (Path.GetExtension(ExcelFileName).Equals(".xlsx"))
                {
                    Provider = ".xlsx";
                    ExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }
                try
                {
                    connection = new OleDbConnection("provider =" + Provider + "; Data Source='" + ExcelPath + ExcelFileName + "';" + "Extended Properties=\"" + ExtendedProperties + "\";");
                    connection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in opening the file " + ExcelFileName + "\nDetails: " + ex.Message);
                }

                listSheetNames = GetExcelSheetName(connection);
                OleDbDataAdapter command = new OleDbDataAdapter("Select * From [" + listSheetNames[0] + "]", connection);
                command.Fill(dataSet);
                connection.Close();
                //Console.WriteLine(dataSet);
                Console.WriteLine(dataSet.GetXml());
                for (double i = 0; i <= 10000000000000; i++)
            {
            }
                
            }
            catch (Exception ex)
            {
                throw new Exception("Error in retrieving data from the file " + ExcelFileName + "\nDetails: " + ex.Message);
            }
            return dataSet;
        }

        private List<string> GetExcelSheetName(OleDbConnection connection)
        {
            List<string> listSheetNames = new List<string>();
            try
            {
                DataTable dtSheet = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheetNames.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not read Excel sheet names\nDetails: " + ex.Message);
            }
            return listSheetNames;
        }

 static void Main()
        {
            var p = new Program();
            p.Bar();
        }

        void Bar()
        {
            

            GetExcelData(@"\\global.scd.scania.com\home\se\121\valhbc\Desktop\", "MsgRed.xls");
           
            






        }
    }
}


Thanks
John

Attached image(s)

  • Attached Image

This post has been edited by vivek20055: 26 November 2013 - 05:34 AM


Is This A Good Question/Topic? 0
  • +

Replies To: how to convert dataset to list

#2 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3652
  • View blog
  • Posts: 11,421
  • Joined: 05-May 12

Re: how to convert dataset to list

Posted 26 November 2013 - 07:10 AM

Instead of doing the transform from DataSet to List, why not go straight to List. Dump using the Adapter, and use the Command and Reader directly. See sample code in MSDN: http://msdn.microsof...(v=vs.110).aspx
Was This Post Helpful? 0
  • +
  • -

#3 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 26 November 2013 - 08:23 AM

Hi,

This code print the shows the data of column1 of each row on console.

Now I need to print the same into excel sheet


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace ConsoleApplication31
{
    class Program
    {
        
        private DataSet GetExcelData(string ExcelPath, string ExcelFileName)
        {
            string Provider = string.Empty;
            string ExtendedProperties = string.Empty;
            List<string> listSheetNames = new List<string>();
            DataSet dataSet = new DataSet();
            OleDbConnection connection = null;
            try
            {
                // for 97-03 Excel file
                if (Path.GetExtension(ExcelFileName).Equals(".xls"))
                {
                    Provider = "Microsoft.Jet.OLEDB.4.0";
                    ExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";
                }
                // for 2007 Excel file
                else if (Path.GetExtension(ExcelFileName).Equals(".xlsx"))
                {
                    Provider = ".xlsx";
                    ExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }
                try
                {
                    connection = new OleDbConnection("provider =" + Provider + "; Data Source='" + ExcelPath + ExcelFileName + "';" + "Extended Properties=\"" + ExtendedProperties + "\";");
                    connection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in opening the file " + ExcelFileName + "\nDetails: " + ex.Message);
                }

                listSheetNames = GetExcelSheetName(connection);
                OleDbDataAdapter command = new OleDbDataAdapter("Select * From [" + listSheetNames[0] + "]", connection);
                command.Fill(dataSet);
                connection.Close();
               
                
                // Print the value of columns 1 in each row 

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Console.WriteLine(row[1]);
                }
                
                    
                
                for (double i = 0; i <= 10000000000000; i++)
                {
                }
                
            }
            catch (Exception ex)
            {
                throw new Exception("Error in retrieving data from the file " + ExcelFileName + "\nDetails: " + ex.Message);
            }
            return dataSet;
        }

        private List<string> GetExcelSheetName(OleDbConnection connection)
        {
            List<string> listSheetNames = new List<string>();
            try
            {
                DataTable dtSheet = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheetNames.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not read Excel sheet names\nDetails: " + ex.Message);
            }
            return listSheetNames;
        }

 static void Main()
        {
            var p = new Program();
            p.Bar();
        }

        void Bar()
        {


            GetExcelData(@"\\global.scd.scania.com\home\se\121\valhbc\Desktop\log files\Output\", "MsgRed.xls");
           
            

        }
    }
}


Can anyone tell me how can I do that?

Thanks
John

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4559
  • View blog
  • Posts: 7,980
  • Joined: 08-June 10

Re: how to convert dataset to list

Posted 26 November 2013 - 09:05 AM

So, what is it you can't do? If you don't know how to write to an Excel file, I suggest you search on how to do so. Normally I wouldn't give that advice, but as you've proved so frequently, you don't search before you ask. I'm sure you can find a tutorial to follow to at least try to write to an Excel file. Search for it, try it, and if you still can't do it, come back and show us what you've tried.
Was This Post Helpful? 0
  • +
  • -

#5 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 01:07 AM

Hi,

I am trying like this to write one table data to excel sheet. But it is saving last table data

How can I save all the tables data to excel sheet??

 if (File.Exists(path1))
                    File.Delete(path1);
               
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    //Console.WriteLine(row[1]);
                    xlWorkSheet.Cells[3, 1] = row[1];
                }
               
                xlWorkBook.SaveAs(path1, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path1);
                xlApp.Visible = true;



Thanks
John
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • D.I.C.
  • member icon

Reputation: 768
  • View blog
  • Posts: 3,358
  • Joined: 03-December 12

Re: how to convert dataset to list

Posted 27 November 2013 - 01:45 AM

xlWorkSheet.Cells[3, 1] = row[1]; 
your not incrementing your row or your cells.

This post has been edited by astonecipher: 27 November 2013 - 01:59 AM

Was This Post Helpful? 0
  • +
  • -

#7 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 02:07 AM

Hi,

Sorry for the trouble. I solved it.

Now I need to do the same with other excel file and compare the values to extract the same strings.

Can you give me some idea, how can I do that??



Thanks
John
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher  Icon User is offline

  • D.I.C.
  • member icon

Reputation: 768
  • View blog
  • Posts: 3,358
  • Joined: 03-December 12

Re: how to convert dataset to list

Posted 27 November 2013 - 02:16 AM

After you have shown more effort.
Was This Post Helpful? 0
  • +
  • -

#9 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 02:21 AM

View Postastonecipher, on 27 November 2013 - 02:16 AM, said:

After you have shown more effort.

Hi,


I need to do the same with the other excel file and compare both for same strings.

For that I need to pass different arguments to same method.

How can I do this?


Thanks
John
Was This Post Helpful? 0
  • +
  • -

#10 astonecipher  Icon User is offline

  • D.I.C.
  • member icon

Reputation: 768
  • View blog
  • Posts: 3,358
  • Joined: 03-December 12

Re: how to convert dataset to list

Posted 27 November 2013 - 02:46 AM

I and others are much more inclined to help and give guidance after YOU have shown an effort that you have searched and tried to do it on your own. You have yet, in the posts I have read, to do either.

This post has been edited by astonecipher: 27 November 2013 - 02:47 AM

Was This Post Helpful? 0
  • +
  • -

#11 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 03:34 AM

Hi,

Thanks for your suggestions. I have solved that problem either.

Now I need to compare the data in 3 rows of excel file to extract same strings

I am attaching the image of the data with this file.


Thanks
John

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#12 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 05:55 AM

Hi,

I am trying something like this for string comparison in excel rows

if (xlWorkSheet.Cells[22, 1].Equals(xlWorkSheet.Cells[10, 2]))
                
xlWorkSheet.Cells[1, 4] = xlWorkSheet.Cells[21, 1];


But it is not working

Even when I am trying to print the value of excel cell
I am getting something like System._Object

Can anyone suggest something about this?

Thanks
John

This post has been edited by vivek20055: 27 November 2013 - 06:02 AM

Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3746
  • View blog
  • Posts: 13,116
  • Joined: 12-December 12

Re: how to convert dataset to list

Posted 27 November 2013 - 06:03 AM

Do you receive error messages? If so, what are they.

Otherwise, what happens when you run this code.
Was This Post Helpful? 0
  • +
  • -

#14 vivek20055  Icon User is offline

  • D.I.C Head

Reputation: -7
  • View blog
  • Posts: 82
  • Joined: 02-November 13

Re: how to convert dataset to list

Posted 27 November 2013 - 06:06 AM

Hi andrew,

I am not getting any error.
But I am not getting the result
I cannot compare excel cell values in this way???
Even when I am trying to print the cell value on console
I am getting something like System._Object



Thanks
John
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3746
  • View blog
  • Posts: 13,116
  • Joined: 12-December 12

Re: how to convert dataset to list

Posted 27 November 2013 - 06:11 AM

In Excel VBA the default property for Cells(x, y) or Range("A1") is the Value property. Running from C# we cannot assume this default. So you need:

xlWorkSheet.Cells[22, 1].Value

to get the value from a cell. Then you will generally need to convert this value (or attempt to) some number, or string, etc.. E.g.

xlWorkSheet.Cells[22, 1].Value.ToString()

or .Value2:

Quote

The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.

http://www.dontbreak...ata-from-excel/

This post has been edited by andrewsw: 27 November 2013 - 06:13 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2