QtXlsxWriter的基本操作

最新推荐文章于 2024-05-16 08:08:16 发布

lt0312

最新推荐文章于 2024-05-16 08:08:16 发布

阅读量735

点赞数 1

分类专栏： C++ 文章标签： qt

本文链接：https://blog.csdn.net/lt0312/article/details/129052822

版权

C++ 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

创建、打开、复制、保存Xlsx文件

QXlsx::Document xlsx1; // 创建的文件
QXlsx::Document xlsx2("book1.xlsx");//打开一个book1的文件
xlsx3.saveAs("Book2.xlsx");	// 保存文件
//复制
QXlsx::Document xlsx3("book1.xlsx");
xlsx3.saveAs("Book2.xlsx");

单元格样式

QXlsx::Format format;/*设置该单元的样式*/
/*单元格边框样式*/
format.setBorderColor(QColor("#000000"));//边框颜色
format.setBorderStyle(QXlsx::Format::BorderMedium);//中等线条边框
format.setBorderStyle(QXlsx::Format::BorderDashed);//虚线边框
format.setBorderStyle(QXlsx::Format::BorderDotted);//打点边框
format.setBorderStyle(QXlsx::Format::BorderThick);//粗边框
format.setBorderStyle(QXlsx::Format::BorderThin);//细边框
format.setBorderStyle(QXlsx::Format::BorderDouble);//双重边框
format.setBorderStyle(QXlsx::Format::BorderDashDot);//点线间隔边框

/*单元格设置字体样式*/
format.setFontColor(QColor("#FFFFFF"));//设置字体颜色
format.setFontSize(11);//设置字体大小
format.setFontBold(false);//设置 false不加粗 true加粗
format.setFontName(QStringLiteral("Calibri"));//设置字体Calibri、Arial、Times New Roman...

/*单元格背景颜色*/
format.setPatternBackgroundColor(QColor("#00B050"));
format.setPatternForegroundColor(Qt::red);

/*单元格居中方式*/
format.setVerticalAlignment(QXlsx::Format::AlignVCenter);//AlignLeft、AlignRight、AlignTop、AlignBottom
format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

/*单元格行高和列宽*/
xlsx.setColumnWidth("C3：H3", 5);//C-H设置宽度
xlsx.setRowHeight(3, 7, 5);//3-7行设置高度

单元格基本操作

// 写入
xlsx.write(1, 1, QString("测试"), format1);
xlsx.write("A1", QString("测试"), format1);

// 合并
xlsx.mergeCells(QXlsx::CellRange(3,1,3,3), tableTitleFormat);
xlsx.mergeCells("A1:A11", tableTitleFormat);

// 取单元格值
xlsx.cellAt(3,2)->value();
xlsx.cellAt("B2")->value();
xlsx.read(3,2);
xlsx.read("B2");//带公式的单元格，输入为公式的字符串

工作表

// 选择当前工作表
xlsx.selectSheet("Sheel1")；

// 添加工作表
xlsx.addsheel("Sheel2");

// 工作表重命名
xlsx.workbook()->renameSheet(0,"Sheel3");

//获取表格行列数
int row = xlsx->dimension().rowCount();
int col = xlsx->dimension().columnCount();

画图

// 饼形图
QXlsx::Chart *pieChart = xlsx.insertChart(3, 3, QSize(300, 300));//在3行3列的位置插入一个图标
pieChart->setChartType(Chart::CT_Pie);//插入一个饼形图
pieChart->addSeries(CellRange("A1:A9"));//饼形图添加数据
pieChart->addSeries(CellRange("B1:B9"));//饼形图添加数据
pieChart->addSeries(CellRange("C1:C9"));//饼形图添加数据

// 3D饼形图
Chart *pie3DChart = xlsx.insertChart(3, 9, QSize(300, 300));
pie3DChart->setChartType(Chart::CT_Pie3D);
pie3DChart->addSeries(CellRange("A1:C9"));

// 条形图
Chart *barChart = xlsx.insertChart(23, 3, QSize(300, 300));
barChart->setChartType(Chart::CT_Bar);
barChart->addSeries(CellRange("A1:C9"));
barChart->addSeries(CellRange("A1:A9"), xlsx.sheet("Sheet1")); // 添加另一个工作表数据

