Export data from textbox to excel without using database

rachayita jaiswal

13y8.6k.NET

Hi,

I want to export data from textbox to excelsheet without using database or gridview.

I have created like this -

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook xlWB;

Microsoft.Office.Interop.Excel.Worksheet xlWS;

Microsoft.Office.Interop.Excel.Range xlRng;

string workbookPath = "D:\\New Microsoft Office Excel Worksheet.xls";

protected void btnexcel_Click(object sender, EventArgs e)

{

xlApp.Workbooks.Open(workbookPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

int colIndex = 0;

string addTNumbers = txtname.Text;

xlWS.Cells[1, 1] = addTNumbers;

//Save the file to where you specified

xlWB.Save();

xlApp.Visible = true;

}

but i am getting error on "xlWS.Cells[1, 1] = addTNumbers;" line that object reference not set to an instance.

Please tell me, how to get this.

17 Replies

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

Iftikar Hussain13y ago
Accepted answer
for ASP.NET use OleDbConnection for updating excel value

protected void btnSend_Click(object sender, EventArgs e)
{
string path = @"C:\Test\Book1.xlsx";
string xlsxConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=NO\"", path);
DataSet dataSet;
using (var con = new OleDbConnection(xlsxConnectionString))
{

con.Open();
OleDbCommand cmd = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=12345", con);
cmd.ExecuteNonQuery();
con.Close();
}
Response.Write("Done");
}

Regards,
Iftikar
Leon D6y ago
You can install Free Spire.XLS library via nuget, and then refer the below code to export data from textbox to excel.
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

sheet.Range["A3"].Text = textbox1.Text;

workbook.SaveToFile("Sample.xlsx",ExcelVersion.Version2013);
Ricki Hutagaol6y ago
Is there another way?

Can i Solve that without using OleDb?
Iftikar Hussain13y ago
this is right way. and I have used in my application

Regards,
Iftikar
rachayita jaiswal13y ago
Thanks,

In place of update query i have put insert query, its working but is there any other way that without using this i ll get?
Iftikar Hussain13y ago
it is not database what you are thinking, here you are treating the excel file as datasource and updating this using OLEDB. The reason I am asking to follow this approach as the earlier approach we should not use for web application as it use server component (Excel lib)

Regards,
Iftikar
rachayita jaiswal13y ago
Thanks for replying me but i don't want to use database at all. statically i want to create this.
Using database i know even in my projects i have done this also.
rachayita jaiswal13y ago
I have checked , it's not working..
Is my code correct??
Iftikar Hussain13y ago
for me it is working, can you create a new excel file and check

Regards,
Iftikar
rachayita jaiswal13y ago
i have added 12 only, i am trying a lot but same issue.
Iftikar Hussain13y ago
which version of Excel interop you have added as reference. It should be 12.0

Regards,
Iftikar
rachayita jaiswal13y ago
this is my design page -

onclick="btnexcel_Click" />

and this is my code behind -

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWB;
Microsoft.Office.Interop.Excel.Worksheet xlWS;
Microsoft.Office.Interop.Excel.Range xlRng;

string workbookPath = "D:\\New Microsoft Office Excel Worksheet.xlsx";

protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnexcel_Click(object sender, EventArgs e)
{
xlApp.Workbooks.Open(workbookPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
int colIndex = 0;
string addTNumbers = txtname.Text;
xlWS = xlApp.Worksheets[1] as Worksheet;
xlWS.Activate();
xlWS.Cells[1, 1] = addTNumbers;

//Save the file to where you specified

xlWB.Save();
xlApp.Visible = true;

}

i want my textbox's text should export in excelsheet.
Iftikar Hussain13y ago
can you share the complete code?

Regards,
Iftikar
rachayita jaiswal13y ago
Hi, i have put as worksheet but still i am getting same issue.
Iftikar Hussain13y ago
try like this

xlWS = xlApp.Worksheets[1] as Worksheet;

Regards,
Iftikar
rachayita jaiswal13y ago
HI,

now i am getting error like -

Error1Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'.
An explicit conversion exists (are you missing a cast?)

on the line - xlWS = xlApp.Worksheets[1];
Iftikar Hussain13y ago
Hi,
Try like this

protected void btnexcel_Click(object sender, EventArgs e)
{
xlApp.Workbooks.Open(workbookPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

int colIndex = 0;
string addTNumbers = txtname.Text;

  xlWS = xlApp.Worksheets[1];
xlWS.Activate();
  xlWS.Cells[1, 1] = addTNumbers;

//Save the file to where you specified
  xlApp.Save();
  xlApp.Workbooks.Close();
xlApp.Application.Quit();
}

Regards,
Iftikar