Convert Excel file to Pdf in ASP.Net

Last Reply 6 months ago By pandeyism

Posted 6 months ago

How to convert Excel file into pdf using asp.net?

As i have retrieved the record from database and save it in excel.

Can i convert saved excel file in pdf format ?

 

Posted 6 months ago Modified on 6 months ago

Hi ritesh18a,

Please refer below sample.

HTML

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
</div>

Namespaces

C#

using System.IO;
using System.Data;
using ClosedXML.Excel;
using iTextSharp.text;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;

VB.Net

Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.html.simpleparser
Imports iTextSharp.text.pdf
Imports ClosedXML.Excel
Imports System.Data

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
        CreatePdf(dt);
    }
}

private void CreatePdf(DataTable dt)
{
    Response.ContentType = "application/pdf";
    Response.AddHeader("content-disposition", "attachment;filename=DataTable.pdf");
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    GridView gvCustomers = new GridView();
    gvCustomers.DataSource = dt;
    gvCustomers.DataBind();
    gvCustomers.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();
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

VB.Net

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()

            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
        CreatePdf(dt)
    End Using
End Sub

Private Sub CreatePdf(ByVal dt As DataTable)
    Response.ContentType = "application/pdf"
    Response.AddHeader("content-disposition", "attachment;filename=DataTable.pdf")
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Dim sw As StringWriter = New StringWriter()
    Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
    Dim gvCustomers As GridView = New GridView()
    gvCustomers.DataSource = dt
    gvCustomers.DataBind()
    gvCustomers.RenderControl(hw)
    Dim sr As StringReader = New StringReader(sw.ToString())
    Dim pdfDoc As Document = New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)
    Dim htmlparser As HTMLWorker = New HTMLWorker(pdfDoc)
    PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
    pdfDoc.Open()
    htmlparser.Parse(sr)
    pdfDoc.Close()
    Response.Write(pdfDoc)
    Response.[End]()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub

Screenshot