package mara.mybox.data2d.tools;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import javafx.scene.paint.Color;
import mara.mybox.data.StringTable;
import mara.mybox.data2d.Data2D;
import mara.mybox.data2d.DataFileCSV;
import mara.mybox.data2d.DataFileExcel;
import mara.mybox.db.data.ColumnDefinition;
import mara.mybox.db.data.ColumnDefinition.ColumnType;
import mara.mybox.db.data.Data2DColumn;
import mara.mybox.db.data.Data2DDefinition;
import mara.mybox.db.data.DataNode;
import mara.mybox.dev.MyBoxLog;
import mara.mybox.tools.CsvTools;
import mara.mybox.tools.FileTmpTools;
import mara.mybox.tools.FileTools;
import mara.mybox.tools.HtmlWriteTools;
import mara.mybox.tools.JsonTools;
import mara.mybox.tools.StringTools;
import mara.mybox.tools.XmlTools;
import static mara.mybox.tools.XmlTools.cdata;
import mara.mybox.value.AppValues;
import static mara.mybox.value.Languages.message;
import mara.mybox.value.UserConfig;
import org.apache.commons.csv.CSVPrinter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * @Author Mara
 * @CreateDate 2023-9-12
 * @License Apache License Version 2.0
 */
public class Data2DDefinitionTools {

    public static List<Data2DColumn> columns() {
        List<Data2DColumn> columns = new ArrayList<>();
        columns.add(new Data2DColumn(message("ColumnName"), ColumnType.String));
        columns.add(new Data2DColumn(message("Type"), ColumnType.String));
        columns.add(new Data2DColumn(message("Label"), ColumnType.String));
        columns.add(new Data2DColumn(message("Length"), ColumnType.String));
        columns.add(new Data2DColumn(message("Width"), ColumnType.String));
        columns.add(new Data2DColumn(message("DisplayFormat"), ColumnType.String));
        columns.add(new Data2DColumn(message("NotNull"), ColumnType.Boolean));
        columns.add(new Data2DColumn(message("Editable"), ColumnType.Boolean));
        columns.add(new Data2DColumn(message("PrimaryKey"), ColumnType.Boolean));
        columns.add(new Data2DColumn(message("AutoGenerated"), ColumnType.Boolean));
        columns.add(new Data2DColumn(message("DefaultValue"), ColumnType.String));
        columns.add(new Data2DColumn(message("Color"), ColumnType.String));
        columns.add(new Data2DColumn(message("ToInvalidValue"), ColumnType.String));
        columns.add(new Data2DColumn(message("DecimalScale"), ColumnType.String));
        columns.add(new Data2DColumn(message("Century"), ColumnType.String));
        columns.add(new Data2DColumn(message("FixTwoDigitYears"), ColumnType.Boolean));
        columns.add(new Data2DColumn(message("Description"), ColumnType.String));
        return columns;
    }

    public static String defInfo(Data2DDefinition def) {
        String info = message("ID") + ": " + def.getDataID() + "\n";
        info += message("Type") + ": " + def.getTypeName() + "\n";
        info += message("DataName") + ": " + def.getDataName() + "\n";
        info += message("Sheet") + ": " + def.getSheet() + "\n";
        info += message("File") + ": " + def.getFile() + "\n";
        info += message("ColumnsNumber") + ": " + def.getColsNumber() + "\n";
        String des = def.getComments();
        if (des != null && !des.isBlank()) {
            info += message("Description") + ": " + des;
        }
        info += message("Pagination") + ": \n" + def.pagination.info() + "\n";
        return info;
    }

    public static String dataInfo(Data2D data2d) {
        String info = defInfo(data2d);
        info += message("DecimalScale") + ": " + data2d.getScale() + "\n";
        info += message("MaxRandom") + ": " + data2d.getMaxRandom() + "\n";
        info += message("Columns") + ": " + data2d.columnNames();
        return info;
    }

