Microsoft Excel


Workbook

The Excel Application has a collection of Workbooks. Each Workbook has a collection of Worksheets. Each Worksheet has a collection of cells (a Range object). A range objet can be a single cell, a rectangular block of cells, or la union of many rectangular blocks (a non-contiguous range). The figures below show these Excel components.
La aplicación de Excel tiene una colección de Workbooks. Cada Workbook tiene una colección de Worksheets. Cada Worksheet tiene una colección de celdas (un objeto Range). Un objeto Range puede ser una sola celda, un bloque rectangular de celdas, o la unión de varios bloques rectangulares (un rango no contiguo). Las figuras de abajo muestra cada uno de estos elementos.

Application

range

Problem 1
Create a Dialog application called ExcelWrite using Wintempla to write a number in a cell in Microsoft Excel.
Cree una aplicación de Diálogo llamada ExcelWrite usando Wintempla para escribir un número en una celda de Microsoft Excel.

ExcelWrite.h
#pragma once //______________________________________ ExcelWrite.h
#include "resource.h"
class ExcelWrite: public Win::Dialog
{
public:
     ExcelWrite()
     {
          ::CoInitialize(NULL);
     }
     ~ExcelWrite()
     {
          ::CoUninitialize();
     }
protected:
     ...
};


ExcelWrite.cpp
...
void ExcelWrite::Window_Open(Win::Event& e)
{
     Com::Object Application;
     try
     {
          Application.CreateInstance(L"Excel.Application", true);
          Application.Put(L"Visible", true);
          //Com::Container::DisplayInterfaceFunctions(hWnd, Application);
          //______________________________________________________________ Get Workbooks
          Com::Object Workbooks;
          Application.Get(L"Workbooks", Workbooks);
          //_____________________________________________________________ Add a Workbook
          Com::Object Workbook;
          Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167
          ////____________________________________________________________ Get Sheets
          //Com::Object Sheets;
          //Application.Get(L"Sheets", Sheets);
          ////____________________________________________________________ Get one Sheet
          //Com::Object Sheet;
          //Sheets.Get(L"Item", 1, Sheet); // The first sheet
          //Sheet.Method(L"Select");
          //____________________________________________________________ Get the active Sheet
          Com::Object ActiveSheet;
          Application.Get(L"ActiveSheet", ActiveSheet);
          //____________________________________________________________ Put the name of the Sheet
          ActiveSheet.Put(L"Name", L"Accounting");
          //____________________________________________________________ Get the A1 Cell
          Com::Object Range;
          ActiveSheet.Get(L"Range", L"A1", Range);
          //_____________________________________________________________ Set the value of the A1 Cell
          Range.Put(L"Value2", L"123.456");
          //_____________________________________________________________ Save the file
          //Com::Container::DisplayInterfaceFunctions(hWnd, Book);
          _variant_t result;
          Workbook.Method(L"SaveAs",
               L"C:\\selo\\110\\info.xlsx", // Filename
               (short)51, //Excel.XlFileFormat.xlOpenXMLWorkbook = 51
               L"", //Password
               L"", //WriteResPassword
               false, //ReadOnlyRecommended
               false, //CreateBackup
               true, //AccessMode
               //ConflictResolution
               //AddToMru
               //TextCodepage
               //TextVisualLayout
               //Local
               result);
          Application.Method(L"Quit");
     }
     catch(Com::Exception excep)
     {
          excep.Display(hWnd, L"ExcelWrite");
          Application.Method(L"Quit");
     }
}

infoXlsx

Active Sheet

The Workbooks has a collection of Sheets and only one Sheet is active. The following code illustrates how to activate a specific Sheet.
Los Workbooks tienen tiene una colección de Sheets y solamente una Sheet está activa. El siguiente código ilustra como activar una Sheet específica.Code Program.cpp//Com::Object Sheets = Workbooks->get_Item(L"Report");Com::Object Sheets = Workbooks->get_Item(3);Sheets.Method(L"Select");

