Tables in Word documents can sometimes disrupt the flow of text or the visual balance of a page. Removing these tables can help in creating a more aesthetically pleasing document, which is crucial for reports, presentations, or publications where appearance is important. In this article, you will learn how to remove tables from a Word document in Python using Spire.Doc for Python.

• Remove a Specified Table in Word in Python
• Remove All Tables in Word in Python

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.Doc

If you are unsure how to install, please refer to this tutorial: How to Install Spire.Doc for Python on Windows

Remove a Specified Table in Word in Python

Spire.Doc for Python provides the Section.Tables.RemoveAt(int index) method to delete a specified table in a Word document by index. The following are the detailed steps.

• Create a Document instance.
• Load a Word document using Document.LoadFromFile() method.
• Get a specified section using Document.Sections[] property.
• Delete a specified table by index using Section.Tables.RemoveAt() method.
• Save the result document using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

inputFile = "Tables.docx"
outputFile = "RemoveTable.docx"

# Create a Document instance
doc = Document()

# Load a Word document
doc.LoadFromFile(inputFile)

# Get the first section in the document  
sec = doc.Sections[0]

# Remove the first table in the section
sec.Tables.RemoveAt(0)

# Save the result document
doc.SaveToFile(outputFile, FileFormat.Docx)
doc.Close()

Remove All Tables in Word in Python

To delete all tables from a Word document, you need to iterate through all sections in the document, then iterate through all tables in each section and remove them through the Section.Tables.Remove() method. The following are the detailed steps.

• Create a Document instance.
• Load a Word document using Document.LoadFromFile() method.
• Iterate through all sections in the document.
• Iterate through all tables in each section.
• Delete the tables using Section.Tables.Remove() method.
• Save the result document using Document.SaveToFile() method.

from spire.doc import *
from spire.doc.common import *

inputFile = "Tables.docx"
outputFile = "RemoveAllTables.docx"

# Create a Document instance
doc = Document()

# Load a Word document
doc.LoadFromFile(inputFile)

# Iterate through all sections in the document
for i in range(doc.Sections.Count):
    sec = doc.Sections.get_Item(i)

    # Iterate through all tables in each section
    for j in range(sec.Tables.Count):
        table = sec.Tables.get_Item(j)
        # Remove the table
        sec.Tables.Remove(table)

# Save the result document
doc.SaveToFile(outputFile, FileFormat.Docx)
doc.Close()

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.

Markdown has become a popular choice for writing structured text due to its simplicity and readability, making it widely used for documentation, README files, and note-taking. However, sometimes there arises a need to present this content in a more universal and polished format, such as PDF, which is compatible across various devices and platforms without formatting inconsistencies. Converting Markdown files to PDF documents not only enhances portability but also adds a professional touch, enabling easier distribution for reports, manuals, or sharing content with non-technical audiences who may not be familiar with Markdown syntax.

This article will demonstrate how to convert Markdown files to PDF documents using Spire.Doc for Python to automate the conversion process.

• Convert Markdown Files to PDF Documents with Python
• Convert Markdown to PDF and Customize Page Settings

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.Doc

If you are unsure how to install, please refer to: How to Install Spire.Doc for Python on Windows

Convert Markdown Files to PDF Documents with Python

With Spire.Doc for Python, developers can load Markdown files using Document.LoadFromFile(string: fileName, FileFormat.Markdown) method, and then save the files to PDF documents using Document.SaveToFile(string: fileName, FileFormat.PDF) method. Besides, developers can also convert Markdown files to HTML, XPS, and SVG formats by specifying enumeration items of the FileFormat enumeration class.

The detailed steps for converting a Markdown file to a PDF document are as follows:

• Create an instance of Document class.
• Load a Markdown file using Document.LoadFromFile(string: fileName, FileFormat.Markdown) method.
• Convert the Markdown file to a PDF document and save it using Document.SaveToFile(string: fileName, FileFormat.PDF) method.

from spire.doc import *
from spire.doc.common import *

# Create an object of Document class
doc = Document()

# Load a Markdown file
doc.LoadFromFile("Sample.md", FileFormat.Markdown)

# Save the file to a PDF document
doc.SaveToFile("output/MarkdownToPDF.pdf", FileFormat.PDF)

doc.Dispose()

Convert Markdown to PDF and Customize Page Settings

Spire.Doc for Python supports performing basic page setup before converting Markdown files to formats like PDF, allowing for control over the appearance of the converted document.

The detailed steps to convert a Markdown file to a PDF document and customize the page settings are as follows:

• Create an instance of Document class.
• Load a Markdown file using Document.LoadFromFile(string: fileName, FileFormat.Markdown) method.
• Get the default section using Document.Sections.get_Item() method.
• Get the page settings through Section.PageSetup property and set the page size, orientation, and margins through properties under PageSetup class.
• Convert the Markdown file to a PDF document and save it using Document.SaveToFile(string: fileName, FileFormat.PDF) method.

from spire.doc import *
from spire.doc.common import *

# Create an instance of Document class
doc = Document()
# Load a Word document
doc.LoadFromFile("Sample.md", FileFormat.Markdown)

# Get the default section
section = doc.Sections.get_Item(0)

# Get the page settings
pageSetup = section.PageSetup

# Customize the page settings
pageSetup.PageSize = PageSize.A4()
pageSetup.Orientation = PageOrientation.Landscape
pageSetup.Margins.All = 50

# Save the Markdown document to a PDF file
doc.SaveToFile("output/MarkdownToPDFPageSetup.pdf", FileFormat.PDF)

doc.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.

The advantages of using an online document editor over a traditional desktop application include enhanced accessibility, seamless collaboration, automatic version control, cross-platform compatibility, and reduced hardware requirements. These features make online document editors a versatile and efficient choice for users who require the ability to access, edit, and share documents from anywhere.

This article demonstrates how to create and edit MS Word, Excel and PowerPoint documents online using the Spire.Cloud.Office document editor library.

• Spire.Cloud.Office Document Editor
• Create a New Document
• Edit an Existing Document
• Co-Edit a Document

Spire.Cloud.Office Document Editor

Spire.Cloud.Office is a feature-rich HTML-5 based document editor component that can be easily integrated into web applications. With the document editor component, your end-users can view, create, edit, and collaborate on diverse document types within a web browser.

To utilize the services offered by Spire.Cloud.Office, you will need to first install it on your system.

• Install Spire.Cloud.Office for .NET on Windows
• Install Spire.Cloud.Office for Linux on Ubuntu
• Install Spire.Cloud.Office for Linux on CentOS

After the installation is complete, you can integrate Spire.Cloud.Office editor in your own web application or visit the example application hosted on port 3000 to explore the editor’s functionality.

The example page offers options to upload existing documents or create new ones. Spire.Office.Cloud supports loading DOC/DOCX, XLS/XLSX, and PPT/PPTX files, and exporting files to DOCX, XLSX, and PPTX formats.

Create a New Document

With the "Create Document", "Create Spreadsheet", and "Create Presentation" buttons on the example page, users can create a new Word document, a new Excel spreadsheet, and a new PowerPoint presentation, respectively.

Upon clicking "Create Document", a new Word document named "new.docx" will be generated, and the editor will launch with the blank document ready for editing.

Once you've finished editing the document, click "File" on the menu and you'll get the options to download the file and save it to your local folder in the desired format.

Alternatively, you can click "Save" to preserve the changes made to the "new.docx" document, which can be found on the example page.

Edit an Existing Document

On the example page, click the "Upload File" button to load an existing document for editing.

Once the file has been uploaded, it will appear on the example page. To open the document in the editor, click the computer icon in the "Editors" section.

Use the editing tools provided in the document editor to make any desired modifications to the file. Once you have finished making changes, save the updated document by clicking "File" and then selecting "Save".

Co-Edit a Document

Spire.Cloud.Office's real-time collaboration features enable multiple users to work on the same document simultaneously. Two different collaborative editing modes are available under the "Review" tab - "Editing Mode".

• Fast Mode: All editors can see the changes made to the document in real-time as they are being typed or made.
• Strict Mode: Changes made by editors are protected and only become visible to other editors after the document has been explicitly saved.

By default, the Fast mode is enabled.

When a document is being collaboratively edited by multiple users, any changes made by one editor are instantly reflected in the document interface for all other editors in real-time.

Adding and removing watermarks in PDF documents play a crucial role in document management, copyright protection, and information security. A watermark can serve as a visual marker, such as a company logo, copyright notice, or the word "Confidential", indicating the source, status, or ownership of the document.

Spire.PDF provides a method for adding watermarks by embedding watermark annotations within the PDF document. This approach affords the flexibility to remove the watermark post-insertion, offering users greater control and options. This article will introduce how to add or remove watermark annotations in PDF documents using Spire.PDF for Java in Java projects.

• Add Watermark Annotations to PDF in Java
• Remove Watermark Annotations from PDF in Java

Install Spire.PDF for Java

First of all, you're required to add the Spire.Pdf.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.pdf</artifactId>
        <version>10.7.3</version>
    </dependency>
</dependencies>
    

Add Watermark Annotations to PDF in Java

Spire.PDF provides a method to add watermark annotations to PDF pages using the PdfWatermarkAnnotation object. Notably, watermarks added by this method can be easily removed later. Here are the key steps involved:

• Initialize an instance of the PdfDocument class.
• Load a PDF document using the PdfDocument.loadFromFile() method.
• Create a PdfTrueTypeFont font object to draw the watermark text.
• Create a Rectangle2D type object to define the boundary of the page.
• Use a for loop to iterate over all PdfPageBase objects in the PDF document.
• Create a PdfTemplate object for drawing the watermark, setting its size to match the current page.
• Call a custom insertWatermark() method to draw the watermark content onto the PdfTemplate object.
• Create a PdfWatermarkAnnotation object and define the position where the watermark should be placed.
• Create a PdfAppearance object to configure the visual effects of the watermark.
• Use the PdfAppearance.setNormal(PdfTemplate) method to associate the PdfTemplate object with the PdfAppearance object.
• Use the PdfWatermarkAnnotation.setAppearance(PdfAppearance) method to associate the PdfAppearance object with the PdfWatermarkAnnotation object.
• Add the watermark annotation to the page by calling PdfPageBase.getAnnotationsWidget().add(PdfWatermarkAnnotation).
• Save the changes to a file using the PdfDocument.saveToFile() method.

import com.spire.pdf.*;
import com.spire.pdf.annotations.*;
import com.spire.pdf.annotations.appearance.*;
import com.spire.pdf.graphics.*;
import java.awt.*;
import java.awt.geom.*;

public class AddWatermarkInPDF {
    public static void main(String[] args) {
        // Create a PdfDocument object
        PdfDocument pdfDocument = new PdfDocument();

        // Load the PDF document from a file
        pdfDocument.loadFromFile("Sample1.pdf");

        // Set the font style
        Font font = new Font("Arial", Font.PLAIN, 22);

        // Create a PdfTrueTypeFont object for subsequent text rendering
        PdfTrueTypeFont trueTypeFont = new PdfTrueTypeFont(font);

        // Get the page object
        PdfPageBase page;

        // Define the watermark text
        String watermarkAnnotationText = "ID_0";

        // Create a size object
        Dimension2D dimension2D = new Dimension();

        // Create a rectangle object
        Rectangle2D loRect = new Rectangle2D.Float();

        // Iterate through each page in the PDF
        for (int i = 0; i < pdfDocument.getPages().getCount(); i++) {
            // Get the current page
            page = pdfDocument.getPages().get(i);

            // Set the size object to the size of the current page
            dimension2D.setSize(page.getClientSize().getWidth(), page.getClientSize().getHeight());

            // Set the rectangle object frame, which is the entire page range
            loRect.setFrame(new Point2D.Float(0, 0), dimension2D);

            // Create a PdfTemplate object to draw the watermark
            PdfTemplate template = new PdfTemplate(page.getClientSize().getWidth(), page.getClientSize().getHeight());

            // Insert the watermark
            insertWatermark(template, trueTypeFont, "Non Editable");

            // Create a PdfWatermarkAnnotation object to define the watermark position
            PdfWatermarkAnnotation watermarkAnnotation = new PdfWatermarkAnnotation(loRect);

            // Create a PdfAppearance object to set the watermark appearance
            PdfAppearance appearance = new PdfAppearance(watermarkAnnotation);

            // Set the normal state template of the watermark
            appearance.setNormal(template);

            // Set the appearance to the watermark object
            watermarkAnnotation.setAppearance(appearance);

            // Set the watermark text
            watermarkAnnotation.setText(watermarkAnnotationText);

            // Set the watermark print matrix to control the watermark's position and size
            watermarkAnnotation.getFixedPrint().setMatrix(new float[]{1, 0, 0, 1, 0, 0});

            // Set the horizontal offset
            watermarkAnnotation.getFixedPrint().setHorizontalTranslation(0.5f);

            // Set the vertical offset
            watermarkAnnotation.getFixedPrint().setVerticalTranslation(0.5f);

            // Add the watermark to the page's annotation widget
            page.getAnnotationsWidget().add(watermarkAnnotation);
        }

        // Save the PDF document to a file
        pdfDocument.saveToFile("AddWatermark.pdf");

        // Close and release the PDF document resources
        pdfDocument.dispose();
    }

