XML转Excel或Excel转XML|tinyxml简单使用|C++使用excel

最近做了一个小工具，可以将XML和Excel之前互转。

里面用到的XML读写库是tinyxml，在Excel2010上运行，请先确保装了Excel，而不是WPS。

代码写的比较挫，一大坨，最近忙也懒得去做优化了。

github地址：XML与Excel互转工具

目前只支持ANSI格式的Excel文件与ANSI格式的XML文件互转。由于在写的时候，里面的存储方式都是CString，默认为ANSI格式，所以哪怕里面把文字转成了UTF8，再保存到CString中也还是被认为是ANSI，这时候就会出现乱码，接下来如果有时间我会把这个问题修复。

直接讲讲代码吧。

读写xml，调用的是tinyxml的接口，核心部分代码都在这下面了，比较挫，分享给大家，希望有帮助。

/*分表和总表模式XML转Excel*/void CReadXlsDlg::OnBnClickedButton3(){// TODO:  在此添加控件通知处理程序代码// TODO:  在此添加控件通知处理程序代码CString strInputFile;CString strOutputPath;strInputFile.Empty();strOutputPath.Empty();m_editInputPath.GetWindowTextW(strInputFile);m_editOutputPath.GetWindowTextW(strOutputPath);//strInputFile = "C:\\Users\\linzhaolun.allen\\Desktop\\a.xml";//strOutputPath = "C:\\Users\\linzhaolun.allen\\Desktop\\b.xls";if (0 == strInputFile.GetLength()){MessageBox(_T("请先选择需要读取的文件"), _T("错误"), MB_OK);return;}if (0 == strOutputPath.GetLength()){MessageBox(_T("请先选择输出路径"), _T("错误"), MB_OK);return;}vector<vector<CString>> vecSheet;CSheetSize SheetSize;TiXmlDocument *doc = new TiXmlDocument(__CString2Constchar(strInputFile));if (!doc->LoadFile(TIXML_ENCODING_UTF8))  //判断XML文件是否加载成功{MessageBox(_T("无法打开该文件"), _T("错误"), MB_OK);return;}//doc->Parse(xmlParament,0,TIXML_ENCODING_UTF8);    //目前仅支持合表模式xml的解析int BoHaveTitle = false;TiXmlElement* RootLayersNestedElement = doc->RootElement();if (NULL == RootLayersNestedElement) //判断文件是否有内容{MessageBox(_T("没有root element"), _T("错误"), MB_OK);return;}//cout << doc->Value() << endl;m_stroutputName = RootLayersNestedElement->Value();       //每个xml的root保存为文件名//cout << m_stroutputName << endl;m_mapSheetList.erase(m_mapSheetList.begin(), m_mapSheetList.end()); //注意使用map前对其进行清空m_mapSheetSize.erase(m_mapSheetSize.begin(), m_mapSheetSize.end());m_vecSheetName.clear();TiXmlElement* TwoLayersNestedElement = RootLayersNestedElement->FirstChildElement();   //第二层嵌套int nSheetCount =0;for (; TwoLayersNestedElement != NULL; TwoLayersNestedElement = TwoLayersNestedElement->NextSiblingElement()) {  //每一层循环代表一个sheetCString strSheetName;CString strSpecialSheetPrefix("xxxxx");CString strList("List");//CString strConnectSheetAndAtti("yyyyy");vector<CString> sub_vecSheetName;  vector<CString>::iterator it;map<CString, CExcelSheet> sub_mapSheetList;strSheetName = TwoLayersNestedElement->Value();//获取sheet名//cout << strSheetName << endl;TiXmlElement* ThreeLayersNestedElement = TwoLayersNestedElement->FirstChildElement();  //第三层嵌套 if (TwoLayersNestedElement->FirstAttribute()){strSheetName = strSpecialSheetPrefix + strSheetName + strList;ThreeLayersNestedElement = TwoLayersNestedElement;}if (TwoLayersNestedElement->Value() != ThreeLayersNestedElement->Value()){//CString tempStr(ThreeLayersNestedElement->Value());//strSheetName = strSheetName + strConnectSheetAndAtti + tempStr;//MessageBox(_T("item和list之间命名规范错误！list名=item名+“list"), _T("错误"), MB_OK);//return;}for (; ThreeLayersNestedElement != NULL; ThreeLayersNestedElement = ThreeLayersNestedElement->NextSiblingElement()){if (ThreeLayersNestedElement->FirstChildElement() && ThreeLayersNestedElement->FirstChildElement()->FirstChildElement()){TiXmlElement* FourLayersNestedElement = ThreeLayersNestedElement->FirstChildElement();  //第四层嵌套，相当于分表的Excel总表的展开，每一个为一个新的sheet，这个sheet的第一列，包含总是包含第三层的ID号作为索引  //第五层嵌套，新的sheet里面的每一项for (; FourLayersNestedElement != NULL; FourLayersNestedElement = FourLayersNestedElement->NextSiblingElement())//每一个循环意味着创建一个新的分表{CExcelSheet asheet;asheet.BoHaveTitle = false;asheet.strSheetName = FourLayersNestedElement->Value();//获取sheet名/*在vec中已经存在该sheet名，也就是之前已经有分表出现过了，此时要做的是把获得的数据push到之前的表内*/it = find(sub_vecSheetName.begin(), sub_vecSheetName.end(), asheet.strSheetName);if (it != sub_vecSheetName.end()){asheet = sub_mapSheetList[asheet.strSheetName];}vector<CString> vecSubRow, vecSubTitle;vecSubTitle.push_back(__Constchar2CString(ConvertUtf8ToAnsi(ThreeLayersNestedElement->FirstAttribute()->Name())));vecSubRow.push_back(__Constchar2CString(ConvertUtf8ToAnsi(ThreeLayersNestedElement->FirstAttribute()->Value())));TiXmlElement* FiveLayersNestedElement = FourLayersNestedElement->FirstChildElement();for (; FiveLayersNestedElement != NULL; FiveLayersNestedElement = FiveLayersNestedElement->NextSiblingElement()){TiXmlAttribute* attributeOfFivelayers = FiveLayersNestedElement->FirstAttribute();  //获得属性 for (; attributeOfFivelayers != NULL; attributeOfFivelayers = attributeOfFivelayers->Next()){vecSubTitle.push_back(__Constchar2CString(ConvertUtf8ToAnsi(attributeOfFivelayers->Name())));vecSubRow.push_back(__Constchar2CString(ConvertUtf8ToAnsi(attributeOfFivelayers->Value())));}if (!asheet.BoHaveTitle){asheet.BoHaveTitle = true;asheet.vecSheet.push_back(vecSubTitle);vecSubTitle.clear();asheet.vecSheet.push_back(vecSubTitle);//SheetSize.row++;}asheet.vecSheet.push_back(vecSubRow);vecSubRow.clear();vecSubRow.push_back(__Constchar2CString(ConvertUtf8ToAnsi(ThreeLayersNestedElement->FirstAttribute()->Value())));it = find(sub_vecSheetName.begin(), sub_vecSheetName.end(), asheet.strSheetName);if (it == sub_vecSheetName.end()){sub_vecSheetName.push_back(asheet.strSheetName);}sub_mapSheetList[asheet.strSheetName] = asheet;}}}TiXmlAttribute* attributeOfThreelayers = ThreeLayersNestedElement->FirstAttribute();  //获得属性  vector<CString> vecRow, vecTitle;//SheetSize.row = SheetSize.col = 0;for (; attributeOfThreelayers != NULL; attributeOfThreelayers = attributeOfThreelayers->Next()){//cout << attributeOfThreelayers->Name() << ":" << attributeOfThreelayers->Value() << endl;vecTitle.push_back(__Constchar2CString(ConvertUtf8ToAnsi(attributeOfThreelayers->Name())));vecRow.push_back(__Constchar2CString(ConvertUtf8ToAnsi(attributeOfThreelayers->Value())));//SheetSize.col++;}if (!BoHaveTitle){BoHaveTitle = true;vecSheet.push_back(vecTitle);vecTitle.clear();vecSheet.push_back(vecTitle);//SheetSize.row++;}vecSheet.push_back(vecRow);//SheetSize.row++;}BoHaveTitle = false;m_mapSheetList[strSheetName] = vecSheet;//m_mapSheetSize[strSheetName] = SheetSize;m_vecSheetName.push_back(strSheetName);for (int i = 0; i < sub_vecSheetName.size(); i++){m_vecSheetName.push_back(sub_vecSheetName[i]);m_mapSheetList[sub_vecSheetName[i]] = sub_mapSheetList[sub_vecSheetName[i]].vecSheet;}nSheetCount++;vecSheet.clear();sub_mapSheetList.erase(sub_mapSheetList.begin(), sub_mapSheetList.end()); //注意使用map前对其进行清空sub_vecSheetName.clear();}    //将m_mapSheetList内容写入xls文件LPDISPATCH lpDisp = NULL;if (!m_obExcel.open(__CString2Constchar(strOutputPath))){MessageBox(_T("无法打开该文件"), _T("错误"), MB_OK);return;}for (int i = m_vecSheetName.size()-1; i >=0 ; i--){CString strSheetName = m_vecSheetName[i];if (!m_obExcel.addNewSheet(strSheetName)){MessageBox(_T("无法新建sheet"), _T("错误"), MB_OK);return;}try{m_obExcel.useSheet(strSheetName);}catch(...){MessageBox(_T("无法使用指定sheet：")+strSheetName, _T("错误"), MB_OK);return;}vector<vector<CString>> vecTempSheet;vecTempSheet = m_mapSheetList[strSheetName];for(int tempRow=0; tempRow < vecTempSheet.size(); tempRow++){vector<CString> vecTempRow;vecTempRow = vecTempSheet[tempRow];for(int tempCol=0; tempCol < vecTempRow.size(); tempCol++){CString tempCell;tempCell = vecTempRow[tempCol];try{m_obExcel.setCellString(tempRow + 1, tempCol + 1, tempCell);}catch(...){MessageBox(_T("无法使用写入cell，可能是坐标错误"), _T("错误"), MB_OK);return;}}}}m_obExcel.deleteSheet(__Constchar2CString("Sheet1"));m_obExcel.deleteSheet(__Constchar2CString("Sheet2"));m_obExcel.deleteSheet(__Constchar2CString("Sheet3"));m_obExcel.saveAsXLSFile(strOutputPath);  //此时生成的xls内为Ansi格式字符,xls再次转回XML时会被转码成为UTF-8    m_obExcel.close();}