CSpreadSheet - 用于读写 Excel 和文本分隔电子表格的类

Yap Chun Wei

4.79/5 (53投票s)

2001年12月5日

21分钟阅读

1218641

19284

用于读写 Excel 和文本分隔电子表格的类。

下载源文件 - 7 Kb

引言

有时我们希望将输出发送到Excel文件或一些文本分隔文件，例如逗号分隔值文件或制表符分隔文件。这个类封装了读写这些类型文件的函数。它的构建目标是易于使用。该类的主要功能有：

构建新的Excel或文本分隔文件，并向其中写入行或单个单元格。
从Excel或文本分隔文件中读取行、列或单个单元格。
替换、插入或追加行到Excel或文本分隔文件中。
将现有或新创建的Excel文件转换为文本分隔文件，反之亦然。

在描述该类的各种功能之前，我将首先说明该类的局限性。这些局限性是：

需要MFC。
可能支持也可能不支持Unicode。（未检查）
使用ODBC读写Excel文件，因此会受到ODBC驱动程序的限制。（我不确定ODBC驱动程序有哪些限制）
Excel文件中必须有标题行，并且标题行的每一列都必须是唯一的。（因为该类将Excel文件视为数据库）
无法从Excel文件中删除工作表。只能删除该工作表的内容。
单元格值被视为字符串，无论其在Excel中的格式如何。

我尽可能插入了错误检查代码，但可能遗漏了一些情况。因此，用户需要牢记这些限制，以防止意外错误。

以下是该类的详细描述。它分为两部分。第一部分适用于只想了解如何使用该类的人。它包含所有有用功能的简要描述以及如何使用它们。接着是一个示例，演示了该类中的大多数功能。

第二部分适用于希望详细了解该类所有功能和变量的人。该部分将描述函数的工作原理、局限性和可能发生的错误，以及这些函数经过多次修订后如何达到最终形式。这对于那些打算修改该类以适应自身需求的人将非常有用。

现在，准备好迎接一篇长文吧！

第一部分：如何使用该类

以下是该类中有用功能的概述，按它们是Excel和文本分隔电子表格共有的功能还是仅限于其中之一的功能进行组织。

常用函数

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电子表格特定功能

bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)

文本分隔电子表格特定功能

无

常用函数

CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup = true)

此构造函数将打开一个Excel文件（xls）或一个文本分隔文件（任何其他文件扩展名，例如csv，tab），用于读取（对于现有文件）或写入（对于新文件或现有文件）。变量SheetOrSeparator是Excel文件的工作表名称，或者是文本分隔文件的分隔符类型。如果打开现有文件，默认构造函数将创建一个备份文件（文本分隔文件为.bak）或一个备份工作表（Excel文件命名为CSpreadSheetBackup）。

bool AddHeaders(CStringArray &FieldNames, bool replace = false)

此函数将向打开的电子表格添加一个标题行。对于Excel电子表格，标题行的每一列都必须是唯一的。对于文本分隔文件，没有此限制。如果打开的电子表格是现有文件，默认情况下会将新列添加到标题行，除非变量replace设置为true。返回值将指示添加是否成功。请注意，对于新的Excel电子表格，此函数应在向电子表格添加任何行之前调用。对于文本分隔电子表格，此函数是可选的。

bool AddRow(CStringArray &RowValues, long row = 0, bool replace = false)

此函数将向打开的电子表格追加、插入或替换一行。默认是向电子表格末尾追加一行。根据变量replace的值，新行将被插入或替换在指示的行。默认是插入新行在指示的行。返回值将指示添加是否成功。请注意，row=1表示第一行（即标题行）。

bool AddCell(CString CellValue, short column, long row = 0) bool AddCell(CString CellValue, CString column, long row = 0, bool Auto = true)

第一个AddCell函数将向打开的电子表格添加或替换一个单元格。默认是在电子表格末尾追加一个新行，其中包含指定列的单元格值。

第二个AddCell函数类似，不同之处在于变量column可以是Excel列名（例如A、B、AE、EF等）或标题行的字段名。如果不想函数自动检测变量column是Excel列名还是标题行字段名（对于标题行字段名为两个字母的情况），请将变量Auto设置为false。

返回值将指示添加是否成功。请注意，row=1表示第一行（即标题行）。

bool ReadRow(CStringArray &RowValues, long row = 0)

此函数将从打开的电子表格中读取一行。默认是读取下一行。也就是说，如果您连续两次运行该函数，或者在中间执行了一些语句后再次运行，则第一次该函数将返回第一行，第二次将返回第二行。返回值将指示该行是否正确读取或是否没有更多行可读。请注意，row=1表示第一行（即标题行）。

bool ReadColumn(CStringArray &ColumnValues, short column) bool ReadColumn(CStringArray &ColumnValues, CString column, bool Auto = true)

第一个函数将从打开的电子表格中读取一列。变量column以列号表示。
第二个函数类似，不同之处在于变量column可以是Excel列名（例如A、B、AE、EF等）或标题行的字段名。如果不想函数自动检测变量column是Excel列名还是标题行字段名（对于标题行字段名为两个字母的情况），请将变量Auto设置为false。

返回值将指示列的读取是否成功。

bool ReadCell (CString &CellValue, short column, long row = 0) bool ReadCell (CString &CellValue, CString column, long row = 0, bool Auto = true)

第一个函数将从打开的电子表格中读取单个单元格。默认是读取下一行中的单元格（类似于上面的ReadRow）。

第二个函数类似，不同之处在于变量column可以是Excel列名（例如A、B、AE、EF等）或标题行的字段名。如果不想函数自动检测变量column是Excel列名还是标题行字段名（对于标题行字段名为两个字母的情况），请将变量Auto设置为false。

返回值将指示列的读取是否成功。请注意，row=1表示第一行（即标题行）。

bool DeleteSheet() bool DeleteSheet(CString SheetName)

第一个函数将擦除打开的电子表格的全部内容。

第二个函数将擦除打开的Excel电子表格中指定工作表的全部内容。该工作表可以是打开的Excel电子表格中存在的任何工作表。

返回值将指示擦除是否成功。请注意，这两个函数不支持Excel电子表格的回滚。

bool Convert(CString SheetOrSeparator)

此函数将Excel工作表转换为具有指定分隔符的文本分隔文件（同名的.csv文件），反之亦然。如果从文本分隔文件转换为Excel文件，则不使用分隔符变量。返回值将指示转换是否成功。

void BeginTransaction() bool Commit() bool RollBack()

以上三个函数与SQL中对应的函数类似。使用BeginTransaction表示事务的开始。使用Commit保存对电子表格所做的更改。使用RollBack撤消对电子表格所做的更改（即，将电子表格恢复到BeginTransaction语句之前的状态）。Commit和RollBack的返回值将分别指示更改是否成功保存或撤消是否成功。

bool GetTransactionStatus()

此函数将返回事务状态。如果事务已启动，则为True。如果事务未启动或已结束，则为False。

void GetFieldNames (CStringArray &FieldNames)

此函数将获取电子表格的标题行。

long GetTotalRows()

此函数将返回电子表格中的总行数。

short GetTotalColumns()

此函数将返回Excel电子表格中的总列数。对于文本分隔电子表格，它将返回电子表格中存在的最大列数。

long GetCurrentRow()

此函数将返回电子表格中当前选定的行。当前选定的行是默认ReadRow函数或默认ReadCell函数将读取的行。

bool GetBackupStatus()

此函数将返回电子表格的备份状态。如果已执行备份，则为True。如果未执行备份（无论是由于用户的选择还是发生错误），则为False。

CString GetLastError()

此函数将返回最后一个错误消息。对于某些函数，如AddHeaders、AddRow、AddCell等，可能会发生一些错误。此函数将返回一个包含所发生错误描述的字符串。

Excel电子表格特定功能

bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues) 此函数将搜索并替换旧行的多个出现处为新行。此函数不支持回滚，因此无法撤消更改。返回值将指示替换是否成功。

功能演示

// 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\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\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); 
}

第二部分。函数和变量的详细描述

CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup = true)

该函数将首先检查文件扩展名，以确定它是Excel文件（.xls）还是文本分隔文件（任何其他扩展名）。如果文件是Excel文件，它将把内部标志m_bExcel设置为true。否则，m_bExcel为false。文件名存储在m_sFile中。然后，构造函数将字符串SheetOrSeparator分配为Excel文件的工作表名称（通过设置m_sSheetName）或文本分隔文件的分隔符（通过设置m_sSeparator）。

然后通过调用Open函数打开文件。使用另一个函数打开电子表格的原因是，该函数也将被RollBack函数用于撤消对文件的更改。成功打开文件后，电子表格的总行数和列数将被找到并分别存储在m_dTotalRows和m_dTotalColumns中。电子表格的标题行也被找到并存储在m_aFieldNames中。当前行（存储在m_dCurrentRow中）然后设置为1。这一行对应于标题行。所有行都存储在内存中（存储在m_aRows中）。对于Excel电子表格，每行中的所有列首先通过分隔符“ ,;.?”连接，然后存储在m_aRows中。这种将行存储在内存中的方式允许实现撤消功能。如果需要，然后执行以前文件或工作表的备份。备份状态然后存储在标志m_bBackup中。

Excel文件使用ODBC驱动程序打开，因此它被视为数据库。由于Excel驱动程序的限制，记录集只能用于从文件读取。记录集不能用于向文件写入。因此，SQL语句用于向Excel文件写入。即便如此，也只有SQL的一个子集可用。因此，向Excel文件写入有些繁琐且可能很慢。

bool AddHeaders(CStringArray &FieldNames, bool replace = false)

在文本分隔电子表格中添加、替换或追加标题行是简单直接的。然而，对于Excel电子表格来说则不然。这是因为Excel电子表格是使用ODBC驱动程序打开的。ODBC驱动程序默认将Excel电子表格的第一行视为包含数据库字段名的行。因此，标题行的每一列都必须是唯一的。标题行中不能有两个名称相同的单元格。因此，当在Excel电子表格中追加标题行时，函数必须首先检查标题行的列是否唯一。当在Excel电子表格中添加新标题行或替换标题行时，检查重复列名的工作会传递给AddRow函数。

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)

这三个函数在实现上非常相似，只是AddCell不支持插入。单个单元格的插入（无论是向下移动单元格还是向右移动单元格）过于繁琐且很少使用（至少对我而言）。任何需要此类插入的人都可以修改此函数。我不会这样做。
函数流程如下：首先，函数检查用户是否指定了要添加新行或单元格的行。如果用户未指定任何行，则新行或单元格将追加到电子表格的末尾。如果指定了行，函数将检查指定行是否大于电子表格中的总行数。如果指定行大于总行数，则会向电子表格添加空白行，直到刚好达到指定行之前。然后将新行追加到电子表格的末尾。如果指定行小于总行数，则AddRow函数将检查新行是插入到指定行还是替换指定行。对于AddCell，新单元格将替换指定单元格。

对于Excel电子表格，还有一些额外的检查。首先，如果指定的行是标题行，它将检查以确保没有重复的列名。然后它将检查以确保标题行中的列数不会减少，因为这会影响Excel电子表格的完整性（请记住Excel被视为数据库）。如果指定的行不是标题行，函数将检查以确保存在标题行。然后它将检查以确保添加的总列数不超过标题行中的列数。

AddCell函数被重载，为用户提供了多种指定所需列的方式。列可以指定为字段名、Excel电子表格中的列字母或从1开始的列号。当列以字符串形式输入时，函数将自动检测列是以字段名还是列字母表示。它通过检查输入字符串的长度来实现这一点。如果输入字符串的长度为1或2，它将假定该字符串是以列字母表示。否则，它将假定该字符串是以字段名表示。对于列字母的情况，函数将调用CalculateColumnNumber函数以获取正确的列号。对于字段名的情况，函数将使用字段名与标题行的每一列进行匹配以获取正确的列号。变量Auto用于处理字段名为1或2个字符长度的特殊情况。由于自动检测列类型的算法，长度为1或2个字符的字段名将被错误地分类为列字母，从而导致计算出的列号不正确。对于此类特殊情况，将Auto设置为false将禁用自动分类系统，并使函数将列解释为字段名而不是列字母。

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)

