none
Iterate over column values? RRS feed

  • Question

  • Using 2.0 (March 2010 download) of the API/SDK.

    I have a spreadsheet with 13 columns and 25,000 rows. One of the columns has a title/header in the first row called "Fullbeat". The column position can change. When I find the column, I want to get every cell value under the header and pass it to another method for linq-to-sql inserting.

    I have a SpreadsheetDocument populated by a byte[].

    I get the first sheet (there is always one sheet) like this:

    Sheet wst = mydoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First();

    But now I don't understand the API to find the right column and then interate (or linq query?) after that.

     

    Help!

     

    Thanks.

     

    Thursday, April 29, 2010 9:03 PM

Answers

  • Hello,

    Based on my knowledge, we cannot get a colum of values directly, but we can get a specified cell value. Then the solution can be,

    1.Loop cell(1,1) to cell(1,13), get the value of the header row to see which cell matches the string "Fullbeat".

    2.After the step1, we should be able to know the the "Fullbeat" column's index. Then we can do a loop to get all cells value under that column.

     

    There are some code snippets we can download from the Microsoft donwload center.

    http://blogs.msdn.com/erikaehrli/archive/2009/08/28/open-xml-sdk-2-0-august-ctp-and-50-open-xml-code-samples.aspx

    http://www.microsoft.com/downloads/details.aspx?FamilyID=78bea298-a3f9-44cf-bde0-b4f30dc986df&displaylang=en

    One of them is about how to get a cell value with specified row and column index:

    Excel: Get cell value given row and column

    Rertrieve a cell value given its row and column numbers, or a row number and column name.

     

    I use it and write the following codes in my console application for a test. It works fine,

            public static void Main()
            {
                for (int i = 1; i <= 13; i++)
                {
                    if (XLGetCellValueRowCol(@"D:\test.xlsx", "Sheet1", i, 1) == "Fullbeat")
                    {
                        for (int j = 2; j <= 10; j++)
                        {
                            Console.WriteLine(XLGetCellValueRowCol(@"D:\test.xlsx", "Sheet1", i, j)); //This line, you can add the retrieved value to your variable which will be handled by LINQ later.
                        }
                        break;
                    }
                }
            }

    Hope this helps!

     

    Regards,
    Ji Zhou
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, May 4, 2010 3:49 AM
    Moderator
  • Hi SC_A9,

    Thanks for your question.

    When you look into the file format of a spreadsheet, you could find that the data is stored in "row"s rather than "column"s (see below):

    <x:row r="1" spans="1:1" x14ac:dyDescent="0.25">

    <x:c r="A1" s="1" t="s">

    <x:v>0</x:v>

    </x:c>

    </x:row>

    So as mentioned by Ji Zhou, I think you could get the column values by loop through all the cells of the column. Here is the how-to article which contains "Get a Cell from a Spreadsheet Document" given row and column index, which I think might be helpful to you.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Tuesday, May 4, 2010 6:52 AM

All replies

  • Hello,

    Based on my knowledge, we cannot get a colum of values directly, but we can get a specified cell value. Then the solution can be,

    1.Loop cell(1,1) to cell(1,13), get the value of the header row to see which cell matches the string "Fullbeat".

    2.After the step1, we should be able to know the the "Fullbeat" column's index. Then we can do a loop to get all cells value under that column.

     

    There are some code snippets we can download from the Microsoft donwload center.

    http://blogs.msdn.com/erikaehrli/archive/2009/08/28/open-xml-sdk-2-0-august-ctp-and-50-open-xml-code-samples.aspx

    http://www.microsoft.com/downloads/details.aspx?FamilyID=78bea298-a3f9-44cf-bde0-b4f30dc986df&displaylang=en

    One of them is about how to get a cell value with specified row and column index:

    Excel: Get cell value given row and column

    Rertrieve a cell value given its row and column numbers, or a row number and column name.

     

    I use it and write the following codes in my console application for a test. It works fine,

            public static void Main()
            {
                for (int i = 1; i <= 13; i++)
                {
                    if (XLGetCellValueRowCol(@"D:\test.xlsx", "Sheet1", i, 1) == "Fullbeat")
                    {
                        for (int j = 2; j <= 10; j++)
                        {
                            Console.WriteLine(XLGetCellValueRowCol(@"D:\test.xlsx", "Sheet1", i, j)); //This line, you can add the retrieved value to your variable which will be handled by LINQ later.
                        }
                        break;
                    }
                }
            }

    Hope this helps!

     

    Regards,
    Ji Zhou
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, May 4, 2010 3:49 AM
    Moderator
  • Hi SC_A9,

    Thanks for your question.

    When you look into the file format of a spreadsheet, you could find that the data is stored in "row"s rather than "column"s (see below):

    <x:row r="1" spans="1:1" x14ac:dyDescent="0.25">

    <x:c r="A1" s="1" t="s">

    <x:v>0</x:v>

    </x:c>

    </x:row>

    So as mentioned by Ji Zhou, I think you could get the column values by loop through all the cells of the column. Here is the how-to article which contains "Get a Cell from a Spreadsheet Document" given row and column index, which I think might be helpful to you.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Tuesday, May 4, 2010 6:52 AM
  • Hello SC,

    What is the status of the issue in your side? If you need more help on this, please feel free to let us know.

    Regards,
    Ji Zhou
    MSDN Subscriber Support in Forum


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, May 7, 2010 2:41 AM
    Moderator