#include "excelengine.h"
#include "qt_windows.h"
#include <QColor>
#include <QProgressDialog>
#include <QProgressBar>
#include <QFont>
#include <QMessageBox>
ExcelEngine::ExcelEngine()
{
    pExcel     = NULL;
    pWorkbooks = NULL;
    pWorkbook  = NULL;
    pWorksheet = NULL;

    sXlsFile     = "";
    nRowCount    = 0;
    nColumnCount = 0;
    nStartRow    = 0;
    nStartColumn = 0;

    bIsOpen  = false;
    bIsValid = false;
    m_WidItemAndNameMap.clear();
    m_WidItemAndIPMap.clear();
    m_IPAndStatueMap.clear();
    HRESULT r = OleInitialize(NULL);
    if (r != S_OK && r != S_FALSE)
    {
        qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
    }
}

ExcelEngine::ExcelEngine(QString xlsFile)
{
    pExcel     = NULL;
    pWorkbooks = NULL;
    pWorkbook  = NULL;
    pWorksheet = NULL;

    sXlsFile     = xlsFile;
    nRowCount    = 0;
    nColumnCount = 0;
    nStartRow    = 0;
    nStartColumn = 0;

    bIsOpen  = false;
    bIsValid = false;

    HRESULT r = OleInitialize(0);
    if (r != S_OK && r != S_FALSE)
    {
        qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
    }

}

ExcelEngine::~ExcelEngine()
{
    if ( bIsOpen )
    {
        //析构前，先保存数据，然后关闭workbook
        Close();
    }
    OleUninitialize();
}

/**
  *@brief 打开sXlsFile指定的excel报表
  *@return true : 打开成功
  *        false: 打开失败
  */
bool ExcelEngine::Open(UINT nSheet, bool visible)
{

    if ( bIsOpen )
    {
        //return bIsOpen;
        Close();
    }

    if ( NULL == pExcel )
    {
        this->pExcel = new QAxObject("Excel.Application",this);
        if(pExcel->isNull())
        {
            QMessageBox::information(0,"提示信息",tr("<font size='2' face='宋体'>Excel程序出错</font>"),"确定");
            return false;
        }
        if ( pExcel )
        {
            bIsValid = true;
        }
        else
        {
            bIsValid = false;
            bIsOpen  = false;
            return bIsOpen;
        }
        bIsVisible = false;
        pExcel->dynamicCall("SetVisible(bool)", bIsVisible);//是否显示Excel
    }

    if ( !bIsValid )
    {
        bIsOpen  = false;
        return bIsOpen;
    }

    if ( sXlsFile.isEmpty() )
    {
        bIsOpen  = false;
        return bIsOpen;
    }


    bool ok = CreateXlsFile(sXlsFile);
    if ( !ok )
    {
        bIsOpen  = false;
        return bIsOpen;
    }

    nCurrSheet = nSheet;
    //bIsVisible = visible;

    pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿
    pWorkbook =  pWorkbooks->querySubObject("Open(QString, QVariant)",sXlsFile,QVariant(0)); //打开xls对应的工作簿
    pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", nCurrSheet);//打开第一个sheet

    //至此已打开，开始获取相应属性
    QAxObject *usedrange = pWorksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
    QAxObject *rows = usedrange->querySubObject("Rows");
    QAxObject *columns = usedrange->querySubObject("Columns");

    //因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
    nStartRow    = usedrange->property("Row").toInt();    //第一行的起始位置
    nStartColumn = usedrange->property("Column").toInt(); //第一列的起始位置
    //qDebug()<<"start row:"<<nStartRow;
    //qDebug()<<"start column:"<<nStartColumn;

    nRowCount    = rows->property("Count").toInt();       //获取行数
    nColumnCount = columns->property("Count").toInt();    //获取列数
    bIsOpen  = true;

    /*qDebug()<<"nStartRow"<<nStartRow;
    qDebug()<<"nStartColumn"<<nStartColumn;
    qDebug()<<"nRowCount"<<nRowCount;
    qDebug()<<"nColumnCount"<<nColumnCount;*/
    return bIsOpen;
}

