package frame.file.office;

import java.util.Map;


import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.dom4j.rule.Stylesheet;

import frame.data.EntitySet;
import frame.file.FileIOContext;
import frame.file.FileIOItem;
import frame.file.processor.Processor;

public class ExcelHandler {
	private static char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();

    protected Processor progressor;
    protected Map<String, String> parameterProvider;
    protected OpenXmlWriter writer;
    protected OpenXmlReader reader;
    protected ColumnReader columnReader;

    protected SpreadsheetDocument sheetDocument;
    protected WorkbookPart workbookPart;
    protected Stylesheet styleSheet;
    protected SharedStringsTable sharedStringTable;
    protected CellFormat cellFormat;
    protected Sheets sheets;

    protected FileIOItem workingIoItem;
    protected FileIOContext workingIoContext;
    protected int[] index;
    protected IExcelValueTranslator[] translator;
    protected int rowIndex = 1;

    protected int percentStyleId;
    protected int dateStyleId;
    protected int dateTimeStyleId;
    protected int numberFormatId;


    public ExcelHandler(Processor progressor, Map<String, String> parameterProvider) {
        columnReader = new ColumnReader();
        numberFormatId = 254;

        this.progressor = progressor;
        this.parameterProvider = parameterProvider;
    }

    public void processFile(String file) {
        sheetDocument = SpreadsheetDocument.Open(file, true);
        try {
            workbookPart = sheetDocument.WorkbookPart;
            try {
                //1. style
                styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
                if (styleSheet == null) {
                    styleSheet = new Stylesheet();
                }

                createCellStyle();

                //2. shared string
                sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable;
                if (sharedStringTable == null) {
                    sharedStringTable = new SharedStringsTable();
                }

                //3. sheets 
                sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                processSheets();
            }
            finally {
                workbookPart.Workbook.Save();
            }
        }
        finally {
            sheetDocument.Close();
        }
    }

    protected void processSheets() {

    }

    protected void writeOneSheet(Sheet sheet, EntitySet entitySet) {
        rowIndex = workingIoItem.getToRowNo();

        WorksheetPart sourceSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.);
        String sourceSheetPartId = workbookPart.GetIdOfPart(sourceSheetPart);

        WorksheetPart destSheetPart = workbookPart.AddNewPart<WorksheetPart>();

        reader = OpenXmlReader.Create(sourceSheetPart);
        try {
            writer = OpenXmlWriter.Create(destSheetPart);
            try {
                bool skip = false;

                while (reader.Read()) {
                    Type elementType = reader.ElementType;

                    if (elementType == typeof(Row)) {
                        if (reader.IsStartElement) {
                            skip = true;
                        }
                        else if (reader.IsEndElement) {
                            skip = false;
                        }

                        continue;
                    }

                    if (skip) {
                        continue;
                    }

                    if (elementType == typeof(SheetData)) {
                        if (reader.IsEndElement) {
                            continue;
                        }

                        doWriteOneSheet(sourceSheetPart, entitySet);
                    }
                    else {
                        if (reader.IsStartElement) {
                            writer.WriteStartElement(reader);
                        }
                        else if (reader.IsEndElement) {
                            writer.WriteEndElement();
                        }
                    }
                }
            }
            finally {
                writer.Close();
                writer = null;
            }
        }
        finally {
            reader.Close();
            reader = null;
        }

