How do i write data in a specific column in excel using python?
Below I've provided a fully reproducible example of how you can go about modifying an existing .xlsx workbook using pandas and the openpyxl module (link to Openpyxl Docs). Show
First, for demonstration purposes, I create a workbook called test.xlsx:
This is the Expected output at this point: In this second part, we load the existing workbook ('test.xlsx') and modify the third column with different data.
This is the Expected output at the end: The following sections explain how to write various types of data to an Excel worksheet using XlsxWriter. Writing data to a worksheet cellThe import xlsxwriter workbook = xlsxwriter.Workbook('write_data.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 1234) # Writes an int worksheet.write(1, 0, 1234.56) # Writes a float worksheet.write(2, 0, 'Hello') # Writes a string worksheet.write(3, 0, None) # Writes None worksheet.write(4, 0, True) # Writes a bool workbook.close() The
The
It should be noted that Excel has a very limited set of types to map to. The Python types that the Writing lists of dataWriting compound data types such as lists with XlsxWriter is done the same
way it would be in any other Python program: with a loop. The Python import xlsxwriter workbook = xlsxwriter.Workbook('write_list.xlsx') worksheet = workbook.add_worksheet() my_list = [1, 2, 3, 4, 5] for row_num, data in enumerate(my_list): worksheet.write(row_num, 0, data) workbook.close() Or if you wanted to write this horizontally as a row: import xlsxwriter workbook = xlsxwriter.Workbook('write_list.xlsx') worksheet = workbook.add_worksheet() my_list = [1, 2, 3, 4, 5] for col_num, data in enumerate(my_list): worksheet.write(0, col_num, data) workbook.close() For a list of lists structure you would use two loop levels: import xlsxwriter workbook = xlsxwriter.Workbook('write_list.xlsx') worksheet = workbook.add_worksheet() my_list = [[1, 1, 1, 1, 1], [2, 2, 2, 2, 1], [3, 3, 3, 3, 1], [4, 4, 4, 4, 1], [5, 5, 5, 5, 1]] for row_num, row_data in enumerate(my_list): for col_num, col_data in enumerate(row_data): worksheet.write(row_num, col_num, col_data) workbook.close() The worksheet class has two utility functions called
import xlsxwriter workbook = xlsxwriter.Workbook('write_list.xlsx') worksheet = workbook.add_worksheet() my_list = [1, 2, 3, 4, 5] worksheet.write_row(0, 1, my_list) worksheet.write_column(1, 0, my_list) workbook.close() Writing dicts of dataUnlike lists there is no single simple way to write a Python dictionary to an Excel worksheet using Xlsxwriter. The method will depend of the structure of the data in the dictionary. Here is a simple example for a simple data structure: import xlsxwriter workbook = xlsxwriter.Workbook('write_dict.xlsx') worksheet = workbook.add_worksheet() my_dict = {'Bob': [10, 11, 12], 'Ann': [20, 21, 22], 'May': [30, 31, 32]} col_num = 0 for key, value in my_dict.items(): worksheet.write(0, col_num, key) worksheet.write_column(1, col_num, value) col_num += 1 workbook.close() Writing dataframesThe best way to deal with dataframes or complex data structure is to use Python Pandas. Pandas is a Python data analysis library. It can read, filter and re-arrange small and large data sets and output them in a range of formats including Excel. To use XlsxWriter with Pandas you specify it as the Excel writer engine: import pandas as pd # Create a Pandas dataframe from the data. df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]}) # Create a Pandas Excel writer using XlsxWriter as the engine. writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter') # Convert the dataframe to an XlsxWriter Excel object. df.to_excel(writer, sheet_name='Sheet1') # Close the Pandas Excel writer and output the Excel file. writer.save() The output from this would look like the following: For more information on using Pandas with XlsxWriter see Working with Python Pandas and XlsxWriter. Writing user defined typesAs shown in the the first section above, the worksheet As an example, say you wanted to modify def write_uuid(worksheet, row, col, uuid, format=None): return worksheet.write_string(row, col, str(uuid), format) You could then add a handler that matches the # match, action() worksheet.add_write_handler(uuid.UUID, write_uuid) Then you can use my_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, 'python.org') # Write the UUID. This would raise a TypeError without the handler. worksheet.write('A1', my_uuid) Multiple callback functions can be added using worksheet.add_write_handler(int, test_number_range) worksheet.add_write_handler(float, test_number_range) How the write handler feature worksThe Here is a simplified version of the def write(self, row, col, *args): # The first arg should be the token for all write calls. token = args[0] # Get the token type. token_type = type(token) # Check for any user defined type handlers with callback functions. if token_type in self.write_handlers: write_handler = self.write_handlers[token_type] function_return = write_handler(self, row, col, *args) # If the return value is None then the callback has returned # control to this function and we should continue as # normal. Otherwise we return the value to the caller and exit. if function_return is None: pass else: return function_return # Check for standard Python types, if we haven't returned already. if token_type is bool: return self.write_boolean(row, col, *args) # Etc. ... The syntax of write handler functionsFunctions used in the def my_function(worksheet, row, col, token, format=None): return worksheet.write_string(row, col, token, format) The function will be passed a
worksheet instance, an integer def my_function(worksheet, row, col, token, *args): return worksheet.write_string(row, col, token, *args) Note, you don’t have to explicitly handle You can also make use of the def hide_password(worksheet, row, col, string, format=None): if col == 1 and row > 0: return worksheet.write_string(row, col, '****', format) else: return worksheet.write_string(row, col, string, format) The return value of write handler functionsFunctions used in the
For example,
say you wanted to ignore def ignore_nan(worksheet, row, col, number, format=None): if math.isnan(number): return worksheet.write_blank(row, col, None, format) else: # Return control to the calling write() method. return None If you wanted to just drop the def ignore_nan(worksheet, row, col, number, format=None): if math.isnan(number): return 0 else: # Return control to the calling write() method. return None How do you write to a specific column in python Excel?Python Pandas - How to write in a specific column in an Excel.... import pandas as pd.. ExcelDataInPandasDataFrame = pd. read_excel("./YourExcel.xlsx"). YourDataInAList = [12.34,17.56,12.45]. ExcelDataInPandasDataFrame ["Col_C"] = YourDataInAList.. ExcelDataInPandasDataFrame . to_excel("./YourNewExcel.xlsx",index=False). How do I go to a specific column in Python?This is the most basic way to select a single column from a dataframe, just put the string name of the column in brackets. Returns a pandas series. Passing a list in the brackets lets you select multiple columns at the same time.
How do you write in an Excel cell in Python?Openpyxl write to a cell
There are two basic ways to write to a cell: using a key of a worksheet such as A1 or D3, or using a row and column notation with the cell method. In the example, we write two values to two cells. Here, we assing a numerical value to the A1 cell.
How do I write text in Excel using Python?It is an ideal way to write data and format information to files with . xls extension.. # import xlsxwriter module.. import xlsxwriter.. book = xlsxwriter.Book('Example2.xlsx'). sheet = book.add_sheet(). # Rows and columns are zero indexed.. row = 0.. column = 0.. content = ["Parker", "Smith", "John"]. |