Bridge xlsxファイル作成

Bridgeでxlsxファイルを作成する（win）メモ

// I am not responsible for any problems caused by using this script.
#target "Bridge"
// cell data
var myCell = {
    A: { 1: "test", 2: "test2" },
    B: { 1: 1, 2: 10}
}
{ // XML creation for Excel
    var mySharedStringsXML = new XML("""<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
</sst>""");
    var mySheetDataXML = new XML("""<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <sheetData></sheetData>
</worksheet>""");
    for (var myProperty in myCell) {
        for (var myProperty2 in myCell[myProperty]) {
            // row element
            if (mySheetDataXML.*::sheetData.*::row.(@r == myProperty2).length() == 0) {
                mySheetDataXML.*::sheetData.insertChildBefore(null, new XML("<row r='" + myProperty2 + "' />"));
            }
            { // c element
                if (isNaN(myCell[myProperty][myProperty2])) {
                    var myT = "s";
                    var myVElementValue = mySharedStringsXML.*::si.length();
                } else {
                    var myT = "n";
                    var myVElementValue = myCell[myProperty][myProperty2];
                }
                mySheetDataXML.*::sheetData.*::row.(@r == myProperty2)
                .insertChildBefore(null, new XML("<c r='" + myProperty + myProperty2 + "' t='" + myT + "'><v>" + myVElementValue + "</v></c>"));
                // sharedStrings
                mySharedStringsXML.insertChildBefore(null, new XML("<si><t>" + myCell[myProperty][myProperty2] + "</t></si>"));
            }
        }
    }
    var myTemporaryFolder = new Folder(Folder.desktop + "/ExcelTemporaryFolder");
    myTemporaryFolder.create();
    var myFile = new File(myTemporaryFolder.fsName + "/[Content_Types].xml");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln("""<?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"/>
    <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
    <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
    <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
</Types>""");
        myFile.close();
    }
    (new Folder(myTemporaryFolder.fsName + "/_rels")).create();
    myFile = new File(myTemporaryFolder.fsName + "/_rels/.rels");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln("""<?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>""");
        myFile.close();
    }
    (new Folder(myTemporaryFolder.fsName + "/xl")).create();
    myFile = new File(myTemporaryFolder.fsName + "/xl/sharedStrings.xml");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + mySharedStringsXML.toXMLString());
        myFile.close();
    }
    myFile = new File(myTemporaryFolder.fsName + "/xl/workbook.xml");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln("""<?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">
    <sheets>
        <sheet name="mySheet" sheetId="1" r:id="rId1"/>
    </sheets>
</workbook>""");
        myFile.close();
    }
    (new Folder(myTemporaryFolder.fsName + "/xl/_rels")).create();
    myFile = new File(myTemporaryFolder.fsName + "/xl/_rels/workbook.xml.rels");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln("""<?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/worksheet" Target="worksheets/sheet1.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
</Relationships>""");
        myFile.close();
    }

    (new Folder(myTemporaryFolder.fsName + "/xl/worksheets")).create();
    myFile = new File(myTemporaryFolder.fsName + "/xl/worksheets/sheet1.xml");
    if  (myFile) {
        myFile.open("w");
        myFile.encoding = "utf-8";
        myFile.writeln('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + mySheetDataXML);
        myFile.close();
    }
}
{ // zip compression
    { // Invoke PowerShell from Command Prompt
        app.system('powershell -Command "Compress-Archive'
            + ' -Path ' + (new File(Folder.desktop)).fsName + '/ExcelTemporaryFolder/*'
            + ' -DestinationPath ' + (new File(Folder.desktop)).fsName + '/myExcelTemporaryFile.zip'
            + ' -Force"');
    }
    for (var i = 0; i <= 10; i++) {
        if ((new File(Folder.desktop + "/myExcelTemporaryFile.zip")).exists) { break; }
        $.sleep(1000);
    }
}
{ // rename
    if ((new File(Folder.desktop + "/myExcel.xlsx")).exists) {
        (new File(Folder.desktop + "/myExcel.xlsx")).remove();
    }
    if ((new File(Folder.desktop + "/myExcel.xlsx")).exists) {
         (new File(Folder.desktop + "/myExcelTemporaryFile.zip")).remove();
         alert("It seems that the xlsx file is open.");
    } else {
        (new File(Folder.desktop + "/myExcelTemporaryFile.zip")).rename("myExcel.xlsx");
        alert("An xlsx file and a ExcelTemporaryFolder were created on the desktop.");
    }
}
/*
{ // delete TemporaryFolder
    (myFunction = function() {
        var myTargetFileAndFolderArray = arguments[0].getFiles();
        for (var i = myTargetFileAndFolderArray.length - 1; i >= 0; i--) {
            if (myTargetFileAndFolderArray[i].constructor.name == "File") {
                myTargetFileAndFolderArray[i].remove();
            } else if (myTargetFileAndFolderArray[i].constructor.name == "Folder") {
                myFunction(myTargetFileAndFolderArray[i]);
            }
        }
        arguments[0].remove();
    })(myTemporaryFolder);
}
*/