Convert range to table, treat all rows as data rows

Mar 19, 2013 at 8:25 PM
I need to read data from an Excel file into a DataTable, and the Excel file may not include column headers. I have tried using IXLRange.AsTable() as well as IXLRange.CreateTable(), and apparently both methods treat the first row in the range as a header row. That first row needs to be included in the table. Is there a more appropriate method I should use?

Thanks in advance.
Mar 21, 2013 at 9:05 PM
When you say DataTable, do you mean System.Data.DataTable so you can use it in a DataGrid control? While ClosedXML can take a DataTable or DataSet or even a List of objects and automatically create an Excel worksheet out of it, there is nothing I know of that would do the reverse. It would be a nice feature if it could. Even the methods CreateTable() and AsTable() do not create DataTable objects. They create IXLTable objects.
As far as I know, in order to do what you want, you could iterate through the range and build your DataTable one row at a time.
You could probably create a LINQ expression on the your IXLRange object that would return a IENumerable list of structures.
That would be a pretty succinct and classy piece of coding. I'd love to see a sample of that code put into the documentation area.

I am routinely creating Lists of objects representing data rows. I would love to see an enhancement where I could pass an object into ClosedXML where the properties of the object had the same names as columns in the Excel sheet and have ClosedXML automatically fill in the data fields and return me a list of the objects. Even if it would do it one row at a time, that would be heaven.
If that exists you could define your DataRow and then fill your DataTable.Rows collection with the rows from the Excel sheet.
// Get a list of objects from the Excel sheet
List<MyObject> myList = new List<MyObject>();
foreach ( IXLRow row in workSheet.RangeUsed(false).Rows() )
{
    MyObject myObject = new MyObject();
    myList.Add(row.FillObject(myObject));
}
Wouldn't that be grand! Too bad it doesn't exist yet.
Oct 6, 2015 at 4:57 AM
Code which exports data from worksheet to datatable (iterative approach):
/// <summary>
/// Exports data and column names from ClosedXML worksheet to DataTable
/// </summary>
public static DataTable ExportWorkSheetToDataTable(IXLWorksheet worksheet)
{
    DataTable dt = new DataTable();
    int ColumnsQty = worksheet.ColumnsUsed().Count();
    int RowsQty = worksheet.RowsUsed().Count();

    // Create column names for DataTable
    for (int i = 1; i <= ColumnsQty; i++)
    {
        dt.Columns.Add(worksheet.Row(1).Cell(i).GetValue<string>());
    }

    // Add data to DataTable
    for (int i = 2; i <= RowsQty; i++)
    {
        DataRow workRow = dt.NewRow();
        for (int j = 1; j <= ColumnsQty; j++)
        {
            var CellValue = worksheet.Row(i).Cell(j).Value;
            workRow[j - 1] = CellValue;
        }
        dt.Rows.Add(workRow);
    }
    return dt;
}
Nov 6, 2015 at 4:56 PM
Below is the code we use to convert a Range to a DataTable.
We had an issue with certain dateformats being the Julian # of days since 1/1/1900 so this tries to convert those. This also converts RichText to HTML:
public static DataTable RangeToDataTable(IXLRange range, bool firstLineHeadings = true)
{
    if (range.IsMerged())
    {
        range = range.Unmerge(); // Unmerge any merged cells
    }

    DataTable dt = new DataTable();
    // First just do headers (1st row in Range)
    int columnCnt = range.ColumnCount();
    for (int k = 1; k <= columnCnt; k++)
    {
        var name  = range.Row(1).Cell(k).Address.ColumnLetter;
        if (firstLineHeadings) name = range.Row(1).Cell(k).GetString();
        dt.Columns.Add(name);
    }

    bool firstRow = true;
    foreach (var myrow in range.RowsUsed()) // RowsUsed seems to also make merged called all report the same value (which we want)
    {
        if (firstRow && firstLineHeadings) { firstRow = false; continue; }
        if (myrow.IsEmpty()) continue;
        DataRow workRow = dt.NewRow();
        for (int j = 1; j <= columnCnt; j++)
        {
            IXLCell cellObj = myrow.Cell(j); // not 0 based
            var cellValue = cellObj.Value;
            var checkForDate = cellObj.GetFormattedString();
            if (checkForDate.Contains("-yy") && checkForDate.Contains("[$-"))
                cellValue = FromExcelSerialDate(cellValue);
            else
            {
                if (cellObj.HasHyperlink && cellObj.Hyperlink.IsExternal) cellValue = "<a href='" + cellObj.Hyperlink.ExternalAddress + "'>" + cellObj.Value + "</a>";
                if (cellObj.HasRichText) cellValue = RichTextToHTML(cellObj); // If the link is formatted this overwrites with the prettier link
            }
            workRow[j - 1] = cellValue; // 0 based in DataRow object for [] indexing
        }
        dt.Rows.Add(workRow);
    }
    return dt;
}

public static DateTime FromExcelSerialDate(object serialDate)
{
    int excelNumber = int.Parse(serialDate.ToString());
    if (excelNumber > 59) excelNumber -= 1; //Excel/Lotus 2/29/1900 bug   
    return new DateTime(1899, 12, 31).AddDays(excelNumber);
}

public static string RichTextToHTML(IXLCell cell)
{
    StringBuilder myString = new StringBuilder();
    // Special case when the whole cell has a link vs. a http:// string in one of the rich text parts below in the loop
    if (cell.HasHyperlink && cell.Hyperlink.IsExternal) myString.Append("<a href='" + cell.Hyperlink.ExternalAddress + "'>");
    foreach (var richText in cell.RichText)
    {
        myString.Append("<span style='"); // Start SPAN and CSS here then add to it below

        if (richText.Bold) myString.Append("font-weight: bold;");
        if (richText.Strikethrough) myString.Append("text-decoration:line-through;");
        if (richText.Italic) myString.Append("font-style: italic;");
        if (richText.Underline == XLFontUnderlineValues.Single) myString.Append("text-decoration: underline;");
        if (richText.FontName != "Arial") myString.Append("font-face: " + richText.FontName + ";");
        if (richText.FontColor.Color.IsKnownColor) myString.Append("color: " + richText.FontColor.Color.Name + ";");
        if (richText.FontSize != 10) myString.Append("font-size: " + richText.FontSize + ";");
        myString.Append("'>"); // End the SPAN
        if (richText.Text.Contains("http")) myString.Append("<a href='" + richText.Text + "'>");

        myString.Append(richText.Text.Replace("\r\n", "<br>"));
        if (richText.Text.Contains("http")) myString.Append("</a>");
        myString.Append("</span>");
    }
    if (cell.HasHyperlink && cell.Hyperlink.IsExternal) myString.Append("</a>");
    return myString.ToString();
}