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));
}
