Import, Parsing & Export Excel in VB Net RRS feed

  • Question

  • Hi,

    I am working on one of my  projects so any assistance or advice would be greatly appreciated.

    The project detail:

    two excel files need to be imported, one with raw data, another one work as template .

    I need to parse these excel files, and populate the data from excel file with raw data, to the template excel, which located at different cell. There are few criteria I need to include within the process.

    Criteria  need to be included:

    1. A1+B1, A2+B2, A3+B3, etc is the serial number+Model Name, the following cells (C1+D1, C2+D2, C3+D3, etc),  are the value.

    2. A1+B1, A2+B2, A3+B3, etc need to be verified in raw data excel file, and compare to the A1+B1, A2+B2, A3+B3, etc in template excel, if both A1+B1, A2+B2 etc data matched, the C1+D1, C2+D2, etc value will export to template excel.

    3. When export to template excel, C1+D1, C2+D2, C3+D3, etc from raw data excel file need to populate to different cell in template excel file, as C1+C2, D1+D2, E1+E2, etc. these value need to be populated up to AE1+AE2 which are vary.

    4. A1+B1 verified & matched, then value C1+D1 need to populate to C1+C2 in template excel file. If A2+B2 same value with A1+B1, the value C2+D2 will continue populate to D1+D2, and so on until the value in AX+BX is different, then will start in next new row.

    Thursday, June 15, 2017 9:29 AM

All replies

  • Hello,

    If the Excel file is Excel 2007 or above format there is a library called SpreadSheetLight (free and can be included into your project via NuGet right in Visual Studio) which has the functionality you need and easy to use.

    Next option is to use OleDb data provider, also easy to use but will take more work to work out the task.

    Last option is Excel automation which requires more code then the two options above.

    I can provide the basics for SpreadSheetLight if interested. To evaluate how SpreadSheetLight works if this might be an option see my MSDN code sample.

    If using Excel version prior to Excel 2007 then SpreadSheetLight is not an option.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, June 15, 2017 10:34 AM
  • It sounds like you've described the algorithm you plan to implement, so what part do you need assistance with?  If it is with coding the algorithm, you should give it a try first and see where you get stuck, then come back with a specific question.

    FWIW, I would only use an automated instance of Excel to populate the template.  The Raw Data file I would save as CSV and import it into my program that way.

    I'm also not sure you need VB at all... this looks like it can probably be done with a VLOOKUP and some basic comparison and assignment functions.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, June 15, 2017 12:58 PM