Qt-QXLS应用

一、写Excel

1. 创建Document

QXlsx::Document doc(fileName);

2. 添加并选中表单

doc.addSheet(sheetName);
doc.selectSheet(sheetName);

3. 表格样式设置

QXlsx::Format cell_format;
cell_format.setBorderStyle(QXlsx::Format::BorderThin);
cell_format.setHorizontalAlignment(QXlsx::Fromat::AlignHCenter);
cell_format.setNumberFormat("0.000");//以0.000的形式显示数据
cell_format.setFontColor(QColor(Qt::green));
//设置的单元格背景颜色
cell_format.setPatternBackgroundColor(QColor(255,255,255));
//设置下划线样式
cell_format.setFontUnderline(QXlsx::Format::FontUnderlineDouble);
cell_format.setFillPattern(QXlsx::Format::PatternLightUp);

4. 写数据

//行、列编号从1开始算起
//value是QVariant类型，cell_format默认是QXlsx::Format()
doc.write(rowIndex, colIndex, value, cell_format);

5. 绘制图表

QXlsx::Chart* crom = doc.insertChart(rowIndex, colIndex, QSize(600,500)); //doc是在写入数据以后的doc
crom.setChartType(Chart::CT_ScatterChart);//设置图表类型，此处表示绘制成散点图
crom.addSeries(CellRange(A8:B2444));//根据选取的数据范围绘制折线
crom->setAxisTitle( Chart::Left, QString("left title") );
crom->setAxisTitle( Chart::Bottom, QString("bottom title") );
crom->setChartTitle( QString("hello chart") );

6. 保存

doc.saveAs(filePath);

二、读Excel

1. 读取xlsx

Document doc("./color.xlsx");
bool bret = doc.load();
//也可通过下面的方式判断是否加载成功
if(!doc.isLoadPackage())
{
    return;
}

2. 遍历表格

Document doc(fileName);
doc.load();
int sheetIndexNumber = 0;
//doc.sheetNames()可以获取表格的所有表单名称
for(const QString currentSheetName : doc.sheetNames())
{
    //根据名称获取表单
    QXlsx::AbstractSheet* currentSheet = doc.sheet(currentSheetName);
    if(!currentSheet)
        continue;
    int maxRow = -1;
    int maxCol = -1;
    //将该表单置顶并激活
    currentSheet->workbook()->setActiveSheet(sheetIndexNumber);
    //获取当前工作表单
    Worksheet* wsheet = (Worksheet*)currentSheet->workbook()->activeSheet();
    if(!wsheet)
        continue;
    //获取表格所有数据的所在位置
    QVector<CellLocation> clList = wsheet->getFullCells(&maxRow, &maxCol);
    //遍历数据
    for(int ic = 0; ic < clList.size(); ++ic)
    {
        CellLocation cl = clList.at(ic);
        int row = cl.row - 1;
        int col = cl.col - 1;
        //获取元素
        QSharedPointer<Cell> ptrCell = cl.cell;
        QVariant var = cl.cell.data()->value();
        QString str = var.toString();
    }
}

3. read与cellAt的区别

using namespace QXlsx;
Document doc;
doc.write("A1", QVariant(QDateTime::currentDateTimeUtc()));
//输出：QDateTime QVariant(QDateTime, 2020-11-23 13:54:21.839 中国标准时间 Qt::TimeSpec(LocalTime)))
qDebug() << doc.read(1,1).type() << doc.read(1,1);
//输出：double QVariant(double, 44158.6)
qDebug() << doc.cellAt(1,1).type << doc.cellAt(1,1)->value();

4. 读写Image

Document doc;
for(int i = 0; i <= 10; ++i)
{
    QImage img(40, 30, QImage::Format_RGB32);
    image.fill(uint(qrand() % 16581275));
    //插入图片
    int index = xlsx.insertImage(10*i, 5, image);
    QImage img2;
    if(xlsx.getImage(index, img2))//获取图片
    {
        QString fileName;
        fileName = QString("image %1.png").arg(index);
        img2.save(fileName);
    }
}

三、表单操作

1. 添加表单

