sudhakarthikeyan Ответов: 1

Как установить запись строки в качестве заголовка столбца из excel на языке C#

Мой выход выглядит следующим образом


Farmerid FarmerName Surveyquestions

1 TestA "Legto":"2","Roeto":"3","Vignto":"4"
2 TestB "Legto":"1","Roeto":"2","Zento":"3"


Из вышесказанного я хочу получить следующий результат

Farmerid FarmerName Legto Roeto Vignto

Теста 1 2 3 4
2 Тестб 1 2 3


Мой код aspx выглядит следующим образом

if (filterCriteria == "2" && dataFormat == "3")
{
если (!Directory.Exists(HttpContext.Current.Сервер.MapPath("~/") + "отчеты"))
{
Каталог.CreateDirectory(HttpContext.Current.Сервер.MapPath("~/") + "отчеты");
}
ВАР имя = "FarmerReportsSurveyQuestions" + Датавремя.Сейчас.Метод toString("ыыыы_ММ_ДД__АА") + ".XLSX-файл";
var outputDir = HttpContext.Current.Сервер.MapPath("~") + "\\отчеты\\";


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

Richard Deeming

Репост
https://www.codeproject.com/Questions/1241432/How-to-split-rows-in-excel[^]

Если вы хотите обновить свой вопрос, чтобы добавить недостающую информацию, Нажмите зеленую ссылку "улучшить вопрос" и отредактируйте свой вопрос. НЕ опубликуйте свое обновление как новый вопрос!

1 Ответов

Рейтинг:
1

Maciej Los

Что ж...

Прежде чем вы начнете экспортировать данные из datatable в Excel, вы должны обновить свой datatable до желаемого результата. Итак, после нижеприведенной строки:

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

добавить это:
//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);

Выше код изменяет исходный объект DataTable в форме ниже:
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

Теперь, у вас есть, чтобы обновить объект DataTable путем установки значений в соответствующих столбцах:
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"]);

Результатом выполнения приведенного выше кода является:
Farmerid FarmerName Legto Roeto Vignto Zento
1 TestA 2 3 4 null 
2 TestB 1 2 null 3


Теперь вы можете перебирать строки и столбцы в datatable, чтобы иметь возможность экспортировать данные в Excel. Например:
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];
}


Удачи вам!

Wendelius

Хороший ответ!

Maciej Los

Спасибо, Мика.