DeepSeek辅助编写的将内存数据转换为xlsx格式的c++程序

原创 于 2025-08-20 09:13:22 发布 · 455 阅读 · 5 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#c++ #开发语言 #xml #xlsx 

#include <iostream>
#include <fstream>
#include <sstream>
#include <vector>
#include <string>
#include <map>
#include <ctime>
#include <iomanip>
#include <memory>
#include <cmath>
#include <cstdint>
#include <algorithm>
#include <numeric>
#include <any>
#include <unordered_map>

#include "zipwriter.cpp"
#define addFile addTextFile


// 过滤数据结构体
struct FilterData {
    int sheetIndex = 0;
    int startColumn = 0;
    int endColumn = 0;
    int startRow = 0;
    int endRow = 0;
};

// XML特殊字符转义
std::string escapeXml(const std::string& input) {
    std::string output;
    output.reserve(input.size());
    
    for (char c : input) {
        switch (c) {
            case '&': output += "&amp;"; break;
            case '<': output += "&lt;"; break;
            case '>': output += "&gt;"; break;
            case '"': output += "&quot;"; break;
            case '\'': output += "&apos;"; break;
            default: output += c; break;
        }
    }
    
    return output;
}

// 生成Excel列字母(A, B, ..., Z, AA, AB, ...)
std::vector<std::string> generateColumnLetters() {
    std::vector<std::string> letters;
    
    // 单字母A-Z
    for (int i = 0; i < 26; ++i) {
        letters.push_back(std::string(1, 'A' + i));
    }
    
    // 双字母AA-ZZ
    for (int i = 0; i < 26; ++i) {
        for (int j = 0; j < 26; ++j) {
            letters.push_back(std::string(1, 'A' + i) + char('A' + j));
        }
    }
    
    // 三字母AAA-XYZ(限制在合理范围内)
    for (int i = 0; i < 24; ++i) {  // A-X
        for (int j = 0; j < 26; ++j) {
            for (int k = 0; k < 26; ++k) {
                if (i == 23 && j > 24) {  // 停在XY
                    break;
                }
                letters.push_back(std::string(1, 'A' + i) + char('A' + j) + char('A' + k));
            }
        }
    }
    
    return letters;
}

class XlsxWriter {

private:
    std::string filename;
    int compressionLevel;
    bool useSharedStrings;
    std::vector<std::string> letters; // 注意声明顺序
    std::unique_ptr<ZipWriter> zipWriter_;  // 使用智能指针管理

public:

    XlsxWriter(const std::string& filename, int compressionLevel = 4, bool useSharedStrings = false)
        : filename(filename), compressionLevel(compressionLevel), 
          useSharedStrings(useSharedStrings), letters(generateColumnLetters()) {

// 初始化 ZipWriter
    try {
        zipWriter_ = std::make_unique<ZipWriter>(filename, compressionLevel);
    } catch (const std::exception& e) {
        throw std::runtime_error("无法初始化 ZipWriter: " + std::string(e.what()));
    }
    
    // 初始化其他成员...
}
    
    // 添加新工作表
    void addSheet(const std::string& sheetName, bool hidden = false) {
        worksheetData.emplace_back(sheetName, std::vector<std::vector<std::any>>(), hidden);
        sheetCount++;
    }

    // 写入数据到当前工作表
    void writeSheet(const std::vector<std::vector<std::any>>& data) {
        if (worksheetData.empty()) {
            addSheet("Sheet1");
        }
        
        auto& currentSheet = worksheetData.back();
        currentSheet.data = data;
        
        std::ostringstream sheetContent;
        writeWorksheetXml(sheetContent, data, sheetCount - 1);
        
        std::string sheetPath = "xl/worksheets/sheet" + std::to_string(sheetCount) + ".xml";
        zipWriter_->addFile(sheetPath, sheetContent.str());
        //std::cout << sheetContent.str();
    }
    
    // 保存所有内容到ZIP文件

    void save() {
    if (!zipWriter_) {
        throw std::runtime_error("ZipWriter 未初始化");
    }

    try {
        // 写入 [Content_Types].xml
        zipWriter_->addTextFile("[Content_Types].xml", createContentTypes());
        
        // 写入 _rels/.rels
        zipWriter_->addTextFile("_rels/.rels", createRootRels());
        
        // 写入 xl/workbook.xml
        zipWriter_->addTextFile("xl/workbook.xml", createWorkbookXml());
        
        // 写入 xl/styles.xml
        zipWriter_->addTextFile("xl/styles.xml", createStylesXml());
        
        // 写入 xl/_rels/workbook.xml.rels
        zipWriter_->addTextFile("xl/_rels/workbook.xml.rels", createWorkbookRels());
        
        // 如果有共享字符串则写入
        if (useSharedStrings && !sharedStrings.empty()) {
            zipWriter_->addTextFile("xl/sharedStrings.xml", createSharedStringsXml());
        }
        
        // 写入各工作表
        for (int i = 0; i < sheetCount; ++i) {
            std::string sheetPath = "xl/worksheets/sheet" + std::to_string(i + 1) + ".xml";
            std::ostringstream sheetContent;
            writeWorksheetXml(sheetContent, worksheetData[i].data, i);
            zipWriter_->addTextFile(sheetPath, sheetContent.str());
        }
        
    } catch (const std::exception& e) {
        throw std::runtime_error("保存 XLSX 文件失败: " + std::string(e.what()));
    }   
}

