Qt将数据库的表导出为CSV文件
简述：CSV，Comma Separated Value（逗号分隔值），通常都是纯文本文件，以行为单位，每行记录多项数据，每项数据用逗号 来分隔（标准英文逗号）。本文主要介绍将数据库的表导出为CSV文件，支持中文。系统：Qt + linux1、写入CSV文件/*** @brief creatCSVFile()* 创建CSV文件* @return*/......
·
简述：
CSV，Comma Separated Value（逗号分隔值），通常都是纯文本文件，以行为单位，每行记录多项数据，每项数据用逗号 来分隔（标准英文逗号）。
本文主要介绍将数据库的表导出为CSV文件，支持中文。
系统：Qt + linux
1、写入CSV文件
/**
* @brief creatCSVFile()
* 创建CSV文件
* @return
*/
void creatCSVFile()
{
QString tablehead;
tablehead = "StudentName, School, Sex, StudeId, Class, Grade, Hobby, \n";
readAllDataFromSqlWriteToCSV("tb_Student","/home/tb_Student.csv",tablehead);
}
/**
* @brief readAllDataFromSqlWriteToCSV
* 将数据库xx表的所有数据写入CSV文件
* @return
*/
void readAllDataFromSqlWriteToCSV(const QString &tableName, const QString &csvFileName, QString tablehead)
{
if(csvFileName.isEmpty())
return;
//打开.csv文件
QFile csvFile(csvFileName);
if(!csvFile.open(QIODevice::WriteOnly))
{
qDebug()<<QString("Cannot open %1 for writing").arg(csvFile.fileName());
return;
}
QSqlQuery query(db);
QString sql;
QString strString;
QByteArray head = tablehead.toLocal8Bit();
QByteArray rowData;
QTextCodec *code = QTextCodec::codecForName("GB2312"); //解决中文乱码问题
QTextCodec::setCodecForTr(code);
QTextCodec::setCodecForLocale(code);
QTextCodec::setCodecForCStrings(code);
csvFile.write(head.data());
sql ="select * from " + tableName;
query.prepare(sql);
if(query.exec(sql))
{
QSqlRecord sqlRecord = query.record();
while(query.next())
{
strString.clear();
rowData.clear();
//sqlRecord.count():获取列
for(int i = 0;i < sqlRecord.count(); i++)
{
QString value = query.value(i).toString();
if(value.contains(","))
{
value.replace(",",";");
strString += value + ", ";
}
else
{
strString += query.value(i).toString() + "\t, ";
}
}
strString += ", \n";
rowData = strString.toLocal8Bit();
csvFile.write(rowData.data());
}
}
else
{
qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
}
csvFile.close();
QTextCodec *codec = QTextCodec::codecForName("UTF-8"); //GB2312 for window; UTF-8 for linux
QTextCodec::setCodecForTr(codec);
QTextCodec::setCodecForLocale(codec);
QTextCodec::setCodecForCStrings(codec);
}
/**
* @brief readOneDataFromSqlWriteToCSV
* 将数据库xx表的一条数据追加写入CSV文件
* type:0,itemValue; 1,itemStr
* @return
*/
void readOneDataFromSqlWriteToCSV(const QString &tableName, const QString &csvFileName, QString item, QString itemStr, quint64 itemValue, int type)
{
if(csvFileName.isEmpty())
return;
//打开.csv文件
QFile csvFile(csvFileName);
if(!csvFile.open(QIODevice::WriteOnly | QIODevice::Text | QIODevice::Append))
{
qDebug()<<QString("Cannot open %1 for writing").arg(csvFile.fileName());
return;
}
QSqlQuery query(db);
QString sql;
QString strString;
QByteArray rowData;
QTextCodec *code = QTextCodec::codecForName("GB2312"); //GB2312 for window; UTF-8 for linux
QTextCodec::setCodecForTr(code);
QTextCodec::setCodecForLocale(code);
QTextCodec::setCodecForCStrings(code);
if(type == 0)
sql = QString("select * from %1 WHERE %2 = %3 ").arg(tableName).arg(item).arg(itemValue);
else
sql = QString("select * from %1 WHERE %2 = '%3' ").arg(tableName).arg(item).arg(itemStr);
query.prepare(sql);
if(query.exec(sql))
{
QSqlRecord sqlRecord = query.record();
while(query.next())
{
strString.clear();
rowData.clear();
for(int i = 0;i < sqlRecord.count(); i++)
{
QString value = query.value(i).toString();
if(value.contains(","))
{
value.replace(",",";");
strString += value + ", ";
}
else
{
strString += query.value(i).toString() + "\t, ";
}
}
strString += ", \n";
rowData = strString.toLocal8Bit();
csvFile.write(rowData.data());
}
}
else
{
qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
}
csvFile.close();
QTextCodec *codec = QTextCodec::codecForName("UTF-8"); //GB2312 for window; UTF-8 for linux
QTextCodec::setCodecForTr(codec);
QTextCodec::setCodecForLocale(codec);
QTextCodec::setCodecForCStrings(codec);
}例子2 （通用模式）：
void updateLogToCSV()
{
QSqlQuery query(db);
QString header;
QStringList tableList;
tableList << "tb_Log";
foreach (QString tableName, tableList) {
if(getTableHeader(query, tableName, header))
{
QString csvFileName = QString(ORGDATA_ROOT) + tableName + ".csv";
readAllDataFromSqlWriteToCSV(query, tableName,csvFileName, header);
}
}
}
/*******************************************************************************
@ Name : getTableHeader
@Description : 获取数据表字段-表头
@ Author : Hebbe
@ Arguments :
* query
* tableName 数据表名称
* header 数据表表头
@ Returns : bool
********************************************************************************/
bool getTableHeader(QSqlQuery &query, const QString &tableName, QString &header)
{
header.clear();
QString str = QString("PRAGMA table_info('%1')").arg(tableName);
query.prepare(str);
if (query.exec())
{
while (query.next())
{
header += query.value(1).toString();
// qDebug()<<"qqqqq.....:"<<query.value(0).toString()<<","<<query.value(1).toString()<<","<<query.value(2).toString();
header += ",";
}
header += "\n";
return true;
}else{
qDebug() << query.lastError();
return false;
}
}
/*******************************************************************************
@ Name : readAllDataFromSqlWriteToCSV
@Description : 从数据库读取数据并生成CSV文件
@ Author : Hebbe
@ Arguments :
* query
* tableName 数据表名称
* csvFileName CSV文件名称
* header 数据表表头
@ Returns : bool
********************************************************************************/
void readAllDataFromSqlWriteToCSV(QSqlQuery &query, const QString &tableName, const QString &csvFileName, QString header)
{
//两种方式均可
#if 1
if(csvFileName.isEmpty())
return;
QFile file(csvFileName);
//打开.csv文件
if(!file.open(QIODevice::WriteOnly))
{
qDebug() << "Open failed -> " << file.fileName();
return;
}
QByteArray head = header.toLocal8Bit();
QTextCodec::setCodecForLocale(QTextCodec::codecForName("GB2312"));
file.write(head.data());
QString sql ="select * from " + tableName;
query.prepare(sql);
if(query.exec(sql))
{
QSqlRecord sqlRecord = query.record();
while(query.next())
{
QString strString;
QByteArray rowData;
for(int i = 0;i < sqlRecord.count(); i++)
{
QString value = query.value(i).toString();
if(value.contains(","))
{
value.replace(",",";");
strString += value + ", ";
}
else
{
strString += query.value(i).toString() + "\t, ";
}
}
strString += " \n";
rowData = strString.toLocal8Bit();
file.write(rowData.data());
}
}
else
{
qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
}
file.close();
QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));
#else
if(csvFileName.isEmpty())
return;
QFile file(csvFileName);
//打开.csv文件
if(!file.open(QIODevice::WriteOnly))
{
qDebug() << "Open failed -> " << file.fileName();
return;
}
QTextStream out(&file);
out.setCodec("GB2312");
out << header;
QString sql ="select * from " + tableName;
query.prepare(sql);
if(query.exec(sql))
{
QSqlRecord sqlRecord = query.record();
while(query.next())
{
QString strString;
for(int i = 0;i < sqlRecord.count(); i++)
{
QString value = query.value(i).toString();
if(value.contains(","))
{
value.replace(",",";");
strString += value + ", ";
}
else
{
strString += query.value(i).toString() + "\t, ";
}
}
strString += " \n";
out<<strString;
}
}
else
{
qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
}
file.close();
#endif
}注意：Excel/CSV显示数字时，如果数字大于12位，它会自动转化为科学计数法；如果数字大于15位，它不仅用于科学技术费表示，还会只保留高15位，其他位都变0。
解决方案：只要把数字字段后面加上显示上看不见的字符即可，字符串前面或者结尾加上制表符 "\t"
2、读取CSV 文件
/**
* @brief getDataFromCSV(QString fileName)
* 读取CSV文件
* @return
*/
void getDataFromCSV(QString fileName)
{
QFile csvFile(fileName);
QStringList csvList;
csvList.clear();
if (csvFile.open(QIODevice::ReadWrite))
{
QTextStream stream(&csvFile);
while (!stream.atEnd())
{
csvList.push_back(stream.readLine());
}
csvFile.close();
}
Q_FOREACH(QString str, csvList)
{
qDebug() << str<<"\n";
}
}
一站式 AI 云服务平台
更多推荐
2
0- 0
已为社区贡献2条内容
所有评论(0)