How to save gridview templated Item data into DB as well as generate a word document?

Question

I have few textbox which is outside of gridview and a templated item gridview, on button click which is outside of gridview. I want to save data into database table as well as generate a word document.

In word doc. data will show something like this

PO No: 123 Company Name: xyz Order No: 12345

Item Qty Description 1 2 test1 2 4 test2

<asp:Label ID="lblPONumber" runat="server" Text="PO Number"></asp:Label>
<asp:TextBox ID="txtPONumber" runat="server"></asp:TextBox>

<asp:Label ID="lblCompanyName" runat="server" Text="Company Name"></asp:Label>
<asp:TextBox ID="txtCompanyName" runat="server"></asp:TextBox>

<asp:Label ID="lblOrderNo" runat="server" Text="Order Number"></asp:Label>
<asp:TextBox ID="txtOrderNo" runat="server"></asp:TextBox>

<asp:GridView ID="gvOrders" runat="server" OnRowDataBound="gvOrder_RowDataBound">
 <Columns>
   <asp:TemplateField HeaderText="Line Item">
    <ItemTemplate>
      <asp:TextBox ID="txtItem" runat="server" CssClass="Gridtextboxes" Width="150px" Text='<% #   Eval("Item")%>'></asp:TextBox></ItemTemplate>
    </asp:TemplateField>
   <asp:TemplateField HeaderText="Return Quantity">
  <ItemTemplate>
    <asp:TextBox ID="txtQuantity" runat="server" CssClass="gridt" Width="150px" Text='<% # Eval("Quantity")%>'></asp:TextBox></ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="Product Description">
   <ItemTemplate>
    <asp:TextBox ID="txtProductDescription" runat="server" CssClass="Gridtextboxes" Width="150px" Text='<% # Eval("ProductDescription")%>'></asp:TextBox></ItemTemplate>
   </asp:TemplateField>
  <asp:TemplateField HeaderText="Action">
    <ItemTemplate>
       <center>
         <asp:ImageButton ID="btnDelete" runat="server" Text="Delete" ToolTip="Remove" ImageUrl="Images/close.png" Height="20px" Width="20px" /></center>
    </ItemTemplate>
  </asp:TemplateField>
 </Columns>

 <asp:Button ID="btnSave" runat="server" Text="Save" Width="110px" OnClick="btnSave_OnClick" />

Inserting data in DB is okay I have done with that and I have Created a word doc. template something like this.

PO Number: #PONumber#

Invoice Number: #InvoiceNumber#

Line Item : #LineItem#

Return Quantity : #ReturnQuantity#

Product Description : #ProductDescription#

Is there any way in which I can replay word doc #Data# with my aspx page data?

Answer 1

I found the solution from this link

