def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/WorksheetToImage/' imageFormat = ImageFormat #Instantiate a workbook with path to an Excel file book = Workbook(dataDir + "Book1.xls") #Create an object for ImageOptions imgOptions = 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 = SheetRender(sheet, imgOptions) for i in range(sr.getPageCount()): #Generate an image for the worksheet sr.toImage(i, dataDir + "mysheetimg" + ".png") # Print message print "Images generated successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/ConvertingToXPS/' saveFormat = SaveFormat workbook = Workbook(dataDir + "Book1.xls") #Get the first worksheet. sheet = workbook.getWorksheets().get(0) #Apply different Image and Print options options = ImageOrPrintOptions() #Set the Format options.setSaveFormat(saveFormat.XPS) # Render the sheet with respect to specified printing options sr = 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."
def __init__(self): dataDir = Settings.dataDir + "WorkingWithWorksheets/UnprotectingSimplyProtectedWorksheet/" filesFormatType = FileFormatType # Instantiating a Workbook object workbook = 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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/ProtectingWorksheet/' #Instantiating a Excel object by excel file path excel = 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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/UnprotectingSimplyProtectedWorksheet/' filesFormatType = FileFormatType #Instantiating a Workbook object workbook = 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."
def __init__(self): dataDir = Settings.dataDir + "WorkingWithFiles/WorksheetToImage/" imageFormat = ImageFormat # Instantiate a workbook with path to an Excel file book = Workbook(dataDir + "Book1.xls") # Create an object for ImageOptions imgOptions = 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 = SheetRender(sheet, imgOptions) for i in range(sr.getPageCount()): # Generate an image for the worksheet sr.toImage(i, dataDir + "mysheetimg" + ".png") # Print message print "Images generated successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/ConvertingWorksheetToSVG/' saveFormat = SaveFormat workbook = Workbook(dataDir + "Book1.xls") #Convert each worksheet into svg format in a single page. imgOptions = ImageOrPrintOptions() imgOptions.setSaveFormat(saveFormat.SVG) imgOptions.setOnePagePerSheet(True) #Convert each worksheet into svg format sheetCount = workbook.getWorksheets().getCount() #for(i=0; i<sheetCount; i++) for i in range(sheetCount): sheet = workbook.getWorksheets().get(i) sr = SheetRender(sheet, imgOptions) pageCount = sr.getPageCount() #for (k = 0 k < pageCount k++) for k in range(pageCount): #Output the worksheet into Svg image format sr.toImage(k, dataDir + sheet.getName() + ".out.svg") # Print message print "Excel to SVG conversion completed successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/ConvertingExcelFilesToHtml/' saveFormat = SaveFormat workbook = Workbook(dataDir + "Book1.xls") #Save the document in PDF format workbook.save(dataDir + "OutBook1.html", saveFormat.HTML) # Print message print "\n Excel to HTML conversion performed successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/Excel2PdfConversion/' saveFormat = SaveFormat workbook = Workbook(dataDir + "Book1.xls") #Save the document in PDF format workbook.save(dataDir + "OutBook1.pdf", saveFormat.PDF) # Print message print "\n Excel to PDF conversion performed successfully."
def __init__(self): dataDir = Settings.dataDir + "WorkingWithFiles/ConvertingExcelFilesToHtml/" saveFormat = SaveFormat workbook = Workbook(dataDir + "Book1.xls") # Save the document in PDF format workbook.save(dataDir + "OutBook1.html", saveFormat.HTML) # Print message print "\n Excel to HTML conversion performed successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/DisplayHideTabs/' workbook = Workbook(dataDir + "Book1.xls") #Hiding the tabs of the Excel file workbook.getSettings().setShowTabs(False) #Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "output.xls") # Print message print "Tabs are now hidden, please check the output file."
def delete_column(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) # Deleting a column from the worksheet at 2nd position worksheet.getCells().deleteColumns(1,1,True) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Delete Column.xls") print "Delete Column Successfully."
def delete_multiple_rows(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) # Deleting 10 rows from the worksheet starting from 3rd row worksheet.getCells().deleteRows(2,10,True) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Delete Multiple Rows.xls") print "Delete Multiple Rows Successfully."
def __init__(self): dataDir = Settings.dataDir + 'quickstart/' workbook = Workbook() sheet = workbook.getWorksheets().get(0) cell = sheet.getCells().get("A1") cell.setValue("Hello World!") file_format_type = FileFormatType workbook.save(dataDir + "HelloWorld.xls" , file_format_type.EXCEL_97_TO_2003 ) print "Document has been saved, please check the output file.";
def __init__(self): dataDir = Settings.dataDir + 'quickstart/' workbook = Workbook() sheet = workbook.getWorksheets().get(0) cell = sheet.getCells().get("A1") cell.setValue("Hello World!") file_format_type = FileFormatType workbook.save(dataDir + "HelloWorld.xls", file_format_type.EXCEL_97_TO_2003) print "Document has been saved, please check the output file."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/FreezePanes/' workbook = Workbook(dataDir + "Book1.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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/ZoomFactor/' workbook = 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."
def set_row_height(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) cells = worksheet.getCells() # Setting the height of the second row to 13 cells.setRowHeight(1, 13) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Set Row Height.xls") print "Set Row Height Successfully."
def move_worksheet(dataDir): dataDir = Settings.dataDir + 'WorkingWithWorksheets/CopyingAndMovingWorksheets/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + "Book1.xls") # Get the first worksheet in the book. sheet = workbook.getWorksheets().get(0) # Move the first sheet to the third position in the workbook. sheet.moveTo(2) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Move Worksheet.xls") print "Move worksheet, please check the output file."
def copy_worksheet(dataDir): dataDir = Settings.dataDir + 'WorkingWithWorksheets/CopyingAndMovingWorksheets/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + "Book1.xls") # Create a Worksheets object with reference to the sheets of the Workbook. sheets = workbook.getWorksheets() # Copy data to a new sheet from an existing sheet within the Workbook. sheets.addCopy("Sheet1") # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Copy Worksheet.xls") print "Copy worksheet, please check the output file."
def set_column_width(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) cells = worksheet.getCells() # Setting the width of the second column to 17.5 cells.setColumnWidth(1, 17.5) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Set Column Width.xls") print "Set Column Width Successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/HideUnhideWorksheet/' workbook = 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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/FreezePanes/' workbook = Workbook(dataDir + "Book1.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."
def insert_multiple_rows(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) # Inserting a row into the worksheet at 3rd position worksheet.getCells().insertRows(2,10) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Insert Multiple Rows.xls") print "Insert Multiple Rows Successfully."
def copy_rows(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) # Copy the second row with data, formattings, images and drawing objects # to the 12th row in the worksheet. worksheet.getCells().copyRow(worksheet.getCells(),1,11) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Copy Rows.xls") print "Copy Rows Successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/ManagingDocumentProperties/' workbook = 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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/DisplayHideGridlines/' workbook = Workbook(dataDir + "Book1.xls") #Accessing the first worksheet in the Excel file worksheets = workbook.getWorksheets() worksheet = worksheets.get(0) #Hiding the grid lines of the first worksheet of the Excel file worksheet.setGridlinesVisible(False) #Saving the modified Excel file in default (that is Excel 2000) format workbook.save(dataDir + "output.xls") # Print message print "Grid lines are now hidden on sheet 1, please check the output document."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/PageBreakPreview/' workbook = 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."
def scaling(dataDir): dataDir = Settings.dataDir + 'WorkingWithWorksheets/SettingPageOptions/' # Instantiating a Workbook object by excel file path workbook = 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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithFiles/ConvertingToMhtmlFiles/' saveFormat = SaveFormat #Specify the file path filePath = dataDir + "Book1.xlsx" #Specify the HTML saving options sv = HtmlSaveOptions(saveFormat.M_HTML) #Instantiate a workbook and open the template XLSX file wb = Workbook(filePath) #Save the MHT file wb.save(filePath + ".out.mht", sv) # Print message print "Excel to MHTML conversion performed successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/AddingWorksheetstoNewExcelFile/' workbook = Workbook(dataDir + "Book1.xls") #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."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/RemovingWorksheetsusingSheetName/' #Creating a file stream containing the Excel file to be opened fstream = FileInputStream(dataDir + "Book1.xls"); #Instantiating a Workbook object with the stream workbook = 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.";
def hide_rows_columns(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) cells = worksheet.getCells() # Hiding the 3rd row of the worksheet cells.hideRow(2) # Hiding the 2nd column of the worksheet cells.hideColumn(1) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Hide Rows And Columns.xls") print "Hide Rows And Columns Successfully."
def unhide_rows_columns(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) cells = worksheet.getCells() # Unhiding the 3rd row and setting its height to 13.5 cells.unhideRow(2,13.5) # Unhiding the 2nd column and setting its width to 8.5 cells.unhideColumn(1,8.5) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Unhide Rows And Columns.xls") print "Unhide Rows And Columns Successfully."
def autofit_column(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Book1.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) # Auto-fitting the 4th column of the worksheet worksheet.autoFitColumn(3) # Auto-fitting the 4th column of the worksheet based on the contents in a range of # cells (from 1st to 9th row) within the column #worksheet.autoFitColumn(3,0,8) #Uncomment this line if you to do AutoFit Column in a Range of Cells. Also, comment line 310. # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Autofit Column.xls") print "Autofit Column Successfully."
def ungroup_rows_columns(dataDir): dataDir = Settings.dataDir + 'WorkingWithRowsAndColumns/RowsAndColumns/' # Instantiating a Workbook object by excel file path workbook = Workbook(dataDir + 'Group Rows And Columns.xls') # Accessing the first worksheet in the Excel file worksheet = workbook.getWorksheets().get(0) cells = worksheet.getCells() # Ungrouping first six rows (from 0 to 5) cells.ungroupRows(0,5) # Ungrouping first three columns (from 0 to 2) cells.ungroupColumns(0,2) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Ungroup Rows And Columns.xls") print "Ungroup Rows And Columns Successfully."
def __init__(self): dataDir = Settings.dataDir + 'WorkingWithWorksheets/UnprotectingPasswordProtectedWorksheet/' filesFormatType = FileFormatType #Instantiating a Workbook object workbook = 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."
def page_orientation(dataDir): dataDir = Settings.dataDir + 'WorkingWithWorksheets/SettingPageOptions/' # Instantiating a Workbook object by excel file path workbook = Workbook() # Accessing the first worksheet in the Excel file worksheets = workbook.getWorksheets() sheet_index = worksheets.add() sheet = worksheets.get(sheet_index) # Setting the orientation to Portrait page_setup = sheet.getPageSetup() page_orientation_type = PageOrientationType page_setup.setOrientation(page_orientation_type.PORTRAIT) # Saving the modified Excel file in default (that is Excel 2003) format workbook.save(dataDir + "Page Orientation.xls") print "Set page orientation, please check the output file."