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 + '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/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/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/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 + '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 __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/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 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 __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 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 __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 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 + '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 + '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/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."