/**
  *@brief Open()的重载函数
  */
bool ExcelEngine::Open(QString xlsFile, UINT nSheet, bool visible)
{
    sXlsFile = xlsFile;
    nCurrSheet = nSheet;
    //bIsVisible = visible;

    return Open(nCurrSheet,bIsVisible);
}

/**
  *@brief 保存表格数据，把数据写入文件
  */
void ExcelEngine::Save()
{
    if ( pWorkbook )
    {
        //pWorkbook->dynamicCall("SaveAs(const QString &)",QString());
        pWorkbook->dynamicCall("Save(void)");
    }
}

/**
  *@brief 关闭前先保存数据，然后关闭当前Excel COM对象，并释放内存
  */
void ExcelEngine::Close()
{
    if(!bIsOpen)
    {
        return;
    }
    //关闭前先保存数据
    pWorkbook->dynamicCall("Save()");
    if ( pExcel && pWorkbook )
    {

        pWorkbook->dynamicCall("Close(bool)", true);
        pExcel->dynamicCall("Quit()");
        qDebug()<<"quit excel.exe";
        delete pExcel;
        pExcel = NULL;

        bIsOpen = false;
        bIsValid = false;
    }
}

void ExcelEngine::SetExcelFile(QString name)
{
    sXlsFile = name;
}

/**
  *@func  读取excel表格数据
  *@brief 把tableWidget中的数据保存到excel中
  *@param tableWidget : 指向GUI中的tablewidget指针
  *@return 保存成功与否 true : 成功
  *                  false: 失败
  */
bool ExcelEngine::SaveDataFrTable(QTableWidget *tableWidget, bool saveAs, QString saveName)
{
    if ( NULL == tableWidget )
    {
        return false;
    }
    if ( !bIsOpen )
    {
        return false;
    }

    int tableR = tableWidget->rowCount();
    int tableC = tableWidget->columnCount();

    //获取表头写做第一行
    for (int i=0; i<tableC; i++)
    {
        if ( tableWidget->horizontalHeaderItem(i) != NULL )
        {
            this->SetCellData(1,i+1,tableWidget->horizontalHeaderItem(i)->text());
        }
    }

    //写数据
    for (int i=0; i<tableR; i++)
    {
        for (int j=0; j<tableC; j++)
        {
            if ( tableWidget->item(i,j) != NULL )
            {
                this->SetCellData(i+2,j+1,tableWidget->item(i,j)->text());
            }
        }
    }
    //保存
    if(saveAs == false){
        qDebug()<<"xls name"<<sXlsFile;
        pWorkbook->dynamicCall("Save(void)");
    }else{
        qDebug()<<"xls anothername"<<saveName;
        pWorkbook->dynamicCall("SaveAs(const QString&)",saveName);
    }
    return true;
}

/**
  *@brief 从指定的xls文件中把数据导入到tableWidget中
  *@param tableWidget : 执行要导入到的tablewidget指针
  *@return 导入成功与否 true : 成功
  *                   false: 失败
  */
bool ExcelEngine::ReadDataToTable(QTableWidget *tableWidget)
{
    if ( NULL == tableWidget )
    {
        return false;
    }
    //先把table的内容清空
    int tableColumn = tableWidget->columnCount();
    tableWidget->clear();
    for (int n=0; n<tableColumn; n++)
    {
        tableWidget->removeColumn(0);
    }

    int rowcnt    = nStartRow + nRowCount;
    int columncnt = nStartColumn + nColumnCount;

    //获取excel中的第一行数据作为表头
    QStringList headerList;
    for (int n = nStartColumn; n<columncnt; n++ )
    {
        QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)",nStartRow, n);
        if ( cell )
        {
            headerList<<cell->dynamicCall("Value2()").toString();
        }
    }

    //重新创建表头
    tableWidget->setColumnCount(nColumnCount);
    tableWidget->setHorizontalHeaderLabels(headerList);


    //插入新数据
    for (int i = nStartRow, r = 0; i < rowcnt; i++, r++ )  //行
    {
        tableWidget->insertRow(r); //插入新行
        for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ )  //列
        {
            QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格

            //在r新行中添加子项数据
            if ( cell )
            {
                tableWidget->setItem(r,c,new QTableWidgetItem(cell->dynamicCall("Value2()").toString()));
            }
        }
    }

    return true;
}

