Programming implementation for Excel table file operations

Programming implementation for Excel table file operations

Compilation: Xu Jingzhou

Download sample source code (http://www.vchelp.net/itbookreview/view_paper.asp?paper_id=770)

Introduction

You can control the Excel table files more flexible by this article and supporting sample source code, including the creation of a new Excel file, written to table data, read table data (including rows that have been manually added to the original Excel file, accurate reading of column data Take it), delete an Excel form, specify lines, columns, cells in the table, insert, replace, etc., can also convert the generated Excel file to other text formats separated by specified separator. Here is the example program of this method to work with VC6:

The basic idea

The basic implementation method is the same as the above article through ODBC to make read, write, etc. in the Database file, so the line name written in the Excel table file. Must be unique (do not reintegrate, equivalent to ID values ​​in the database). In this article, the operation of the Excel file is encapsulated into a class cspreadsheet, which we can easily implement various Excel table data operations, and can expand this class to meet your needs.

Implementation

First, contain the Excel file operation class header file

#include "cspreadsheet.h"

Second, create an Excel file and write the default data

// New Excel file name and path, testsheet is internal table name

Cspreadsheet SS ("C: //Test.xls", "Testsheet");

CstringArray SampleArray, Testrow;

Ss.begintransaction ();

// Add title

SampleArray.removeall ();

SampleArray.Add ("Name");

SampleArray.Add ("age");

Ss.addheaders (SampleArray);

// Add data

CString Strname [] = {"Xu Jingzhou", "Xu Zhihui", "Guo Hui", "Bao Handsome", "Zhu Xiaopeng"};

CString Strage [] = {"27", "23", "28", "27", "26"};

For (int i = 0; i

{

SampleArray.removeall ();

SampleArray.Add (Strname [i]);

SampleArray.Add (Strage [i]);

Ss.addrow (SampleArray);

}

Ss.commit ();

Third, read the Excel file data

Cspreadsheet SS ("C: //Test.xls", "Testsheet");

CstringArray Rows, Column;

// Clear list box

m_accessList.resetContent ();

For (int i = 1; i <= ss.gettotaocks (); i )

{

// read a line

SS.READROW (Rows, i);

CString strcontents = ""

For (int J = 1; j <= rows.getsize (); J )

{

IF (j == 1)

StrContents = rows.getat (j-1); Else

StrContents = strcontents "->" rows.getat (j-1);

}

m_accesslist.addstring (strcontents);

}

Fourth, add, insert, and replace the existing Excel table data

// Initialize the test line data, add, insert, and replace data operation demonstration

For (int K = 1; k <= 2; k )

{

Testrow.Add ("Test");

}

Ss.addrow (testrow); // Add to the tail

Ss.addrow (TestRow, 2); // Insert a new row to the second line

Ss.addrow (Testrow, 6, True); // Replace the fourth line to new content

SS.Addcell ("Xu Jing Week", 1, 2); // Add (not existed) or replace (exist) second line, first column cell content

Ss.commit ();

5. For the existing Excel form data, column, cell query

Void CexceLaccessdlg :: ONQUERY ()

{

Cspreadsheet SS ("C: //Test.xls", "Testsheet");

CstringArray Rows, Column;

CString Tempstring = ""

Updatedata ();

IF (m_strrow == "" && m_strcolumn == "") // query is empty

{

AfxMessageBox ("line number," number, not available at the same time! ");

Return;

}

Else if (m_strrow == "" && m_strcolumn! = ") // query specified column data

{

INT iColumn = atoi (m_strcolumn);

INT ICOLS = ss.gettotalcolumns ();

Icolumn> iCols) // When the table range query

{

CString Str;

Str.Format ("Total number of columns in the table is:% D,", ICOLS);

AfxMessageBox (STR "query column number is greater than the total number of columns in the Excel table, please re-enter!");

Return;

}

// Read a column and read according to the line

IF (! ss.readcolumn (column, icolumn))

{

AfxMessageBox (ss.getlasterror ());

Return;

}

CSTRING TMPSTR;

For (int I = 0; i

{

TMPSTR.FORMAT ("Number:% D, List:% D, Content:% S / N", I 1, IColumn, Column.get (i));

Tempstring = tmpstr;

}

AfxMessageBox (Tempstring);

}

Else if (m_strrow! = "" && m_strcolumn == ") // query specified row number data {

IROW = ATOI (m_strrow);

INT IROWS = ss.gettotalrows ();

IF (irow> irows) // When the table range query

{

CString Str;

Str.Format ("The total number of total lines in the table is:% D,", IROWS;

AfxMessageBox (STR "query is greater than the total number of lines in the Excel table, please re-enter!");

Return;

}

// Read the specified line data

IF (! ss.readrow (rows, irow))

{

AfxMessageBox (ss.getlasterror ());

Return;

}

CSTRING TMPSTR;

For (int i = 0; i

{

TMPSTR.FORMAT ("Number:% D, List:% D, Content:% S / N", IROW, I 1, Rows.Getat (i));

Tempstring = tmpstr;

}

AfxMessageBox (Tempstring);

}

Else if (m_strrow! = "&& m_strcolumn! =") // query specified cell data

{

IROW = ATOI (M_Strrow), IColumn = ATOI (M_STRCOLUMN);

INT IROWS = ss.gettotalrows (), ICOLS = ss.gettotalcolumns ();

Icolumn> iCols) // When the table range query

{

CString Str;

Str.Format ("Total number of columns in the table is:% D,", ICOLS);

AfxMessageBox (STR "query column number is greater than the total number of columns in the Excel table, please re-enter!");

Return;

}

Else IF (Irow> IROWS)

{

CString Str;

Str.Format ("The total number of total lines in the table is:% D,", IROWS;

AfxMessageBox (STR "query is greater than the total number of lines in the Excel table, please re-enter!");

Return;

}

// Read the specified line, column cell data

IF (! ss.readcell (tempstring, icolumn, irow))

{

AfxMessageBox (ss.getlasterror ());

Return;

}

CString Str;

Str.Format ("Number:% D, List:% D, Content:% S", IROW, ICOLUMN, TEMPSTRING

AfxMessageBox (STR);

}

}

6. Save the excel conversion to the specified partial text file

Ss.convert (";"); // convert the original Excel file to a semicolon separated text, and save as the same text file

7. Delete the form of Excel

Ss. DeleteSheet (); // Delete all the forms in the Excel file

SS. DeleteSheet ("testsheet"); // Delete the TEXTSHEET Table 8 in Excel, get the total number of total lines in Excel, the total number of columns, the current line

Int iCols = ss.gettottotalcolumns (); // Total number

INT IROWS = ss.gettotalrows (); // Total number

Int icrrow = ss.getcurrentrow (); // Current line number

Nine, get line data

CStringArray Rowhead;

Ss.GetfieldNames (Rowheader);

CSTRING TMPSTR;

For (int i = 0; i

{

TMPSTR.FORMAT ("Number:% D, List:% D, Content:% S / N", 1, I 1, RowHeader.getat (i));

Tempstring = tmpstr;

}

AfxMessageBox (Tempstring);

Finally, if you want to know in detail details, you can download the source code after downloading the sample source code (detailed note).

references:

Read directly by ODBC, write Excel table files - Xu Jinghou (translation)

A class to read and write to excel and text delimited spreadsheet - YAP Chun WEI

Contact information:

Email: jingzhou_xu@163.com

Future Studio (Future Studio)