Knowledgebase (2300)
Converting a document from Word to TIFF can be useful when you need to share the content as an image file, such as for electronic forms, presentations, or publishing. The TIFF format preserves the visual layout and appearance of the document. Conversely, converting a TIFF image to a Word document can be helpful when you want to present information in the Word format.
This article demonstrates how to convert Word to TIFF and TIFF to Word (non-editable) using Python and the Spire.Doc for Python library.
Install the Required Libraries
This situation relies on the combination of Spire.Doc for Python and Pillow (PIL). Spire.Doc is used to read, create and convert Word documents, while the PIL library is used for handling TIFF files and accessing their frames.
The libraries can be easily installed on your device through the following pip commands.
pip install Spire.Doc pip install pillow
Convert Word to TIFF in Python
To convert a Word document into a TIFF image, the initial step is to use the Spire.Doc library to load the Word document and transform the individual pages into image data streams. Then, you can leverage the functionality provided by the PIL to merge these separate image streams into a unified TIFF image.
The following are the steps to convert Word to TIFF using Python.
- Create a Document object.
- Load a Word document from a specified file path.
- Iterate through the pages in the document.
- Convert each page into an image stream using Document.SaveImageToSteams() method.
- Convert the image stream into a PIL image.
- Combine these PIL images into a single TIFF image.
- Python
from spire.doc import *
from spire.doc.common import *
from PIL import Image
from io import BytesIO
# Create a Document object
doc = Document()
# Load a Word document
doc.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.docx")
# Create an empty list to store PIL Images
images = []
# Iterate through pages in the document
for i in range(doc.GetPageCount()):
# Convert a specific page to image stream
with doc.SaveImageToStreams(i, ImageType.Bitmap) as imageData:
# Open a specific image stream as a PIL image
img = Image.open(BytesIO(imageData.ToArray()))
# Append the PIL image to list
images.append(img)
# Save the PIL Images as a multi-page TIFF file
images[0].save("Output/ToTIFF.tiff", save_all=True, append_images=images[1:])
# Dispose resources
doc.Dispose()
Convert TIFF to Word in Python
By utilizing PIL library, you can load a TIFF file and break it down into separate PNG images for each frame. You can then utilize the Spire.Doc library to incorporate these separate PNG files as distinct pages within a Microsoft Word document.
To convert a TIFF image to a Word document using Python, follow these steps.
- Create a Document object.
- Add a section to it and set the page margins to zero.
- Load a TIFF image.
- Iterate though the frames in the TIFF image.
- Get a specific frame, and save it as a PNG file.
- Add a paragraph to the section.
- Append the image file to the paragraph.
- Set the page size to be the same as the image size.
- Save the document to a Word file.
- Python
from spire.doc import *
from spire.doc.common import *
from PIL import Image
import io
# Create a Document object
doc = Document()
# Add a section
section = doc.AddSection()
# Set margins to 0
section.PageSetup.Margins.All = 0.0
# Load a TIFF image
tiff_image = Image.open("C:\\Users\\Administrator\\Desktop\\TIFF.tiff")
# Iterate through the frames in it
for i in range(tiff_image.n_frames):
# Go to the current frame
tiff_image.seek(i)
# Extract the image of the current frame
frame_image = tiff_image.copy()
# Save the image to a PNG file
frame_image.save(f"temp/output_frame_{i}.png")
# Add a paragraph
paragraph = section.AddParagraph()
# Append image to the paragraph
image = paragraph.AppendPicture(f"temp/output_frame_{i}.png")
# Get image width and height
width = image.Width
height = image.Height
# Set the page size to be the same as the image size
section.PageSetup.PageSize = SizeF(width, height)
# Save the document to a Word file
doc.SaveToFile("Output/ToWord.docx",FileFormat.Docx2019)
# Dispose resources
doc.Dispose()
Get a Free License
To fully experience the capabilities of Spire.Doc for Python without any evaluation limitations, you can request a free 30-day trial license.
Python: Export and Import Data Between Excel Files and Databases
2024-06-28 01:05:53 Written by KoohjiThe ability to export data from Excel files to databases, and vice versa, has become a crucial functionality in data management. Exporting data from an Excel file to a database offers scalability and enhanced security, enabling efficient handling of large datasets and facilitating collaboration with concurrency control. Conversely, importing data from databases into Excel provides the familiar spreadsheet interface for data analysis, visualization, and presentation, making complex information accessible to users. With the powerful Python language, developers can easily automate the transfer of data between databases and spreadsheets.
This article demonstrates how to use Spire.XLS for Python to export data from a database to Excel files and import data from Excel files to a database with simple Python code, taking the SQLite database as the example.
- Export Data from a Database to an Excel File with Python
- Export Data from an Excel Worksheet to a Database with Python
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to: How to Install Spire.XLS for Python on Windows
Export Data from a Database to an Excel File with Python
Spire.XLS for Python offers a range of classes, methods, and properties to create, read, and edit Excel workbooks. Developers can use the sqlite3 module from the Python standard library to read data from databases and utilize Spire.XLS for Python to create Excel files and write data into them, thereby enabling the export of database data to Excel worksheets.
The detailed steps are as follows:
- Connect to a database.
- Create an instance of Workbook class and clear the default worksheets.
- Iterate through the tables in the database to get all the column names and the data in the columns.
- Create a worksheet for each table using Workbook.Worksheets.Add() method, write the column names to the header row of the worksheet, and then write data to other rows through Worksheet.Range[].Value property.
- Format the worksheet.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
import sqlite3
# Connect to database
conn = sqlite3.connect("Sales Data.db")
cursor = conn.cursor()
# Get all the table names in the database
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tableNames = [name[0] for name in cursor.fetchall()]
# Create an Excel file
workbook = Workbook()
workbook.Worksheets.Clear()
# Iterate through each table in the database
for tableName in tableNames:
# Get the column names of the table
cursor.execute(f"PRAGMA table_info('{tableName}')")
columnsInfo = cursor.fetchall()
columnNames = [columnInfo[1] for columnInfo in columnsInfo]
# Get the data of the table
cursor.execute(f"SELECT * FROM {tableName}")
rows = cursor.fetchall()
# Create a worksheet
sheet = workbook.Worksheets.Add(tableName)
# Write the header to the worksheet
for i in range(len(columnNames)):
sheet.Range[1, i + 1].Value = columnNames[i]
# Write the data to the worksheet
for j in range(1, len(rows)):
column = rows[j]
for k in range(len(column)):
sheet.Range[j + 1, k + 1].Value = column[k]
# Format the worksheet
sheet.AllocatedRange.Style.Font.FontName = "Times New Roman"
sheet.AllocatedRange.Style.Font.Size = 12.0
sheet.AllocatedRange.AutoFitRows()
sheet.AllocatedRange.AutoFitColumns()
# Save the Excel file
workbook.SaveToFile("output/DataBaseToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
conn.close()
Export Data from an Excel Worksheet to a Database with Python
Spire.XLS for Python can also assist developers in reading various types of data from Excel files, and then developers can use sqlite3 to write the data to a database. Below are the detailed steps:
- Create an instance of Workbook class.
- Load an Excel workbook using Workbook.LoadFromFile() method.
- Connect to a database.
- Iterate through the worksheets in the workbook.
- Get a worksheet using Workbook.Worksheets.get_Item() method and get the name of the worksheet using Worksheet.Name property.
- Get the data in the header row through Worksheet.Range[].Value property.
- Create a table in the database with the worksheet name as the table name and create columns in the table with the header row data as the column names.
- Get the values from the worksheet cells through Worksheet.Range[].Value property and write them to the corresponding position in the database table.
- Commit the changes and close the connection.
- Python
from spire.xls import *
from spire.xls.common import *
import sqlite3
# Create an instance of Workbook
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Connect to database
conn = sqlite3.connect("output/Report.db")
cursor = conn.cursor()
for s in range(workbook.Worksheets.Count):
# Get a worksheet
sheet = workbook.Worksheets.get_Item(s)
# Get the sheet name
sheetName = sheet.Name
sheetName = sheetName.replace(" ", "")
# Get the data in the header row
header = []
for i in range(sheet.AllocatedRange.ColumnCount):
headerValue = sheet.Range[1, i + 1].Value
headerValue = headerValue.replace(" ", "")
header.append(headerValue)
# Create a database table
createTableSql = f"CREATE TABLE IF NOT EXISTS {sheetName} ({', '.join([f'{header[i]} TEXT' for i in range(len(header))])})"
cursor.execute(createTableSql)
# Insert data into the database table
for row in range(1, sheet.AllocatedRange.RowCount):
data = []
for col in range(sheet.AllocatedRange.ColumnCount):
# Get the cell value
value = sheet.Range[row + 1, col + 1].Value
data.append(value)
# Insert the cell values into the database table
insertSql = f"INSERT INTO {sheetName} ({', '.join(header)}) VALUES ({', '.join(['?' for _ in data])})"
cursor.execute(insertSql, data)
# Commit the changes and close the connection
conn.commit()
conn.close()
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Working with large Excel workbooks can sometimes become unwieldy, especially when you need to share or distribute parts of the data independently. In these cases, it can be helpful to split your Excel file into multiple smaller files. This not only makes the individual files more manageable, but also allows you to better organize and share your data. In this article, we will demonstrate how to split an Excel file in Python using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Split Excel by Worksheets in Python
If your Excel file contains multiple worksheets, you can easily split each sheet into an Excel file by using the Workbook.Worksheets.AddCopy() method provided by Spire.XLS for Python. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Iterate through the worksheets in the Excel file.
- For each worksheet, create a new Workbook object for it.
- Remove the default worksheets in the new workbook using Workbook.Worksheets.Clear() method.
- Copy the worksheet to the new workbook using Workbook.Worksheets.AddCopy() method.
- Save the new workbook to an Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Specify the folder path for the generated Excel files
folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\"
# Iterate through all worksheets in the Excel file
for worksheet in workbook.Worksheets:
# For each worksheet, create a new Workbook object
newWorkbook = Workbook()
# Remove the worksheets from the new workbook
newWorkbook.Worksheets.Clear()
# Copy the worksheet from the Excel file to the new workbook
newWorkbook.Worksheets.AddCopy(worksheet)
# Save the new workbook to the specified folder
newWorkbook.SaveToFile(folderPath + worksheet.Name + ".xlsx", FileFormat.Version2016)
workbook.Dispose()
Split Excel by Rows in Python
If you have a large worksheet where a specific number of rows represent a unique record or entry, you can extract these individual rows or records into separate Excel files for focused data analysis using the Worksheet.CopyRow() method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the original worksheet where you want to copy rows from using Workbook.Worksheets[index] property.
- Create a new Workbook object and remove the default worksheets from the new workbook using Workbook.Worksheets.Clear() method.
- Add a new Worksheet to the new workbook using Workbook.Worksheets.Add() method.
- Copy specific rows from the original worksheet to the new worksheet using Worksheet.CopyRow() method.
- Copy Column widths from the original worksheet to the new worksheet.
- Save the new workbook to an Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object and load an Excel file
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
# Get the original (the 1st) worksheet
worksheet = workbook.Worksheets[0]
# Get the header row
header = worksheet.Rows[0]
# Specify the folder path for the generated Excel files
folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\"
# Create a new Workbook object
newWorkbook1 = Workbook()
# Remove the default worksheets
newWorkbook1.Worksheets.Clear()
# Add a new worksheet
newWorksheet1 = newWorkbook1.Worksheets.Add("Sheet1")
# Copy rows 1-5 from the original worksheet to the new worksheet
for i in range(1, 6):
worksheet.CopyRow(worksheet.Rows[i - 1], newWorksheet1, newWorksheet1.LastDataRow + 1, CopyRangeOptions.All)
# Copy column widths from the original worksheet to the new worksheet
for i in range(worksheet.Columns.Count):
newWorksheet1.SetColumnWidth(i + 1, worksheet.GetColumnWidth(i + 1))
# Save the new workbook to the specified folder
newWorkbook1.SaveToFile(folderPath + "Rows1-5.xlsx", FileFormat.Version2016)
newWorkbook1.Dispose()
# Create a new Workbook object
newWorkbook2 = Workbook()
# Remove the default worksheets
newWorkbook2.Worksheets.Clear()
# Add a new worksheet
newWorksheet2 = newWorkbook2.Worksheets.Add("Sheet1")
# Copy header row from the original worksheet to the new worksheet
worksheet.CopyRow(worksheet.Rows[0], newWorksheet2, newWorksheet2.LastDataRow + 1, CopyRangeOptions.All)
# Copy rows 6-10 from the original worksheet to the new worksheet
for i in range(6, 11):
worksheet.CopyRow(worksheet.Rows[i - 1], newWorksheet2, newWorksheet2.LastDataRow + 1, CopyRangeOptions.All)
# Copy column widths from the original worksheet to the new worksheet
for i in range(worksheet.Columns.Count):
newWorksheet2.SetColumnWidth(i + 1, worksheet.GetColumnWidth(i + 1))
# Save the new workbook to the specified folder
newWorkbook2.SaveToFile(folderPath + "Rows6-10.xlsx", FileFormat.Version2016)
newWorkbook2.Dispose()
Split Excel by Columns in Python
In addition to splitting by rows, you can also split an Excel file by columns using the Worksheet.CopyColumn() method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the original worksheet where you want to copy columns from using Workbook.Worksheets[index] property.
- Create a new Workbook object and remove the default worksheets from the new workbook using Workbook.Worksheets.Clear() method.
- Add a new Worksheet to the new workbook using Workbook.Worksheets.Add() method.
- Copy specific columns from the original worksheet to the new worksheet using Worksheet.CopyColumn() method.
- Copy row heights from the original worksheet to the new worksheet.
- Save the new workbook to an Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object and load an Excel file
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
# Get the original (the 1st) worksheet
worksheet = workbook.Worksheets[0]
# Specify the folder path for the generated Excel files
folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\"
# Create a new Workbook object
newWorkbook1 = Workbook()
# Remove the default worksheets
newWorkbook1.Worksheets.Clear()
# Add a new worksheet
newWorksheet1 = newWorkbook1.Worksheets.Add("Sheet1")
# Copy columns 1-2 from the original worksheet to the new worksheet
for i in range(1, 3):
worksheet.CopyColumn(worksheet.Columns[i-1], newWorksheet1, newWorksheet1.LastDataColumn + 1, CopyRangeOptions.All)
# Copy row heights from the original worksheet to the new worksheet
for i in range(worksheet.Rows.Count):
newWorksheet1.SetRowHeight(i + 1, worksheet.GetRowHeight(i + 1))
# Save the new workbook to the specified folder
newWorkbook1.SaveToFile(folderPath + "Columns1-2.xlsx", FileFormat.Version2016)
newWorkbook1.Dispose()
# Create a new Workbook object
newWorkbook2 = Workbook()
# Remove the default worksheets
newWorkbook2.Worksheets.Clear()
# Add a new worksheet
newWorksheet2 = newWorkbook2.Worksheets.Add("Sheet1")
# Copy columns 3-5 from the original worksheet to the new worksheet
for i in range(3, 6):
worksheet.CopyColumn(worksheet.Columns[i-1], newWorksheet2, newWorksheet2.LastDataColumn + 1, CopyRangeOptions.All)
# Copy row heights from the original worksheet to the new worksheet
for i in range(worksheet.Rows.Count):
newWorksheet2.SetRowHeight(i + 1, worksheet.GetRowHeight(i + 1))
# Save the new workbook to the specified folder
newWorkbook2.SaveToFile(folderPath + "Columns3-5.xlsx", FileFormat.Version2016)
newWorkbook2.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
