Inserting value into excel via asp.net app

Question

I have a spreadsheet that need to accept data from a asp.net app.

I have wrote some c# that allows me to read data from another spread sheet but now i want to input some data from text boxes.

        protected void Button2_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection();
        string path = Server.MapPath("LOG_TEST.xlsx");
        String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;IMEX=1;'";
        conn.ConnectionString = connString;
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$] ([NAME], [MOBILE], [EMAIL]) VALUES('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')",conn);
        OleDbDataReader rd = cmd.ExecuteReader();
        conn.Close();
    }

Just imple insert 3 values into the spread sheet.

error im getting is below.

I believe the issue is around

OleDbDataReader rd = cmd.ExecuteReader();

but not sure what to replace it with.

First search on google: stackoverflow.com/questions/34922325/… — kooshy
– kooshy, Commented Dec 7, 2018 at 15:56
also looked at the permissions in the spreadsheet and all looks fine. — Sam Parky
– Sam Parky, Commented Dec 7, 2018 at 16:13
Still getting the same error message when using ExecuteNonQuery — Sam Parky
– Sam Parky, Commented Dec 7, 2018 at 16:19

kooshy · Accepted Answer · 2018-12-07 16:56:30Z

1

I recommend simplifying it just as I did it below: Use a spreadsheet from your Local Drive, C:, update the rest of the directory in the connString. Use string values in your INSERT QUERY, you can update it later. We just want to be sure that it works first! I didn't test this code btw, but I think it should work.

protected void Button2_Click(object sender, EventArgs e)
{
    string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "C:\\SPECIFY HERE\\LOG_TEST.xlsx" + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";

 string query ="INSERT INTO [Sheet1$] (NAME, Mobile, Emails) VALUES ('Bob', '1', 'Whatever@mail')";

 OleDbConnection con = new OleDbConnection(connString);
 OleDbCommand cmd = new OleDbCommand(query, con);

 con.Open();
 cmd.ExecuteNonQuery();


}

I assume you can write a function to open your excel file something like

//Make sure you add these two references. 
using Microsoft.Office.Interop
using Excel = Microsoft.Office.Interop.Excel

//Call this before your insert
static void FileOpen() {
    string path = "Path.xlsx"
    var excel = new Excel.Application
    excel.Visible=True

    Excel.Workbooks books = excel.Workbooks;
    Excel.Workbook sheet = books.Open(path);
}

edited Dec 7, 2018 at 16:56

answered Dec 7, 2018 at 16:32

kooshy

1341 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Parky Over a year ago

Only issue i can see is that the spreadsheet has to be open in order for it to insert the row.

UsernameNotAccepted · Accepted Answer · 2018-12-07 16:54:54Z

I have been working with Excel files for several years now, and the easiest and most reliable way for me has been using NPOI for .NET. It's available as a free NuGet package https://www.nuget.org/packages/NPOI/

Sample:

XSSFWorkbook wb1 = null;
using (var file = new FileStream("D:\\banding.xlsx", FileMode.Open, 
FileAccess.ReadWrite)) 
{
  wb1 = new XSSFWorkbook(file);
}

wb1.GetSheetAt(0).GetRow(0).GetCell(0).SetCellValue("Sample");

using (var file2 = new FileStream("D:\\banding2.xlsx", FileMode.Create, 
       FileAccess.ReadWrite)) {
         wb1.Write(file2);
         file2.Close();
      }

Taken from Muthukrishnan Ramasamy's answer on https://social.msdn.microsoft.com/Forums/vstudio/en-US/d1c5e191-135b-45c0-9f88-cc3e02849257/npoi-how-to-write-to-an-xlsx-excel-file?forum=csharpgeneral

Collectives™ on Stack Overflow

Inserting value into excel via asp.net app

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related