how to export jqgrid to word using jquery in asp.net?

Question

1.00/5 (1 vote)

See more:

how to export jqgrid to word using jquery in asp.net?

Note-Word file is downloaded but word file content is corrupted

Thanks in advance pls help me....

C#

using System;
using System.Web;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Web;
using System.Web.Script.Serialization;
using System.IO;
using OfficeOpenXml;
using System.IO;
  
      private Collection<user> GetUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords, string _search, string searchField, string searchOper, string searchString)
    {

        Collection<user> users = new Collection<user>();
        string connectionString = "Server=localhost;Port=3306;Database=projecttt;UID=root;Pwd=techsoft;pooling=false;";


        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                DataSet ds = new DataSet("New_DataSet");
                DataTable dt = new DataTable("New_DataTable");
                ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
                dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
                int numRows = Convert.ToInt32(numberOfRows) * (Convert.ToInt32(pageIndex));
                int excluderows = Convert.ToInt32(numberOfRows) * ((Convert.ToInt32(pageIndex) - 1));
                command.Connection = connection;
                if (_search == "false")
                {
                    command.CommandText = "SELECT depname, username, dialnumber, dialdate, dialtime, duration, price, units FROM processeddata_table";
                }
                if (_search == "true")
                {
                    command.CommandText = "SELECT depname, username, dialnumber, dialdate, dialtime, duration, price, units FROM processeddata_table where " + searchField + " " + searchOper + " '" + searchString + "' ";


                }

                command.CommandType = CommandType.Text;
                connection.Open();

                MySqlDataAdapter adptr = new MySqlDataAdapter();

                adptr.SelectCommand = command;
                adptr.Fill(dt);
                ds.Tables.Add(dt);
                //string excelSheetName = "mydoc";
                string fileName = "okkk";
                string DateFolder = DateTime.Now.ToString();
                DateFolder = DateFolder.Replace('/', '-');
                DateFolder = DateFolder.Replace(':', '-');
                string currentDirectorypath = Environment.CurrentDirectory;
                string finalFileNameWithPath = string.Empty;
                string fileName1 = string.Format("{0}.docx", fileName);
                fileName = string.Format("{0}_{1}", fileName, DateFolder);
                string pathString = @"c:\" + fileName + "";
                System.IO.Directory.CreateDirectory(pathString);
                finalFileNameWithPath = string.Format("{0}\\{1}.docx", pathString, fileName1);

                if (File.Exists(finalFileNameWithPath))
                    File.Delete(finalFileNameWithPath);
                var newFile = new FileInfo(finalFileNameWithPath);
                //using (var package = new  (newFile))
                using (var package = new ExcelPackage(newFile))
                {
                    //ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelSheetName);
                    //worksheet.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light17);
                    package.Save();
                }

                String FileName = fileName1;
                String FilePath = finalFileNameWithPath; //Replace this
                System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
                response.ClearContent();
                response.Clear();
                response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
                HttpContext.Current.Response.ContentEncoding = System.Text.UnicodeEncoding.UTF8;
                response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ";");
                response.TransmitFile(FilePath);
                response.Flush();
                response.End();
                totalRecords = 0;
                connection.Close();

            }

            return users;
        }

    }

}

Posted 12-Jun-13 19:17pm

Roopa 10064853

Updated 12-Jun-13 19:42pm

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 13-Jun-13 1:28am

Why jQuery? This is C#...
—SA

Roopa 10064853 13-Jun-13 1:39am

clientside is jquery so only

Sergey Alexandrovich Kryukov 13-Jun-13 1:44am

So what? The word content should better be generated on server side and only delivered to client side. (Do you really need to mess up with Word format? I would not recommend it. This is proprietary format (even though Open XML is a public specification), not a part of W3 standards. However I understand that some businesses need it.) It depends on what you want to do with it. Do you understand that you cannot render it on a page? You can only cause downloading dialog on client side.
—SA

Roopa 10064853 13-Jun-13 2:25am

ok i understood, below code exports jqgrid to excel and working fine i need to know how to export jqgrid to word?

adptr.SelectCommand = command;
adptr.Fill(dt);
ds.Tables.Add(dt);
string excelSheetName = "myxl";
string fileName = "okkk";
string DateFolder = DateTime.Now.ToString();
DateFolder = DateFolder.Replace('/', '-');
DateFolder = DateFolder.Replace(':', '-');
string currentDirectorypath = Environment.CurrentDirectory;
string finalFileNameWithPath = string.Empty;
string fileName1 = string.Format("{0}.xlsx", fileName);
fileName = string.Format("{0}_{1}", fileName, DateFolder);
string pathString = @"c:\" + fileName + "";
System.IO.Directory.CreateDirectory(pathString);
finalFileNameWithPath = string.Format("{0}\\{1}.xlsx", pathString, fileName1);

if (File.Exists(finalFileNameWithPath))
File.Delete(finalFileNameWithPath);
var newFile = new FileInfo(finalFileNameWithPath);
using (var package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add (excelSheetName);
worksheet.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light17);
package.Save();
}

