Fill spreadsheet with DataTable

For current versions

From version 0.8.7, reading data from DataTable is available by ReoGrid core, it’s possible to set the DataTable to any position on spreadsheet:

var adapter = new DataSet1TableAdapters.MyTableAdapter();
var dt = adapter.GetData();

worksheet["A1"] = dt;

For earlier versions

In earlier versions, ReoGrid cannot read data from DataTable, it is necessary to copy data into cell one by one:

// get a DataTable from database
var adapter = new DataSet1TableAdapters.MyTableAdapter();
var dt = adapter.GetData();

// iterate to copy all cells
for (int row = 0; row < dt.Rows.Count; row++)
{
  for (int col = 0; col < dt.Columns.Count; col++)
  {
     grid[row, col] = dt.Rows[row][col]; 
  }
}

Return to FAQ

6 Responses to “Fill spreadsheet with DataTable”

  1. naftaly weinberger says:

    I tried to fill reogrid with content of dataTable, following above code (“Worksheet[“A1″] = dt;”) but it doesn’t work, the code pastes the name of the table in the first cell instead of bringing in the dataTable contents, what am I doing wrong?

    • Eric Miller says:

      Had the same problem, got it to work doing this:

      sheet.Resize(dt.Rows.Count, dt.Columns.Count);
      sheet[“A1”] = dt;

      • Trafouli says:

        Hi !
        Realy?

        It’s working for you with resize code before?

        • Eric Miller says:

          Yes, new to using this Control and much needed for my application. The should be a standard Control in WPF. But here my code so you can see what I’m doing and in conjunction I’m using EPPLus to process the Sheet data.

          private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)
          {
          int totalRows = oSheet.Dimension.End.Row;
          int totalCols = oSheet.Dimension.End.Column;
          DataTable dt = new DataTable();
          DataRow dr = null;

          for (int i = 1; i <= totalRows; i++)
          {
          if (oSheet.Cells[i, 1].Value != null)
          {
          dr = dt.Rows.Add();
          for (int j = 1; j <= totalCols; j++)
          {
          if (i == 1)
          {
          dt.Columns.Add();
          dr[j – 1] = oSheet.Cells[i, j].Value == null ? string.Empty : oSheet.Cells[i, j].Value.ToString();
          }
          else
          dr[j – 1] = oSheet.Cells[i, j].Value == null ? string.Empty : oSheet.Cells[i, j].Value.ToString();
          }
          }
          }

          return dt;
          }

          using (ExcelPackage excelPkg = new ExcelPackage())
          {
          using (FileStream stream = new FileStream(ExcelFile.FileName, FileMode.Open, FileAccess.Read))
          {
          excelPkg.Load(stream);

          foreach (DocumentSheet sheetName in DocumentSheets)
          {
          DataTable d = new DataTable();
          var sheet = workSheetsGrid.CreateWorksheet(sheetName.Name);
          ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets[sheetName.Name];
          Task getWorksheetTask = Task.Factory.StartNew(() => WorksheetToDataTable(oSheet));

          DataTable dt = await getWorksheetTask;

          sheet.Resize(dt.Rows.Count, dt.Columns.Count);
          sheet[“A1”] = dt;
          workSheetsGrid.AddWorksheet(sheet);
          }
          }
          }

  2. naftaly weinberger says:

    again i tried severel times, its not working. (the other way, iterating cel by cel ro w by row works), what am i mising why is is giving me only the table name? your help will be greatly apricciated