ComboBoxCellType when exported to excel

ComboBoxCellType when exported to excel

Posted by: RainRiku05 on 26 October 2017, 12:44 am EST

• 

  RainRiku05

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 26 October 2017, 12:44 am EST

  I have an issue with the ComboBoxCellType when exported to excel. It displays the ItemData value of the cell but the dropdown displays the Item of the combobox.
  
  Please see attached for the issue.
  
  
  
  00001 = For Preliminary
  
  '------------------------------------------------------
  
  Here’s my comboboxcelltype settings, as you can see i have different collection of the Item and ItemData of the comboboxCellType

  Dim cmb As New CellType.ComboBoxCellType ’ コンボボックス設定用

          cmb.DropDownOptions = FarPoint.Win.DropDownOptions.Button
          cmb.Editable = False
          cmb.DropDownWhenStartEditing = True
          cmb.DoubleClickTextToDropDown = True
          cmb.StopEditingAfterDropDownItemSelected = True
          cmb.EditorValue = CellType.EditorValue.String
          cmb.Items = pstrDisplay
          cmb.ItemData = pstrData
          cmb.EditorValue = CellType.EditorValue.ItemData
  

  '---------------------------------------------------------------------------------------------

  Here’s my save to excel code

  sprVw.SaveExcel(ofd.FileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders Or FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)
  

  '------------------------------------------------------------------------

  My target is to display the ComboBoxCellType.Item (‘For Preliminary’) not the ItemData value.

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 30 October 2017, 5:30 am EST

  Hello,

  In your code you do not need to set the EditorValue to ItemData if you want the cell’s text to be exported as it is instead of the underlying value.When EditorValue = Items (which is the default), the Items list is exported. When EditorValue = ItemData, the ItemData list is exported.

  I used the following code to export to Excel with no issues:

  
   private void Form1_Load(object sender, EventArgs e)
          {
              FarPoint.Win.Spread.CellType.ComboBoxCellType combo = new FarPoint.Win.Spread.CellType.ComboBoxCellType();
              string[] cbstr;
              cbstr = new String[] { "One", "Two", "Three" };
              string[] strval;
              strval = new String[] { "1", "2", "3" };
              combo.Items = cbstr;
              combo.ItemData = strval;
              fpSpread1.Sheets[0].Cells[0, 0].CellType = combo;
  
          }
  
          private void button1_Click(object sender, EventArgs e)
          {
              fpSpread1.SaveExcel(@"C:\Data\ExportedExcel.xlsx", FarPoint.Excel.ExcelSaveFlags.SaveAsViewed| FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
          }
  
  

  Thanks,
  
  Deepak Sharma

• 

  RainRiku05

  • Post Options:
  • Link
    Copy

  Posted 24 November 2017, 1:28 am EST

  Hi Deepak,

  Thank you for answering my issue. 
  

  In your example, When i provide a value FpSpread1.Sheets(0).Cells(0, 0).Value = “1”. When exported, the value will be “Two”. Is this correct?

  Basically in my case the value in the database is a varchar2 with the 1,2,3 values. The target is to display and to export this value into One, Two, Three, based on your example.

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 27 November 2017, 4:16 am EST

  Hello,

  With my code , if I set the value in code for combo cell and then export it to Excel it will show the value “One” and not “Two”.
  
  I used following code:

  
   private void Form1_Load(object sender, EventArgs e)
          {
              FarPoint.Win.Spread.CellType.ComboBoxCellType combo = new FarPoint.Win.Spread.CellType.ComboBoxCellType();
              string[] cbstr;
              cbstr = new String[] { "One", "Two", "Three" };
              string[] strval;
              strval = new String[] { "1", "2", "3" };
              combo.Items = cbstr;
              combo.ItemData = strval;
              fpSpread1.Sheets[0].Cells[0, 0].CellType = combo;
          }
  
          private void button1_Click(object sender, EventArgs e)
          {
              fpSpread1.Sheets[0].Cells[0, 0].Value = 1;
              fpSpread1.SaveExcel(@"C:\Data\ComboExcel.xlsx", FarPoint.Excel.ExcelSaveFlags.SaveAsViewed | FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
          }
  
  

  I hope it helps.

  Thanks,
  
  Deepak Sharma