一、背景介绍
环境:Win10 + QT5 + VS2015
Excel文件类型:Microsoft Excel 97-2003 工作表 (.xls)
目的:用QT处理Excel自动计算一下加班时间等信息。
主要涉及到Excel的读写操作、单元格格式设置等内容。
待处理的Excel表格内容如图所示:
二、涉及问题
QT相关配置
1、引入Active Qt库
QT += axcontainer
2、配置
右键项目→属性→VC++目录→库目录 加入.lib文件目录
右键项目→属性→链接器→输入 加入Qt5AxBase.lib、Qt5AxContainer.lib【release版】
Debug版加d:Qt5AxContainerd.lib、Qt5AxBased.lib
中文字符问题
由于中间涉及到对中文字符的操作,为了解决中文字符的乱码问题,需在编译时加入命令行参数,设置执行字符集为utf-8。
因此,需要在源文件中加入:
#pragma execution_character_set("utf-8")
三、代码内容
界面:
OvertimeCalculate.h内容:
#pragma once
#include <QtWidgets/QMainWindow>
#include "ui_OvertimeCalculate.h"
#include <QFileDialog>
#include<ActiveQt/QAxObject>
#include <QDebug>
#pragma execution_character_set("utf-8") //中文字符
class OvertimeCalculate : public QMainWindow
{
Q_OBJECT
public:
OvertimeCalculate(QWidget *parent = Q_NULLPTR);
private slots:
void openFileSlot();
private:
Ui::OvertimeCalculateClass ui;
QString filename;
};
OvertimeCalculate.cpp内容:
#include "OvertimeCalculate.h"
OvertimeCalculate::OvertimeCalculate(QWidget *parent)
: QMainWindow(parent)
{
ui.setupUi(this);
}
void OvertimeCalculate::openFileSlot()
{
//打开文件
QString filePath = QFileDialog::getOpenFileName(this, tr("Open File"), ".", tr("Microsoft Office 2007 (*.xls)"));//获取保存路径
QAxObject *excel = new QAxObject("this");
excel->setControl("Excel.Application");
excel->dynamicCall("SetVisible(bool)", true); //true 表示操作文件时可见 false表示不可见
QAxObject *workbooks = excel->querySubObject("WorkBooks");
//按文件路径打开文件
QAxObject *workbook = workbooks->querySubObject("Open(QString&)", filePath);
QAxObject * worksheets = workbook->querySubObject("WorkSheets");// 获取excel文件中所有工作sheet
QAxObject * worksheet = worksheets->querySubObject("Item(int)", 1);//获取第1个
//获取sheet的数据范围
QAxObject * usedrange = worksheet->querySubObject("UsedRange");
//获取行数
QAxObject * rows = usedrange->querySubObject("Rows");
int iRows = rows->property("Count").toInt();
//获取列数
QAxObject * columns = usedrange->querySubObject("Columns");
int iColumns = columns->property("Count").toInt();
QString stringWrite;
while (iRows-1)
{
QString cell = QString::number(iRows, 10);
QAxObject *range0 = worksheet->querySubObject("Range(QString)", "D" + cell);//周末判断
QString strRowD = "";
strRowD = range0->property("Value").toString();
QAxObject *range1 = worksheet->querySubObject("Range(QString)", "F" + cell);
QAxObject *range2 = worksheet->querySubObject("Range(QString)", "G" + cell);//下班时间
QString strRowF = "";
QString strRowG = "";
strRowF = range1->property("Value").toString();
QString RowFhh = strRowF.mid(11, 2); //上班时间hh
QString RowFmm = strRowF.mid(14, 2); //上班时间mm
strRowG = range2->property("Value").toString();
QString RowGhh = strRowG.mid(11, 2); //上班时间hh
QString RowGmm = strRowG.mid(14, 2); //上班时间mm
bool ok;
int intRowFhh = RowFhh.toInt(&ok, 10);//上班时间hh
int intRowFmm = RowFmm.toInt(&ok, 10);//上班时间mm
int intRowGhh = RowGhh.toInt(&ok, 10);//下班时间hh
int intRowGmm = RowGmm.toInt(&ok, 10);//下班时间mm
double weekendOvertime = 0;
double weekdayOvertime = 0;
QString stringWeekendOvertime = "";
QString stringWeekdayOvertime = "";
double lunchTime = 0.5;
double dinnerTime = 0.5;
double mmTimeGo = 0;
double mmTimeOff = 0;
if (strRowD == "星期六" || strRowD == "星期日")
{
if (intRowFhh < 8) //早于8点按8点
{
intRowFhh = 8;
intRowFmm = 0; //8点整开始
}
if (intRowFhh == 12) //12点-13点到 按13点整
{
intRowFhh = 13;
intRowFmm = 0; //13点整开始
}
//-------------------------------------
if (intRowFhh <12)
{
if (intRowGhh <= 12)//上午来上午走
{
lunchTime = 0;
dinnerTime = 0;
}
if (intRowGhh < 17)//上午来下午走
{
lunchTime = 1;
dinnerTime = 0;
}
if (intRowGhh >= 17)//上午来晚上走
{
if (intRowGhh == 17 || (intRowGhh == 18 && intRowFmm < 30)) //晚上不足1小时 按17点整
{
intRowGhh = 17;
intRowGmm = 0; //17点整开始
lunchTime = 1;
dinnerTime = 0;
}
else
{
lunchTime = 1;
dinnerTime = 0.5;
}
}
}
if (intRowFhh >12 && intRowGhh <= 17)
{
if (intRowGhh < 17)//下午来下午走
{
lunchTime = 0;
dinnerTime = 0;
}
if (intRowGhh >= 17)//下午来晚上走
{
if (intRowGhh == 17 || (intRowGhh == 18 && intRowFmm < 30)) //晚上不足1小时 按17点整
{
intRowGhh = 17;
intRowGmm = 0; //17点整开始
lunchTime = 0;
dinnerTime = 0;
}
else
{
lunchTime = 0;
dinnerTime = 0.5;
}
}
}
if (intRowGhh > 17)//晚上来晚上走
{
lunchTime = 0;
dinnerTime = 0;
}
if (intRowFmm >= 30) //某点半到 去掉0.5小时
{
mmTimeGo = -0.5;
}
if (intRowGmm >= 30) //某点半走 加上0.5小时
{
mmTimeOff = 0.5;
}
weekendOvertime = intRowGhh - intRowFhh - lunchTime - dinnerTime + mmTimeGo + mmTimeOff;
stringWeekendOvertime = QString::number(weekendOvertime);
if (intRowFhh == 0 || intRowGhh == 0)
{
stringWeekendOvertime = "";
}
stringWrite = stringWeekendOvertime;
}
else
{
if (intRowGhh > 17)
{
if (intRowGmm < 30) //30前
{
weekdayOvertime = intRowGhh - 17 - 0.5;
}
else
{
weekdayOvertime = intRowGhh - 17;
}
stringWeekdayOvertime = QString::number(weekdayOvertime);
if (weekdayOvertime < 1) //小于一小时不计入
{
stringWeekdayOvertime = "";
}
}
else
{
stringWeekdayOvertime = "";
}
stringWrite = stringWeekdayOvertime;
}
//写入数据
//获取K*的位置
QAxObject *range3 = worksheet->querySubObject("Range(QString)", "K"+ cell);
//写入数据, 第*行,第K列
range3->setProperty("Value", stringWrite);
QAxObject* interior = range3->querySubObject("Interior");
interior->setProperty("Color", QColor(150, 150, 0)); //设置单元格背景色
QAxObject* border = range3->querySubObject("Borders");
border->setProperty("Color", QColor(0, 0, 125)); //设置单元格边框色
QAxObject *font = range3->querySubObject("Font"); //获取单元格字体
font->setProperty("Color", QColor(255, 0, 0)); //设置单元格字体颜色(红色)
font->setProperty("Bold", true); //设置单元格字体加粗
iRows--;
qDebug() << "第" << iRows << "行";
}
//获取K*的位置 //写入数据, 第*行,第K列
QAxObject *range3 = worksheet->querySubObject("Range(QString)", "K1");
range3->setProperty("Value", "加班时间");
QAxObject* interior = range3->querySubObject("Interior");
interior->setProperty("Color", QColor(150, 150, 0)); //设置单元格背景色
QAxObject* border = range3->querySubObject("Borders");
border->setProperty("Color", QColor(0, 0, 125)); //设置单元格边框色
QAxObject *font = range3->querySubObject("Font"); //获取单元格字体
font->setProperty("Color", QColor(255, 0, 0)); //设置单元格字体颜色(红色)
font->setProperty("Bold", true); //设置单元格字体加粗
//另存为
//workbook->dynamicCall("SaveAs(const QString &)",QDir::toNativeSeparators(filePath));
//close文件
//workbook->dynamicCall("Close(Boolean)", false);
//excel->dynamicCall("Quit(void)");
//delete excel;
qDebug() << "Over!!" ;
return;
}
main.cpp内容:
#include "OvertimeCalculate.h"
#include <QtWidgets/QApplication>
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
OvertimeCalculate w;
w.setWindowTitle("加班计算");//窗体名称
w.show();
return a.exec();
}
处理结果如图所示: