Conversion (13)
XML is widely used for storing and exchanging data due to its flexibility and self-descriptive nature. However, many users find it challenging to work with XML files directly. Excel, on the other hand, is a familiar and user-friendly tool for data analysis. Converting XML to Excel not only makes data more accessible but also enhances its usability for various applications.
In this article, you will learn how to convert XML to Excel as well as XML to PDF 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
Understanding XML Structure: Elements, Attributes, and Data
Before converting XML to Excel, it's crucial to understand the structure of XML files. XML is a markup language that uses tags to define elements, attributes, and data. Here’s a breakdown of these components:
- Elements: These are the building blocks of XML. They are defined by start and end tags and can contain data or other elements.
<person>
<name>John Doe</name>
<age>30</age>
</person>
- Attributes: These provide additional information about elements. They are specified within the start tag of an element.
<person id="1">
<name>John Doe</name>
<age>30</age>
</person>
- Data: This is the content enclosed within the start and end tags of an element.
Understanding these components will help you map XML data to Excel effectively.
Convert XML to Excel in Python
To load an XML file into Python, you can use the xml.etree.ElementTree library, which is included in Python's standard library. This library provides methods to navigate and manipulate the XML tree. Here is an example:
import xml.etree.ElementTree as ET
# Load the XML file
tree = ET.parse('data.xml')
root = tree.getroot()
# Iterate through elements
for person in root.findall('person'):
name = person.find('name').text
age = person.find('age').text
After parsing the XML data, the next step is to map it to an Excel worksheet. You can utilize Spire.XLS for Python to create a new workbook, input data into specific cells, and apply styles and formatting to the worksheet. These formatting options include auto-fitting column widths, adjusting text alignment and making the header bold.
To convert XML to Excel in Python, follow these steps:
- Use the xml.etree.ElementTree library to retrieve data from an XML file.
- Create a Workbook object.
- Add a worksheet using the Workbook.Worksheets.Add() method.
- Write data extracted from the XML file into the cells of the worksheet using the Worksheet.SetValue() method.
- Apply styles and formatting to enhance the worksheet appearance.
- Save the workbook to an Excel file.
The following code provides a more intelligent and advanced way to read data from XML and import it into an Excel file.
- Python
from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET
# Create a Workbook object
workbook = Workbook()
# Remove default worksheets
workbook.Worksheets.Clear()
# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")
# Load an XML file
xml_tree = ET.parse("C:\\Users\\Administrator\\Desktop\\Books.xml")
# Get the root element of the XML tree
xml_root = xml_tree.getroot()
# Get the first the "book" element
first_book = xml_root.find("book")
# Extract header information and convert it into a list
header = list(first_book.iter())[1:]
# Write header to Excel
for col_index, header_node in enumerate(header, start=1):
header_text = header_node.tag
worksheet.SetValue(1, col_index, header_text)
# Write other data to Excel by iterating over each book element and each data node within it
row_index = 2
for book in xml_root.iter("book"):
for col_index, data_node in enumerate(list(book.iter())[1:], start=1):
value = data_node.text
header_text = list(header[col_index - 1].iter())[0].tag
worksheet.SetValue(row_index, col_index, value)
row_index += 1
# Set column width
worksheet.AllocatedRange.AutoFitColumns()
# Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left
# Set font style
worksheet.Range["A1:F1"].Style.Font.IsBold = True
# Save the workbook to an Excel file
workbook.SaveToFile("output/XmlToExcel.xlsx")
# Dispose resources
workbook.Dispose()
Convert XML to PDF in Python
The previous example successfully imports data from an XML file into an Excel worksheet. You can convert this worksheet to a PDF using the Worksheet.SaveToPdf() method. To create a well-structured PDF, consider adjusting page layout settings, such as margins and gridline preservation, during the conversion process.
The steps to convert XML to PDF using Python are as follows:
- Use the xml.etree.ElementTree library to retrieve data from an XML file.
- Create a Workbook object.
- Add a worksheet using the Workbook.Worksheets.Add() method.
- Write data extracted from the XML file into the cells of the worksheet using the Worksheet.SetValue() method.
- Apply styles and formatting to enhance the worksheet appearance.
- Configure page settings using the properties under the PageSetup object, which is returned by the Worksheet.PageSetup property.
- Save the worksheet as a PDF file using the Worksheet.SaveToPdf() method.
The following code snippet demonstrates how to import data from XML into a worksheet and save that worksheet as a PDF file.
- Python
from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET
# Create a Workbook object
workbook = Workbook()
# Remove default worksheets
workbook.Worksheets.Clear()
# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")
# Load an XML file
xml_tree = ET.parse("C:\\Users\\Administrator\\Desktop\\Books.xml")
# Get the root element of the XML tree
xml_root = xml_tree.getroot()
# Get the first the "book" element
first_book = xml_root.find("book")
# Extract header information and convert it into a list
header = list(first_book.iter())[1:]
# Write header to Excel
for col_index, header_node in enumerate(header, start=1):
header_text = header_node.tag
worksheet.SetValue(1, col_index, header_text)
# Write other data to Excel by iterating over each book element and each data node within it
row_index = 2
for book in xml_root.iter("book"):
for col_index, data_node in enumerate(list(book.iter())[1:], start=1):
value = data_node.text
header_text = list(header[col_index - 1].iter())[0].tag
worksheet.SetValue(row_index, col_index, value)
row_index += 1
# Set column width
worksheet.AllocatedRange.AutoFitColumns()
# Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left
# Set font style
worksheet.Range["A1:F1"].Style.Font.IsBold = True
# Fit worksheet on one page
workbook.ConverterSetting.SheetFitToPage = True
# Get the PageSetup object
pageSetup = worksheet.PageSetup
# Set page margins
pageSetup.TopMargin = 0.3
pageSetup.BottomMargin = 0.3
pageSetup.LeftMargin = 0.3
pageSetup.RightMargin = 0.3
# Preserve gridlines
pageSetup.IsPrintGridlines = True
# Save the worksheet to a PDF file
worksheet.SaveToPdf("output/XmlToPdf.pdf")
# Dispose resources
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.
Python: Convert Excel Data to Word Table with Formatting
2024-11-18 01:07:09 Written by support iceblueExcel is ideal for data calculations, analysis, and organization, while Word shines at creating polished, well-formatted documents and reports. Transferring data from Excel to Word is often necessary for professionals preparing reports or presentations, as it allows for advanced formatting options that enhance readability and create a more professional look. In this guide, you will learn how to convert data in an Excel sheet to a Word table with formatting in Python using Spire.Office for Python.
Install Spire.Office for Python
This scenario requires Spire.Office for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.Office
Convert Excel Data to Word Table with Formatting in Python
This process uses two libraries in the Spire.Office for Python package. They’re Spire.XLS for Python and Spire.Doc for Python. The former is used to read data and formatting from an Excel worksheet, and the latter is used to create a Word document and write data, including formatting, into a table. To make this code example easy to understand, we have defined the following two custom methods that handle specific tasks:
- MergeCells() - Merge the corresponding cells in the Word table based on the merged cells in the Excel sheet.
- CopyStyle() - Copy various cell styles from the Excel worksheet to the Word table, including font style, background color, and text alignment.
The following steps demonstrate how to convert data from an Excel sheet to a Word table with formatting using Spire.Office for Python.
- Create an object of the Workbook class and load a sample Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet through the Workbook.Worksheets[index] property.
- Create a new Word document using the Document class, and add a section to it.
- Add a table to the Word document using the Section.AddTable() method.
- Detect the merged cells in the worksheet and merge the corresponding cells in the Word tale using the custom method MergeCells().
- Iterate through the cells in the worksheet, read the data of the cells through the CellRange.Value property and add the data to Word table cells using the TableCell.AddParagraph().AppendText() method.
- Copy the cell styles from the Excel worksheet to the Word table using the custom method CopyStyle().
- Save the Word document to a file using the Document.SaveToFile() method.
- Python
from spire.xls import *
from spire.doc import *
def MergeCells(sheet, table):
"""Merge cells in the Word table based on merged cells in the Excel sheet."""
if sheet.HasMergedCells:
ranges = sheet.MergedCells
for i in range(len(ranges)):
startRow = ranges[i].Row
startColumn = ranges[i].Column
rowCount = ranges[i].RowCount
columnCount = ranges[i].ColumnCount
if rowCount > 1 and columnCount > 1:
for j in range(startRow, startRow + rowCount):
table.ApplyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1)
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)
if rowCount > 1 and columnCount == 1:
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)
if columnCount > 1 and rowCount == 1:
table.ApplyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount - 1)
def CopyStyle(wTextRange, xCell, wCell):
"""Copy cell styling from Excel to Word."""
# Copy font style
wTextRange.CharacterFormat.TextColor = Color.FromRgb(xCell.Style.Font.Color.R, xCell.Style.Font.Color.G, xCell.Style.Font.Color.B)
wTextRange.CharacterFormat.FontSize = float(xCell.Style.Font.Size)
wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName
wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold
wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic
# Copy background color
if xCell.Style.FillPattern is not ExcelPatternType.none:
wCell.CellFormat.BackColor = Color.FromRgb(xCell.Style.Color.R, xCell.Style.Color.G, xCell.Style.Color.B)
# Copy horizontal alignment
if xCell.HorizontalAlignment == HorizontalAlignType.Left:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left
elif xCell.HorizontalAlignment == HorizontalAlignType.Center:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center
elif xCell.HorizontalAlignment == HorizontalAlignType.Right:
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right
# Copy vertical alignment
if xCell.VerticalAlignment == VerticalAlignType.Bottom:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Bottom
elif xCell.VerticalAlignment == VerticalAlignType.Center:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Middle
elif xCell.VerticalAlignment == VerticalAlignType.Top:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Top
# Load an Excel file
workbook = Workbook()
workbook.LoadFromFile("Contact list.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Create a Word document
doc = Document()
section = doc.AddSection()
section.PageSetup.Orientation = PageOrientation.Landscape
# Add a table
table = section.AddTable(True)
table.ResetCells(sheet.LastRow, sheet.LastColumn)
# Merge cells
MergeCells(sheet, table)
# Export data and styles from Excel to Word table
for r in range(1, sheet.LastRow + 1):
table.Rows[r - 1].Height = float(sheet.Rows[r - 1].RowHeight)
for c in range(1, sheet.LastColumn + 1):
xCell = sheet.Range[r, c]
wCell = table.Rows[r - 1].Cells[c - 1]
# Add text from Excel to Word table cell
textRange = wCell.AddParagraph().AppendText(xCell.NumberText)
# Copy font and cell style
CopyStyle(textRange, xCell, wCell)
# Save the document to a Word file
doc.SaveToFile("ConvertExcelDataToWordTable.docx", FileFormat.Docx)
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.
Charts and shapes in Excel are vital tools for clear and effective data presentation. Sometimes, it's beneficial to convert these visual elements into images. Perhaps you need to include a specific chart in a report or presentation outside of Excel. Or maybe you want to use an Excel-created infographic on your company's website. Regardless of the use case, knowing how to export these visuals as standalone image files can be invaluable. In this guide, we will explore how to convert charts and shapes in Excel to images in Python using Spire.XLS for Python.
- Convert a Specific Chart in an Excel Worksheet to Image in Python
- Convert All Charts in an Excel Worksheet to Images in Python
- Convert a Chart Sheet in Excel to Image in Python
- Convert Shapes in Excel to Images in 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
Convert a Specific Chart in an Excel Worksheet to Image in Python
Spire.XLS for Python offers the Workbook.SaveChartAsImage(worksheet: Worksheet, chartIndex: int) method, allowing you to convert a specific chart within a worksheet into an image stream. This image stream can then be saved as an image file in various formats, including PNG, JPG, BMP, and more. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet in the file using the Workbook.Worksheets[] property.
- Save a specific chart in the worksheet to an image stream using the Workbook.SaveChartAsImage(worksheet: Worksheet, chartIndex: int) method.
- Save the image stream to an image file using the Stream.Save() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Charts.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Save the first chart in the worksheet to an image stream
image_stream = workbook.SaveChartAsImage(sheet, 0)
# Save the image stream to a PNG image file
image_stream.Save("Output/chart.png")
workbook.Dispose()
Convert All Charts in an Excel Worksheet to Images in Python
To convert all charts in an Excel worksheet to images, you can use the Workbook.SaveChartAsImage(worksheet: Worksheet) method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet in the file using the Workbook.Worksheets[] property.
- Save all charts in the worksheet to a list of image streams using the Workbook.SaveChartAsImage(worksheet: Worksheet) method.
- Iterate through the image streams in the list and save them to separate image files.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Charts.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
image_streams = []
# Save the charts in the worksheet to a list of image streams
image_streams = workbook.SaveChartAsImage(sheet)
# Save the image streams to PNG image files
for i, image_stream in enumerate(image_streams):
image_stream.Save(f"Output/chart-{i}.png")
workbook.Dispose()
Convert a Chart Sheet in Excel to Image in Python
In Microsoft Excel, a chart sheet is a special type of sheet that is dedicated to displaying a single chart or graph. You can convert a chart sheet in an Excel workbook to an image using the Workbook.SaveChartAsImage(chartSheet: ChartSheet) method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific chart sheet in the file using the Workbook.Chartsheets[] property.
- Save the chart sheet to an image stream using the Workbook.SaveChartAsImage(chartSheet: ChartSheet) method.
- Save the image stream to an image file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSheet.xlsx")
# Get the first chart sheet
chart_sheet = workbook.Chartsheets[0]
# Save the chart sheet to an image stream
image_stream = workbook.SaveChartAsImage(chart_sheet)
# Save the image stream to a PNG image file
image_stream.Save("Output/chartSheet.png")
workbook.Dispose()
Convert Shapes in Excel to Images in Python
In addition to converting charts or chart sheets to images, you can also convert shapes in an Excel worksheet to images by using the XlsShape.SaveToImage() method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet in the file using the Workbook.Worksheets[] property.
- Iterate through all the shapes in the worksheet.
- Typecast the shape to an XlsShape object.
- Save the shape to an image stream using the XlsShape.SaveToImage() method.
- Save the image stream to an image file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Shapes.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Iterate through all the shapes in the worksheet
for i, shape in enumerate(sheet.PrstGeomShapes):
xls_shape = XlsShape(shape)
# Save the shape to an image stream
image_stream = shape.SaveToImage()
# Save the image stream to a PNG image file
image_stream.Save(f"Output/shape_{i}.png")
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.
Python: Convert Excel to ODS, XPS, PostScript and PDF/A-1b
2024-07-30 00:49:07 Written by support iceblueConverting XLS files to various formats is necessary for data management and presentation. ODS, XPS, PostScript, and PDF/A-1b offer unique advantages and are suitable for different scenarios.
ODS is widely used for compatibility with many office suites. XPS preserves document fidelity and is ideal for sharing and archiving. PostScript is a versatile page description language often used for printing and graphic design. PDF/A-1b ensures long-term archiving by complying with strict preservation standards.
This guide will illustrate how to convert Excel to ODS, XPS, PostScript, and PDF/A-1b with Python using Spire.XLS for Python, leveraging their specific strengths to meet diverse needs.
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 it, please refer to this tutorial: How to Install Spire.XLS for Python on Windows.
Convert Excel to ODS, XPS, and PostScript with Python
To convert Excel to ODS, XPS, and PostScript documents, you can utilize Workbook.SaveToFile() method. It supports converting CSV to Excel and PDF, Excel to PDF and XLSX, etc. By using this method provided by Spire.XLS for Python, you can seamlessly transform your documents into these formats while maintaining accuracy without data loss. Read the following steps to learn more:
Steps to convert Excel to ODS, XPS, and PostScript:
- Create a new Workbook object.
- Import the file to be converted from the disk using Workbook.LoadFromFile() method.
- Convert it to ODS, XPS, or PostScript with Workbook.SaveToFile() method.
Here is the code example for reference:
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load the file from the disk
workbook.LoadFromFile("sample.xlsx")
# Save the document to an ODS file
workbook.SaveToFile("to_ods.ods", FileFormat.ODS)
# Save the document as an XPS file
workbook.SaveToFile("to_xps.xps", FileFormat.XPS)
# Save the document as a PostScript file
workbook.SaveToFile("to_postscript.ps", FileFormat.PostScript)
workbook.Dispose()
Note: Images 1, 2, and 3 show the results of converting Excel files to ODS, XPS, and PostScript formats, respectively.
How to Convert Excel Documents to PDF/A-1b Format
If you need to convert Excel to PDF/A-1b Format with Python, call Workbook.SaveToFile will help you. The steps to transform Excel documents to PDF/A-1b are similar to those above, except the former involves an additional step. This tutorial will guide you through the process with detailed steps and a code example.
Steps to convert Excel to PDF/A-1b
- Instantiate a new Workbook object.
- Read the Excel document from the disk using Workbook.LoadFromFile() method.
- Set the PDF conformance level to PDF/A-1b.
- Save the generated document as PDF with Workbook.SaveToFile() method.
Here is the code example for you:
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Open the file from the disk
workbook.LoadFromFile("sample.xlsx")
# Set the PDF conformance to PDF/A-1b
workbook.ConverterSetting.PdfConformanceLevel = PdfConformanceLevel.Pdf_A1B
# Convert the Excel document to PDF/A-1b
workbook.SaveToFile("to_pdfa1b", FileFormat.PDF)
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.
Excel has been a widely used tool for data organization and analysis for many years. Over time, Microsoft has introduced different file formats for storing Excel data, the most common being the older XLS format and the more modern XLSX format.
The XLS format, introduced in the late 1990s, had certain limitations, such as a file size limit of 65,536 rows and 256 columns, and a maximum of 65,000 unique styles. The XLSX format, introduced in 2007, addressed these limitations by allowing for larger file sizes, more rows and columns, and expanded style capabilities. While XLSX is now the standard format, there are still many existing XLS files that need to be accessed and used, which makes the ability to convert between these formats an essential skill. In this article, we will explain how to convert Excel XLS to XLSX and vice versa 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
Convert XLSX to XLS in Python
To convert an XLSX file to XLS format, you can use the Workbook.SaveToFile(fileName, ExcelVersion.Version97to2003) method. The ExcelVersion.Version97to2003 parameter specifies that the workbook should be saved in the Excel 97-2003 (XLS) format. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an XLSX file using the Workbook.LoadFromFile() method.
- Save the XLSX file to XLS format using the Workbook.SaveToFile(fileName, ExcelVersion.Version97to2003) method.
- Python
from spire.xls import * from spire.xls.common import * # Specify the input and output file paths inputFile = "Sample1.xlsx" outputFile = "XlsxToXls.xls" # Create a Workbook object workbook = Workbook() # Load the XLSX file workbook.LoadFromFile(inputFile) # Save the XLSX file to XLS format workbook.SaveToFile(outputFile, ExcelVersion.Version97to2003) workbook.Dispose()
Convert XLS to XLSX in Python
To convert an XLS file to XLSX format, you need to specify the target Excel version to a version higher than 97-2003, such as 2007 (ExcelVersion.Version2007), 2010 (ExcelVersion.Version2010), 2013 (ExcelVersion.Version2013), or 2016 (ExcelVersion.Version2016). The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an XLS file using the Workbook.LoadFromFile() method.
- Save the XLS file to an Excel 2016 (XLSX) file using the Workbook.SaveToFile(fileName, ExcelVersion.Version2016) method.
- Python
from spire.xls import * from spire.xls.common import * # Specify the input and output file paths inputFile = "Sample2.xls" outputFile = "XlsToXlsx.xlsx" # Create a Workbook object workbook = Workbook() # Load the XLS file workbook.LoadFromFile(inputFile) # Save the XLS file to XLSX format workbook.SaveToFile(outputFile, ExcelVersion.Version2016) 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.
Text files have a distinct advantage over Excel spreadsheets in terms of simplicity as they don't contain complex formatting, macros or formulas. This streamlined nature not only enhances portability, but also reduces the possibility of file corruption. Consequently, converting Excel files to text files can greatly facilitates data parsing and ensures compatibility with various applications. In this article, you will learn how to convert Excel to TXT text 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
Convert Excel to TXT in Python
Spire.XLS for Python offers the Worksheet.SaveToFile(fileName: str, separator: str, encoding: Encoding) method to convert a specified worksheet to a TXT text file. The three parameters represent:
- fileName: Specifies the path and the name of the output text file.
- separator: Specifies the separator for the output text file. Common separators include commas (,), tabs, semicolons (;), etc.
- encoding: Specifies the encoding format of the file, e.g. UTF-8, Unicode, ASCII, etc. You need to use the correct encoding format to ensure that the text is represented and interpreted correctly.
The following are the detailed steps to convert Excel to text files in Python.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet by its index using Workbook.Worksheets[sheetIndex] property.
- Convert the Excel worksheet to a TXT file using Worksheet.SaveToFile() method.
- Python
import os import sys curPath = os.path.abspath(os.path.dirname(__file__)) rootPath = os.path.split(curPath)[0] sys.path.append(rootPath) from spire.xls import * from spire.xls.common import * inputFile = "Inventories.xlsx" outputFile = "ExceltoTxt.txt" # Create a Workbook instance workbook = Workbook() # Load an Excel document from disk workbook.LoadFromFile(inputFile) # Get the first worksheet sheet = workbook.Worksheets[0] # Save the worksheet as a txt file sheet.SaveToFile(outputFile, " ", Encoding.get_UTF8()) 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.
SVG (Scalable Vector Graphics) is a flexible file format widely used on the web. Unlike traditional image formats, SVG files are not based on pixels. Instead, they use mathematical equations to define shapes, lines, and colors. This unique characteristic allows SVG files to be scaled up or down without any loss of quality, making them an excellent choice for creating interactive and visually appealing graphics. By converting Excel files to SVG, you can seamlessly embed the resulting SVG files into web pages, ensuring smooth integration and display of your Excel data on the web. In this article, we will demonstrate how to convert Excel to SVG format 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
Convert a Worksheet in Excel to SVG in Python
Spire.XLS for Python provides the Worksheet.ToSVGStream() method to convert an Excel worksheet to SVG. The detailed steps are as follows:
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet by its index through Workbook.Worksheets[] property.
- Create an object of the Stream class.
- Save the worksheet to an SVG using Worksheet.ToSVGStream() method.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Save the worksheet to an SVG
stream = Stream("WorksheetToSVG.svg")
worksheet.ToSVGStream(stream, 0, 0, 0, 0)
stream.Flush()
stream.Close()
workbook.Dispose()
Convert a Chart Sheet in Excel to SVG in Python
A chart sheet in Excel is a separate sheet within an Excel workbook that is dedicated to displaying a chart. Spire.XLS for Python allows you to convert a chart sheet to SVG by using the ChartSheet.ToSVGStream() method. The detailed steps are as follows:
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific chart sheet using Workbook.GetChartSheetByName() method.
- Create an object of the Stream class.
- Save the chart sheet to an SVG using ChartSheet.ToSVGStream() method.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample2.xlsx")
# Get a specific chart sheet
chartSheet = workbook.GetChartSheetByName("Chart1")
# Save the chart sheet to an SVG
stream = Stream("ChartSheetToSVG.svg")
chartSheet.ToSVGStream(stream)
stream.Flush()
stream.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.
Python: Convert Excel to Open XML or Open XML to Excel
2023-12-27 01:21:07 Written by support iceblueIn the context of Excel, Open XML refers to the underlying file format used by Excel to store spreadsheet data, formatting, formulas, and other related information. It provides a powerful and flexible basis for working with Excel files programmatically.
By converting Excel to Open XML, developers gain greater control and automation when working with spreadsheet-related tasks. In turn, you can also generate Excel files from Open XML to take advantage of Excel's built-in capabilities to perform advanced data operations. In this article, you will learn how to convert Excel to Open XML or Open XML to Excel 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
Convert Excel to Open XML in Python
Spire.XLS for Python offers the Workbook.SaveAsXml() method to save an Excel file in Open XML format. The following are the detailed steps.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Save the Excel file in Open XML format using Workbook.SaveAsXml() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("sample.xlsx")
# Save the Excel file in Open XML file format
workbook.SaveAsXml("ExcelToXML.xml")
workbook.Dispose()
Convert Open XML to Excel in Python
To convert an Open XML file to Excel, you need to load the Open XML file through the Workbook.LoadFromXml() method, and then call the Workbook.SaveToFile() method to save it as an Excel file. The following are the detailed steps.
- Create a Workbook object.
- Load an Open XML file using Workbook.LoadFromXml() method.
- Save the Open XML file to Excel using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Open XML file
workbook.LoadFromXml("ExcelToXML.xml")
# Save the Open XML file to Excel XLSX format
workbook.SaveToFile("XMLToExcel.xlsx", ExcelVersion.Version2016)
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.
If you need to display or interact with the contents of an Excel spreadsheet on a web page, converting Excel to HTML is a good choice. This conversion allows users to view and manipulate the table data directly on the web page without having to download the Excel file, providing a more convenient way to share and display the data. When needed, you can also convert the HTML file back to Excel format for better data editing. In this article, we will show you how to convert Excel to HTML and HTML to Excel in Python by using Spire.XLS for Python.
- Convert Excel to HTML in Python
- Convert Excel to HTML with Images Embedded in Python
- Convert HTML to Excel in 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 commands.
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
Convert Excel to HTML in Python
Spire.XLS for Python supports converting a specific Excel worksheet to HTML using Worksheet.SaveToHtml() method. Detailed steps are listed below.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[] property.
- Save the worksheet as an HTML file using Worksheet.SaveToHtml() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "C:/Users/Administrator/Desktop/Sample_1.xlsx" outputFile = "C:/Users/Administrator/Desktop/ToHtml.html" # Create a Workbook instance workbook = Workbook() # Load a sample Excel file workbook.LoadFromFile(inputFile) # Get the first sheet of this file sheet = workbook.Worksheets[0] # Save the worksheet to HTML sheet.SaveToHtml(outputFile) workbook.Dispose()
Convert Excel to HTML with Images Embedded in Python
If the Excel file you want to convert contains images, you can embed the images into the HTML file by setting the ImageEmbedded property to "True". Detailed steps are listed below.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[] property.
- Create an HTMLOptions instance.
- Set the ImageEmbedded as “True” to embed images to HTML.
- Save the worksheet as an HTML file using Worksheet.SaveToHtml() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "C:/Users/Administrator/Desktop/Sample_2.xlsx" outputFile = "C:/Users/Administrator/Desktop/ToHtmlwithImages.html" # Create a Workbook instance workbook = Workbook() # Load a sample Excel file workbook.LoadFromFile(inputFile) # Get the first sheet of this file sheet = workbook.Worksheets[0] # Create an HTMLOptions instance options = HTMLOptions() # Embed images to HTML options.ImageEmbedded = True # Save the worksheet to HTML sheet.SaveToHtml(outputFile, options) workbook.Dispose()
Convert HTML to Excel in Python
You are also allowed to convert an HTML back to an Excel file by calling the Workbook.SaveToFile() method provided by Spire.XLS for Python. Detailed steps are listed below.
- Create a Workbook instance.
- Load an HTML file from disk using Workbook.LoadFromFile() method.
- Save the HTML file to an Excel file by using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "C:/Users/Administrator/Desktop/Sample.html" outputFile = "C:/Users/Administrator/Desktop/ToExcel.xlsx" # Create a Workbook instance workbook = Workbook() # Load an HTML file from disk workbook.LoadFromHtml(inputFile) # Save the HTML file to an Excel file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) 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.
A CSV (Comma-Separated Values) file is a plain text file used to store tabular data. Although CSV files are widely supported by spreadsheet programs, there may still be times when you need to convert them to PDF files to ensure broader accessibility and also enable security features. This article will demonstrate how to convert CSV to PDF 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
Convert CSV to PDF in Python
The Workbook.SaveToFile() method provided by Spire.XLS for Python allows to save a CSV file as a PDF file. The following are the detailed steps.
- Create a Workbook object.
- Load a CSV file using Workbook.LoadFromFile() method.
- Set the Workbook.ConverterSetting.SheetFitToPage property as true to ensure the worksheet is rendered to one PDF page.
- Get the first worksheet in the Workbook using Workbook.Worksheets[] property.
- Loop through the columns in the worksheet and auto-fit the width of each column using Worksheet.AutoFitColumn() method.
- Convert the CSV file to PDF using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load a CSV file
workbook.LoadFromFile("sample.csv", ",", 1, 1)
# Set the SheetFitToPage property as true
workbook.ConverterSetting.SheetFitToPage = True
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Autofit columns in the worksheet
i = 1
while i < sheet.Columns.Length:
sheet.AutoFitColumn(i)
i += 1
# Save the CSV file to PDF
workbook.SaveToFile("CSVToPDF.pdf", FileFormat.PDF)
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.
