This technical tip shows how to use Aspose.Pdf and Aspose.Cells for converting 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.
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.
Please take a look over the following code snippet for this approach.
[C#]
Workbook workbook = new Workbook();
//Creating a file stream containing the Excel file to be opened
FileStream fstream = 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
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]
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")
More about Aspose Components
- Homepage of Aspose.Cells for .NET: http://www.aspose.com/.net/excel-component.aspx
- Homepage of Aspose.Pdf for .NET: http://www.aspose.com/.net/pdf-component.aspx
- Read More Technical Tips by Aspose.Cells for .NET: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/technical-articles.html
Contact Information
Aspose Pty Ltd, Suite 163,
79 Longueville Road
Lane Cove, NSW, 2066
Australia
http://www.aspose.com/
sales@aspose.com
Phone: 888.277.6734
Fax: 866.810.9465
|
