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









share|improve this question

























  • 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
















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









share|improve this question

























  • 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














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









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 13:07







scott martin

















asked Nov 22 '18 at 10:38









scott martinscott martin

998




998













  • 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











  • @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












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


















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














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





















































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







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'