VS2015+QT Excel相关处理

一、背景介绍

环境：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();
}

处理结果如图所示：