// 3D条形图
Chart *bar3DChart = xlsx.insertChart(23, 9, QSize(300, 300));
bar3DChart->setChartType(Chart::CT_Bar3D);
bar3DChart->addSeries(CellRange("A1:C9"));

// 折线图
Chart *lineChart = xlsx.insertChart(43, 3, QSize(300, 300));
lineChart->setChartType(Chart::CT_Line);
lineChart->addSeries(CellRange("A1:C9"));

//3D折线图
Chart *line3DChart = xlsx.insertChart(43, 9, QSize(300, 300));
line3DChart->setChartType(Chart::CT_Line3D);
line3DChart->addSeries(CellRange("A1:C9"));

// 面积图
Chart *areaChart = xlsx.insertChart(63, 3, QSize(300, 300));
areaChart->setChartType(Chart::CT_Area);
areaChart->addSeries(CellRange("A1:C9"));

//3D面积图
Chart *area3DChart = xlsx.insertChart(63, 9, QSize(300, 300));
area3DChart->setChartType(Chart::CT_Area3D);
area3DChart->addSeries(CellRange("A1:C9"));

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

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

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

conditionalformatting 条件格式化

ConditionalFormatting cf1;	// 条件对象
Format fmt1;	//格式
fmt1.setFontColor(Qt::green);
fmt1.setBorderStyle(Format::BorderDashed);
cf1.addHighlightCellsRule(ConditionalFormatting::Highlight_LessThan, "40", fmt1);	// 设置条件：<阈值40，低于40，使用特定格式
cf1.addRange("B3:B21"); //单元格作用范围
xlsx.addConditionalFormatting(cf1);

cf2.addHighlightCellsRule(ConditionalFormatting::Highlight_Between, "30", "70", fmt2); //满足条件：30 < x < 70
cf3.addHighlightCellsRule(ConditionalFormatting::Highlight_BeginsWith, "2", fmt3); // 满足条件：含有符号“2”
cf4.addDataBarRule(Qt::blue); //添加蓝色数据条形尺
cf5.add2ColorScaleRule(Qt::blue, Qt::red);//添加蓝红色数据标准尺

datavalidation 数据有效性

DataValidation validation(DataValidation::Whole, DataValidation::Between, "33", "99");//设置有效条件对象，处于33和99之间的数字
validation.addRange("A2");//作用单元格
validation.addRange("A3:E5");//作用单元格
validation.setPromptMessage("Please Input Integer between 33 and 99");//设置提醒信息
xlsx.addDataValidation(validation);

在这里插入图片描述

definenam 定义名字

xlsx.defineName("MyCol_1", "=Sheet1!$A$1:$A$10");//定义工作表1中A1:A10为MyCol_1
xlsx.defineName("MyCol_2", "=Sheet1!$B$1:$B$10", "This is comments");
xlsx.defineName("MyCol_3", "=Sheet1!$C$1:$C$10", "", "Sheet1");
xlsx.defineName("Factor", "=0.5");
xlsx.write(11, 3, "=SUM(MyCol_3)");
xlsx.write(12, 1, "=SUM(MyCol_1)*Factor");

demo 示例

https://blog.csdn.net/u014779536/article/details/111769792

documentproperty 文档属性

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

extractdata 提取数据

qDebug() << xlsx.read("A1");
for (int row = 1; row < 10; ++row) {
   if (QXlsx::Cell *cell = xlsx.cellAt(row, 1))
       qDebug() << cell->value();//如果有值，则输出

formulas 公式

xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);

sheet->writeFormula("D2", CellFormula("B2:B19+C2:C19", "D2:D19", CellFormula::ArrayType));
sheet->writeFormula("E2", CellFormula("=CONCATENATE(\"The total is \",D2:D19,\" units\")", "E2:E19", CellFormula::ArrayType));

image 图像

QXlsx::Document xlsx;
QImage image(40, 30, QImage::Format_RGB32);
image.fill(Qt::green);
for (int i = 0; i < 10; ++i)
xlsx.insertImage(10 * i, 5, image);
xlsx.saveAs("Book1.xlsx");

numberformat 数值格式

