用js脚本提取合并多个excel表格

发表于 分类于 阅读次数： 本文字数： 4.5k 阅读时长 ≈ 4 分钟

如何合并提取大量相似表格里的数据？

以前玩过vba打开文件提取，也玩过不打开直接提取，也玩过公式操作流，这次换个玩法，用JavaScript脚本来处理，下回再玩就要上python大杀器了。

注：用js脚本处理表格需要电脑安装有excel。

老样子，先描述需求：

1、多个表格，分类结构一致，但每个人填写的行数不同。

2、最终需要得到一维表来制作透视表，如何批量提取多个文件的内容？

3、执行js脚本的过程及结果。

4、一言不合就发代码，前半部分是批量读取文件的，以后都可通用，
针对每个表格提取的单元格位置 excelSheet.Name 和 excelSheet.Cells 做调整即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
/*
    oicu 2016-11-03
*/
if (WScript.Arguments.Length < 1) {
    WScript.Echo("错误：请补上Excel文件所在的目录作为参数1"
        + "\n\n用法：cscript //nologo merge-excel.js <要处理的目录>"
        + "\n\nExcel文件与脚本在同一个目录时可使用 . 作为参数");
    WScript.Quit(1);
}
WScript.Echo("\n处理完成后打开 MergeFile.txt 文件，复制内容粘贴到excel表。\n");
var fso = new ActiveXObject("Scripting.FileSystemObject");
var ExcelApp = new ActiveXObject("Excel.Application");
var dir = WScript.Arguments(0);
if (fso.FolderExists(dir)) {
    processFolder(fso.GetAbsolutePathName(dir));
}
ExcelApp = null;
fso = null;

function processFolder(path) {
    var folder = fso.getFolder(path);
    var files = folder.Files;
    var fc = new Enumerator(files);
    for (; !fc.atEnd(); fc.moveNext()) {
        viewExcelFile(fc.item().Path);
    }
    var folders = folder.SubFolders;
    fc = new Enumerator(folders);
    for (; !fc.atEnd(); fc.moveNext()) {
        processFolder(fc.item().Path);
    }
}

function viewExcelFile(excelFile) {
    try {
        if (excelFile.toLowerCase().indexOf("\\~$") >= 0) {
            return;
        }
        var temp = excelFile.toLowerCase().split(".");
        if (temp[temp.length - 1] == "xls" || temp[temp.length - 1] == "xlsx") {
            var tf;
            if (!fso.FileExists(dir + "\\MergeFile.txt")) {
                tf = fso.CreateTextFile(dir + "\\MergeFile.txt", true);
            } else {
                tf = fso.OpenTextFile(dir + "\\MergeFile.txt", 8, true);
            }
            var excelWorkBook = ExcelApp.Workbooks.Open(excelFile);
            var sheets = 0;
            var excelSheet = null;
            for (var s = 1; s <= excelWorkBook.Worksheets.Count; s++) {
                excelSheet = excelWorkBook.Worksheets(s);
                if (excelSheet.Name == "能力自评") {
                    sheets++;
                    break;
                }
            }
            if (sheets === 0) {
                tf.Close();
                excelSheet = null;
                WScript.Echo("不处理Excel文件：" + excelWorkBook.Name);
                excelWorkBook.close(0);
                excelWorkBook = null;
                ExcelApp.Application.Quit();
                return;
            }
            var blankCell = "";
            var headText = "";
            headText = [excelSheet.Cells(4, 3).Text,
                blankCell,
                blankCell,
                excelSheet.Cells(4, 5).Text,
                blankCell,
                blankCell,
                excelSheet.Cells(5, 3).Text
            ].join('\t');
            var classifyRow = 0;
            for (var row = 7; row <= excelSheet.UsedRange.Rows.count; row++) {
                if (excelSheet.Cells(row, 2).Text.substring(0, 2) == "项目") {
                    classifyRow = row;
                    row++;
                    row++;
                }
                if (excelSheet.Cells(row, 2).Text.length === 0) {
                    continue;
                }
                var content = "";
                content = [headText,
                    excelSheet.Cells(classifyRow, 3).Text,
                    excelSheet.Cells(row, 2).Text,
                    excelSheet.Cells(row, 3).Text,
                    excelSheet.Cells(row, 4).Text,
                    excelSheet.Cells(row, 5).Text
                ].join('\t');
                tf.WriteLine(content);
            }
            tf.Close();
            excelSheet = null;
            WScript.Echo("已处理Excel文件：" + excelWorkBook.Name);
            excelWorkBook.close(0);
            excelWorkBook = null;
            ExcelApp.Application.Quit();
        }
    } catch (e) {
        WScript.Echo(e.description);
        return null;
    }
}
WScript.Quit(1);