Question read from excel file and write into database table

[raysefo]

Hi,

I have an asp.net core 2.1 web application. I would like to read an excel file and insert it into a database table. I think OleDb is not supported so I need some other 3rd party library. Is there any good tutorial about reading from excel and inserting into DB?

Best Regards.

 

[Skydiver]

Is this a constant periodic import, or just something that happens one a month or only once? If the frequency is low, just manually save the Excel data to a .CSV, and then parse and read the .CSV in your .NET core app.

 

[raysefo]

It is periodic maybe once a week but the important point is, the client has to upload it from the web application.

 

[Skydiver]

Sounds simple enough. Only allow .CSV file uploads.

 

[raysefo]

The excel might have 5000 records at most, should I use sqlbulkcopy?

Here is my working code:

[HttpPost]
        [Authorize]
        public async Task<IActionResult> Upload(IFormFile postedFile)
        {
            if (postedFile == null || postedFile.Length == 0)
            {
                return RedirectToAction("ImportExcel");
            }

            //Get file
            var newfile = new FileInfo(postedFile.FileName);
            var fileExtension = newfile.Extension;

            //Check if file is an Excel File
            if (fileExtension.Contains(".xls"))
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    await postedFile.CopyToAsync(ms);

                    using (ExcelPackage package = new ExcelPackage(ms))
                    {
                        ExcelWorksheet workSheet = package.Workbook.Worksheets["Game"];
                        int totalRows = workSheet.Dimension.Rows;

                        List<GameBanks> customerList = new List<GameBanks>();

                        for (int i = 2; i <= totalRows; i++)
                        {
                            customerList.Add(new GameBanks
                            {
                                ProductDescription = workSheet.Cells[i, 1].Value.ToString(),
                                ProductCode = workSheet.Cells[i, 2].Value.ToString(),
                                UnitPrice = Convert.ToDouble(workSheet.Cells[i, 3].Value),
                                Quantity = Convert.ToInt16(workSheet.Cells[i,4].Value),
                                Version = workSheet.Cells[i,5].Value.ToString(),
                                Currency = workSheet.Cells[i,6].Value.ToString(),
                                TotalPrice = Convert.ToDouble(workSheet.Cells[i,7].Value),
                                Status = Convert.ToInt16(workSheet.Cells[i,8].Value),
                                Used = Convert.ToInt16(workSheet.Cells[i,9].Value)
                            });
                        }

                        _context.GameBanks.AddRange(customerList);
                        await _context.SaveChangesAsync();
                    }
                }
                
            }

            

            return RedirectToAction("Index");
        }

 

[Skydiver]

It is easier and faster to parse a comma delimited text file (e.g. .CSV) as opposed to decompressing a .zip file into component XML files, and then parsing the component XML files (e.g. a .XLSX). So for the same 5000 records you will be expending less computing resources to process them for import into your database.

Furthermore, you could require that the .CSV be compressed prior to upload and you decompress on the server. .CSV files compress very well and have less overhead than XML for the same data.

 

[vikasjkn]

I think you can use EPPlus.Core version.

For usual C#

 //provide file path
            FileInfo existingFile = new FileInfo(@"D:\sample_XLSX.xlsx");
            //use EPPlus
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                //get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        //Print data, based on row and columns position
                        Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
                    }
                }
            }

Reference

C# - Read excel file in C# (.XLSX or .XLS using OLEDB / EPPlus or Interop) - QA With Experts

In this article, I am going to provide you code with explanation to read excel in C# using OLEDB OR using EPPlus OR using Interop in Console application.

qawithexperts.com

 

[Skydiver]

Yes, but at $299 per year per developer for commercial use?

 

[vikasjkn]

Yes, but at $299 per year per developer for commercial use?

Right, forgot to mention there is NPOI also, which we can use. But Epplus older ( previous than 5 )versions are free.