void worksheet::title(const std::string &title)
{
// do no work if we don't need to
if (d_->title_ == title)
{
return;
}
// excel limits worksheet titles to 31 characters
if (title.empty() || title.length() > 31)
{
throw invalid_sheet_title(title);
}
// invalid characters in a worksheet name
if (title.find_first_of("*:/\\?[]") != std::string::npos)
{
throw invalid_sheet_title(title);
}
// try and insert the new name into the worksheets map
// if the insert fails, we have a duplicate sheet name
auto insert_result = workbook().d_->sheet_title_rel_id_map_.insert(
std::make_pair(title, workbook().d_->sheet_title_rel_id_map_[d_->title_]));
if (!insert_result.second) // insert failed, duplication detected
{
throw invalid_sheet_title(title);
}
// if the insert succeeded (i.e. wasn't a duplicate sheet name)
// update the worksheet title and remove the old relation
workbook().d_->sheet_title_rel_id_map_.erase(d_->title_);
d_->title_ = title;
workbook().update_sheet_properties();
}
relationship worksheet::referring_relationship() const
{
auto &manifest = workbook().manifest();
auto wb_rel = manifest.relationship(xlnt::path("/"),
relationship_type::office_document);
auto ws_rel = manifest.relationship(wb_rel.target().path(),
workbook().d_->sheet_title_rel_id_map_.at(title()));
return ws_rel;
}
void test1()
{
QAxObject excel( "Excel.Application", 0);
excel.setProperty("Visible", true);
QAxObjectPtr workbooks(excel.querySubObject("Workbooks"));
QAxObjectPtr workbook(workbooks->querySubObject("Add()"));
QAxObjectPtr sheets(workbook->querySubObject("Worksheets"));
sheets->dynamicCall("Add()");
QAxObjectPtr sheet(sheets->querySubObject( "Item( int )", 1 ));
// sheet->setProperty("Name","Nova Planilha");
QAxObjectPtr range(sheet->querySubObject("Cells(int,int)",1,1));
range->setProperty("Value", QVariant(1234));
QImage image("c:/Users/jhlee/Dropbox/orgwiki/img/class01.png");
QClipboard* clip = QApplication::clipboard();
clip->setImage(image);
QAxObjectPtr shapes(sheet->querySubObject("Shapes"));
sheet->dynamicCall("Paste()");
int shapeCount = shapes->property("Count").toInt();
QAxObjectPtr shape(shapes->querySubObject(QString::fromLatin1("Item(%1)").arg(shapeCount).toAscii()));
// shapes->dynamicCall("AddPicture( QString&, bool, bool, double, double, double, double","c:\\Users\\jhlee\\Dropbox\\orgwiki\\img\\class01.png",true,true,100,100,70,70);
excel.setProperty("DisplayAlerts", false);
workbook->dynamicCall("SaveAs(QString&)", "c:\\temp\\testexcel.xlsx");
// //workbook->dynamicCall("Close()");
// //excel.dynamicCall("Quit()");
// workbook->dynamicCall("Close (Boolean)", true);
excel.dynamicCall("Quit()");
}
const range worksheet::named_range(const std::string &name) const
{
if (!workbook().has_named_range(name))
{
throw key_not_found();
}
if (!has_named_range(name))
{
throw key_not_found();
}
return range(d_->named_ranges_[name].targets()[0].second);
}
// big task: convert Excel XFormat into Swinder::Format
const Format* GlobalsSubStreamHandler::convertedFormat(unsigned index) const
{
static const Format blankFormat;
if (index >= xformatCount()) return &blankFormat;
int& formatIt = d->formatCache[index];
if (formatIt) return workbook()->format(formatIt-1);
Format format;
XFRecord xf = xformat(index);
QString valueFormat = this->valueFormat(xf.formatIndex());
if (valueFormat.isEmpty()) {
const unsigned ifmt = xf.formatIndex();
switch (ifmt) {
case 0: valueFormat = "General"; break;
case 1: valueFormat = "0"; break;
case 2: valueFormat = "0.00"; break;
case 3: valueFormat = "#,##0"; break;
case 4: valueFormat = "#,##0.00"; break;
case 5: valueFormat = "\"$\"#,##0_);(\"S\"#,##0)"; break;
case 6: valueFormat = "\"$\"#,##0_);[Red](\"S\"#,##0)"; break;
case 7: valueFormat = "\"$\"#,##0.00_);(\"S\"#,##0.00)"; break;
case 8: valueFormat = "\"$\"#,##0.00_);[Red](\"S\"#,##0.00)"; break;
case 9: valueFormat = "0%"; break;
case 10: valueFormat = "0.00%"; break;
case 11: valueFormat = "0.00E+00"; break;
case 12: valueFormat = "#?/?"; break;
case 13: valueFormat = "#\?\?/\?\?"; break;
case 14: valueFormat = "M/D/YY"; break;
case 15: valueFormat = "D-MMM-YY"; break;
case 16: valueFormat = "D-MMM"; break;
case 17: valueFormat = "MMM-YY"; break;
case 18: valueFormat = "h:mm AM/PM"; break;
case 19: valueFormat = "h:mm:ss AM/PM"; break;
case 20: valueFormat = "h:mm"; break;
case 21: valueFormat = "h:mm:ss"; break;
case 22: valueFormat = "M/D/YY h:mm"; break;
case 37: valueFormat = "_(#,##0_);(#,##0)"; break;
case 38: valueFormat = "_(#,##0_);[Red](#,##0)"; break;
case 39: valueFormat = "_(#,##0.00_);(#,##0)"; break;
case 40: valueFormat = "_(#,##0.00_);[Red](#,##0)"; break;
case 41: valueFormat = "_(\"$\"*#,##0_);_(\"$\"*#,##0_);_(\"$\"*\"-\");(@_)"; break;
case 42: valueFormat = "_(*#,##0_);(*(#,##0);_(*\"-\");_(@_)"; break;
case 43: valueFormat = "_(\"$\"*#,##0.00_);_(\"$\"*#,##0.00_);_(\"$\"*\"-\");(@_)"; break;
case 44: valueFormat = "_(\"$\"*#,##0.00_);_(\"$\"*#,##0.00_);_(\"$\"*\"-\");(@_)"; break;
case 45: valueFormat = "mm:ss"; break;
case 46: valueFormat = "[h]:mm:ss"; break;
case 47: valueFormat = "mm:ss.0"; break;
case 48: valueFormat = "##0.0E+0"; break;
case 49: valueFormat = "@"; break;
default: {
if (ifmt >= 164 && ifmt <= 392) { // custom format
valueFormat = d->formatsTable[ifmt];
} else {
std::cout << "Unhandled format with index " << xf.formatIndex() << ". Using general format." << std::endl;
valueFormat = "General";
}
}
break;
}
}
format.setValueFormat(valueFormat);
format.setFont(d->workbook->font(xf.fontIndex()));
FormatAlignment alignment;
switch (xf.horizontalAlignment()) {
case XFRecord::Left:
alignment.setAlignX(Format::Left); break;
case XFRecord::Right:
alignment.setAlignX(Format::Right); break;
case XFRecord::Centered:
alignment.setAlignX(Format::Center); break;
case XFRecord::Justified:
alignment.setAlignX(Format::Justify); break;
case XFRecord::Distributed:
alignment.setAlignX(Format::Distributed); break;
case XFRecord::Filled:
case XFRecord::CenteredSelection:
default: break;
// FIXME still unsupported: CenteredSelection, Filled
}
switch (xf.verticalAlignment()) {
case XFRecord::Top:
alignment.setAlignY(Format::Top); break;
case XFRecord::VCentered:
alignment.setAlignY(Format::Middle); break;
case XFRecord::Bottom:
alignment.setAlignY(Format::Bottom); break;
case XFRecord::VJustified:
alignment.setAlignY(Format::VJustify); break;
case XFRecord::VDistributed:
alignment.setAlignY(Format::VDistributed); break;
default: break;
// FIXME still unsupported: Justified, Distributed
}
alignment.setWrap(xf.isTextWrap());
unsigned angle = xf.rotationAngle();
if (angle > 90) angle = 360 - (angle - 90);
alignment.setRotationAngle(angle);
alignment.setStackedLetters(xf.stackedLetters());
alignment.setShrinkToFit(xf.isShrinkToFit());
alignment.setIndentLevel(xf.indentationLevel());
format.setAlignment(alignment);
FormatBorders borders;
Pen pen;
pen = convertBorderStyle(xf.leftBorderStyle());
pen.color = d->workbook->color(xf.leftBorderColor());
borders.setLeftBorder(pen);
pen = convertBorderStyle(xf.rightBorderStyle());
pen.color = d->workbook->color(xf.rightBorderColor());
borders.setRightBorder(pen);
pen = convertBorderStyle(xf.topBorderStyle());
pen.color = d->workbook->color(xf.topBorderColor());
borders.setTopBorder(pen);
pen = convertBorderStyle(xf.bottomBorderStyle());
pen.color = d->workbook->color(xf.bottomBorderColor());
borders.setBottomBorder(pen);
if(xf.isDiagonalTopLeftBorder()) {
pen = convertBorderStyle(xf.diagonalBorderStyle());
pen.color = d->workbook->color(xf.diagonalBorderColor());
borders.setTopLeftBorder(pen);
}
if(xf.isDiagonalBottomLeftBorder()) {
pen = convertBorderStyle(xf.diagonalBorderStyle());
pen.color = d->workbook->color(xf.diagonalBorderColor());
borders.setBottomLeftBorder(pen);
}
format.setBorders(borders);
FormatBackground background;
background.setForegroundColor(d->workbook->color(xf.patternForeColor()));
background.setBackgroundColor(d->workbook->color(xf.patternBackColor()));
background.setPattern(convertPatternStyle(xf.fillPattern()));
format.setBackground(background);
formatIt = workbook()->addFormat(format) + 1;
return workbook()->format(formatIt-1);
}
conditional_format worksheet::conditional_format(const range_reference &ref, const condition &when)
{
return workbook().d_->stylesheet_.get().add_conditional_format_rule(d_, ref, when);
}
void worksheet::garbage_collect_formulae()
{
workbook().garbage_collect_formulae();
}
void worksheet::register_calc_chain_in_manifest()
{
workbook().register_workbook_part(relationship_type::calculation_chain);
}
void worksheet::register_comments_in_manifest()
{
workbook().register_worksheet_part(*this, relationship_type::comments);
}