// Custom number formats
QStringList numFormats;
numFormats << "Qt #"
    << "yyyy-mmm-dd"
    << "$ #,##0.00"
    << "[red]0.00";
xlsx.write(1, 1, "Raw data", header);
xlsx.write(1, 2, "Format", header);
xlsx.write(1, 3, "Shown value", header);
for (int i = 0; i < numFormats.size(); ++i) {
int row = i + 2;
xlsx.write(row, 1, 100.0);
xlsx.write(row, 2, numFormats[i]);
QXlsx::Format format;
format.setNumberFormat(numFormats[i]);
xlsx.write(row, 3, 100.0, format);
}

// Builtin number formats
xlsx.addSheet();
xlsx.setColumnWidth(1, 4, 20.0);
xlsx.write(1, 1, "Raw data", header);
xlsx.write(1, 2, "Builtin Format", header);
xlsx.write(1, 3, "Shown value", header);
for (int i = 0; i < 50; ++i) {
int row = i + 2;
int numFmt = i;
xlsx.write(row, 1, 100.0);
xlsx.write(row, 2, numFmt);
QXlsx::Format format;
format.setNumberFormatIndex(numFmt);
xlsx.write(row, 3, 100.0, format);
}

在这里插入图片描述
richtext 富文本

QXlsx::RichString rich;
rich.addFragment("Hello ", blue);
rich.addFragment("Qt ", red);
rich.addFragment("Xlsx", bold);
xlsx.write("B2", rich);

xlsx.workbook()->setHtmlToRichStringEnabled(true);
xlsx.write("B4", "<b>Hello</b> <font color=\"red\">Qt</font> <i>Xlsx</i>");

xlsx.write("B6", "<font color=\"red\"><b><u><i>Qt Xlsx</i></u></b></font>");

在这里插入图片描述
rowcolumn 行列

// Set style for the row 11th.
QXlsx::Format format1;
format1.setFontBold(true);
format1.setFontColor(QColor(Qt::blue));
format1.setFontSize(20);
xlsx.write(11, 1, "Hello Row Style");
xlsx.write(11, 6, "Blue Color");
xlsx.setRowFormat(11, format1);
xlsx.setRowHeight(11, 41);

// Set style for the col [9th, 16th)
QXlsx::Format format2;
format2.setFontBold(true);
format2.setFontColor(QColor(Qt::magenta));
for (int row = 12; row <= 30; row++)
    for (int col = 9; col <= 15; col++)
        xlsx.write(row, col, row + col);
xlsx.setColumnWidth(9, 16, 5.0);
xlsx.setColumnFormat(9, 16, format2);

在这里插入图片描述
worksheetoperations 工作表的操作

Document xlsx;

for (int i = 1; i < 20; ++i) {
    for (int j = 1; j < 15; ++j)
        xlsx.write(i, j, QString("R %1 C %2").arg(i).arg(j));
}
xlsx.addSheet();
xlsx.write(2, 2, "Hello Qt Xlsx");
xlsx.addSheet();
xlsx.write(3, 3, "This will be deleted...");

xlsx.addSheet("HiddenSheet");
xlsx.currentSheet()->setHidden(true);
xlsx.write("A1", "This sheet is hidden.");

xlsx.addSheet("VeryHiddenSheet");
xlsx.sheet("VeryHiddenSheet")->setSheetState(AbstractSheet::SS_VeryHidden);
xlsx.write("A1", "This sheet is very hidden.");

xlsx.save(); // Default name is "Book1.xlsx"
//![Create a xlsx file]

Document xlsx2("Book1.xlsx");
//![add_copy_move_delete]
xlsx2.renameSheet("Sheet1", "TheFirstSheet");

xlsx2.copySheet("TheFirstSheet", "CopyOfTheFirst");

xlsx2.selectSheet("CopyOfTheFirst");
xlsx2.write(25, 2, "On the Copy Sheet");

xlsx2.deleteSheet("Sheet3");

xlsx2.moveSheet("Sheet2", 0);
//![add_copy_move_delete]

//![show_hidden_sheets]
xlsx2.sheet("HiddenSheet")->setVisible(true);
xlsx2.sheet("VeryHiddenSheet")->setVisible(true);
//![show_hidden_sheets]

xlsx2.saveAs("Book2.xlsx");