Use excel spreadsheet as data source for report viewer c#

Question

Im trying to use the `reportviewer` provided by Visual Studio (windows form application) to create a report based on an excel spreadsheet. However I am struggling to find the correct way to read/access the spreadsheet. When I try to create a new report I get the following window: I've tried to use the object option but havent had any luck Question: How would I use an excel spreadsheet to create a report? I had some luck with the following code which allowed me to process the file, but I cant find a way to tie it into the reportviewer: ``` Excel.Application ExcelObj = new Excel.Application(); this.openFileDialog1.FileName = "*.xls"; if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open( openFileDialog1.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "	", false, false, 0, true); Excel.Sheets sheets = theWorkbook.Worksheets; Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); for (int i = 1; i <= 10;…

Accepted Answer

After much googling I managed to piece this together and get the excel sheet into a basic report.

This requires you to setup a dataset with the column names specified in the first row of the excel spreadsheet, and to also tie this dataset into a report. Then you can use the following to populate it:

```
[open file dialog code..]
try
{
    string path = this.openFileDialog1.FileName;
    if (Path.GetExtension(path) == ".xls")
    {
        oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
    }
    else if (Path.GetExtension(path) == ".xlsx")
    {
        oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
    }
    oledbConn.Open();

DataSet ds = new DataSet();

OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = oledbConn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * FROM [sheet1$]";

OleDbDataAdapter oleda = new OleDbDataAdapter();
    oleda = new OleDbDataAdapter(cmd);
    oleda.Fill(ds);
    ds.Tables[0].TableName = "DataTable1";

this.DataTable1BindingSource.DataSource = ds;
    this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
}
finally
{
    oledbConn.Close();
}
```

I found these articles helpful:

Dyanmic report source

Loading excel into a dataset

Use excel spreadsheet as data source for report viewer c#

Solution

Problem

Related problems