    static void insertWatermark(PdfTemplate template, PdfTrueTypeFont font, String watermark) {
        // Create a Dimension2D object to set the size of the watermark
        Dimension2D dimension2D = new Dimension();

        // Set the size of the watermark to half the width and one third the height of the template
        dimension2D.setSize(template.getWidth() / 2, template.getHeight() / 3);

        // Create a PdfTilingBrush object for repeating pattern fill of the watermark
        PdfTilingBrush brush = new PdfTilingBrush(dimension2D);

        // Set the transparency of the watermark to 0.3
        brush.getGraphics().setTransparency(0.3F);

        // Start a group of graphic state saves
        brush.getGraphics().save();

        // Translate the graphics context so its center aligns with the center of the watermark tile
        brush.getGraphics().translateTransform((float) brush.getSize().getWidth() / 2, (float) brush.getSize().getHeight() / 2);

        // Rotate the graphics context to tilt the watermark at 45 degrees
        brush.getGraphics().rotateTransform(-45);

        // Draw the watermark text in the graphics context using the specified font, color, and centered alignment
        brush.getGraphics().drawString(watermark, font, PdfBrushes.getGray(), 0, 0, new PdfStringFormat(PdfTextAlignment.Center));

        // End the group of graphic state saves and restore
        brush.getGraphics().restore();

        // Reset the watermark transparency to 1, i.e., completely opaque
        brush.getGraphics().setTransparency(1);

        // Create a Rectangle2D object to define the area for filling the watermark
        Rectangle2D loRect = new Rectangle2D.Float();

        // Set the fill area for the watermark to cover the entire size of the template
        loRect.setFrame(new Point2D.Float(0, 0), template.getSize());

        // Draw the watermark on the template using the watermark tile
        template.getGraphics().drawRectangle(brush, loRect);
    }
}

Remove Watermark Annotations from PDF in Java

Spire.PDF can remove watermark annotations added to PDF pages via the PdfWatermarkAnnotation object. Here are the detailed steps:

• Initialize an instance of the PdfDocument class.
• Load a PDF document using the PdfDocument.loadFromFile() method.
• Iterate over every PdfPageBase object in the PDF document using a for loop.
• Retrieve all annotations on the current page using the PdfPageBase.getAnnotationsWidget() method.
• Again, use a for loop to iterate over every annotation object on the current page, filtering out annotations of type PdfWatermarkAnnotationWidget.
• Determine the target watermark annotation by invoking the PdfWatermarkAnnotationWidget.getText() method and perform the deletion operation.
• Save the changes to a file using the PdfDocument.saveToFile() method.

import com.spire.pdf.*;
import com.spire.pdf.annotations.*;

