Как назначить список ячейке (выпадающий формат) в closedXML при экспорте в Excel

Question

Я просто пытаюсь экспортировать Datatable (Datatable не имеет никаких данных - только заголовок) Кроме того, у меня есть List<String> который я хочу добавить в столбец, так что после экспорта в Excel этот столбец (все ячейки, кроме заголовка) должен содержать данные списка в виде Dropdown формат.

Я много гуглил и не мог найти решения. Ниже приведены ссылки, которые я посетил, но не повезло.

Переполнение стека

Codeplex - ClosedXML

И ниже это то, что я пробовал до сих пор.

    private void ExcelExport(DataTable dt, GridView Template)
    {
        bool isListRequired = false;
        List<string> groupCodeList = new List<string>(); ;
        Template.DataBind();
        if (Template.HeaderRow != null)
        {
            foreach (TableCell cell in Template.HeaderRow.Cells)
            {
                if (cell.Text == "ActivityGroup_Code")
                {
                    isListRequired = true;
                    groupCodeList = db.PMS_M_ActivityGroup.Select(a => a.ActivityGroup_Code).ToList();
                }
                    dt.Columns.Add(cell.Text);
            }

            var workbook = new XLWorkbook();
            var returnValue = workbook.AddWorksheet(dt);
            var worksheet = workbook.Worksheet(1);
            if (isListRequired)
            {
                //worksheet.Cell("E2").DataValidation.List(string.Join("",groupCodeList.ToArray()));
                //worksheet.Cell(2,5).InsertData(groupCodeList);

                // Trying to add a list to the Cell (E2) - and applying datavalidation to the Column (5th column - E Range)
                // Dropdown is created in excel(with blank) but data is not population to that dropdown
                worksheet.Cell(2, 5).InsertData(groupCodeList);
                worksheet.Column(5).SetDataValidation().List(string.Join("", groupCodeList.ToArray()));
            }

            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=ExcelFormat.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                workbook.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }

И код, где я вызываю этот метод

 List<PMS_M_ActivityGroup> activitygroup = new List<PMS_M_ActivityGroup>();
 activitygroup = db.PMS_M_ActivityGroup.Select(a => a).ToList();
 DataTable dt2 = new DataTable("Excel Template");
 GridView Template2 = new GridView();
 Template2.DataSource = activitygroup;
 ExcelExport(dt2, Template2);

Прошу вас предоставить мне решение, как добиться требуемой функциональности.

Спасибо

Answer 1

Я получил решение. Это не так просто, но нам нужно сделать несколько трюков для достижения этой функциональности. Размещение здесь - может быть полезно для других разработчиков, которые используют специально ClosedXML библиотека.

Итак, что нам нужно сделать, это: нам нужно создать отдельный (второй) лист, применить DataValidation в столбец 1-го листа, где появится раскрывающийся список.

private void ExcelExportValidation(DataTable dt, GridView Template)
{
    try
    {
        bool isListRequired = false;
        List<string> groupCodeList = new List<string>(); ;
        Template.DataBind();
        if (Template.HeaderRow != null)
        {
           foreach (TableCell cell in Template.HeaderRow.Cells)
           {
               if (cell.Text == "ActivityGroup_Code")
               {
                   isListRequired = true;
                   groupCodeList = db.PMS_M_ActivityGroup.Select(a => a.ActivityGroup_Code).ToList();
               }
               dt.Columns.Add(cell.Text);
           }

           var workbook = new XLWorkbook();
           var returnValue = workbook.AddWorksheet(dt);
           var worksheet = workbook.Worksheet(1);
           if (isListRequired)
           {
               var tempDt = Helper.ConvertListToDataTable(groupCodeList);
               tempDt.TableName = "Sheet1";
               var returnValue2 = workbook.AddWorksheet(tempDt);
               var worksheet2 = workbook.Worksheet(2);
               int lastCellNo = groupCodeList.Count + 1;                       
                     worksheet.Column(5).SetDataValidation().List(worksheet2.Range("A2:A" + lastCellNo), true);                                               
            }
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=ExcelFormat.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                workbook.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
    catch (Exception ex)
    {
        ExceptionLogging.SendErrorToText(ex);
    }
}

Answer 2

@Chandan, безусловно, дал правильное руководство для ответа. Я публикую фрагмент кода автономного решения, который сохраняет файл Excel на диск:

var workbook = new XLWorkbook();

DataTable userData = new DataTable("Sheet1");
userData.Columns.Add("Master Column");
workbook.AddWorksheet(userData);
var worksheet = workbook.Worksheet(1);

DataTable validationTable = new DataTable();
validationTable.Columns.Add("DropDownItems");
validationTable.TableName = "Sheet2";

DataRow dr = validationTable.NewRow();
dr["DropDownItems"] = "Item1";
validationTable.Rows.Add(dr);

dr = validationTable.NewRow();
dr["DropDownItems"] = "Item2";
validationTable.Rows.Add(dr);

dr = validationTable.NewRow();
dr["DropDownItems"] = "Item3";
validationTable.Rows.Add(dr);

var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(1).SetDataValidation().List(worksheet2.Range("A2:A4"), true);

//optional: you can hide the data validation sheet from your users if you want
//worksheet2.Hide();

workbook.SaveAs(@"C:\myworkbook.xlsx");

Это создает раскрывающийся список в Sheet1, как показано ниже: