How to write an Excel file from Flux ?

SimonGuicheteau
SimonGuicheteau
Altair Employee
edited June 2021 in Community Q&A

How can we write an excel file from Flux ? Which library is available ?

Tagged:

Answers

  • SimonGuicheteau
    SimonGuicheteau
    Altair Employee
    edited June 2021

    The main library to be used is called Apache POI.

    Here is an example I built to set sheets, cells and even formulas using either a manual input or the same row,column number that the cells created. 

    Please note you have to create the cell to enter something in it.

     

    Example:

     

    #! Flux2D 21.0
    from java.io import FileOutputStream
    from java.util import Date
    from java.lang import System, Math
    from org.apache.poi.hssf import *
    from org.apache import *
    from org.apache.poi.ss.usermodel import *
    from org.apache.poi.ss.usermodel.CellStyle import *
    from org.apache.poi.hssf.usermodel import *
    from org.apache.poi.hssf.util import *


    #Creating workbook
    wb = HSSFWorkbook()
    fileOut = FileOutputStream("test1706.xls")

    #Creating sheet
    sheet1 = wb.createSheet("first sheet")

    #creating empty cells first 30x10
    Cells=[]
    CellsNames=[]
    abc=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
    nb_rows=30
    nb_cols=10
    for row in range(nb_rows):
    rowi=sheet1.createRow(row)
    coli=[]
    coliname=[]
    for col in range(nb_cols):
    coli.append(rowi.createCell(col))
    coliname.append((abc[col]+str(row+1)))
    Cells.append(coli)
    CellsNames.append(coliname)

    #setting a value
    Cells[20][5].setCellValue(111) # corresponds to F21 (not E20)
    Cells[20][6].setCellValue(111) # corresponds to G21 (not F20)

    #setting a formula
    # using a text formula
    Cells[20][8].setCellType(HSSFCell.CELL_TYPE_FORMULA)# corresponds to G21 (not F20)
    Cells[20][8].setCellFormula('F21+G21')
    # using a cells numbers as defined previously
    Cells[20][9].setCellType(HSSFCell.CELL_TYPE_FORMULA)# corresponds to H21 (not F20)
    Cells[20][9].setCellFormula(CellsNames[20][5]+'+'+CellsNames[20][6])


    #Other sheet
    sheet2 = wb.createSheet("second sheet")
    sheet2.createRow(5).createCell(3).setCellValue(222)

    wb.write(fileOut)
    fileOut.close()