BuyLog In
Ticket T429078
Visible to All Users

ExcelDataSource - How to retrieve actual values in code

created 9 years ago

Hi,
pls help me with my problem.
I want to open a Excel file and bind it to a exceldatasource. I bind the datasource to a Gridview without any problems. But I have to walk throw to all excel rows to do a column mapping with my sql server datasource. I've tried this one:

for (int i = 0; i < gvExcel.RowCount; i++)
                {
                    int RowHandle = gvExcel.GetVisibleRowHandle(i);

DataRow ExcelRow = gvExcel.GetDataRow(RowHandle);

DataRow StundenRow = tADAMDataSet.tStundenImport.NewRow();

foreach (DataRow item in tADAMDataSet.tColumnMapping.Rows)
                    {
                            StundenRow[item["StundenColumn"].ToString()]+= ExcelRow[item["ExcelColumn"].ToString()].ToString();
                    }
                    tADAMDataSet.tStundenImport.Rows.Add(StundenRow);
                }

but the ExcelRow is always null. Then I've read in the KBs I need to take the Nativ Excel View, can you send me a code example how this works?
 DevExpress.DataAccess.Native.Excel.DataView dv = new DevExpress.DataAccess.Native.Excel.DataView(excelDataSource, ???);

thanks
br
Gregory

Comments (2)
Vasily (DevExpress Support) 9 years ago

    Hi Gregory,

    If I understand your scenario correctly, you would like to bind your GridView control to the data that comes from the Excel file by using the ExcelDataSource control, am I right? In any case, it is not completely clear to me why you are iterating all the Excel rows. What kind of mapping would you like to perform? Would you please describe your scenario in greater detail so that we will be able to provide you with an appropriate solution? We look forward to hearing from you.

    GK GK
    Gregor Klatovsky 1 9 years ago

      Hi Vasily,
      yes you are correct. Will try to explain in more detail.
      I have one table with some columns and want to import a excel file to this table. The excel file has columns I don't know. Therefore I want map the excel columns to the table column. I have tow combo fields, one with the excel columns, one with the table columns. The columns mapping looks like in this way:
      Excel                          Table
      Col1                            Col1
      Col2                            Col1
      Col3                            Col2    and so on.
      I've bound the exceldatasource to a grid view for preview. When the user has mapped the correct columns, I need to read each excel row or gridview row to put the excel data into the table with the correct column mapping.
      So I go through the excel bound gridview like discribed in the first post, but my problem is, the DataRow of the excel gridview is null. I guess it is because of the exceldatasource. So I have to go through the excel nativ dataview, but I don't have the correct syntax.  Or is there a other way to iterate the exceldatasource?
      It is more clear for you what I want to do?
      Thanks
      BR
      Gregory

      Sign in to comment on this post

      Answers approved by DevExpress Support

      created 9 years ago (modified 9 years ago)

      Hello Gregory,
      As far as I understand, your task is to iterate through all rows and obtain actual values from ExcelDataSource. If you are going to use the ExcelDataSource native API, I suggest you review this code:

      C#
      DevExpress.DataAccess.Native.Excel.DataView dv = ((IListSource)excelDataSource1).GetList() as DevExpress.DataAccess.Native.Excel.DataView;  
for(int i = 0; i < dv.Count; i++) {  
    DevExpress.DataAccess.Native.Excel.ViewRow row = dv[i] as DevExpress.DataAccess.Native.Excel.ViewRow;  
    foreach(DevExpress.DataAccess.Native.Excel.ViewColumn col in dv.Columns) {  
        object val = col.GetValue(row);  
    }  
}

      The main idea is that DataView columns are PropertyDescriptor objects, and you can use the GetValue method to obtain an actual value.
      Note that you can also use the grid API for this purpose. It provides the GridView.GetRowCellValue method allowing you to get a value using the row index and field name. See the Obtaining and Setting Cell Values help topic for more details.
      As for your code, the GetDataRow method returns null since the grid's data source is not a DataTable or DataView. You would need to use the GetRow method in this scenario. However, with any of the above-mentioned approaches, it is not necessary.
      Please let me know if this information is helpful.

        Show previous comments (1)
        DevExpress Support Team 9 years ago

          You are welcome, Gregory! Please feel free to contact us in case of further difficulties.

          SG SG
          Scott Gross 8 years ago

            is there a way to always use the first sheet in the workbook or query the worksheet names?

            Dmitry Tokmachev (DevExpress) 8 years ago

              Hello Scott,

              The ExcelWorksheetSettings.WorksheetName property value should be explicitly specified to point the ExcelDataSource to the target worksheet. I'm afraid that there is no way to force the ExcelDataSource to automatically load data from the first sheet of a workbook. You can use the non-documented native functionality of the ExcelDataSource (v16.2) to obtain the sheet names from a workbook (this code is used internally, when the Select a Worksheet, Table or Named Range wizard page is displayed in the End-User Report Designer):

              C#
              using DevExpress.DataAccess.Excel;  
using DevExpress.DataAccess.Native.Excel;  
using DevExpress.SpreadsheetSource;  
...  
public const string filename = "Categories.xlsx";  
...  
private void GetSheetNames() {  
            using (ISpreadsheetSource spreadsheetSource = ExcelDataLoaderHelper.CreateSource(null, ExcelDataLoaderHelper.DetectFormat(filename), filename, new ExcelSourceOptions())) {  
                var worksheetCollection = spreadsheetSource.Worksheets;  
                var definedNamesCollection = spreadsheetSource.DefinedNames.Where(dn => !dn.IsHidden).ToList();  
                var tablesCollection = spreadsheetSource.Tables;  
            }  
}

              See also: How to create Data Access Library data sources at runtime

              Thanks,
              Dmitry

              Sign in to comment on this post

              Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

              Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.