Click here to Skip to main content
15,891,597 members
Search within:


Index was outside the bound of the array
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#3.5
From the above i want output as follows

In table i have record as follows

Farmer id FarmerName FarmDetailsdata

1 TestA "Área de Milho":"1","Área de Mandioca":"2","Área de
Feijão":"2","Plantou alogdao no ano?":"Nao"

2 TestB "Área de Milho":"2","Área de Mandioca":"3","Área de
Feijão":"4","Plantou alogdao no ano?":"Yes"


I want to get the below output as follows

Farmerid NameArea de Milho Area de Mandioca Area de Feijao Plantou alogdao no ano?

1 TestA 1 2 2 Nao
2 TestB 2 3 4 Yes

My aspx code as follows

if (filterCriteria == "2" && dataFormat == "3")
{
if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/") + "reports"))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/") + "reports");
}
var fileName = "FarmerReportsSurveyQuestions" + DateTime.Now.ToString("yyyy_MM_dd__hh") + ".xlsx";
var outputDir = HttpContext.Current.Server.MapPath("~") + "\\reports\\";


var file = new FileInfo(outputDir + fileName);

try
{
using (var package = new ExcelPackage(file))
{

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("FARMER/FARM REPORTS");
worksheet.TabColor = Color.Green;
worksheet.DefaultRowHeight = 12;
worksheet.HeaderFooter.FirstFooter.LeftAlignedText = string.Format("Generated: {0}", DateTime.Now.ToShortDateString());

DataTable dtFarmerFarmReports = dal.DAL_GetFarmer_FarmReports_All_ForExcel(originname, hdnSeasonalYear.Value.ToString());

var othercols = dtFarmerReports.AsEnumerable().SelectMany(x => x.Field<string>("farm_detailsdata").Split(new string[] { ":", "," },
StringSplitOptions.RemoveEmptyEntries).Where((y, i) => i % 2 == 0)).Distinct().Select(x => new DataColumn(x, typeof(string))).ToList(); //added


foreach (DataColumn oc in othercols)
dtFarmerReports.Columns.Add(oc);

foreach (DataRow dr in dtFarmerReports.Rows)
{
foreach (DataColumn oc in othercols)
dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',')
.Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());
}

dtFarmerReports.Columns.Remove(dtFarmerReports.Columns["farm_detailsdata"]);

for (int r = 0; r < dtFarmerReports.Rows.Count; r++)
{
DataRow dr = dtFarmerReports.Rows[r];
for (int c = 0; c < dr.Table.Columns.Count; c++)
worksheet.Cells[r + 2, c + 1].Value = dr[c];
}


if (dtFarmerReports.Rows.Count > 0)
{
using (var range = worksheet.Cells[2, 1, 2, 11])
{
range.Style.Font.Bold = true;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Font.Color.SetColor(Color.Black);
range.Style.Fill.BackgroundColor.SetColor(Color.Green);
range.AutoFitColumns();
}

worksheet.Cells["A1:AY1"].Merge = true;
worksheet.Cells["A1:AY1"].Value = "FARMER/FARM DATA";
worksheet.Row(1).Height = 35;
using (var range = worksheet.Cells[1, 1, 1, 11])
{
range.Style.Font.Bold = true;
range.Style.Font.Size = 22;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightSeaGreen);
range.Style.Font.Color.SetColor(Color.Black);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.ShrinkToFit = false;
}

worksheet.Cells[2, 1].Value = "FARMER ID";
worksheet.Cells[2, 2].Value = "NAME";
worksheet.Cells[2, 3].Value = "NO. OF FARMS";
worksheet.Cells[2, 4].Value = "PRODUCTION ESTIMATION";
worksheet.Cells[2, 5].Value = "FARM NO";
worksheet.Cells[2, 6].Value = "DISTRICT";
worksheet.Cells[2, 7].Value = "SUB DISTRICT";
worksheet.Cells[2, 8].Value = "SECTION";
worksheet.Cells[2, 9].Value = "ZONE";
worksheet.Cells[2, 10].Value = "FIELD STAFF";
worksheet.Cells[2, 11].Value = "FARM DETAILS DATA";