using Word = Microsoft.Office.Interop.Word;
using System.Reflection;
using Microsoft.Office.Interop.Word;

 protected void btnSubmit_OnClick(object sender, EventArgs e)
    {
        try
        {
            object oMissing = System.Reflection.Missing.Value;
            object oEndOfDoc = "\\endofdoc";
            Word._Application oWord;
            Word._Document oDoc;
            oWord = new Word.Application();
            oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
            object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;

            Word.Paragraph oParag;
            oParag = oDoc.Content.Paragraphs.Add(ref oMissing);
            oParag.Range.Text = "Return Order";
            oParag.Range.Font.Bold = 2;
            oParag.Format.SpaceAfter = 30;
            oParag.Range.InsertParagraphAfter();

            Word.Paragraph oPara1;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara1 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara1.Range.Text = "Date: " + txtDate.Text;
            oPara1.Range.Font.Bold = 0;
            oPara1.Format.SpaceAfter = 24;
            oPara1.Range.InsertParagraphAfter();

            Word.Paragraph oPara2;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara2 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara2.Range.Text = "PO Number: " + txtPONumber.Text;
            oPara2.Range.Font.Bold = 0;
            oPara2.Format.SpaceAfter = 24;
            oPara2.Range.InsertParagraphAfter();

            Word.Paragraph oPara3;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara3.Range.Text = "Invoice Number: " + txtnvoiceNo.Text;
            oPara3.Range.Font.Bold = 0;
            oPara3.Format.SpaceAfter = 24;
            oPara3.Range.InsertParagraphAfter();

            Word.Paragraph oPara4;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara4 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara4.Range.Text = "Company Name or Dealer Name: " + txtCompanyName.Text;
            oPara4.Range.Font.Bold = 0;
            oPara4.Format.SpaceAfter = 24;
            oPara4.Range.InsertParagraphAfter();

            Word.Paragraph oPara5;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara5 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara5.Range.Text = "Order Number: " + txtOrderNo.Text;
            oPara5.Range.Font.Bold = 0;
            oPara5.Format.SpaceAfter = 24;
            oPara5.Range.InsertParagraphAfter();

            Word.Paragraph oPara6;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara6 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara6.Range.Text = "Contact Person to Return: " + txtReturnPerson.Text;
            oPara6.Range.Font.Bold = 0;
            oPara6.Format.SpaceAfter = 24;
            oPara6.Range.InsertParagraphAfter();

            Word.Paragraph oPara7;
            oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oPara7 = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara7.Range.Text = "Email: " + txtEmail.Text;
            oPara7.Range.Font.Bold = 0;
            oPara7.Format.SpaceAfter = 24;
            oPara7.Range.InsertParagraphAfter();

            //inserting table
            Word.Table oTable;
            int iTblRowCount = gvReturnOrders.Rows.Count + 1;
            Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            oTable = oDoc.Tables.Add(wrdRng, iTblRowCount, 3, ref oMissing, ref oMissing);
            oTable.Range.ParagraphFormat.SpaceAfter = 6;

            oTable.Rows[1].Cells[1].Range.Text = "Line Item";
            oTable.Rows[1].Cells[2].Range.Text = "Return Quantity";
            oTable.Rows[1].Cells[3].Range.Text = "Product Description";
            oTable.Rows[1].Cells[1].Range.Bold = 1;
            oTable.Rows[1].Cells[2].Range.Bold = 1;
            oTable.Rows[1].Cells[3].Range.Bold = 1;

            int iRowCount, iCount = 2;
            int rowIndex = 0;
            for (iRowCount = 1; iRowCount <= gvReturnOrders.Rows.Count; iRowCount++)
            {
                TextBox txtboxLineItems = (TextBox)gvReturnOrders.Rows[rowIndex].Cells[1].FindControl("txtLineItem");
                TextBox txtBoxQty = (TextBox)gvReturnOrders.Rows[rowIndex].Cells[2].FindControl("txtReturnQuantity");
                TextBox txtBoxproductDescription = (TextBox)gvReturnOrders.Rows[rowIndex].Cells[3].FindControl("txtProductDescription");
                oTable.Rows[iCount].Cells[1].Range.Text = txtboxLineItems.Text;
                oTable.Rows[iCount].Cells[2].Range.Text = txtBoxQty.Text;
                oTable.Rows[iCount].Cells[3].Range.Text = txtBoxproductDescription.Text;
                iCount++;
                rowIndex++;
            }
            //var myUniqueFileName = string.Format(@"{0}.doc", Guid.NewGuid()); // for Unique Id
            string fileName = "C:\\ReturnOrder.doc";
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
                oDoc.SaveAs2("C:\\ReturnOrder.doc");
                lblMsg.Visible = true;
                lblMsg.Text = "Successful";
            }
            else
            {
                oDoc.SaveAs2("C:\\ReturnOrder.doc");
                lblMsg.Visible = true;
                lblMsg.Text = "Successful";
            }
            oDoc.Close();
            oWord.Quit();

        }
        catch (Exception ex)
        {

        }
    }

Answer 2

There is no need to use any third party.

First you need to all the data from the grid and text box to the database.

protected void btnAddCopy_Click(object sender, EventArgs e)
    {
      for (int i = 0; i < gvTrip.Rows.Count; i++)
                {
                        string strTripDate = DateValidator.ConvertToMMDDYYYY(txtCopyTripDt.Text).ToString();
                        string strRouteSlcn = Convert.ToString(((Label)gvTrip.Rows[i].FindControl("lblgRouteSlcn2")).Text.Trim());
                        string strTripStartTime = Convert.ToString(((Label)gvTrip.Rows[i].FindControl("lblgTripStartTime")).Text.Trim());
                        string strTripEndTime = Convert.ToString(((Label)gvTrip.Rows[i].FindControl("lblgTripEndTime")).Text.Trim());
                        objMasterTransport.**insertTripDetail**(strTripDate, strRouteSlcn, strTripStartTime, strTripEndTime);
objMasterTransport.**YourFunctionForGenWordFile**(strTripDate, strRouteSlcn, strTripStartTime, strTripEndTime);
                    txtCopyTripDt.Text = null;
                    gvTrip.DataBind();
                    BindData();
       }
    }

insertTripDetail is my function some where.I have deleted some code.

Example for generating word file is(from the net)

protected void ExportToWord(object sender, EventArgs e)
{

    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +
                      " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
        "attachment;filename=DataTable.doc");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-word ";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    GridView1.RenderControl(hw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

you can either get the data from the database or store it in those variables (strTripDate, strRouteSlcn, strTripStartTime, strTripEndTime)

this is the code link for excel update http://seroter.wordpress.com/2009/12/23/populating-word-2007-templates-through-open-xml/

Answer 3

I swear by Aspose. They have fantastic tools for working with Office documents and PDF files. They are pretty expensive though but each time I've used the tools I've saved weeks of work so it ends up being cheaper in the end.