locked
How to load an excel sheet into dataset using oledb RRS feed

  • Question

  • Hi,

    I am having a requirement to load all rows of an excel sheet into a datatable irrespective of empty rows in the starting.

    Currently using query -  

    query = "SELECT * FROM [" + excelSheetName + "$]";

    But if having a picture in the top of the sheet, that rows are skipped while loading into datatable some times.

    Need a generalized solution to load all rows into datatable including empty rows or rows having images.

    Someone please help with this.

    Wednesday, February 8, 2012 8:03 AM

Answers

  • YOu can't read images from a worksheet using oledb.  Images are not part of the worksheet structure.  The images sit ontop of the worksheet and are not associated with any rows or columns.  The location of the image is a pixel location (x,y) which is a postion from the top left corner of the worksheet.

    jdweng

    • Proposed as answer by bhanu_mallya Wednesday, February 8, 2012 9:20 PM
    • Unproposed as answer by bhanu_mallya Wednesday, February 8, 2012 9:26 PM
    • Proposed as answer by ryguy72 Friday, February 10, 2012 10:59 PM
    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:23 AM
    Wednesday, February 8, 2012 11:41 AM
  • Hi Keen,

    When you use "SELECT * FROM [" + excelSheetName + "$]";command to query your worksheet, it has already retrieved all the data from the worksheet include the columns which are covered by imanges. The images of worksheet doesn't affect the result you retrieve via OLEDB.

    So unless you are trying to do something like Joel said, which is not possible, or you don't need worry about this issue.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:23 AM
    Friday, February 10, 2012 5:48 AM
  • The short answer is that you'll need to know how the image got there in the first place. If there is a directory somewhere with the original images in it that has a logical naming convention (like file_sheet_row_item.jpg), you may be able to use that to construct the source image path programmatically and then copy the path as a string. Then it's a matter of displaying the image when the data is retrieved.

    So instead of moving the image itself, you're finding a way to copy the "path" to the image instead as a string.

    The details will depend a lot on how the source information is organized. You "ignore" blank rows because there should not be any corresponding match to the image name elsewhere.

    If, however, there is no standardized naming convention or common source point, you're looking at a solution that is beyond me...

    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:24 AM
    Friday, February 10, 2012 2:34 PM

All replies

  • YOu can't read images from a worksheet using oledb.  Images are not part of the worksheet structure.  The images sit ontop of the worksheet and are not associated with any rows or columns.  The location of the image is a pixel location (x,y) which is a postion from the top left corner of the worksheet.

    jdweng

    • Proposed as answer by bhanu_mallya Wednesday, February 8, 2012 9:20 PM
    • Unproposed as answer by bhanu_mallya Wednesday, February 8, 2012 9:26 PM
    • Proposed as answer by ryguy72 Friday, February 10, 2012 10:59 PM
    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:23 AM
    Wednesday, February 8, 2012 11:41 AM
  • Hi Keen,

    When you use "SELECT * FROM [" + excelSheetName + "$]";command to query your worksheet, it has already retrieved all the data from the worksheet include the columns which are covered by imanges. The images of worksheet doesn't affect the result you retrieve via OLEDB.

    So unless you are trying to do something like Joel said, which is not possible, or you don't need worry about this issue.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:23 AM
    Friday, February 10, 2012 5:48 AM
  • The short answer is that you'll need to know how the image got there in the first place. If there is a directory somewhere with the original images in it that has a logical naming convention (like file_sheet_row_item.jpg), you may be able to use that to construct the source image path programmatically and then copy the path as a string. Then it's a matter of displaying the image when the data is retrieved.

    So instead of moving the image itself, you're finding a way to copy the "path" to the image instead as a string.

    The details will depend a lot on how the source information is organized. You "ignore" blank rows because there should not be any corresponding match to the image name elsewhere.

    If, however, there is no standardized naming convention or common source point, you're looking at a solution that is beyond me...

    • Marked as answer by Calvin_Gao Tuesday, February 28, 2012 7:24 AM
    Friday, February 10, 2012 2:34 PM