for (int j = 0; j < dtFarmerReports.Rows.Count; j++)
{
int j1 = (j + 3);
farmerid = dtFarmerReports.Rows[j]["farmer_id"].ToString();
worksheet.Cells[(j1), 1].Value = dtFarmerReports.Rows[j]["farmer_id"].ToString();
worksheet.Cells[(j1), 2].Value = dtFarmerReports.Rows[j]["farmer_name"].ToString();
worksheet.Cells[(j1), 3].Value = dtFarmerReports.Rows[j]["nooffarms"].ToString();
worksheet.Cells[(j1), 4].Value = dtFarmerReports.Rows[j]["produtionestimation"].ToString();
worksheet.Cells[(j1), 5].Value = dtFarmerReports.Rows[j]["farm_no"].ToString();
worksheet.Cells[(j1), 6].Value = dtFarmerReports.Rows[j]["districtname"].ToString();
worksheet.Cells[(j1), 7].Value = dtFarmerReports.Rows[j]["subdistrictname"].ToString();
worksheet.Cells[(j1), 8].Value = dtFarmerReports.Rows[j]["sectionname"].ToString();
worksheet.Cells[(j1), 9].Value = dtFarmerReports.Rows[j]["zonename"].ToString();
worksheet.Cells[(j1),10].Value = dtFarmerReports.Rows[j]["fieldstaffname"].ToString();
worksheet.Cells[(j1),11].Value = dtFarmerReports.Rows[j]["farm_detailsdata"].ToString();
}

worksheet.Cells["A2:AY2"].AutoFilter = true;
}
else
{
worksheet.Cells["A1:I1"].Merge = true;
worksheet.Cells["A1:I1"].Value = "NO FARMER/FARM DATA";
worksheet.Row(1).Height = 35;
using (var range = worksheet.Cells[1, 1, 1, 11])
{
range.Style.Font.Bold = true;
range.Style.Font.Size = 18;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Red);
range.Style.Font.Color.SetColor(Color.Black);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.ShrinkToFit = false;
}
}

worksheet.Cells.AutoFitColumns();
package.Workbook.Properties.Title = "Farmer Reports";
package.Workbook.Properties.Author = "Olam";
package.Workbook.Properties.Company = "Olam";
package.Save();
modalPopupExport.Hide();
}
Response.Clear();
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.WriteFile(outputDir + fileName);
HttpContext.Current.ApplicationInstance.CompleteRequest();
Response.End();
}
catch (Exception ex)
{
BindDetails();
this.modalPopupExport.Hide();
showStatusTrue.Style.Add("display", "none");
showStatusTrue.InnerHtml = "";
showStatusWarning.Style.Add("display", "none");
showStatusWarning.InnerHtml = "";
showStatusAlready.Style.Add("display", "none");
showStatusAlready.InnerHtml = "";
showStatusFalse.Style.Add("display", "block");
showStatusFalse.InnerHtml = "Something went wrong while export";
farmerid = "" + farmerid;
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "tmp", "myFunction();", false);
Logger log = new Logger();
log.WriteToErrorLog("FARMER REPORTS EXCEL", "EX", ex.Message.ToString(), "", "");
}
}


When i run the above code shows error as follows

Index was outside the bounds of the array.

The error shows in below line as follows


dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',').Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());

please help me what is the mistake in my above line code

What I have tried:

From the above i want output as follows

  In table i have record as follows

Farmer id   FarmerName     FarmDetailsdata

1             TestA     "Área de Milho":"1","Área de Mandioca":"2","Área de 
                         Feijão":"2","Plantou alogdao no ano?":"Nao"

2             TestB     "Área de Milho":"2","Área de Mandioca":"3","Área de 
                         Feijão":"4","Plantou alogdao no ano?":"Yes"


I want to get the below output as follows

Farmerid NameArea de Milho  Area de Mandioca  Area de Feijao   Plantou alogdao no ano?

1       TestA             1           2                 2                 Nao
2      TestB              2           3                 4                 Yes

My aspx code as follows

if (filterCriteria == "2" && dataFormat == "3")
{
if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/") + "reports"))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/") + "reports");
}
var fileName = "FarmerReportsSurveyQuestions" + DateTime.Now.ToString("yyyy_MM_dd__hh") + ".xlsx";
var outputDir = HttpContext.Current.Server.MapPath("~") + "\\reports\\";