Document doc;
doc.addSheet();//默认名称："Sheet1"
doc.addSheet("HiddenSheet");

2. 基本操作

//重命名
doc.renameSheet("Sheet1", "TheFirstSheet");
//拷贝
doc.copySheet("TheFirstSheet", "CopyOfTheFirst");
//选中
doc.selectSheet("CopyOfTheFirst");
//删除
doc.deleteSheet("CopyOfTheFirst");
//移动
doc.moveSheet("HiddenSheet", 1);
//显示/隐藏
doc.sheet("HiddenSheet").setVisible(true);
doc.sheet("HiddenSheet").setHidden(true);

四、行列相关设置

1. 行高和列宽设置

Document doc;
//关闭边框显示
doc.currentWorksheet()->setGridLinesVisible(false);
doc.setRowHeight(1, 10, 50.0);//设置第一行到第10行行高
doc.setColumnWidth(1, 3, 40.0);//设置第一列到第三列列宽

2. 设置行样式

QXlsx::Format format1;
format1.setFontBold(true);
format1.setFontColor(QColor(Qt::blue));
format1.setFontSize(20);
doc.setRowFormat(1, 11, format1);//设置1到11行的样式

3. 设置列样式

doc.setColumnFormat(1, 3, format1);//设置1到3列的样式

4. 列自适应

doc.autosizeColumnWidth();
doc.autosizeColumnWidth(firstCol, lastCol);

五、富文本显示设置

QXlsx::Format blue;
blue.setFontColor(Qt::Blue);
QXlsx::Format bold;
blod.setFontBold(true);
QXlsx::RichString rich;
rich.addFragment("Hello", blue);
rich.addFragment("Xlsx", bold);
doc.write("B2", rich);//以设定的样式写入B2单元格
//写html
doc.workbook()->setHtmlToRichStringEnabled(true);
doc.write("B4", "<b>Hello</b> <font color=\"red\">Qt</font> <i>Xlsx</i>");

六、单元格合并

Document doc;
QXlsx::Format format;
format.setHorizontalAlignment(Format::AlignHCenter);
format.setVerticalAlignment(Format::AlignVCenter);
doc.write("B4", "Hello Qt!");
doc.mergeCells("B4:F6", format);//合并B4到F6范围的单元格，并居中显示

七、文档属性设置

Document doc;
doc.setDocumentProperty("title", "This is an example spreadsheet");
doc.setDocumentProperty("subject", "With document properties");
doc.setDocumentProperty("creator", "Debao Zhang");
xlsx.setDocumentProperty("company", "HMICN");
doc.setDocumentProperty("category", "Example spreadsheets");
doc.setDocumentProperty("keywords", "Sample, Example, Properties");
doc.setDocumentProperty("description", "Created with Qt Xlsx");

八、宏操作

//定义宏：A1到A10表示MyCol_1
doc.defineName("MyCol_1", "=Sheet1!$A$1:$A$10");
doc.defineName("MyCol_2", "=Sheet1!$B$1:$B$10", "This is comments");
doc.defineName("MyCol_3", "=Sheet1!$C$1:$C$10", "", "Sheet1");
doc.defineName("Factor", "=0.5");
//对第一列数据求和并写入11,1单元格
doc.write(11, 1, "=SUM(MyCol_1)");
doc.write(11, 2, "=SUM(MyCol_2)");
doc.write(11, 3, "=SUM(MyCol_3)");
//对第一列数据求和并*factor
doc.write(12, 1, "=SUM(MyCol_1)*Factor");
doc.write(12, 2, "=SUM(MyCol_2)*Factor");
doc.write(12, 3, "=SUM(MyCol_3)*Factor");
doc.write("B3", 40, lAlign);
doc.write("B4", 30, lAlign);
doc.write("B5", 50, lAlign);
doc.write("A7", "SUM(B3:B5)=", rAlign);
doc.write("B7", "=SUM(B3:B5)", lAlign);
doc.write("A8", "AVERAGE(B3:B5)=", rAlign);
doc.write("B8", "=AVERAGE(B3:B5)", lAlign);
doc.write("A9", "MAX(B3:B5)=", rAlign);
doc.write("B9", "=MAX(B3:B5)", lAlign);
doc.write("A10", "MIN(B3:B5)=", rAlign);
doc.write("B10", "=MIN(B3:B5)", lAlign);
doc.write("A11", "COUNT(B3:B5)=", rAlign);
doc.write("B11", "=COUNT(B3:B5)", lAlign);
doc.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
doc.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
doc.write("A15", "SQRT(25)=", rAlign);
doc.write("B15", "=SQRT(25)", lAlign);
doc.write("A16", "RAND()=", rAlign);
doc.write("B16", "=RAND()", lAlign);
doc.write("A17", "2*PI()=", rAlign);
doc.write("B17", "=2*PI()", lAlign);
doc.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
doc.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
doc.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
doc.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
doc.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
doc.write("B21", "=LEN(\"Hello Qt!\")", lAlign);

