2

My requirement is to export a blank excel sheet with 5 columns in which 3rd columns of all rows as a dropdownlist, so that user can use this work sheet to modify the data as per their need. I am using c# to exporting file.

I already worked on it but that at the moment, it only creates a dropdown list in a particular cell but I want to make all the rows of first column as a dropdown list .

Am using gembox spreadsheet to create an excel file.

Below is the code I am using:

        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        ExcelFile ef = new ExcelFile();
        ExcelWorksheet ws = ef.Worksheets.Add("Journal Entry Format");

        ws.Columns[0].Width = 30 * 256;
        ws.Columns[1].Width = 30 * 256;
        ws.Columns[2].Width = 30 * 256;
        ws.Columns[3].Width = 30 * 256;
        ws.Columns[4].Width = 30 * 256;
        ws.Columns[5].Width = 30 * 256;
        ws.Columns[6].Width = 30 * 256;

        CellStyle tmpStyle2 = new CellStyle();
        tmpStyle2.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        tmpStyle2.VerticalAlignment = VerticalAlignmentStyle.Center;
        tmpStyle2.FillPattern.SetSolid(System.Drawing.Color.Yellow);
        ws.Cells.GetSubrangeAbsolute(0, 0, 1, 5).Style = tmpStyle2;
        ws.Cells[1, 0].Value = "Last";
        ws.Cells.GetSubrangeAbsolute(1, 0, 1, 5).Merged = true;

        CellStyle tmpStyle1 = new CellStyle();
        tmpStyle1.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        tmpStyle1.VerticalAlignment = VerticalAlignmentStyle.Center;
        tmpStyle1.FillPattern.SetSolid(System.Drawing.Color.Blue);
        tmpStyle1.Font.Weight = ExcelFont.BoldWeight;
        tmpStyle1.Font.Color = System.Drawing.Color.White;
        tmpStyle1.WrapText = true;

        ws.Cells[3, 0].Value = "Voucher Date";
        ws.Cells[3, 1].Value = "Voucher No.";
        ws.Cells[3, 2].Value = "Select Ledger";
        ws.Cells[3, 3].Value = "Debit/Credit";
        ws.Cells[3, 4].Value = "Amount";
        ws.Cells[3, 5].Value = "Narration";
        ws.Cells.GetSubrangeAbsolute(3, 0, 3, 5).Style = tmpStyle1;

        var list = new List<string>();
        foreach (var led in ledgers)
        {
            list.Add(led.AccountHead);
        }

        var flatList = string.Join(",", list.ToArray());

        for (int i = 3; i < 100; ++i)
        {
            DataValidation dv = new DataValidation();
            dv.InCellDropdown = true;
            dv.InputMessage = "Select Ledger";
            dv.Formula1 = flatList;
            ws.DataValidations.Add(dv);
            
            ws.Columns[2].Cells[i + 1].Value = dv.Formula1;
        }

        ws.PrintOptions.FitWorksheetWidthToPages = 1;
        ef.Save(this.Response, "JournalEntry Format" + ".xlsx");
Community
  • 1
  • 1
Arvind
  • 75
  • 2
  • 12

1 Answers1

0

Here is how you can set 3rd columns cells as a dropdownlist:

var flatList = string.Join(",", list.ToArray());

DataValidation dv = new DataValidation(ws.Columns[2].Cells);
dv.Type = DataValidationType.List;
dv.InputMessage = "Select Ledger";
dv.Formula1 = flatList;

ws.DataValidations.Add(dv);
GemBox Dev Team
  • 669
  • 5
  • 18
  • Its not working properly for me...Error : Removed Feature: Data validation from /xl/worksheets/sheet1.xml part – Arvind Jun 26 '15 at 06:14
  • xml error sheet : - error046680_01.xml Errors were detected in file 'C:\Users\Singh\Downloads\JournalEntry Format (1).xlsx' - Removed Feature: Data validation from /xl/worksheets/sheet1.xml part – Arvind Jun 26 '15 at 06:16
  • Did you replace your 'for' loop with the snippet code that I provided? Unfortunately I'm unable to reproduce your issue, can you please try again with the latest bug fix version: http://www.gemboxsoftware.com/spreadsheet/downloads/BugFixes.htm If problem remains would it be possible for you to send me a test project that would reproduce your issue so that I may investigate it, you can send it on the following link: http://www.gemboxsoftware.com/support-center/new-ticket – GemBox Dev Team Jun 26 '15 at 06:25