如何在C＃中将行条目设置为excel的列标题 [英] How to set row entry as column header from excel in C#
本文介绍了如何在C＃中将行条目设置为excel的列标题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的产品如下
Farmerid FarmerName Surveyquestions
1 TestALegto：2，Roeto：3，Vignto：4
2 TestBLegto：1，Roeto：2 ，Zento：3
从上面我想要输出如下
Farmerid FarmerName Legto Roeto Vignto
1 TestA 2 3 4
2 TestB 1 2 3
我的aspx代码如下
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）;
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
What I have tried:
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);
<pre> 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
推荐答案
嗯。 ..
在开始将数据从datatable导出到Excel之前，必须将数据表更新为所需的输出。因此，在以下行之后：
Well...
Before you start exporting data from datatable to Excel, you have to update your datatable to desired output. So, after below line:
DataTable dtFarmerFarmReports = dal.DAL_GetFarmer_FarmReports_All_ForExcel(originname, hdnSeasonalYear.Value.ToString());
添加：
add this:
//get unique column names from [SurveyQuestions] field
var othercols = dtFarmerFarmReports.AsEnumerable()
.SelectMany(x=>x.Field<string>("SurveyQuestions").Split(new string[]{":", ","}, StringSplitOptions.RemoveEmptyEntries)
.Where((y,i)=>i%2==0))
.Distinct()
.Select(x=>new DataColumn(x, typeof(string)))
.ToList();
//add into source data table
foreach(DataColumn oc in othercols)
dtFarmerFarmReports.Columns.Add(oc);
上面的代码将原始数据表更改为以下形式：
Above code changes your original datatable to below form:
Farmerid FarmerName SurveyQuestions Legto Roeto Vignto Zento
1 TestA Legto:2,Roeto:3,Vignto:4 null null null null
2 TestB Legto:1,Roeto:2,Zento:3 null null null null
现在，你有通过将值设置到相应的列来更新数据表：
Now, you have to update your datatable by setting values into corresponding columns:
foreach(DataRow dr in dtFarmerFarmReports.Rows)
{
foreach(DataColumn oc in othercols)
dr.SetField(oc, dr.Field<string>("SurveyQuestions").Split(',')
.Where(y=>y.Contains(oc.ColumnName)).Select(z=>z.Split(':')[1]).FirstOrDefault());
}
//remove unneeded column
dtFarmerFarmReports.Columns.Remove(dtFarmerFarmReports.Columns["SurveyQuestions"]);
执行上述代码的结果是：
The result of executing above code is:
Farmerid FarmerName Legto Roeto Vignto Zento
1 TestA 2 3 4 null
2 TestB 1 2 null 3
现在，您可以循环遍历数据表中的行和列，以便能够将数据导出到Excel中。例如：
Now, you can loop through the rows and cols in datatable to be able to export data into Excel. For example:
for(int r=0; r<dtFarmerFarmReports.Rows.Count; r++)
{
DataRow dr = dtFarmerFarmReports.Rows[r];
for(int c=0; c<dr.Table.Columns.Count; c++)
worksheet.Cells[r+2, c+1].Value = dr[c];
}
祝你好运！
Good luck!
这篇关于如何在C＃中将行条目设置为excel的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
