1

I am a novice in C# so please forgive me silly mistakes in my code :P I am having the following error come up whenever I am looping through an Excel file:

Additional information: Cannot perform run time binding on a null reference

I have gathered the problem that it is picking up far too many rows (the Excel file only has 2 rows, ends up finding 6 rows?), and by the time it gets to the 3rd row, the error happens. I have opened the Excel file and deleted as many rows after the 2nd row but it keeps coming up with the same issue. As I am here, I wish to ask the following questions:

  1. How do I go about addressing the above error.
  2. How do I output all the values on the same row, and when row count changes, go onto the next line in the next file and start writing to that?

.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;


namespace KTypeLookUp
{
    public partial class Form1 : Form
    {
        FileStream ostrm;
        StreamWriter writer;
        TextWriter oldOut = Console.Out;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {//Select Input File Button
            String textPath = @"C:\AAAtemp\export.txt";
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Files(*.xls)|*.xls|All Files|*.*";
            if (ofd.ShowDialog() == DialogResult.OK)
            {//Read and display Excel file
                string path = ofd.FileName.ToString();

                textBox3.Text = path;
                OleDbConnection conn = new OleDbConnection();
                conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

                OleDbCommand command = new OleDbCommand
                (
                      "SELECT * FROM [Sheet1$]", conn
                );
                DataSet dataset = new DataSet();
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                adapter.Fill(dataset);

                dataGridView1.DataSource = dataset.Tables[0];

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;
                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;
                try
                {
                    ostrm = new FileStream(textPath, FileMode.OpenOrCreate, FileAccess.Write);
                    writer = new StreamWriter(ostrm);
                }
                catch (Exception ex)
                {
                    ex.ToString();
                   MessageBox.Show("", "My Application",
                   MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
                    return;
                }
                for (int i = 1; i <= rowCount; i++)
                {
                    for (int j = 1; j <= colCount; j++)
                    {
                        Console.SetOut(writer);
                        Console.WriteLine(xlRange.Cells[i, j].Value2.ToString());
                    }
                }
                writer.Close();
                ostrm.Close();
                MessageBox.Show("Text file created", "My Application",
                MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk); 
            }   
        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {//Text box to display path
            textBox3.ReadOnly = true;
        }  
    }
}
7
  • Which line gives you the null reference error? Commented May 9, 2015 at 19:23
  • Hi ChrisF On line Console.WriteLine(xlRange.Cells[i, j].Value2.ToString()); When theres a null value being written out Commented May 9, 2015 at 19:27
  • You need to run this in a debugger and find out exactly which part of xlRange.Cells[i,j].Value2 is null. I suspect that will also help you determine when you've reached the end of the data. Commented May 9, 2015 at 19:30
  • What values to rowCount and colCount have? Are they wildly different to what you expect? If so you can't rely on these to determine the range of your data. Commented May 9, 2015 at 19:34
  • rowCount is 6 when it should be 2 (as there are only 2 rows in the excel file). colCount works fine as there are only 10 columns and it loops through all 10 columns. It when it gets to the 3rd row column 1 (i = 3 j = 1), it causes an error. I cant seem to step in to the xlRange.Cells bit of code either, because it literally breaks when i attempt to. Commented May 9, 2015 at 23:34

1 Answer 1

1

Your problem is likely related to using UsedRange to determine the number of rows in the spreadsheet.

UsedRange is useful when you know what it includes and keep the spreadsheet in good order. Specifically, UsedRange will include cells that are formatted but have no data in them. That formatting may include details which are not visible like a change to the default font.

If you want to test this, you need to delete all the rows below the ones that you want to include. In a comment, you mentioned this is only 2 rows. I would select row 3 (click on row label) and use CTRL+SHIFT+DOWN to select all the rows below it and then delete them (CTRL+MINUS). Your UsedRange should then adjust to only be 2 rows total.

Alternatively, depending on your data, you can use a number of other techniques to determine the number of rows of data you have. If the data is contiguous (no gaps of entire rows/columns) you can use CurrentRegion to get the "block" of data.

Sign up to request clarification or add additional context in comments.

2 Comments

The CTRL+SHIFT+DOWN and CTRL+MINUS worked flawlessly. Thank you :D As for the second part of the question: How do I output all the values on the same row, and when row count changes, go onto the next line in the next file and start writing to that? Any ideas :)
I don't have as much to say on that front. I don't have much interest writing C# code for a specific application. Your description of the problem sounds well formed and just needs to be coded. If you have a specific problem after trying, I would post a new question on SO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.