Q4.7.0T操作EXCEL

 这两天杨老师让我处理一下学校EXCEL课表的数据，需要筛选整理出用到的信息，于是我就用编了一个QT程序处理了一下，两天的时间总算完成了任务。源程序代码如下：

qtandexcel.pro

#-------------------------------------------------

#

# Project created by QtCreator 2011-08-19T19:46:26

#

#-------------------------------------------------

QT       += core gui

TARGET = qtandexcel

TEMPLATE = app

SOURCES += main.cpp

HEADERS  +=

CONFIG  += console    //qDebug打印信息的需要

CONFIG  +=qaxcontainer //QT操作EXCEL的相关控件

 

main.cpp

#include <QtGui/QApplication>

#include <QAxObject>

#include <QAxWidget>

#include <QAxSelect>

#include <qaxselect.h>

#include <QDebug>

#include <QFile>

#include <QTextStream>

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

{

    QApplication a(argc, argv);

    QAxObject* excel = new QAxObject("Excel.Application");

    excel->setProperty("Visible",true);

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

    workbooks->dynamicCall("Open(const QString&)",QString("E:/2011-2012biao.xls"));//表格名字不能有中文

    /*获取活动工作薄*/

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

    /*获取工作表总数*/

    QAxObject* worksheets=workbook->querySubObject("WorkSheets");

    int intCount=worksheets->property("Count").toInt();

    qDebug()<<"the num of worksheets : "<<intCount<<endl;//1

    /*获取第一张工作表的起始行，总行数，起始列，总列数*/

    QAxObject* worksheet=workbook->querySubObject("Worksheets(int)",1);

    QAxObject* usedrange=worksheet->querySubObject("UsedRange");

    QAxObject* rows=usedrange->querySubObject("Rows");

    QAxObject* columns=usedrange->querySubObject("Columns");

    int intRowStart=usedrange->property("Row").toInt();

    int intColStart=usedrange->property("Column").toInt();

    int intCols=columns->property("Count").toInt();

    int intRows=rows->property("Count").toInt();

    qDebug()<<"RowStart,Rows,ColStart,Cols : "<<intRowStart<<intRows<<intColStart<<intCols<<endl;

    /*从excel中读取数据，并写入rawbiao.txt*/

    QFile data("rawbiao.txt");

    if(data.open(QFile::WriteOnly | QFile::Truncate))

    {

        QTextStream out(&data);

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

        {

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

          {

            QAxObject* range=worksheet->querySubObject("Cells(int,int)",i,j);

            out<<range->property("Value").toString()<<";";//用分号做隔列符

          }

          out<<"\r\n";

        }

    }

    data.close();

    qDebug()<<"read excel data to rawbiao.txt complete!!"<<endl;

    /*筛选出北区课表，删除东区*/

    QString oneline,xiaoqu,qizhiweek,danshuangzhou,week,jieci,classname,teachername,teachernumber,newline;

    int i=0;

    int j=0;

    int totalline=0;

    QFile bqbiao("bqkebiao.txt");//存放筛选出的北区课表

    if(bqbiao.open(QFile::WriteOnly | QFile::Truncate))

    {

        QTextStream out(&bqbiao);

        if(data.open(QIODevice::ReadOnly))

        {

            QTextStream in(&data);

            /*读取第一行标题各列并直接写入txt*/

            oneline=in.readLine();

            qizhiweek=oneline.section(";",9,9);//起始周

            qizhiweek.append(";");

            danshuangzhou=oneline.section(";",8,8);//单双周

            danshuangzhou.append(";");

            week=oneline.section(";",6,6);//星期

            week.append(";");

            jieci=oneline.section(";",7,7);//节次

            jieci.append(";");

            classname=oneline.section(";",2,2);//课程名称

            classname.append(";");

            teachername=oneline.section(";",4,4);//教师姓名

            teachername.append(";");

            teachernumber=oneline.section(";",3,3);//教师工号

            newline=qizhiweek.append(danshuangzhou);

            newline.append(week);

            newline.append(jieci);

            newline.append(classname);

            newline.append(teachername);

            newline.append(teachernumber);

            out<<newline<<"\r\n";

            while(!in.atEnd())

            {

               oneline=in.readLine();

               totalline++;

               xiaoqu=oneline.section(";",11,11);

               if(xiaoqu.contains("2"))//是否是北区的课表信息

               {

                   i++;

                   qizhiweek=oneline.section(";",9,9);//起始周

                   qizhiweek.append(";");

                   danshuangzhou=oneline.section(";",8,8);//单双周

                   danshuangzhou.append(";");

                   week=oneline.section(";",6,6);//星期

                   week.append(";");

                   jieci=oneline.section(";",7,7);//节次

                   jieci.append(";");

                   classname=oneline.section(";",2,2);//课程名称

                   classname.append(";");

                   teachername=oneline.section(";",4,4);//教师姓名

                   teachername.append(";");

                   teachernumber=oneline.section(";",3,3);//教师工号

                   newline=qizhiweek.append(danshuangzhou);

                   newline.append(week);

                   newline.append(jieci);

                   newline.append(classname);

                   newline.append(teachername);

                   newline.append(teachernumber);

                   out<<newline<<"\r\n";

               }

               else if(xiaoqu.contains("1"))

               {

                   j++;

               }

               else

               {

                   qDebug()<<totalline<<xiaoqu<<endl;

               }

           }

        }

    }

    data.close();

    bqbiao.close();

    qDebug()<<totalline<<i<<j<<endl;

    qDebug()<<"beiqu kebiao is complete!!";

    excel->setProperty("DisplayAlerts",0);

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

    workbook->dynamicCall("Close(Boolean)",false);

    excel->dynamicCall("Quit(void)");

    excel->setProperty("DisplayAlerts",1);

    delete excel;

    return a.exec();

}