学习windows excel转json工具实现 2-爱代码爱编程
Windows excel转json工具实现 主要源文件
介绍主要的逻辑代码 一些简单的就没有展示了
//ExcelOperator.h 转sjon的主要类
//读取excel 分为每个sheet记录数据到类内
void ExcelOperator::ReadExcel(TSTR filePath)
{
xlnt::workbook wb;//xlnt 库
wb.load(filePath.c_str());
COUT << _T("sheet count:") << wb.sheet_count() << std::endl;
int sheetCount = wb.sheet_count();
ClearSheet();
for (int sheetIndex = 0; sheetIndex < sheetCount; ++sheetIndex)
{
auto ws = wb.sheet_by_index(sheetIndex);
xlnt::range range = ws.rows(false);
if (range[0][0].to_string().length() == 0)
continue;
TSTR title;
StrChangeT(ws.title(), title);
COUT << _T("sheet title : ") << title << std::endl;
//这里是分成了每个Sheet为一个记录对象
if (m_SheetMap.find(title) != m_SheetMap.end())
{
COUT << _T("error seam sheet title:") << title << std::endl;
continue;
}
SheetOperator* sheetOp = new SheetOperator();
m_SheetMap.insert(std::make_pair(title, sheetOp));
int rowIndex = 0;
//通过xlnt 获取excel数据 存入SheetOperator里
for (auto row : range)
{
++rowIndex;
sheetOp->m_ExcelData.insert(std::make_pair(rowIndex, std::map<int, TSTR>()));
int colIndex = 0;
if (row[0].to_string().length() != 0)
{
for (auto cell : row)
{
++colIndex;
TSTR tcell;
StrChangeT(cell.to_string(), tcell);
sheetOp->m_ExcelData[rowIndex].insert(std::make_pair(colIndex, tcell));
}
}
}
}
int pos = filePath.find_last_of(_T("\\"));
int pos2 = filePath.find_last_of(_T("."));
if (pos != TSTR::npos && pos2 != TSTR::npos)
{
m_ConfigName = filePath.substr(pos+1, pos2 - pos - 1);//文件名
}
else
{
m_ConfigName = _T("NotName");
}
m_OutPath = filePath.substr(0, pos);//默认的输出路径
}
//创建json文本的主函数
//根据加载的excel数据生成json字符串
void ExcelOperator::CreateConfig()
{
TSS context;
context << "{";
//遍历每个sheet
for (auto sheetIte = m_SheetMap.begin(); sheetIte != m_SheetMap.end(); ++sheetIte)
{
SheetOperator* pOp = sheetIte->second;
if (!pOp)
continue;
if (pOp->m_ExcelData.size() == 0)
{
COUT << _T("create config error not data ") << std::endl;
return;
}
auto fieldIte = pOp->m_ExcelData.find(DATAROW_FIELDTYPE);
if (fieldIte == pOp->m_ExcelData.end())
{
COUT << _T(" not find field type row") << std::endl;
return;
}
auto filedNameIte = pOp->m_ExcelData.find(DATAROW_USNAME);
if (filedNameIte == pOp->m_ExcelData.end())
{
COUT << _T(" not find field us name row") << std::endl;
return;
}
const std::map<int, TSTR> &fieldTypeMap = fieldIte->second;
const std::map<int, TSTR>& fieldNameMap = filedNameIte->second;
context << _T("\"") << sheetIte->first << _T("\":[") << std::endl;//子表名
for (int row = DATAROW_BEGIN; row <= pOp->m_ExcelData.size(); ++row)//遍历sheet 的行
{
int rowChenge = 0;
context << _T("{");
std::map<int, TSTR> &dataCol = pOp->m_ExcelData[row];
for (int col = 1; col <= dataCol.size(); ++col)//遍历sheet 的列
{
if (fieldTypeMap.at(col).length() == 0)
{
continue;
}
TSTR type = fieldTypeMap.at(col);
if (_tcscmp( type.c_str(), T_INT ) == 0)
{
pOp->WriteIntData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if(_tcscmp(type.c_str(), T_INTARR) == 0)
{
pOp->WriteIntArrData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_STRING) == 0)
{
pOp->WritStringData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_TABLE) == 0)
{
int startCol = col;
int endCol = col;
while (_tcscmp(fieldTypeMap.at(endCol).c_str(), T_TABLEEND) != 0 && endCol<=dataCol.size())
{
++endCol;
}
if (_tcscmp(fieldTypeMap.at(endCol).c_str(), T_TABLEEND) != 0)
{
COUT << _T("read table type error! not find table end") << std::endl;
return;
}
pOp->WriteTableData(context, fieldNameMap.at(startCol), startCol, endCol,row);
context << _T(",");
col = endCol;
}
else if (_tcscmp(type.c_str(), T_TABLEARR) == 0)
{
int startCol = col;
int endCol = col;
while (_tcscmp(fieldTypeMap.at(endCol).c_str(), T_TABLEEND) != 0 && endCol <= dataCol.size())
{
++endCol;
}
if (_tcscmp(fieldTypeMap.at(endCol).c_str(), T_TABLEEND) != 0)
{
COUT << _T("read tablearr type error! not find table end") << std::endl;
return;
}
col = endCol;
int startRow = row;
int endRow = row;
while (pOp->m_ExcelData.find(endRow) != pOp->m_ExcelData.end()
&& _tcscmp(pOp->m_ExcelData[endRow][endCol].c_str(), T_TABLEEND) != 0
&& endRow <= pOp->m_ExcelData.size())
{
++endRow;
}
if (_tcscmp(pOp->m_ExcelData[endRow][endCol].c_str(), T_TABLEEND) != 0)
{
COUT << _T("read tablearr type error! not find sub table end") << std::endl;
return;
}
rowChenge = endRow;
pOp->WriteTableArrData(context, fieldNameMap.at(startCol), startCol, endCol, startRow, endRow);
context << _T(",");
}
else
{
COUT << _T("not define field type:") << type << std::endl;
}
}
if (context.str().length() > 1 && context.str().substr(context.str().length() - 1, 2) == _T(","))
context.seekp(-1,std::ios::cur);//把多的,去掉
context << _T("}");
context << _T(",") << std::endl;
if (rowChenge > 0)
{
row = rowChenge;
}
}
if (context.str().length() > 2 && context.str().substr(context.str().length() - 2, 2) == _T(",\n"))
context.seekp(-2, std::ios::cur);//把多的,去掉
context << _T("]");
context << std::endl;
pOp->m_Context = context.str();
context << _T(",") << std::endl;
}
if (context.str().length() > 2 && context.str().substr(context.str().length() - 2, 2) == _T(",\n"))
context.seekp(-2, std::ios::cur);//把多的,去掉
context << _T("}");
m_OutData = context.str();
}
//下面是 json的字符串拼接函数
void SheetOperator::WriteIntData(TSS& context, const TSTR& fieldName, TSTR& data)
{
if(data.length() == 0)
context << _T("\"") << fieldName << _T("\":") << DUF_INT;
else
context << _T("\"") << fieldName << _T("\":")<< data;
}
void SheetOperator::WriteIntArrData(TSS& context, const TSTR& fieldName, TSTR& data)
{
context << _T("\"") << fieldName << _T("\":[") << data << _T("]");
}
void SheetOperator::WritStringData(TSS& context, const TSTR& fieldName, TSTR& data)
{
if (data.length() == 0)
{
context << _T("\"") << fieldName << _T("\":\"") << DUF_STRING << _T("\"");
}
else
{
context << _T("\"") << fieldName << _T("\":\"") << data<<_T("\"");
}
}
void SheetOperator::WriteTableData(TSS& context, const TSTR fieldName, int startCol, int endCol, int row)
{
auto fieldIte = m_ExcelData.find(DATAROW_FIELDTYPE);
if (fieldIte == m_ExcelData.end())
{
COUT << _T(" not find field type row") << std::endl;
return;
}
auto filedNameIte = m_ExcelData.find(DATAROW_USNAME);
if (filedNameIte == m_ExcelData.end())
{
COUT << _T(" not find field us name row") << std::endl;
return;
}
const std::map<int, TSTR>& fieldTypeMap = fieldIte->second;
const std::map<int, TSTR>& fieldNameMap = filedNameIte->second;
std::map<int, TSTR>& dataCol = m_ExcelData[row];
context << _T("\"") << fieldName << _T("\":{");
for (int col = startCol + 1; col < endCol; ++col)
{
TSTR type = fieldTypeMap.at(col);
if (_tcscmp(type.c_str(), T_INT) == 0)
{
WriteIntData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_INTARR) == 0)
{
WriteIntArrData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_STRING) == 0)
{
WritStringData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else
{
COUT << _T("read table data not define field type:") << type << std::endl;
return;
}
}
context.seekp(-1, std::ios::cur);//把多的,去掉
context << _T("}");
}
void SheetOperator::WriteTableArrData(TSS& context, const TSTR fieldName, int startCol, int endCol, int startRow, int endRow)
{
auto fieldIte = m_ExcelData.find(DATAROW_FIELDTYPE);
if (fieldIte == m_ExcelData.end())
{
COUT << _T(" not find field type row") << std::endl;
return;
}
auto filedNameIte = m_ExcelData.find(DATAROW_USNAME);
if (filedNameIte == m_ExcelData.end())
{
COUT << _T(" not find field us name row") << std::endl;
return;
}
const std::map<int, TSTR>& fieldTypeMap = fieldIte->second;
const std::map<int, TSTR>& fieldNameMap = filedNameIte->second;
context << _T("\"") << fieldName << _T("\":[");
for (int row = startRow; row <= endRow; ++row)
{
context << _T("{");
for (int col = startCol + 1; col < endCol; ++col)
{
std::map<int, TSTR>& dataCol = m_ExcelData[row];
TSTR type = fieldTypeMap.at(col);
if (_tcscmp(type.c_str(), T_INT) == 0)
{
WriteIntData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_INTARR) == 0)
{
WriteIntArrData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else if (_tcscmp(type.c_str(), T_STRING) == 0)
{
WritStringData(context, fieldNameMap.at(col), dataCol[col]);
context << _T(",");
}
else
{
COUT << _T("read table data not define field type:") << type << std::endl;
return;
}
}
context.seekp(-1, std::ios::cur);//去除多余的,
context << _T("}");
context <<_T(",");
}
context.seekp(-1, std::ios::cur);//去除多余的,
context << _T("]");
}
//获取选择的目录下所有 xlsx文件 用于展示和选择需要转换的文件
//ExcelPath.h
bool ExcelPath::LoadFileName()
{
_tfinddata_t fileInfo;
long long hFile = 0;
if (m_SelectFolderPath.length() == 0)
{
COUT << _T("not select path") << std::endl;
return false;
}
TSTR p = m_SelectFolderPath;
m_FilePathInSelect.clear();
p = p.append(_T("\\*"));
if ((hFile = _tfindfirst(p.c_str(), &fileInfo) ) != -1)
{
do {
if (fileInfo.attrib & _A_ARCH)
{
TSTR fileName(fileInfo.name);
int pos = fileName.find_last_of(_T("."));
if (pos != TSTR::npos)
{
TSTR affix = fileName.substr(pos + 1, fileName.length() - pos - 1);
//std::cout << "affix:" << affix << std::endl;
if (affix == _T("xlsx"))
{
COUT << _T("file:") << fileInfo.name << std::endl;
m_FilePathInSelect.push_back(fileInfo.name);
}
}
}
} while (_tfindnext(hFile, &fileInfo) == 0);
}
if (m_FilePathInSelect.size() == 0)
return false;
return true;
}
//MyConfig.h
//读取配置 这到没什么好看的 就cjson读取json 获取输入和输出路径
bool MyConfig::LoadConfig(TSTR path)
{
TFS ifs;
ifs.open(path, std::ios::in);
if (ifs.fail())
{
COUT << _T("read config fail:") << path<< std::endl;
return false;
}
char buff[10240] = { 0 };
ifs.read(buff, sizeof(buff)-1);
ifs.close();
//std::cout << "file content:" << buff << std::endl;
cJSON* json_file = cJSON_Parse(buff);
if (!json_file)
{
COUT << "json error" << std::endl;
return false;
}
if (json_file->type != cJSON_Object)
{
COUT << "config.json format error" << std::endl;
return false;
}
cJSON *child_sjon = json_file->child;
while (child_sjon)
{
//std::cout << child_sjon->string << std::endl;
//std::cout << child_sjon->valuestring << std::endl;
if (strcmp(child_sjon->string,"ExcelPath") == 0)
{
CharChangeT(child_sjon->valuestring, m_DefaultOpenFolder);
//m_DefaultOpenFolder = child_sjon->valuestring;
}
else if (strcmp(child_sjon->string, "OutJsonPath") == 0)
{
CharChangeT(child_sjon->valuestring, m_JsonOutFolder);
//m_JsonOutFolder = child_sjon->valuestring;
}
child_sjon = child_sjon->next;
}
return true;
}
//全局函数 (介绍头文件时忘记加了)
#ifndef HGLOBALFUNC
#define HGLOBALFUNC
#include <Windows.h>
#include <string>
#include "Define.h"
//用于 字符串转达自适应字符串的方法
inline void StrChangeT(const std::string& str, TSTR& outTChar)
{
#ifdef UNICODE
wchar_t* pWc = new wchar_t[str.length() + 1]{ 0 };
::MultiByteToWideChar(CP_UTF8, 0, str.c_str(), str.length(), pWc, str.length());
outTChar = pWc;
delete[] pWc;
#else
outTChar = str;
#endif // UNICODE
}
inline void CharChangeT(const char* str, TSTR& outTChar)
{
#ifdef UNICODE
wchar_t* pWc = new wchar_t[strlen(str) + 1]{ 0 };
::MultiByteToWideChar(CP_UTF8, 0, str, strlen(str), pWc, strlen(str));
outTChar = pWc;
delete[] pWc;
#else
outTChar = str;
#endif // UNICODE
}
inline void TCharChange(const TCHAR* tchar, std::string& str)
{
#ifdef UNICODE
char* buff = new char[_tcslen(tchar)*2 + 1]{ 0 };
::WideCharToMultiByte(CP_UTF8, 0, tchar, -1, buff, _tcslen(tchar)*2, NULL, NULL);
str = buff;
delete[] buff;
#else
str = tchar;
#endif // UNICODE
}
#endif // !HGLOBALFUNC
如果有疑问或错误的地方欢迎提出!