xlsxwriter - Defining column format based on column name

up vote
1
down vote

favorite

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

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

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 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 at 12:14

One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 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 at 12:33

@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 at 12:35

|
show 4 more comments

up vote
1
down vote

favorite

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

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

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 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 at 12:14

One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 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 at 12:33

@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 at 12:35

|
show 4 more comments

up vote
1
down vote

favorite

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

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

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

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

edited Nov 22 at 13:07

asked Nov 22 at 10:38

scott martin

587

asked Nov 22 at 10:38

scott martin

587

asked Nov 22 at 10:38

scott martin

587

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 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 at 12:14

One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 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 at 12:33

@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 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 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 at 12:14

One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 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 at 12:33

@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 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 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 at 12:14

One instance of the table will have static positions, right? It can change in other runs.
– Rahul Chawla
Nov 22 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 at 12:33

@RahulChawla the max number of columns could go upto 30..
– scott martin
Nov 22 at 12:35

|
show 4 more comments

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',
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');
});

Post as a guest

Name

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

Name

Required, but never shown

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl