qt导入导出excel和csv

说明：从qtableview导入导出excel和csv文件，excel包括xls和xlsx文件，需要包含头文件和lib库

#include <ActiveQt\QAxObject>

Qt5AxContainerd.lib
Qt5AxServerd.lib
Qt5AxBased.lib

1、导入excel

void qexceldemo::ImportExcel(QString fileName)
{
QAxObject *excel = NULL;
QAxObject *workbooks = NULL;
QAxObject *workbook = NULL;
QAxObject *cell=NULL;


excel = new QAxObject("Excel.Application");
if (excel->isNull()) 
{
if (excel != NULL)//网络中很多使用excel==NULL判断，是错误的
{
excel->dynamicCall("Quit (void)");
delete excel;
}


QMessageBox::critical(0, "Import", "not find EXCEL!");
return;
}
excel->dynamicCall("SetVisible(bool)", false);
workbooks = excel->querySubObject("WorkBooks");
workbook = workbooks->querySubObject("Open(QString,QVariant,QVariant)", fileName,3,true);//两个参数时，三个参数true和false都很正常，false 锁定excel文件，其它程序只能只读方式打开，否则程序正在处理excel文件时，在外面打开excel，程序异常退出
if (!workbook) 
{
if (excel != NULL)
{
excel->dynamicCall("Quit (void)");
delete excel;
}
QMessageBox::critical(0, "Import", "excel is not exist!");
return;
}
QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1);//打开第一个sheet
QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
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();
QString str;


ui.tableWidgetExcel->setRowCount(intRows);
ui.tableWidgetExcel->setColumnCount(intCols);


for(int i=intRowStart;i <intRowStart + intRows;i++)
{
for(int j=intColStart ;j<intColStart+intCols;j++)
{
cell = worksheet->querySubObject("Cells(int,int)", i,j ); //获取单元格
str = cell->property("Value").toString();
if(cell->property("Value").type()==QVariant::Double)
{
qDebug()<<QString::number(cell->property("Value").toDouble(),'f',0);
}
else if(cell->property("Value").type()==QVariant::String)
{
qDebug()<<str;
}
QTableWidgetItem *item = new QTableWidgetItem(str);
ui.tableWidgetExcel->setItem(i - intRowStart, j - intColStart, item);
}
}
workbook->dynamicCall("Close (Boolean)", false);
excel->dynamicCall("Quit (void)");
delete workbook;
delete workbooks;
delete excel;
}

2、导出excel

void qexceldemo::ExportExcel(QString fileName)
{
QAxObject *excel = new QAxObject("Excel.Application");
if (excel->isNull()) 
{
if (excel != NULL)//网络中很多使用excel==NULL判断，是错误的
{
excel->dynamicCall("Quit()");
delete excel;
}
QMessageBox::critical(0, "错误信息", "没有找到EXCEL应用程序");
return;
}
QAxObject *workbooks = NULL;
QAxObject *workbook = NULL;
QAxObject *worksheets = NULL;
QAxObject *worksheet = NULL;


excel->dynamicCall("SetVisible (bool)", false);//不显示窗体
excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合


if (QFile::exists(fileName))
{
workbook = workbooks->querySubObject("Open(const QString &)", fileName);
}
else
{
workbooks->dynamicCall("Add");//新建一个工作簿
workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
}


worksheets = workbook->querySubObject("Sheets");//获取工作表集合
worksheet = worksheets->querySubObject("Item(int)",1);//获取工作表集合的工作表1，即sheet1


//删除工作表（删除第一个）
//QAxObject *first_sheet = worksheets->querySubObject("Item(int)", 1);
//first_sheet->dynamicCall("delete");


插入工作表（插入至最后一行）
//QAxObject *last_sheet = worksheets->querySubObject("Item(int)", 1);
//worksheet = worksheets->querySubObject("Add(QVariant)", last_sheet->asVariant());
//last_sheet->dynamicCall("Move(QVariant)", worksheet->asVariant());
//worksheet->setProperty("Name", "Sheet1");  //设置工作表名称


QAxObject * usedrange = worksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
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();


QAxObject *cell = NULL;
QString str;
int rowCount = ui.tableWidgetExcel->rowCount();
int colCount = ui.tableWidgetExcel->columnCount();


// 清空数据
for(int i=intRowStart;i <intRowStart + intRows;i++)
{
for(int j=intColStart ;j<intColStart+intCols;j++)
{
cell = worksheet->querySubObject("Cells(int,int)", i,j ); //获取单元格
cell->setProperty("Value", "");
}
}


// 插入数据
for (int i = intRowStart; i < intRowStart + rowCount; i++)
{
for(int j = intColStart; j < intColStart + colCount; j++)
{
str = ui.tableWidgetExcel->item(i - intRowStart, j - intColStart)->text();
cell = worksheet->querySubObject("Cells(int,int)", i, j);//获取单元格
cell->setProperty("Value", str);
}
}


workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
workbook->dynamicCall("Close()");//关闭工作簿
excel->dynamicCall("Quit()");//关闭excel
delete workbook;
delete workbooks;
delete excel;
}

3、导入csv

void qexceldemo::ImportCsv(QString fileName)
{
QFile file(fileName);
if(!file.open(QIODevice::ReadOnly | QIODevice::Text))
{
qDebug() << "Open file failed!";
return;
}

QList<QStringList> qlist;
QStringList strlist;
QTextStream in(&file);
int row = 0;
int col = 0;


while(!in.atEnd())
{
QString fileLine = in.readLine();
strlist = fileLine.split(",", QString::SkipEmptyParts);
qlist.push_back(strlist);


// 取更小值
if (col == 0)
{
col = strlist.count();
}
else
{
col = col < strlist.count() ? col : strlist.count();
}
row++;
}
ui.tableWidgetExcel->setRowCount(row);
ui.tableWidgetExcel->setColumnCount(col);


for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
QTableWidgetItem *item = new QTableWidgetItem(qlist[i][j]);
ui.tableWidgetExcel->setItem(i, j, item);
}
}


file.close();
}

4、导出csv

void qexceldemo::ExportCsv(QString fileName)
{
//打开.csv文件
QFile file(fileName);
if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
{
qDebug() << "Open file failed!";
return;
}


QTextStream out(&file);
QString str;


//获取表格内容
int row = ui.tableWidgetExcel->rowCount();//表格总行数
int col = ui.tableWidgetExcel->columnCount();
for(int i = 0; i < row; i ++)
{
for(int j = 0; j < col; j++)
{
str = ui.tableWidgetExcel->item(i, j)->text();
out << str << ",";// 写入文件
}
out << "\n";
}


file.close();
}