locked
Validate Excel Sheet Column Using List RRS feed

  • Question

  • User-1379963870 posted

    Hello All,

    I m having a problem with Excel sheet,

    In Excel sheet one feature like we can have a list of value for perticluar column and user will select value from that dropdown list, same thing i m also trying to do using asp.net

    I m creating excel sheet from my page , i have also create rang for cells and also give name to that perticular range, and now i m having a problem with how to assign that list of range to Excel sheet column .

     

    Thanks

    Esha Desai.  

     

    Wednesday, January 5, 2011 4:21 AM

All replies

  • User1983487377 posted

    post this line before closing the excel

     Range rng = worksheet.get_Range("A1", "Z1");           
    
     rng.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);



    Friday, January 7, 2011 5:31 AM
  • User-1379963870 posted

    Not Such Suppose I have  Selection for Column that is AA,AB,AC,AD, So i want Validation Filter In Excel Sheet for that Column , User Only Select from (AA,AB,AC,AD) and for While Column when we move to next line that Validation also come to next line ,

    AND also this (AA,AB,AC,AD ) , will write from asp.net to excel sheet when sheet is Generated ,,

     

    See the code i have done till Range and i have gave name to that range.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    protected void Page_Load(object sender, EventArgs

    e)

    {

    try

    {

    Microsoft.Office.Interop.Excel.

    Application

    xlApp;

    Microsoft.Office.Interop.Excel.

    Workbook

    xlWorkBook;

    Microsoft.Office.Interop.Excel.

    Worksheet

    xlWorkSheet;

    Object misValue = System.Reflection.Missing

    .Value;

    xlApp =

    new Microsoft.Office.Interop.Excel.ApplicationClass

    ();

    xlWorkBook = xlApp.Workbooks.Add(misValue);

    xlWorkSheet = (Microsoft.Office.Interop.Excel.

    Worksheet

    )xlWorkBook.ActiveSheet;

    xlWorkSheet.Name =

    "Sheet1"

    ;

     

    string[] ddl_item = { "Answers", "Autos", "Finance", "Games", "Groups", "HotJobs", "Maps", "Mobile Web", "Movies", "Music", "Personals", "Real Estate", "Shopping", "Sports", "Tech", "Travel", "TV", "Yellow Pages"

    };

    List<string[]> ddl = new List<string

    []>();

    ddl.Add(ddl_item);

    Range

    xlsRange, xlsRange1;

    xlsRange = xlWorkSheet.get_Range(

    "A1", "A1"

    );

    xlsRange1 = xlWorkSheet.get_Range(

    "A2", "A2"

    );

    Microsoft.Office.Interop.Excel.

    DropDowns

    xlDropDowns;

    Microsoft.Office.Interop.Excel.

    DropDown

    xlDropDown;

    xlDropDowns = ((Microsoft.Office.Interop.Excel.

    DropDowns

    )(xlWorkSheet.DropDowns(misValue)));

    xlDropDown = xlDropDowns.Add((

    double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true

    );

    // xlDropDown = xlDropDowns.Add((double)xlsRange1.Left, (double)xlsRange1.Top, (double)xlsRange.Width, (double)xlsRange1.Height, true);

    //Add item into drop down list

    for (int

    k = 0; k < ddl_item.Length; k++)

    {

    xlDropDown.AddItem(ddl_item[k], k + 1);

    }

    System.Data.

    DataTable dt = new System.Data.DataTable

    ();

    dt.Columns.Add(

    "Name"

    );

    for (int

    ctr = 5; ctr <= 9; ctr += 1)

    {

    if

    (ctr == 5)

    {

    xlWorkSheet.Cells[5,

    "F"] = "AE"

    ;

    }

    if

    (ctr == 6)

    {

    xlWorkSheet.Cells[6,

    "F"] = "AA"

    ;

    }

    if

    (ctr == 7)

    {

    xlWorkSheet.Cells[7,

    "F"] = "AB"

    ;

    }

    if

    (ctr == 8)

    {

    xlWorkSheet.Cells[8,

    "F"] = "AC"

    ;

    }

    if

    (ctr == 9)

    {

    xlWorkSheet.Cells[9,

    "F"] = "AD"

    ;

    }

    xlWorkSheet.Columns.Justify();

    }

    xlWorkSheet.get_Range(

    "F5", "F9").Name = "Status"

    ;

    Microsoft.Office.Interop.Excel.

    Range _Range = xlWorkSheet.get_Range("A1", "A1"

    );

     

    //xlWorkSheet.get_Range("E1", "E1").EntireColumn.Cells.Validation.

    //int i, j;

    //for (i = 0; i <= dt.Rows.Count - 1; i += 1)

    //{

    // for (j = 1; j <= dt.Columns.Count - 1; j += 1)

    // {

    // xlWorkSheet.Cells[i + 2, j] = dt.Rows[i][j].ToString();

    // }

    //}

    //Range rng = xlWorkSheet.get_Range("A1", "Z1");

    //rng.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

    xlWorkSheet.get_Range(

    "A1", "A1"

    ).EntireColumn.Cells.ColumnWidth = 15;

    xlWorkSheet.get_Range(

    "A1", "A1").EntireColumn.Cells.Font.Bold = true

    ;

    xlWorkSheet.get_Range(

    "B1", "B1"

    ).EntireColumn.Cells.ColumnWidth = 10;

    xlWorkSheet.get_Range(

    "B1", "B1").EntireColumn.Cells.Font.Bold = true

    ;

    xlWorkSheet.get_Range(

    "C1", "C1"

    ).EntireColumn.Cells.ColumnWidth = 10;

    xlWorkSheet.get_Range(

    "C1", "C1").EntireColumn.Cells.Font.Bold = true

    ;

    Random

    ran;

    l1:

    ran =

    new Random

    ();

    string filename = HttpContext.Current.Server.MapPath("~/Document/" + Utils.GenerateRandomPassword(10) + ".xls"

    );

    if (System.IO.File

    .Exists(filename))

    {

    goto

    l1;

    }

    xlWorkBook.SaveAs(filename, Microsoft.Office.Interop.Excel.

    XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null

    );

    xlWorkBook.Close(

    null, null, null

    );

    xlApp.Workbooks.Close();

    xlApp.Visible =

    true

    ;

    xlApp.Quit();

    System.Runtime.InteropServices.

    Marshal

    .ReleaseComObject(xlApp);

    System.Runtime.InteropServices.

    Marshal

    .ReleaseComObject(xlWorkSheet);

    System.Runtime.InteropServices.

    Marshal

    .ReleaseComObject(xlWorkBook);

    xlWorkSheet =

    null

    ;

    xlWorkBook =

    null

    ;

    xlApp =

    null

    ;

    GC

    .Collect();

    }

    catch (Exception

    ex)

    {

    //Functions.DisplayMessage(cph.Page, ex.Message.ToString());

    }

     

    }

     

     

    Friday, January 7, 2011 5:43 AM