xlsxwriter - Defining column format based on column name
1
How to define a form for column based on column name in xlsxwriter.
For example when column name is Sale_Date I would like to define date_format whereas if the column name is count_of_sales I would like to use number_format
I know how to define a format for a specific column but I am not sure how to define it based on column name as shown below.
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
worksheet1.write('A1', 'Sale_Date', date_format)
Could anyone advice. Thanks..
Update:
# create an output stream
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# Create the workbook
workbook = writer.book
# Create and name the sheets
worksheet = workbook.add_worksheet('Cover Sheet')
worksheet1 = workbook.add_worksheet('Sample Data')
# Number formatting
number_format = workbook.add_format({'num_format': '####', 'font_size': 14})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
for col in field_names:
worksheet1.write('A1', col, date_format if 'created_at' in col else number_format)
# Defining Cover sheet format
worksheet.set_column('A:BZ', None, color_format)
# # header column
row = 0
col = 0
for data in title:
worksheet1.write(row, col, data, number_format)
col += 1
#Pulling monthly data for columns for weekly raw data sheet
current_row = 1
for values in output_file:
for idx, value in enumerate(values):
worksheet1.write(current_row, idx, value, number_format)
current_row += 1
# the writer has done its job
writer.close()
# go back to the beginning of the stream
output.seek(0)
# finally return the file
return output
Sample column list:
name, age,class,join_date,date_of_birth,filing_date
python xlsxwriter
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
|
show 4 more comments
1
How to define a form for column based on column name in xlsxwriter.
For example when column name is Sale_Date I would like to define date_format whereas if the column name is count_of_sales I would like to use number_format
I know how to define a format for a specific column but I am not sure how to define it based on column name as shown below.
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
worksheet1.write('A1', 'Sale_Date', date_format)
Could anyone advice. Thanks..
Update:
# create an output stream
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# Create the workbook
workbook = writer.book
# Create and name the sheets
worksheet = workbook.add_worksheet('Cover Sheet')
worksheet1 = workbook.add_worksheet('Sample Data')
# Number formatting
number_format = workbook.add_format({'num_format': '####', 'font_size': 14})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
for col in field_names:
worksheet1.write('A1', col, date_format if 'created_at' in col else number_format)
# Defining Cover sheet format
worksheet.set_column('A:BZ', None, color_format)
# # header column
row = 0
col = 0
for data in title:
worksheet1.write(row, col, data, number_format)
col += 1
#Pulling monthly data for columns for weekly raw data sheet
current_row = 1
for values in output_file:
for idx, value in enumerate(values):
worksheet1.write(current_row, idx, value, number_format)
current_row += 1
# the writer has done its job
writer.close()
# go back to the beginning of the stream
output.seek(0)
# finally return the file
return output
Sample column list:
name, age,class,join_date,date_of_birth,filing_date
python xlsxwriter
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
Have you tried using conditions, something like:for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)
– Rahul Chawla
Nov 22 '18 at 11:21
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35
|
show 4 more comments
1
1
1
How to define a form for column based on column name in xlsxwriter.
For example when column name is Sale_Date I would like to define date_format whereas if the column name is count_of_sales I would like to use number_format
I know how to define a format for a specific column but I am not sure how to define it based on column name as shown below.
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
worksheet1.write('A1', 'Sale_Date', date_format)
Could anyone advice. Thanks..
Update:
# create an output stream
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# Create the workbook
workbook = writer.book
# Create and name the sheets
worksheet = workbook.add_worksheet('Cover Sheet')
worksheet1 = workbook.add_worksheet('Sample Data')
# Number formatting
number_format = workbook.add_format({'num_format': '####', 'font_size': 14})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
for col in field_names:
worksheet1.write('A1', col, date_format if 'created_at' in col else number_format)
# Defining Cover sheet format
worksheet.set_column('A:BZ', None, color_format)
# # header column
row = 0
col = 0
for data in title:
worksheet1.write(row, col, data, number_format)
col += 1
#Pulling monthly data for columns for weekly raw data sheet
current_row = 1
for values in output_file:
for idx, value in enumerate(values):
worksheet1.write(current_row, idx, value, number_format)
current_row += 1
# the writer has done its job
writer.close()
# go back to the beginning of the stream
output.seek(0)
# finally return the file
return output
Sample column list:
name, age,class,join_date,date_of_birth,filing_date
python xlsxwriter
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
How to define a form for column based on column name in xlsxwriter.
For example when column name is Sale_Date I would like to define date_format whereas if the column name is count_of_sales I would like to use number_format
I know how to define a format for a specific column but I am not sure how to define it based on column name as shown below.
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
worksheet1.write('A1', 'Sale_Date', date_format)
Could anyone advice. Thanks..
Update:
# create an output stream
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# Create the workbook
workbook = writer.book
# Create and name the sheets
worksheet = workbook.add_worksheet('Cover Sheet')
worksheet1 = workbook.add_worksheet('Sample Data')
# Number formatting
number_format = workbook.add_format({'num_format': '####', 'font_size': 14})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 14})
for col in field_names:
worksheet1.write('A1', col, date_format if 'created_at' in col else number_format)
# Defining Cover sheet format
worksheet.set_column('A:BZ', None, color_format)
# # header column
row = 0
col = 0
for data in title:
worksheet1.write(row, col, data, number_format)
col += 1
#Pulling monthly data for columns for weekly raw data sheet
current_row = 1
for values in output_file:
for idx, value in enumerate(values):
worksheet1.write(current_row, idx, value, number_format)
current_row += 1
# the writer has done its job
writer.close()
# go back to the beginning of the stream
output.seek(0)
# finally return the file
return output
Sample column list:
name, age,class,join_date,date_of_birth,filing_date
python xlsxwriter
python xlsxwriter
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
edited Nov 22 '18 at 13:07
scott martin
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
asked Nov 22 '18 at 10:38
scott martinscott martin
1108
1108
Have you tried using conditions, something like:for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)
– Rahul Chawla
Nov 22 '18 at 11:21
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35
|
show 4 more comments
Have you tried using conditions, something like:for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)
– Rahul Chawla
Nov 22 '18 at 11:21
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35
Have you tried using conditions, something like:
for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)– Rahul Chawla
Nov 22 '18 at 11:21
Have you tried using conditions, something like:
for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)– Rahul Chawla
Nov 22 '18 at 11:21
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35
|
show 4 more comments
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
});
}
});
draft saved
draft discarded
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%2f53429058%2fxlsxwriter-defining-column-format-based-on-column-name%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53429058%2fxlsxwriter-defining-column-format-based-on-column-name%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
Have you tried using conditions, something like:
for col in col_names: worksheet1.write('A1', col, date_format if 'date' in col else num_format)– Rahul Chawla
Nov 22 '18 at 11:21
@RahulChawla,wodering how could I do if the date column is not fixed and its position keeps changing from column 1 to column 2 and so on..
– scott martin
Nov 22 '18 at 12:14
One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 '18 at 12:24
@RahulChawla what I meant was how to iterate through columns other than A1.. Believe this would check cell A1 each time.. Reason being the columns are dynamic and hence the date column could be in cell A1 in one run and then G1 in the next run
– scott martin
Nov 22 '18 at 12:33
@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 '18 at 12:35