Sam

Sam

  • NA
  • 166
  • 0

Excel to DataGridView error: "Array was not one-dimensional array"

Nov 8 2009 4:38 AM

Hi,
I made a class that reads an Excel file and makes it into an array (Attached).
It is then supposed to convert the array to DataGridView object.
It seems to 'almost' work  - It reads the Excel and builds a nice array as can be seen in the debugger and in a temporary text box I added.
When it comes to making the array the DataGrid  DataSource, it looks like the grid is accessed and rows are added, but at the same time there is a big error message with the header:
"Array was not a one-dimensional array" followed by a list of errors, followed by the text: "To replace this default dialog please handle the DataError event."
I expected DataGridView to handle 2-dimentionals, am I wrong?
What am I doing wrong?
Sam
 
 
using
System;
using
System.Windows.Forms;
using
Microsoft.Office.Interop.Excel;
using
System.Runtime.InteropServices;
 

namespace
ExcelToDataGrid
{
public partial class Form1 : Form
{
private string fileName;
private string startCol = "A";
private string endCol = "C";
private int startRow = 1;
private int endRow = 3;
private Worksheet ObjWorkSheet = null;
private OpenFileDialog openFileDialog = new OpenFileDialog();
private int sheet = 1;
private Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
public Form1()
{
InitializeComponent();
}
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
openFileDialog.InitialDirectory =
"C:\\";
openFileDialog.Filter =
"Excel Files (*.xls)|*.xls|All Files (*.*)|*.*";
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
this.fileName = (openFileDialog.FileName);
this.currentSheet = GetExcel(fileName, sheet);
loadExcelData(currentSheet, startCol, startRow, endCol, endRow);
}
else MessageBox.Show("ERROR: File not found!");
}
private Worksheet GetExcel(string fileName, int sheet)
{
if (ObjExcel == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.
Application.Exit();
}
Microsoft.Office.Interop.Excel.
Workbook ObjWorkBook;
Microsoft.Office.Interop.Excel.
Worksheet ObjWorkSheet;
ObjWorkBook = ObjExcel.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ObjWorkSheet = (Microsoft.Office.Interop.Excel.
Worksheet)ObjWorkBook.Sheets[sheet];
return ObjWorkSheet;
}
public Worksheet currentSheet
{
get { return ObjWorkSheet; }
set { ObjWorkSheet = value; }
}
public void loadExcelData(Worksheet currentSheet, string startCol, int startRow, string endCol, int endRow)
{
System.
Array myExcelValues;
try
{
Microsoft.Office.Interop.Excel.
Range range = currentSheet.get_Range(this.startCol + this.startRow, this.endCol + this.endRow);
myExcelValues = (System.
Array)range.Cells.Value2;
foreach (object obj in myExcelValues)
richTextBox1.Text = richTextBox1.Text +
"\n" + Convert.ToString(obj);
ExcelGridView1.DataSource = myExcelValues;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
ObjExcel.Quit();
Marshal.ReleaseComObject(ObjExcel);
System.Windows.Forms.
Application.Exit();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void CloseToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
ObjExcel.Quit();
Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
}
}

Answers (1)