Mixing
Pandas xlsxwriter to write dataframe to excel and implementing column-width and border related formatting
Background:
I am using Pandas and have a dataframe 'df' which I intend to write into an Excel sheet. I use the code below and get the output Excel sheet as shown in attached snapshot 'Present.JPG':
import pandas as pd
import xlsxwriter
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
Problem description:
I would like to write the dataframe to Excel and incorporate the following changes.
1) Get rid of the first column indicating the index
2) Implement text wrapping on all columns (to auto-size each column width)
3) Sketch thick border A1 to C4, D1 to F4 and column G
Eventually, I would like the Excel sheet to look like as shown in snapshot 'Desired.JPG':
Tried till now:
I tried the following commands but they over-write the border on to the content of the cells. Furthermore, I am not able to figure out how to extend the border (and text wrapping) beyond a single cell.
writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet= writer.sheets['Sheet1']
full_border = workbook.add_format({"border":1,"border_color": "#000000"})
link_format = workbook.add_format({'text_wrap': True})
worksheet.write("D3", None, full_border)
worksheet.write("E1", None, link_format)
writer.save()
python excel pandas dataframe xlsxwriter
asked Jan 1 at 23:43
SinhaSinha
334
add a comment |
Background:
I am using Pandas and have a dataframe 'df' which I intend to write into an Excel sheet. I use the code below and get the output Excel sheet as shown in attached snapshot 'Present.JPG':
import pandas as pd
import xlsxwriter
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
Problem description:
I would like to write the dataframe to Excel and incorporate the following changes.
1) Get rid of the first column indicating the index
2) Implement text wrapping on all columns (to auto-size each column width)
3) Sketch thick border A1 to C4, D1 to F4 and column G
Eventually, I would like the Excel sheet to look like as shown in snapshot 'Desired.JPG':
Tried till now:
I tried the following commands but they over-write the border on to the content of the cells. Furthermore, I am not able to figure out how to extend the border (and text wrapping) beyond a single cell.
writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet= writer.sheets['Sheet1']
full_border = workbook.add_format({"border":1,"border_color": "#000000"})
link_format = workbook.add_format({'text_wrap': True})
worksheet.write("D3", None, full_border)
worksheet.write("E1", None, link_format)
writer.save()
python excel pandas dataframe xlsxwriter
asked Jan 1 at 23:43
SinhaSinha
334
worksheet.set_column('A:G', 4, link_format)?
– aws_apprentice
Jan 1 at 23:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10
add a comment |
Background:
I am using Pandas and have a dataframe 'df' which I intend to write into an Excel sheet. I use the code below and get the output Excel sheet as shown in attached snapshot 'Present.JPG':
import pandas as pd
import xlsxwriter
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
Problem description:
I would like to write the dataframe to Excel and incorporate the following changes.
1) Get rid of the first column indicating the index
2) Implement text wrapping on all columns (to auto-size each column width)
3) Sketch thick border A1 to C4, D1 to F4 and column G
Eventually, I would like the Excel sheet to look like as shown in snapshot 'Desired.JPG':
Tried till now:
I tried the following commands but they over-write the border on to the content of the cells. Furthermore, I am not able to figure out how to extend the border (and text wrapping) beyond a single cell.
writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet= writer.sheets['Sheet1']
full_border = workbook.add_format({"border":1,"border_color": "#000000"})
link_format = workbook.add_format({'text_wrap': True})
worksheet.write("D3", None, full_border)
worksheet.write("E1", None, link_format)
writer.save()
python excel pandas dataframe xlsxwriter
asked Jan 1 at 23:43
SinhaSinha
334
Background:
I am using Pandas and have a dataframe 'df' which I intend to write into an Excel sheet. I use the code below and get the output Excel sheet as shown in attached snapshot 'Present.JPG':
import pandas as pd
import xlsxwriter
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
Problem description:
I would like to write the dataframe to Excel and incorporate the following changes.
1) Get rid of the first column indicating the index
2) Implement text wrapping on all columns (to auto-size each column width)
3) Sketch thick border A1 to C4, D1 to F4 and column G
Eventually, I would like the Excel sheet to look like as shown in snapshot 'Desired.JPG':
Tried till now:
I tried the following commands but they over-write the border on to the content of the cells. Furthermore, I am not able to figure out how to extend the border (and text wrapping) beyond a single cell.
writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet= writer.sheets['Sheet1']
full_border = workbook.add_format({"border":1,"border_color": "#000000"})
link_format = workbook.add_format({'text_wrap': True})
worksheet.write("D3", None, full_border)
worksheet.write("E1", None, link_format)
writer.save()
python excel pandas dataframe xlsxwriter
python excel pandas dataframe xlsxwriter
asked Jan 1 at 23:43
SinhaSinha
334
asked Jan 1 at 23:43
SinhaSinha
334
asked Jan 1 at 23:43
SinhaSinha
334
asked Jan 1 at 23:43
SinhaSinha
334
asked Jan 1 at 23:43
SinhaSinha
334
334
worksheet.set_column('A:G', 4, link_format)?
– aws_apprentice
Jan 1 at 23:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10
add a comment |
worksheet.set_column('A:G', 4, link_format)?
– aws_apprentice
Jan 1 at 23:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10
worksheet.set_column('A:G', 4, link_format) ?– aws_apprentice
Jan 1 at 23:52
worksheet.set_column('A:G', 4, link_format) ?– aws_apprentice
Jan 1 at 23:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999811%2fpandas-xlsxwriter-to-write-dataframe-to-excel-and-implementing-column-width-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999811%2fpandas-xlsxwriter-to-write-dataframe-to-excel-and-implementing-column-width-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
worksheet.set_column('A:G', 4, link_format)?– aws_apprentice
Jan 1 at 23:52
Nope, does not work. Using 'A:G' I get following error AttributeError: 'NoneType' object has no attribute 'group'
– Sinha
Jan 2 at 0:52
explore .. styleframe.readthedocs.io/en/latest
– iamklaus
Jan 2 at 9:10