ASP.NET - how to export textbox in excel

Asked By MuraliKrishna K on 19-Jan-12 05:24 AM

Hi,

How to export text box values in excel ? I need to export text box values in "excel".(when we click export to excel button)

Please help me!

Riley K replied to MuraliKrishna K on 19-Jan-12 05:24 AM

In references, right click, add reference

Choose the COM tab

Choose Microsoft Excel 12.0 Object Library

using Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application myExcelApp;

Excel.Workbooks myExcelWorkbooks;

Excel.Workbook myExcelWorkbook;

object misValue = System.Reflection.Missing.Value;

myExcelApp = new Excel.ApplicationClass();

myExcelApp.Visible = true;

myExcelWorkbooks = myExcelApp.Workbooks;

String fileName = "C:\\book1.xls"; \\ set this to your file you want

myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;

String cellFormulaAsString = myExcelWorksheet.get_Range("A2", misValue).Formula.ToString(); \\ this puts the formula in Cell A2 or text depending whats in it in the string.

myExcelWorksheetToChange.get_Range("C22", misValue).Formula = "New Value" \\ this changes the cell value in C2 to "New Value"

Regards

Jitendra Faye replied to MuraliKrishna K on 19-Jan-12 05:24 AM

Use this code-

The code is as bellow

Private Sub Excelcmd2_Click() '' Exports Txtbox values to excel Sheet

Dim oXL As Object
Dim oBook As Object
Dim oSheet As Object
Dim i As Integer, lvlCount As Long
Dim lvlName As String
Dim oLevels As Levels

Dim oLvl As Level

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Sheets(1)

Set oLevels = ActiveDesignFile.Levels
lvlCount = oLevels.Count

For i = 1 To lvlCount

Set oLvl = oLevels.Item(i)
lvlName = oLvl.Name
oSheet.cells(i, 1).Value = lvlName

oSheet.cells(i, 1).Value = BearingFrm1.txtbearing.Value '' text box
oSheet.cells(i, 2).Value = BearingFrm1.txtrec.Value '' text box

Next i

End Sub

MuraliKrishna K replied to Jitendra Faye on 19-Jan-12 05:28 AM

i want c# coding.

kalpana aparnathi replied to MuraliKrishna K on 19-Jan-12 05:34 AM

Try this:

Excel.Application xlApp = new Excel.Application();

Excel.Workbook xlWB;

Excel.Worksheet xlWS;

Excel.Range xlRng;

string workbookPath = "C:\\Documents and Settings\\adavis\\My Documents\\Area51\\T-Numbers Data";

private void addTNumberBtn_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 = addTNumberTextBox.Text;

xlWS.Cells[1, 1] = addTNumbers;

//Save the file to where you specified

xlWB.Save();

xlApp.Visible = true;

}

kalpana aparnathi replied to MuraliKrishna K on 19-Jan-12 05:35 AM

hi,
http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

D Company replied to MuraliKrishna K on 19-Jan-12 05:38 AM

Hello Murali,
here is sample example to do so,

try this
//open your excel in .net
//read the particulare cell where you want enter textbox value
String fileName = "C:\\sample.xls";

create object of this to open your Excel file MyExcelWorkbook

//assign the value like this

Sheet1.Range("A1").Value = TextBox1.Value

hope this will help you!!

Regards
D

MuraliKrishna K replied to Jitendra Faye on 19-Jan-12 05:44 AM

Code:(when i click Export button i need to save text box value in excel sheet. ) In below code is working properly, But i need to save textbox value. (text box name is "txtBillable")

protected void btnExportToExcel_Click(object sender, EventArgs e)
      {

        gvHours.AllowPaging = false;
        gvHours.AllowSorting = false;
        gvHours.DataSource=Cache["dataset"];
        gvHours.DataBind();
        ChangeControlsToValue(gvHours);
        Response.ClearContent();
        string from = dtFrom.SelectedDate.ToShortDateString();
        string to = dtTo.SelectedDate.ToShortDateString();
        Response.AddHeader("content-disposition", "attachment; filename=" + from + "_to_" + to + ".xls");
        Response.ContentType = "application/excel";
        StringWriter sWriter = new StringWriter();
        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
        HtmlForm hForm = new HtmlForm();
        gvHours.Parent.Controls.Add(hForm);
        hForm.Attributes["runat"] = "server";
        hForm.Controls.Add(gvHours);
        hForm.RenderControl(hTextWriter);
        Response.Write(sWriter.ToString());
        gvHours.AllowPaging = true;
        gvHours.AllowSorting = true;
        gvHours.DataSource = Cache["dataset"];
        gvHours.DataBind();
        Response.End();

      }

      private void ChangeControlsToValue(Control gridView)
      {
        Literal literal = new Literal();

        for (int i = 0; i < gridView.Controls.Count; i++)
        {
          if (gridView.Controls[i].GetType() == typeof(LinkButton))
          {
            literal.Text = (gridView.Controls[i] as LinkButton).Text;
            gridView.Controls.Remove(gridView.Controls[i]);
            gridView.Controls.AddAt(i, literal);
          }
          else if (gridView.Controls[i].GetType() == typeof(DropDownList))
          {
            literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;
            gridView.Controls.Remove(gridView.Controls[i]);
            gridView.Controls.AddAt(i, literal);
          }
          else if (gridView.Controls[i].GetType() == typeof(CheckBox))
          {
            literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
            gridView.Controls.Remove(gridView.Controls[i]);
            gridView.Controls.AddAt(i, literal);
          }
          if (gridView.Controls[i].HasControls())
          {

            ChangeControlsToValue(gridView.Controls[i]);

          }

        }

      }

      protected void gvHours_RowDataBound(object sender, GridViewRowEventArgs e)
      {
        btnExportToExcel.Visible = true;
      }

      protected void gvHours_SelectedIndexChanged(object sender, EventArgs e)
      {

      }

    }

dipa ahuja replied to MuraliKrishna K on 19-Jan-12 06:05 AM

protected void Button3_Click(object sender, EventArgs e)

{

DataTable dt = Class2.GetData();

ExportDataTable(dt);

}

public void ExportDataTable(DataTable dt)

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=e1.xls");

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string sTab = "";

foreach (DataColumn dc in dt.Columns)

{

HttpContext.Current.Response.Write(sTab + dc.ColumnName);

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

int i;

foreach (DataRow dr in dt.Rows)

{

sTab = "";

for (i = 0; i < dt.Columns.Count; i++)

{

HttpContext.Current.Response.Write(sTab + dr[i].ToString());

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

}

HttpContext.Current.Response.End();

}