Clear Previous selection of Dropdown in EXCEL usingApache POI

Question

I have developed the excel using the Apache NPOI dll using HSSFWORKBOOK. But in my excel having 3 dependent dropdowns one in each other.

Here my question is I am unable to clear the cell value of dependent dropdowns. Let say like dropdown1 have country values and on the selection of this dropdown2 state values got a filter and selected one of the state. Now if I changed the country again able to filter the data but whatever the previous state selection not getting cleared.

code ref:

No, i am just referring the list of columns data from sheet2 to sheet1 using the formulae. below is code ref:

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    //if (i >= MaxRowCount)
                    //{
                    //    sheet2.CreateRow(1000 + i).CreateCell(1).SetCellValue(ds.Tables[0].Rows[i][1].ToString());
                    //    MaxRowCount += 1;
                    //}
                    //else
                    //    sheet2.GetRow(1000 + i).CreateCell(1).SetCellValue(ds.Tables[0].Rows[i][1].ToString());

                    if (CategoryCount == 250)
                    {
                        rownumber = rownumber + 3000;
                        CategoryCount = 16;
                        MaxRowCount = 0;

                    }

                    DataRow[] DR = ds.Tables[1].Select("LOCATION_ID=" + ds.Tables[0].Rows[i][0].ToString());
                    int RowCount = 0;

                    //int rownumber = 1000;
                    foreach (DataRow d in DR)
                    {

                        if (RowCount >= MaxRowCount)
                        {
                            sheet2.CreateRow(rownumber + RowCount).CreateCell(CategoryCount).SetCellValue(d[0].ToString());
                            MaxRowCount += 1;
                        }
                        else
                        {
                            sheet2.GetRow(rownumber + RowCount).CreateCell(CategoryCount).SetCellValue(d[0].ToString());
                        }
                        RowCount++;
                    }





IName DeptHierarchy_Dept = hssfworkbook.CreateName();
                            DeptHierarchy_Dept.NameName = "XDeptHierarchy_Dept_SUB" + (XDeptHierarchy_Dept_SUBCount).ToString();
                            if (CategoryCount / 26 < 1)
                                DeptHierarchy_Dept.RefersToFormula = "'Template Field Specs2'!$" + ((char)(65 + CategoryCount)).ToString() + "$" + (StartRowNumber + 1) + ":$" + ((char)(65 + CategoryCount)).ToString() + "$" + ((StartRowNumber + 1) + (RowCount == 0 ? 0 : RowCount - 1)).ToString();
                            else
                                DeptHierarchy_Dept.RefersToFormula = "'Template Field Specs2'!$" + ((char)(65 + (CategoryCount / 26) - 1)).ToString() + ((char)(65 + CategoryCount % 26)).ToString() + "$" + (StartRowNumber + 1) + ":$" + ((char)(65 + (CategoryCount / 26) - 1)).ToString() + ((char)(65 + CategoryCount % 26)).ToString() + "$" + ((StartRowNumber + 1) + (RowCount == 0 ? 0 : RowCount - 1)).ToString();
                            CategoryCount++;
                            XDeptHierarchy_Dept_SUBCount++;

are you setting the value using row.Cells[header].SetCellValue("state"); on country change? Please share your code snippet. — kumar chandraketu, Jun 13 '18 at 19:27
No, i am just referring the list of columns data from sheet2 to sheet1 using the formulae. pls See the code ref above. — user2681423, Jun 18 '18 at 11:49

score 0 · Answer 1 · answered Jun 18 '18 at 14:54

You are looking for "Dependent Drop Down Lists". You'll have to be creative for your formula because your row will keep changing. one approach i can think of is store the list of "Country-capital" on named region some where deep in excel. On selection of country, parse and find the capital from named region and populate your target cell. Please see the documentation. Also this link;

Clear Previous selection of Dropdown in EXCEL usingApache POI

1 Answers1