# Collection of code snippets by Arne Vajhøj 
# (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) 
from java.io import FileOutputStream
from java.util import ArrayList

from  org.apache.poi.ss.usermodel import IndexedColors, FillPatternType
from org.apache.poi.ss.util import CellRangeAddress
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.xddf.usermodel.chart import XDDFDataSourcesFactory, LegendPosition, ChartTypes

from dk.vajhoej.isam.map import PyIsamMap

import AutoMap
import OrdersISAM
import OrderLinesISAM
import Orders
import OrderLines

# read from index-sequential file
oisqmp = PyIsamMap.createIsamMapRMS("orders.isq", OrdersISAM)
olisqmp = PyIsamMap.createIsamMapRMS("orderlines.isq", OrderLinesISAM)
isamdata = ArrayList()
for oisam in oisqmp.values():
    for olisam in olisqmp.key(1).isInt(oisam.id).values():
        oisam.orderLinesT.add(olisam)
    isamdata.add(oisam)
# convert
mo = AutoMap(OrdersISAM, Orders, True)
mol = AutoMap(OrderLinesISAM, OrderLines, True)
data = ArrayList()
for oisam in isamdata:
    o = Orders()
    mo.convert(oisam, o)
    for olisam in oisam.orderLinesT:
        ol = OrderLines()
        mol.convert(olisam, ol)
        o.orderLines.add(ol)
    data.add(o)
# write to Excel
def createCell(rowobj, colix, style):
    c = rowobj.createCell(colix)
    c.setCellStyle(style)
    return c

workbook = XSSFWorkbook()
sheet = workbook.createSheet()
headerstyle = workbook.createCellStyle()
headerstyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index)
headerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
amountstyle = workbook.createCellStyle()
amountstyle.setDataFormat(workbook.createDataFormat().getFormat('#,##0.00'))
row = 0
headerrow = sheet.createRow(row)
createCell(headerrow, 0, headerstyle).setCellValue('Order')
createCell(headerrow, 1, headerstyle).setCellValue('Customer')
createCell(headerrow, 2, headerstyle).setCellValue('Status')
createCell(headerrow, 3, headerstyle).setCellValue('Total')
row = row + 1
for o in data:
    datarow = sheet.createRow(row)
    datarow.createCell(0).setCellValue(o.id)
    datarow.createCell(1).setCellValue(o.customer)
    datarow.createCell(2).setCellValue(o.status)
    datarow.createCell(3).setCellValue(o.totalPrice.doubleValue())
    row = row + 1
datarow = sheet.createRow(row)
datarow.createCell(0).setCellValue('Total')
createCell(datarow, 3, amountstyle).setCellFormula('SUM(D%d:D%d)' % (row - data.size() + 1, row))
row = row + 1
drawing = sheet.createDrawingPatriarch()
anchor = drawing.createAnchor(0, 0, 0, 0, 0, row, 3, row + 9)
chart = drawing.createChart(anchor)
legend = chart.getOrAddLegend()
legend.setPosition(LegendPosition.TOP_RIGHT)
cat = XDDFDataSourcesFactory.fromStringCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 1, 1))
val = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress(row - data.size() - 1, row - 2, 3, 3))
piedata = chart.createData(ChartTypes.PIE, None, None)
piedata.setVaryColors(True)
piedata.addSeries(cat, val)
chart.plot(piedata)
os = FileOutputStream('orders4.xlsx')
workbook.write(os)
os.close()
workbook.close()
#
print('isq2excel done')