    void close() {
       if (zipWriter_) {
        try {
            zipWriter_->close();
        } catch (...) {
            // 忽略关闭时的异常
        }
        zipWriter_.reset();
       }
   }
//在析构函数中确保资源释放

~XlsxWriter() {
    try {
        close();
    } catch (...) {
        // 确保析构不抛出异常
    }
}
private:
    // 创建[Content_Types].xml文件
    std::string createContentTypes() {
        std::ostringstream oss;
        oss << R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>)";
        
        for (int i = 0; i < sheetCount; ++i) {
            oss << R"(<Override PartName="/xl/worksheets/sheet)" << (i + 1) 
                << R"(.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>)";
        }
        
        oss << R"(<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>)";
        
        if (useSharedStrings && !sharedStrings.empty()) {
            oss << R"(<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>)";
        }
        
        oss << "</Types>";
        return oss.str();
    }
    
    // 创建_rels/.rels文件
    std::string createRootRels() {
        return R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>)";
    }
    
    // 创建xl/_rels/workbook.xml.rels文件
    std::string createWorkbookRels() {
        std::ostringstream oss;
        oss << R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">)";
        
        for (int i = 0; i < sheetCount; ++i) {
            oss << R"(<Relationship Id="rId)" << (i + 1) 
                << R"(" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet)" 
                << (i + 1) << R"(.xml"/>)";
        }
        
        int styleRid = sheetCount + 1;
        oss << R"(<Relationship Id="rId)" << styleRid 
            << R"(" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>)";
        
        if (useSharedStrings && !sharedStrings.empty()) {
            int sharedStringsRid = sheetCount + 2;
            oss << R"(<Relationship Id="rId)" << sharedStringsRid 
                << R"(" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>)";
        }
        
        oss << "</Relationships>";
        return oss.str();
    }
    
    // 创建xl/workbook.xml文件
    std::string createWorkbookXml() {
        std::ostringstream oss;
        oss << R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>
<workbookPr defaultThemeVersion="124226"/>
<bookViews><workbookView xWindow="240" yWindow="15" windowWidth="16095" windowHeight="9660"/></bookViews>
<sheets>)";
        
        for (size_t i = 0; i < worksheetData.size(); ++i) {
            const auto& sheet = worksheetData[i];
            std::string hiddenAttr = sheet.hidden ? " state=\"hidden\"" : "";
            oss << R"(<sheet name=")" << escapeXml(sheet.name) << R"(" sheetId=")" 
                << (i + 1) << R"(")" << hiddenAttr << R"( r:id="rId)" << (i + 1) << R"("/>)";
        }
        
        oss << "</sheets>";
        
        // 添加自动筛选定义名称(如果需要)
        if (!filteredDataList.empty()) {
            oss << "<definedNames>";
            for (const auto& filterData : filteredDataList) {
                const std::string& sheetName = worksheetData[filterData.sheetIndex].name;
                const std::string& startCol = letters[filterData.startColumn];
                const std::string& endCol = letters[filterData.endColumn];
                std::string rangeRef = sheetName + "!$" + startCol + "$" + 
                                      std::to_string(filterData.startRow + 1) + ":$" + 
                                      endCol + "$" + std::to_string(filterData.endRow + 1);
                oss << R"(<definedName name="_xlnm._FilterDatabase" localSheetId=")" 
                    << filterData.sheetIndex << R"(" hidden="1">)" << rangeRef << R"(</definedName>)";
            }
            oss << "</definedNames>";
        }
        
        oss << R"(<calcPr calcId="124519" fullCalcOnLoad="1"/>
