Programmatic implementation of Excel table file operations

Brief introduction

Through this article and the supporting sample source code you can more flexible control of Excel form files, this includes creating new Excel files, writing tabular data, reading tabular data (including accurate reading of rows and column data that have been manually added to the original Excel file), deleting existing Excel tables, and specifying rows in the table , columns, cells, query, insert, and replace, and you can also convert the resulting Excel file to a file in another text format delimited by the specified delimiter. Here is a sample program that uses this method with VC6 to run the effect:

Basic ideas

The basic implementation of the same article, "read directly through ODBC, Excel form file" is the same, through ODBC to the Excel table file as a database file for reading, writing, and so on, so the Excel table file to write in the row header name must be unique (do not duplicate the name, Equivalent to the ID value in the database). The operation of Excel files in this article is encapsulated into a class cspreadsheet, through which we can easily implement various Excel table data operations, and can expand the class to meet their own needs.

Concrete implementation

One, contains Excel file operation class header file

#include "CSpreadSheet.h"Second, create a new Excel file and write the default data

// 新建Excel文件名及路径，TestSheet为内部表名
CSpreadSheet SS("c:\\Test.xls", "TestSheet");
CStringArray sampleArray, testRow;
　　
SS.BeginTransaction();
　　
// 加入标题
sampleArray.RemoveAll();
sampleArray.Add("姓名");
sampleArray.Add("年龄");
SS.AddHeaders(sampleArray);
// 加入数据
CString strName[] = {"徐景周","徐志慧","郭徽","牛英俊","朱小鹏"};
CString strAge[]　= {"27","23","28","27","26"};
for(int i = 0; i < sizeof(strName)/sizeof(CString); i++)
{
　　sampleArray.RemoveAll();
　　sampleArray.Add(strName[i]);
　　sampleArray.Add(strAge[i]);
　　SS.AddRow(sampleArray);
}
　　
SS.Commit();

Third, read Excel file data

CSpreadSheet SS("c:\\Test.xls", "TestSheet");
CStringArray Rows, Column;
//清空列表框
m_AccessList.ResetContent();
for (int i = 1; i <= SS.GetTotalRows(); i++)
{
　　// 读取一行
　　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);
}