3 Replies - 1114 Views - Last Post: 16 January 2013 - 07:29 AM Rate Topic: -----

#1 Xna4life  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 21-February 12

Read xls/xlsx and display on screen

Posted 16 January 2013 - 03:08 AM

Hey guys....so i'm using 'Excel data reader' to read in an excel file, I then want to display the file on screen.
     protected void Page_Load(object sender, EventArgs e)
    {
        string filePath = @"C:\WORK\BoireannSVN\trunk\VS\CRCConnect\Spreadsheet\Spreadsheet.xlsx";

        FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
     
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        while (excelReader.Read())
        {
            Label x = new Label();
            x.Text = result.Tables[0].Rows[0].ItemArray[0].ToString();
            uploadExcel.Controls.Add(x); 

            excelReader.GetInt32(0);
        }

        excelReader.Close();

    }


I've added in the
    Label x = new Label(); 
    x.Text = result.Tables[0].Rows[0].ItemArray[0].ToString();



Attempting to get the excel sheet to display on screen, although I'm having no luck with this. I think it is something like
    table1.Columns.Add;
    table1.Rows.Add;



but can't seem to get it going, ay help much appreciated.
Thank You

Is This A Good Question/Topic? 0
  • +

Replies To: Read xls/xlsx and display on screen

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,352
  • Joined: 12-December 12

Re: Read xls/xlsx and display on screen

Posted 16 January 2013 - 04:38 AM

DataSet result = excelReader.AsDataSet();

I read this as copying the Excel data to a DataSet, so then you could close the reader excelReader.Close(); and navigate the DataSet. For example,

foreach(var table in result.Tables) {
    foreach(var col in table.Columns) {
       ...
    }
    foreach(var row in table.Rows) {
        object[] values = row.ItemArray;
        ...
    }
}


You might include if (result != null) {

This post has been edited by andrewsw: 16 January 2013 - 04:42 AM

Was This Post Helpful? 0
  • +
  • -

#3 Xna4life  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 21-February 12

Re: Read xls/xlsx and display on screen

Posted 16 January 2013 - 04:56 AM

Thank you for your help but it is only allowing me to select 'equals/gethashcode/getstring/tostring from the telesense inside the 2d and 3rd foreach loop, stating object does not contain a defination for...
 if (result != null)
        {
            foreach (var table in result.Tables)
            {
                foreach (var col in table.Columns)//wont allow me to select columns
                {
                    //...
                }

                foreach (var row in table.Rows)//wont allow me to select rows
                {
                    object[] values = row.ItemArray;
                    //...
                }
            }
        }



any suggestions as to why?

iv also tried
if (table != null && table is Table)
{

This post has been edited by Xna4life: 16 January 2013 - 05:11 AM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,352
  • Joined: 12-December 12

Re: Read xls/xlsx and display on screen

Posted 16 January 2013 - 07:29 AM

That's because the example code I posted uses var.

Ignore the intellisense. Or use DataTable, DataColumn DataRow.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1