public class RemoveWatermarkFromPDF {
    public static void main(String[] args) {
        // Create a PdfDocument object
        PdfDocument pdfDocument = new PdfDocument();

        // Load the PDF document from a file
        pdfDocument.loadFromFile("Sample2.pdf");

        // Define a string ID to match and remove a specific watermark
        String id = "ID_0";

        // Iterate through every page in the PDF document
        for (int i = 0; i < pdfDocument.getPages().getCount(); i++) {
            // Get all annotations on the current page
            PdfAnnotationCollection annotationWidget = pdfDocument.getPages().get(i).getAnnotationsWidget();

            // Iterate through all annotations on the current page
            for (int j = 0; j < annotationWidget.getCount(); j++) {
                // Check if the current annotation is a watermark annotation
                if (annotationWidget.get(j) instanceof PdfWatermarkAnnotationWidget) {
                    // If the watermark text equals the ID, remove the watermark
                    if (annotationWidget.get(j).getText().equals(id)) {
                        annotationWidget.remove(annotationWidget.get(j));
                    }
                }
            }
        }

        // Save the modified PDF document to a new file
        pdfDocument.saveToFile("RemoveWatermark.pdf");

        // Dispose of the PdfDocument object resources
        pdfDocument.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.

Markdown is a lightweight markup language that is becoming increasingly popular for writing content on the web. It offers a simple and human-readable syntax for formatting text, adding links, images, lists, and more. Many websites and content management systems support Markdown, as it can be easily converted to HTML. On the other hand, Microsoft Word is a widely used word-processing software that utilizes its own proprietary file format. While Word offers robust formatting options, its files are not always compatible with other platforms or content management systems.

In certain scenarios, it is useful to convert between Word and Markdown file formats. It allows you to take advantage of Word's advanced editing tools while also being able to publish your content in a web-friendly Markdown format. In this article, we will demonstrate how to convert Markdown to Word DOC or DOCX and convert Word DOC or DOCX to Markdown in Python using Spire.Doc for Python.

• Convert Markdown to Word in Python
• Convert Word to Markdown in Python

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.Doc

If you are unsure how to install, please refer to this tutorial: How to Install Spire.Doc for Python on Windows

Convert Markdown to Word in Python

You can load a Markdown file using the Document.LoadFromFile(fileName, FileFormat.Markdown) method and then convert it to Word DOC or DOCX format using the Document.SaveToFile(fileName, FileFormat.Doc) or Document.SaveToFile(fileName, FileFormat.Docx) method. The detailed steps are as follows.

• Create an object of the Document class.
• Load a Markdown file using the Document.LoadFromFile(fileName, FileFormat.Markdown) method.
• Save the Markdown file to a Word DOC or DOCX file using Document.SaveToFile(fileName, FileFormat.Doc) or Document.SaveToFile(fileName, FileFormat.Docx) method.

from spire.doc import *
from spire.doc.common import *

# Create an object of the Document class
document = Document()
# Load a Markdown file
document.LoadFromFile("input.md")

# Save the Markdown file to a Word DOCX file
document.SaveToFile("MdToDocx.docx", FileFormat.Docx)
# Save the Markdown file to a Word DOC file
document.SaveToFile("MdToDoc.doc", FileFormat.Doc)
document.Close()

Convert Word to Markdown in Python

You are also able to convert a Word DOC or DOCX file to Markdown format using the Document.SaveToFile(fileName, FileFormat.Markdown) method. The detailed steps are as follows.

• Create an object of the Document class.
• Load a Word DOC or DOCX file using the Document.LoadFromFile(fileName) method.
• Save the Word DOC or DOCX file to a Markdown file using Document.SaveToFile(fileName, FileFormat.Markdown) method.

from spire.doc import *
from spire.doc.common import *

# Create an object of the Document class
document = Document()
# Load a Word DOCX file
document.LoadFromFile("input.docx")
# Or load a Word DOC file
#document.LoadFromFile("input.doc")

# Save the Word file to a Markdown file
document.SaveToFile("WordToMarkdown.md", FileFormat.Markdown)
document.Close()

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.

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.

• Convert Word to TIFF in Python
• Convert TIFF to Word in Python

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.

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.

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()

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.

The 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.

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.

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.