九、数据验证

//限制数据在33-99之间
DataValidation validation(DataValidation::Whole, DataValidation::Between, "33", "99");
//A2和 A3到E5范围内的单元需要满足该限制
validation.addRange("A2");
validation.addRange("A3:E5");
//提示框
validation.setPromptMessage("Please Input Integer between 33 and 99");
doc.addDataValidation(validation);

十、分组

doc.groupRows(4, 7);
doc.groupRows(11, 26, false);
doc.groupRows(15, 17, false);
doc.groupRows(20, 22, false);
doc.setColumnWidth(1, 10, 10.0);
doc.groupColumns(1, 2);
doc.groupColumns(5, 8, false);

十一、样式获取

1. Google spread sheet

int readGoogleSpreadsheet()
{
Document xlsx("google-spreadsheet.xlsx"); // google docs 

if (!xlsx.isLoadPackage())
{
qDebug() << "[readGoogleSpreadsheet] failed to load package"; 
return (-1); 
}

// current sheet is default sheet. (Sheet1)
for (int row = 1; row < 20; ++row)
{
Cell* cell = xlsx.cellAt(row, 1);
if ( cell == NULL )
continue;
QVariant var = cell->readValue();
qint32 styleNo = cell->styleNumber();

Format fmt = cell->format();
QString strFormat; 
if (fmt.hasNumFmtData())
{
QString strNumFormat = fmt.numberFormat();
if ( ! strNumFormat.isEmpty() )
strFormat = strFormat + QString(" number format : ") + strNumFormat;
}

if (fmt.hasFontData() ) 
{
Format::FontScript fs = fmt.fontScript(); 
}

if (fmt.hasFillData())
{
int fillIndex = fmt.fillIndex();
QByteArray ba = fmt.fillKey();
}

if (fmt.hasBorderData())
{

}

if (fmt.hasAlignmentData())
{

}

if (fmt.hasProtectionData())
{

}

if ( styleNo >= 0 )
{
qDebug() << row << " " << var << " , style:" << styleNo << strFormat;
}
else
{
qDebug() << row << " " << var << strFormat;
}
}
return 0; 
}

2. office excel

int readMSExcel201xNumber(QXlsx::Document* pXlsx)
{
if (NULL == pXlsx)
return (-1);

for (int row = 1; row < 10; ++row)
{
Cell* cell = pXlsx->cellAt(row, 1);
if (cell == NULL)
continue;

QVariant var = cell->readValue();
qint32 styleNo = cell->styleNumber();
Format fmt = cell->format();

QString strFomrat;
if (fmt.hasNumFmtData())
{
QString strNumFormat = fmt.numberFormat();
strFomrat = strFomrat + QString(" number format :") + strNumFormat;
}

if (styleNo >= 0)
{
qDebug() << row << " " << var << " , style:" << styleNo << strFomrat;
}
else
{
qDebug() << row << " " << var << strFomrat;
}
}
return 0;
}

十二、图表处理

1. 饼状图

Chart *pieChart = doc.insertChart(3, 3, QSize(300, 300));
pieChart->setChartType(Chart::CT_PieChart);
pieChart->addSeries(CellRange("A1:A9"));
pieChart->addSeries(CellRange("B1:B9"));
pieChart->addSeries(CellRange("C1:C9"));

