#include <QApplication>

#include "widget.h"

#include <QtGui>

#include <QAxObject>

#include <QStandardPaths>

#include <QFileDialog>

#include <QFileDialog>

/*excel操作*/

enum EXcel_ColumnType{

    ColumnA = ,

    ColumnB = ,

    ColumnC = ,

    ColumnD = ,

    ColumnE = ,

    ColumnF = ,

    ColumnG = ,

    ColumnH = ,

    ColumnI =

};

QString saveas()

{

    QString file;

    QString filter;

    //如果版本低于QT5,则需要将:

    //  QStandardPaths::writableLocation(QStandardPaths::DesktopLocation),

    //改为:QDesktopServices::storageLocation(QDesktopServices::DesktopLocation),

    file = QFileDialog::getSaveFileName (

     NULL,                               //父组件

    "另存为",                              //标题

     QStandardPaths::writableLocation(QStandardPaths::DesktopLocation),                 //设置路径, .表示当前路径,./表示更目录

     "Excel(*.xlsx)",     //过滤器

     &filter  );

    return file;

}

//设置EXCEL中单个内容的数据

void  Excel_SetCell(QAxObject *worksheet,EXcel_ColumnType column,int row,QColor color,QString text)

{

  QAxObject *cell = worksheet->querySubObject("Cells(int,int)", row, column);

  cell->setProperty("Value", text);

  QAxObject *font = cell->querySubObject("Font");

  font->setProperty("Color", color);

}

//把QVariant转为QList<QList<QVariant> >,用于快速读出的

void castVariant2ListListVariant(const QVariant &var, QList<QList<QVariant> > &res)

{

    QVariantList varRows = var.toList();

    if(varRows.isEmpty())

    {

        return;

    }

    const int rowCount = varRows.size();

    QVariantList rowData;

    for(int i=;i<rowCount;++i)

    {

        rowData = varRows[i].toList();

        res.push_back(rowData);

    }

}

//把QList<QList<QVariant> > 转为QVariant,用于快速写入的

void castListListVariant2Variant(const QList<QList<QVariant> > &cells, QVariant &res)

{

    QVariantList vars;

    const int rows = cells.size();

    for(int i=;i<rows;++i)

    {

        vars.append(QVariant(cells[i]));

    }

    res = QVariant(vars);

}

int main(int argc, char *argv[])

{

    QApplication a(argc, argv);

    QString templatePath = "./template.xlsx";

    QFileInfo info(templatePath);

    if(!info.exists())

    {

            qDebug()<<"template.xlsx is NULL";

            return ;

    }

    templatePath = info.absoluteFilePath();                   //获取模板的绝地路径

    templatePath = QDir::toNativeSeparators(templatePath);   //转换一下路径,让windows能够识别

    QString ExcelFile = QDir::toNativeSeparators(saveas());  //打开文件保存对话框,找到要保存的位置

    if(ExcelFile=="")

            return  ;

    QFile::copy(templatePath, ExcelFile);                   //将模板文件复制到要保存的位置去

    info.setFile(ExcelFile);

    info.setFile(info.dir().path()+"/~$"+info.fileName());

    if(info.exists())          //判断一下,有没有"~$XXX.xlsx"文件存在,是不是为只读

    {

        qDebug()<<"报表属性为只读,请检查文件是否已打开!";

         return   ;

    }

    QAxObject *excel = new QAxObject();//建立excel操作对象

    excel->setControl("Excel.Application");//连接Excel控件

    excel->setProperty("Visible", true);//显示窗体看效果

    excel->setProperty("DisplayAlerts", false);//显示警告看效果

    QAxObject *workbooks = excel->querySubObject("WorkBooks");

    QAxObject* workbook = workbooks->querySubObject("Open(const QString&)",QDir::toNativeSeparators(ExcelFile) ); //打开

    excel->setProperty("Caption", "Qt Excel");      //标题为Qt Excel

    QAxObject *work_book = excel->querySubObject("ActiveWorkBook");

    QAxObject *worksheet = work_book->querySubObject("Sheets(int)",);     //获取表单1

    Excel_SetCell(worksheet,ColumnB,,QColor(,,),"");     //设置B2单元格内容为12345

    Excel_SetCell(worksheet,ColumnB,,QColor(,,),"B3");     //设置B3单元格内容

    Excel_SetCell(worksheet,ColumnB,,QColor(,,),"B4");     //设置B4单元格内容

    /*批量一次性设置A6~I106所在内容*/

    QAxObject *user_range = worksheet->querySubObject("Range(const QString&)","A6:I106");

    QList<QList<QVariant> > datas;

    for(int i=;i<;i++)

    {

        QList<QVariant> rows;

        for(int j=;j<;j++)

        {

            rows.append(i*j);

        }

        datas.append(rows);

    }

    QVariant var;

    castListListVariant2Variant(datas,var);

    user_range->setProperty("Value", var);

    workbook->dynamicCall("Save()" );


    // workbook->dynamicCall("Close()");  //关闭文件

    // excel->dynamicCall("Quit()");//关闭excel

    return ;

}