Problem 2
Modify the program of Problem 1 to illustrate how to select cells.
Modifique el programa del Problema 1 para ilustrar como seleccionar celdas.

Selection

ExcelWrite.cpp
...
void ExcelWrite::Window_Open(Win::Event& e)
{
     Com::Object Application;
     Com::Object Range;
     try
     {
          Application.CreateInstance(L"Excel.Application", true);
          Application.Put(L"Visible", true);
          //Com::Container::DisplayInterfaceFunctions(hWnd, Application);
          //________________________________________________ Get Workbooks
          Com::Object Workbooks;
          Application.Get(L"Workbooks", Workbooks);
          //________________________________________________ Add a Workbook
          Com::Object Workbook;
          Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167
          //____________________________________________________________ Get the active Sheet
          Com::Object ActiveSheet;
          Application.Get(L"ActiveSheet", ActiveSheet);
          //____________________________________________________________ Set the value of the A1 Cell
          ActiveSheet.Get(L"Range", L"A1", Range);
          Range.Put(L"Value2", L"10");
          //____________________________________________________________ Set the value of the B1 Cell
          ActiveSheet.Get(L"Range", L"B1", Range);
          Range.Put(L"Value2", L"20");
          //____________________________________________________________ Set the value of the C1 Cell
          ActiveSheet.Get(L"Range", L"C1", Range);
          Range.Put(L"Value2", L"30");
          //____________________________________________________________ Select A1 and B1
          ActiveSheet.Get(L"Range", L"A1", L"B1", Range);
          Range.Method(L"Select");
     }
     catch(Com::Exception excep)
     {
          excep.Display(hWnd, L"ExcelWrite");
          Application.Method(L"Quit");
     }
}

Tip
A Range can also be seen as a matrix, in this case, the first parameter and second parameter of Range can be integer values starting at index 1.
Un Range puede también verse como una matriz, en este caso, el primer y segundo parámetro de Range pueden ser valores enteros empezando en el índice 1.

Problem 3
Test the following code.
Pruebe el siguiente código.

CellsItem

ExcelWrite.cpp
...
void ExcelWrite::Window_Open(Win::Event& e)
{
     Com::Object Application;
     Com::Object Range;
     Com::Object Cells;
     Com::Object Item;
     try
     {
          Application.CreateInstance(L"Excel.Application", true);
          Application.Put(L"Visible", true);
          //Com::Container::DisplayInterfaceFunctions(hWnd, Application);
          //________________________________________________ Get Workbooks
          Com::Object Workbooks;
          Application.Get(L"Workbooks", Workbooks);
          //________________________________________________ Add a Workbook
          Com::Object Workbook;
          Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167
          //____________________________________________________________ Get the active Sheet
          Com::Object ActiveSheet;
          Application.Get(L"ActiveSheet", ActiveSheet);
          //____________________________________________________________ Set the value of the A1 Cell
          ActiveSheet.Get(L"Range", L"A1", Range);
          Range.Put(L"Value2", L"10");
          //____________________________________________________________ Set the value of the B1 Cell
          ActiveSheet.Get(L"Range", L"B1", Range);
          Range.Put(L"Value2", L"20");
          //____________________________________________________________ Set the value of the C1 Cell
          ActiveSheet.Get(L"Range", L"C1", Range);
          Range.Put(L"Value2", L"30");
          //____________________________________________________________ Get the Cells.Item(1, 1)
          ActiveSheet.Get(L"Cells", Cells);
          Cells.Get(L"Item", 1, 1, Item);
          Item.Put(L"Value2", L"Hello");
     }
     catch(Com::Exception excep)
     {
          excep.Display(hWnd, L"ExcelWrite");
          Application.Method(L"Quit");
     }
}

