CODEWITHSUNDEEP
pythonexcelnumpy
edesz
edesz

Reputation: 12406

python write to EXCEL cell range (NumPy?)

I am trying to write a matrix prepared with NumPy to an EXCEL file. I need to specify a range of cells in which the matrix must be written.

I need to write the matrix to cells A4:Z512 in sheet 4 of the EXCEL file.

Now, the standard EXCEL file has 3 sheets, so I need to first add a 4th sheet and then write the matrix to it.

Is there a way to do this in python 2.7? Is it possible to do this with pure NumPy or not?

Upvotes: 0

Views: 2683

Answers (1)

David C
David C

Reputation: 2038

I have not used NumPy, so I am not sure if you can manipulate excel files. But for working on excel files I recommend using the win32com library. Below is some code I have used in the past to make win32com API easier to use. Feel free to use the code yourself. Hope this helps!

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')

def openExcel(makeExcelVisible=True):
    excel.Visible = makeExcelVisible

def closeExcel():
    excel.Application.Quit()

class ExcelFile(object):
# opens up a workbook to work on, not selecting a file name creates a new one
    def __init__(self, fileName=None):
        if fileName == None:
            self.wb = excel.Workbooks.Add()
        else:
            self.wb = excel.Workbooks.Open(fileName)
        self.ws = None

    def addWorksheet(self):
    # adds a new worksheet to the workbook and makes it the current worksheet
        self.ws = self.wb.Worksheets.Add()

    def selectWorksheet(self, worksheetName):
    # selects a worksheet to work on
        self.ws = self.wb.Worksheets(worksheetName)

    def renameWorksheet(self, worksheetName):
    # renames current worksheet
        self.ws.Name = worksheetName

    def save(self):
    # saves the workbook
        self.wb.Save()

    def saveAs(self, fileName):
    # saves the workbook with the file name
        self.wb.SaveAs(fileName)

    def close(self):
    # closes the workbook
        self.wb.Close()

    def insertIntoCell(self, cellRow, cellCol, data):
        self.ws.Cells(cellRow,cellCol).Value = data

    def clearCell(self, cellRow, cellCol):
        self.ws.Cells(cellRow,cellCol).Value = None

Here is an example of how to use the code above. It creates a new excel file, renames the worksheets, adds information into the first cell on each worksheet and saves the file as "test.xlsx". Default save location is your home directory.

worksheets = ["Servers", "Printers", "Drives", "IT Phones"]
information = ["Server WS", "Printer WS", "Driver WS", "IT Phone WS"]

def changeCells(information):
    excelFile = ExcelFile()
    for index in xrange(len(worksheets)):
        sheetNumber = index + 1
        if sheetNumber == 4:
            excelFile.addWorksheet()
        excelFile.selectWorksheet("Sheet%d" % sheetNumber)
        excelFile.renameWorksheet(worksheets[index])
        excelFile.insertIntoCell(1,1,information[index])
    excelFile.saveAs("test.xlsx")
    excelFile.close()

openExcel()
changeCells(information)
closeExcel()

Also, I would recommend looking at the API for win32com yourself. It's a very nice and useful library.

I put together the actual code you would need for entering your matrix on Sheet4 to A4:Z512.

def addMatrix(matrix):
    # use ExcelFile("fileName.xlsx") if you need to add to a specific file
    excelFile = ExcelFile()
    excelFile.addWorksheet()
    # default excel files only have 3 sheets so had to add one
    excelFile.selectWorksheet("Sheet4")
    # xrange(4,513) since end index is exclusive
    for row in xrange(4,513):
        # 1 for A, 26 for Z
        for col in xrange(1,27):
            mRow = row - 4
            mCol = col - 1
            excelFile.insertIntoCell(row, col, matrix[mRow][mCol])
    excelFile.saveAs("test.xlsx")
    excelFile.close()

matrix = list()
for row in xrange(509):
    matrix.append([])
    for col in xrange(26):
        matrix[row].append(0)
# the matrix is now filled with zeros

# use openExcel(False) to run faster, it won't open a window while running
openExcel()
addMatrix(matrix)
closeExcel()

Upvotes: 1

Related Questions