import excel data to sql server

Question

User821258875 posted

hi,

I'm trying to write a function to import data from an excel sheet to the SQL server .and the function doesn't give me an error when I try to debug . At the same time, it doesn't import the data to SQL server.

Here is what I have done so far :

protected void Page_Load(object sender, EventArgs e)
{

if (IsPostBack && FileUpload1.HasFile)
{
if (Path.GetExtension(FileUpload1.FileName).Equals(".xlsx"))
{
var excel = new ExcelPackage(FileUpload1.FileContent);
var dataTable = new DataTable();
var table = "FindingDetails";
string cs = ConfigurationManager.ConnectionStrings["AuditConnectionString"].ConnectionString;
using (var conn = new SqlConnection(cs))
{

var bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = table;
conn.Open();
ExcelWorksheet excelWorksheet = excel.Workbook.Worksheets[1];

for (int i = excelWorksheet.Dimension.Start.Row; i <= excelWorksheet.Dimension.End.Row; i++)
{
for (int j = excelWorksheet.Dimension.Start.Column; j <= excelWorksheet.Dimension.Columns; j++)
{
object cellValue = excelWorksheet.Cells[i, j].Value;

dataTable.Columns.Add(cellValue.ToString(),typeof (string));
bulkCopy.ColumnMappings.Add(cellValue.ToString(), cellValue.ToString());
break;

}
}
bulkCopy.WriteToServer(dataTable);

}
}
}

}

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
ExcelPackage package = new ExcelPackage(FileUpload1.FileContent);
DataSet ds = new DataSet();

GridView1.DataSource = ds.Tables;
GridView1.DataBind();
}

}

}

I appreciate any help.i'm really stuck

Answer 1

User288213138 posted

Hi khloud_ali39,

khloud_ali39

for (int j = excelWorksheet.Dimension.Start.Column; j <= excelWorksheet.Dimension.Columns; j++)
{
object cellValue = excelWorksheet.Cells[i, j].Value;

dataTable.Columns.Add(cellValue.ToString(),typeof (string));
bulkCopy.ColumnMappings.Add(cellValue.ToString(), cellValue.ToString());
break;

You have break inside the for loop. if you debug your code, you will find the j++ statement of the for loop will never get executed, because break immediately exits the loop.

And if you want to loop all columns in a row you should set the j <= worksheet.Dimension.End.Column.

About how to use Epplus to Import Data From Excel To SQL Server you can refer to this link: https://www.mikesdotnetting.com/article/297/the-best-way-to-import-data-from-excel-to-sql-server-via-asp-net

But you can also try use the closedxml to get the data in Excel. 

<asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
            <hr />
            <asp:GridView ID="GridView1" runat="server">
            </asp:GridView>



protected void ImportExcel(object sender, EventArgs e)
        {
            using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
            {
                IXLWorksheet workSheet = workBook.Worksheet(1);
                DataTable dt = new DataTable();
                var table = "FindingDetails";
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    if (firstRow)
                    {
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        firstRow = false;
                    }
                    else
                    {
                        dt.Rows.Add();
                        int i = 0;
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            i++;
                        }
                    }

                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (var bulkCopy = new SqlBulkCopy(cs))
                {
                   
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.DestinationTableName = table;
                    bulkCopy.WriteToServer(dt);
                }
            }
        }

The result:

Best regards,

Sam