String FileName = fileName1;
String FilePath = finalFileNameWithPath; //Replace this
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ";");
response.TransmitFile(FilePath);
response.Flush();
response.End();
totalRecords = 0;
connection.Close();

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Roopa 10064853 · Accepted Answer · 2013-07-30T19:56:00

private void microsoftWordToolStripMenuItem_Click(object sender, EventArgs e)
        {
        
            try
            {
                string date1 = dateTimePicker1.Value.ToString("yyyy-MM-dd");
                string date2 = dateTimePicker2.Value.ToString("yyyy-MM-dd");
                string bg8 = date1;
                string bg9 = date2;

                Object oMissing = System.Reflection.Missing.Value;

                OBJECTS OF FALSE AND TRUE
                Object oTrue = true;
                Object oFalse = false;

                CREATING OBJECTS OF WORD AND DOCUMENT
                Microsoft.Office.Interop.Word.Application oWord = new Microsoft.Office.Interop.Word.Application();
                Microsoft.Office.Interop.Word.Document oWordDoc = new Microsoft.Office.Interop.Word.Document();

                MAKING THE APPLICATION VISIBLE
                oWord.Visible = true;

                ADDING A NEW DOCUMENT TO THE APPLICATION
                oWordDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);

                LoadDataGridView();
              
                Object start1 = 0;
                Object end1 = 1;
                Microsoft.Office.Interop.Word.Range rng1 = oWordDoc.Range(ref start1, ref end1);
                rng1.InsertAfter(" Date Range: " + bg8 + " to " + bg9 + "");
                rng1.Font.Name = "Verdana";
                rng1.Font.Size = 12;
                rng1.InsertParagraphAfter();
                rng1.SetRange(rng1.End, rng1.End);
                rng1.Select();

                Object start2 = 0;
                Object end2 = 1;
                Microsoft.Office.Interop.Word.Range rng2 = oWordDoc.Range(ref start2, ref end2);
                rng2.InsertAfter("                                                                                         Report Type: Report");
                rng2.Font.Name = "Verdana";
                rng2.Font.Size = 12;
                rng2.InsertParagraphAfter();
                rng2.SetRange(rng2.End, rng2.End);
                rng2.Select();

                Object start3 = 0;
                Object end3 = 0;
                Microsoft.Office.Interop.Word.Range rng3 = oWordDoc.Range(ref start1, ref end1);
                rng3.InlineShapes.AddPicture(@"C://Users//Public//Pictures//Sample Pictures//11111111.jpg", ref oMissing, ref oMissing, ref oMissing);
                rng3.SetRange(rng3.End, rng3.End);
                rng3.Select();

                Object start = oWordDoc.Content.End - 1;
                Object end = oWordDoc.Content.End;
                Microsoft.Office.Interop.Word.Range rng = oWordDoc.Range(ref start, ref end);
                ADD TABLE
                Microsoft.Office.Interop.Word.Table tbl = oWordDoc.Tables.Add(rng, Dt.Rows.Count, Dt.Columns.Count, ref oMissing, ref oMissing);
                END ADD TABLE
                Object defaultTableBehavior = Type.Missing;
                Object autoFitBehavior = Type.Missing;
                object missing = System.Type.Missing;

                Microsoft.Office.Interop.Word.Row newRow = oWordDoc.Tables[1].Rows.Add(ref missing);
                newRow.Range.Font.Bold = 0;
                newRow.Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft;
                tbl.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                tbl.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                tbl.Rows.Borders.InsideColor = Microsoft.Office.Interop.Word.WdColor.wdColorBlueGray;
                tbl.Rows.Shading.ForegroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorDarkBlue;
                tbl.Rows.Shading.BackgroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorPlum;

                string filename = string.Empty;
                saveFileDialog1.InitialDirectory = @"C:\";
                saveFileDialog1.Title = "Save Word Files";
                saveFileDialog1.CheckFileExists = true;
                saveFileDialog1.CheckPathExists = true;
                saveFileDialog1.DefaultExt = "docx";
                saveFileDialog1.Filter = "Word files (*.doc) |*.doc|Word files (*.docx) |*.docx|All files (*.*) |*.*";
                saveFileDialog1.FilterIndex = 2;
                saveFileDialog1.RestoreDirectory = true;

                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    filename = saveFileDialog1.FileName;

                    for (int i = 0; i <= Dt.Rows.Count; i++)
                    {
                        if (i == Dt.Rows.Count)
                        {
                            oWordDoc.SaveAs(filename, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                            MessageBox.Show("The file " + saveFileDialog1.FileName + " has been exported successfully.");
                        }

                        else
                        {
                             storing header part in Word 
                            for (int i2 = 1; i2 < dataGridView1.Columns.Count + 1; i2++)
                            {
                                tbl.Cell(1, i2).Range.Text = dataGridView1.Columns[i2 - 1].HeaderText;
                            }

                             storing each row and column value to Word document 
                            for (int j = 0; j < Dt.Columns.Count; j++)
                            {
                                tbl.Cell(i + 2, j + 1).Range.Text = Dt.Rows[i][j].ToString();
                            }
                        }
                    }
                }


              
                StyleDataGridView();
                oWord.Selection.TypeParagraph();
            }
            catch
            {

            }
            

        }