How to Write Python Array (Data = []) to Excel

How to write Python Array into Excel Spread sheet

Here is one way to do it using the XlsxWriter module:

import xlsxwriter

workbook = xlsxwriter.Workbook('arrays.xlsx')
worksheet = workbook.add_worksheet()

array = [['a1', 'a2', 'a3'],
         ['a4', 'a5', 'a6'],
         ['a7', 'a8', 'a9'],
         ['a10', 'a11', 'a12', 'a13', 'a14']]

row = 0

for col, data in enumerate(array):
    worksheet.write_column(row, col, data)

workbook.close()

Output:

how to write python array (data = []) to excel?

A great file type to be aware of is a CSV, or Comma Separated Value file. It's a very simple text file type (normally already associated with Excel or other spreadsheet apps) where each comma separates multiple cells on the same row and each new line in the file represents data on a new row. I.E.:

A,B,C
1,2,3
"Hello, World!"

The above example would result in the first row having 3 cells, each cell holding each letter. The new line states that 1, 2, and 3 are in the next row, each in their own cell. If a cell needs a comma in it, you can place that cell in quotes. In my example, "Hello, World!" would exist in the 3rd row, 1st cell. For a more formal definition: http://www.csvreader.com/csv_format.php

How to Write Python array into a Single Excel Spread Sheet Cell

Using join() and set_text_wrap()

https://xlsxwriter.readthedocs.io/workbook.html#workbook-add-format
https://xlsxwriter.readthedocs.io/format.html#set_text_wrap

import xlsxwriter

workbook = xlsxwriter.Workbook('arrays.xlsx')
worksheet = workbook.add_worksheet()

cell_format = workbook.add_format()
cell_format.set_text_wrap()
cell_format.set_align('vcenter')

array = [['a1', 'a2', 'a3'],
         ['a4', 'a5', 'a6'],
         ['a7', 'a8', 'a9'],
         ['a10', 'a11', 'a12', 'a13', 'a14']]

row = 0

for col, data in enumerate(array):
    worksheet.write(row, col, '\n'.join(data), cell_format)

workbook.close() 

How to write a 1D array into an existing Excel file starting from a specific cell?

import xlsxwriter
import numpy as np

workbook  = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Sheet3')

worksheet.write_column('E10', np.array([1, 2, 3, 4, 5]))

workbook.close()

To edit an existing file, use the following code.

import openpyxl
import numpy as np
wb = openpyxl.load_workbook("test.xlsx")   
sheet = wb["Sheet3"]
arr = np.array([1, 2, 3, 4, 5])
r = 10
for i in range(len(arr)):
    sheet.cell(row = r + i, column = 5).value = arr[i]
wb.save("test.xlsx")

How to export 2d array into Excel sheets using python using Pandas data frame?

You transpose the matrix/array by doing the

.T

thus your results is orientated differently:

simply doing without the transformation gives your desired result:

import pandas as pd

array = [ ["N1","N2", "N3"], ["N4", "N5","N6"], ["N7","N8","N9"], ["N10", "N11", "N12"]]
result = pd.DataFrame(array) #remove the .T here
result.to_excel('clean_data.xlsx')

How Do I Write Google Sheets Data to Microsoft Excel?

This may not be correct but my answer is a bit too much to put in a comment.

Where you're doing:

if not values:
    print('No data found.')
else:
    wb = Workbook()
    ws = wb.active  
    # Add new row at bottom
    ws.append([values])
    wb.save("users.xlsx")  # Write to disk

I would try

import pandas
if not values:
    print('No data found.')
else:
    dataframe = pandas.DataFrame([values])
    dataframe.to_excel('excel_out.xlsx')

Write Array to Excel Range

This is an excerpt from method of mine, which converts a DataTable (the dt variable) into an array and then writes the array into a Range on a worksheet (wsh var). You can also change the topRow variable to whatever row you want the array of strings to be placed at.

object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
    DataRow dr = dt.Rows[r];
    for (int c = 0; c < dt.Columns.Count; c++)
    {
        arr[r, c] = dr[c];
    }
}
Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1];
Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count];
Excel.Range range = wsh.get_Range(c1, c2);
range.Value = arr;

Of course you do not need to use an intermediate DataTable like I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.

---

Related Topics