VC Excel files and cspreadsheet Chinese documents

Because of work requirements, data needs to be exchanged between the Excel and MySQL databases, so I searched the internet for how to use VC to Operate Excel. There are basically three implementation methods, one is to use ODBC, one is to use Microsoft's automation technology, and the other is to directly analyze the Excel format and parse it.

Find two open-source operation classes on codeproject, one is basicexcel, address: http://www.codeproject.com/KB/office/BasicExcel.aspx

One is cspreadsheet, address: http://www.codeproject.com/KB/database/cspreadsheet.aspx

 

I used both of them and finally decided to use basicexcel. Let's talk about my feelings:

1. cspreadsheet, which is implemented using ODBC, is slow and has many bugs. However, it provides better support for Chinese characters, but I have the most intolerable certainty, it is used as a string to write all the data into the Excel file. As a result, a single quotation mark is automatically added before each field in the Excel file. The reason is that in order to prevent automatic format conversion, add a single quotation mark to forcibly convert each data item to the string type. In the end, I had to stop using it.

 

2. basicexcel is implemented using COM, which provides fast access and Simple API interfaces. You can set the field content type in excel at will, but it does not support Chinese, you must encode Chinese characters by yourself. ANSI and Unicode characters are supported. At the beginning, all the Chinese characters written are garbled characters. Later, I made character conversion. The gb2312 Encoded chinese characters can be converted to unicode encoding, so they can be properly displayed.

 

The two types have API usage instructions and sample projects on the official website...

As I was just using it, I feel so much at the moment that Microsoft is in trouble...

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhsp1029/archive/2008/09/23/2970255.aspx

 

Cspreadsheet Chinese Document
Http://www.codeproject.com/info/Licenses.aspx
Introduction
Cspreadsheet is a C ++ Excel read/write control.
When this file is separated by tabs, cspreadsheet can help us get twice the result with half the effort. This control
This makes it easy for us to read and write such files and use them as objects.

Main features

Create an Excel file or text feature file. Write multiple rows or one row. read multiple rows, columns, and one row from the Excel file

Or text feature files. Replace, insert, or append to an Excel file or text feature file. The conversion already exists or is most recent

Opened Excel files or text feature files.

Restrictions
This control must be supported by MFC (Microsoft basic library). It is not tested whether Unicode encoding is supported. The control can be read and written using ODBC.

Excel file, ODBC driver. Excel file must be column marked. And the first row and column marked unique (field). Prohibited

Delete A Workbook. Only the content of the workbook can be deleted. The column value type refers to the program data type. Excel format is not used.

I. How to use this class?

Common functions:
Cspreadsheet (cstring file, cstring sheetorseparator, bool backup = true)
Bool addheaders (cstringarray & fieldnames, bool replace = false)
Bool addrow (cstringarray & rowvalues, long row = 0, bool replace = false)
Bool addcell (cstring cellvalue, short column, long row = 0)
Bool addcell (cstring cellvalue, cstring column, long row = 0, bool auto = true)
Bool readrow (cstringarray & rowvalues, long row = 0)
Bool readcolumn (cstringarray & columnvalues, short column)
Bool readcolumn (cstringarray & columnvalues, cstring column, bool auto = true)
Bool readcell (cstring & cellvalue, short column, long row = 0)
Bool readcell (cstring & cellvalue, cstring column, long row = 0, bool auto = true)
Bool deletesheet ()
Bool deletesheet (cstring sheetname)
Bool convert (cstring sheetorseparator)
Void begintransaction ()
Bool commit ()
Bool rollback ()
Bool gettransactionstatus ()
Void getfieldnames (cstringarray & fieldnames)
Long gettotalrows ()
Short gettotalcolumns ()
Long getcurrentrow ()
Bool getbackupstatus ()
Cstring getlasterror ()

Excel-specific functions:
Bool replacerows (cstringarray & newrowvalues, cstringarray & oldrowvalues)
Text Functions:
No.

Function introduction:
Cspreadsheet (cstring file, cstring sheetorseparator, bool backup = true)

This constructor opens an Excel (xls) file or other workbook files for reading and writing. Creates a cspreadsheet object.
Parameters:
File: the file path, which can be an absolute or relative path. If the file does not exist, a file is created.
Sheetorseparator workbook name.
Backup specifies whether to back up files. By default, files are not backed up. If the files exist, a backup file named cspreadsheetbackup will be created.

Bool addheaders (cstringarray & fieldnames, bool replace = false)

This function adds a header (field) to the first line of the opened Workbook. For excel, each column field must be unique.
There is no limit on character text files. A column is added by default for an open workbook file. If replace = true is set
The existing field value will be replaced. This function returns a bool type value. For excel, this function requires
Called before adding any row. This function is optional for text files with specific features.
Parameters:
Fieldnames field name array.
If the replace field exists, this parameter determines whether to replace the original field.

Bool addrow (cstringarray & rowvalues, long row = 0, bool replace = false)

This function appends, inserts, or replaces a row to an opened document. By default, it is appended to the end of the row. the substitution is determined by the value of the variable, and the new row is inserted or replaced with the specified row.
Parameters:
Rowvalues row Value
Row row number. If ROW = 1, the first row is the field row.
If this row exists, replace indicates whether to replace the original row.

Bool addcell (cstring cellvalue, short column, long row = 0)
Bool addcell (cstring cellvalue, cstring column, long row = 0, bool auto = true)
Add or replace a cell in an open Workbook. By default, it is the last cell of the row. A bool type value (Status) is returned );
Parameters:
The value of the cell filled with cellvalue.
Column number
Column name
Row contains numbers. If ROW = 1, the first line is the field row.
Auto determines whether to enable the function to automatically determine fields.