bool ExcelEngine::ReadDataToTree(QTreeWidget *treeWidget)
{   
    QProgressDialog pro;
    pro.setCancelButton(0);
    pro.setRange(0,nRowCount);
    pro.setWindowModality(Qt::WindowModal);
    pro.setWindowFlags(Qt::WindowTitleHint|Qt::WindowCloseButtonHint);
    pro.setLabelText("<font size='2' face='宋体'>正在加载路段信息...</font>");
    pro.setWindowTitle("请稍后");
    pro.show();
    int len = 0;
    for(int i=1;i<=nRowCount;i=i+len)
    {
        CreateTreePoint(1,i,len,treeWidget);
        pro.setValue(i);
    }
    //加载节点的设备状态
    if(!m_IPAndStatueMap.empty())
    {
        qDebug()<<"add count:"<<m_IPAndStatueMap.count();
        QMap<QString, int>::const_iterator it;
        for(it=m_IPAndStatueMap.begin();it!=m_IPAndStatueMap.end();it++)
        {
            SetPointStatue(it.key(),it.value());
        }
    }
    else
    {
        QMap<QString,QTreeWidgetItem*>::const_iterator it;
        for(it=m_WidItemAndIPMap.begin();it!=m_WidItemAndIPMap.end();it++)
        {
            //qDebug()<<"add to tree"<<it.key();
            SetPointStatue(it.key(),r_null);
        }
    }
}
void ExcelEngine::CreateTreePoint(int startColumn, int startRow, int &len,QTreeWidget *treeWidget)
{
    QAxObject* cell;
    int rowLen = 0;
    QString value = "";
    int i = startRow;
    for(;i<=nRowCount;i++)
    {
        cell = pWorksheet->querySubObject("Cells(int,int)", i, startColumn );//获取单元格
        //qDebug()<<"cell"<<cell->dynamicCall("Value2()").toString();
        if(cell->dynamicCall("Value2()").toString() != value && cell->dynamicCall("Value2()").toString().length()!=0)
        {
            if(value == "")
            {
                value = cell->dynamicCall("Value2()").toString();
                rowLen = i;
                continue;
            }
            rowLen = i-startRow;
            break;
        }
    }
    rowLen = i-startRow;
    //qDebug()<<"value:"<<value<<"rowLen"<<rowLen;
    len = rowLen;
    QTreeWidgetItem* firstItem = new QTreeWidgetItem(treeWidget,QStringList()<<value);
    //m_WidItemAndNameMap.insert(value,firstItem);
    //第二节点
    value = "";
    for(int i=startRow;i<startRow+rowLen;i++)
    {
        cell = pWorksheet->querySubObject("Cells(int,int)", i, startColumn+1 );//获取单元格
        //qDebug()<<"cell"<<cell->dynamicCall("Value2()").toString();
        if( cell->dynamicCall("Value2()").toString().length()!=0)
        {
            value = cell->dynamicCall("Value2()").toString();
            QTreeWidgetItem* secItem = new QTreeWidgetItem(firstItem,QStringList()<<value);
            m_WidItemAndNameMap.insert(value,secItem);
        }
    }
    //第三节点
    value = "";
    QString pvalue = "";
    QAxObject* pcell;

    for(int i=startRow;i<startRow+rowLen;i++)
    {
        pcell = pWorksheet->querySubObject("Cells(int,int)", i, startColumn+1 );//获取单元格
        cell = pWorksheet->querySubObject("Cells(int,int)", i, startColumn+2 );//获取单元格
        //qDebug()<<"cell"<<cell->dynamicCall("Value2()").toString();
        if(pcell->dynamicCall("Value2()").toString().length() != 0)
        {
            if(pvalue == "" || pvalue != pcell->dynamicCall("Value2()").toString())
            {
                pvalue = pcell->dynamicCall("Value2()").toString();
            }
        }
        if( cell->dynamicCall("Value2()").toString().length()!=0)
        {
            value = cell->dynamicCall("Value2()").toString();
            QString ip = pWorksheet->querySubObject("Cells(int,int)", i, startColumn+3)->dynamicCall("Value2()").toString();
            QTreeWidgetItem* thirdItem = new QTreeWidgetItem(m_WidItemAndNameMap.value(pvalue),QStringList()<<(value+" "+ip));
            m_WidItemAndIPMap.insert(ip,thirdItem);
        }
    }

}

