深入解析：美团龙猫利用expat库实现的保存xml指定范围数据到csv的C程序

用自己代码逐个字符解析的速度较慢，尝试了libxml2也比较慢，它需要一次性读入内存，而expat库支持流式读取。就让龙猫写了一个程序，毕竟是久经考验的库，程序很快就调试通过了。要不是我一开始没信心，让他先输出10行试试，还能少走很多弯路。

#include <stdio.h>
  #include <stdlib.h>
    #include <string.h>
      #include <expat.h>
        #define MAX_CELL_CONTENT 256
        // 解析范围
        typedef struct {
        int start_row;
        int end_row;
        char start_col;
        char end_col;
        } ParseRange;
        // 全局结果存储
        typedef struct {
        int row;
        char col;
        char value[MAX_CELL_CONTENT];
        } CellResult;
        // 范围检查
        int is_cell_in_range(int row, char col, ParseRange range) {
        return (row >= range.start_row && row <= range.end_row &&
        col >= range.start_col && col <= range.end_col);
        }
        // 全局结果存储（动态数组）
        typedef struct {
        CellResult *data;
        // 动态数组
        int count;
        // 当前数量
        int capacity;
        // 当前容量
        } DynamicResults;
        DynamicResults all_results = {
        0
        };
        // 全局变量
        // 初始化动态数组
        void init_results() {
        all_results.capacity = 1024;
        // 初始容量
        all_results.data = malloc(all_results.capacity * sizeof(CellResult));
        all_results.count = 0;
        }
        // 扩容动态数组
        void ensure_capacity(int needed) {
        if (all_results.count + needed >= all_results.capacity) {
        all_results.capacity *= 2;
        // 翻倍扩容
        all_results.data = realloc(all_results.data, all_results.capacity * sizeof(CellResult));
        }
        }
        // 添加结果（无限制版本）
        void add_cell_result(int row, char col, const char *value, int is_empty) {
        ensure_capacity(1);
        // 确保有空间
        all_results.data[all_results.count].row = row;
        all_results.data[all_results.count].col = col;
        strncpy(all_results.data[all_results.count].value, value, MAX_CELL_CONTENT - 1);
        all_results.count++;
        }
        // 释放内存
        void free_results() {
        free(all_results.data);
        all_results.data = NULL;
        all_results.count = all_results.capacity = 0;
        }
        // 解析Excel范围 (如"A1:Z100")
        int parse_excel_range(const char *range_str, ParseRange *range) {
        if (sscanf(range_str, "%c%d:%c%d",
        &range->start_col, &range->start_row,
        &range->end_col, &range->end_row) != 4) {
        return -1;
        }
        if (range->start_col > range->end_col) return -1;
        if (range->start_row > range->end_row) return -1;
        return 0;
        }
        // 解析器状态
        typedef struct {
        ParseRange range;
        int in_row;
        int current_row;
        char current_col;
        int value_started;
        char temp_value[MAX_CELL_CONTENT];
        int value_len;
        int rows_parsed;
        // 已解析行数
        } ParserState;
        // 开始标签回调
        void XMLCALL start_element(void *user_data, const XML_Char *name, const XML_Char **attrs) {
        ParserState *state = (ParserState*)user_data;
        if (strcmp(name, "row") == 0) {
        state->in_row = 1;
        state->current_row = -1;
        // 解析行号属性
        for (int i = 0; attrs[i]; i += 2) {
        if (strcmp(attrs[i], "r") == 0) {
        state->current_row = atoi(attrs[i+1]);
        state->rows_parsed++;
        break;
        }
        }
        if(1==0)printf("解析行 %d\n", state->current_row);
        }
        else if (strcmp(name, "c") == 0 && state->in_row) {
        // 解析列属性
        for (int i = 0; attrs[i]; i += 2) {
        if (strcmp(attrs[i], "r") == 0) {
        state->current_col = attrs[i+1][0];
        break;
        }
        }
        }
        else if (strcmp(name, "v") == 0 || strcmp(name, "t") == 0) {
        if (state->current_row >= state->range.start_row &&
        state->current_row <= state->range.end_row) {
          state->value_started = 1;
          state->value_len = 0;
          state->temp_value[0] = '\0';
          }
          }
          }
          // 文本内容回调
          void XMLCALL character_data(void *user_data, const XML_Char *s, int len) {
          ParserState *state = (ParserState*)user_data;
          if (state->value_started && state->value_len + len < MAX_CELL_CONTENT - 1) {
          memcpy(state->temp_value + state->value_len, s, len);
          state->value_len += len;
          state->temp_value[state->value_len] = '\0';
          }
          }
          // 结束标签回调
          void XMLCALL end_element(void *user_data, const XML_Char *name) {
          ParserState *state = (ParserState*)user_data;
          if (strcmp(name, "row") == 0) {
          state->in_row = 0;
          }
          else if ((strcmp(name, "v") == 0 || strcmp(name, "t") == 0) && state->value_started) {
          if (is_cell_in_range(state->current_row, state->current_col, state->range)) {
          if(1==0)printf(" 单元格 %c%d: '%s'\n", state->current_col, state->current_row, state->temp_value);
          add_cell_result(state->current_row, state->current_col, state->temp_value, 0);
          }
          state->value_started = 0;
          }
          }
          // 主解析函数
          int parse_sheet_xml(const char *filename, ParseRange range) {
          XML_Parser parser = XML_ParserCreate(NULL);
          ParserState state = {
          0
          };
          state.range = range;
          XML_SetUserData(parser, &state);
          XML_SetElementHandler(parser, start_element, end_element);
          XML_SetCharacterDataHandler(parser, character_data);
          FILE *file = fopen(filename, "rb");
          if (!file) {
          printf("错误: 无法打开文件 %s\n", filename);
          XML_ParserFree(parser);
          return -1;
          }
          char buffer[8192];
          int done;
          do {
          size_t len = fread(buffer, 1, sizeof(buffer), file);
          done = (len <
          sizeof(buffer));
          if (XML_Parse(parser, buffer, len, done) == XML_STATUS_ERROR) {
          printf("解析错误: %s (行 %d)\n",
          XML_ErrorString(XML_GetErrorCode(parser)),
          XML_GetCurrentLineNumber(parser));
          break;
          }
          } while (!done);
          fclose(file);
          XML_ParserFree(parser);
          return 0;
          }
          /**
          * 输出CSV文件
          * @param filename 输出文件名
          */
          int save_results_to_csv(const char *filename) {
          FILE *csv = fopen(filename, "w");
          if (!csv) {
          printf("错误: 无法创建CSV文件 %s\n", filename);
          return -1;
          }
          // 计算列范围
          char min_col = all_results.data[0].col;
          char max_col = all_results.data[0].col;
          for (int i = 1; i < all_results.count; i++) {
          if (all_results.data[i].col < min_col) min_col = all_results.data[i].col;
          if (all_results.data[i].col > max_col) max_col = all_results.data[i].col;
          }
          // 输出标题
          fprintf(csv, "Row");
          for (char col = min_col; col <= max_col; col++) {
          fprintf(csv, ",%c", col);
          }
          fprintf(csv, "\n");
          // 数据行
          int current_row = all_results.data[0].row;
          int row_start_idx = 0;
          for (int i = 0; i < all_results.count; i++) {
          if (all_results.data[i].row != current_row) {
          // 输出当前行
          fprintf(csv, "%d", current_row);
          for (char col = min_col; col <= max_col; col++) {
          int found = 0;
          for (int j = row_start_idx; j < i; j++) {
          if (all_results.data[j].col == col) {
          fprintf(csv, ",%s", all_results.data[j].value);
          found = 1;
          break;
          }
          }
          if (!found) fprintf(csv, ",");
          }
          fprintf(csv, "\n");
          // 下一行
          current_row = all_results.data[i].row;
          row_start_idx = i;
          }
          }
          fclose(csv);
          printf("CSV已保存到 %s\n", filename);
          return 0;
          }
          // 主函数
          int main(int argc, char *argv[]) {
          if (argc != 3) {
          printf("用法: %s <xml文件> <范围(A1:Z100)>\n", argv[0]);
            return 1;
            }
            ParseRange range;
            if (parse_excel_range(argv[2], &range) != 0) {
            printf("错误: 无效范围格式，应为 A1:Z100\n");
            return 1;
            }
            printf("解析范围: %c%d:%c%d\n", range.start_col, range.start_row, range.end_col, range.end_row);
            init_results();
            if (parse_sheet_xml(argv[1], range) == 0) {
            // 生成CSV文件名（替换.xml为.csv）
            char csv_filename[256];
            strncpy(csv_filename, argv[1], sizeof(csv_filename) - 1);
            char *ext = strrchr(csv_filename, '.');
            if (ext) strcpy(ext, ".csv");
            else strncat(csv_filename, ".csv", sizeof(csv_filename) - strlen(csv_filename) - 1);
            // 输出CSV
            save_results_to_csv(csv_filename);
            }
            free_results();
            return 0;
            }

