2 Replies - 515 Views - Last Post: 29 November 2013 - 04:01 PM Rate Topic: -----

#1 vivek20055  Icon User is offline

  • D.I.C Head

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

Dataset is having some capacity?

Posted 28 November 2013 - 02:48 AM

Hi,

I am reading the data from excel file and saving in dataset.

Now when I am trying to extract first row of the data from dataset. I am not getting the complete data.

The same code is working fine for some other excel file with less number of columns

Is the data set is having some capacity???

My 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;
using System.Collections;

namespace ConsoleApplication31
{
    class Program
    {
        
        private DataSet GetExcelData(string ExcelPath, string ExcelFileName)
        {
            object misValue = System.Reflection.Missing.Value;
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;


            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(); 
                
            }
            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()
        {

            object misValue = System.Reflection.Missing.Value;
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;


           // DataSet ds1 = GetExcelData(@"\\global.scd.scania.com\home\se\121\valhbc\Desktop\log files\Output\", "ResponseTime.xls");

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

           //DataSet ds1 = GetExcelData(@"\\global.scd.scania.com\home\se\121\valhbc\Desktop\log files\Output\", "MsgGreen.xls");


           string path1 = @"\\global.scd.scania.com\home\se\121\valhbc\Desktop\log file data\Output";
           // Print the value of columns 1 in each row 
           if (File.Exists(path1))
               File.Delete(path1);
           int i = 1;
          // int j = 1;

           //Console.WriteLine(ds1.GetXml());
           foreach (DataRow row in ds1.Tables[0].Rows)
           {

               xlWorkSheet.Cells[i, 1] = row[0];
              
               
               
               

               i++;
           }

     

           

          
                               
        }
    }
}


Thanks
John

Is This A Good Question/Topic? 0
  • +

Replies To: Dataset is having some capacity?

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,008
  • Joined: 12-December 12

Re: Dataset is having some capacity?

Posted 29 November 2013 - 12:57 PM

It is not clear to me: at what stage are you examining the DataSet? That is, how do you know that it doesn't contain the data that you are after?



Is there anything different about the Excel file, compared to the other files?

Open the file and press Ctrl-End to see what it considers the last used-cell.
Click in the data area and press Ctrl-A; does it highlight just the data you need?
Does every column have a heading, without odd characters?

Hopefully, there are no merged cells in the sheet either.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5846
  • View blog
  • Posts: 12,705
  • Joined: 16-October 07

Re: Dataset is having some capacity?

Posted 29 November 2013 - 04:01 PM

There can be a column cap of 255 on some providers.

You can fake it out by explicitly stating the range to some larger domain:
select * from [Sheet1$A1:AB2000]


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1