DMelt:IO/Excel Tables

Table of contents

Excel tables

One can create MS Excel tables using Python / Java syntax is a completely programmable way. The output files can be opened in MS Excel or OpenOffice / Libre office.

Here is a small Jython example that creates a table with custom fields.

from  org.apache.poi.xssf.usermodel import XSSFWorkbook 
from  java.io import *

wb = XSSFWorkbook()
sheet = wb.createSheet("New spreadsheet");
row=sheet.createRow(0)   # create a new row 
row.createCell(0).setCellValue(100)     # cell=0 has value 100
row.createCell(1).setCellValue(200)     # cell=1 has value 200
row.createCell(2).setCellValue("dmelt") # cell=2 has "dmelt"
row.createCell(3).setCellValue(False)   # boolean
row.createCell(4).setCellFormula("SUM(A1:B1)") # formula (sum A+B) 
out=FileOutputStream("table1.xlsx");
wb.write(out)
out.close();

You can open this file in MS Excel and look inside. Or one can build custom files like this:

from org.apache.poi.xssf.usermodel import XSSFColor,XSSFWorkbook 
from org.apache.poi.ss.usermodel  import CellStyle
from java.io import *
from java.util import Date,Random 
from java.awt import Color

wb = XSSFWorkbook()
sheet=wb.createSheet("Custom fields")
helper=wb.getCreationHelper()
style=wb.createCellStyle()
style.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"))
row=sheet.createRow(0)
cell=row.createCell(0)
cell.setCellValue(Date())
cell.setCellStyle(style)

# prepare next cell style 
text= helper.createRichTextString("Test")
style=wb.createCellStyle()
style.setAlignment(CellStyle.ALIGN_CENTER);
font = wb.createFont()
font.setItalic(True)
font.setColor(XSSFColor(Color.red))
text.applyFont(font)
# create this cell
cell=row.createCell(1)
cell.setCellValue(text)
cell.setCellStyle(style)

out=FileOutputStream("table3.xlsx")
wb.write(out)
out.close()