</workbook>)";
        
        return oss.str();
    }
    
    // 创建xl/styles.xml文件
    std::string createStylesXml() {
        return R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">
<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font>
<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/><b/></font>
</fonts>
<fills count="2">
<fill><patternFill patternType="none"/></fill>
<fill><patternFill patternType="gray125"/></fill>
</fills>
<borders count="1">
<border><left/><right/><top/><bottom/><diagonal/></border>
</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
</cellStyleXfs>
<cellXfs count="4">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="22" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/>
</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>
</styleSheet>)";
    }
    
    // 创建共享字符串XML文件
    std::string createSharedStringsXml() {
        std::ostringstream oss;
        oss << R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count=")" 
            << sstAllCount << R"(" uniqueCount=")" << sstUniqueCount << R"(">)";
        
        for (const auto& s : sharedStrings) {
            std::string escaped = escapeXml(s);
            if (!s.empty() && (s[0] == ' ' || s[0] == '\t')) {
                oss << R"(<si><t xml:space="preserve">)" << escaped << R"(</t></si>)";
            } else {
                oss << R"(<si><t>)" << escaped << R"(</t></si>)";
            }
        }
        
        oss << "</sst>";
        return oss.str();
    }
    
    // 计算列宽
    std::vector<double> calculateColumnWidths(const std::vector<std::vector<std::any>>& data, int maxCols) {
        std::vector<double> colWidths(maxCols, 8.43);  // 默认宽度
        

        // 分析前100行进行宽度估算
        int rowsAnalyzed = 0;
        for (const auto& row : data) {
            if (rowsAnalyzed >= 100) break;
            
            for (int colIdx = 0; colIdx < std::min(static_cast<int>(row.size()), maxCols); ++colIdx) {
                const auto& cell = row[colIdx];
                
                if (cell.has_value()) {
                    double width = 8.43;
                    
                    if (cell.type() == typeid(std::string)) {
                        const std::string& str = std::any_cast<std::string>(cell);
                        width = std::max(8.43, str.length() * 1.25 + 2);
                    } else if (cell.type() == typeid(int) || cell.type() == typeid(double)) {
                        width = 12.0;
                    } else if (cell.type() == typeid(bool)) {
                        width = 5.0;
                    }
                    
                    colWidths[colIdx] = std::max(colWidths[colIdx], std::min(width, 255.0));
                }
            }
            
            rowsAnalyzed++;
        }
        
        return colWidths;
    }
    
    // 写入工作表XML
    void writeWorksheetXml(std::ostream& os, const std::vector<std::vector<std::any>>& data, int worksheetIndex) {
        // 开始工作表
        os << R"(<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">)";
        
        int maxCols = data.empty() ? 1 : std::max_element(data.begin(), data.end(), 
            [](const auto& a, const auto& b) { return a.size() < b.size(); })->size();
        int numRows = data.size();
        
        // 添加维度
        if (numRows > 0 && maxCols > 0) {
            std::string endCell = letters[maxCols - 1] + std::to_string(numRows);
            os << R"(<dimension ref="A1:)" << endCell << R"("/>)";
        }
        
        // 添加工作表视图(冻结首行)
        if (worksheetIndex == 0) {
            os << R"(<sheetViews><sheetView tabSelected="1" workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/><selection pane="bottomLeft" activeCell="A2" sqref="A2"/></sheetView></sheetViews>)";
        } else {
            os << R"(<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/><selection pane="bottomLeft" activeCell="A2" sqref="A2"/></sheetView></sheetViews>)";
        }
        
        // 计算列宽
        auto colWidths = calculateColumnWidths(data, maxCols);
        
        // 写入列定义
        if (std::any_of(colWidths.begin(), colWidths.end(), [](double w) { return w != 8.43; })) {
            os << "<cols>";
            for (int i = 0; i < colWidths.size(); ++i) {
                if (colWidths[i] != 8.43) {
                    os << R"(<col min=")" << (i + 1) << R"(" max=")" << (i + 1) 
                       << R"(" width=")" << std::fixed << std::setprecision(2) << colWidths[i] 
                       << R"(" bestFit="1" customWidth="1"/>)";
                }
            }
            os << "</cols>";
        }
        
        // 写入工作表数据
        os << "<sheetData>";
        
        int lastRowIdx = 0;
        for (int rowIdx = 0; rowIdx < data.size(); ++rowIdx) {
            lastRowIdx = rowIdx;
            const auto& row = data[rowIdx];
            //os << "<row r=\""<<rowIdx+1 <<"\">";
            os << "<row>";//不加行号也可
            for (int colIdx = 0; colIdx < row.size(); ++colIdx) {
                const auto& cell = row[colIdx];
                
                if (!cell.has_value()) {
                    os << "<c/>"; //空标签
                    continue;
                }
                
                if (cell.type() == typeid(const char*)) {
                    writeStringCell(os, std::any_cast<const char*>(cell), rowIdx == 0);
                } else if (cell.type() == typeid(bool)) {
                    bool val = std::any_cast<bool>(cell);
                    os << R"(<c t="b"><v>)" << (val ? "1" : "0") << R"(</v></c>)";
                } else if (cell.type() == typeid(int) || cell.type() == typeid(double)) {
                    double val = cell.type() == typeid(int) ? 
                                static_cast<double>(std::any_cast<int>(cell)) : 
                                std::any_cast<double>(cell);
                    
                    // 处理特殊浮点值
                    if (std::isnan(val)) {
                        writeStringCell(os, "NaN", rowIdx == 0);
                    } else if (val == std::numeric_limits<double>::infinity()) {
                        writeStringCell(os, "∞", rowIdx == 0);
                    } else if (val == -std::numeric_limits<double>::infinity()) {
                        writeStringCell(os, "-∞", rowIdx == 0);
                    } else {
                        os << R"(<c><v>)" << val << R"(</v></c>)";
                    }
                } else {
                    writeStringCell(os, "Unsupported type", rowIdx == 0);
                    //writeStringCell(os, std::string(cell), rowIdx == 0); //未知类型当做字符串
                }
            }
            
            os << "</row>";
        }
        
        os << "</sheetData>";
        
        // 如果有数据则添加自动筛选
        if (lastRowIdx > 0) {
            std::string startCol = letters[0];
            std::string endCol = letters[maxCols - 1];
            os << R"(<autoFilter ref=")" << startCol << "1:" << endCol << (lastRowIdx + 1) << R"("/>)";
            
            // 存储筛选数据
            filteredDataList.push_back(FilterData{
                worksheetIndex,
                0,
                maxCols - 1,
                0,
                lastRowIdx
            });
        }
        
        os << R"(<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>)";
        os << "</worksheet>";
    }
    
    // 写入字符串单元格
    void writeStringCell(std::ostream& os, const std::string& cellValue, bool isHeader = false) {
        std::string escapedValue = escapeXml(cellValue);
        
        if (useSharedStrings) {
            // 使用共享字符串
            sstAllCount++;
            auto it = sharedStringsDict.find(cellValue);
            if (it == sharedStringsDict.end()) {
                int stringIndex = sstUniqueCount;
                sharedStringsDict[cellValue] = stringIndex;
                sharedStrings.push_back(cellValue);
                sstUniqueCount++;
            }
            
            // 样式3是粗体标题样式，样式0是普通样式
            std::string styleRef = isHeader ? " s=\"3\"" : "";
            os << R"(<c t="s")" << styleRef << R"(><v>)" << sharedStringsDict[cellValue] << R"(</v></c>)";
        } else {
            // 内联字符串
            std::string styleRef = isHeader ? " s=\"3\"" : "";
            if (!cellValue.empty() && (cellValue[0] == ' ' || cellValue[0] == '\t')) {
                os << R"(<c t="inlineStr")" << styleRef << R"(><is><t xml:space="preserve">)" 
                   << escapedValue << R"(</t></is></c>)";
            } else {
                os << R"(<c t="inlineStr")" << styleRef << R"(><is><t>)" 
                   << escapedValue << R"(</t></is></c>)";
            }
        }
    }
    
