How to write an Excel file from Flux ?
Altair EmployeeAnswers
-
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()0