参考 ドラッグ&ドロップ開発で利用するインポートツールのソースコード

package jp.jasminesoft.jfc.tools.repository.converter.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import jp.jasminesoft.jfc.core.util.FileUtils;
import jp.jasminesoft.jfc.tools.repository.RepositoryIO;
import jp.jasminesoft.jfc.tools.repository.converter.ConvertProcessorBase;
import jp.jasminesoft.jfc.tools.xls2appschema.ModelInfo;
import jp.jasminesoft.jfc.tools.xls2appschema.ModelitemInfo;
import jp.jasminesoft.util.ExcelFunction;
import jp.jasminesoft.util.StringUtil;
/**
 * jp.jasminesoft.jfc.tools.repository.converter.rdb.ExcelConverterProcessor
 * 
 * excludeSheet=xx ; 除外シート名を指定する。正規表現を書ける。
 * includeSheet=xx ; 適用シート名を指定する。正規表現を書ける。
 * datafolder=xx ; export/data_XXX の XXX を指定する。省略時は実行時の日付時刻。
 * 
 * シート名は シート名;<モデルID> という表記ができる。そうでない場合、モデルID は model1 から連番で割り当てられる。
 * 
 * @since R8.0.3
 */
public class ExcelConverterProcessor extends ConvertProcessorBase {
    private Logger logger = Logger.getLogger(ExcelConverterProcessor.class);
    protected File ExportFolder = new File(RepositoryIO.getRepositoryDirectory(), ".."+SEP+".."+SEP+"export");
    protected File ExportDataFolder;
    protected File ExportDataInitLoaderPropertiesFile;
    private Workbook workbook;
    final static int [] intRangeTable = { 
        9, // 1 
        99, // 2
        999, // 3
        9999, // 4
        99999, // 5
        999999, // 6
        9999999, // 7
        99999999, // 8
        999999999  // 9
    };
    @Override
    public void init(Map<String, String> environment, List<String> messageList) throws IOException {
        super.init(environment, messageList);
        String filename = environment.get("filename");
        if (StringUtils.isBlank(filename)) {
            messageList.add("[ERROR] No file is specified.");
            return;
        }
        String datafolder_suffix = environment.get("datafolder");
        if (StringUtils.isBlank(datafolder_suffix)) {
            datafolder_suffix = ExcelFunction.TEXT(new java.util.Date(), "yyyyMMddHHmmss");
        }
        ExportDataFolder = new File(ExportFolder, "data_" + datafolder_suffix + SEP + "init");
        if (ExportDataFolder.exists()) {
            FileUtils.deleteDirectory(ExportDataFolder);
            new File(ExportFolder, "data_" + datafolder_suffix).mkdir();
            new File(ExportFolder, "data_" + datafolder_suffix + SEP + "init").mkdir();
        }
        ExportDataInitLoaderPropertiesFile = new File(ExportFolder, "data_" + datafolder_suffix + SEP + "initloader.properties");
        if (ExportDataInitLoaderPropertiesFile.exists()) {
            FileUtils.deleteQuietly(ExportDataInitLoaderPropertiesFile);
        }
        try {
            workbook = WorkbookFactory.create(new FileInputStream(filename));
        } catch (Exception e) {
            workbook = null;
            logger.error("読み込み失敗。"+filename, e);
            throw new IOException(e.getMessage());
        }
        if (debug) {
            System.out.println("environment="+environment);
            System.out.println("ExportDataFolder="+ExportDataFolder.getAbsolutePath());
        }
    }
    @Override
    public void process() {
        if (workbook == null) return;
        //シート記載順に意味がある。モデル参照で最初にみつかったものから参照、とする。
        Map<String,ModelInfo> entityMap = new LinkedHashMap<String,ModelInfo>();
        Map<String,Sheet> sheetMap = new HashMap<String,Sheet>();
        int menuorderBase = 100000 - 10000; // 起点
        int countOfMenu = 0;
        // (1) シート一覧を読み込みます。
        try {
            // 除外モデルの対応
            // includeSheet と excludeSheet の両方が指定されていたときは、includeSheet を
            // 優先して、excludeSheet 指定は無視する。
            List<Pattern> excludeSheetPattern = null;
            List<Pattern> includeSheetPattern = null;
            {
                String value = environment.get("includeSheet");
                if (StringUtils.isNotEmpty(value)) {
                    includeSheetPattern = new ArrayList<Pattern>();
                    String[] ss = value.split(",");
                    for (String regex : ss) {
                        Pattern p = Pattern.compile(regex);
                        includeSheetPattern.add(p);
                    }
                }
            }
            if (includeSheetPattern == null) {
                String value = environment.get("excludeSheet");
                if (StringUtils.isNotEmpty(value)) {
                    excludeSheetPattern = new ArrayList<Pattern>();
                    String[] ss = value.split(",");
                    for (String regex : ss) {
                        Pattern p = Pattern.compile(regex);
                        excludeSheetPattern.add(p);
                    }
                }
            }
            int numOfModel = 1;
            int sheetSize = workbook.getNumberOfSheets();
            if (debug) { System.out.println("sheet size is "+sheetSize); }
            for (int sheetIndex = 0; sheetIndex < sheetSize; sheetIndex++) {
                String modelId = null;
                String remarks = null;
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                boolean skipFlag = false;
                String sheetName = sheet.getSheetName();
                int ptr = sheetName.indexOf(";");// 仕様 シート名;モデルID、という表記が行える。
                if (ptr > 0) {
                    modelId = sheetName.substring(ptr+1);
                    remarks = sheetName.substring(0,ptr);
                    if (debug) { System.out.println("modelId="+modelId+", remarks="+remarks); }
                } else {
                    remarks = sheetName;
                }
                if (debug) { System.out.println("sheetIndex:"+sheetIndex+", sheetName="+remarks); }
                if (includeSheetPattern != null) {
                    skipFlag = true;
                    for (Pattern p : includeSheetPattern) {
                        Matcher m = p.matcher(remarks);
                        if (m.find()) {
                            skipFlag = false;
                            break;
                        }
                    }
                } else if (excludeSheetPattern != null) {
                    for (Pattern p : excludeSheetPattern) {
                        Matcher m = p.matcher(remarks);
                        if (m.find()) {
                            skipFlag = true;
                            break;
                        }
                    }
                }
                if (skipFlag) { 
                    logger.info("シート "+modelId+" の読み込みをスキップします。");
                    continue;
                }
                logger.info("シート "+sheetName+" を読み込んでいます...");
                String mname = null;
                String warnmsg = (modelId != null) ? isValidModelId(modelId) : null;
                if (modelId == null || warnmsg != null) {
                    mname = "model"+(numOfModel++);
                    while ((warnmsg = isValidModelId(mname)) != null) {
                        mname = "model"+(numOfModel++);
                    }
                    logger.warn(modelId+" を "+mname+" に改名します。");
                } else {
                    mname = modelId;
                }
                ModelInfo minfo = createModelInfo(mname, remarks, remarks);
                Map<String, String> modelInfoMap = minfo.getModelInfoMap();
                modelInfoMap.put("action/@menurefShowlist", "true");//検索画面への遷移をメニューに配置する
                modelInfoMap.put("model/@presentationShowListOnescreen", "true");// 検索画面と一覧表示を同一画面で扱う
                modelInfoMap.put("presentation/displaymodel/@showListDisplayType", "true");// グリッド形式表示
                modelInfoMap.put("action/initselect=false=", "");// 検索画面遷移直後の検索処理を止める
                modelInfoMap.put("action/@rest", "true");// REST API を有効にする
                int myMenuOrder = menuorderBase + countOfMenu * 100;
                modelInfoMap.put("action/@menuorderSelect", String.valueOf(myMenuOrder)); // メニューならび
                modelInfoMap.put("model/@menuorder", String.valueOf(menuorderBase));
                repman.getModelMap().put(mname, minfo);
                repman.saveModelMap(mname);
                entityMap.put(mname, minfo);
                sheetMap.put(mname, sheet);
                countOfMenu++;
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        // データを一時的に保存するマップ
        Map<String, Map<Integer, Map<String,String>>> datas = new HashMap<String, Map<Integer, Map<String,String>>>();
        // (2) シートごとの詳細を読み込みます。entityMapはシートの登場順に並んでいるので、これを使うこと。
        // (2-1) 先頭行からヘッダを読み込み、項目名一覧を用意します。
        int processCount = 0;
        Map<String,Field[]> fieldInfoMap = new HashMap<String,Field[]>();
        for (String modelId : entityMap.keySet()) {
            Sheet sheet = sheetMap.get(modelId);
            Row row = sheet.getRow(0);
            int first = row.getFirstCellNum();
            int last = row.getLastCellNum();
            if (debug) { System.out.println("first=" + first + ", last=" + last); }
            Field[] fields = readHeader(row, first, last);
            fieldInfoMap.put(modelId, fields);
        }
        // (2-2) 主キーを決定します。自動で "ID" 項目を追加する可能性があります。
        // また、二行目以降のデータを読み込んで、項目の「型」を決めます。この段階ではモデル参照はわかりません。
        for (String modelId : entityMap.keySet()) {
            Field[] fields = fieldInfoMap.get(modelId);
            Map<Integer, Map<String,String>> datasByModel = datas.get(modelId);
            if (datasByModel == null) {
                datasByModel = new TreeMap<Integer, Map<String,String>>();
                datas.put(modelId, datasByModel);
            }
            boolean isAutoId = false;
            if (debug) System.out.println("read pkey info..."); 
            List<String> pkeyList = readPrimaryKeys(fields);
            if (debug) System.out.println("pkeyList="+pkeyList);
            if (pkeyList.size() == 0) {// 主キー項目を追加する必要がある。
                isAutoId = true;
                Field field = new Field();
                field.name = createAutoPkeyName(fields);
                if (debug) System.out.println("create original pkye item,"+field.name);
                field.type = "number";
                field.isPrimaryKey = true;
                field.remarks = "ID";
                field.is_autoincrement = true;
                fields = ArrayUtils.insert(0, fields, field);
                fieldInfoMap.put(modelId, fields);// 新しい項目名情報を再セットする。
                pkeyList.add(field.name);
            }
            Sheet sheet = sheetMap.get(modelId);
            Row row = sheet.getRow(0);
            int first = row.getFirstCellNum();
            int last = row.getLastCellNum();
            int rowNum = sheet.getLastRowNum();
            for (int i=1; i<=rowNum; i++) {
                row = sheet.getRow(i);
                if (debug) System.out.println("read row data "+i+"...");
                Map<String,String> dataMap = readData(modelId, i, row, fields, first, last, isAutoId);
                datasByModel.put(i, dataMap);
            }
        }
        // (2-3) モデル参照関係を整理し、モデル項目情報をリポジトリとして書き込みます。
        for (String modelId : entityMap.keySet()) {
            Field[] fields = fieldInfoMap.get(modelId);
            ModelInfo minfo = entityMap.get(modelId);
            List<String> pkeyList = readPrimaryKeys(fields);
            Map<Integer, Map<String,String>> datasByModel = datas.get(modelId);
            int lineNumber = 1;
            int numOfModelitem = 1;
            for (Field field : fields) {
                Map<ModelitemInfo, String> modelitemInfoMap = minfo.getModelitemInfoMap();
                String label = field.remarks;
                if (label == null || label.length() == 0) {
                    label = field.name;
                }
                String item = null;// 2016.1.24
                String warnmsg = isValidModelitem(field.name);
                if (warnmsg != null) {
                    item = "item"+(numOfModelitem++);
                    logger.warn(field.name+" を "+item+" に改名します。");
                } else {
                    item = field.name;
                }
                if (debug) System.out.println("processing "+item+","+label+"...");
                modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@label", item), label);
                modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@labelWithoutLayout", item), label);
                modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@name", item), item);
                modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@name", item), item);
                modelitemInfoMap.put(new ModelitemInfo("ref_model/@name", item), modelId);
                modelitemInfoMap.put(new ModelitemInfo("action/condition/@cond", item), "");
                // 参照関係かどうかを調べる
                for (String refId : entityMap.keySet()) {
                    if (refId.equals(modelId)) continue;
                    Field[] o_fields = fieldInfoMap.get(refId);
                    for (Field o_field : o_fields) {
                        if (field.remarks != null && field.remarks.equals(o_field.remarks)
                                && !field.isPrimaryKey
                                && !"listbox".equals(o_field.type)
                                ) {
                            field.type = "listbox";
                            field.rid = refId;
                            field.ritem = o_field.name;
                            Field ref_field_pkey = null;// 参照先モデルの主キー項目
                            Map<Integer, Map<String,String>> datasByRefModel = datas.get(refId);
                            Field[] ref_fields = fieldInfoMap.get(refId);
                            for (Field ref_field : ref_fields) {
                                if (ref_field.isPrimaryKey) {
                                    ref_field_pkey = ref_field;
                                    break;
                                }
                            }
                            if (ref_field_pkey == null) {
                                logger.warn(refId+" は主キーがありません。");
                                continue;
                            }
                            for (Integer rowIndex : datasByModel.keySet()) {
                                Map<String,String> dataMap = datasByModel.get(rowIndex);
                                String o_value = dataMap.get(field.name);
                                for (Map<String, String> ref_values : datasByRefModel.values()) {
                                    String ref_value = ref_values.get(field.ritem);
                                    if (ref_value.equals(o_value)) {
                                        String ref_id = ref_values.get(ref_field_pkey.name);
                                        dataMap.put(field.name, ref_id);// 参照先モデルの主キーに書き換える。
                                        if (debug) {
                                            System.out.println("[Override] value:"+o_value+" -> "+ref_id);
                                        }
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
                if ("listbox".equals(field.type)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "reference_listbox");
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "reference_listbox");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation/@idref", item),  field.rid);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation/@navigation", item),  "true");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@restrict", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@rolecontent", item),  field.ritem);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@roleid", item),  "pkeyOfRefmodel");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@roleid_2", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item), null);// 範囲検索は無効にする
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("action/condition/@cond", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/range", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@alloption", item), "true");// "すべて" を検索条件に用意する
                } else if ("boolean".equals(field.type)) {
                    // フラグとみなす
                    final String flagModelId = "Flag_m";
                    File flagModelRepository = new File(RepositoryTrunk.getAbsolutePath() + SEP + flagModelId);
                    if (!flagModelRepository.exists()) {
                        createCollectionModel(flagModelId, "二値フラグ", "二値フラグ");
                        int fileId = 1;
                        final String[] choices = { "無効", "有効" };
                        for (String choice : choices) {
                            String itemfolder = flagModelId + SEP + flagModelId+ SEP + ".initialdata";
                            File f = new File(RepositoryTrunk.getAbsolutePath() + SEP + itemfolder);
                            saveMasterData(fileId, flagModelId, String.valueOf(fileId), choice, f);
                            fileId++;
                        }
                    }
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "reference_radiobutton"/*"他のストアモデルの参照(ラジオボタン)"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation/@idref", item),  flagModelId);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@rolecontent", item),  "content");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@roleid", item),  "pkeyOfRefmodel"/*"参照モデルの主キー"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation/@navigation", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@roleorder", item),  "descending"/*"降順"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@rolepriority", item),  "priority");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@rolevalid", item),  "valid");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@sortrule", item),  "priority"/*"表示優先度"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation_storeref/@restrict", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/relation/@navigation", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "reference_radiobutton"/*"他のストアモデルの参照(ラジオボタン)"*/);
                    modelitemInfoMap.put(new ModelitemInfo("action/condition/@cond", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item), null);// 範囲検索は無効にする
                } else if (field.type.equals("date"/*"日付"*/)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "dateFilter"/*"日付型"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "date"/*"日付"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "date"/*"日付"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type_ext", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinput", item),  "datePicker"/*"カレンダの利用"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinputForSearch", item),  "datePicker"/*"カレンダの利用"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item),  "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/format", item),  "yyyy-MM-dd");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/defaultValue[@action=init]", item),  "calc"/*"四則演算"*/);//登録画面の初期値
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/defaultValue[@action=init]_2", item),  "TODAY()");
                    modelitemInfoMap.put(new ModelitemInfo("action/condition/@cond", item),  "");
                } else if (field.type.equals("time"/*"時間"*/)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "dateFilter"/*"日付型"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "time"/*"時間"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "time"/*"時間"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type_ext", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinput", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinputForSearch", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item),  "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/format", item),  "HH:mm");
                } else if (field.type.equals("dateTime"/*"日付と時間"*/)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "dateFilter"/*"日付型"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "dateTime"/*"日付と時間"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "dateTime"/*"日付と時間"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type_ext", item),  "");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinput", item),  "datePicker"/*"カレンダの利用"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@calendarinputForSearch", item),  "datePicker"/*"カレンダの利用"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item),  "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/format", item),  "yyyy-MM-dd HH:mm:ss");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/defaultValue[@action=init]", item),  "calc"/*"四則演算"*/);//登録画面の初期値
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/defaultValue[@action=init]_2", item),  "NOW()");
                } else if ((field.type.equals("string"/*"文字列"*/) || field.type.equals("URL") || field.type.equals("mailaddress"))) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "commonFilter"/*"文字列(一般)"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  field.type);
                    modelitemInfoMap.put(new ModelitemInfo("action/condition/@cond", item),  "partial"/*"文字列の部分一致"*/);
                    modelitemInfoMap.put(new ModelitemInfo("action/condition/@andorsearch", item),  "true"/*"○"*/);
                    if (field.column_size > 255) {// テキストエリアにする
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type", item),  "textarea"/*"テキストエリア"*/);
                        //System.out.println("** テキストエリア ("+field.column_size+") "+field.name+" "+modelId);
                    } else {
                        int size = Math.min(field.column_size, 40);// length設定時の入力サイズは最大40文字とする
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@inputmaxlength", item),  String.valueOf(field.column_size));
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@size", item),  String.valueOf(size));
                    }
                } else if (field.type.equals("number"/*"整数"*/)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "intFilter"/*"数字型"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "number"/*"整数"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "number"/*"整数"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@numberformatCombo", item), "###,###,###");
                    if (field.isConditionItem) {
                        modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item),  "true"/*"○"*/);
                    }
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type", item),  "numberformat"/*"数字"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type_ext", item),  "numberformat"/*"数字"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/chartype\\=number", item),  "true"/*"○"*/);
                    if (field.column_size > 0) {
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@size", item),  String.valueOf(field.column_size+5));
                        if (field.column_size <= 9) {
                            modelitemInfoMap.put(new ModelitemInfo("model/modelitem/range", item),  "0-"+intRangeTable[field.column_size-1]);
                        }
                    }
                } else if (field.type.equals("double"/*"8バイト浮動小数点"*/)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@filter", item),  "intFilter"/*"数字型"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@type", item),  "double"/*"8バイト浮動小数点"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@type", item),  "double"/*"8バイト浮動小数点"*/);
                    if (field.isConditionItem) {
                        modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@rangeCondition", item),  "true"/*"○"*/);
                    }
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type", item),  "numberformat"/*"数字"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@type_ext", item),  "numberformat"/*"数字"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/chartype\\=number", item),  "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@size", item),  "20"); // デフォルトとする。
                    if (field.decimal_digits > 0) {
                        String fmt = "";
                        for (int k=0; k<field.decimal_digits; k++) {
                            fmt = fmt + "#";
                        }
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@numberformat", item),  "###."+fmt);
                    } else {
                        modelitemInfoMap.put(new ModelitemInfo("presentation/displayitem/@numberformat", item),  "###.##");
                    }
                }
                if (field.isConditionItem) {
                    if (debug) System.out.println("field "+field.remarks+" ("+field.name+") -> condition");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@hasConditionModel", item), "true"/*"○"*/);// 検索条件に加える
                } else {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@hasConditionModel", item), "");
                }
                if (field.isListItem) {
                    if (debug) System.out.println("field "+field.remarks+" ("+field.name+") -> list");
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@hasListModel", item), "true"/*"○"*/); // 文字列は一覧表示に加える
                } else {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@hasListModel", item), "");
                }
                // 主キー
                if (pkeyList.contains(field.name)) {
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@primaryKey", item), "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("model/modelitem/notnull", item), "true"/*"○"*/);
                    modelitemInfoMap.put(new ModelitemInfo("ref_model/modelitem/@primaryKey", item), "true"/*"○"*/);
                    if (field.type.equals("number"/*"整数"*/) && field.is_autoincrement) {
                        modelitemInfoMap.put(new ModelitemInfo("model/primaryKey/@autoid", item), "true"/*"○"*/);
                    }
                }
                modelitemInfoMap.put(new ModelitemInfo("model/modelitem/@__linenumber", item),  Integer.toString(lineNumber++));
                repman.getModelMap().put(modelId, minfo);
                repman.saveModelMap(modelId);
            }
            processCount++;
        }
        // (2-4) データを保存する
        StringBuilder sb = new StringBuilder();
        for (String modelId : entityMap.keySet()) {
            Map<Integer, Map<String,String>> datasByModel = datas.get(modelId);
            for (Integer rowIndex : datasByModel.keySet()) {
                Map<String,String> map = datasByModel.get(rowIndex);
                saveModelData(rowIndex, modelId, map);
            }
            sb.append("InitLoader.file.count.").append(modelId).append("=").append(datasByModel.size()).append(CR);
        }
        // (2-5) initloader.properts を作成する
        try {
            if (debug) System.out.println(sb);
            FileUtils.writeStringToFile(ExportDataInitLoaderPropertiesFile, sb.toString(), "UTF-8");
        } catch (IOException e) {
            System.err.println("Write error,"+ExportDataInitLoaderPropertiesFile.getAbsolutePath());
        }
        logger.info("コンバートしたテーブル数は "+String.valueOf(processCount)+" です。");
    }
    private String createAutoPkeyName(Field[] fields) {
        return createAutoPkeyName(fields, 1);
    }
    private String createAutoPkeyName(Field[] fields, int suffix) {
        String candidate = "ID";
        if (suffix > 1) {
            candidate = candidate + suffix;
        }
        for (Field field : fields) {
            if (candidate.equals(field.name)) {
                return createAutoPkeyName(fields, ++suffix);
            }
        }
        return candidate;
    }
    class Field {
        String name;
        String type;
        String remarks;
        int column_size;
        int decimal_digits;
        boolean is_autoincrement;
        boolean isPrimaryKey;
        boolean isConditionItem;
        boolean isListItem;
        String rid;
        String ritem;
        public String toString() {
            return remarks+"("+name+")";
        }
    }
    private Field[] readHeader(Row row, int firstRowNum, int lastRowNum) {
        if (debug) System.out.println("read header...");
        List<Field> list = new ArrayList<Field>();
        int index = 1;
        for (int i=firstRowNum; i<=lastRowNum; i++) {
            if (debug) System.out.println("read cell("+i+")...");
            Cell cell = row.getCell(i);
            if (cell == null) {
                if (debug) System.out.println("cell is null, continue.");
                continue;
            }
            Field item = createField(getCellValue(cell), index++);
            list.add(item);
        }
        return list.toArray(new Field[0]);
    }
    private String getCellValue(Cell cell) {
        String value = null;
        if (debug) System.out.println("cell type="+cell.getCellType());//+",formula="+cell.getCellFormula());
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                value = ExcelFunction.TEXT(cell.getDateCellValue(), "yyyy-MM-dd");
            } else {
                value = String.valueOf(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        default:
            value = cell.getStringCellValue();
        }
        return value;
    }
    private Field createField(String s, int index) {
        if (debug) System.out.println("createField, s="+s+",index="+index);
        Field item = new Field();
        if (StringUtils.isBlank(s)) {
            s = "";
        }
        int ptr = s.indexOf(";");
        if (ptr < 0) {
            // 項目名日本語のみ。
            item.name = "item" + index;
            item.remarks = s;
            item.isConditionItem = true;
            item.isListItem = true;
        } else {
            item.remarks = s.substring(0,ptr);
            s = s.substring(ptr+1);
            ptr = s.indexOf(";");
            if (ptr < 0) {
                // 項目名日本語;英語
                item.name = s;
                item.isConditionItem = true;
                item.isListItem = true;
            } else {
                item.name = s.substring(0,ptr);
                s = s.substring(ptr+1);
                s = StringUtil.removePreAndPostWhitespace(s);
                if (s.indexOf("K") >= 0) {
                    item.isPrimaryKey = true;
                    s = s.replace("K", "");
                }
                if (s.equals("CL") || s.equals("LC")) {
                    item.isConditionItem = true;
                    item.isListItem = true;
                } else if (s.equals("C")) {
                    item.isConditionItem = true;
                    item.isListItem = false;
                } else if (s.equals("L")) {
                    item.isConditionItem = false;
                    item.isListItem = true;
                } else if (s.equals("-")) {
                    item.isConditionItem = false;
                    item.isListItem = false;
                }
            }
        }
        return item;
    }
    private Map<String,String> readData(String modelId, int rowIndex, Row row, Field[] fields, int firstRowNum, int lastRowNum, boolean isAutoId) {
        Map<String,String> map = new LinkedHashMap<String,String>();
        if (isAutoId) {
            Field field = fields[0];
            map.put(field.name, String.valueOf(rowIndex));
        }
        for (int i=firstRowNum; i<lastRowNum; i++) {
            Cell cell = row.getCell(i);
            Field field = fields[i-firstRowNum + ((isAutoId)?1:0)];
            if (debug) System.out.println("readData ("+rowIndex+"),i="+i+",cell="+cell+",field="+field);
            String value = getCellValue(cell);
            if (debug) System.out.println("  -> value="+value);
            map.put(field.name, value);
            if (field.type == null) {
                int cell_type = cell.getCellType();
                if (cell_type == Cell.CELL_TYPE_NUMERIC) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        field.type = "date";
                    } else {
                        if (value.endsWith(".0")) {
                            value = value.substring(0, value.length()-".0".length());// '1000.0'は'1000'とみなす。
                            if (debug) System.out.println("=== remove '.0' -> "+value);
                            map.put(field.name, value);//上書き
                        }
                        if (value.indexOf(".") > 0) {
                            // 小数点があれば double とする
                            field.type = "double";
                        } else {
                            field.type = "number";
                        }
                    }
                } else if (cell_type == Cell.CELL_TYPE_STRING) {
                    if (value != null && (value.startsWith("http://") || value.startsWith("https://")))  {
                        field.type = "URL";
                    } else if (value != null && value.startsWith("mailto:")) {
                        field.type = "mailaddress";
                    } else {
                        field.type = "string";
                    }
                } else if (cell_type == Cell.CELL_TYPE_BOOLEAN) {
                    field.type = "boolean";
                }
            } else {
                if (field.type.equals("number")) {
                    if (value != null && value.endsWith(".0")) {
                        value = value.substring(0, value.length()-".0".length());// '1000.0'は'1000'とみなす。
                        map.put(field.name, value);//上書き
                    } else if (value != null && value.indexOf(".") > 0) {
                        field.type = "double"; // 二行目以降で小数のデータが見つかった
                    }
                }
            }
            if (StringUtils.isNotBlank(value)) {
                field.column_size = Math.max(field.column_size, value.length());
            }
            if (debug) { System.out.println(rowIndex+": field="+field+",type="+field.type+",value="+value+",length="+field.column_size); }
        }
        return map;
    }
    private void saveModelData(int fileId, String modelId, Map<String,String> map) {
        if (debug) System.out.println("save model data...");
        StringBuilder sb = new StringBuilder();
        sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>");
        sb.append("<").append(modelId).append(">");
        for (String name : map.keySet()) {
            String content = map.get(name);
            sb.append("<").append(name).append(">").append(content).append("</").append(name).append(">");
        }
        sb.append("</").append(modelId).append(">");
        String saveFileName = ExportDataFolder.getAbsolutePath() + SEP + modelId + SEP + fileId + ".xml";
        try {
            if (debug) System.out.println(sb);
            if (debug) System.out.println("-> "+saveFileName);
            FileUtils.writeStringToFile(new File(saveFileName), sb.toString(), "UTF-8");
        } catch (IOException e) {
            System.err.println("Write error,"+saveFileName);
        }
    }
    private List<String> readPrimaryKeys(Field[] fields) {
        List<String> pkeys = new ArrayList<String>();
        for (Field field : fields) {
            if (field.isPrimaryKey) {
                pkeys.add(field.name);
            }
        }
        return pkeys;
    }
}