This technical tip shows how to use Aspose.Pdf and Aspose.Cells for converting the excel worksheet i

This tip submitted by sherazam on 2013-05-23 01:28:48. It has been viewed 12117 times.
Rating of 7.1 with 27 votes

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");

More tips

Help your fellow programmers! Add a tip!