How to update Excel Data in SQL without saving Excel File

Question

User1912965948 posted

Hi all,

I want to save Excel data in SQL server without saving Excel file, I don't want to save excel file just update data in <g class="gr_ gr_115 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="115" data-gr-id="115">sql</g>.

I've written some code please correct this.

Code.

protected void btnSumit_Click(object sender, EventArgs e)
    {
        int empNo;
        string empName;
        string date;
        string clockIn;
        string clockOut;

        string path = Path.GetFileName(FileUpload1.FileName);
        path = path.Replace(" ", "");
        OleDbConnection oledbCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 8.0'; Persist Security Info=False;");
        oledbCon.Open();
        OleDbCommand oledbCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbCon);
        OleDbDataReader oledbDr = oledbCmd.ExecuteReader();
        while (oledbDr.Read()) {
            empNo = Convert.ToInt32(oledbDr[1].ToString());
            empName = oledbDr[0].ToString();
            date = oledbDr[2].ToString();
            clockIn = oledbDr[3].ToString();
            clockOut = oledbDr[4].ToString();
            SaveData(empNo, empName, date, clockIn, clockOut);
        }

        lblMsg.Text = "Data has been saved successfully !!";
    }

    private void SaveData(int empNo, string empName, string date, string clockIn, string clockOut) { 
    string query = "INSERT INTO Attendance(Emp_No, Emp_Name, Att_Date, Clock_In, Clock_Out) VALUES (" + empNo + ", '" + empName + "', '" + date + "', '" + clockIn + "','" + clockOut +"')";
    string cs = ConfigurationManager.ConnectionStrings["My_DB"].ConnectionString;
    SqlConnection sqlCon = new SqlConnection(cs);
    sqlCon.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = query;
    cmd.Connection = sqlCon;
    cmd.ExecuteNonQuery();
        
    }

I'll be very thankful if someone solves this problem.

Regards

Answer 1

User753101303 posted

Hi,

Not sure if you but I answered a similar question previously. The OleDb provider can only load from a file. If you don't want to save the file you'll have to use another library that allows loading an Excel sheet from a stream (such as Aspose.Cells - not free - or https://docs.microsoft.com/en-us/office/open-xml/how-to-open-a-spreadsheet-document-from-a-stream which is at a lower level or some other library).

Even deleting this file once done is not enough ? This is a security issue ? As often knowing why you can't save this file could help to raise other suggestions. For example rather than using the original name you could use your own naming scheme and you could save this file outside of the web app. The weird thing is that it seems you work with just a file name rather than a full path? Could it even be some kind of issue such as not even uplaoding this file prior processing it ???

Answer 2

User-271186128 posted

Hi fungus.00,

fungus.00

How to update Excel Data in SQL without saving Excel File

You could try to use the EPPlus package to import data from the uploaded Excel files to SQL Server. By using this method, you could work with streams, so the uploaded file doesn't need to be saved. 

Please refer to the following steps:

1.Install the EPPlus using the Nuget Package Manager or by typing the following command:

install-package epplus

2.Defined in a class called ExcelPackageExtenstions

using OfficeOpenXml;
using System.Data;
using System.Linq;

public static class ExcelPackageExtensions
{
    public static DataTable ToDataTable(this ExcelPackage package)
    {
        ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
        DataTable table = new DataTable();
        foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
        {
            table.Columns.Add(firstRowCell.Text);
        }
        for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
        {
            var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
            var newRow = table.NewRow();
            foreach (var cell in row)
            {
                newRow[cell.Start.Column - 1] = cell.Text;
            }
            table.Rows.Add(newRow);
        }
        return table;
    }
}

3. using the following code to upload data:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="SqlBulkInsertWithEPPlus.aspx.cs" Inherits="WebFormsTest.SqlBulkInsertWithEPPlus" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <asp:FileUpload ID="Upload" runat="server" />
    <asp:Button ID="Button1" runat="server" Text="Upload" />
</asp:Content>

Code behind:

using OfficeOpenXml;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace WebFormsTest
{
    public partial class SqlBulkInsertWithEPPlus : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack && Upload.HasFile)
            {
                if(Path.GetExtension(Upload.FileName).Equals(".xlsx"))
                {
                    var excel = new ExcelPackage(Upload.FileContent);
                    var dt = excel.ToDataTable();
                    var table = "Contacts";
                    using (var conn = new SqlConnection("Server=.;Database=Test;Integrated Security=SSPI"))
                    {
                        var bulkCopy = new SqlBulkCopy(conn);
                        bulkCopy.DestinationTableName = table;
                        conn.Open();
                        var schema = conn.GetSchema("Columns", new[]{null, null, table, null} );
                        foreach (DataColumn sourceColumn in dt.Columns)
                        {
                            foreach (DataRow row in schema.Rows)
                            {
                                if (string.Equals(sourceColumn.ColumnName, (string)row["COLUMN_NAME"], StringComparison.OrdinalIgnoreCase))
                                {
                                    bulkCopy.ColumnMappings.Add(sourceColumn.ColumnName, (string)row["COLUMN_NAME"]);
                                    break;
                                }
                            }
                        }
                        bulkCopy.WriteToServer(dt);
                    }
                }
            }
        }
    }
}

More detail steps, please check this article.

Best regards,
Dillion

Answer 3

User1912965948 posted

@Zhi Lv - MSF

Thank you for replying.

It gives me an error

Additional information: The given value of type String from the data source cannot be converted to type int of the specified target column

Input string was not in a correct format

How to resolve this issue.