C#, ASP.NET, .NET, SQL-Server, convert, ASP.NET-Core, .NET-Core
Introduction
This is the easiest and quick way to make a simple application for importing bulk data from Excel Sheet and insert the all data in one command into MSSQL server database using BulkCopy with Column Mapping. After importing display the all imported data into Html /mvc razor view page. And easy way to export the Grid/Html table data into pdf.
Using the code
//
Install-Package Microsoft.Data.SqlClient --version 5.1.1
Install-Package System.Data.OleDb --version 7.0.0
Install-Package EPPlus --version 6.2.7
Install-Package itext7.pdfhtml -Version 4.0.4
//
Step1:
Create a new asp.net core project ExcelToDataTable in Visual Studio.
Add the above packages.
Create a model view class FileUploadModel
//
using System.ComponentModel.DataAnnotations;
namespace ExcelToDataTable.Models
{
public class FileUploadModel
{
[Required(ErrorMessage = "Please select file")]
public IFormFile File { get; set; }
}
}
Put connectionStrings in appsettings.json . One is for MSSQL Server database connection.
Anothr is for Excel OleDb connection.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"constr": "Data Source=.;Initial Catalog=AssetManagement;
user id=sa;password=sapassword;MultipleActiveResultSets=True;
App=EntityFramework;Encrypt=false",
"ExcelConString": "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"
}
}
Step3:Create a Databse AssetManagement in MS SQl Server. And Create a database table Assets.USE [AssetManagement]
GO
/****** Object: Table [dbo].[Assets] Script Date: 7/24/2023 12:16:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Assets](
[Id] [int] IDENTITY(1,1) NOT NULL ,
[ItemName] [nvarchar](50) NULL,
[Price] [nvarchar](50) NULL,
[Quntity] [nvarchar](50) NULL,
[IsAvailable] [bit] NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
( [Id] ASC)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Assets] ADD CONSTRAINT [DF_Assets_IsAvailable] DEFAULT ((1))
FOR [IsAvailable]
GO
Create a Controller ExcelToDataTableController.
Add required namespaecs like the below code.
Actions:
i)Index
ii) Convert
a) import from excel
b) bulkCopy to mssql datatabase table (make sure database table name, column names are same as excel columns)
iii) Display
iv) Export
//
using ExcelToDataTable.Models;
using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.IO;
using Microsoft.AspNetCore.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using OfficeOpenXml;
using System.Text;
using iText.Html2pdf;
using iText.IO.Source;
using iText.Kernel.Geom;
using iText.Kernel.Pdf;
namespace ExcelToDataTable.Controllers
{
public class ExcelToDataTableController : Controller
{
private IConfiguration Configuration;
public ExcelToDataTableController( IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
FileUploadModel model = new FileUploadModel();
return View(model);
}
[HttpPost]
public IActionResult Convert(FileUploadModel model)
{
DataTable table = new DataTable();
try
{
if (model.File != null)
{
//if you want to read data from a excel file use this
//using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
using (var stream = model.File.OpenReadStream())
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage();
package.Load(stream);
if (package.Workbook.Worksheets.Count > 0)
{
using (ExcelWorksheet workSheet =
package.Workbook.Worksheets.First())
{
int noOfCol = workSheet.Dimension.End.Column;
int noOfRow = workSheet.Dimension.End.Row;
int rowIndex = 2;
for (int c = 1; c <= noOfCol; c++)
{
table.Columns.Add(workSheet.Cells[rowIndex, c].Text.Replace(" ","").Trim());
}
rowIndex = 3;
for (int r = rowIndex; r <= noOfRow; r++)
{
DataRow dr = table.NewRow();
for (int c = 1; c <= noOfCol; c++)
{
dr[c - 1] = workSheet.Cells[r, c].Value;
}
if (!string.IsNullOrEmpty(dr[0].ToString()))
{
table.Rows.Add(dr);
}
}
//Read the connection string for the Excel file.
string conString =
this.Configuration.GetConnectionString("ExcelConString");
//Insert the Data read from the Excel file to Database Table.
conString = this.Configuration.GetConnectionString("constr");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Assets";
//[OPTIONAL]: Map the Excel columns with that of the database table.
sqlBulkCopy.ColumnMappings.Add("ItemName", "ItemName");
sqlBulkCopy.ColumnMappings.Add("Price", "Price");
sqlBulkCopy.ColumnMappings.Add("Quntity", "Quntity");
con.Open();
sqlBulkCopy.WriteToServer(table);
con.Close();
}
}
ViewBag.SuccessMessage = "Excel Successfully Converted to Data Table";
}
}
else
ViewBag.ErrorMessage = "No Work Sheet available in Excel File";
}
}
}
catch (Exception ex)
{
ViewBag.ErrorMessage = ex.Message;
}
if(table.Rows.Count > 0)
{
return View("Display", table);
}
else
return View("Index");
}
public IActionResult Display(DataTable dt)
{
) ON [PRIMARY]
ALTER TABLE [dbo].[Assets] ADD CONSTRAINT [DF_Assets_IsAvailable] DEFAULT ((1))
FOR [IsAvailable]
GO
//
Step4:
Create a Controller ExcelToDataTableController.
Add required namespaecs like the below code.
Actions:
i)Index
ii) Convert
a) import from excel
b) bulkCopy to mssql datatabase table (make sure database table name, column names are same as excel columns)
iii) Display
iv) Export
//
using ExcelToDataTable.Models;
using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.IO;
using Microsoft.AspNetCore.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using OfficeOpenXml;
using System.Text;
using iText.Html2pdf;
using iText.IO.Source;
using iText.Kernel.Geom;
using iText.Kernel.Pdf;
namespace ExcelToDataTable.Controllers
{
public class ExcelToDataTableController : Controller
{
private IConfiguration Configuration;
public ExcelToDataTableController( IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
FileUploadModel model = new FileUploadModel();
return View(model);
}
[HttpPost]
public IActionResult Convert(FileUploadModel model)
{
DataTable table = new DataTable();
try
{
if (model.File != null)
{
//if you want to read data from a excel file use this
//using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
using (var stream = model.File.OpenReadStream())
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage();
package.Load(stream);
if (package.Workbook.Worksheets.Count > 0)
{
using (ExcelWorksheet workSheet =
package.Workbook.Worksheets.First())
{
int noOfCol = workSheet.Dimension.End.Column;
int noOfRow = workSheet.Dimension.End.Row;
int rowIndex = 2;
for (int c = 1; c <= noOfCol; c++)
{
table.Columns.Add(workSheet.Cells[rowIndex, c].Text.Replace(" ","").Trim());
}
rowIndex = 3;
for (int r = rowIndex; r <= noOfRow; r++)
{
DataRow dr = table.NewRow();
for (int c = 1; c <= noOfCol; c++)
{
dr[c - 1] = workSheet.Cells[r, c].Value;
}
if (!string.IsNullOrEmpty(dr[0].ToString()))
{
table.Rows.Add(dr);
}
}
//Read the connection string for the Excel file.
string conString =
this.Configuration.GetConnectionString("ExcelConString");
//Insert the Data read from the Excel file to Database Table.
conString = this.Configuration.GetConnectionString("constr");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Assets";
//[OPTIONAL]: Map the Excel columns with that of the database table.
sqlBulkCopy.ColumnMappings.Add("ItemName", "ItemName");
sqlBulkCopy.ColumnMappings.Add("Price", "Price");
sqlBulkCopy.ColumnMappings.Add("Quntity", "Quntity");
con.Open();
sqlBulkCopy.WriteToServer(table);
con.Close();
}
}
ViewBag.SuccessMessage = "Excel Successfully Converted to Data Table";
}
}
else
ViewBag.ErrorMessage = "No Work Sheet available in Excel File";
}
}
}
catch (Exception ex)
{
ViewBag.ErrorMessage = ex.Message;
}
if(table.Rows.Count > 0)
{
return View("Display", table);
}
else
return View("Index");
}
public IActionResult Display(DataTable dt)
{
return View(dt);
}
[HttpPost]
public FileResult Export(string GridHtml)
{
using (MemoryStream stream = new MemoryStream(Encoding.ASCII.GetBytes(GridHtml)))
}
[HttpPost]
public FileResult Export(string GridHtml)
{
using (MemoryStream stream = new MemoryStream(Encoding.ASCII.GetBytes(GridHtml)))
{ ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
PdfWriter writer = new PdfWriter(byteArrayOutputStream);
PdfDocument pdfDocument = new PdfDocument(writer);
pdfDocument.SetDefaultPageSize(PageSize.LEDGER);
HtmlConverter.ConvertToPdf(stream, pdfDocument);
pdfDocument.Close();
return File(byteArrayOutputStream.ToArray(), "application/pdf", "Grid.pdf");
}
}
}
}
//
Step5:
Create a view Index Under Views>ExcelToDatatable folder.
//
@model FileUploadModel
@{
ViewData["Title"] = "Convert Excel To DataTable";
}
<form asp-action="Convert"
asp-controller="ExcelToDataTable" method="post" enctype="multipart/form-data">
<div class="row mt-2">
@if (ViewBag.SuccessMessage != null)
{
<div class="alert alert-success">
@ViewBag.SuccessMessage
</div>
}
@if (ViewBag.ErrorMessage != null)
{
<div class="alert alert-danger">
@ViewBag.ErrorMessage
</div>
}
</div>
<div class="row mt-2">
<div class="col-6">
<label class="col-form-label">Select File</label>
<input asp-for="File" class="form-control" />
<span asp-validation-for="File" class="text-danger"></span>
</div>
</div>
<div class="row mt-2">
<div class="col-6">
<button type="submit" class="btn btn-success">Upload File</button>
</div>
</div>
</form>
Step 6:
Create a view Display Under Views>ExcelToDatatable folder.
//
@using System.Data
@model DataTable
@{
ViewData["Title"] = "Display";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h1>Display</h1>
<form method="post" asp-action="Export" asp-controller="ExcelToDataTable">
<input type="hidden" name="GridHtml" />
<input type="submit" id="btnSubmit" class="btn btn-info" value="Export To PDF" />
</form>
<br />
<div id="Grid">
<table class="table table-responsive table-bordered table-striped"
cellpadding="5" cellspacing="0"
style="border: 1px solid #ccc; font-family: Arial; font-size: 10pt;">
<tr>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">ItemName</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">Price</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">Quantity</th>
</tr>
@foreach (DataRow row in Model.Rows)
{
<tr>
<td style="border: 1px solid #ccc">@row["ItemName"]</td>
<td style="border: 1px solid #ccc">@row["Price"]</td>
<td style="border: 1px solid #ccc">@row["Quantity"]</td>
</tr>
}
</table>
</div>
<script type="text/javascript"
src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$("#btnSubmit").click(function () {
$("input[name='GridHtml']").val($("#Grid").html());
});
});
</script>
//
Step 7:
Run the application Ctrl+F5
You can use this example in any .Net project, not only MVC. Reading data from excel and converting it into DataTable will remain the same in all .Net projects. I hope you find this article helpful. If you have any doubts, you can ask them in the comments.
PdfWriter writer = new PdfWriter(byteArrayOutputStream);
PdfDocument pdfDocument = new PdfDocument(writer);
pdfDocument.SetDefaultPageSize(PageSize.LEDGER);
HtmlConverter.ConvertToPdf(stream, pdfDocument);
pdfDocument.Close();
return File(byteArrayOutputStream.ToArray(), "application/pdf", "Grid.pdf");
}
}
}
}
//
Step5:
Create a view Index Under Views>ExcelToDatatable folder.
//
@model FileUploadModel
@{
ViewData["Title"] = "Convert Excel To DataTable";
}
<form asp-action="Convert"
asp-controller="ExcelToDataTable" method="post" enctype="multipart/form-data">
<div class="row mt-2">
@if (ViewBag.SuccessMessage != null)
{
<div class="alert alert-success">
@ViewBag.SuccessMessage
</div>
}
@if (ViewBag.ErrorMessage != null)
{
<div class="alert alert-danger">
@ViewBag.ErrorMessage
</div>
}
</div>
<div class="row mt-2">
<div class="col-6">
<label class="col-form-label">Select File</label>
<input asp-for="File" class="form-control" />
<span asp-validation-for="File" class="text-danger"></span>
</div>
</div>
<div class="row mt-2">
<div class="col-6">
<button type="submit" class="btn btn-success">Upload File</button>
</div>
</div>
</form>
Step 6:
Create a view Display Under Views>ExcelToDatatable folder.
//
@using System.Data
@model DataTable
@{
ViewData["Title"] = "Display";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h1>Display</h1>
<form method="post" asp-action="Export" asp-controller="ExcelToDataTable">
<input type="hidden" name="GridHtml" />
<input type="submit" id="btnSubmit" class="btn btn-info" value="Export To PDF" />
</form>
<br />
<div id="Grid">
<table class="table table-responsive table-bordered table-striped"
cellpadding="5" cellspacing="0"
style="border: 1px solid #ccc; font-family: Arial; font-size: 10pt;">
<tr>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">ItemName</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">Price</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">Quantity</th>
</tr>
@foreach (DataRow row in Model.Rows)
{
<tr>
<td style="border: 1px solid #ccc">@row["ItemName"]</td>
<td style="border: 1px solid #ccc">@row["Price"]</td>
<td style="border: 1px solid #ccc">@row["Quantity"]</td>
</tr>
}
</table>
</div>
<script type="text/javascript"
src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$("#btnSubmit").click(function () {
$("input[name='GridHtml']").val($("#Grid").html());
});
});
</script>
//
Step 7:
Run the application Ctrl+F5
You can use this example in any .Net project, not only MVC. Reading data from excel and converting it into DataTable will remain the same in all .Net projects. I hope you find this article helpful. If you have any doubts, you can ask them in the comments.
You can download the source code of this project from my GitHub Account.