Problem 4
Modify the program of Problem 1 to illustrate how to change the color of a cell.
Modifique el programa del Problema 1 para ilustrar como cambiar el color de una celda.

FontColor

ExcelWrite.cpp
...
void ExcelWrite::Window_Open(Win::Event& e)
{
     Com::Object Application;
     Com::Object Range;
     Com::Object Cells;
     Com::Object Item;
     Com::Object Font;
     try
     {
          Application.CreateInstance(L"Excel.Application", true);
          Application.Put(L"Visible", true);
          //Com::Container::DisplayInterfaceFunctions(hWnd, Application);
          //________________________________________________ Get Workbooks
          Com::Object Workbooks;
          Application.Get(L"Workbooks", Workbooks);
          //________________________________________________ Add a Workbook
          Com::Object Workbook;
          Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167
          //____________________________________________________________ Get the active Sheet
          Com::Object ActiveSheet;
          Application.Get(L"ActiveSheet", ActiveSheet);
          //____________________________________________________________ Set the value of the A1 Cell
          ActiveSheet.Get(L"Range", L"A1", Range);
          Range.Put(L"Value2", L"10");
          //_____________________________________________________________ Get the Cells
          ActiveSheet.Get(L"Cells", Cells);
          //_____________________________________________________________ Get the First Cell
          Cells.Get(L"Item", 1, 1, Item);
          //_____________________________________________________________ Get the Item Font
          Item.Get(L"Font", Font);
          //_____________________________________________________________ Set the Font Color
          Font.Put(L"ColorIndex", 3);
     }
     catch(Com::Exception excep)
     {
          excep.Display(hWnd, L"ExcelWrite");
          Application.Method(L"Quit");
     }
}

Problem 5
Modify the program of Problem 1 to illustrate how to get the number of rows and columns in a Worksheet.
Modifique el programa del Problema 1 para ilustrar como obtener el número de renglones y columnas en una Worksheet.

RowsCols

ExcelWrite.cpp
...
void ExcelWrite::Window_Open(Win::Event& e)
{
     Com::Object Application;
     Com::Object Range;
     Com::Object Cells;
     try
     {
          Application.CreateInstance(L"Excel.Application", true);
          Application.Put(L"Visible", true);
          //Com::Container::DisplayInterfaceFunctions(hWnd, Application);
          //________________________________________________ Get Workbooks
          Com::Object Workbooks;
          Application.Get(L"Workbooks", Workbooks);
          //________________________________________________ Add a Workbook
          Com::Object Workbook;
          Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167
          //____________________________________________________________ Get the active Sheet
          Com::Object ActiveSheet;
          Application.Get(L"ActiveSheet", ActiveSheet);
          //____________________________________________________________ Set the value of the cells
          ActiveSheet.Get(L"Range", L"A1", Range);
          Range.Put(L"Value2", L"100");
          //
          ActiveSheet.Get(L"Range", L"B1", Range);
          Range.Put(L"Value2", L"101");
          //
          ActiveSheet.Get(L"Range", L"C1", Range);
          Range.Put(L"Value2", L"102");
          //
          ActiveSheet.Get(L"Range", L"A2", Range);
          Range.Put(L"Value2", L"200");
          //
          ActiveSheet.Get(L"Range", L"B2", Range);
          Range.Put(L"Value2", L"201");
          //
          ActiveSheet.Get(L"Range", L"C2", Range);
          Range.Put(L"Value2", L"202");
          //_____________________________________________________________ Get the Cells
          ActiveSheet.Get(L"Cells", Cells);
          //_____________________________________________________________ Get the SpecialCells
          Cells.Method(L"SpecialCells", (short)11, Range); //Excel.XlCellType.xlCellTypeLastCell = 11
          _variant_t numRows;
          _variant_t numCols;
          Range.Get(L"Row", numRows);
          Range.Get(L"Column", numCols);
          wstring text;
          Sys::Format(text, L"Rows = %d\r\nCols = %d", (short)numRows, (short)numCols);
          this->MessageBox(text, L"ExcelWrite", MB_OK);
     }
     catch(Com::Exception excep)
     {
          excep.Display(hWnd, L"ExcelWrite");
          Application.Method(L"Quit");
     }
}


