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:
- How do I go about addressing the above error.
- 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;
}
}
}
xlRange.Cells[i,j].Value2is null. I suspect that will also help you determine when you've reached the end of the data.rowCountandcolCounthave? Are they wildly different to what you expect? If so you can't rely on these to determine the range of your data.