Export Excel Worksheet data into PDF table

Background

Aspose.Pdf and Aspose.Cells can be used to convert the excel worksheet into pdf document. In this all the contents over the Excel worksheet would be transformed in the form of Pdf file. Beside this, if you only need to extract the contents over the excel worksheet and display as Table in Pdf document, it very possible. Through this method you can even specify the formatting of the table in PDF document i.e. set the background color of the Table Rows, specify the font for the table cells, set the font color for the table cells and many more.

Details

In the example specified below, we would be using an excel sheet which contains 5 rows and 3 columns. DataTable class offers the capability to use the first row of the sheet as ColumnName. In the code specified below, we are going to Open the excel worksheet using Aspose.Cells. In our example we are going read the data from first sheet of the workbook, and export data into DataTable object using ExportDataTable (….) method in Cells class of Aspose.Cells .
Create an instance of Pdf file, create a Table object and add it to Paragraphs collection of Pdf section. Before importing the data from DataTable object, first we would define the table formatting and then will be using ImportDataTable (…) method in Table class of Aspose.Pdf to import the data from DataTable into Pdf table.

You can also import data from Object Array and DataView into table generated by Aspose.Pdf. For more information please visit Integrate Table with Database

Following is the data present in Excel worksheet.

We would be using the following code snippet to extract data from Excel worksheet and display it as table in PDF document.

C#

Workbook workbook = new Workbook();
//Creating a file stream containing the Excel file to be openedFileStream fstream = new FileStream("d:\\pdftest\\newBook1.xls", FileMode.Open);//Opening the Excel file through the file streamworkbook.Open(fstream);
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);
//Closing the file stream to free all resources
fstream.Close();

//Instantiate a Pdf instanc
Aspose.Pdf.Generator.Pdf pdf1 = new Aspose.Pdf.Generator.Pdf(); 
//Create a section in the Pdf instance
Aspose.Pdf.Generator.Section sec1 = pdf1.Sections.Add(); 

//Create a Table object
Aspose.Pdf.Generator.Table tab1 = new Aspose.Pdf.Generator.Table(); 

//Add the Table object in the paragraphs collection of the section
sec1.Paragraphs.Add(tab1); 

//Set column widths of the table.  We need to specify the ColumnCount manually.
// As the curent excel worksheet has three columsn, so we are specifying the same count
tab1.ColumnWidths = "40 100 100"; 

 //Set default cell border of the table using BorderInfo object
tab1.DefaultCellBorder = new Aspose.Pdf.Generator.BorderInfo((int) Aspose.Pdf.Generator.BorderSide.All,0.1F); 

//Import data into the Table object from the DataTable created above
tab1.ImportDataTable(dataTable, true, 0, 0, dataTable.Rows.Count+1, dataTable.Columns.Count);
 
//Get 1st row from the table
Aspose.Pdf.Generator.Row row1 = tab1.Rows[0]; 

//Iterate through all cells in the 1st row and set their background color to blue
foreach (Aspose.Pdf.Generator.Cell curCell in row1.Cells)
{
    // set the background of all the cells in 1st row of the table.
    curCell.BackgroundColor = new Aspose.Pdf.Generator.Color("Blue");
    // set the font face for the cells of 1st row in the table.
    curCell.DefaultCellTextInfo.FontName = "Helvetica-Oblique";
    // set the font Color of all the cells in 1st row of the table.
    curCell.DefaultCellTextInfo.Color = new Aspose.Pdf.Generator.Color("Yellow");
    // Set the text allignment for the cells of 1st row as Center.
    curCell.DefaultCellTextInfo.Alignment = Aspose.Pdf.Generator.AlignmentType.Center;
}

for (int All_Rows = 1; All_Rows <= dataTable.Rows.Count; All_Rows++)
 {
     //Iterate through all cells in the 1st row and set their background color to blue
     foreach (Aspose.Pdf.Generator.Cell curCell in tab1.Rows[All_Rows].Cells)
     {
        // set the background color of all the cells except of the 1st row.
         curCell.BackgroundColor = new Aspose.Pdf.Generator.Color("Gray");
        // set the Text color of all the cells except the 1st row.
         curCell.DefaultCellTextInfo.Color = new Aspose.Pdf.Generator.Color("White");
     }  
 }

//Save the Pdf
pdf1.Save(@"d:\pdftest\Exceldata_toPdf_table.pdf");

VB.NET

Dim workbook As Workbook = New Workbook()
'Creating a file stream containing the Excel file to be opened
Dim fstream As FileStream = New FileStream("d:\\pdftest\\newBook1.xls", FileMode.Open)
'Opening the Excel file through the file stream
workbook.Open(fstream)
'Accessing the first worksheet in the Excel file
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
Dim dataTable As DataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, True)
'Closing the file stream to free all resources
fstream.Close()

'Instantiate a Pdf instanc
Dim pdf1 As Aspose.Pdf.Generator.Pdf = New Aspose.Pdf.Generator.Pdf()
'Create a section in the Pdf instance
Dim sec1 As Aspose.Pdf.Generator.Section = pdf1.Sections.Add()

'Create a Table object
Dim tab1 As Aspose.Pdf.Generator.Table = New Aspose.Pdf.Generator.Table()

'Add the Table object in the paragraphs collection of the section
sec1.Paragraphs.Add(tab1)

'Set column widths of the table.  We need to specify the ColumnCount manually.
' As the curent excel worksheet has three columsn, so we are specifying the same count
tab1.ColumnWidths = "40 100 100"

'Set default cell border of the table using BorderInfo object
tab1.DefaultCellBorder = New Aspose.Pdf.Generator.BorderInfo(Aspose.Pdf.Generator.BorderSide.All, 0.1F)

'Import data into the Table object from the DataTable created above
tab1.ImportDataTable(dataTable, True, 0, 0, dataTable.Rows.Count + 1, dataTable.Columns.Count)

'Get 1st row from the table
Dim row1 As Aspose.Pdf.Generator.Row = tab1.Rows(0)

'Iterate through all cells in the 1st row and set their background color to blue

For Each curCell As Aspose.Pdf.Generator.Cell In row1.Cells

     ' set the background of all the cells in 1st row of the table.
     curCell.BackgroundColor = New Aspose.Pdf.Generator.Color("Blue")
     ' set the font face for the cells of 1st row in the table.
     curCell.DefaultCellTextInfo.FontName = "Helvetica-Oblique"
     ' set the font Color of all the cells in 1st row of the table.
     curCell.DefaultCellTextInfo.Color = New Aspose.Pdf.Generator.Color("Yellow")
     ' Set the text allignment for the cells of 1st row as Center.
     curCell.DefaultCellTextInfo.Alignment = Aspose.Pdf.Generator.AlignmentType.Center
Next
     Dim All_Rows As Integer
     For All_Rows = 1 To dataTable.Rows.Count

            'Iterate through all cells in the 1st row and set their background color to blue
            For Each curCell As Aspose.Pdf.Generator.Cell In tab1.Rows(All_Rows).Cells

            ' set the background color of all the cells except of the 1st row.
            curCell.BackgroundColor = New Aspose.Pdf.Generator.Color("Gray")
            ' set the Text color of all the cells being added to the table except the 1st row.
            curCell.DefaultCellTextInfo.Color = New Aspose.Pdf.Generator.Color("White")
            Next
     Next All_Rows

'Save the Pdf
pdf1.Save("d:\pdftest\Exceldata_toPdf_table.pdf")

Export Excel Worksheet data into PDF table

Background

Details

Labels