Tip
The following code illustrates how to delete cells.
El código siguiente ilustra como borrar celdas.

Program.cpp
Excel::Range range;
_variant_t delete = -4162;
_variant_t first_cell = L"A2";
_variant_t last_cell = L"E2";

range = worksheet->get_Range(first_cell, last_cell);
range.Select();
range.Delete(delete);


Tip
In some cases, the program performance can be improved by preventing Microsoft Excel from updating the screen while the program is running; in this case you may set the Application.Visible to false or Application.ScreenUpdating to false.
En algunos casos, la velocidad del programa se puede mejorar previniendo que Microsoft Excel actualice la pantalla mientras el programa se ejecuta; en este caso usted puede fijar Application.Visible en false o Application.ScreenUpdating en false.

Tip
The Range object is very complex and has many options, you may search over the Internet to learn more about it.
El objeto Range es muy complejo y tiene muchas opciones, usted puede buscar en la Internet para aprender más acerca de él.

Tip
Remember that variable of the data type _variant_t can store any data type, this includes empty. To know if a _variant_t variable is empty, you must use the .vt element of the variable. The code shown below checks if the variable x is empty.
Recuerde que una variable del tipo de datos _variant_t puede almacenar cualquier tipo de datos, esto incluye a un valor vacío. Para saber si un valor _variant_t está vacío, usted puede usar el elemento .vt. El código de abajo checa si la variable x está vacía.

Program.cpp
_variant_t x;
...
if (x.vt == VT_EMPTY)
{
     // x is empty
}


Tip
The following code shows how to convert some text to _variant_t.
El siguiente código muestra como convertir un texto a _variant_t.

Program.cpp
//___________________________________________________ Using wchar_t
wchar_t text[32];
_snwprintf_s(text, 32, _TRUNCATE, L"Number = %d", 5);
_variant_t vtext = text;
//___________________________________________________ Using wstring
wstring wstr;
Sys::Format(wstr, L"Number = %d", 5);
_variant_t vstr = wstr.c_str();



Problem 6
Modify the program of Problem 1 to create the file shown. You may use Item.Formula to set the formula of each cell.
Modifique el programa del Problema 1 para crear el archivo mostrado. Usted puede usar Item.Formula para fijar la fórmula de cada celda.

SumaXlsx

Tip
The file shown below illustrates how to use the import command to import Microsoft Excel.
El archivo mostrado debajo ilustra cómo usar el comando import para importar Microsoft Excel.

Problem 7
Create a Dialog application using Wintempla called ImportExcel. Edit the stdafx.h file to generate the files: excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli. Once the files have been generated, remove the #import command from the stdafx.h, move the respective files to your project and add them to the project using the menu Project > Add Existing Item... .
Cree una aplicación de Diálogo usando Wintempla llamada ImportExcel. Edite el archivo stdafx.h para generar los archivos: excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli. Una vez que los archivos han sido generados, remueva el comando #import del archivo stdafx.h, mueva los archivos respectivo a su proyecto y agréguelos a su proyecto usando el menú Project > Add Existing Item... .

stdafx.h
...
// To generate excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli
#import "C:\\Program Files (x86)\\Microsoft Office\\Office12\\excel.exe" auto_search auto_rename dual_interfaces \
     rename("IFont", "IFontx") \
     rename("IPicture", "IPicturex") \


ImportExcel

ImportExcel.h
#pragma once //______________________________________ ImportExcel.h
#include "Resource.h"
#include "excel.tlh"
#include "mso.tlh"
#include "vbe6ext.tlh"
class ImportExcel: public Win::Dialog
{
public:
     ImportExcel()
     {
          ::CoInitialize(NULL);
     }
     ~ImportExcel()
     {
          ::CoUninitialize();
     }
     ...
};

