2

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."}"

TimK
  • 115
  • 2
  • 2
  • 12
  • Do you have a table called `MYTABLE` in the database, and does it contain a column called `CODE`? – mason Feb 23 '16 at 21:41
  • Yes there is a MYTABLE table. CODE is the excel column and code is the table column. – TimK Feb 23 '16 at 22:04
  • Sorry, I need to comment out `currentWorksheet.Fill(excelData);` to get everything to run. So the error is because the 'excelData' is empty. I need to figure out how to get the currentWorksheet data to excelData. – TimK Feb 23 '16 at 22:12
  • [This question](http://stackoverflow.com/questions/13396604/excel-to-datatable-using-epplus-excel-locked-for-editing) may prove useful. – mason Feb 23 '16 at 22:14
  • Sorry mason, I meant I used this source for the .cs bit. https://logicalcafe.wordpress.com/2012/03/31/reading-an-excel-2007-xlsx-into-a-datatable-without-installing-offic/ I'm going to try the code on the page you suggested now and see if that will get me my table to load into the bulkcopy. – TimK Feb 24 '16 at 18:05
  • Eureka! mason, I simply replaced my procedure with the `public static DataTable getDataTableFromExcel(string path)` bit from the link you sent and it works. – TimK Feb 24 '16 at 18:21
  • I have a similar requirement, reading excel data with EPPlus and uploading it with SQL Bulk Copy. Is this the working code? – Cenk Jan 18 '23 at 06:09

0 Answers0