struct Worksheet {
    std::string name;
    std::vector<std::vector<std::any>> data;
    bool hidden;
    
    // 默认构造函数
    Worksheet() = default;
    
    // 带参数的构造函数
    Worksheet(std::string name_, std::vector<std::vector<std::any>> data_, bool hidden_)
        : name(std::move(name_)), data(std::move(data_)), hidden(hidden_) {}
    
    // 移动构造函数
    Worksheet(Worksheet&&) = default;
    
    // 拷贝构造函数
    Worksheet(const Worksheet&) = default;
};
    

    std::vector<Worksheet> worksheetData;
    std::vector<std::string> sharedStrings;
    std::map<std::string, int> sharedStringsDict;
    int sheetCount = 0;
    int sstUniqueCount = 0;
    int sstAllCount = 0;
    std::vector<FilterData> filteredDataList;
    
};

// 示例用法
int main() {
    XlsxWriter writer("example.xlsx");
    
    // 添加工作表
    writer.addSheet("Sheet1");
    
    // 准备数据
    std::vector<std::vector<std::any>> data = {
        {"Name", "Age", "Score", "Passed"},
        {"Alice", 25, 95.5, true},
        {"Bob", 30, 87.2, true},
        {"Charlie", 22, 42.1, false}
    };
    
    // 写入数据
    writer.writeSheet(data);
    
    // 保存文件
    writer.save();
    
    return 0;
}

这个程序调试过程中有几个陷阱：
1.std::any类型中的字符串字面量是const char *类型（其type().name() 为PKc），一开始用cell.type() == typeid(std::string)去判断，永假。
2.XlsxWriter类中的ZipWriter对象需要在XlsxWriter类的构造函数中初始化。
3.Worksheet结构需要有三个参数的构造函数。

上面代码中#include "zipwriter.cpp"的zipwriter.cpp引用上文的代码，需要把其中的main函数改名或删除。
本程序支持使用或不使用共享字符串，使用useSharedStrings参数控制。

l1t
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值