var file = new FileInfo(outputDir + fileName);

 try
                        {
                            using (var package = new ExcelPackage(file))
                            {

                                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("FARMER/FARM REPORTS");
                                worksheet.TabColor = Color.Green;
                                worksheet.DefaultRowHeight = 12;
                                worksheet.HeaderFooter.FirstFooter.LeftAlignedText = string.Format("Generated: {0}", DateTime.Now.ToShortDateString());

                                DataTable dtFarmerFarmReports = dal.DAL_GetFarmer_FarmReports_All_ForExcel(originname, hdnSeasonalYear.Value.ToString());

                                 var othercols = dtFarmerReports.AsEnumerable().SelectMany(x => x.Field<string>("farm_detailsdata").Split(new string[] { ":", "," },
                                StringSplitOptions.RemoveEmptyEntries).Where((y, i) => i % 2 == 0)).Distinct().Select(x => new DataColumn(x, typeof(string))).ToList(); //added


                                foreach (DataColumn oc in othercols)   
                                    dtFarmerReports.Columns.Add(oc);   

                                foreach (DataRow dr in dtFarmerReports.Rows) 
                                {
                                    foreach (DataColumn oc in othercols)
                                        dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',')
                                                .Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());
                                } 

                                dtFarmerReports.Columns.Remove(dtFarmerReports.Columns["farm_detailsdata"]); 

                                for (int r = 0; r < dtFarmerReports.Rows.Count; r++) 
                                {
                                    DataRow dr = dtFarmerReports.Rows[r];
                                    for (int c = 0; c < dr.Table.Columns.Count; c++)
                                        worksheet.Cells[r + 2, c + 1].Value = dr[c];
                                } 

                                
                                if (dtFarmerReports.Rows.Count > 0)
                                {
                                    using (var range = worksheet.Cells[2, 1, 2, 11])
                                    {
                                        range.Style.Font.Bold = true;
                                        range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        range.Style.Font.Color.SetColor(Color.Black);
                                        range.Style.Fill.BackgroundColor.SetColor(Color.Green);
                                        range.AutoFitColumns();
                                    }

                                    worksheet.Cells["A1:AY1"].Merge = true;
                                    worksheet.Cells["A1:AY1"].Value = "FARMER/FARM DATA";
                                    worksheet.Row(1).Height = 35;
                                    using (var range = worksheet.Cells[1, 1, 1, 11])
                                    {
                                        range.Style.Font.Bold = true;
                                        range.Style.Font.Size = 22;
                                        range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        range.Style.Fill.BackgroundColor.SetColor(Color.LightSeaGreen);
                                        range.Style.Font.Color.SetColor(Color.Black);
                                        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                        range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                        range.Style.ShrinkToFit = false;
                                    }

                                    worksheet.Cells[2, 1].Value = "FARMER ID";
                                    worksheet.Cells[2, 2].Value = "NAME";
                                    worksheet.Cells[2, 3].Value = "NO. OF FARMS";
                                    worksheet.Cells[2, 4].Value = "PRODUCTION ESTIMATION";
                                    worksheet.Cells[2, 5].Value = "FARM NO";
                                    worksheet.Cells[2, 6].Value = "DISTRICT";
                                    worksheet.Cells[2, 7].Value = "SUB DISTRICT";
                                    worksheet.Cells[2, 8].Value = "SECTION";
                                    worksheet.Cells[2, 9].Value = "ZONE";
                                    worksheet.Cells[2, 10].Value = "FIELD STAFF";
                                    worksheet.Cells[2, 11].Value = "FARM DETAILS DATA";

                                    for (int j = 0; j < dtFarmerReports.Rows.Count; j++)
                                    {
                                        int j1 = (j + 3);
                                        farmerid = dtFarmerReports.Rows[j]["farmer_id"].ToString();
                                        worksheet.Cells[(j1), 1].Value = dtFarmerReports.Rows[j]["farmer_id"].ToString();
                                        worksheet.Cells[(j1), 2].Value = dtFarmerReports.Rows[j]["farmer_name"].ToString();
                                        worksheet.Cells[(j1), 3].Value = dtFarmerReports.Rows[j]["nooffarms"].ToString();
                                        worksheet.Cells[(j1), 4].Value = dtFarmerReports.Rows[j]["produtionestimation"].ToString();
                                        worksheet.Cells[(j1), 5].Value = dtFarmerReports.Rows[j]["farm_no"].ToString();
                                        worksheet.Cells[(j1), 6].Value = dtFarmerReports.Rows[j]["districtname"].ToString();
                                        worksheet.Cells[(j1), 7].Value = dtFarmerReports.Rows[j]["subdistrictname"].ToString();
                                        worksheet.Cells[(j1), 8].Value = dtFarmerReports.Rows[j]["sectionname"].ToString();
                                        worksheet.Cells[(j1), 9].Value = dtFarmerReports.Rows[j]["zonename"].ToString();
                                        worksheet.Cells[(j1),10].Value = dtFarmerReports.Rows[j]["fieldstaffname"].ToString();
                                        worksheet.Cells[(j1),11].Value = dtFarmerReports.Rows[j]["farm_detailsdata"].ToString();
                                    }

                                    worksheet.Cells["A2:AY2"].AutoFilter = true;
                                }
                                else
                                {
                                    worksheet.Cells["A1:I1"].Merge = true;
                                    worksheet.Cells["A1:I1"].Value = "NO FARMER/FARM DATA";
                                    worksheet.Row(1).Height = 35;
                                    using (var range = worksheet.Cells[1, 1, 1, 11])
                                    {
                                        range.Style.Font.Bold = true;
                                        range.Style.Font.Size = 18;
                                        range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                        range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        range.Style.Fill.BackgroundColor.SetColor(Color.Red);
                                        range.Style.Font.Color.SetColor(Color.Black);
                                        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                        range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                        range.Style.ShrinkToFit = false;
                                    }
                                }

                                worksheet.Cells.AutoFitColumns();
                                package.Workbook.Properties.Title = "Farmer Reports";
                                package.Workbook.Properties.Author = "Olam";
                                package.Workbook.Properties.Company = "Olam";
                                package.Save();
                                modalPopupExport.Hide();
                            }
                            Response.Clear();
                            Response.ContentType = ContentType;
                            Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);
                            Response.WriteFile(outputDir + fileName);
                            HttpContext.Current.ApplicationInstance.CompleteRequest();
                            Response.End();
                        }
                        catch (Exception ex)
                        {
                            BindDetails();
                            this.modalPopupExport.Hide();
                            showStatusTrue.Style.Add("display", "none");
                            showStatusTrue.InnerHtml = "";
                            showStatusWarning.Style.Add("display", "none");
                            showStatusWarning.InnerHtml = "";
                            showStatusAlready.Style.Add("display", "none");
                            showStatusAlready.InnerHtml = "";
                            showStatusFalse.Style.Add("display", "block");
                            showStatusFalse.InnerHtml = "Something went wrong while export";
                            farmerid = "" + farmerid;
                            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "tmp", "myFunction();", false);
                            Logger log = new Logger();
                            log.WriteToErrorLog("FARMER REPORTS EXCEL", "EX", ex.Message.ToString(), "", "");
                        }
                    }


          When i run the above code shows error as follows
       
             Index was outside the bounds of the array.
  
        The error shows in below line as follows


     dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',').Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());

       please help me what is the mistake in my above line code
