Creating Excel spreadsheets using pyExcelerator

After getting my statistics and reports from Splunk, I wanted to automatically generate Excel spreadsheets from the results. I found pyExcelerator, and after massaging the output from Splunk into a comma separated values (CSV) format, it was easy to create Excel spreadsheets. To get the Splunk output into a more usable CSV format, I used:

awk '{ printf "%s,%s", $1, $2 }'

Example script:

# Import pyExcelerator and date modules
import pyExcelerator as pycel
from datetime import date, timedelta
 
# Create Excel workbook
wb = pycel.Workbook()
 
# Create Excel sheet and header
sheet1 = wb.add_sheet("Sheet1")
sheet1.write(0, 0, "Value 1")
sheet1.write(0, 1, "Value 2")
 
# Read lines from a file and use the comma separated values
i = 1
file = open("splunk_output.txt", "r")
for line in file:
    elements = line.split(",")
    languages.write(i, 0, str(elements[0]))
    languages.write(i, 1, str(elements[1]))
    i = i+1
file.close()
 
# Write out Excel file
wb.save("splunk_output.xls")

Leave a Reply

Your email address will not be published. Required fields are marked *