0

I am trying to upload a large Ms Excel file into a relational database using C#.

Using following code:

But the process takes a very long time to read the excel (Approximated 45 hours to read an excel file with 185,000 record).

public static DataTable GetSpreadsheetWorkbookSheet(string filepath)
{
    DataTable dataTable = new DataTable();
    List<string> sheetList = new List<string>();
    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filepath, true))
    {
        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        int sheetCount = workbookPart.Workbook.Descendants<Sheet>().Count();
        string relationshipId = "";
        dataTable.Columns.Add("Sheet");
        dataTable.Columns.Add("Path");
        for (int i = 0; i < sheetCount; i++)
        {
            DataRow dataRow = dataTable.NewRow();
            string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(i).Name;
            relationshipId = workbookPart.Workbook.Descendants<Sheet>().ElementAt(i).Id;
            dataTable.Rows.Add(sheetName, filepath);
        }
    }

    return dataTable;
}

public static DataTable CreateSpreadsheetWorkbook(string filepath, string sheetName)
{
    DataTable dataTable = new DataTable();
    List<string> sheetList = new List<string>();
    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filepath, true))
    {
        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = "";
        int x = sheets.ToList().Count;
        if (sheets.ToList().Count > 1)
        {
            relationshipId = sheets.ToList().Find(io => io.Name.ToString().Equals(sheetName)).Id;
        }
        else
        {
            relationshipId = sheets.ToList().First().Id.Value;
        }
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();
        foreach (Cell cell in rows.ElementAt(0))
        {
            dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }

        foreach (Row row in rows)
        {
            int t = row.Descendants<Cell>().Count();
            DataRow dataRow = dataTable.NewRow();

            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                Thread.Sleep(1);
                dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                Thread.Sleep(1);
            }

            dataTable.Rows.Add(dataRow);
            Thread.Sleep(1);
        }

    }
    dataTable.Rows.RemoveAt(0);

    return dataTable;
}

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.InnerText;

    if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString))
    {
        string txt = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText.ToString();
        Thread.Sleep(1);
        return txt;

    }
    else if (cell.DataType != null && (cell.DataType.Value == CellValues.String))
    {
        string txt = value;
        Thread.Sleep(1);
        return txt;

    }
    else if (cell.DataType != null && (cell.DataType.Value == CellValues.InlineString))
    {
        string txt = stringTablePart.ToString();
        Thread.Sleep(1);
        return txt;

    }
    else if (cell.DataType == null)
    {
        string txt = value;
        Thread.Sleep(1);
        return txt;
    }
    else
    {
        if (String.IsNullOrEmpty(value) || String.IsNullOrWhiteSpace(value))
        {
            value = "0";
        }

        return Convert.ToDecimal(value).ToString("N4");
    }
}

I need to upload the file quickly, Is there anyway to doing it quickly, Please help?

uvr
  • 515
  • 4
  • 12
user11953618
  • 69
  • 7
  • What like library are you using to read Excel? – Tarik Jun 02 '21 at 08:38
  • 2
    https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-5.0 OR https://stackoverflow.com/a/18927213/585968. Basically get your data into a `DataTable` and the rest is easy –  Jun 02 '21 at 08:39
  • Interop with Excel is too slow either you can use some other libraries like Excel-DNA or you can use OleDb to [connect](https://stackoverflow.com/questions/43728201/how-to-read-xlsx-and-xls-files-using-c-sharp-and-oledbconnection) to Excel and make Sql queries – Eldar Jun 02 '21 at 08:43
  • 1
    "Approximated 45 hours to read an excel file with 185,000 record" - you're doing something very wrong! – Mitch Wheat Jun 02 '21 at 08:50
  • 5
    Do you really need all those `Thread.Sleep(1)`s? – Hans Kesting Jun 02 '21 at 08:54

0 Answers0