Export data from excel to a database table using ClosedXML example

Question

i have to read rows of data from an excel file which has only a column and then i have to save the rows in a table in database . In my project i have to use ClosedXML .dll . I have search but i couldn't find an example . Can you please help me? Thanks

Answer 1

For the ClosedXML part, you can refer to the documentation at https://github.com/ClosedXML/ClosedXML/wiki/Finding-and-extracting-the-data

private static void Main()
{
  List<String> categories;
  List<String> companies;
  ExtractCategoriesCompanies("NorthwindData.xlsx", out categories, out companies);

  // Do something with the categories and companies
}

private static void ExtractCategoriesCompanies(string northwinddataXlsx, out List<string> categories, out List<string> companies)
{
  categories = new List<string>();
  const int coCategoryId = 1;
  const int coCategoryName = 2;

  var wb = new XLWorkbook(northwinddataXlsx);
  var ws = wb.Worksheet("Data");

  // Look for the first row used
  var firstRowUsed = ws.FirstRowUsed();

  // Narrow down the row so that it only includes the used part
  var categoryRow = firstRowUsed.RowUsed();

  // Move to the next row (it now has the titles)
  categoryRow = categoryRow.RowBelow();

  // Get all categories
  while (!categoryRow.Cell(coCategoryId).IsEmpty())
  {
    String categoryName = categoryRow.Cell(coCategoryName).GetString();
    categories.Add(categoryName);

    categoryRow = categoryRow.RowBelow();
  }

  // There are many ways to get the company table.
  // Here we're using a straightforward method.
  // Another way would be to find the first row in the company table
  // by looping while row.IsEmpty()

  // First possible address of the company table:
  var firstPossibleAddress = ws.Row(categoryRow.RowNumber()).FirstCell().Address;
  // Last possible address of the company table:
  var lastPossibleAddress = ws.LastCellUsed().Address;

  // Get a range with the remainder of the worksheet data (the range used)
  var companyRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed();

  // Treat the range as a table (to be able to use the column names)
  var companyTable = companyRange.AsTable();

  // Get the list of company names
  companies = companyTable.DataRange.Rows()
    .Select(companyRow => companyRow.Field("Company Name").GetString())
    .ToList();
}

Answer 2

EDIT: The below only gets you a populated datatable. You'll then need to load that datatable into your database. You don't say which database this is, but for SQL Server you would use the SqlBulkCopy class (see definition, which also has an example).

Late to the party, but try this:

public static DataTable GetDataTableFromExcel(string path, string sheetname = "", bool hasHeader = true)
{
  using (var workbook = new XLWorkbook(path))
  {
    IXLWorksheet worksheet;
    if (string.IsNullOrEmpty(sheetname))
      worksheet = workbook.Worksheets.First();
    else
      worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == sheetname);

    var rangeRowFirst = worksheet.FirstRowUsed().RowNumber();
    var rangeRowLast = worksheet.LastRowUsed().RowNumber();
    var rangeColFirst = worksheet.FirstColumnUsed().ColumnNumber();
    var rangeColLast = worksheet.LastColumnUsed().ColumnNumber();

    DataTable tbl = new DataTable();

    for (int col = rangeColFirst; col <= rangeColLast; col++)
      tbl.Columns.Add(hasHeader ? worksheet.FirstRowUsed().Cell(col).Value.ToString() : $"Column {col}");

    Logger("started creating datatable");

    rangeRowFirst = rangeRowFirst + (hasHeader ? 1 : 0);
    var colCount = rangeColLast - rangeColFirst;
    for (int rowNum = rangeRowFirst; rowNum <= rangeRowLast; rowNum++)
    {
      List<string> colValues = new List<string>();
      for (int col = 1; col <= colCount; col++)
      {
        colValues.Add(worksheet.Row(rowNum).Cell(col).Value.ToString());
      }
      tbl.Rows.Add(colValues.ToArray());
    }

    Logger("finished creating datatable");
    return tbl;
  }
}

and call like this:

var datatable = GetDataTableFromExcel(fileName, sheetName);

If you're using (the excellent and free in its basic form) LinqPad, you can inspect the datatable using datatable.Dump();