Excel Automation in VB.NET 2010 Express RRS feed

  • Question

  • Hi Guys,

    I need some assistance (again) with Excel automation. I've decided to do my reports via excel. I've set up a template on excel. In the template I've got columns that must be filled according to the data filtered and then rows that also needs to be completed. On the form I have 4 combo-boxes (for filtering the data) and then 2x Listview controls that will display the data upon the user's selection.

    I understood from a previous posting that using "For i = 0 To ListView1.Items.Count - 1" can be used to add rows underneath one-another for each listview item. But how do I go about doing it for the columns?

    Another question is that upon the data being filtered, it will display a persons name with all the assessments done. So there will be for example 5 lines in the listview containing the same name but different assessments and scores. In the spreadsheet template there is space for the name (e.g. L10), then for each assignment for that specific person, the scores must be added to columns (M10, N10, O10, etc...). How do I do this? I don't have any code to paste here as yet as I'm still trying to figure out how to do it.

    Hope someone can assist me here.

    Thanks in advance!

    • Moved by Alex Liang Friday, August 20, 2010 3:03 AM (From:Visual Basic Express Edition)
    Wednesday, August 4, 2010 9:05 AM


All replies

  • Perhaps you could explain the relationship between the Excel Worksheet and the ListView control and the other controls on Winform? I'm not making a connection here.

    Below is an example of how to add items to a ListView:



    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by ddynamix Tuesday, August 24, 2010 1:27 PM
    Wednesday, August 4, 2010 3:40 PM
  • OK, let me rephrase. I have a form that will filter results according to the choices made by the end-user. After the results is displayed in the listview, I need some sort of print function to print the selected results (in the listview). Now seeing that VB Express does not have any reporting functionality and there is no free reporting tool available (those that are, lack documentation), I need to turn to some sort of third party reporting manner. Thus, Excel Automation.

    I've drawn up a spreadsheet. I need the data that is displayed in the listview to be carried over to the spreadsheet. That's what I'm after. But there will be multiple records displayed with the same details in the first column but differ in details on the second, third, fourth, fifth columns. I can't exactly use the "For...End For" statement and neither can I use "While...End While" as there are records in the database (and listview) that will contain the same name but not the same assignment name or score. I've been thinking about using the "Do While...Loop" function but that isn't going to work either as the program needs to check in which column the result must go depending on the assessment name.

    e.g. L10 = Assessment 1
    M10 = Assessment 2
    N11 = Assessment 3

    A24 = Student name. Columns L24 - R24 must contain the scores for that specific student. Scores must be carried over to the correct column where the assessment name (L10/M10/N10) = assessment name in the table for the student. The student is matched with the enrollment number but although there is different assessment names and scores, there will be thousands of records with the same enrollment number and student name.

    It's a pitty I can't attach a screenshot of what I'm wanting to do, but I do hope the above is more clear. If it's not, please let me know. Thanks!
    Thursday, August 5, 2010 8:57 AM
  • Juan,

    Try to avoid the Listview for data, it is fine for files but for data it cost only bunches of time, while your user will not be happy (only the first column is for instance editable)

    Use a DataGridView and hide the ColumnHeaders and the Rowheaders (just two properties to set)

    You won't see the difference, cost less time and your users will probably be much happier.


    Thursday, August 5, 2010 9:14 AM
  • Looks like you need something like this algorithm:

       Assuming each record contains student details and a single assignment mark.
       1 Sort the records by assignment within student reference ( you should not use name because you can have two studens with same name but they should have different identifiers)
       2 Read a record to get student
       3 Set X to be row for first student
       4 While still got a student
            output name to column A, row X
            Set currentStudent = student
            while currentStudent = student
               Set Y to column for assignment
               output mark in column Y, row X
               read next record
            next assignment
            Increment X
          Next student

    In the ListView, the students might be SubItem(0), while the assignments are the other SubItems. It's not clear if a ListView row contains multiple assignents for a student or what.

    One query, you say there may be thousands of records with the same enrollment number. That seems implausible, I don't know any courses where there are thousands of assignments. And I don't know anyone who would enroll on such a course. :)

    Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.
    Thursday, August 5, 2010 9:36 AM
  • Let me put it this way:

    Students will be enrolled and assigned an enrollment number. This enrollment number NEVER changes throughout the primary school cycle. When the student moves to highschool (secondary school), a new enrollment number will be assigned which also NEVER changes throughout the secondary school cycle. So what basically happens is:

    A student must complete 5 assignments per quarter. Each student is assigned to a class and class year. This is the sorting "mechanism". Indexing will occur for: ID (AutoNumber), Quarter (1 - 4), Class Year (2010 - xxxx), and Class Mnemonic. A student can never have the same class year and class mnemonic unless he/she needs to repeat the year. In such a case, the mnemonic stays the same but the year will always increment. Thus, there will be approximately 60 entries for each student displaying the same enrollment number, but different ID numbers, quarters, class mnemonics and class years. It's not for college or anything. This system is specifically designed for the new educational system in SA.

    I will try the code you supplied and see if it works for me. I will give feedback shortly.

    Thanks for the replies, really appreciate them.
    Thursday, August 5, 2010 11:34 AM
  • Hi, Juan

    A new forum has started for excel automation, you may post the excel automation related question in this




    Please unmark it if it does not help, and mark it if it helps.
    • Marked as answer by ddynamix Tuesday, August 24, 2010 1:27 PM
    Tuesday, August 10, 2010 5:29 AM