public static function run($dataDir = null)
{
$saveFormat = new SaveFormat();
//Instantiate a new workbook
//Open a template file
$book = new Workbook($dataDir . "book.xls");
//Set the active cell
$book->getWorksheets()->get(0)->setActiveCell("A20");
//Split the worksheet window
$book->getWorksheets()->get(0)->split();
//Save the excel file
$book->save($dataDir . "book.out.xls", $saveFormat->EXCEL_97_TO_2003);
//Print Message
print "Panes split successfully." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Excel object by excel file path
$workbook = new Workbook($dataDir . "book.xls");
//Accessing the first worksheet in the Excel file
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
//Applying freeze panes settings
$worksheet->freezePanes(3, 2, 3, 2);
//Saving the modified Excel file in default format
$workbook->save($dataDir . "book.out.xls");
//Print Message
print "Panes freeze successfull." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Excel object by excel file path
$workbook = new Workbook($dataDir . "book1.xls");
//Adding a new worksheet to the Workbook object
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
//Displaying the worksheet in page break preview
$worksheet->setPageBreakPreview(true);
//Saving the modified Excel file in default format
$workbook->save($dataDir . "output.xls");
// Print message
print "Page break preview is enabled for sheet 1, please check the output document." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Workbook object by excel file path
$workbook = new Workbook($dataDir . "book1.xls");
//Accessing the first worksheet in the Excel file
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
//Hiding the first worksheet of the Excel file
$worksheet->setVisible(false);
//Saving the modified Excel file in default (that is Excel 2003) format
$workbook->save($dataDir . "output.xls");
// Print message
print "Worksheet 1 is now hidden, please check the output document." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Excel object by excel file path
$workbook = new Workbook($dataDir . "book1.xls");
//Accessing the first worksheet in the Excel file
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
//Setting the zoom factor of the worksheet to 75
$worksheet->setZoom(75);
//Saving the modified Excel file in default format
$workbook->save($dataDir . "output.xls");
// Print message
print "Zoom factor set to 75% for sheet 1, please check the output document.";
}
public static function run($dataDir = null)
{
//Creating a file stream containing the Excel file to be opened
$fstream = new FileInputStream($dataDir . "book.xls");
//Instantiating a Workbook object with the stream
$workbook = new Workbook($fstream);
//Removing a worksheet using its sheet name
$workbook->getWorksheets()->removeAt("Sheet1");
//Saving the Excel file
$workbook->save($dataDir . "book.out.xls");
//Closing the file stream to free all resources
$fstream->close();
//Print Message
print "Sheet removed successfully.";
}
public static function run($dataDir = null)
{
$filesFormatType = new FileFormatType();
//Instantiating a Workbook object
$workbook = new Workbook($dataDir . "book1.xls");
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
$protection = $worksheet->getProtection();
//Unprotecting the worksheet with a password
$worksheet->unprotect("aspose");
// Save the excel file.
$workbook->save($dataDir . "output.xls", $filesFormatType->EXCEL_97_TO_2003);
//Print Message
print "Worksheet unprotected successfully.";
}
public static function scaling($dataDir = null)
{
# Instantiating a Workbook object by excel file path
$workbook = new Workbook($dataDir . 'Book1.xls');
# Accessing the first worksheet in the Excel file
$worksheets = $workbook->getWorksheets();
$sheet_index = $worksheets->add();
$sheet = $worksheets->get($sheet_index);
# Setting the scaling factor to 100
$page_setup = $sheet->getPageSetup();
$page_setup->setZoom(100);
# Saving the modified Excel file in default (that is Excel 2003) format
$workbook->save($dataDir . "Scaling.xls");
print "Set scaling, please check the output file." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Workbook object
$workbook = new Workbook();
//Adding a new worksheet to the Workbook object
$worksheets = $workbook->getWorksheets();
$sheetIndex = $worksheets->add();
$worksheet = $worksheets->get($sheetIndex);
//Setting the name of the newly added worksheet
$worksheet->setName("My Worksheet");
//Saving the Excel file
$workbook->save($dataDir . "book.out.xls");
//Print Message
print "Sheet added successfully.";
}
public static function run($dataDir = null)
{
$saveFormat = new SaveFormat();
$path = $dataDir . "Template.xlsx";
//Create a workbook object from the template file
$workbook = new Workbook($path);
//Convert each worksheet into svg format in a single page.
$imgOptions = new ImageOrPrintOptions();
$imgOptions->setSaveFormat($saveFormat->SVG);
$imgOptions->setOnePagePerSheet(true);
//Convert each worksheet into svg format
$sheetCount = $workbook->getWorksheets()->getCount();
for ($i = 0; $i < $sheetCount; $i++) {
$sheet = $workbook->getWorksheets()->get($i);
$sr = new SheetRender($sheet, $imgOptions);
$pageCount = $sr->getPageCount();
for ($k = 0; $k < $pageCount; $k++) {
//Output the worksheet into Svg image format
$sr->toImage($k, $path . $sheet->getName() . $k . ".out.svg");
}
}
// Print message
print "Excel to SVG conversion completed successfully.";
}
public static function run($dataDir = null)
{
// Instantiating a Workbook object that represents a Microsoft Excel file.
$workbook = new Workbook();
// Note when you create a new workbook, a default worksheet,
// "Sheet1", is by default added to the workbook.
// Accessing the first worksheet in the book ("Sheet1").
$sheet = $workbook->getWorksheets()->get(0);
# Access cell "A1" in the sheet.
$cell = $sheet->getCells()->get("A1");
# Input the "Hello World!" text into the "A1" cell
$cell->setValue("Hello World!");
# Saving the modified Excel file in default (that is Excel 2003) format
$file_format_type = new FileFormatType();
$workbook->save($dataDir . "HelloWorld.xls", $file_format_type->EXCEL_97_TO_2003);
print "Document has been saved, please check the output file.";
}
public static function run($dataDir = null)
{
$saveFormat = new SaveFormat();
$workbook = new Workbook($dataDir . "Book1.xls");
//Get the first worksheet.
$sheet = $workbook->getWorksheets()->get(0);
//Apply different Image and Print options
$options = new ImageOrPrintOptions();
//Set the Format
$options->setSaveFormat($saveFormat->XPS);
// Render the sheet with respect to specified printing options
$sr = new SheetRender($sheet, $options);
$sr->toImage(0, $dataDir . "out_printingxps.xps");
//Save the complete Workbook in XPS format
$workbook->save($dataDir . "out_whole_printingxps", $saveFormat->XPS);
// Print message
print "Excel to XPS conversion performed successfully.";
}
public static function run($dataDir = null)
{
$filesFormatType = new FileFormatType();
//Instantiating a Workbook object
$workbook = new Workbook($dataDir . "book1.xls");
$worksheets = $workbook->getWorksheets();
$worksheet = $worksheets->get(0);
$protection = $worksheet->getProtection();
//The following 3 methods are only for Excel 2000 and earlier formats
$protection->setAllowEditingContent(false);
$protection->setAllowEditingObject(false);
$protection->setAllowEditingScenario(false);
//Unprotecting the worksheet
$worksheet->unprotect();
// Save the excel file.
$workbook->save($dataDir . "output.xls", $filesFormatType->EXCEL_97_TO_2003);
//Print Message
print "Worksheet unprotected successfully." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiating a Excel object by excel file path
$excel = new Workbook($dataDir . "book1.xls");
//Accessing the first worksheet in the Excel file
$worksheets = $excel->getWorksheets();
$worksheet = $worksheets->get(0);
$protection = $worksheet->getProtection();
//The following 3 methods are only for Excel 2000 and earlier formats
$protection->setAllowEditingContent(false);
$protection->setAllowEditingObject(false);
$protection->setAllowEditingScenario(false);
//Protects the first worksheet with a password "1234"
$protection->setPassword("1234");
//Saving the modified Excel file in default format
$excel->save($dataDir . "output.xls");
//Print Message
print "Sheet protected successfully." . PHP_EOL;
}
public static function run($dataDir = null)
{
$imageFormat = new ImageFormat();
//Instantiate a new workbook with path to an Excel file
$book = new Workbook($dataDir . "MyTestBook1.xls");
//Create an object for ImageOptions
$imgOptions = new ImageOrPrintOptions();
//Set the image type
$imgOptions->setImageFormat($imageFormat->getPng());
//Get the first worksheet.
$sheet = $book->getWorksheets()->get(0);
//Create a SheetRender object for the target sheet
$sr = new SheetRender($sheet, $imgOptions);
for ($j = 0; $j < $sr->getPageCount(); $j++) {
//Generate an image for the worksheet
$sr->toImage($j, $dataDir . "mysheetimg" . $j . ".png");
}
// Print message
print "Images generated successfully." . PHP_EOL;
}
public static function run($dataDir = null)
{
//Instantiate a Workbook object by excel file path
$workbook = new Workbook($dataDir . "Book1.xls");
//Retrieve a list of all custom document properties of the Excel file
$customProperties = $workbook->getWorksheets()->getCustomDocumentProperties();
//Accessing a custom document property by using the property index
$customProperty1 = $customProperties->get(3);
//Accessing a custom document property by using the property name
$customProperty2 = $customProperties->get("Owner");
//Adding a custom document property to the Excel file
$publisher = $customProperties->add("Publisher", "Aspose");
//Save the file
$workbook->save($dataDir . "Test_Workbook.xls");
//Removing a custom document property
$customProperties->remove("Publisher");
//Save the file
$workbook->save($dataDir . "Test_Workbook_RemovedProperty.xls");
// Print message
print "Excel file's custom properties accessed successfully." . PHP_EOL;
}
public static function run($dataDir = null)
{
$chartType = new ChartType();
$color = new Color();
$imageFormat = new ImageFormat();
//Create a new Workbook.
$workbook = new Workbook();
//Get the first worksheet.
$sheet = $workbook->getWorksheets()->get(0);
//Set the name of worksheet
$sheet->setName("Data");
//Get the cells collection in the sheet.
$cells = $workbook->getWorksheets()->get(0)->getCells();
//Put some values into a cells of the Data sheet.
$cells->get("A1")->setValue("Region");
$cells->get("A2")->setValue("France");
$cells->get("A3")->setValue("Germany");
$cells->get("A4")->setValue("England");
$cells->get("A5")->setValue("Sweden");
$cells->get("A6")->setValue("Italy");
$cells->get("A7")->setValue("Spain");
$cells->get("A8")->setValue("Portugal");
$cells->get("B1")->setValue("Sale");
$cells->get("B2")->setValue(70000);
$cells->get("B3")->setValue(55000);
$cells->get("B4")->setValue(30000);
$cells->get("B5")->setValue(40000);
$cells->get("B6")->setValue(35000);
$cells->get("B7")->setValue(32000);
$cells->get("B8")->setValue(10000);
//Create chart
$chartIndex = $sheet->getCharts()->add($chartType->COLUMN, 12, 1, 33, 12);
$chart = $sheet->getCharts()->get($chartIndex);
//Set properties of chart title
$chart->getTitle()->setText("Sales By Region");
$chart->getTitle()->getFont()->setBold(true);
$chart->getTitle()->getFont()->setSize(12);
//Set properties of nseries
$chart->getNSeries()->add("Data!B2:B8", true);
$chart->getNSeries()->setCategoryData("Data!A2:A8");
//Set the fill colors for the series's data points (France - Portugal(7 points))
$chartPoints = $chart->getNSeries()->get(0)->getPoints();
$point = $chartPoints->get(0);
$point->getArea()->setForegroundColor(java_values($color->getCyan()));
$point = $chartPoints->get(1);
$point->getArea()->setForegroundColor($color->getBlue());
$point = $chartPoints->get(2);
$point->getArea()->setForegroundColor($color->getYellow());
$point = $chartPoints->get(3);
$point->getArea()->setForegroundColor($color->getRed());
$point = $chartPoints->get(4);
$point->getArea()->setForegroundColor($color->getBlack());
$point = $chartPoints->get(5);
$point->getArea()->setForegroundColor($color->getGreen());
$point = $chartPoints->get(6);
$point->getArea()->setForegroundColor($color->getMaroon());
//Set the legend invisible
$chart->setShowLegend(false);
//Get the Chart image
$imgOpts = new ImageOrPrintOptions();
$imgOpts->setImageFormat($imageFormat->getEmf());
$fs = new FileOutputStream($dataDir . "Chart.emf");
//Save the chart image file.
$chart->toImage($fs, $imgOpts);
$fs->close();
// Print message
print "<BR>";
print "Processing performed successfully.";
}
public static function copy_columns($dataDir)
{
# Instantiating a Workbook object by excel file path
$workbook = new Workbook();
# Accessing the first worksheet in the Excel file
$worksheet = $workbook->getWorksheets()->get(0);
# Put some data into header rows (A1:A4)
$i = 0;
while ($i < 5) {
$worksheet->getCells()->get($i, 0)->setValue("Header Row #{$i}");
$i++;
}
# Put some detail data (A5:A999)
$i = 5;
while ($i < 1000) {
$worksheet->getCells()->get($i, 0)->setValue("Detail Row #{$i}");
$i++;
}
# Create another Workbook.
$workbook1 = new Workbook();
# Get the first worksheet in the book.
$worksheet1 = $workbook1->getWorksheets()->get(0);
# Copy the first column from the first worksheet of the first workbook into
# the first worksheet of the second workbook.
$worksheet1->getCells()->copyColumn($worksheet->getCells(), 0, 2);
# Autofit the column.
$worksheet1->autoFitColumn(2);
# Saving the modified Excel file in default (that is Excel 2003) format
$workbook->save($dataDir . "Copy Columns.xls");
print "Copy Columns Successfully." . PHP_EOL;
}