QTXLSX_USE_NAMESPACE int main() { Document xlsx; for (int i=1; i<=10; ++i) { xlsx.write(i, 1, i); xlsx.write(i, 2, i*i); xlsx.write(i, 3, i*i*i); } Worksheet* sheet = xlsx.currentWorksheet(); sheet->writeFormula(CellReference(11, 1), CellFormula(Formula::AVERAGE(CellRange(1, 1, 10, 1)), CellReference::toString(11, 1), CellFormula::SharedType)); sheet->writeFormula(CellReference(11, 2), CellFormula(Formula::SUM(CellRange(1, 2, 10, 2)), CellReference::toString(11, 2), CellFormula::SharedType)); sheet->writeFormula(CellReference(11, 3), CellFormula(Formula::COUNTIF(CellRange(1, 3, 10, 3), QString("\">50\"")), CellReference::toString(11, 3), CellFormula::SharedType)); xlsx.save(); return 0; }
CellRange locationRangeToCellRange(const LocationRange& locationRange) { return CellRange( locationToCellCoord(locationRange.topLeft), locationToCellCoord(locationRange.bottomRight) ); }
/*! \overload Add the cell(\a row, \a col) on which the conditional formatting will apply to. */ void ConditionalFormatting::addCell(int row, int col) { d->ranges.append(CellRange(row, col, row, col)); }
/*! Add the \a cell on which the conditional formatting will apply to. */ void ConditionalFormatting::addCell(const QString &cell) { d->ranges.append(CellRange(cell)); }
/*! \overload Add the range(\a firstRow, \a firstCol, \a lastRow, \a lastCol) on which the DataValidation will apply to. */ void DataValidation::addRange(int firstRow, int firstCol, int lastRow, int lastCol) { d->ranges.append(CellRange(firstRow, firstCol, lastRow, lastCol)); }
/*! Add the \a range on which the DataValidation will apply to. */ void DataValidation::addRange(const QString &range) { d->ranges.append(CellRange(range)); }
/*! \overload Add the cell(\a row, \a col) on which the DataValidation will apply to. */ void DataValidation::addCell(int row, int col) { d->ranges.append(CellRange(row, col, row, col)); }
Formula CellRange::toFormula(const CellReference &topLeft, const CellReference &bottomRight) { return CellRange(topLeft, bottomRight).toFormula(); }
//-------------------------------------------------------------------------------------------------- /// //-------------------------------------------------------------------------------------------------- void CellRangeFilter::addCellExcludeRange(size_t minI, size_t minJ, size_t minK, size_t maxI, size_t maxJ, size_t maxK) { m_excludeRanges.push_back(CellRange(minI, minJ, minK, maxI, maxJ, maxK)); }
void ExcelControl::run(){ if(m_tableView == NULL || m_tableModel == NULL){ return; } int rowCount = m_tableModel->rowCount(); int colCount = m_tableModel->columnCount(); qDebug() << rowCount << colCount; Document xlsx; //单元格格式 Format cellFormat; //颜色 QColor originColor = cellFormat.patternBackgroundColor(); //水平居中 cellFormat.setHorizontalAlignment(Format::AlignHCenter); //垂直居中 cellFormat.setVerticalAlignment(Format::AlignVCenter); //边框 cellFormat.setBorderStyle(Format::BorderThin); //设置字体 cellFormat.setFont(QFont("微软雅黑")); cellFormat.setFontSize(9); //添加标题 cellFormat.setPatternBackgroundColor(QColor(0, 176, 200)); for(int i = 0;i < colCount;i++){ //获取标题单元格值 QString headerValue = m_tableModel->headerData(i, Qt::Horizontal).toString(); //字体加粗 cellFormat.setFontBold(true); //给单元格设值并合并单元格 xlsx.write(1, i + 1, headerValue, cellFormat); } //字体不加粗 cellFormat.setFontBold(false); //循环添加单元格内容 for(int i = 0;i < rowCount;i++){ for(int j = 0;j < colCount;j++){ QStandardItem *cellItem = m_tableModel->item(i, j); if(cellItem != NULL){ //获取单元格值 QString cellValue = cellItem->text(); //获取单元格背景色 QColor cellBgColor = cellItem->background().color(); //给单元格设值并合并单元格 int rowSpan = m_tableView->rowSpan(i, j); int colSpan = m_tableView->columnSpan(i, j); cellFormat.setPatternBackgroundColor(cellBgColor); xlsx.write(i + 2, j + 1, cellValue, cellFormat); xlsx.mergeCells(CellRange(i + 2, j + 1, i + rowSpan + 1, j + colSpan), cellFormat); }else{ //设置空值 int rowSpan = m_tableView->rowSpan(i, j); int colSpan = m_tableView->columnSpan(i, j); if(rowSpan == 1 && colSpan == 1){ cellFormat.setPatternBackgroundColor(originColor); xlsx.write(i + 2, j + 1, "", cellFormat); } } emit setProgressValue(i * colCount + j + 1, rowCount * colCount); } } //保存excel if(m_path.isEmpty()){ xlsx.save(); }else{ xlsx.saveAs(m_path); } //初始化 m_tableView = NULL; m_tableModel = NULL; //发送处理完成的信号 emit execute(true); qDebug() << "excel done"; }
//-------------------------------------------------------------------------------------------------- /// //-------------------------------------------------------------------------------------------------- void CellRangeFilter::addCellInclude(size_t i, size_t j, size_t k, bool applyToSubGridAreas) { m_includeRanges.push_back(CellRange(i, j, k, i, j, k, applyToSubGridAreas)); }
//-------------------------------------------------------------------------------------------------- /// //-------------------------------------------------------------------------------------------------- void CellRangeFilter::addCellExcludeRange(size_t minI, size_t minJ, size_t minK, size_t maxI, size_t maxJ, size_t maxK, bool applyToSubGridAreas) { m_excludeRanges.push_back(CellRange(minI, minJ, minK, maxI, maxJ, maxK, applyToSubGridAreas)); }
QString CellRange::toString(const CellReference &topLeft, const CellReference &bottomRight) { return CellRange(topLeft, bottomRight).toString(); }
QString CellRange::toString(int firstRow, int firstColumn, int lastRow, int lastColumn, const QString &sheet) { return CellRange(firstRow, firstColumn, lastRow, lastColumn, sheet).toString(); }
/*! Add the \a range on which the conditional formatting will apply to. */ void ConditionalFormatting::addRange(const QString &range) { d->ranges.append(CellRange(range)); }
/*! \overload Add the range(\a firstRow, \a firstCol, \a lastRow, \a lastCol) on which the conditional formatting will apply to. */ void ConditionalFormatting::addRange(int firstRow, int firstCol, int lastRow, int lastCol) { d->ranges.append(CellRange(firstRow, firstCol, lastRow, lastCol)); }
/*! Add the \a cell on which the DataValidation will apply to. */ void DataValidation::addCell(const QString &cell) { d->ranges.append(CellRange(cell)); }
//-------------------------------------------------------------------------------------------------- /// //-------------------------------------------------------------------------------------------------- void CellRangeFilter::addCellInclude(size_t i, size_t j, size_t k) { m_includeRanges.push_back(CellRange(i, j, k, i, j, k)); }
/*! Add the \a cell on which the conditional formatting will apply to. */ void ConditionalFormatting::addCell(const CellReference &cell) { d->ranges.append(CellRange(cell, cell)); }