编译执行

gcc -o expatxml3 expatxml3.c -lexpat -O3
time ./expatxml3 lineitem/xl/worksheets/sheet1.xml A1:Z10000
解析范围: A1:Z10000
CSV已保存到 lineitem/xl/worksheets/sheet1.csv
real 0m6.508s
user 0m2.132s
sys 0m0.392s
time ./expatxml3 lineitem/xl/worksheets/sheet1.xml A100001:Z110000
解析范围: A100001:Z110000
CSV已保存到 lineitem/xl/worksheets/sheet1.csv
real 0m6.534s
user 0m2.111s
sys 0m0.431s
time ./expatxml3 lineitem/xl/worksheets/sheet1.xml A1:Z1000000
解析范围: A1:Z1000000
CSV已保存到 lineitem/xl/worksheets/sheet1.csv
real 0m10.207s
user 0m3.046s
sys 0m1.795s
time ./expatxml3 lineitem/xl/worksheets/sheet1.xml A300000:Z660000
解析范围: A300000:Z660000
CSV已保存到 lineitem/xl/worksheets/sheet1.csv
real 0m9.378s
user 0m2.574s
sys 0m1.030s

针对60万行16列，300MB xml, 这个时间还不错，但是没有考虑sharedstrings.xml，否则会慢一些。