This project has moved. For the latest updates, please go here.

.ToTable() to return TableData

Oct 28, 2011 at 9:29 AM
public System.Data.DataTable Import(string sheetName) 
{ 
var datatable = new DataTable(); 
var workbook = new XLWorkbook(this.FilePathXls + this.FileNameXls); 
var xlWorksheet = workbook.Worksheet(sheetName); 
IXLRange xlRangeRow = xlWorksheet.AsRange(); 
var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed()); 
IXLCell rowCell = xlWorksheet.LastCellUsed(); 
int col = range.ColumnCount(); 
int row = range.RowCount(); 
// add columns hedars 
datatable.Clear(); 
for (int i = 1; i <= col; i++) 
{ 
IXLCell column = xlWorksheet.Cell(1, i); 
datatable.Columns.Add(column.Value.ToString()); 
} 
// add row data
foreach (var item in range.Rows()) 
{ 
// some code prepears
IXLRange rangeRow = item.AsRange(); 
IXLTable tableRow = item.AsRange().AsTable(); 
IXLCells cellTable = item.Cells(); 
IEnumerable ienumEnumerable = item.Cells().AsEnumerable(); 
IQueryable iQueryable = item.Cells().AsQueryable();  
// here have error
/*
exception apear in: public XLRange Range(IXLRangeAddress rangeAddress)
exception message is: Specified argument was out of the range of valid values.
Parameter name: The cells A2 and C1 are outside the range ''Data Solution Sheet'!A1:C1'.
*/
datatable.Rows.Add(tableRow.Columns().ToList()); 
} 
return datatable; 
}
Oct 28, 2011 at 12:29 PM

Thank you for support ;)

 

///<summary> 
        /// Get all data from a sheet Excell file      
        ///</summary> 
        public System.Data.DataTable Import(string sheetName)
        {            
            var datatable = new DataTable();
            // var dataset = new DataSet();
            var workbook = new XLWorkbook(this.FilePathXls + this.FileNameXls);
            var xlWorksheet = workbook.Worksheet(sheetName);

            //IXLRange xlRangeRow = xlWorksheet.AsRange();                        
            var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());            
            // IXLCell rowCell = xlWorksheet.LastCellUsed();
           
            int col = range.ColumnCount();  
            int row = range.RowCount();

            // add columns hedars
            datatable.Clear();
            
            for (int i = 1; i <= col; i++)
            {
                IXLCell column = xlWorksheet.Cell(1, i);
                datatable.Columns.Add(column.Value.ToString());
            }

            // add rows data   
            int firstHeadRow = 0;     
            foreach (var item in range.Rows())
            {
                if (firstHeadRow != 0)
                {
                    var array = new object[col];
                    for (int y = 1; y <= col; y++)
                    {
                        array[y - 1] = item.Cell(y).Value;
                    }
                    datatable.Rows.Add(array);
                }
                firstHeadRow++;
            }                    
            return datatable;
        }

Coordinator
Oct 28, 2011 at 4:31 PM

I don't understand the problem, could you please clarify?

Oct 31, 2011 at 9:05 AM

Just simple presentation for importing only excel 2007 format. Not any problem.

If you make show more fast example?