这5个函数的实现相对简单。ReadRow函数是这五个函数中最重要的函数。此函数由AddRow和AddCell函数调用。当文件是文本分隔文件时，ReadColumn和ReadCell函数也使用此函数。

ReadRow 函数首先需要将每行拆分为单独的列。行通过分隔符 (m_sSeparator) 拆分。对于 Excel 电子表格，分隔符是 ,;.?. 该函数首先会尝试通过假定文件具有以下语法来拆分行："column1"separator"column2"separator"column3"。例如，对于 csv 文件："column1","column2","column3"。如果函数无法以这种方式拆分行，它将尝试通过假定文件具有 column1separatorcolumn2separatorcolumn3 的语法来拆分行。例如，对于 csv 文件：column1,column2,column3。除了上述两种语法之外的其他语法不受支持。如果文本分隔文件属于第一种语法，则该函数将删除第一列和最后一列的引号。此步骤是必要的，因为行的拆分方式就是这样实现的。对于 Excel 电子表格，行使用第一种语法存储在内存中。
ReadColumn和ReadCell函数依赖于ReadRow函数才能工作。对于ReadCell，它将调用ReadRow以获取所需行的列，然后获取所需的单元格值。对于ReadColumn，它将多次调用ReadRow以提取所需列。
至于AddCell的情况，ReadColumn和ReadCell函数被重载，以便在输入所需列时具有灵活性。变量column和Auto的用法与AddCell相同。

bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)

此函数使用SQL语句UPDATE...SET...WHERE在Excel电子表格中搜索和替换多行。由于它使用SQL，因此该函数不适用于文本分隔电子表格。此外，该函数不作用于行的内存副本，而是直接作用于磁盘副本。因此，无法撤消更改。

bool DeleteSheet() bool DeleteSheet(CString SheetName)

这两个重载函数对于文本分隔电子表格来说是直接的。基本上，它删除行的内存副本和字段名。它还将总列数和行数重置为零。对于Excel电子表格，它使用SQL语句DROP TABLE。因此，删除无法撤消，因为它直接作用于磁盘副本。此外，由于ODBC驱动程序的限制，工作表本身不会被删除。只会擦除内容。

bool Convert(CString SheetOrSeparator)

将Excel电子表格转换为文本分隔电子表格相当简单。此函数可用于Excel到文本分隔电子表格或反之的批量转换。但是，在从文本分隔电子表格转换为Excel时，必须牢记标题行中每列唯一性的限制。

void BeginTransaction() bool Commit() bool RollBack()

这三个函数非常有用，因为它们可以通过使用RollBack函数来撤销更改。如果您正在大量添加行或单元格，使用BeginTransaction还有另一个优点。默认情况下，每次添加后，更改都会保存到磁盘。但是如果使用BeginTransaction，则将更改保存到磁盘会延迟，直到调用Commit函数。这将大大加快行或单元格的添加速度。

Commit 的实现对于文本分隔电子表格来说是直接的。对于 Excel 电子表格，实现稍微复杂一些。如前所述，写入 Excel 文件需要使用 SQL 语句。要写入 Excel 文件，我们必须注意工作表是已经存在还是新工作表。这两种情况在 CREATE TABLE 语句中需要不同的方法。对于新工作表，我们将使用 CREATE TABLE sheetname。对于现有工作表，我们必须首先删除工作表。然而，如前所述，删除 Excel 电子表格只会删除其内容。实际的工作表不会被删除。这会带来问题，因为现在无法使用 CREATE TABLE sheetname，因为 ODBC 驱动程序将返回一个异常，指出工作表已经存在。但是，如果您使用 INSERT INTO 语句将行插入电子表格，它也会返回一个错误。解决此问题的方法是使用 CREATE TABLE [sheetname$A1:IV65536] 首先创建工作表，然后使用 INSERT INTO 语句插入行。我发现另一个有趣的事情是 INSERT INTO 语句也不是那么直接。要将一行插入工作表，您必须发出语句 INSERT INTO [sheetname$A1:IVx]，其中 x 是一个递增的数字，它随着电子表格中行数的增加而增加。

