locked
import excel data to sql server RRS feed

  • 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

    Sunday, November 17, 2019 11:34 AM

All replies

  • 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

    Monday, November 18, 2019 11:00 AM