Velocity like Excel template engine

VeloPoi.java

/*
 * VeloPoi.java
 *
 * Created on 2005/02/19, 9:09
 */

package velopoi;

import java.io.IOException;
import java.lang.reflect.Array;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Stack;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import ognl.Ognl;
import ognl.OgnlException;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.iterators.ArrayIterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 *
 * @author kishida
 */
public class VeloPoi {
    private static class IteratorStack{
        Iterator ite;
        int rownum;
        String var;
        int line;
        IteratorStack(int num, String v, Iterator i){
            ite = i;
            var = v;
            line = 0;
            rownum = num;
        }
    }
    
    /**
     *テンプレートからワークブックを生成する
     *@param filesysytem テンプレートのExcel
     *@param variables 変数のマップ
     *@return 生成ワークブック
     */
    public static HSSFWorkbook merge(POIFSFileSystem filesystem, Map variables) throws IOException{
        HSSFWorkbook wb = new HSSFWorkbook(filesystem);
        HSSFSheet sheet = wb.getSheetAt(0);
        
        //生成Excelドキュメント
        HSSFWorkbook wbout = new HSSFWorkbook();
        HSSFSheet sheetout = wbout.createSheet();
        wbout.setSheetName(0, "テスト", HSSFWorkbook.ENCODING_UTF_16);
        
        //シート幅のコピー
        sheetout.setDefaultColumnWidth(sheet.getDefaultColumnWidth());
        sheetout.setDefaultRowHeight(sheet.getDefaultRowHeight());
        int maxcolumn = -1;
        
        //正規表現の準備
        String reg = "\\$\\{(.+)\\}";
        Pattern patel = Pattern.compile(reg);
        reg = "#eachrow\\s+(\\w+)\\s*:\\s*(.+)";
        Pattern pater = Pattern.compile(reg);
        reg = "#ifrow\\s*\\(\\s*(.+)\\s*\\)";
        Pattern patif = Pattern.compile(reg);
        reg = "#else";
        Pattern patelse = Pattern.compile(reg);
        reg = "#style\\s*\\(\\s*(.+)\\s*\\)\\s*([a-zA-Z]+)([0-9]+)\\s+(.*)";
        Pattern patstyle = Pattern.compile(reg);
        
        Stack<IteratorStack> stk = new Stack<IteratorStack>();
        boolean rowskipping = false;
        boolean ifskipping = false;
        int outrownum = 0;
        HSSFRow outrow = sheetout.createRow(0);
        Map<HSSFCellStyle, HSSFCellStyle> styleMap = new HashMap<HSSFCellStyle, HSSFCellStyle>();
        Map<Short, HSSFFont> fontMap = new HashMap<Short, HSSFFont>();
        for(int i = 0; i <= sheet.getLastRowNum(); ++i){
            System.out.println(i);
            HSSFRow row = sheet.getRow(i);
            if(row == null) continue;
            if(rowskipping){
                //行の処理を飛ばすとき
                HSSFCell cell = row.getCell((short)0);
                if(cell == null) continue;
                String value = cell.getStringCellValue();
                if(ifskipping){
                    //ifの処理中
                    if("#else".equals(value)){
                        rowskipping = !rowskipping;
                    } else if("#corn".equals(value)){
                        rowskipping = false;
                        ifskipping = false;
                    }
                } else{
                    //データのないeachrow中
                    if("#corn".equals(value)){
                        rowskipping = false;
                    }
                }
                continue;
            }
            outrow.setHeight(row.getHeight());
            boolean controlrow = false;
            boolean noline = false;
            for(short j = 0; j < row.getLastCellNum(); ++j){
                if(maxcolumn < j) sheetout.setColumnWidth(j, sheet.getColumnWidth(j));
                HSSFCell cell = row.getCell(j);
                if(cell == null) continue;
                //セルの取得
                HSSFCell outcel = outrow.getCell(j);
                if(outcel == null) outcel = outrow.createCell(j);
                //スタイルのコピー
                HSSFCellStyle source = cell.getCellStyle();
                HSSFCellStyle dest = styleMap.get(source);
                if(dest == null){
                    dest = wbout.createCellStyle();
                    styleMap.put(source, dest);
                    try{
                        BeanUtils.copyProperties(dest, source);
                    }catch(Exception e){
                        e.printStackTrace();
                    }
                    //フォントのコピー
                    short fontidx = source.getFontIndex();
                    HSSFFont font = fontMap.get(fontidx);
                    if(font == null){
                        font = wbout.createFont();
                        fontMap.put(fontidx, font);
                        try{
                            BeanUtils.copyProperties(font, wb.getFontAt(fontidx));
                        }catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                    dest.setFont(font);
                }
                outcel.setCellStyle(dest);
                outcel.setEncoding(HSSFCell.ENCODING_UTF_16);
                
                String value = cell.getStringCellValue();
                Matcher mat;
                Matcher mater = pater.matcher(value);
                try{
                    if((mat = patstyle.matcher(value)).find()){
                        String cond = mat.group(1);
                        String cellrow = mat.group(3);
                        String cellcol = mat.group(2);
                        String v = mat.group(4);
                        System.out.printf("スタイル 条件 %s セル%s:%s 式 %s%n", cond, cellrow, cellcol, v);
                        boolean res = (Boolean)Ognl.getValue("(" + cond + ")==true", variables);
                        if(res){
                            //スタイルを変更する
                            int rownum = Integer.parseInt(cellrow) - 1;
                            int cellnum = cellcol.toLowerCase().charAt(0) - 'a';
                            try{
                                HSSFCell stylecell = sheet.getRow(rownum).getCell((short)cellnum);
                                HSSFCellStyle src = stylecell.getCellStyle();
                                HSSFCellStyle dst = styleMap.get(src);
                                if(dst == null){
                                    dst = wbout.createCellStyle();
                                    styleMap.put(src, dst);
                                    try{
                                        BeanUtils.copyProperties(dst, src);
                                    }catch(Exception e){
                                        e.printStackTrace();
                                    }
                                    //フォントのコピー
                                    short fontidx = src.getFontIndex();
                                    HSSFFont font = fontMap.get(fontidx);
                                    if(font == null){
                                        font = wbout.createFont();
                                        fontMap.put(fontidx, font);
                                        try{
                                            BeanUtils.copyProperties(font, wb.getFontAt(fontidx));
                                        }catch(Exception e){
                                            e.printStackTrace();
                                        }
                                    }
                                    dst.setFont(font);
                                }
                                outcel.setCellStyle(dst);
                            }catch(Exception e){
                                System.out.println(value);
                                e.printStackTrace();
                            }
                        }
                        if((mat = patel.matcher(v)).find()){
                            //式
                            Object o = Ognl.getValue(mat.group(1), variables);
                            if(o instanceof Date){
                                outcel.setCellValue((Date)o);
                            } else{
                                outcel.setCellValue(o.toString());
                            }
                        } else{
                            outcel.setCellValue(v);
                        }
                    } else if((mat = patel.matcher(value)).find()){
                        //式
                        Object o = Ognl.getValue(mat.group(1), variables);
                        if(o instanceof Date){
                            outcel.setCellValue((Date)o);
                        } else{
                            outcel.setCellValue(o.toString());
                        }
                    } else if(mater.find()){
                        //eachrow文
                        String var = mater.group(1);
                        String coll = mater.group(2);
                        Object o = Ognl.getValue(coll, variables);
                        Iterator ite = null;
                        if(o instanceof List){
                            ite = ((List)o).iterator();
                        } else if(o instanceof Array){
                            ite = new ArrayIterator(o);
                        } else{
                            System.out.printf("%sは集合じゃない", coll);
                            rowskipping = true;
                            break;
                        }
                        if(ite.hasNext()){
                            variables.put(var, ite.next());
                            variables.put("line", 0);
                            stk.push(new IteratorStack(i, var, ite));
                        } else {
                            //行データがなければ飛ばす
                            rowskipping = true;
                        }
                        controlrow = true;
                        continue;
                    } else if((mat = patif.matcher(value)).find()){
                        //ifrow
                        String cond = mat.group(1);
                        System.out.printf("if 条件 %s%n", cond);
                        rowskipping = (Boolean)Ognl.getValue("(" + cond + ")!=true", variables);
                        ifskipping = true;
                        controlrow = true;
                        continue;
                    } else if("#else".equals(value)){
                        if(ifskipping){
                            rowskipping = !rowskipping;
                        }
                        controlrow = true;
                        noline = true;
                        continue;
                    } else if("#corn".equals(value)){
                        if(controlrow){
                            //制御文の行範囲を決める
                            controlrow = false;
                            noline = true;
                            continue;
                        }
                        if(ifskipping){
                            rowskipping = false;
                            ifskipping = false;
                            noline = true;
                            continue;
                        }
                        IteratorStack istk = stk.peek();
                        if(istk.ite.hasNext()){
                            //ループの繰り返し
                            ++istk.line;
                            variables.put("line", istk.line);
                            variables.put(istk.var, istk.ite.next());
                            i = istk.rownum;
                        } else{
                            //ループの終わり
                            stk.pop();
                        }
                        noline = true;
                        break;
                    } else{
                        //通常の値
                        outcel.setCellValue(value);
                    }
                } catch(OgnlException e){
                    System.out.printf("構文エラー %s", e.getMessage());
                } catch(RuntimeException e){
                    System.out.printf("%d行の%sで%s%n", i, value, e.getMessage());
                    throw e;
                }
            }
            if(noline){
                noline = false;
            } else{
                ++outrownum;
                outrow = sheetout.createRow(outrownum);
            }
        }
        return wbout;
    }

}

↓

https://nowokay.hatenablog.com/entry/20050219/1195060227
https://nowokay.hatenablog.com/entry/20050526