    public static String toHtml(Data2D data2d) {
        try {
            if (data2d == null) {
                return null;
            }
            StringTable attrTable = new StringTable();
            List<String> row = new ArrayList<>();
            row.addAll(Arrays.asList(message("DataName"), data2d.getDataName()));
            attrTable.add(row);
            row = new ArrayList<>();
            row.addAll(Arrays.asList(message("DecimalScale"), data2d.getScale() + ""));
            attrTable.add(row);
            row = new ArrayList<>();
            row.addAll(Arrays.asList(message("MaxRandom"), data2d.getMaxRandom() + ""));
            attrTable.add(row);
            row = new ArrayList<>();
            String comments = data2d.getComments();
            if (comments != null && !comments.isBlank()) {
                row.addAll(Arrays.asList(message("Description"), HtmlWriteTools.codeToHtml(comments)));
            }
            attrTable.add(row);
            String html = attrTable.div();
            List<Data2DColumn> columns = data2d.getColumns();
            if (columns == null || columns.isEmpty()) {
                return html;
            }
            List<String> names = new ArrayList<>();
            names.addAll(Arrays.asList(message("ColumnName"), message("Type"),
                    message("Label"), message("Length"),
                    message("NotNull"), message("PrimaryKey")));
            StringTable columnsTable = new StringTable(names);
            for (ColumnDefinition column : columns) {
                row = new ArrayList<>();
                row.add(column.getColumnName());
                row.add(message(column.getType().name()));
                row.add(column.getLabel());
                row.add(column.getLength() + "");
                row.add(column.isNotNull() ? message("Yes") : "");
                row.add(column.isIsPrimaryKey() ? message("Yes") : "");
                columnsTable.add(row);
            }
            html += "<BR>" + StringTable.tableDiv(columnsTable);

            for (ColumnDefinition column : columns) {
                names = new ArrayList<>();
                names.addAll(Arrays.asList(message("Key"), message("Value")));
                StringTable columnTable = new StringTable(names);
                row = new ArrayList<>();
                row.add(message("ColumnName"));
                row.add(column.getColumnName());
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Type"));
                row.add(message(column.getType().name()));
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Label"));
                row.add(column.getLabel());
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Length"));
                row.add(column.getLength() + "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Width"));
                row.add(column.getWidth() + "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("DisplayFormat"));
                String s = column.getFormatDisplay();
                row.add(s == null || s.isBlank() ? null : HtmlWriteTools.codeToHtml(s));
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("NotNull"));
                row.add(column.isNotNull() ? message("Yes") : "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Editable"));
                row.add(column.isEditable() ? message("Yes") : "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("PrimaryKey"));
                row.add(column.isIsPrimaryKey() ? message("Yes") : "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("AutoGenerated"));
                row.add(column.isAuto() ? message("Yes") : "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("DefaultValue"));
                s = column.getDefaultValue();
                row.add(s == null || s.isBlank() ? null : HtmlWriteTools.codeToHtml(s));
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Color"));
                row.add(column.getColor().toString());
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("DecimalScale"));
                row.add(column.getScale() + "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Century"));
                row.add(column.getCentury() + "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("FixTwoDigitYears"));
                row.add(column.isFixTwoDigitYear() ? message("Yes") : "");
                columnTable.add(row);
                row = new ArrayList<>();
                row.add(message("Description"));
                row.add(column.getDescription());
                columnTable.add(row);
                html += "<BR>" + StringTable.tableDiv(columnTable);
            }
            return html;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static String toXML(Data2D data2d, boolean withAttributes, String prefix) {
        try {
            if (data2d == null) {
                return null;
            }
            String indent = AppValues.Indent;
            StringBuilder s = new StringBuilder();
            s.append(prefix).append("<").append(XmlTools.xmlTag("DataDefinition")).append(">\n");
            if (withAttributes) {
                s.append(prefix).append(indent).append("<").append(XmlTools.xmlTag("Attributes")).append(">\n");
                String v = data2d.getDataName();
                if (v != null && !v.isBlank()) {
                    s.append(prefix).append(indent).append(indent).append("<").append(XmlTools.xmlTag("DataName")).append(">").append("<![CDATA[").append(v).append("]]>").append("</").append(XmlTools.xmlTag("DataName")).append(">\n");
                }
                s.append(prefix).append(indent).append(indent).append("<").append(XmlTools.xmlTag("DecimalScale")).append(">").append(data2d.getScale()).append("</").append(XmlTools.xmlTag("DecimalScale")).append(">\n");
                s.append(prefix).append(indent).append(indent).append("<").append(XmlTools.xmlTag("MaxRandom")).append(">").append(data2d.getMaxRandom()).append("</").append(XmlTools.xmlTag("MaxRandom")).append(">\n");
                v = data2d.getComments();
                if (v != null && !v.isBlank()) {
                    s.append(prefix).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Description")).append(">").append("<![CDATA[").append(v).append("]]>").append("</").append(XmlTools.xmlTag("Description")).append(">\n");
                }
                s.append(prefix).append(indent).append("</").append(XmlTools.xmlTag("Attributes")).append(">\n");
            }
            s.append(prefix).append(indent).append("<").append(XmlTools.xmlTag("ColumnsDefinition")).append(">\n");
            List<Data2DColumn> columns = data2d.getColumns();
            if (columns != null) {
                for (Data2DColumn col : columns) {
                    if (col.getColumnName() == null) {
                        continue;
                    }
                    s.append(prefix).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Column")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("ColumnName")).append(">").append("<![CDATA[").append(col.getColumnName()).append("]]>").append("</").append(XmlTools.xmlTag("ColumnName")).append(">\n");
                    if (col.getType() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Type")).append(">").append(col.getType().name()).append("</").append(XmlTools.xmlTag("Type")).append(">\n");
                    }
                    if (col.getLabel() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Label")).append(">").append("<![CDATA[").append(col.getLabel()).append("]]>").append("</").append(XmlTools.xmlTag("Label")).append(">\n");
                    }
                    if (ColumnType.String == col.getType()) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Length")).append(">").append(col.getLength()).append("</").append(XmlTools.xmlTag("Length")).append(">\n");
                    }
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Width")).append(">").append(col.getWidth()).append("</").append(XmlTools.xmlTag("Width")).append(">\n");
                    if (col.getFormat() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("DisplayFormat")).append(">");
                        if (col.isEnumType()) {
                            s.append("\n").append(prefix).append(indent).append(indent).append(indent).append("<![CDATA[").append(col.getFormat()).append("]]>\n").append(indent).append(indent);
                        } else {
                            s.append(col.getFormat());
                        }
                        s.append("</").append(XmlTools.xmlTag("DisplayFormat")).append(">\n");
                    }
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("NotNull")).append(">").append(col.isNotNull() ? "true" : "false").append("</").append(XmlTools.xmlTag("NotNull")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Editable")).append(">").append(col.isEditable() ? "true" : "false").append("</").append(XmlTools.xmlTag("Editable")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("PrimaryKey")).append(">").append(col.isIsPrimaryKey() ? "true" : "false").append("</").append(XmlTools.xmlTag("PrimaryKey")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("AutoGenerated")).append(">").append(col.isAuto() ? "true" : "false").append("</").append(XmlTools.xmlTag("AutoGenerated")).append(">\n");
                    if (col.getDefaultValue() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("DefaultValue")).append(">").append("<![CDATA[").append(col.getDefaultValue()).append("]]>").append("</").append(XmlTools.xmlTag("DefaultValue")).append(">\n");
                    }
                    if (col.getColor() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Color")).append(">").append(col.getColor()).append("</").append(XmlTools.xmlTag("Color")).append(">\n");
                    }
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("DecimalScale")).append(">").append(col.getScale()).append("</").append(XmlTools.xmlTag("DecimalScale")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Century")).append(">").append(col.getCentury()).append("</").append(XmlTools.xmlTag("Century")).append(">\n");
                    s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("FixTwoDigitYears")).append(">").append(col.isFixTwoDigitYear() ? "true" : "false").append("</").append(XmlTools.xmlTag("FixTwoDigitYears")).append(">\n");
                    if (col.getDescription() != null) {
                        s.append(prefix).append(indent).append(indent).append(indent).append("<").append(XmlTools.xmlTag("Description")).append(">").append("<![CDATA[").append(col.getDescription()).append("]]>").append("</").append(XmlTools.xmlTag("Description")).append(">\n");
                    }
                    s.append(prefix).append(indent).append(indent).append("</").append(XmlTools.xmlTag("Column")).append(">\n");
                }
            }
            s.append(prefix).append(indent).append("</").append(XmlTools.xmlTag("ColumnsDefinition")).append(">\n");
            s.append(prefix).append("</").append(XmlTools.xmlTag("DataDefinition")).append(">\n");
            return s.toString();
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static String toJSON(Data2D data2d, boolean withAttributes, String prefix) {
        try {
            if (data2d == null) {
                return null;
            }
            String indent = AppValues.Indent;
            StringBuilder s = new StringBuilder();
            s.append(prefix).append("\"").append(message("DataDefinition")).append("\": {\n");
            if (withAttributes) {
                s.append(prefix).append(indent).append("\"").append(message("Attributes")).append("\": {\n");
                String v = data2d.getDataName();
                if (v != null && !v.isBlank()) {
                    s.append(prefix).append(indent).append(indent).append("\"").append(message("DataName")).append("\": ").append(JsonTools.encode(v)).append(",\n");
                }
                s.append(prefix).append(indent).append(indent).append("\"").append(message("DecimalScale")).append("\": ").append(data2d.getScale()).append(",\n");
                s.append(prefix).append(indent).append(indent).append("\"").append(message("MaxRandom")).append("\": ").append(data2d.getMaxRandom());
                v = data2d.getComments();
                if (v != null && !v.isBlank()) {
                    s.append(",\n").append(prefix).append(indent).append(indent).append("\"").append(message("Description")).append("\": ").append(JsonTools.encode(v)).append("\n");
                } else {
                    s.append("\n");
                }
                s.append(prefix).append(indent).append("},\n");
            }
            s.append(prefix).append(indent).append("\"").append(message("ColumnsDefinition")).append("\": [\n");
            boolean firstRow = true;
            List<Data2DColumn> columns = data2d.getColumns();
            if (columns != null) {
                for (Data2DColumn col : columns) {
                    if (firstRow) {
                        firstRow = false;
                    } else {
                        s.append(",\n");
                    }
                    s.append(prefix).append(indent).append(indent).append("{").append("\n");
                    if (col.getColumnName() == null) {
                        continue;
                    }
                    s.append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("ColumnName")).append("\": ").append(JsonTools.encode(col.getColumnName()));
                    if (col.getType() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Type")).append("\": \"").append(col.getType().name()).append("\"");
                    }
                    if (col.getLabel() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Label")).append("\": ").append(JsonTools.encode(col.getLabel()));
                    }
                    if (ColumnType.String == col.getType()) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Length")).append("\": ").append(col.getLength());
                    }
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Width")).append("\": ").append(col.getWidth());
                    if (col.getFormat() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("DisplayFormat")).append("\": ").append(JsonTools.encode(col.getFormat()));
                    }
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("NotNull")).append("\": ").append(col.isNotNull() ? "true" : "false");
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Editable")).append("\": ").append(col.isEditable() ? "true" : "false");
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("PrimaryKey")).append("\": ").append(col.isIsPrimaryKey() ? "true" : "false");
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("AutoGenerated")).append("\": ").append(col.isAuto() ? "true" : "false");
                    if (col.getDefaultValue() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("DefaultValue")).append("\": ").append(JsonTools.encode(col.getDefaultValue()));
                    }
                    if (col.getColor() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Color")).append("\": \"").append(col.getColor()).append("\"");
                    }
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("DecimalScale")).append("\": ").append(col.getScale());
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Century")).append("\": ").append(col.getCentury());
                    s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("FixTwoDigitYears")).append("\": ").append(col.isFixTwoDigitYear() ? "true" : "false");
                    if (col.getDescription() != null) {
                        s.append(",\n").append(prefix).append(indent).append(indent).append(indent).append("\"").append(message("Description")).append("\": ").append(JsonTools.encode(col.getDescription()));
                    }
                    s.append("\n").append(prefix).append(indent).append(indent).append("}");
                }
                s.append("\n").append(prefix).append(indent).append("]\n");
                s.append(prefix).append("}\n");
            }
            return s.toString();
        } catch (Exception e) {
            MyBoxLog.error(e.toString());
            return null;
        }
    }

    public static DataFileCSV toCSVFile(Data2D data2d, File file) {
        try {
            if (data2d == null || file == null) {
                return null;
            }
            File tmpFile = FileTmpTools.getTempFile();
            List<Data2DColumn> definition = columns();
            try (CSVPrinter csvPrinter = new CSVPrinter(new FileWriter(tmpFile, Charset.forName("UTF-8")), CsvTools.csvFormat(",", true))) {
                List<String> row = new ArrayList<>();
                for (Data2DColumn col : definition) {
                    row.add(col.getColumnName());
                }
                csvPrinter.printRecord(row);
                if (UserConfig.getBoolean("Data2DDefinitionExportAtributes", true)) {
                    csvPrinter.printComment("The first row defines attributes of the data. And other rows define columns.");
                    row.clear();
                    row.add(data2d.getDataName());
                    row.add("TableAttributes");
                    row.add(data2d.getMaxRandom() + "");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add("");
                    row.add(data2d.getScale() + "");
                    row.add("");
                    row.add("");
                    row.add(data2d.getComments());
                    csvPrinter.printRecord(row);
                }
                List<Data2DColumn> columns = data2d.getColumns();
                if (columns != null) {
                    for (Data2DColumn col : columns) {
                        row.clear();
                        row.add(col.getColumnName());
                        row.add(col.getType().name());
                        row.add(col.getLabel());
                        row.add(ColumnType.String == col.getType() ? col.getLength() + "" : "");
                        row.add(col.getWidth() + "");
                        row.add(col.getFormat());
                        row.add(col.isNotNull() ? "1" : "0");
                        row.add(col.isEditable() ? "1" : "0");
                        row.add(col.isIsPrimaryKey() ? "1" : "0");
                        row.add(col.isAuto() ? "1" : "0");
                        row.add(col.getDefaultValue());
                        row.add(col.getColor().toString());
                        row.add(col.getScale() + "");
                        row.add(col.getCentury() + "");
                        row.add(col.isFixTwoDigitYear() ? "1" : "0");
                        row.add(col.getDescription());
                        csvPrinter.printRecord(row);
                    }
                }
                csvPrinter.flush();
                csvPrinter.close();
            }
            if (!FileTools.override(tmpFile, file, true)) {
                return null;
            }
            DataFileCSV csv = new DataFileCSV();
            csv.setColumns(definition).setFile(file)
                    .setCharset(Charset.forName("UTF-8")).setDelimiter(",").setHasHeader(true)
                    .setColsNumber(definition.size());
            csv.saveAttributes();
            return csv;
        } catch (Exception e) {
            MyBoxLog.error(e.toString());
            return null;
        }
    }

    public static DataFileExcel toExcelFile(Data2D data2d, File file) {
        try {
            File tmpFile = FileTmpTools.getTempFile();
            List<Data2DColumn> definition = columns();
            try (XSSFWorkbook xssfBook = new XSSFWorkbook()) {
                XSSFSheet xssfSheet = xssfBook.createSheet("sheet1");
                xssfSheet.setDefaultColumnWidth(20);
                int rowIndex = 0;
                XSSFRow titleRow = xssfSheet.createRow(rowIndex++);
                XSSFCellStyle horizontalCenter = xssfBook.createCellStyle();
                horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
                for (int i = 0; i < definition.size(); i++) {
                    XSSFCell cell = titleRow.createCell(i);
                    cell.setCellValue(definition.get(i).getColumnName());
                    cell.setCellStyle(horizontalCenter);
                    xssfSheet.autoSizeColumn(i);
                }
                int cellIndex = 0;
                if (UserConfig.getBoolean("Data2DDefinitionExportAtributes", true)) {
                    XSSFRow commentsRow = xssfSheet.createRow(rowIndex++);
                    commentsRow.createCell(0).setCellValue("The first row defines attributes of the data. And other rows define columns.");
                    XSSFRow attributesRow = xssfSheet.createRow(rowIndex++);
                    attributesRow.createCell(cellIndex++).setCellValue(data2d.getDataName());
                    attributesRow.createCell(cellIndex++).setCellValue("TableAttributes");
                    attributesRow.createCell(cellIndex++).setCellValue(data2d.getMaxRandom() + "");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue(data2d.getScale() + "");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue("");
                    attributesRow.createCell(cellIndex++).setCellValue(data2d.getComments());
                }
                List<Data2DColumn> columns = data2d.getColumns();
                if (columns != null) {
                    for (Data2DColumn col : columns) {
                        XSSFRow columnRow = xssfSheet.createRow(rowIndex++);
                        cellIndex = 0;
                        columnRow.createCell(cellIndex++).setCellValue(col.getColumnName());
                        columnRow.createCell(cellIndex++).setCellValue(col.getType().name());
                        columnRow.createCell(cellIndex++).setCellValue(col.getLabel());
                        columnRow.createCell(cellIndex++).setCellValue(ColumnType.String == col.getType() ? col.getLength() + "" : "");
                        columnRow.createCell(cellIndex++).setCellValue(col.getWidth() + "");
                        columnRow.createCell(cellIndex++).setCellValue(col.getFormat());
                        columnRow.createCell(cellIndex++).setCellValue(col.isNotNull() ? "1" : "0");
                        columnRow.createCell(cellIndex++).setCellValue(col.isEditable() ? "1" : "0");
                        columnRow.createCell(cellIndex++).setCellValue(col.isIsPrimaryKey() ? "1" : "0");
                        columnRow.createCell(cellIndex++).setCellValue(col.isAuto() ? "1" : "0");
                        columnRow.createCell(cellIndex++).setCellValue(col.getDefaultValue());
                        columnRow.createCell(cellIndex++).setCellValue(col.getColor().toString());
                        columnRow.createCell(cellIndex++).setCellValue(col.getScale() + "");
                        columnRow.createCell(cellIndex++).setCellValue(col.getCentury() + "");
                        columnRow.createCell(cellIndex++).setCellValue(col.isFixTwoDigitYear() ? "1" : "0");
                        columnRow.createCell(cellIndex++).setCellValue(col.getDescription());
                    }
                }
                try (FileOutputStream fileOut = new FileOutputStream(tmpFile)) {
                    xssfBook.write(fileOut);
                }
                xssfBook.close();
            }
            if (!FileTools.override(tmpFile, file, true)) {
                return null;
            }
            DataFileExcel excel = new DataFileExcel();
            excel.setColumns(definition).setFile(file).setSheet("sheet1").setHasHeader(true).setColsNumber(definition.size());
            excel.saveAttributes();
            return excel;
        } catch (Exception e) {
            MyBoxLog.error(e.toString());
            return null;
        }
    }

    public static DataFileCSV fromXML(String s) {
        try {
            if (s == null || s.isBlank()) {
                return null;
            }
            Element e = XmlTools.toElement(null, null, s);