2. 饼状图(3D)

Chart *pie3DChart = doc.insertChart(3, 9, QSize(300, 300));
pie3DChart->setChartType(Chart::CT_Pie3DChart);
pie3DChart->addSeries(CellRange("A1:C9"));

3. 柱状图

Chart *barChart = doc.insertChart(23, 3, QSize(300, 300));
barChart->setChartType(Chart::CT_BarChart);
barChart->addSeries(CellRange("A1:C9"));

4. 柱状图(3D)

Chart *bar3DChart = doc.insertChart(23, 9, QSize(300, 300));
bar3DChart->setChartType(Chart::CT_Bar3DChart);
bar3DChart->addSeries(CellRange("A1:C9"));

5. 折线图

Chart *lineChart = doc.insertChart(43, 3, QSize(300, 300));
lineChart->setChartType(Chart::CT_LineChart);
lineChart->addSeries(CellRange("A1:C9"));

6. 折线图(3D)

Chart *line3DChart = doc.insertChart(43, 9, QSize(300, 300));
line3DChart->setChartType(Chart::CT_Line3DChart);
line3DChart->addSeries(CellRange("A1:C9"));

7. 面积图

Chart *areaChart = doc.insertChart(63, 3, QSize(300, 300));
areaChart->setChartType(Chart::CT_AreaChart);
areaChart->addSeries(CellRange("A1:C9"));

8. 面积图(3D)

Chart *area3DChart = doc.insertChart(63, 9, QSize(300, 300));
area3DChart->setChartType(Chart::CT_Area3DChart);
area3DChart->addSeries(CellRange("A1:C9"));

9. 散点图

Chart *scatterChart = xlsx.insertChart(83, 3, QSize(300, 300));
scatterChart->setChartType(Chart::CT_ScatterChart);
//Will generate three lines.
scatterChart->addSeries(CellRange("A1:A9"));
scatterChart->addSeries(CellRange("B1:B9"));
scatterChart->addSeries(CellRange("C1:C9"));

Chart *scatterChart_2 = xlsx.insertChart(83, 9, QSize(300, 300));
scatterChart_2->setChartType(Chart::CT_ScatterChart);
//Will generate two lines.
scatterChart_2->addSeries(CellRange("A1:C9"));

10. 圆环图

Chart *doughnutChart = xlsx.insertChart(103, 3, QSize(300, 300));
doughnutChart->setChartType(Chart::CT_DoughnutChart);
doughnutChart->addSeries(CellRange("A1:C9"));

11. chartsheet操作

doc.addSheet("Chart1", AbstractSheet::ST_ChartSheet);
Chartsheet *sheet = static_cast<Chartsheet*>(doc.currentSheet());
Chart *barChart = sheet->chart();
barChart->setChartType(Chart::CT_BarChart);
barChart->addSeries(CellRange("A1:A9"), doc.sheet("Sheet1"));

posted on 2021-10-03 08:23 imbaby 阅读(1831) 评论(1) 收藏举报

刷新页面返回顶部

imbaby

Qt-QXLS应用

一、 写Excel

1. 创建Document

2. 添加并选中表单

3. 表格样式设置

4. 写数据

5. 绘制图表

6. 保存

二、读Excel

1. 读取xlsx

2. 遍历表格

3. read与cellAt的区别

4. 读写Image

三、表单操作

1. 添加表单

2. 基本操作

四、行列相关设置

1. 行高和列宽设置

2. 设置行样式

3. 设置列样式

4. 列自适应

五、富文本显示设置

六、单元格合并

七、文档属性设置

八、宏操作

九、数据验证

十、分组

十一、样式获取

1. Google spread sheet

2. office excel

十二、图表处理

1. 饼状图

2. 饼状图(3D)

3. 柱状图

4. 柱状图(3D)

5. 折线图

6. 折线图(3D)

7. 面积图

8. 面积图(3D)

9. 散点图

10. 圆环图

11. chartsheet操作

导航

公告

一、写Excel