bool Open() Open 函数由构造函数和 RollBack 函数调用。它基本上是打开电子表格，读取其内容并将其存储在内存中，然后关闭文件。此函数不能由用户直接调用。

void GetExcelDriver() 此函数获取 Excel-ODBC 驱动程序的名称。它由构造函数调用，不能由用户直接调用。

short CalculateColumnNumber(CString column, bool Auto) 此函数将字母形式的Excel列或Excel字段名转换为列号。它被AddCell、ReadCell和ReadColumn函数使用。它不能由用户直接调用。

变量

bool m_bAppend; // Internal flag to denote newly created spreadsheet or 
                // previously created spreadsheet
bool m_bBackup; // Internal flag to denote status of Backup
bool m_bExcel; // Internal flag to denote whether file is Excel spreadsheet 
               // or text delimited spreadsheet
bool m_bTransaction; // Internal flag to denote status of Transaction
long m_dCurrentRow; // Index of current row, starting from 1
long m_dTotalRows; // Total number of rows in spreadsheet
short m_dTotalColumns; // Total number of columns in Excel spreadsheet. Largest number of columns 
                       // in text delimited spreadsheet
CString m_sSql; // SQL statement to open Excel spreadsheet for reading
CString m_sDsn; // DSN string to open Excel spreadsheet for reading and writing
CString m_stempSql; // Temporary string for SQL statements or for use by functions
CString m_stempString; // Temporary string for use by functions
CString m_sSheetName; // Sheet name of Excel spreadsheet
CString m_sExcelDriver; // Name of Excel Driver
CString m_sFile; // Spreadsheet file name
CString m_sSeparator; // Separator in text delimited spreadsheet
CString m_sLastError; // Last error message
CStringArray m_atempArray; // Temporary array for use by functions
CStringArray m_aFieldNames; // Header row in spreadsheet
CStringArray m_aRows; // Content of all the rows in spreadsheet
CDatabase *m_Database; // Database variable for Excel spreadsheet
CRecordset *m_rSheet; // Recordset for Excel spreadsheet

历史

当我第一次开始编写这个类时，我首先实现了Excel电子表格的读取。然而，很快发现那个实现对于写入Excel文件来说不是很好。所以我修改了它，很快它就能够读写Excel电子表格了。关于SQL语句出现了一些小问题。经过一段时间的调试，我才设法使SQL语句正确。然后我开始添加处理文本分隔文件的函数。起初一切似乎都很好，我准备发布这个类。然而，我越想越觉得有些不对劲。在那个版本中，BeginTransaction、Commit和Rollback函数只能在文本分隔文件上工作。由于我开始实现Excel部分的方式，任何更改都会立即写入磁盘。这阻止了任何更改的撤销，并降低了该类在进行大量读写操作时的性能。所以我决定彻底改造Excel部分，最终的实现类似于文本分隔文件部分。这使得BeginTransaction、Commit和Rollback函数得以使用。然而，我必须承认，到这个时候我已经相当厌倦了处理这个类。因此，我可能没有像第一个版本那样彻底检查所有函数。所以这个类中可能存在错误。我欢迎任何改进这个类的建议和任何错误报告。最后，我希望任何修改这个类的人都能发布修改后的版本供大家使用，以便所有人都能受益。

致谢

我要感谢Alexander Mikula为我编写这个类提供了灵感。如果没有他关于使用ODBC驱动程序读写Excel的文章，我将无法开始编写这个类。我还要感谢Christopher W. Backen提供了GetExcelDriver函数。最后，我要感谢所有设法阅读完这篇文章的人。我知道这是一篇相当长的文章。嗯，这个类不小（1335行），而且有很多函数，所以我希望提供一份很好的文档。希望有了这份文档，初学者可以轻松无问题地使用这个类，高级用户可以根据自己的需求更好地修改这个类。