Creating an Excel spreadsheet with multiple worksheets

Photoshop Script Snippets - Note: Full Scripts go in the Photoshop Scripts Forum

Moderators: Tom, Kukurykus

wade

Creating an Excel spreadsheet with multiple worksheets

Post by wade »

This is pretty valuable for reporting, of course it requires Microsoft Excel to be installed

Code: Select all/* ################################################
   description: script that shows an approach to creating an Excel spreadsheet with multiple worksheets
   by: Wade Harrell
   date: 20051206
################################################### */

// enable double clicking from the Macintosh Finder or the Windows Explorer
#target photoshop

// in case we double clicked the file
app.bringToFront();

// Ask user for output folder; taken directly from bb/viewtopic.php?t=32
var outputFolder = Folder.selectDialog("Select a destination folder for Excel font list file"); //
var outputFile = outputFolder.fsName + "/" + getDateTimeStr() + "FontList.xls";

var outputReport = new File(outputFile);
initializeOutputReport(outputReport);
processFonts();

//close out XML
outputReport.open("e");
outputReport.seek(0,2);
outputReport.writeln("</Workbook>");
outputReport.close();
// open Excel
outputReport.execute();

outputReport = null;

// based on bb/viewtopic.php?t=32, it is not really valuable to format this data this way, but good for the purposes of this demonstation
function processFonts(){
   function _aStyle(){
      this.name = null;
      this.fonts = new Array();
   }
   var stylesAry = new Array();
    for(var iAF=0; iAF < app.fonts.length; iAF++){
        var curObj = app.fonts[iAF];
        var isNewStyle = true;
        for(var iSA=0; iSA < stylesAry.length; iSA++){
           if(stylesAry[iSA].name.toLowerCase() == trim(curObj.style.toLowerCase())){
              stylesAry[iSA].fonts[stylesAry[iSA].fonts.length] = curObj;
              isNewStyle = false;
           }
        }
        iSA = null;
        if(isNewStyle){
           var newIndex = stylesAry.length;
           stylesAry[newIndex] = new _aStyle();
           stylesAry[newIndex].name = trim(curObj.style);
           stylesAry[newIndex].fonts[0] = curObj;
           newIndex = null;
        }
        isNewStyle = null;
    }
    iAF = null;
    for(var itSA=0; itSA < stylesAry.length; itSA++){
      var currStyle = stylesAry[itSA];
      var currData = "";
      currData += getColumnsHeaderString("name","family","postScriptName");
      currData += "\n";
      for(var iSAF=0; iSAF < currStyle.fonts.length; iSAF++){
         var currFont = currStyle.fonts[iSAF];
         currData += "\n";
         currData += "<Row>";
         currData += generateCell(currFont.name,"string");
         currData += generateCell(currFont.family,"string");
         currData += generateCell(currFont.postScriptName,"string");
         currData += "</Row>";
         currFont = null;
      }
      addWorksheet(currStyle.name,currData);
      currData = null;
      currStyle = null;
    }
}

function addWorksheet(sheetName,sheetData){  // generic function for adding a worksheet
   var xmlWorksheetStart = new String('<Worksheet ss:Name="[title]">');
      xmlWorksheetStart += '<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">';
      xmlWorksheetStart += '<FreezePanes/>';
      xmlWorksheetStart += '<FrozenNoSplit/>';
      xmlWorksheetStart += '<SplitHorizontal>1</SplitHorizontal>';  // freeze header column
      xmlWorksheetStart += '<TopRowBottomPane>1</TopRowBottomPane>';
      xmlWorksheetStart += '<ActivePane>2</ActivePane>';
      xmlWorksheetStart += '</WorksheetOptions>';
      xmlWorksheetStart += '<Table>';
   var xmlWorksheetEnd = new String('</Table></Worksheet>');
    outputReport.open("e");
    outputReport.seek(0,2);
    outputReport.writeln(xmlWorksheetStart.replace("[title]",sheetName));     
    outputReport.writeln(sheetData);
    outputReport.writeln(xmlWorksheetEnd);
    outputReport.close();
    xmlWorksheetStart = null;
   xmlWorksheetEnd = null;
}

function getColumnsHeaderString(){  // Easy way to create the column headings
   var chStr = '<Row ss:StyleID="boldText">';
   for(iArgs=0;iArgs<arguments.length;iArgs++){
      chStr += generateCell(arguments[iArgs],"string");
   }
   chStr += '</Row>';
   return chStr;
}

function initializeOutputReport(theReportFile){  // Prep the file
   if(theReportFile.exists){theReportFile.remove();}
   theReportFile.open("w");
   theReportFile.writeln('<?xml version="1.0"?>');
   var startStr = '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
   var stylesStr = '<Styles>'; // The styles are optional.  I seperated them out so that it is easier to edit them.
       stylesStr += '<Style ss:ID="boldText"><Font x:Family="Swiss" ss:Bold="1"/></Style>'; // Used to bold the column headings
       stylesStr += '<Style ss:ID="dateFormat"><NumberFormat ss:Format="[$-409]m/d/yy\ h:mm\ AM/PM;@"/></Style>';  // Used to format dates
       stylesStr += '</Styles>';
   theReportFile.writeln(startStr+stylesStr);
   theReportFile.close();
}

function generateCell(cellData,cellType){ // This example only uses String data, but other types are supported
   switch(cellType.toLowerCase()){
      case "string" :
         returnCell = new String('<Cell><Data ss:Type="String">[data]</Data></Cell>').replace("[data]",cellData);
         break;
      case "boolean" :
         returnCell = new String('<Cell><Data ss:Type="Boolean">[data]</Data></Cell>').replace("[data]",cellData);
         break;
      case "number" :
         returnCell = new String('<Cell><Data ss:Type="Number">[data]</Data></Cell>').replace("[data]",cellData);
         break;
      case "date" :
         returnCell = new String('<Cell ss:StyleID="dateFormat"><Data ss:Type="DateTime">[data]</Data></Cell>').replace("[data]",cellData);
         break;
      default :
         returnCell = "<Cell><Data></Data></Cell>";
   }
   return returnCell;
}

function getDateTimeStr(){  // bb/viewtopic.php?t=320
    function _zeroPad(val,len){
        val = val.toString();
        len = (len)?len:2;
        while(val.length<len){
            val = "0" + val;
        }
        return val;
    }
    var newDateTime = new Date();
    var outDate = newDateTime.getFullYear().toString();
        outDate += _zeroPad(newDateTime.getMonth()+1,2);
        outDate += _zeroPad(newDateTime.getDate(),2);
        outDate += "T";
        outDate += _zeroPad(newDateTime.getHours(),2);
        outDate += _zeroPad(newDateTime.getMinutes(),2);
        outDate += "-";
        outDate += _zeroPad(newDateTime.getSeconds(),2);
        outDate += "-";
        outDate += _zeroPad(newDateTime.getMilliseconds(),3);
        outDate += "+";
        newDateTime = null;
    return outDate;
}

function trim(sString){ // Standard javascript trim function
   while(sString.substring(0,1) == ' '){
      sString = sString.substring(1, sString.length);
   }
   while(sString.substring(sString.length-1, sString.length) == ' '){
      sString = sString.substring(0,sString.length-1);
   }
   return sString;
}