Export Each Excel row in separate PDF using C# and VB.Net in ASP.Net

Last Reply 7 months ago By pandeyism

Posted 7 months ago

Hi All,

I have an Excel of 1000 rows , I want to create new PDF (Invoice Format) of EACH ROW. 

CONDITIONS - 

1)Uploading Excel file. , and download and save PDF files in some particular folder .

2)Save PDF of Each Excel rows.

Example - If there is 500 rows in excel sheet there will be 500 PDF save in folder.
Desktop / Web application does not matter. Only Solutions Matter

 

Please Help...I am stuck for around 3 Days

Posted 7 months ago

Hi BugHunter,

Refer below sample.

HTML

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

Namespaces

C#

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

VB.Net

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

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(Server.MapPath("~/Files/") + fileName);
    using (XLWorkbook workBook = new XLWorkbook(Server.MapPath("~/Files/") + fileName))
    {
        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++;
                }
            }
        }
        int loop = 0;
        foreach (DataRow row in dt.Rows)
        {
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("<table border = '1'>");
                    sb.Append("<tr>");
                    foreach (DataColumn column in dt.Columns)
                    {
                        sb.Append("<th align='center' style='font-weight: bold; color: Red;'>");
                        sb.Append(column.ColumnName);
                        sb.Append("</th>");
                    }
                    sb.Append("</tr>");
                    sb.Append("<tr>");
                    foreach (DataColumn column in dt.Columns)
                    {
                        sb.Append("<td>");
                        sb.Append(row[column]);
                        sb.Append("</td>");
                    }
                    sb.Append("</tr>");
                    sb.Append("</table>");
                    StringReader sr = new StringReader(sb.ToString());

                    Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
                    HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
                    PdfWriter writer = PdfWriter.GetInstance(pdfDoc, new FileStream(Server.MapPath("~/Files/" + "Customer" + (loop + 1) + ".pdf"), FileMode.Create));
                    pdfDoc.Open();
                    htmlparser.Parse(sr);
                    pdfDoc.Close();
                }
                loop++;
            }
        }
    }
}

VB.Net

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(Server.MapPath("~/Files/") & fileName)

    Using workBook As XLWorkbook = New XLWorkbook(Server.MapPath("~/Files/") & fileName)
        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
        Dim [loop] As Integer = 0
        For Each row As DataRow In dt.Rows
            Using sw As StringWriter = New StringWriter()
                Using hw As HtmlTextWriter = New HtmlTextWriter(sw)
                    Dim sb As StringBuilder = New StringBuilder()
                    sb.Append("<table border = '1'>")
                    sb.Append("<tr>")

                    For Each column As DataColumn In dt.Columns
                        sb.Append("<th align='center' style='font-weight: bold; color: Red;'>")
                        sb.Append(column.ColumnName)
                        sb.Append("</th>")
                    Next

                    sb.Append("</tr>")
                    sb.Append("<tr>")

                    For Each column As DataColumn In dt.Columns
                        sb.Append("<td>")
                        sb.Append(row(column))
                        sb.Append("</td>")
                    Next

                    sb.Append("</tr>")
                    sb.Append("</table>")
                    Dim sr As StringReader = New StringReader(sb.ToString())
                    Dim pdfDoc As Document = New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)
                    Dim htmlparser As HTMLWorker = New HTMLWorker(pdfDoc)
                    Dim writer As PdfWriter = PdfWriter.GetInstance(pdfDoc, New FileStream(Server.MapPath("~/Files/" & "Customer" & ([loop] + 1) & ".pdf"), FileMode.Create))
                    pdfDoc.Open()
                    htmlparser.Parse(sr)
                    pdfDoc.Close()
                End Using

                [loop] += 1
            End Using
        Next
    End Using
End Sub

Screenshot

Excel

PDF