Python calls xlsxwriter to create xlsx method-Python Tutorial-php.cn

Home

Backend Development

Python Tutorial

Python calls xlsxwriter to create xlsx method

不言

May 03, 2018 am 11:49 AM

pythonxlsxwriter

这篇文章主要介绍了关于python调用xlsxwriter创建xlsx的方法，有着一定的参考价值，现在分享给大家，有需要的朋友可以参考一下

通过pip安装xlsxwriter

pip install xlsxwriter

下面进行基本的操作演示：

1. 首先创建一个excel的文档

workbook = xlsxwriter.Workbook(dir)

2. 在文档中创建表

table_name = &#39;sheet1&#39;
worksheet = workbook.add_worksheet(table_name) # 创建一个表名为‘sheet1&#39;的表，并返回这个表对象

3. 创建表后，就可以在表格上面进行写入操作

worksheet.write_column(&#39;A1&#39;, 5) # 在A1单元格写入数字5

有时候，我们想修改输入内容的格式，例如设置字体颜色加粗，斜体，日期格式等，这时候，就可以通过使用xlsxwriter提供的格式类。

具体可见：http://xlsxwriter.readthedocs.io/format.html

下面以写入一个粗体的红色的日期类为例

import datetime
# 需要先把字符串格式化成日期
date_time = datetime.datetime.strptime(&#39;2017-1-25&#39;, &#39;%Y-%m-%d&#39;)
# 定义一个格式类，粗体的红色的日期
date_format = workbook.add_format({&#39;bold&#39;: True, &#39;font_color&#39;: &#39;red&#39;, &#39;num_format&#39;: &#39;yyyy-mm-dd&#39;})
# 写入该格式类
worksheet.write_column(&#39;A2&#39;, date_time, date_format)

4. xlsxwriter支持很多图表格式的插入

具体可以参见：http://xlsxwriter.readthedocs.io/chart.html

这里摘抄一段图标类型的介绍：

excel定义的图分两级类别描述，第一级分别有九大类，如下所示

area: 面积图
bar: 转置直方图
column: 柱状图
line: 直线图
pie: 饼状图
doughnut: 环形图
scatter: 散点图
stock: 股票趋势图
radar: 雷达图

第二级则是描述是否有连线，是否有平滑曲线等细节调整。

area
 stacked
 percent_stacked
bar
 stacked
 percent_stacked
column
 stacked
 percent_stacked
scatter
 straight_with_markers
 straight
 smooth_with_markers
 smooth
radar
 with_markers
 filled

下面举例绘制一个散点图。

chart1 = workbook.add_chart({&#39;type&#39;: &#39;scatter&#39;, &#39;subtype&#39;: &#39;straight&#39;})
chart1.set_title({&#39;name&#39;: &#39;图1&#39;})
chart1.add_series({
 &#39;name&#39;: &#39;系列1&#39;,
 # 集合范围
 &#39;categories&#39;: &#39;=sheet1!$A$1:$A$89&#39;,
 # 数值范围
 &#39;values&#39;: &#39;=sheet1!$B$1:$B$89&#39;),
 # 是否连线
 &#39;line&#39;: {&#39;none&#39;: True},
 # 默认图格式
 &#39;marker&#39;: {&#39;type&#39;: &#39;automatic&#39;},
})
# 设置y轴的范围
chart1.set_y_axis({&#39;max&#39;: 1.1, &#39;min&#39;: 0})
chart1.set_size({&#39;x_scale&#39;: 2, &#39;y_scale&#39;: 2})
# 把图例插入到表中的C2位置，并且设置横轴偏移量为25，纵轴偏移量为10
worksheet.insert_chart(&#39;C2&#39;, chart1, {&#39;x_offset&#39;: 25, &#39;y_offset&#39;: 10})

以上这篇

详细的官方文档可见：http://xlsxwriter.readthedocs.io/

通过pip安装xlsxwriter

pip install xlsxwriter

下面进行基本的操作演示：

1. 首先创建一个excel的文档

workbook = xlsxwriter.Workbook(dir)

2. 在文档中创建表

table_name = &#39;sheet1&#39;
worksheet = workbook.add_worksheet(table_name) # 创建一个表名为‘sheet1&#39;的表，并返回这个表对象

3. 创建表后，就可以在表格上面进行写入操作

worksheet.write_column(&#39;A1&#39;, 5) # 在A1单元格写入数字5

有时候，我们想修改输入内容的格式，例如设置字体颜色加粗，斜体，日期格式等，这时候，就可以通过使用xlsxwriter提供的格式类。

具体可见：http://xlsxwriter.readthedocs.io/format.html

下面以写入一个粗体的红色的日期类为例

import datetime
# 需要先把字符串格式化成日期
date_time = datetime.datetime.strptime(&#39;2017-1-25&#39;, &#39;%Y-%m-%d&#39;)
# 定义一个格式类，粗体的红色的日期
date_format = workbook.add_format({&#39;bold&#39;: True, &#39;font_color&#39;: &#39;red&#39;, &#39;num_format&#39;: &#39;yyyy-mm-dd&#39;})
# 写入该格式类
worksheet.write_column(&#39;A2&#39;, date_time, date_format)

4. xlsxwriter支持很多图表格式的插入

具体可以参见：http://xlsxwriter.readthedocs.io/chart.html

这里摘抄一段图标类型的介绍：

excel定义的图分两级类别描述，第一级分别有九大类，如下所示

area: 面积图
bar: 转置直方图
column: 柱状图
line: 直线图
pie: 饼状图
doughnut: 环形图
scatter: 散点图
stock: 股票趋势图
radar: 雷达图

第二级则是描述是否有连线，是否有平滑曲线等细节调整。

area
 stacked
 percent_stacked
bar
 stacked
 percent_stacked
column
 stacked
 percent_stacked
scatter
 straight_with_markers
 straight
 smooth_with_markers
 smooth
radar
 with_markers
 filled

下面举例绘制一个散点图。

chart1 = workbook.add_chart({&#39;type&#39;: &#39;scatter&#39;, &#39;subtype&#39;: &#39;straight&#39;})
chart1.set_title({&#39;name&#39;: &#39;图1&#39;})
chart1.add_series({
 &#39;name&#39;: &#39;系列1&#39;,
 # 集合范围
 &#39;categories&#39;: &#39;=sheet1!$A$1:$A$89&#39;,
 # 数值范围
 &#39;values&#39;: &#39;=sheet1!$B$1:$B$89&#39;),
 # 是否连线
 &#39;line&#39;: {&#39;none&#39;: True},
 # 默认图格式
 &#39;marker&#39;: {&#39;type&#39;: &#39;automatic&#39;},
})
# 设置y轴的范围
chart1.set_y_axis({&#39;max&#39;: 1.1, &#39;min&#39;: 0})
chart1.set_size({&#39;x_scale&#39;: 2, &#39;y_scale&#39;: 2})
# 把图例插入到表中的C2位置，并且设置横轴偏移量为25，纵轴偏移量为10
worksheet.insert_chart(&#39;C2&#39;, chart1, {&#39;x_offset&#39;: 25, &#39;y_offset&#39;: 10})

相关推荐：

Python中XlsxWriter模块简介与用法分析

Python中几种存取xls/xlsx文件的方法总结

The above is the detailed content of Python calls xlsxwriter to create xlsx method. For more information, please follow other related articles on the PHP Chinese website!

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What are the alternatives to concatenate two lists in Python?May 09, 2025 am 12:16 AM

There are many methods to connect two lists in Python: 1. Use operators, which are simple but inefficient in large lists; 2. Use extend method, which is efficient but will modify the original list; 3. Use the = operator, which is both efficient and readable; 4. Use itertools.chain function, which is memory efficient but requires additional import; 5. Use list parsing, which is elegant but may be too complex. The selection method should be based on the code context and requirements.

Python: Efficient Ways to Merge Two ListsMay 09, 2025 am 12:15 AM

There are many ways to merge Python lists: 1. Use operators, which are simple but not memory efficient for large lists; 2. Use extend method, which is efficient but will modify the original list; 3. Use itertools.chain, which is suitable for large data sets; 4. Use * operator, merge small to medium-sized lists in one line of code; 5. Use numpy.concatenate, which is suitable for large data sets and scenarios with high performance requirements; 6. Use append method, which is suitable for small lists but is inefficient. When selecting a method, you need to consider the list size and application scenarios.

Compiled vs Interpreted Languages: pros and consMay 09, 2025 am 12:06 AM

Compiledlanguagesofferspeedandsecurity,whileinterpretedlanguagesprovideeaseofuseandportability.1)CompiledlanguageslikeC arefasterandsecurebuthavelongerdevelopmentcyclesandplatformdependency.2)InterpretedlanguageslikePythonareeasiertouseandmoreportab

Python: For and While Loops, the most complete guideMay 09, 2025 am 12:05 AM

In Python, a for loop is used to traverse iterable objects, and a while loop is used to perform operations repeatedly when the condition is satisfied. 1) For loop example: traverse the list and print the elements. 2) While loop example: guess the number game until you guess it right. Mastering cycle principles and optimization techniques can improve code efficiency and reliability.

Python concatenate lists into a stringMay 09, 2025 am 12:02 AM

To concatenate a list into a string, using the join() method in Python is the best choice. 1) Use the join() method to concatenate the list elements into a string, such as ''.join(my_list). 2) For a list containing numbers, convert map(str, numbers) into a string before concatenating. 3) You can use generator expressions for complex formatting, such as ','.join(f'({fruit})'forfruitinfruits). 4) When processing mixed data types, use map(str, mixed_list) to ensure that all elements can be converted into strings. 5) For large lists, use ''.join(large_li

Python's Hybrid Approach: Compilation and Interpretation CombinedMay 08, 2025 am 12:16 AM

Pythonusesahybridapproach,combiningcompilationtobytecodeandinterpretation.1)Codeiscompiledtoplatform-independentbytecode.2)BytecodeisinterpretedbythePythonVirtualMachine,enhancingefficiencyandportability.

Learn the Differences Between Python's 'for' and 'while' LoopsMay 08, 2025 am 12:11 AM

ThekeydifferencesbetweenPython's"for"and"while"loopsare:1)"For"loopsareidealforiteratingoversequencesorknowniterations,while2)"while"loopsarebetterforcontinuinguntilaconditionismetwithoutpredefinediterations.Un

Python concatenate lists with duplicatesMay 08, 2025 am 12:09 AM

In Python, you can connect lists and manage duplicate elements through a variety of methods: 1) Use operators or extend() to retain all duplicate elements; 2) Convert to sets and then return to lists to remove all duplicate elements, but the original order will be lost; 3) Use loops or list comprehensions to combine sets to remove duplicate elements and maintain the original order.

See all articles