I already have added the EPPlus library to my solution. I just can't seem to figure out how to get my excel data it into a datatable that will allow my bulkcopy to work. The below code doesn't work. Can anyone help me massage this into place? Thank you in advance for your assistance. I have edited this after comments from 'mason' below.
try
{
//// open file
var excel = Request.Files[0];
var file = Path.Combine(Server.MapPath("~/Uploads/"), excel.FileName);
var sqlConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ToString();
// Get the datatable from procedure on Utility.cs page
var datapush = Utility.ImportToDataTable(file, "Sheet1");
// open connection to sql and use bulk copy to write excelData to my table
using (var destinationConnection = new SqlConnection(sqlConnectionString))
{
destinationConnection.Open();
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "MYTABLE";
bulkCopy.ColumnMappings.Add("CODE", "code");
bulkCopy.ColumnMappings.Add("TITLE", "title");
bulkCopy.ColumnMappings.Add("LAST_NAME", "last_name");
bulkCopy.ColumnMappings.Add("FIRST_NAME", "first_name");
bulkCopy.WriteToServer(datapush);
}
}
}
and here is the code on the Utility.cs page based on Mason's suggested link:
public class Utility
{
public static DataTable ImportToDataTable(string FilePath, string SheetName)
{
DataTable dt = new DataTable();
FileInfo fi = new FileInfo(FilePath);
// Check if the file exists
if (!fi.Exists)
throw new Exception("File " + FilePath + " Does Not Exists");
using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[SheetName];
// Fetch the WorkSheet size
ExcelCellAddress startCell = worksheet.Dimension.Start;
ExcelCellAddress endCell = worksheet.Dimension.End;
// create all the needed DataColumn
for (int col = startCell.Column; col <= endCell.Column; col++)
dt.Columns.Add(col.ToString());
// place all the data into DataTable
for (int row = startCell.Row; row <= endCell.Row; row++)
{
DataRow dr = dt.NewRow();
int x = 0;
for (int col = startCell.Column; col <= endCell.Column; col++)
{
dr[x++] = worksheet.Cells[row, col].Value;
}
dt.Rows.Add(dr);
}
}
return dt;
}
}
Currently when I run the code and F11 the bug is on the Utility.cs page. right after "// get the first worksheet in the workbook"
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[SheetName];
returns null and the next line of code
ExcelCellAddress startCell = worksheet.Dimension.Start;
stops everything and kicks the following error "{"Object reference not set to an instance of an object."}"