ImportExcel.cpp
...
void ImportExcel::Window_Open(Win::Event& e)
{
     Excel::_ApplicationPtr Application;
     HRESULT hr;
     Com::Exception ex;

     try
     {
          //______________________________________________________________ Create Excel Application
          hr = Application.CreateInstance(L"Excel.Application");
          ex.ok(L"Application.CreateInstance", hr);
          //______________________________________________________________ Application->Visible
          Application->Visible[0] = VARIANT_TRUE;
          //______________________________________________________________ Workbooks->Add
          Excel::WorkbooksPtr Workbooks = Application->Workbooks;
          Excel::_WorkbookPtr Workbook = Workbooks->Add((long)Excel::xlWorksheet);
          ex.ok(L"Workbooks->Add", hr);
          //____________________________________________________________ Get the Active Sheet
          Excel::_WorksheetPtr WorkSheet = Application->ActiveSheet;
          WorkSheet->Name = "No. students";
          // _______________________________________________________________
          WorkSheet->Range["A1"][vtMissing]->Value2 = "Electronics";
          WorkSheet->Range["B1"][vtMissing]->Value2 = "Algebra";
          WorkSheet->Range["C1"][vtMissing]->Value2 = "SQL Databases";
          WorkSheet->Range["D1"][vtMissing]->Value2 = "Basic C#";
          // ______________________________________________________
          WorkSheet->GetRange("A2")->Value2 = 20;
          WorkSheet->GetRange("B2")->Value2 = 50;
          WorkSheet->GetRange("C2")->Value2 = 10;
          WorkSheet->GetRange("D2")->Value2 = 30;
          //______________________________________________________
          WorkSheet->GetRange("A2")->Font->ColorIndex = 3;
          WorkSheet->GetRange("B2")->Font->ColorIndex = 4;
          WorkSheet->GetRange("C2")->Font->ColorIndex = 5;
          WorkSheet->GetRange("D2")->Font->ColorIndex = 6;
          //______________________________________________________
          WorkSheet->GetRange("A1")->HorizontalAlignment = (long)Excel::xlHAlignCenter;
          WorkSheet->GetRange("B1")->HorizontalAlignment = (long)Excel::xlHAlignCenter;
          WorkSheet->GetRange("C1")->HorizontalAlignment = (long)Excel::xlHAlignCenter;
          WorkSheet->GetRange("D1")->HorizontalAlignment = (long)Excel::xlHAlignCenter;
          //
          Excel::RangePtr Cells = WorkSheet->GetCells();
          Excel::RangePtr LastCell = Cells->SpecialCells(Excel::XlCellType::xlCellTypeLastCell);
          long row = LastCell->Row;
          long col = LastCell->Column;
          wchar_t text[256];
          _snwprintf_s(text, 256, _TRUNCATE, L"Rows = %d\r\nCols = %d", row, col);
          this->MessageBox(text, L"ImportExcel", MB_OK | MB_ICONINFORMATION);
          //
          Excel::_ChartPtr chart = Workbook->Charts->Add();
          hr = chart->ChartWizard((Excel::Range*)Cells, (long)Excel::xl3DPie, 7L, (long)Excel::xlRows, 1L, 0L, 2L, "No. Students");
          ex.ok(L"chart->ChartWizard", hr);
          ////WorkSheet->SaveAs(_bstr_t(filename), vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing);
          //Application->Quit();
     }
     catch(Com::Exception& excep)
     {
          excep.Display(hWnd, L"ImportExcel");
     }
     catch(_com_error excep)
     {
          Com::Exception::Display(hWnd, excep, L"ImportExcel");
     }
}

Pie3D

© Copyright 2000-2019 Wintempla selo. All Rights Reserved. Sep 05 2019. Home