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, "\t", false, false,
        0, true);
    Excel.Sheets sheets = theWorkbook.Worksheets;
    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
    for (int i = 1; i <= 10; i++)
    {
        Excel.Range range = worksheet.get_Range("A" + i.ToString(), "J" + i.ToString());
        System.Array myvalues = (System.Array)range.Cells.Value;
        string[] strArray = ConvertToStringArray(myvalues);
    }
}

Any advice/guidance is welcome

Answer 1

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

Answer 2

You can query the data as mention in this question.

Query excel sheet in c#

But I recommend try to create a process to obtain the data from the excel file and move to sql server or any formal database. In this process you can validate the data format and eliminate errors.