Написано: 16.12.2022
Excel без Microsoft Office
Предисловие
Была поставлена задача программно на компьютерах пользователей (страховых агентов) реализовать выпуск Отчета Агента, заполненного в соответствии с требованиями.
Шаблон Отчёта Агента (форма 113м) представляет собой Еxcel-файл. Ниже представлен его фрагмент.
Задача осложнялась тем, что есть страховые агенты, на компьютерах которых пакет Microsoft Office установлен, и агенты, у которых Microsoft Office отсутствует (вместо него используется другой офисный пакет).
Соответственно, существуют разные технологии работы с Excel-файлом в зависимости от среды: в той среде, где пакет Microsoft Office отсутствует, нет необходимых компонентов, которые используются обычно при работе с Excel-файлом.
К счастью, оказалось, что с Excel-файлом можно работать напрямую.
Работа с Excel-файлом.
Excel-файл – представляет собой zip-архив, в котором находятся xml-файлы.
Соответственно, можно программно:
- распаковать zip-архив,
- добраться до нужно xml-файла (в котором содержится информация о значении, хранящемся в той или иной ячейке таблицы),
- изменить данные xml-файла, как нужно
- сформировать новый zip-архив (сделать Excel-файл таким, каким он должен быть).
Тестовое приложение для изменения ячейки Excel-файла.
Технику, которая используется для изменения данных в Excel-файле, иллюстрирует тестовое приложение.
Архив с исходным текстом приложения
Ниже показано окно приложения.
В приложении запрашиваются параметры:
- Имя исходного Excel-файла ( Source File )
- Имя Excel-файла для записи результата ( Dest File )
- Название временной папки, которая будет использоваться для преобразования ( Temp Dir )
- Значение, которое будет записываться в ячейку ( Set Value= )
- Имя ячейки, куда будет записано значение (To Cell)
Также есть кнопка ( Do It ), при нажатии на которую происходит процесс преобразования.
Результат.
Ниже показан результат работы приложения.
На основании файла 3.xlsx был сформирован файл 4.xlsx, а в ячейку AP2 было записано значение 3065010.
Проект TExcel.bpr
Для реализации данного несложного приложения была использована интергированная среда разработки Borland C++ Builder (v.6.0)
Исходный текст приложения демонстрирует несколько интересных техник.
Процедура TForm1::B_DoItClick()
void __fastcall TForm1::B_DoItClick(TObject *Sender)
{
AnsiString SourceDir, SourceFile;
AnsiString DestDir, DestFile;
AnsiString ZipName, DestZip;
AnsiString Unique, TempDir;
AnsiString WorksheetsPath = "xl\\worksheets";
AnsiString sharedStringsPath = "xl";
AnsiString nameSheet1 = "sheet1.xml", nameSharedStrings = "sharedStrings.xml";
AnsiString Worksheets, SharedStrings;
AnsiString fileNameSheet1, fileNameSharedStrings;
AnsiString X;
int pos;
bool b = false;
_di_IXMLDocument xmlSheet1, xmlSharedStrings;
if(E_SourceFile->Text.IsEmpty() == true) {
ShowMessage("Source File не задан");
} else if ( E_DestFile->Text.IsEmpty() == true ) {
ShowMessage("Dest File не задан");
} else if ( E_TempDir->Text.IsEmpty() == true ) {
ShowMessage("Temp Dir не задан");
} else {
SourceFile = GetFileName(E_SourceFile->Text);
// проверка существования исходного файла
if(FileExists(SourceFile) == false) {
X.sprintf("Файл не найден: %s", SourceFile.c_str());
ShowMessage(X);
return ;
}
// Создание темперной папки
Unique = GetUnique();
TempDir.sprintf("%s\\%s", E_TempDir->Text.c_str(), Unique.c_str());
AssertDir(TempDir);
// Копируем исходный файл в темперную папку (и меняем расширение xlsx --> zip)
SourceFile.sprintf("%s", E_SourceFile->Text.c_str());
if(FileExists(SourceFile) == true) {
X = ExtractFileName(SourceFile);
if((pos = X.Pos(".")) > 0) {
X = X.SubString(1, pos - 1);
}
ZipName.sprintf("%s\\%s.zip", TempDir.c_str(), X.c_str());
CopyFile(SourceFile.c_str(), ZipName.c_str(), TRUE);
}
// Извлекаем файл 'Sheet1.xml' из архива
Worksheets.sprintf("%s\\%s", ZipName.c_str(), WorksheetsPath.c_str());
UnZipFile(Worksheets, nameSheet1, TempDir);
// Извлекаем файл 'sharedStrings.xml' из архива
SharedStrings.sprintf("%s\\%s", ZipName.c_str(), sharedStringsPath.c_str());
UnZipFile(SharedStrings, nameSharedStrings, TempDir);
// Открываем 'Sheet1.xml'
xmlSheet1 = NewXMLDocument();
fileNameSheet1.sprintf("%s\\%s", TempDir.c_str(), nameSheet1.c_str());
if(FileExists(fileNameSheet1) == true) {
xmlSheet1->LoadFromFile( fileNameSheet1 );
}
// Открываем 'sharedStrings.xml'
xmlSharedStrings = NewXMLDocument();
fileNameSharedStrings.sprintf("%s\\%s", TempDir.c_str(), nameSharedStrings.c_str());
if(FileExists(fileNameSharedStrings) == true) {
xmlSharedStrings->LoadFromFile( fileNameSharedStrings );
}
// Редактируем 'Sheet1.xml'
b = EditCell(xmlSheet1, xmlSharedStrings, E_Cell->Text, E_Value->Text);
// Сохраняем 'Sheet1.xml'
if(b == true) {
SaveXml(xmlSheet1, fileNameSheet1);
SaveXml(xmlSharedStrings, fileNameSharedStrings);
}
// Помещаем в архив файл 'Sheet1.xml'
UnZipFile(TempDir, nameSheet1, Worksheets);
// Помещаем в архив файл 'sharedStrings.xml'
UnZipFile(TempDir, nameSharedStrings, SharedStrings);
// Копируем результат, куда нужно (и меняем расширение zip --> xlsx)
DestFile = GetFileName(E_DestFile->Text);
if(FileExists(DestFile) == true) {
::DeleteFile(DestFile.c_str());
}
if(FileExists(DestFile) == false) {
MoveFile(ZipName.c_str(), DestFile.c_str());
}
// Удаляем темперную папку
::RemoveDirectory(TempDir.c_str());
// сообщаем, что закончили
ShowMessage("Done");
}
}
Создание темперной папки.
Ниже ещё раз приводится фрагмент создания временной папки:
- Сначала вызывается функция GetUnique (), возвращающая уникальное имя.
- Затем формируется имя уникальной папки (путём соединения имени временного папки и уникального имени, полученного ранее)
- После вызывается функция AssertDir () для проверки папки (и создания, если её нет).
// Создание темперной папки
Unique = GetUnique();
TempDir.sprintf("%s\\%s", E_TempDir->Text.c_str(), Unique.c_str());
AssertDir(TempDir);
Уникальное имя.
AnsiString __fastcall TForm1::GetUnique()
{
AnsiString X;
GUID g;
wchar_t* buf;
try{
CoCreateGuid(&g);
buf = new wchar_t[100];
StringFromGUID2(g, buf, 100);
X = AnsiString(buf);
delete [] buf;
}catch(...) {}
return X;
}
Проверка папки.
Если нет каталога с полученным именем, то проверяются родительские каталоги, если полученное имя содержит имена родительских каталогов, производится проверка для них (путём рекурсивного вызова AssertDir), после проверки родительских каталогов, создаётся отсутствующий каталог.
void __fastcall TForm1::AssertDir(const AnsiString p)
{
if (::GetFileAttributes(p.c_str()) == INVALID_FILE_ATTRIBUTES) { // если каталога нет...
int i = ReversePos(p, "\\"); // если есть, проверяем родителей
if (i > 0) {
AssertDir(p.SubString(1, i - 1));
}
::CreateDirectory(p.c_str(), NULL); // создаем каталог
}
}
Код функции ReversePos () показан ниже.
int __fastcall TForm1::ReversePos(const AnsiString S, const AnsiString Match)
{
int p = 0, n = S.Length();
for (int i = S.Length(); i > 0; i--) {
if(S.SubString(i, 1) == Match && i != n) {
p = i;
break;
}
}
return p;
}
Функция TForm1::UnZipFile()
Для Windows shell-а zip-файл представляет собой папку.
Соответственно, файлы из этой папки (zip-файла) можно скопировать в другую папку (это аналог извлечения файла из архива).
Можно и наоборот: файлы из другой папки переместить в папку zip-файла (это аналог архивации файла, помещения файла в архив).
В функции используется функция GetVariant (). Текст будет приведён ниже.
bool __fastcall TForm1::UnZipFile(AnsiString SourceDir, AnsiString SourceFile, AnsiString DestDir)
{
bool b = false;
HRESULT hResult = S_FALSE;
IShellDispatch *pISD = NULL;
IDispatch *d = NULL;
Folder *pToFolder = NULL, *pFromFolder = NULL, *pCurrentFolder = NULL;
FolderItems *fi = NULL;
FolderItem *item = NULL;
VARIANT vSrc, vDest, vOptions, vItem, vIndex;
long count = 0, OldCount = 0;
BSTR name;
AnsiString ItemName, MatchItem, MatchStr = SourceFile;
bool found = true;
int pos;
short is_folder = 0;
CoInitialize(NULL);
VariantInit(&vOptions);
vOptions.vt = VT_I4;
vOptions.lVal = 0x0004; // FOF_SILENT don't create progress/report
VariantInit(&vItem);
vItem.vt = VT_DISPATCH;
VariantInit(&vIndex);
vIndex.vt = VT_I4;
hResult = CoCreateInstance(CLSID_Shell, NULL, CLSCTX_INPROC_SERVER, IID_IShellDispatch, (void **)&pISD);
if (SUCCEEDED(hResult) && pISD != NULL) {
GetVariant(DestDir, vDest);
hResult = pISD->NameSpace(vDest, &pToFolder);
if (SUCCEEDED(hResult) && pToFolder != NULL) {
GetVariant(SourceDir, vSrc);
hResult = pISD->NameSpace(vSrc, &pFromFolder);
if (SUCCEEDED(hResult) && pFromFolder != NULL) {
pFromFolder->Items(&fi);
// поиск нужно Item-а
fi->get_Count(&count);
while(found == true && MatchStr.IsEmpty() == false) {
found = false;
if((pos = MatchStr.Pos('\\')) > 0 ) {
MatchItem = MatchStr.SubString(1, pos - 1);
MatchStr = MatchStr.SubString(pos + 1, MatchStr.Length() - pos);
} else {
MatchItem = MatchStr;
MatchStr = "";
}
for(long i = 0; i < count; i++) {
vIndex.lVal = i;
fi->Item(vIndex, &item);
item->get_Path(&name);
ItemName = name;
if((pos = ItemName.LastDelimiter('\\')) > 0) {
ItemName = ItemName.SubString(pos + 1, ItemName.Length() - pos);
}
if(ItemName == MatchItem) {
found = true;
break;
}
}
}
if(found == true) {
vItem.pdispVal = item;
} else {
vItem.pdispVal = fi;
}
pToFolder->Items(&fi);
fi->get_Count(&OldCount);
hResult = pToFolder->MoveHere(vItem, vOptions);
// ждем завершения перемещения.
while( true )
{
Sleep(100);
fi = NULL;
pToFolder->Items(&fi);
fi->get_Count(&count);
Application->ProcessMessages();
if( count > OldCount ) {
break;
}
}
pFromFolder->Release();
}
pToFolder->Release();
}
pISD->Release();
}
CoUninitialize();
return b;
}
Функция GetVariant().
void __fastcall TForm1::GetVariant(AnsiString str, VARIANT &var)
{
DWORD strlen = 0;
BSTR strptr;
strlen = MultiByteToWideChar(CP_ACP, 0, str.c_str(), -1, 0, 0);
strptr = SysAllocStringLen(0, strlen);
MultiByteToWideChar(CP_ACP, 0, str.c_str(), -1, strptr, strlen);
VariantInit(&var);
var.vt = VT_BSTR;
var.bstrVal = strptr;
}
Разбор xml-файла.
Приложение демонстрирует также технику разбора xml-файла.
В excel-файле xml-файлами являются как вкладки, так и коллекции строк.
Для работы с xml-файлами используются переменные типа _di_IXMLDocument
Ниже показано, как инициализируется переменная данного типа и как происходит загрузка данных из соответствующего файла.
_di_IXMLDocument xmlSheet1, xmlSharedStrings;
// Открываем 'Sheet1.xml'
xmlSheet1 = NewXMLDocument();
fileNameSheet1.sprintf("%s\\%s", TempDir.c_str(), nameSheet1.c_str());
if(FileExists(fileNameSheet1) == true) {
xmlSheet1->LoadFromFile( fileNameSheet1 );
}
Редактирование ячейки.
Редактирование ячейки осуществляется с помощью функции EditCell ()
В качестве параметров функции передаются:
- указатели на xml-структуры для sheet1 и для sharedStrings
- адрес ячейки, которую нужно изменить.
- значение, которое необходимо записать в ячейку.
// Редактируем 'Sheet1.xml'
b = EditCell(xmlSheet1, xmlSharedStrings, E_Cell->Text, E_Value->Text);
Функция EditCell().
Для редактирования ячейки:
- сначала производится поиск нужного узла для полученного адреса из xmlSheet1
- затем производится запись значения в xmlSharedStrings
- для найденного узла из xmlSheet1 производится обновление индекса
Для выполнения необходимых действий в EditCell () используются соответствующие функции. Они поясняются ниже.
// Редактирование ячейки 'CellName'
// (если ячейка найдена, устанавливается значение 'Value').
// Внимание! Value прописывается в 'sharedStrings.xml',
// а в ячейке прописывается индекс записи из 'sharedStrings'
//
bool __fastcall TForm1::EditCell(
_di_IXMLDocument xmlSheet1, _di_IXMLDocument xmlSharedStrings
, AnsiString CellName, AnsiString Value
)
{
_di_IXMLNode xNode;
AnsiString StrIndex;
bool b = false;
if ((xNode = FindCellNode(xmlSheet1, CellName)) == NULL) {
; // ячейка не найдена
} else if((StrIndex = SetSharedStrings(xmlSharedStrings, Value)).IsEmpty() == true) {
; // неудачная запись в sharedStrings.xml
} else {
xNode->Attributes["t"] = AnsiString("s");
b = SetNodeValue( xNode, "v", StrIndex );
}
return b;
}
Функция FindCellNode(), поиск ячейки.
_di_IXMLNode __fastcall TForm1::FindCellNode(_di_IXMLDocument xmlDoc, AnsiString CellName)
{
_di_IXMLNode xNode = NULL, xRoot;
AnsiString RowName = GetRowName(CellName);
if ((xRoot = xmlDoc->DocumentElement) == NULL) {
; // отсутствует рут
} else if((xNode = FindNode( xRoot, "sheetData" )) == NULL) {
; // отсутствует дата
} else if((xNode = FindNodeAttr( xNode, "row", "r", RowName )) == NULL) {
; // отсутствует нужная строка
} else {
// ищем ячейку
xNode = FindNodeAttr( xNode, "c", "r", CellName );
}
return xNode;
}
Функция SetSharedStrings(), запись в sharedStrings.xml
// Запись полученной строки 'S' в sharedStrings.xml
// Возвращается строковое представление индекса строки.
//
AnsiString __fastcall TForm1::SetSharedStrings(
_di_IXMLDocument xmlDoc, AnsiString S
)
{
_di_IXMLNode xNode = NULL, xRoot;
AnsiString R, X;
int Count, UniqueCount;
_di_IXMLNodeList xChildNodes;
if ((xRoot = xmlDoc->DocumentElement) == NULL) {
; // отсутствует рут
} else {
Count = AnsiString(xRoot->Attributes["count"]).ToIntDef(0);
UniqueCount = AnsiString(xRoot->Attributes["uniqueCount"]).ToIntDef(0);
xChildNodes = xRoot->GetChildNodes();
for (int i = 0; i < xChildNodes->Count; i++) {
xNode = xChildNodes[0][i];
xNode = xNode->ChildNodes->FindNode(S, L"t");
if(xNode != NULL) {
X = xNode->Text;
if(X == S) {
R.sprintf("%i", i); // строка найдена
X.sprintf("%i", Count + 1);
xRoot->Attributes["count"] = X;
break;
}
}
}
if(R.IsEmpty() == true) {
// строка в коллекции не найдена
if((xNode = xRoot->AddChild("si", L"", false)) != NULL) { // добавляем узел "si"
if((xNode = xNode->AddChild("t", L"", false)) != NULL) { // добавляем узел "t"
xNode->Text = S.c_str();
R.sprintf("%i", UniqueCount);
X.sprintf("%i", UniqueCount + 1);
xRoot->Attributes["uniqueCount"] = X;
X.sprintf("%i", Count + 1);
xRoot->Attributes["count"] = X;
}
}
}
}
return R;
}
Функция SetNodeValue(), установка значения для узла
bool __fastcall TForm1::SetNodeValue(_di_IXMLNode parent, AnsiString NodeName, AnsiString Value)
{
bool b = false;
try {
_di_IXMLNode n = FindNode( parent, NodeName );
if(n == NULL) {
n = parent->AddChild(NodeName.c_str(), L"", false);
}
if(n != NULL) {
n->Text = Value.c_str();
b = true;
}
} catch(...) {}
return b;
}
Функция FindNode(), поиск узла
// возвращает узел по полученному имени NodeStr
// (оно может быть составным, например: "aaa\bbb\ccc\ddd")
//
_di_IXMLNode __fastcall TForm1::FindNode( _di_IXMLNode parent, AnsiString NodeStr )
{
AnsiString S = NodeStr, X;
_di_IXMLNode n = parent, r = NULL;
int p;
while (n != NULL) {
if((p = S.Pos("\\")) == 0) {
// пора на выход
r = n->ChildNodes->FindNode(S, L"");
break;
} else {
X = S.SubString(1, p - 1);
S = S.SubString(p + 1, S.Length() - p);
n = n->ChildNodes->FindNode(X, L"");
}
}
return r;
}
Функция FindNodeAttr(), поиск узла с аттрибутами
_di_IXMLNode __fastcall TForm1::FindNodeAttr(
_di_IXMLNode node, AnsiString NodeName, AnsiString AttrName, AnsiString Value
)
{
bool found = false;
_di_IXMLNode xNode = NULL, FoundNode = NULL;
_di_IXMLNodeList xChildNodes;
AnsiString CurrentName, CurrentAttr;
xChildNodes = node->GetChildNodes();
for (int i = 0; i < xChildNodes->Count; i++) {
xNode = xChildNodes[0][i];
CurrentName = AnsiString(xNode->NodeName);
if(CurrentName == NodeName) {
// проверяем совпадение аттрибутов
CurrentAttr = AnsiString(xNode->Attributes[AttrName]);
if(CurrentAttr == Value) {
FoundNode = xNode;
break;
}
}
}
return FoundNode;
}
Функция FindChildNode()
_di_IXMLNode __fastcall TForm1::FindChildNode( _di_IXMLNode node, AnsiString FoundName )
{
bool found = false;
_di_IXMLNode xNode = NULL, FoundNode = NULL;
_di_IXMLNodeList xChildNodes;
AnsiString NodeName;
xChildNodes = node->GetChildNodes();
for (int i = 0; i < xChildNodes->Count; i++) {
xNode = xChildNodes[0][i];
NodeName = AnsiString(xNode->NodeName);
if(NodeName == FoundName) {
FoundNode = xNode;
break;
}
}
return FoundNode;
}
Функция ParseCellRowCol()
void __fastcall TForm1::ParseCellRowCol(AnsiString Cell, int &row, int &col)
{
int i, cp = 1, rp = 1;
const char* c = Cell.c_str();
row = col = 0;
for(i=0; c[i] != '\0'; i++);
for(i = i - 1; i >= 0; i--) {
if(c[i] >= '1' && c[i] <= '9') {
row += (c[i] - '0') * rp;
rp *= 10;
} else if(c[i] >= 'A' && c[i] <= 'Z') {
col += (c[i] - 'A' + 1) * cp;
cp *= 26;
} else if(c[i] >= 'a' && c[i] <= 'z') {
col += (c[i] - 'a' + 1) * cp;
cp *= 26;
}
}
}
Функция GetRowName()
AnsiString __fastcall TForm1::GetRowName(AnsiString CellName)
{
int row, col;
AnsiString X;
ParseCellRowCol(CellName, row, col);
X.sprintf("%i", row);
return X;
}
Функция SaveXml()
bool __fastcall TForm1::SaveXml(_di_IXMLDocument xmlDoc, AnsiString FileName)
{
AnsiString X, OldFile;
X = StringReplace(xmlDoc->XML->Text, " xmlns=\"\"", "", TReplaceFlags()<< rfReplaceAll << rfIgnoreCase); // убираем ' xlmns=""'
// xmlDoc->SaveToFile( FileName ); // после того, как убрали пустые xlmns, xmlDoc стал ругаться при сохранении, будем сохранять самостоятельно
// xmlDoc->XML->Text = X;
OldFile.sprintf("%s.1", FileName.c_str());
if(RenameFile(FileName, OldFile) == true) {
FILE* f;
f = fopen(FileName.c_str(), "wb");
fclose(f);
f = fopen(FileName.c_str(), "a+");
fputs(X.c_str(), f);
fclose(f);
DeleteFile(OldFile.c_str());
}
}
Функция GetFileName()
AnsiString __fastcall GetFileName(AnsiString FileName)
{
AnsiString X, R;
X.sprintf("%s", FileName.c_str());
if(X.Pos("\\") > 0 || X.Pos(":") > 0) {
// ôàéë ñ ïóòåì, âîçâðàùàåì, êàê åñòü
R = X;
} else {
// ïóòè íåò, äîáàâëÿåì ïóòü çàïóñêà
R.sprintf("%s%s"
, ExtractFilePath(Application->ExeName).c_str()
, X.c_str()
);
}
return R;
}