Excel with VC

Nowadays, Excel is becoming more and more important, so we have to deal with Excel in our own programs. Using Automation technology, we can play Excel without understanding the database, and you will find everything so easy!

Well, let's get started. I don't like to use lengthy code to get confused. So the following code is a piece of important information, which is a coherent process, including starting Excel, reading data, writing data, and closing Excel at last include the processing of merged cells that many people are interested in.

Note that the following code requires the support of MFC, and the project also contains the definition files of EXCEL2000: EXCEL9.H, EXCEL9.CPP

**************************************** **************************************

//*****

// Variable definition

_ Application app;

Workbooks books;

_ Workbook;

Worksheets sheets;

_ Worksheet sheet;

Range;

Range iCell;

LPDISPATCH lpDisp;

COleVariant vResult;

COleVariant

CovTrue (short) TRUE ),

CovFalse (short) FALSE ),

CovOptional (long) DISP_E_PARAMNOTFOUND, VT_ERROR );

//*****

// Initialize the dynamic Connection Library of COM

If (! AfxOleInit ())

{

AfxMessageBox ("the dynamic Connection Library of COM cannot be initialized !");

Return;

}

//*****

// Create an Excel 2000 Server (start Excel)

If (! App. CreateDispatch ("Excel. Application "))

{

AfxMessageBox ("the Excel server cannot be started !");

Return;

}

App. SetVisible (TRUE); // make Excel visible

App. SetUserControl (TRUE); // allow other users to control Excel

//*****

// Open c: \ 1.xls

Books. AttachDispatch (app. GetWorkbooks ());

LpDisp = books. Open ("C: \ 1.xls ",

CovOptional,

CovOptional,

Covoptional, covoptional );

//*****

// Obtain the workbook

Book. attachdispatch (lpdisp );

//*****

// Obtain the worksheets

Sheets. attachdispatch (book. getworksheets ());

//*****

// Obtain the currently active Sheet

// If a cell is being edited, this operation cannot be returned and will remain waiting.

Lpdisp = book. getactivesheet ();

Sheet. attachdispatch (lpdisp );

//*****

// Read the information of the region in use, including the number of rows in use, number of columns, start row, and start column.

Range usedrange;

Usedrange. attachdispatch (sheet. getusedrange ());

Range. attachdispatch (usedrange. getrows ());

Long irownum = range. getcount (); // number of rows in use

Range. attachdispatch (usedrange. getcolumns ());

Long icolnum = range. getcount (); // Number of columns in use

Long istartrow = usedrange. getrow (); // start row of the used region, starting from 1

Long istartcol = usedrange. getcolumn (); // start column of the used region, starting from 1

//*****

// Read the value of the first cell

Range. attachdispatch (sheet. getcells ());

Range. attachdispatch (range. getitem (colevariant (long) 1), colevariant (long) 1). pdispval );

Colevariant vresult = range. getvalue ();

Cstring STR;

If (vresult. Vt = vt_bstr) // string

{

STR = vresult. bstrval;

}

Else if (vresult. Vt = vt_r8) // 8-byte number

{

Str. Format ("% F", vresult. dblval );

}

Else if (vresult. Vt = vt_date) // time format

{

Systemtime st;

Varianttimetosystemtime (& vresult. Date, & St );

}

Else if (vresult. Vt = vt_empty) // empty cell

{

STR = "";

}

//*****

// Read the alignment of the first cell. Data Type: vt_i4

// Read Horizontal Alignment

Range. attachdispatch (sheet. getcells ());

Icell. attachdispatch (range. getitem (colevariant (long (1), colevariant (long (1). pdispval );

Vresult. lval = 0;

Vresult = icell. gethorizontalalignment ();

If (vresult. lval! = 0)

{

Switch (vresult. lval)

{

Case 1: // default

Break;

Case-4108: // center

Break;

Case-4131: // * left

Break;

Case-4152: // * Right

Break;

}

}

// Vertical Alignment

ICell. AttachDispatch (range. GetItem (COleVariant (long (1), COleVariant (long (1). pdispVal );

VResult. lVal = 0;

VResult = iCell. GetVerticalAlignment ();

If (vResult. lVal! = 0)

{

Switch (vResult. lVal)

{

Case-4160: // *

Break;

Case-4108: // center

Break;

Case-4107: // *

Break;

}

}

//*****

// Set the value of the first cell. "HI, EXCEL !"

Range. SetItem (COleVariant (1), COleVariant (1), COleVariant ("HI, EXCEL !"));

//*****

// Set the font color of the first cell: red

Font font;

Range. AttachDispatch (sheet. GetCells ());

Range. AttachDispatch (range. GetItem (COleVariant (long (1), COleVariant (long (1). pdispVal );

Font. SetColor (COleVariant (long) 0xFF0000 ));

//*****

// Merge Cells

// Including checking whether the first cell is a merged cell and merging the first cell

Range unionRange;

Range. AttachDispatch (sheet. GetCells ());

UnionRange. AttachDispatch (range. GetItem (COleVariant (long) 1), COleVariant (long) 1). pdispVal );

VResult = unionRange. GetMergeCells ();

If (vResult. boolVal =-1) // merged Cells

{

// Number of rows in the merged Cells

Range. AttachDispatch (unionRange. GetRows ());

Long iUnionRowNum = range. GetCount ();

// Merge the number of columns in a cell

Range. AttachDispatch (unionRange. GetColumns ());

Long iUnionColumnNum = range. GetCount ();

// Start row and column of the merged Area

Long iUnionStartRow = unionRange. GetRow (); // start row, starting from 1

Long iUnionStartCol = unionRange. GetColumn (); // start column, starting from 1

}

Else if (vResult. boolVal = 0)

{// Not the merged cell}

// Merge the first cell into two rows and three columns

Range. AttachDispatch (sheet. GetCells ());

UnionRange. AttachDispatch (range. GetItem (COleVariant (long) 1), COleVariant (long) 1). pdispVal );

UnionRange. AttachDispatch (unionRange. GetResize (COleVariant (long) 2), COleVariant (long) 3 )));

UnionRange. Merge (COleVariant (long) 0); // Merge Cells

//*****

// Save the file as 2.xls

Book. SaveAs (COleVariant ("C: \ 2.xls"), covOptional, covOptional ,\

CovOptional, 0 ,\

CovOptional, covOptional );

//*****

// Close all books and exit Excel

Book. Close (covOptional, COleVariant (OutFilename), covOptional );

Books. Close ();

App. Quit ();