        workbookPart.DeletePart(sourceSheetPart);
        workbookPart.ChangeIdOfPart(destSheetPart, sourceSheetPartId);
    }

    protected void doWriteOneSheet(WorksheetPart worksheetPart, EntitySet entitySet) {
        writer.WriteStartElement(new SheetData());
        try {
            //1. header
            progressor.newPhase("HandleHeader", "处理标题");
            writeHeader(worksheetPart);
            progressor.endPhase();

            //2. mapping
            progressor.newPhase("CreateMapping", "创建Mapping");
            createColumnMappings();
            progressor.endPhase();

            //3.
            progressor.newPhase("WriteRows", "写入数据");
            writeRows(entitySet);
            progressor.endPhase();
        }
        finally {
            writer.WriteEndElement();
        }
    }

    protected void writeHeader(WorksheetPart worksheetPart) {
        IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();

        int pos = 1;
        foreach (Row row in rows ) {
            columnReader.read(row, sharedStringTable, cellFormats);
            writer.WriteElement(row);

            pos++;
            if (pos >= rowIndex) {
                break;
            }
        }
    }

    protected void createColumnMappings() {
        //1. create mapping run time
        List<string> excelColumns = columnReader.getColumnNames();
        workingIoItem.createMappingRuntime(excelColumns, workingIoContext);

        //2. init index and translator
        index = new int[excelColumns.Count];
        translator = new IExcelValueTranslator[excelColumns.Count];

        IOMappingItemRuntimeList itemRuntimeList = workingIoItem.getInsertMappingRuntime();

        int i = 0;
        foreach (String column in excelColumns) {
            if (!itemRuntimeList.contaisToField(column)) {
                index[i++] = -1;
                continue;
            }

            IOMappingItemRuntime itemRuntime = itemRuntimeList.getItemByToField(column);
            index[i] = itemRuntime.getFromIndex();
            translator[i] = Translator.createExcelValueTranslator(itemRuntime.getExportType());

            i++;
        }
    }

    public void writeRows(EntitySet entitySet) {
        while (entitySet.hasNext()) {
            Entity entity = entitySet.getCurrent();
            WriteOneRow(entity);
        }

        progressor.appendMesage("共写入数据" + entitySet.size() + "行");
    }

    public void WriteOneRow(Entity entity) {
        Row row = new Row();
        row.RowIndex = new UInt32Value(rowIndex);

        writer.WriteStartElement(row);
        try {
            int columnIndex = 0; int max = index.Length;

            for (int i = 0; i < max; i++) {
                int idx = index[i];

                if (idx < 0) {
                    continue;
                }

                Object value = entity.getObjectByIndex(idx);
                writeOneCell(i, translator[i], value);
                
                columnIndex++;
            }
        }
        finally {
            writer.WriteEndElement();
        }

        rowIndex++;
    }

    public void writeOneCell(int columnIndex, IExcelValueTranslator translator, Object data) {
        Cell cell = new Cell();
        cell.CellReference = toCellReference(columnIndex) + rowIndex;

        if (data == null || data == DBNull.Value) {
            writer.WriteElement(cell);
            return;
        }

        CellDataType type = CellDataType.Error;
        String value = translator.toString(ref type, data);

        if (CellDataType.Boolean == type) {
            WriteBoolean(cell, value);
        }
        else if (CellDataType.Int == type) {
            WriteNumber(cell, value);
        }
        else if (CellDataType.Decimal == type) {
            WriteNumber(cell, value);
        }
        else if (CellDataType.Percent == type) {
            WritePercent(cell, value);
        }
        else if (CellDataType.String == type) {
            WriteString(cell, value);
        }
        else if (CellDataType.Date == type) {
            WriteDate(cell, value);
        }
        else if (CellDataType.DateTime == type) {
            WriteDateTime(cell, value);
        }

        writer.WriteElement(cell);
    }

    protected void WriteBoolean(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.DataType = new EnumValue<CellValues>(CellValues.Boolean);
    }

    protected void WriteNumber(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
    }

    protected void WritePercent(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        cell.StyleIndex = percentStyleId;
    }
    protected void WriteDate(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.StyleIndex = dateStyleId;
    }

    protected void WriteDateTime(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.StyleIndex = dateStyleId;
    }

    protected void WriteString(Cell cell, string value) {
        cell.CellValue = new CellValue(value);
        cell.DataType = new EnumValue<CellValues>(CellValues.String);
    }

    protected void WriteSharedString(Cell cell, string value) {
        int stringId = InsertSharedStringItem(value);
        cell.CellValue = new CellValue(stringId.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    }

    protected void WriteInlineString(Cell cell, string value) {
        InlineString inlineString = new InlineString();
        Text cellValueText = new Text { Text = value };
        inlineString.AppendChild(cellValueText);
        cell.Append(inlineString);
        cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
    }

    protected int InsertSharedStringItem(string text) {
        int i = 0;
        // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        foreach (SharedStringItem item in sharedStringTable.Elements<SharedStringItem>()) {
            if (item.InnerText == text) {
                return i;
            }

            i++;
        }

        // The text does not exist in the part. Create the SharedStringItem and return its index.
        sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
        sharedStringTable.Save();
        return i;
    }

    protected string toCellReference(int columnIndex) {
        // start form 0;
        string result = "";

        int num = columnIndex;
        do {
            int i = num % 26;
            result = alphabet[i] + result;
            num = num / 26 - 1;
        } 
        while (num > -1);

        if (string.IsNullOrEmpty(result)) {
            result = "A";
        }

        return result;
    }

    protected Sheet getSheetByName(String sheetname) {
        IEnumerable<Sheet> sheetList = sheets.Elements<Sheet>();
        Sheet sheet = null;

        //1. sheet name is null
        if (string.IsNullOrEmpty(sheetname)) {
            sheet = sheetList.First<Sheet>();
        }

        if (sheet != null) {
            return sheet;
        }

        sheetname = sheetname.ToLower();

        //2. locate by name
        sheet = sheetList.FirstOrDefault<Sheet>(s => s.Name.Value.ToLower().Equals(sheetname));

        if (sheet != null) {
            return sheet;
        }

        //3. 
        sheet = sheetList.First<Sheet>();

        if (sheet != null) {
            return sheet;
        }

        return null;
    }

    protected void createCellStyle() {
        //1. create style
        percentStyleId = createPercentStyleFormat();
        dateStyleId = createDateStyleFormat();
        dateTimeStyleId = createDateTimeStyleFormat();

        //2. save
        styleSheet.Save();
    }

    protected UInt32Value createPercentStyleFormat() {
        CellFormat cellFormat = new CellFormat();
        cellFormat.NumberFormatId = 10;
        cellFormat.ApplyNumberFormat = true;

        styleSheet.CellFormats.Append(cellFormat);

        UInt32Value result = styleSheet.CellFormats.Count;
        styleSheet.CellFormats.Count++;
        return result;
    }

    protected UInt32Value createDateStyleFormat() {
        //1. add number fromat
        NumberingFormats numberingFormats = styleSheet.NumberingFormats;
        if (numberingFormats == null) {
            numberingFormats = new NumberingFormats();
        }

        NumberingFormat numberingFormat = new NumberingFormat();
        numberingFormat.NumberFormatId = numberFormatId++;
        numberingFormat.FormatCode = "yyyy\\-mm\\-dd";

        numberingFormats.Append(numberingFormat);

        //2. add cell fromat
        CellFormat cellFormat = new CellFormat();
        cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
        cellFormat.ApplyNumberFormat = true;

        styleSheet.CellFormats.Append(cellFormat);

        UInt32Value result = styleSheet.CellFormats.Count;
        styleSheet.CellFormats.Count++;
        return result;
    }

    protected UInt32Value createDateTimeStyleFormat() {
        //1. add number fromat
        NumberingFormats numberingFormats = styleSheet.NumberingFormats;
        if (numberingFormats == null) {
            numberingFormats = new NumberingFormats();
        }

        NumberingFormat numberingFormat = new NumberingFormat();
        numberingFormat.NumberFormatId = numberFormatId++;
        numberingFormat.FormatCode = "yyyy\\-mm\\-dd hh:mm:ss";

        numberingFormats.Append(numberingFormat);

        //2. add cell fromat
        CellFormat cellFormat = new CellFormat();
        cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
        cellFormat.ApplyNumberFormat = true;

        styleSheet.CellFormats.Append(cellFormat);

        UInt32Value result = styleSheet.CellFormats.Count;
        styleSheet.CellFormats.Count++;
        return result;
    }
}

}