Manipulating Excel in Visual Studio

Navigation
0

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

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

I was able to get the data from a Web Service and save it to an Excel worksheet, creating this file in Excel and all. However, there is another Web service that depends on the information contained in this spreadsheet. This information is in a column named Sequence .

In case what I need:

  • The number of cells and variables.
  • I need to open this worksheet
  • I need 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 moves to the next cell until it finds an empty cell.

Below is an explanation:

Assuming these are the Spreadsheet data: 

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

In case I want the variable to receive the values from cells A4 to A7, since from A8 onwards there is no data.

The variable is Sequence of type String and I need it to look like this: 

Sequencia = 1,2,3,4

How can I do this? Remembering that I'm programming in Visual Basic in Visual Studio 2017.

    
asked by anonymous 21.11.2017 / 21:05

1 answer

0

It will depend on the library you use, I did not program in Visual Basic, but I did this 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 this case I'm using ExcelPackage by passing the stream from the file you opened, you can use another library to open the file, such as ReaderStream.

I hope this helps you a little bit.

    
21.11.2017 / 21:31
How to use the self-generated mvnw.cmd file from the Spring Initializer site? How to create a "* .exe" executable in python?