Click here to Skip to main content
15,943,745 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
Hi, right now i am exporting a table using a hidden grid. Is there any way we can export a dataview directly to excel without using grid or drawing a table in page???
Posted

Yes, you can iterate over your data source directly, and write your file from that without rendering it into a grid first. In many ways, that seems like a better idea to me anyhow.
 
Share this answer
 
Yes you can export table data into multiple document as PDF, Excel, Word and HTML. I am giving you methods for separate document..

protected void ExportToExcel(DataTable datatable) // For EXcel
{
string attachment = "attachment; filename=PaymentFile.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView gridview1 = new GridView();
gridview1.DataSource = datatable;
gridview1.DataBind();
gridview1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}


protected void ExportToPDF(DataTable datatable) // For PDF
{
if (datatable.Rows.Count > 0)
{
PdfDocument BranchDoc = new PdfDocument(PdfDocumentFormat.Letter_8_5x11_Horizontal);
PdfTable table = BranchDoc.NewTable(new Font("Vardana", 7), datatable.Rows.Count, datatable.Columns.Count, 4.0);
table.ImportDataTable(datatable);
table.HeadersRow.SetColors(Color.White, Color.Navy);
table.SetColors(Color.Black, Color.White, Color.Gainsboro);
table.SetBorders(Color.Black, 1, BorderType.CompleteGrid);
table.SetColumnsWidth(new int[] { 6,4,4,4,5,6,6,4,7,9,4,4,5,6,6,5,5,5,5,6});
table.SetContentAlignment(ContentAlignment.MiddleCenter);
table.Columns[1].SetContentAlignment(ContentAlignment.MiddleLeft);
while (!table.AllTablePagesCreated)
{
PdfPage newPage = BranchDoc.NewPage();
PdfTablePage newPdfTablePage = table.CreateTablePage(new PdfArea(BranchDoc, 8, 80, 780, 700));
PdfTextArea pta = new PdfTextArea(new Font("Verdana", 26, FontStyle.Bold), Color.Red, new PdfArea(BranchDoc, 20, 5, 595, 120), ContentAlignment.MiddleCenter, "Revenue Reconcilation-Payment File");
newPage.Add(newPdfTablePage);
newPage.Add(pta);
newPage.SaveToDocument();
}
Response.ClearHeaders();
Response.AppendHeader("Content-disposition", string.Format("attachment;filename={0}", "PaymentFile.pdf"));
Response.ContentType = "application/pdf";
BranchDoc.SaveToStream(Response.OutputStream);
Response.End();
}
else
{
lblMsg.Text = "No Data for Export";
}

}



protected void ExportToWord(DataTable datatable) // For Word
{
string attachment = "attachment; filename=Payment.doc";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-word";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView gridview1 = new GridView();
gridview1.DataSource = datatable;
gridview1.DataBind();
gridview1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}



protected void ExportToHTML(DataTable datatable) // For Html
{
string attachment = "attachment; inline; filename=Payment.html";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/html";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView gridview1 = new GridView();
gridview1.DataSource = datatable;
gridview1.DataBind();
gridview1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

I have used these functions and it is working properly. so i think it will be helpfull for you.


Thanks & Regards
Lalit Kumar
New Delhi
India
 
Share this answer
 
I find the library PdfDocument :confused:
 
Share this answer
 
you can use

ExcelPackage: Office Open XML Format file creation

by the using of ExcelPackege no need of gridview and any type of
interope dll for excel and
excel installation on server
its best way

for more help Click here
 
Share this answer
 
protected void btnExportInWord_Click(object sender, EventArgs e)
{
try
{
Response.AddHeader("content-disposition", "attachment;filename=Export.doc");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.word";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
grdExport.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(grdExport);
frm.RenderControl(htmlWrite);
//GridView1.RenderControl(htw);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (SqlException Ex)
{
lblError.Text = Ex.Message;
}
}
protected void btnExportInExcel_Click(object sender, EventArgs e)
{
try
{
string attachment = "attachment; filename=Export.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
grdExport.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(grdExport);
frm.RenderControl(htw);
//GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
catch (SqlException Ex)
{
lblError.Text = Ex.Message;
}

}
protected void btnExportInPdf_Click(object sender, EventArgs e)
{
try
{
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment;filename=Export.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
HtmlForm frm = new HtmlForm();
grdExport.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(grdExport);
frm.RenderControl(hw);
StringReader sr = new StringReader(sw.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
Response.Write(pdfDoc);
Response.End();
}
catch (SqlException Ex)
{
lblError.Text = Ex.Message;
}
}
//protected void btnExportInCSV_Click(object sender, EventArgs e)
//{

// try
// {
// Response.ContentType = "application/csv";
// Response.AddHeader("content-disposition", "attachment;filename=Export.csv");
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
// StringWriter sw = new StringWriter();
// HtmlTextWriter hw = new HtmlTextWriter(sw);
// HtmlForm frm = new HtmlForm();
// grdExport.Parent.Controls.Add(frm);
// frm.Attributes["runat"] = "server";
// frm.Controls.Add(grdExport);
// frm.RenderControl(hw);
// StringReader sr = new StringReader(sw.ToString());
// Document csvDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
// HTMLWorker htmlparser = new HTMLWorker(csvDoc);
// PdfWriter.GetInstance(csvDoc, Response.OutputStream);
// csvDoc.Open();
// htmlparser.Parse(sr);
// csvDoc.Close();
// Response.Write(csvDoc);
// Response.End();
// }
// catch (SqlException Ex)
// {
// lblError.Text = Ex.Message;
// }
//}
}
 
Share this answer
 
Comments
Prasanta_Prince 20-Apr-11 8:40am    
Try to put your code in code block.
Try to put this Function and call from your code behind.

public static void ExportToSpreadsheet(DataTable table, string name)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();

            foreach (DataColumn column in table.Columns)
            {
                context.Response.Write(column.ColumnName + ",");
            }
            context.Response.Write(Environment.NewLine);

            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
                }
                context.Response.Write(Environment.NewLine);
            }
            context.Response.ContentType = "text/csv";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");
            context.Response.End();
        }

Hope This can work for you.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900