CODEWITHSUNDEEP
c#asp.netexceldatatable
heb-NR
heb-NR

Reputation: 327

Dynamically create data table based on incoming Excel data in C#?

I'm trying to import an excel sheet into a data table in C#. I keep getting an out of bounds exception because the columns value is being hard coded by me.

for (int c = 0; c <40; c++) { ... }

So rather than hard coding this and having a bunch of empty columns listed on my data table, is there a way to count all the columns being passed in before I create the table? Thereby allowing me to create the table dynamically around the incoming data?

Below is my code.

 if (fileUpload.HasFile)
      {
        DataTable dt = new DataTable();
        for (int c = 0; c <40; c++) { dt.Columns.Add(c.ToString(), typeof(string)); }
        using (Stream fileStream = fileUpload.PostedFile.InputStream)
        using (StreamReader sr = new StreamReader(fileStream))
        {
          string a = null;
          while ((a = sr.ReadLine()) != null)
          {
            dt.Rows.Add(a.Split(new char[] { ',' }).ToArray());
          }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();

Upvotes: 0

Views: 3234

Answers (1)

gmiley
gmiley

Reputation: 6604

Try using something like this instead:

        DataTable dt;
        using (Stream fileStream = fileUpload.PostedFile.InputStream)
        using (StreamReader sr = new StreamReader(fileStream))
        {
            string a = null;
            while ((a = sr.ReadLine()) != null)
            {
                string[] columns = a.Split(',');
                if (dt == null)
                {
                    dt = new DataTable();
                    for (int i = 0; i < columns.Count(); i++)
                        dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
                }
                dt.Rows.Add(columns);
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();

So long as the first row contains the maximum number of columns that the rest of the data has, you should be all set.

If the first row is column names, you could modify the code to skip the dt.Rows.Add(columns) on the first pass, and replace the i.ToString() with columns[i].ToString() to name the DataColumn properly.

If you columns/values are also surrounded by quotation marks (") then you could modify the while statement above to look like this:

while ((a = sr.ReadLine()) != null)
{
    a = a.Substring(1, a.Length - 2);
    string[] columns = a.Split(new string[] { "\",\"" }, StringSplitOptions.None);
    if (dt == null)
    {
        dt = new DataTable();
        for (int i = 0; i < columns.Count(); i++)
            dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
    }
    dt.Rows.Add(a.Split(new char[] { ',' }).ToArray());
}

That will strip the first and last " from the overall string, then split on ",". You might want to play around with it some though, and add code to figure out how your fields are delimited, or have some input fields that you can specify. That is, if your input is variable in how it is defined.

Upvotes: 1

Related Questions