Posted
Updated 29-Apr-18 21:55pm
Add a Solution
Comments
Richard MacCutchan 30-Apr-18 3:51am    
Please edit your question and remove the code not connected to the problem, and show exactly where the problem occurred.

In simple terms you are trying to access an array element with an index value that is less than zero, or greater than the limit of the array.

1 solution

dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',').Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());

The simple reason is that the string you split does not contain a ':' character - so teh Split return array does not contain two or more elements, so your access of element 1 will fail.

There isn't anything we can do to fix that: it's a data problem of some form, and you need to find out how data without your colon got into your system.
 
Share this answer
 
Posted
Comments
[no name] 30-Apr-18 6:38am    
i checked already we split the below line

var othercols = dtFarmerReports.AsEnumerable().SelectMany(x => x.Field<string>("farm_detailsdata").Split(new string[] { ":", "," },

Then again why the below line is needed

dr.SetField(oc, dr.Field<string>("farm_detailsdata").Split(',').Where(y => y.Contains(oc.ColumnName)).Select(z => z.Split(':')[1]).FirstOrDefault());
OriginalGriff 30-Apr-18 6:46am    
Don't forget that Split with an array of characters splits on ALL of those characters: It generates three strings from this: "aaa,bbb:ccc". If your data originally contains "aaa:bbb,ccc" then your first split creates
aaa:bbb
ccc
And your second split will then fail on the second of these and give you the error you get.
Check your data! :laugh:
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
System.indexoutofrangeexception: 'index was outside the bounds of the array.'
Index was outside the bounds of the array
How do I resolve this: 'index was outside the bounds of the array.'
Index was outside the bounds of the array.
Byte[] password index was outside the bounds of the array
Index was outside the bounds of the array. in c#
Index was outside the bounds of the array. in c#
Index was outside the bounds of the array.
Indexing out of bounds with arrays
index was outside the bounds of the array

Advertise
Privacy
Cookies
Terms of Use
Last Updated 30 Apr 2018
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900