Excel in the company with a lot of, and this thing I use is not very good, I want to use the program to handle, encountered a lot of errors. These days have been studied under OLE operations Excel.
Environment: VS2008 Sp1+excel 2007
Join OLE Type Library
Arbitrarily build an MFC program, choose Add Class, TypeLib MFC class, come out a dialog, available type library select "Microsoft Excel 12.0 Object Library"
In general, it's enough to add the classes below, but you can also add them all
CApplication
CWorkbook
Cworkbooks
Cworksheet
CRange
CWorksheets
CPicture
Cpictures
Cborder
Cborders
CFont0
Cnterior
When you include the header file, put the CApplication.h to the front
An example of an operation
#define VT (x) _variant_t (x)
void Cexceltest2dlg::onbnclickedbutton1 ()
{
CApplication M_appexcel; Excel application
Cworkbooks M_books;
CWorkbook M_book;
CWorksheets sheets;
Cworksheet sheet;
CRange Range; Select Range
Cnterior Interior;
CFont0 font; Font
Cborders borders; Border
Cborder border;
CRange column;
CRange Row;
Initialize COM
if (:: CoInitialize (NULL) = = E_INVALIDARG)
{
MessageBox ("Initialize COM failed!");
}
Start Excel
if (!m_appexcel.createdispatch (_t ("Excel.Application"), NULL))
{
MessageBox (_t ("Create Excel failed!"));
:: CoUninitialize ();
}
COleVariant covoptional ((Long) disp_e_paramnotfound, VT_ERROR);
M_appexcel.put_visible (TRUE);
M_books. AttachDispatch (M_appexcel.get_workbooks ());
M_book. AttachDispatch (m_books. ADD (covoptional));
Sheets. AttachDispatch (M_book.get_worksheets ()); Get worksheets
Sheet. AttachDispatch (Sheets.get_item (_variant_t ("Sheet1")); Get Sheet1
Sheet.put_name ("1234"); Sheet1 renaming
All cell colors are set to white
Range. AttachDispatch (Sheet.get_cells ());
Interior. AttachDispatch (Range.get_interior ());
Interior.put_color (VT (RGB (255, 255, 255)));
Interior. ReleaseDispatch ();
Range. ClearContents ();
Range. ReleaseDispatch ();
Range. AttachDispatch (Sheet.get_range (VT ("A1"), VT ("C1001"));
Range. ClearFormats ();
Inserting data
Range.put_item (VT (1), VT (1), VT ("function"));
Range.put_item (VT (1), VT (2), VT ("Large Project"));
Range.put_item (VT (1), VT (3), VT ("Small Project"));
for (int i = 2; i <; i++)
{
Range.put_item (VT (i), VT (2), VT (i-1));
Range.put_item (VT (i), VT (3), VT ("37122368~37097735~"));
}
Add a border for four weeks and inside
Borders. AttachDispatch (Range.get_borders ());
for (Long i = xledgeleft; I <= xlinsidehorizontal; i++)
{
border = Borders.get_item (i);
Border.put_linestyle (VT (xlcontinuous));
Border. ReleaseDispatch ();
}
Borders. ReleaseDispatch ();
Adjust column widths
Column = Range.get_entirecolumn ();
Column.put_columnwidth (VT (18.63));
Column. ReleaseDispatch ();
Range. ReleaseDispatch ();
Range. AttachDispatch (Sheet.get_range (VT ("A10"), VT ("A20")); Selected
Range. Merge (VT (0)); Merge cells
Range. ReleaseDispatch ();
Range. AttachDispatch (Sheet.get_range (VT ("A1"), VT ("C1"));
Interior. AttachDispatch (Range.get_interior ());
Interior.put_colorindex (VT (7));
Interior.put_pattern (VT (xlpatternsolid));
Interior. ReleaseDispatch ();
Font. AttachDispatch (Range.get_font ());
Font.put_colorindex (VT (6));
Font.get_bold ();
Font. ReleaseDispatch ();
Range. ReleaseDispatch ();
Range. AttachDispatch (Sheet.get_range (VT ("A2"), VT ("C1001")); To set the range of a Range object
Interior. AttachDispatch (Range.get_interior ()); Select inside the table
Interior.put_colorindex (VT (13)); Color
Interior.put_pattern (VT (xlpatternsolid)); Bold
Interior. ReleaseDispatch ();
Font. AttachDispatch (Range.get_font ()); Select Word
Font.put_colorindex (VT (3)); Set Word color
Font. ReleaseDispatch ();
Row. AttachDispatch (Range.get_entirerow ()); Select all rows in range
Row.put_rowheight (VT (24)); Row height
Row. ReleaseDispatch ();
Range. ReleaseDispatch ();
Sheet. ReleaseDispatch ();
Sheets. ReleaseDispatch ();
M_book. ReleaseDispatch ();
M_books. ReleaseDispatch ();
M_appexcel.releasedispatch ();
}
Next start compiling, wow, a whole bunch of bugs ...
E:\MYPROGRAMS3\EXCELTEST2\EXCELTEST2\DEBUG\EXCEL.TLH (1461): Error C2371: "Fontptr": redefine, different base type, etc.
#import "C:\\Program Files\\Microsoft Office\\office12\\excel in the CApplication.h. EXE "No_namespace
Change to #import "C:\Program Files\Microsoft Office\office12\excel.exe" Exclude ("IFont", "IPicture") Rename ("RGB", " IgnoreThis "), rename (" DialogBox "," IgnoreThis "), rename (" VBE "," Greatwsvbe ")
Rebuild All
E:\MYPROGRAMS3\EXCELTEST2\EXCELTEST2\DEBUG\EXCEL.TLH (2036): Error C2504: "_imsodispobj": base class Not defined
Should be interface not defined, Mso.dll this interface, on the top of CApplication.h added #import "C:\Program files\common Files\Microsoft Shared\office12\mso.dll"
Rebuild all errors are much less
And the VBE and the like, the method of modification:
Join
#import "C:\Program files\common Files\Microsoft Shared\vba\vba6\vbe6ext. OLB "raw_interfaces_only, rename (" Reference "," IgnoreThis "), rename (" VBE "," Greatwsvbe ")
Pay attention to the path of your machine!
There are still some rename warnings that can be rename ("XXX", "XXXXX") after the relevant import.
I am also a beginner OLE operation Excel, I hope to discuss with you.
OLE operations Excel compilation error handling
