1 Replies - 6601 Views - Last Post: 11 October 2012 - 07:35 PM Rate Topic: -----

#1 Supermario65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-October 12

Read excel file line by line and write to other file

Posted 11 October 2012 - 01:10 PM

Hi,

I'm an all-round programmer, but VB.NET and I never get along.

I have one excel file with a bunch of data. I want to read each row, check if column X is a specific value and if so, write that row to a new excel file (without adding empty rows in output file).

The 'check if column = X' is just to get things started.


I'll post my code below, but it's the result of trying 20 different approaches and none of them seem to work. Right now my variable 'value_range' in ReadData has a 'Cells' property with all the cells of the first excel file, but I can't figure out how to get one row out.

I'm also outputting "Height", but I'm guessing that that's the actual height of a row in excel, which is useless to me. I'm really frustrated because of the terrible documentation (I'm using these pages: http://msdn.microsof..._members.aspx). If there's a better documentation out there, please let me know.

So this is my code so far. It won't make that much sense, but I don't want you guys to think I'm not trying.

Private Sub ReadData()
        ' GetCell = mExcelApp.Sheets(SheetName).cells(Column, Row).value
        Try
            Dim sheet As Excel.Worksheet = mExcelApp.Sheets(strSheetName)
            Dim rowCount As Integer = sheet.UsedRange.Rows.Count
            Dim columnCount As Integer = sheet.UsedRange.Columns.Count


            Dim rows As Excel.Range = sheet.UsedRange.Rows

            For c As Integer = 1 To 100
                Dim value_range As Excel.Range = rows.Range(c)

                trace("Height: " & value_range.Height & ", Count: " & value_range.Count)

                'AddRow(value_range.Columns)
            Next


        Catch ex As Exception
            trace(ex.Message)
        End Try

    End Sub


'Never got it to write data to my output file, but I'm focusing on the filtering first
Public Sub AddRow(ByVal row As Range)
        trace("Printing row " & outputcounter)




        Dim value_range As Excel.Range = outputsheet.Range("A" & outputcounter).Resize(1, row.Height).Columns

        trace("Height: " & value_range.Height & ", Count: " & value_range.Count)
        trace(vbCrLf)
        outputcounter = outputcounter + 1
        value_range.Value2 = row



    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Read excel file line by line and write to other file

#2 Supermario65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-October 12

Re: Read excel file line by line and write to other file

Posted 11 October 2012 - 07:35 PM

I'm getting pretty good results with the following code. It appears that UsedRange is only usable to get the dimensions (and not the actual cells).

This code is completely hacked together (it's just a proof of concept), but someone with a similar problem might find this useful.

The code loads an excel file, goes over it line by line and checks if the third column has a 'C'. If it has, the row is written into a new excel file.

Option Explicit On
'Option Strict On


Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class Form1



    Private mExcelApp As Excel.Application

    Private strSheetName As String = "filtered result_0"
    Private strFileName As String = "C:\patiŽnt.xlsx"
    Private strTargetName As String = "C:\Output.xlsx"


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        btnLoadFile_Click(Nothing, Nothing)
    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
        CloseWorkbook()

    End Sub

    Private Sub btnLoadFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadFile.Click


        CreateNewDoc()


        mExcelApp = New Excel.Application
        mExcelApp.Workbooks.Open(strFileName)

        ReadData()

        CloseWorkbook()


    End Sub



    Private Sub ReadData()
        ' GetCell = mExcelApp.Sheets(SheetName).cells(Column, Row).value
        Try
            Dim sheet As Excel.Worksheet = mExcelApp.Sheets(strSheetName)
            Dim rowCount As Integer = sheet.UsedRange.Rows.Count
            Dim columnCount As Integer = sheet.UsedRange.Columns.Count


            Dim rows As Excel.Range = sheet.UsedRange.Rows

            For c As Integer = 1 To 100
                Dim value_range As Excel.Range = sheet.Range("A" & c, "S" & c)

                Dim array As Object = value_range.Value2



                trace(array(1, 3))

                If (String.Compare(array(1, 3), "c")) Then

                    AddRow(array)
                End If

            Next


        Catch ex As Exception
            trace(ex.Message)
        End Try
        trace("Done")
    End Sub


    Private Sub trace(ByVal str As String)
        txtOutput.AppendText(vbCrLf & str)
    End Sub



    Public Sub CloseWorkbook() 'Cleans up our mess If you don't do this you will have an instance of excel runnign on your task list you just can't see it.        
        mExcelApp.ActiveWorkbook.Close(True) 'This is needed so you don't get a do you wanna save message        
        mExcelApp.Workbooks.Close() 'This closes all workbooks in our application
        mExcelApp.Quit()
        mExcelApp = Nothing 'This stops running excell
    End Sub


    Public Sub AddRow(ByVal row As Object)

        Dim value_range As Excel.Range = worksheet.Range("A" & outputcounter, "S" & outputcounter)

        'trace("Height: " & value_range.Height & ", Count: " & value_range.Count)
        trace(vbCrLf)
        outputcounter = outputcounter + 1
        value_range.Value2 = row
    End Sub

    Dim outputApp As New Microsoft.Office.Interop.Excel.Application
    Dim workbook As Workbook
    Dim worksheet As Worksheet
    Dim outputcounter As Integer = 1


    Public Sub CreateNewDoc()

        outputApp.Visible = True

        workbook = outputApp.Workbooks.Add
        worksheet = workbook.Worksheets(1)

    End Sub

End Class


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1