关于qt与excel

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC")

if(!db.isValid())

{

qDebug() <<"export2Excel failed: QODBC not supported."

return

}

QString xlsFile = "D:/test.xls"

QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)}Readonly=0DriverId=790DBQ=%1").arg(xlsFile)

db.setDatabaseName(dsn)

if(!db.open())

{

//错误处理

return

}

QString queryystr = "select * from [sheet1$]"

SqlTableModel*excelTableModel = new SqlTableModel(this, db)

QString tableName = "[sheet1$]"

excelTableModel->setTable(tableName)

QSqlQuery querry(queryystr, db)

excelTableModel->setQuery(querry)

excelTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit)

excelTableModel->setSort(0, Qt::AscendingOrder)

bool ok = excelTableModel->select()//选取整个表的所有行

ui.tableView_2->setModel(excelTableModel)

int i = 0

foreach(QString heardName, heardList)

{

excelTableModel->setHeaderData(i, Qt::Horizontal, heardName)

i++

}

int sqlTableCount = m_sqlTableModel->rowCount()

int excelTableCount = excelTableModel->rowCount()

QList<QSqlRecord>tmprecord

for (int row = 0row <excelTableCountrow++ )

{

QSqlRecord excelRecord = excelTableModel->record(row)

tmprecord <<excelRecord

}

db.close()

bool open = m_dba.open()

foreach(QSqlRecord record, tmprecord)

{

bool ok = m_sqlTableModel->insertRow(sqlTableCount)//添加一行

for (int k = 0k<5k++)

{

if (k == 0)

{

bool suc= m_sqlTableModel->setData(m_sqlTableModel->index(sqlTableCount,k),record.value(k), Qt::CheckStateRole)

}

bool suc= m_sqlTableModel->setData(m_sqlTableModel->index(sqlTableCount,k),record.value(k), Qt::EditRole)

}

sqlTableCount++

}

AlterButtonClicked()

initWindow(TEACHERMANAGECLICKED)

AlterButtonClicked()

{

m_sqlTableModel->database().transaction()//开始事务 *** 作

if (m_sqlTableModel->submitAll())

{

m_sqlTableModel->database().commit()//提交

}

else

{

m_sqlTableModel->database().rollback()//回滚

QMessageBox::warning(this, tr("Model"), tr("数据库错误: %1").arg(m_sqlTableModel->lastError().text()))

}

}

initWindow(int index)

{

int i = 0

heardList.clear()

if (index == STUDENTMANAGECLICKED)

{

heardList <<"选择" <<"编号" <<"姓名" <<"性别" <<"班级" <<"备注"

m_sqlTableModel->setTable("\"StudentInfo\"")

m_sqlTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit)

m_sqlTableModel->setSort(0, Qt::AscendingOrder)

m_sqlTableModel->select()//选取整个表的所有行

ui.tableView_2->setModel(m_sqlTableModel)

foreach(QString heardName, heardList)

{

m_sqlTableModel->setHeaderData(i, Qt::Horizontal, heardName)

i++

}

}

else if (index == TEACHERMANAGECLICKED)

{

heardList <<"选择" <<"用户名" <<"姓名" <<"性别" <<"角色" <<"备注"

m_sqlTableModel->setTable("\"TeacherInfo\"")

m_sqlTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit)

m_sqlTableModel->setSort(0, Qt::AscendingOrder)

bool ok1 = m_sqlTableModel->select()//选取整个表的所有行

ui.tableView_2->setModel(m_sqlTableModel)

foreach(QString heardName, heardList)

{

m_sqlTableModel->setHeaderData(i, Qt::Horizontal, heardName)

i++

}

}

//m_sqlTableModel->insertColumn(0)

ui.tableView_2->resizeColumnsToContents()

}

Excel读取

便于测试假设已存excel文件 *** 作内容已经红色标记所示：

主要读取内容：

标题

工作表数目

工作表名称

起始行

起始列

行数

列数

单元格内容

代码：

QAxObject excel("Excel.Application")

excel.setProperty("Visible", true)

QAxObject *work_books = excel.querySubObject("WorkBooks")

work_books->dynamicCall("Open (const QString&)", QString("E:/test.xlsx"))

QVariant title_value = excel.property("Caption")//获取标题

qDebug()<<QString("excel title : ")<<title_value

QAxObject *work_book = excel.querySubObject("ActiveWorkBook")

QAxObject *work_sheets = work_book->querySubObject("Sheets")//Sheets换用WorkSheets

Excel读取

为了便于测试，假设已存在一个excel文件， *** 作内容已经被红色标记出来。如下所示：

主要读取内容：

标题

工作表数目

工作表名称

起始行

起始列

行数

列数

单元格内容

代码如下：

QAxObject excel("Excel.Application")

excel.setProperty("Visible", true)

QAxObject *work_books = excel.querySubObject("WorkBooks")

work_books->dynamicCall("Open (const QString&)", QString("E:/test.xlsx"))

QVariant title_value = excel.property("Caption") //获取标题

qDebug()<<QString("excel title : ")<<title_value

QAxObject *work_book = excel.querySubObject("ActiveWorkBook")

QAxObject *work_sheets = work_book->querySubObject("Sheets") //Sheets也可换用WorkSheets

int sheet_count = work_sheets->property("Count").toInt() //获取工作表数目

qDebug()<<QString("sheet count : ")<<sheet_count

for(int i=1i<=sheet_counti++)

{

QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", i) //Sheets(int)也可换用Worksheets(int)

QString work_sheet_name = work_sheet->property("Name").toString() //获取工作表名称

QString message = QString("sheet ")+QString::number(i, 10)+ QString(" name")

qDebug()<<message<<work_sheet_name

}

if(sheet_count >0)

{

QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1)

QAxObject *used_range = work_sheet->querySubObject("UsedRange")

QAxObject *rows = used_range->querySubObject("Rows")

QAxObject *columns = used_range->querySubObject("Columns")

int row_start = used_range->property("Row").toInt() //获取起始行

int column_start = used_range->property("Column").toInt() //获取起始列

int row_count = rows->property("Count").toInt() //获取行数

int column_count = columns->property("Count").toInt() //获取列数

for(int i=row_starti

{

for(int j=column_startj

{

QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", i, j)

QVariant cell_value = cell->property("Value") //获取单元格内容

QString message = QString("row-")+QString::number(i, 10)+QString("-column-")+QString::number(j, 10)+QString(":")

qDebug()<<message<<cell_value

---