Change the row into column in excel using c# code
My ouput as followsFarmerid 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
