SpreadSheetGear WorkbookView - Cells

Question

I have a SpreadSheetGear WorkbookView in my form. The SpreadSheetGear WorkbookView has filled with excel file 10 rows and 10 columns. I want to get the data from the cells using the code below:

 for (int i = 0; i < cells.Range.RowCount; i++)
 {
   for (int j = 0; j < cells.Range.ColumnCount; j++)
   {
     arrLstWorkBookView.Add(cells[i, j].Text);
     arrLstOutputData.Add(cells[i, j].Text);
   }
 }

and the debugger shows me rowCount = 1048576 instead of 10, and rowCount = 16384 instead of 10.

How to resolve the problem with WorkbookView and to take the right number of rows and columns from excel file rows should be 10 and columns should be 10.

Unfortunately your values are coming from the full worksheet maximums rather than populated values: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 — SharpC, Jan 25 '22 at 13:35

Tim Andersen · Answer 1 · 2013-07-19T19:10:43.117

You don't provide a line of code that indicates where the cells variable is coming from, but if you are getting a row count of 1,048,576, this indicates to me that cells came from IWorksheet.Cells/Range, both of which represents all cells in a worksheet (A1:XFD1048576).

If you are trying to iterate over some smaller range within the worksheet, you'd need to specify this range with IWorksheet.Cells["A1_Ref"] or IWorksheet.Cells[row1,col1,row2,col2]. Example:

// The following two lines are equivalent
IRange cells = worksheet.Cells["A1:J10"];
IRange cells = worksheet.Cells[0, 0, 9, 9];
// ...the rest of your code...

If you want to iterate through the "used range" of a worksheet you would want to use IWorksheet.UsedRange instead. Example:

IRange cells = worksheet.UsedRange;

Also, your call into Range in cells.Range.RowCount is redundant, as IRange.Range simply represents the specified IRange. You could just use cells.RowCount.

You may also consider looking through our documentation for the IRange indexer (IRange.Item(…)), which demonstrates a number of different ways to reference cells: http://www.spreadsheetgear.com/support/help/spreadsheetgear.net.7.0/#SpreadsheetGear2012.Core~SpreadsheetGear.IRange~Item.html

Disclaimer: I work for SpreadsheetGear.

score 2 · Answer 2 · edited May 15 '20 at 16:43

2

You should use

SpreadsheetGear.IRange cells = worksheet.UsedRange;

edited May 15 '20 at 16:43

Richard Housham

1,525
2
15
31

answered May 15 '20 at 15:27

Pradeep Gudlanur

21
1

score 0 · Answer 3 · answered Jul 18 '13 at 10:42

Unless you know for sure the range is always 10x10, in which case it is just a matter of changing the loop conditions, you will need a way to detect consecutive values.

This code snippet should help you doing so

/// <summary>
/// gets the number of consecutive (without any empty cell) values vertically
/// </summary>
/// <param name="aRow"></param>
/// <param name="aColumn"></param>
/// <returns></returns>
public int GetNumberConsecValues(int aRow, int aColumn)
{
   int wCount = 0;
   while (this[aRow + wCount, aColumn] != null)
   {
     wCount++;
   }
   return wCount;
}

and use it to read the values into an array/matrix as needed:

/// <summary>
/// Reads the vertical array from the specified cell
/// </summary>
/// <param name="aRow"></param>
/// <param name="aColumn"></param>
/// <returns></returns>
public int[] ReadArrayInt(int aRow, int aColumn)
{
  int wNbValues = this.GetNumberConsecValues(aRow, aColumn);
  if (wNbValues == 0)
     return null;

  int[] wArr = new int[wNbValues];
  for (int iRow = 0; iRow < wNbValues; iRow++)
  {
    wArr[iRow] = Convert.ToInt32(this[aRow + iRow, aColumn]);
  }

  return wArr;
}

If you know the Range then you can use this other code snippet

public object[,] ReadRange(int aColFrom, int aColTo, int aRowFrom, int aRowTo)
{
  this.BeginUpdate();
  try
  {
    SpreadsheetGear.IRange oRange = m_ViewLock.Workbook.ActiveWorksheet.Cells[aRowFrom, aColFrom, aRowTo, aColTo];

    object[,] oValues = new object[aRowTo - aRowFrom + 1, aColTo - aColFrom + 1];

    int iRCol = 0;
    for (int iCol = aColFrom; iCol <= aColTo; iCol++)
    {
      int iRRow = 0;
      for (int iRow = aRowFrom; iRow <= aRowTo; iRow++)
      {
        oValues[iRRow, iRCol] = oRange.Cells[iRRow, iRCol].Value;
        iRRow++;
      }
      iRCol++;
    }
    return oValues;
  }
  finally
  {
    this.EndUpdate();
  }
}

Where BeginUpdate and EndUpdate are defined as:

/// <summary>
/// Must be called before the grid is being modified
/// We also call it inside every method so that if the user forgets to call it is no big deal
/// For this reason the calls can be nested.
/// So if the user forgets to make the call it is safe
/// and if the user does not forget he/she gets the full speed benefits..
/// </summary>
public void BeginUpdate()
{
  if (m_ViewLockCount == 0)
    m_ViewLock = new CWorkbookViewLockHelper(this.Workbook);

  m_ViewLockCount++;
}

/// <summary>
/// Must be called after the grid has being modified
/// </summary>
public void EndUpdate()
{
  m_ViewLockCount--;
  if (m_ViewLockCount <= 0)
  {
    m_ViewLock.Dispose();
    m_ViewLock = null;
  }
}

score 0 · Answer 4 · answered Jul 21 '13 at 04:29

The other way to get grids of data is through datatable as follows:

In the spreadsheet name a range

MyTable =Sheet1!$A$1:$J$11

This is MVC4 / Razor but it should translate to ASP.NET

<!-- language: c# -->
// Declare a DataTable variable
public DataTable myTable;

// Open the workbook.
String filename = HttpContext.Server.MapPath("myspreadsheet.xlsx");
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename);


//Get the table as a dataset:
DataSet dataSet = workbook.GetDataSet("MyTable", SpreadsheetGear.Data.GetDataFlags.FormattedText);
myTable = dataSet.Tables[0];

//Which can also be written as:
myTable = workbook.GetDataSet("MyTable", SpreadsheetGear.Data.GetDataFlags.FormattedText).Tables[0];

In my view I call a partial view with the name of the DataTable:

@Html.Partial("_DataTable", Model.myTable)

And the DataTable partial view looks like this:

@using System.Data;
@model DataTable
<table>
@{DataRow rowHead = Model.Rows[0];
    <thead>
        <tr>
            @foreach (DataColumn colHead in Model.Columns)       
            {
                <th>@rowHead[colHead.ColumnName]</th> 
             }
        </tr> 
    </thead>
}
    <tbody>
        @{int rowCount = 1;}
        @foreach (DataRow row in Model.Rows)
            {
                <tr>
                    @foreach (DataColumn col in Model.Columns)
                    {
                        <td>@row[col.ColumnName]</td> 
                     }
                </tr>
               rowCount = rowCount + 1;
            } 
    </tbody>
</table>

A quirk of this code is that the first row of the range is not available in the table. Somebody else needs to explain this but I believe that it is because the range is first fed into a DataSet and the first row is preserved for column names. Perhaps some smarter code than mine can extract this in the partial view.

This example also shows how to render a DataTable from a spreadsheet range.

SpreadsheetGear DataTable example

SpreadSheetGear WorkbookView - Cells

4 Answers4