1 Replies - 224 Views - Last Post: 28 November 2013 - 08:46 AM Rate Topic: -----

#1 vivek20055  Icon User is offline

  • D.I.C Head

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

why I am not able to write all the lines of dataset to excel sheet

Posted 28 November 2013 - 07:45 AM

Hi,

I am using the following code to read all rows of one column into dataset and write into new excel sheet

It is working fine with some excel files but not working fine with other( giving only partial data)

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[2];
              
               i++;
           }

     

           
           xlWorkBook.SaveAs(path1, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
           xlWorkBook.Close(true, misValue, misValue);
            
          
                               
        }
    }
}


Can anyone help me in solving this???

Thanks
John

Is This A Good Question/Topic? 0
  • +

Replies To: why I am not able to write all the lines of dataset to excel sheet

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5431
  • View blog
  • Posts: 11,641
  • Joined: 02-June 10

Re: why I am not able to write all the lines of dataset to excel sheet

Posted 28 November 2013 - 08:46 AM

Duplicate question. You've been warned before about that. Thread closed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1