void ExcelEngine::SetPointStatue(const QString ip, const int status)
{
    QTreeWidgetItem *item = m_WidItemAndIPMap.value(ip);
    if(item == NULL)
    {
        return;
    }
    if(!m_WidItemAndIPMap.contains(ip))
    {
        //qDebug()<<"null ip";
        return;
    }
    QColor col;
    switch(status)
    {
    case r_onLine:
        //col.setRgb(123,104,238,255);
        //col.setRgb(0,0,205,255);
        item->setToolTip(0,"");
        col.setRgb(255,255,255,250);
        break;
    case r_offLine:
        //col.setRgb(236,218,106,255);
        col.setRgb(255,255,0,220);
        item->setToolTip(0,"<font color='red'>网络未连接</font>");
        break;
    case r_select:
        col.setRgb(0,0,205,255);
        break;
    case r_error:
        item->setToolTip(0,"<font color='red'>设备未连接</font>");
        //col.setRgb(255,0,0,255);
        col.setRgb(252,70,3,230);
        break;
    case r_null:
        item->setToolTip(0,"");
        col.setRgb(0,0,0);
        break;
    default:
        item->setToolTip(0,"");
        col.setRgb(0,0,0);
        break;
    }
    item->setForeground(0,QBrush(col));
    if(item->parent() != NULL)
    {
        item->parent()->setForeground(0,QBrush(QColor(0,0,0,250)));
        item->parent()->setToolTip(0,"");
        for(int i = 0;i<item->parent()->childCount();i++)
        {
            if(!item->parent()->child(i)->toolTip(0).isEmpty() )
            {
                item->parent()->setForeground(0,QBrush(QColor(255,0,0,255)));
                item->parent()->setToolTip(0,"<font color='red'>故障</font>");
                break;
            }
            else
            {
                if(m_IPAndStatueMap.contains(ip))
                {
                    if(i == item->parent()->childCount()-1)
                    {
                        item->parent()->setForeground(0,QBrush(QColor(255,255,255,250)));
                        item->parent()->setToolTip(0,"");
                    }
                }
            }
        }
    }
    if(item->parent()->parent()!= NULL)
    {
        item->parent()->parent()->setForeground(0,QBrush(QColor(0,0,0,250)));
        item->parent()->parent()->setToolTip(0,"");
        for(int i = 0;i<item->parent()->parent()->childCount();i++)
        {
            if(!item->parent()->parent()->child(i)->toolTip(0).isEmpty())
            {
                item->parent()->parent()->setForeground(0,QBrush(QColor(255,0,0,255)));
                item->parent()->parent()->setToolTip(0,"<font color='red'>故障</font>");
                break;
            }
            else
            {
                if(m_IPAndStatueMap.contains(ip))
                {
                    if(i == item->parent()->parent()->childCount()-1)
                    {
                        item->parent()->parent()->setForeground(0,QBrush(QColor(255,255,255,250)));
                        item->parent()->parent()->setToolTip(0,"");
                    }

                }

            }
        }
    }
}

