Manipulating Excel in Visual Studio

Question

I’m doing an application in Visual Studio with Visual Basic. This application will serve to consume data from a Webservice.

I have a problem with one of these Webservices, what happens is this:

I was able to take the data from a Web Service and save it in an Excel spreadsheet, creating this file in Excel and everything. But there is another web service that depends on information contained in this created spreadsheet. This information is in a column with the name of Sequence.

In case what I need:

• The amount of cells and variables.
• I need to open this spreadsheet
• I need to make a Loop where the Range starts in the cell ( example: "A4" )

The code copies the value of this cell into a variable by adding a comma and go to the next cell until you find an empty cell.

Below is an explanation:

Assuming these are Spreadsheet Data:

A4 = 1    
A5 = 2    
A6 = 3    
A7 = 4

In case I want the variable to receive the values of cells A4 until A7, because from A8 onwards there are no data.

The variable is Sequence String type and I need her to stay that way:

Sequencia = 1,2,3,4

How can I do it ? Remembering that I am programming in Visual Basic in Visual Studio 2017.

Answer 1

It will depend on the library you use, I did not program in Visual Basic, but I did it in . Net

Follow the example in c#:

//starting
ExcelPackage pck = new ExcelPackage(stream);

var tab= 1;
ExcelWorksheet ws = pck.Workbook.Worksheets.ElementAt(tab);

var row = 4;
var column = 1;
var value = ws.Cells[row, column].Value

var result = "";

while(!string.IsNullOrEmpity(value)){

  if(!string.IsNullOrEmpty(result))
  {
    result += ","
  }

  result += value
  row++;

  value = ws.Cells[row, column].Value
}

//end

In such case I am using Excelpackage by going to stream the file you opened, you can use another library to open the file such as Readerstream.

I hope it helps you a little.