Visual Basic for Applications (VBA) is a macro language of Visual Basic that can be used to extend the application functionality of Windows, especially Microsoft Office software. It can also be said to be an application-visualization basic Script. In the 1994 release of Excel 5.0, you have the macro functionality of VBA.
In the VBA reference manual can see specific functions, the use of properties, QT operation Excel mainly through Qaxobject + Excel VBA to achieve! About QT's operations on Excel, there are a lot of information on the web, but most are relatively basic, about inserting worksheets (to the last row), deleting worksheets, merging/splitting cells, setting cell background color, setting cell border color, setting cell font (type, size, bold, italic, underline, color, etc.), as well as the use of cell alignment and other uses are not mentioned, and today summarizes the operation of QT on Excel. QT operations Excel, whether the suffix is xls or xlsx can be. As below, I downloaded an Excel VBA reference manual, the content is not too full!
Excel reads
For testing purposes, if an Excel file already exists, the action content has been marked in red. As shown below:
Main Read content:
- Title
- Number of worksheets
- Sheet name
- Start line
- Start column
- Number of rows
- Number of columns
- Cell contents
The code is as follows: qaxobject Excel ("Excel.Application"); excel.setproperty ("Visible", true); qaxobject *work_books = Excel.querysubobject ("WorkBooks"); work_books->dynamiccall ("Open (const qstring&)", QString ("e:/test.xlsx")); Qvariant Title_value = Excel.property ("Caption"); //get title Qdebug () <<qstring ("Excel title:") <<title_value; qaxobject *work_book = Excel.querysubobject ("ActiveWorkbook"); qaxobject *work_sheets = work_book->querysubobject ("sheets"); //sheets can also be swapped with worksheets int Sheet_count = Work_sheets->property ("Count"). ToInt (); //gets the number of worksheets Qdebug () <<qstring ("Sheet count:") <<sheet_count; for (int i=1; i<=sheet_count; i++) { qaxobject *work_sheet = Work_book->querysubobject ("Sheets ( int) ", i); //sheets (int) can also be swapped with worksheets (int) QString work_sheet_name = Work_sheet->property ("name"). ToString (); //Get sheet name QString message = QString ("sheet") +qstring::number (I, ten) + QStRing ("name"); Qdebug () <<message<<work_sheet_name; }if (Sheet_count > 0) {qaxobject *work_sheet = Work_book->querysubobject ("Sheets (int)", 1); qaxobject *used_range = Work_sheet->querysubobject ("UsedRange"); qaxobject *rows = Used_range->querysubobject ("Rows"); qaxobject *columns = used_range->querysubobject ("columns"); int Row_start = Used_range->property ("Row"). ToInt (); //gets the starting line int Column_start = Used_range->property ("column"). ToInt (); //gets the starting column int row_count = Rows->property ("Count"). ToInt (); //gets the number of rows int column_count = Columns->property ("Count"). ToInt (); //gets the number of columns for (int i=row_start; i{ for (int j=column_start; j{ qaxobject *cell = work_sheet-> Querysubobject ("Cells (Int,int)", I, j); qvariant Cell_value = Cell->property ("value"); //Get cell contents QString message = QString ("row-") +qstring::number (I, Ten) +qstring ("-column-") +qstring::number (J, 10) + QString (":"); Qdebug () <<message<<cell_value; } } effects are as follows:
Excel Add, delete, change
Main operation:
- Set Title
- Insert Sheet (to last row)
- Set sheet name
- Delete a worksheet
- Set cell contents
- Set the cell font (type, size, bold, italic, underline, color, and so on)
- Set cell alignment
- Set cell height, width
- Set cell background color, border color
- Merge/Split Cells
- Empty cells
The code is as follows: qaxobject Excel ("Excel.Application"); excel.setproperty ("Visible", true); qaxobject *work_books = Excel.querysubobject ("WorkBooks"); work_books->dynamiccall ("Open (const qstring&)", "e:\\test.xlsx"); Excel.setproperty ("Caption", "Qt Excel "); Qaxobject *work_book = Excel.querysubobject ("ActiveWorkbook"); qaxobject *work_sheets = work_book->querysubobject ("sheets"); //sheets can also be swapped with worksheets //Delete sheet (delete first) Qaxobject *first_sheet = Work_sheets->querysubobject ("Item (int ) ", 1); First_sheet->dynamiccall (" delete "); //Insert Sheet (insert to last line) int sheet_count = Work_sheets->property (" Count "). ToInt (); //gets the number of worksheets qaxobject *last_sheet = Work_sheets->querysubobject ("Item (int)", sheet_count); Qaxobject *work_sheet = Work_sheets->querysubobject ("Add (qvariant)", Last_sheet->asvariant ()); last_sheet- >dynamiccall ("Move (qvariant)", Work_sheet->asvariant ()) work_sheet->setproperty ("Name", "Qt sheet" ); //Set sheet name //action UnitGrid (2nd row 2nd) Qaxobject *cell = Work_sheet->querysubobject ("Cells (Int,int)", 2, 2); Cell->setproperty ("Value", "Java C + + C # PHP Perl Python Delphi Ruby "); //Set cell Value Cell->setproperty ("RowHeight", 50); //Set cell row height Cell->setproperty ("ColumnWidth", 30); //Setup Unit Grekon cell->setproperty ("HorizontalAlignment",-4108); Left-aligned (xlleft): -4131 Center (xlcenter): -4108 Right-aligned (Xlright): -4152cell->setproperty ("VerticalAlignment",- 4108); //on Alignment (xltop)-4160 Center (xlcenter): -4108 Bottom (Xlbottom): -4107cell->setproperty ("WrapText", true); //content, automatic line//cell->dynamiccall ("ClearContents ()"); //empty the contents of the cell qaxobject* interior = cell->querysubobject ("interior"); Interior->setproperty ("Color", Qcolor (0, 255, 0)); //Set cell background color (green) qaxobject* border = Cell->querysubobject ("Borders"); border-> SetProperty ("Color", Qcolor (0, 0, 255)); //Set cell border color (blue) qaxobject *font = cell->querysubobject ("Font"); //Get cell Font font-≫setproperty ("Name", Qstringliteral ("Chinese Choi Wan")); //Set the cell font font->setproperty ("Bold", true); //Set the cell font bold Font->setproperty ("Size", 20); //Set the cell font size font->setproperty ("Italic", true); //Set the cell font italic font->setproperty ("Underline", 2); //sets the cell underline Font->setproperty ("Color", Qcolor (255, 0, 0)); //Set cell font color (red) //set cell contents and merge cells (5th row 3rd Column-8th row 5th column) Qaxobject *cell_5_6 = Work_sheet->querysubobject (" Cells (Int,int) ", 5, 3); Cell_5_6->setproperty (" Value "," Java "); //set the cell value qaxobject *cell_8_5 = Work_sheet->querysubobject ("Cells (int,int)", 8, 5); Cell_8_5->setproperty ( "Value", "C + +"); qstring merge_cell;merge_cell.append (Qchar (3-1 + ' A ')); //Initial column Merge_cell.append (Qstring::number (5)); //Initial Line Merge_cell.append (":"); Merge_cell.append (Qchar (5-1 + ' A ')); //terminating column merge_cell.append (Qstring::number (8)); //terminating line Qaxobject *merge_range = Work_sheet->querysubobject ("Range (const qstring&)", Merge_cell); Merge_ Range->setproPerty ("HorizontalAlignment", -4108); Merge_range->setproperty ("VerticalAlignment", -4108);merge_range-> SetProperty ("WrapText", true); Merge_range->setproperty ("MergeCells", true); //Merge Cell//merge_range->setproperty ("MergeCells", false); //Split Cell //work_book->dynamiccall ("Save ()"); //Save the file (in order to compare the test with the following test2 file, do not save operation here) Work_book->dynamiccall ("SaveAs (const qstring&)", "e:\\ Test2.xlsx "); //Save As another file work_book->dynamiccall ("Close (Boolean)", false); //Close File Excel.dynamiccall ("Quit (void)"); //exit effect as follows: before: operation: to here a lot of people may be wondering, how does a cell macro get it? such as alignment (center alignment), why is the value 4108 instead of the other values? Of course, that's not what I write, I can record a macro, and then track. See: As mentioned above, has basically been able to meet the commonly used operations, such as more professional needs, please refer to Excel VBA ...
- Excel Object Model Reference.
QT Operation Excel
