Am ende kann ich super dokumente herstellen wie Crystal Reports 2008, oder serie briefen as xslt seit qt4.5 geht dass prima, oder sqltabellen zu xml excel 2008 convertieren, die man als normale excel aufmachen kann.
Beispiel code unten...
Dabei bemerkte ich wenn man mehrere QSqlDatabase mysql verbindungen macht kommt immer am schluss ein fehler :
my_thread_global_end() 3 thread not exit ... was der selbe fehler ist wie php version 5 mit mysql version 5
zum lesen bei...
http://www.google.ch/search?q=my_thread_global_end
um diesen fehler nicht mehr zu bekommen einfach die libmysql.dll in libmysql.old umbenennen
und mit http://ppk.ciz.ch/win_build/new_dll/libmysql.dll ersetzen
die fehler dll hat 2MB die korrekte funktionierende dll 1.4 MB
dabei muss man qt nicht neu bauen .. eben nur die dll austauschen.
beispiel sqlquery zu excel xml dass mit OpenOffice oder Excel normal auf geht..
Code: Alles auswählen
/* excel xml 2008 reformat from sql query */
QByteArray xmlexcel2008( const QString query , const QString connection_name )
{
bool isopen = false;
QString tablename("Table1");
QStringList m_header;
QSqlQueryModel *mod;
QSqlDatabase db = QSqlDatabase::database(connection_name,&isopen);
if (!isopen)
db.open();
if (db.isOpen()) {
mod = new QSqlQueryModel();
mod->setQuery(query,db);
}
QString excelNS,xsiNS,xNS,x2NS,oNS,htmlNS,cNS ;
excelNS = QString("urn:schemas-microsoft-com:office:spreadsheet");
xsiNS = QString("http://www.w3.org/2001/XMLSchema-instance");
xNS = QString("urn:schemas-microsoft-com:office:excel");
x2NS = QString("http://schemas.microsoft.com/office/excel/2003/xml");
oNS = QString("urn:schemas-microsoft-com:office:office");
htmlNS = QString("http://www.w3.org/TR/REC-html40");
cNS = QString("urn:schemas-microsoft-com:office:component:spreadsheet");
QBuffer *d = new QBuffer();
d->open(QIODevice::ReadWrite);
QXmlStreamWriter writer( d );
writer.setCodec("UTF-8");
writer.setAutoFormatting(true);
writer.setAutoFormattingIndent(0);
writer.writeNamespace(xsiNS,QString::fromLatin1("xsi"));
writer.writeNamespace(xNS,QString::fromLatin1("x"));
writer.writeNamespace(x2NS,QString::fromLatin1("x2"));
writer.writeNamespace(excelNS,QString::fromLatin1("ss"));
writer.writeNamespace(oNS,QString::fromLatin1("o"));
writer.writeNamespace(htmlNS,QString::fromLatin1("html"));
writer.writeNamespace(cNS,QString::fromLatin1("c"));
/*
correct namenspace order
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:html="http://www.w3.org/TR/REC-html40"
xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">
*/
writer.writeStartDocument();
writer.writeStartElement(excelNS,QString::fromLatin1("Workbook"));
/*
writer.writeAttribute(QString::fromLatin1("xmlns"),QString("urn:schemas-microsoft-com:office:spreadsheet"));
writer.writeAttribute(QString::fromLatin1("xmlns:xsi"),QString("http://www.w3.org/2001/XMLSchema-instance"));
writer.writeAttribute(QString::fromLatin1("xmlns:x"),QString("urn:schemas-microsoft-com:office:excel"));
writer.writeAttribute(QString::fromLatin1("xmlns:x2"),QString("http://schemas.microsoft.com/office/excel/2003/xml"));
writer.writeAttribute(QString::fromLatin1("xmlns:ss"),QString("urn:schemas-microsoft-com:office:spreadsheet"));
writer.writeAttribute(QString::fromLatin1("xmlns:o"),QString("urn:schemas-microsoft-com:office:office"));
writer.writeAttribute(QString::fromLatin1("xmlns:html"),QString("http://www.w3.org/TR/REC-html40"));
writer.writeAttribute(QString::fromLatin1("xmlns:c"),QString("urn:schemas-microsoft-com:office:component:spreadsheet"));
*/
writer.writeStartElement(excelNS,QString::fromLatin1("Styles"));
writer.writeStartElement(excelNS,QString::fromLatin1("Style"));
writer.writeAttribute(excelNS,QString::fromLatin1("ID"),QString("ce_head"));
writer.writeStartElement(excelNS,QString::fromLatin1("Alignment"));
writer.writeAttribute(excelNS,QString::fromLatin1("Horizontal"),QString("Center"));
writer.writeAttribute(excelNS,QString::fromLatin1("Indent"),QString("0"));
writer.writeEndElement();
writer.writeStartElement(excelNS,QString::fromLatin1("Font"));
writer.writeAttribute(excelNS,QString::fromLatin1("Bold"),QString("1"));
writer.writeAttribute(excelNS,QString::fromLatin1("Italic"),QString("1"));
writer.writeAttribute(excelNS,QString::fromLatin1("Size"),QString("9"));
writer.writeStartElement(excelNS,QString::fromLatin1("Interior"));
writer.writeAttribute(excelNS,QString::fromLatin1("Color"),QString("#ebebeb"));
writer.writeAttribute(excelNS,QString::fromLatin1("Pattern"),QString("Solid"));
writer.writeEndElement();
writer.writeEndElement();
writer.writeEndElement(); ///// Styles 1
writer.writeStartElement(excelNS,QString::fromLatin1("Style")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("ID"),QString("ce1"));
writer.writeStartElement(excelNS,QString::fromLatin1("Alignment")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("Horizontal"),QString("Right"));
writer.writeAttribute(excelNS,QString::fromLatin1("Indent"),QString("0"));
writer.writeEndElement();
writer.writeStartElement(excelNS,QString::fromLatin1("Interior")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("Color"),QString("#e4fdff"));
writer.writeAttribute(excelNS,QString::fromLatin1("Pattern"),QString("Solid"));
writer.writeEndElement();
writer.writeEndElement(); ///// Style 2
writer.writeStartElement(excelNS,QString::fromLatin1("Style")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("ID"),QString("ce2"));
writer.writeStartElement(excelNS,QString::fromLatin1("Alignment")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("Horizontal"),QString("Left"));
writer.writeAttribute(excelNS,QString::fromLatin1("Indent"),QString("2"));
writer.writeEndElement();
writer.writeStartElement(excelNS,QString::fromLatin1("Interior")); //// 0
writer.writeAttribute(excelNS,QString::fromLatin1("Color"),QString("#ffffe5"));
writer.writeAttribute(excelNS,QString::fromLatin1("Pattern"),QString("Solid"));
writer.writeEndElement();
writer.writeEndElement();
writer.writeEndElement(); ///// Styles 0
/* Styles */
writer.writeStartElement(excelNS,QString::fromLatin1("Worksheet"));
writer.writeAttribute(excelNS,QString::fromLatin1("Name"),tablename);
writer.writeStartElement(excelNS,QString::fromLatin1("Table"));
if (db.isOpen()) {
for (int i = 0; i < mod->columnCount(); ++i) {
const QString Htext = mod->headerData(i,Qt::Horizontal,Qt::DisplayRole).toString();
m_header.append(Htext);
}
for (int i = 0; i < m_header.size(); ++i) {
writer.writeStartElement(excelNS,QString::fromLatin1("Column"));
writer.writeAttribute(QString::fromLatin1("Width"),QString("100"));
writer.writeEndElement();
}
writer.writeStartElement(excelNS,QString::fromLatin1("Row"));
writer.writeAttribute(excelNS,QString::fromLatin1("StyleID"),QString("ce_head"));
for (int i = 0; i < m_header.size(); ++i) {
writer.writeStartElement(excelNS,QString::fromLatin1("Cell"));
writer.writeStartElement(excelNS,QString::fromLatin1("Data"));
writer.writeAttribute(excelNS,QString::fromLatin1("Type"),QString("String"));
writer.writeCharacters(m_header.at(i));
writer.writeEndElement();
writer.writeEndElement();
}
writer.writeEndElement();
for (int i = 0; i < mod->rowCount(); ++i)
{
writer.writeStartElement(excelNS,QString::fromLatin1("Row"));
QSqlRecord r = mod->record(i);
for (int j = 0; j < m_header.size(); ++j) {
QString curr = StringStripslash(r.value(j).toString());
if (GrepNummer(curr)) {
writer.writeStartElement(excelNS,QString::fromLatin1("Cell"));
writer.writeAttribute(excelNS,QString::fromLatin1("StyleID"),QString("ce1"));
writer.writeStartElement(excelNS,QString::fromLatin1("Data"));
writer.writeAttribute(QString::fromLatin1("Type"),QString("Number"));
writer.writeCharacters(curr);
writer.writeEndElement();
writer.writeEndElement();
} else {
writer.writeStartElement(excelNS,QString::fromLatin1("Cell"));
writer.writeAttribute(excelNS,QString::fromLatin1("StyleID"),QString("ce2"));
writer.writeStartElement(excelNS,QString::fromLatin1("Data"));
writer.writeAttribute(excelNS,QString::fromLatin1("Type"),QString("String"));
writer.writeCharacters(curr);
writer.writeEndElement();
writer.writeEndElement();
}
}
writer.writeEndElement();
}
}
writer.writeEndElement(); //// Table
writer.writeEndElement(); //// Worksheet
writer.writeEndElement(); //// Workbook
writer.writeEndDocument();
QByteArray stream = d->data();
d->close();
stream.replace("<?xml version=\"1.0\" encoding=\"UTF-8\"?>","");
QByteArray stream0("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<?mso-application progid=\"Excel.Sheet\"?>");
stream0.append(stream);
return stream0;
}