You must Sign In to post a response.
  • Category: ASP.NET

    Change the row into column in excel using c# code

    My ouput as follows


    Farmerid FarmerName Surveyquestions

    1 TestA Legto:2,Roeto:3,Vignto:4

    2 TestB Legto:1,Roeto:2,Zento:3


    From the above i want output as follows

    Farmerid FarmerName Legto Roeto Vignto

    1 TestA 2 3 4
    2 TestB 1 2 3


    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());
    if (dtFarmerFarmReports.Rows.Count > 0)
    {
    using (var range = worksheet.Cells[2, 1, 2, 3])
    {
    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:K1"].Merge = true;
    worksheet.Cells["A1:K1"].Value = "FARMER/FARM DATA";
    worksheet.Row(1).Height = 35;
    using (var range = worksheet.Cells[1, 1, 1, 3])
    {
    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 = "";

    for (int j = 0; j < dtFarmerFarmReports.Rows.Count; j++)
    {
    int j1 = (j + 3);
    farmerid = dtFarmerFarmReports.Rows[j]["farmer_id"].ToString();
    worksheet.Cells[(j1), 1].Value = dtFarmerFarmReports.Rows[j]["farmer_id"].ToString();
    worksheet.Cells[(j1), 2].Value = dtFarmerFarmReports.Rows[j]["farmer_name"].ToString();
    worksheet.Cells[(j1), 3].Value = dtFarmerFarmReports.Rows[j][""].ToString();

    <pre> }

    worksheet.Cells["A2:C2"].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(), "", "");
    }
    }


    from my above asp.net code what change i have to made to get below output

    Farmerid FarmerName Legto Roeto Vignto

    1 TestA 2 3 4
    2 TestB 1 2 3
  • #769582
    looks good

    good.pdf

    Delete Attachment


  • Sign In to post your comments