C＃import excel to database - system.indexoutofrangeexception是什么意思？ [英] C# import excel to database - what is the system.indexoutofrangeexception means?

问题描述

喜！当我运行下面的代码时，它显示一个错误，如System.Data.dll中发生类型'System.IndexOutOfRangeException'未处理的异常'

附加信息：找不到第2列。



有谁能帮我找到解决方案？

hi! when I run Below code it shows an error like "An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll
Additional information: Cannot find column 2."

Can anyone help me to find the solution ?

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 Excel;
using System.Data.Sql;

namespace ProjectTrackingExcel
{
public partial class Form2 : Form
    { 
 private void button3_Click(object sender, EventArgs e)
        {
            OpenFileDialog op = new OpenFileDialog();
            op.Filter = "Excel filters|*.xls;*.xlsx;*.xlxm|Excel2003*xlsx)|*.xlsx";
            if (op.ShowDialog() == DialogResult.Cancel)
                return;

            FileStream strm = File.Open(op.FileName,FileMode.Open,FileAccess.Read); 
            IExcelDataReader exceldata ExcelReaderFactory.CreateBinaryReader(strm);

            DataSet result = exceldata.AsDataSet();

            DataClasses1DataContext con = new DataClasses1DataContext();

            foreach (DataTable table in result.Tables) {

                foreach (DataRow dr in table.Rows){
                   
                  Proj addTable = new Proj (){
                  
                   id = Convert.ToString(dr[0]),
                   name = Convert.ToString(dr[1]),
                   city = Convert.ToString(dr[2]),
                   gender = Convert.ToString(dr[3])
                  
                  };

                  con.Projs.InsertOnSubmit(addTable)  
                }
                 }
                    con.SubmitChanges();
                    exceldata.Close();
                    strm.Close();

            MessageBox.Show("Data Successfully added to the database");

            }    
        }
}

我尝试过：



我也提到了你网站的一些解决方案。但仍然会出现同样的问题。

What I have tried:

I referred some solutions of your site as well. But still, occurs the same problem.

推荐答案

索引超出范围意味着：您正在尝试使用索引值访问数组元素大于最大索引值。

如果声明一个整数数组：

"Index out of range" means just that: you are trying to access an array element by using an index value that is larger than the maximum index value.
If you declare an array of of integers:

int[] ints = new int[3];

然后你可以使用索引快乐地访问它：

Then you can access it happily using the index:

int a = ints[0];
int b = ints[1];
int c = ints[2];

但是如果你尝试使用负数或大于2的值，您将得到错误索引超出范围，因为该索引值没有元素。



你的代码使用魔术数字 - 这通常是一个坏主意 - 并且几乎可以肯定这一行导致了这个问题：

But if you try to use a negative number, or a value greater than two you will get the error "Index out of range" because there is no element at that index value.

Your code uses "magic numbers" - which is generally a bad idea - and it's almost certainly this line that causes the problem:

city = Convert.ToString(dr[2]),

由于添加信息表明它是第2列，因此无法找到。

I首先使用调试器找出DataReader中的内容：在行上放置一个断点

Since the addition info says it's "column 2" it can't find.
I'd start by using the debugger to find out what is in the DataReader: put a breakpoint on the line

Proj addTable = new Proj ()

并在调试器中运行你的应用程序。

当它到达那一行时，它会停止，让你看看到底是什么在变量中。

查看您的数据：这可能与问题有关 - 而且您需要它读取的实际数据来准确识别发生了什么。



抱歉，我们不能为你做任何事情：我们没有你的数据文件，所以我们不能在这里复制您的问题！

And run your app in the debugger.
When it hits that line, it will stop, and let you look at exactly what is in the variables.
Look to your data: it's probably something to do with the problem - and you need the actual data it read to identify exactly what is going on.

Sorry, but we can't do any of that for you: we don't have your data files, so we can't duplicate your problem here!

您的问题似乎在以下代码中 -

Your problem seems to be in following piece of code-

Proj addTable = new Proj (){

id = Convert.ToString(dr[0]),
name = Convert.ToString(dr[1]),
city = Convert.ToString(dr[2]),
gender = Convert.ToString(dr[3])

};

在尝试访问数据行之前，请检查该数据行是否确实存在。

您可以查看类似的内容 -

Before trying to access a column of data row check if that datarow really exists.
You can check with something like-

int noOfCols = dr.Table.Columns.Count;

对于解决方法，你可以这样做 -

For a workaround, you can do like-

Proj addTable = new Proj (){

id = dr.Table.Columns.Count>0 ? Convert.ToString(dr[0]) : String.Empty,
name = dr.Table.Columns.Count>1 ? Convert.ToString(dr[1]) : String.Empty,
city = dr.Table.Columns.Count>2 ? Convert.ToString(dr[2]) : String.Empty,
gender = dr.Table.Columns.Count>3 ? Convert.ToString(dr[3]) : String.Empty

};

注意：我没有测试过此代码，可能包含语法错误。



希望，它有帮助：）

Note: I haven't tested this code and may contain syntax errors.

Hope, it helps :)

这篇关于C＃import excel to database - system.indexoutofrangeexception是什么意思？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！