void ExcelEngine::SetIPAndStatue(const QString ip, const int statue)
{
    //qDebug()<<"add ip"<<ip;
    if(m_IPAndStatueMap.contains(ip))
    {
        //qDebug()<<"set exist ip";
        m_IPAndStatueMap[ip] = statue;
    }
    else
    {
        m_IPAndStatueMap.insert(ip,statue);
    }
}

bool ExcelEngine::GetItemLaneIP(QTreeWidgetItem *item, QString &ip)
{
    QString tmp = m_WidItemAndIPMap.key(item,"");
    if(tmp != "")
    {
        ip = tmp;
        return true;
    }
    return false;
}

bool ExcelEngine::GetItemLaneName(const QString ip, QString &Secname, QString &FirstName)
{
    Secname = "";
    FirstName="";
    if(m_WidItemAndIPMap.contains(ip))
    {
        QTreeWidgetItem* item = m_WidItemAndIPMap.value(ip);
        if(item == NULL || item->parent() == NULL ||item->parent()->parent() == NULL)
        {
            return false;
        }
        Secname = item->parent()->text(0);
        FirstName=item->parent()->parent()->text(0);
        return true;
    }
    return false;
}

/**
  *@brief 获取指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格的列号
  *@return [row,column]单元格对应的数据
  */
QVariant ExcelEngine::GetCellData(UINT row, UINT column)
{
    QVariant data;

    QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
    if ( cell )
    {
        data = cell->dynamicCall("Value2()");
    }

    return data;
}

/**
  *@brief 修改指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格指定的列号
  *@param data : 单元格要修改为的新数据
  *@return 修改是否成功 true : 成功
  *                   false: 失败
  */
bool ExcelEngine::SetCellData(UINT row, UINT column, QVariant data)
{
    bool op = false;

    QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
    if ( cell )
    {
        QString strData = data.toString(); //excel 居然只能插入字符串和整型，浮点型无法插入
        cell->dynamicCall("SetValue(const QVariant&)",strData); //修改单元格的数据
        op = true;
    }
    else
    {
        op = false;
    }

    return op;
}

/**
  *@brief 创建一个xls报表文件
  *@param xlsFile : 报表文件名
  *@return true : 创建成功
  *        false: 创建失败
  */
bool ExcelEngine::CreateXlsFile(const QString &xlsFile)//引用
{

    bool cr = false;
    QFile f(xlsFile);
    if ( !f.exists())
    {
        /**
          *@note 由于无法找到workbook增加一个工作簿的接口，只能用这种拷贝文件的方法
          *或者，直接创建一个.xls为后缀的空白文档
          */
        QFile copyFile("d:/template.xls");
        if ( copyFile.exists() )
        {
            //直接拷贝一个模块
            QFile::copy("./template/template.xls",xlsFile);
        }
        else
        {
            //创建已xls为后缀的空白文档
            f.open(QIODevice::WriteOnly);
            f.close();
        }

        //判断是否创建成功
        cr = f.open(QIODevice::ReadWrite);
        f.close();

    }
    else
    {
        cr = true;
    }

    return cr;
}

/**
  *@brief 清空除报表之外的数据
  */
void ExcelEngine::Clear()
{
    sXlsFile     = "";
    nRowCount    = 0;
    nColumnCount = 0;
    nStartRow    = 0;
    nStartColumn = 0;
}

/**
  *@brief  判断excel是否已被打开
  *@return true : 已打开
  *        false: 未打开
  */
bool ExcelEngine::IsOpen()
{
    return bIsOpen;
}

/**
  *@brief 判断excel COM对象是否调用成功，excel是否可用
  *@return true : 可用
  *        false: 不可用
  */
bool ExcelEngine::IsValid()
{
    return bIsValid;
}

/**
  *@brief 获取excel的行数
  */
UINT ExcelEngine::GetRowCount()const
{
    return nRowCount;
}

/**
  *@brief 获取excel的列数
  */
UINT ExcelEngine::GetColumnCount()const
{
    return nColumnCount;
}