Author Topic: Controlling Excel with EPPLUS  (Read 1082 times)

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Controlling Excel with EPPLUS
« on: October 16, 2017, 03:54:39 AM »


I've been collecting data now for a couple of weeks and all is working good. The current project is copying 7 array (500 elements in each) to the file. I am using a string tag (MyValues20) with mmddyyyy as well as shift "1st" and this is the name of the tab. I  save  this data each shift, clear the array and start over. As long as the value is MyValues20 is different than last it creates a new tab and writes the data. This has been working great.


Code: [Select]
'*Creates Excel file
            Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Test.xlsx"))
                ExcelPackage.Workbook.Worksheets.Add(MyValues20)
                Console.WriteLine("ws creation ok")


                For Index = 0 To MyValues0.Length - 1
                    Console.WriteLine("Element " & Index & "=" & MyValues0(Index))
                    Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(MyValues20)
                    Dim CellNum As String = "A" & (Index + 2)
                    ws.Cells(CellNum).Value = MyValues0(Index)
                Next
                Console.WriteLine("Inspect")

I've moved on to a smaller application only collecting data from 7 tags twice daily.  Instead of creating a new tab I want to just index to the next row and let it run for the month but as long as MyValues20 is same as existing tab i get an exception error.

Do I need to ignore this line "ExcelPackage.Workbook.Worksheets.Add(MyValues20)" if the tab already exist?

Thanks.

Archie

  • Administrator
  • Hero Member
  • *****
  • Posts: 5260
    • View Profile
    • AdvancedHMI
Re: Controlling Excel with EPPLUS
« Reply #1 on: October 16, 2017, 05:53:18 PM »
Try something like this:
Code: [Select]
        Using ExcelPackage As New OfficeOpenXml.ExcelPackage(New IO.FileInfo("C:\Test.xlsx"))
            Dim ws As OfficeOpenXml.ExcelWorksheet = ExcelPackage.Workbook.Worksheets(MyValues20)
            If ws Is Nothing Then
                ws = ExcelPackage.Workbook.Worksheets.Add(MyValues20)
            End If

             For Index = 0 To MyValues0.Length - 1
                 Dim CellNum As String = "A" & (Index + 2)
                 ws.Cells(CellNum).Value = MyValues0(Index)
             Next

bob1371

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Controlling Excel with EPPLUS
« Reply #2 on: October 19, 2017, 01:57:54 AM »
Thanks Archie,

Made these changes and a couple other things and working great.