Bool readrow (cstringarray & rowvalues, long row = 0)

Read a row from an opened Workbook. By default, the next row is read. If you do not use the connection pool and run twice consecutively, the first row is read and the second row is read. returns a value of the bool type (Status );
Parameters:
Rowvalues is used to store read values.
Row row number. The default value is the first row.

Bool readcolumn (cstringarray & columnvalues, short column)
Bool readcolumn (cstringarray & columnvalues, cstring column, bool auto = true)
Reads a column from an open Workbook. Returns a Boolean value (Status );
Parameters:
Short Column number
Cstring column name or field name.
Columnvalues stores the read value.
Auto sets the function to automatically scan column names or fields.

Bool readcell (cstring & cellvalue, short column, long row = 0)
Bool readcell (cstring & cellvalue, cstring column, long row = 0, bool auto = true)

Read the value of a cell from an opened workbook. By default, the next row is read. A bool type value (Status) is returned );
Parameters:
Cellvalue stores the cell value.
Short Column number.
Row row number
Cstring column name
Auto sets the function to automatically scan column names or fields.

Bool deletesheet ()
Deletes all workbooks from an opened document. A bool value (Status) is returned );

Bool deletesheet (cstring sheetname)
Deletes the content of a specified workbook from an open document. A bool value (Status) is returned );
Parameters:
Sheetname: Workbook name. e. g sheet1

Bool convert (cstring sheetorseparator)
Convert the excel(xls(file to a specific local file (.csv)or convert the specified local file (.csv) to an Excel (xls) file. If
Converting a special certificate file (.csv) to an Excel (xls) file sheetorseparator will not be used. A bool type value (Status) will be returned );
Parameters:
Sheetorseparator feature style.

Void begintransaction ()
Bool commit ()
Bool commit ()
Similar to SQL functions and functions, begintransaction starts a transaction and commit commits the transaction. roolback rolls back to the storage point. Commit commit returns a bool value to indicate whether the transaction is successful.

Bool gettransactionstatus ()
Query the transaction status. If it is true, it indicates that the transaction has started. If it is false, it indicates that the transaction has not started or ended.

Void getfieldnames (cstringarray & fieldnames)
Returns an array of fields in a workbook.
Parameters:
Fieldnames stores the array of field names.

Long gettotalrows ()
Returns the number of rows in the workbook.

Short gettotalcolumns ()
Returns the number of columns in the workbook.

Long getcurrentrow ()
Obtain the row number of the selected current row. Return the row number. The current row calls the default readrow function.

Bool getbackupstatus ()
To obtain the backup execution status, true has been executed, false has not been executed (User selected) or execution error.

Cstring getlasterror ()

Returns the last error message.
Excel-specific functions:
Bool replacerows (cstringarray & newrowvalues, cstringarray & oldrowvalues)
This function will search for and replace the values of multiple executions. documents that have been rolled back or released are not supported. The execution status will be returned after execution.
Parameters:

Newrowvalues: the new value.
Oldrowvalues original value, that is, the existing value.

Example:
// Create a new Excel spreadsheet, filename is test.xls, sheetname is testsheet
Cspreadsheet SS ("test.xls", "testsheet ");

// Fill a sample 5 by 5 Sheet
Cstringarray samplearray, testrow, rows, column;
Cstring tempstring;
Char alphabet = 'a ';

SS. begintransaction ();
For (INT I = 1; I <= 5; I ++)
{
Samplearray. removeall ();
For (Int J = 1; j <= 5; j ++)
{
Tempstring. Format ("% C % d", alphabet ++, I );
Samplearray. Add (tempstring );
}
Alphabet = 'a ';
If (I = 1) // Add header rows
{
SS. addheaders (samplearray );
}
Else // Add data rows
{
SS. addrow (samplearray );
}
}
// Set up test row for appending, inserting and replacing
For (int K = 1; k <= 5; k ++)
{
Testrow. Add ("test ");
}
SS. addrow (testrow); // append test row to spreadsheet
SS. addrow (testrow, 2); // insert test row into second row of Spreadsheet
SS. addrow (testrow, 4, true); // replace fourth row of spreadsheet with test row
SS. committ ();

SS. Convert (";"); // convert Excel spreadsheet into text delimited format
// With; as Separator
// Print out total number of rows
Printf ("Total number of rows = % d/n", ss. gettotalrows ());
// Print out entire Spreadsheet
For (I = 1; I <= ss. gettotalrows (); I ++)
{
// Read row
SS. readrow (rows, I );
For (Int J = 1; j <= rows. getsize (); j ++)
{
If (J! = Rows. getsize ())
{
Printf ("% S/T", rows. getat (J-1 ));
}
Else
{
Printf ("% s/n", rows. getat (J-1 ));
}
}
}
// Print out total number of Columns
Printf ("/ntotal Number of columns = % d/n", ss. gettotalcolumns ());

// Read and print out contents of second column of Spreadsheet
SS. readcolumn (column, 2 );
For (I = 0; I <column. getsize (); I ++)
{
Printf ("column 2 row % d: % s/n", I + 1, column. getat (I ));
}
// Read in and print out the cell value at column 3, Row 3 of Spreadsheet
If (ss. readcell (tempstring, 3, 3 ))
{
Printf ("/ncell value at (3, 3): % s/n", tempstring );
}
Else
{
// Print out error message if cell value cannot be read
Printf ("error: % s/n", ss. getlasterror );
}

 

About Author:
Yap Chun Wei
Http://www.codeproject.com/Members/Yap-Chun-Wei
Translation:
Meng dejun, Wuhan Software Engineering Vocational College
Http://hi.baidu.Com/mak0000
Download:
Http://www.codeproject.com/KB/database/CSpreadSheet/CSpreadSheet_src.zip