How to import Data into Excel sheet i try my self but ..

Mohammad Muzammil

7y943Visual Basic .NET

i'm trying to import Data into Excel sheet from Sql server 2014 using C # but it does not Work.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.IO;

using System.Data.OleDb;

namespace TelerikWinFormsApp2

{

public partial class RadForm1 : Telerik.WinControls.UI.RadForm

{

public RadForm1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

string Excelfile = _uploaddata.Text;

int ID;

String PerName;

String PerLast;

String PerDesignation;

int PerCnic;

int PerPhoneNo;

string sWorkbook = "[Sheet1$]";

// string path = Path.GetFileName(FileUpload1.FileName);

Excelfile = Excelfile.Replace(" ", "");

// FileUpload1.SaveAs(Server.MapPath("~/ExcelFile/") + path);

// String ExcelPath = Server.MapPath("~/ExcelFile/") + path;

OleDbConnection mycon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; " +

"Data Source = " + Excelfile + "; Extended Properties=Excel 8.0; Persist Security Info = False");

mycon.Open();

OleDbCommand cmd = new OleDbCommand("select * from" + sWorkbook, mycon);

// OleDbCommand cmd = new OleDbCommand(@"Select * from [" + Excelfile + "]", mycon);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

// Response.Write("
"+dr[0].ToString());

ID = Convert.ToInt32(dr[0].ToString());

PerName = dr[1].ToString();

PerLast = dr[2].ToString();

PerDesignation = dr[3].ToString();

PerCnic = Convert.ToInt32(dr[4].ToString());

PerPhoneNo =Convert.ToInt32(dr[5].ToString());

savedata(ID, PerName, PerLast, PerDesignation, PerCnic, PerPhoneNo);

}

// Label3.Text = "Data Has Been Saved Successfully";

}

private void button2_Click(object sender, EventArgs e)

{

OpenFileDialog OFD = new OpenFileDialog();

if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)

{

this._uploaddata.Text = OFD.FileName;

}

private void savedata(int ID, String PerName, String PerLast, String PerDesignation, int PerCnic, int PerPhoneNo )

{

String query = "insert into Excel sheet(ID,PerName,PerLast,PerDesignation,PerCnic,PerPhoneNo) values(" + ID + ",'"

+ PerName + "','" + PerLast + "','" + PerDesignation + "','"+PerCnic +"','"+ PerPhoneNo +"')";

String mycon = "Data Source=PC105\\SQLSVR2014;Initial Catalog=Excel_sheet; User ID=sa;Password=sa@123";

SqlConnection con = new SqlConnection(mycon);

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = query;

cmd.Connection = con;

cmd.ExecuteNonQuery();

}

Please Help me with your valuable thoughts..

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Can you check with the below link:
https://www.youtube.com/watch?v=CfNMPDJVjPI
Munish Kumar7y ago
Hope you will find a way to get it sorted here:

https://www.c-sharpcorner.com/UploadFile/83fe73/solutions-to-export-data-from-database-to-excel-in-C-Sharp/
Piyush Pansuriya7y ago
Hello,
Your requirement is export to excel from sql server.
Your code shows in error access database connection.
So please share your latest code with actual requirement.
Thanks.
Mohammad Muzammil7y ago
Respected Sir it's Windows Forms Applicatopn not Web. So how can i used it?
@suraj kumar
Leon D7y ago
Hi,

Use Free Spire.xls library you can easily export data to excel from database via data table using code as follows:
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.InsertDataTable(dataTable, true, 1, 1);

workbook.SaveToFile("DataTableToExcel.xlsx",ExcelVersion.Version2013);
You can check this article for more information:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
Suraj Kumar7y ago
Have you added asppostback trigger and render like this
<Triggers>
        <asp:PostBackTrigger ControlID="button1" />
Triggers>
Try also adding
public override void VerifyRenderingInServerForm(Control control)
   {
       //required to avoid the runtime error "
       //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
   }
Request you not to post your full connectionstring information like server name, user id and password publicly community do not mind from where data is coming and where to insert.

Hope this will help you