/*
 *  Concordance Programming Language Export to Excel Utility
 *
 *      Program to export the current query to Excel. Excel must be
 *      running for this to work. All fields are exported to the
 *      spreadsheet, but Excel may only accept the first line of a
 *      full text field as it does not like carriage returns.
 *
 *      Copyright (c) 2002 Dataflight Software, Inc. 
 *      ALL RIGHTS RESERVED.
 *      2337 Roscomare Road, Suite 11
 *      Los Angeles, CA 90077-1851
 *
 *      Unauthorized distribution, adaptation or use may be
 *      subject to civil and criminal penalties.
 *
 *      You may incorporate this program into your own programs
 *      ONLY if you incorporate the following copyright notice:
 *
 *      Copyright (c) 2002 YOUR NAME. ALL RIGHTS RESERVED.
 *      Portions copyright (c) 2002 Dataflight Software, Inc.
 *
 */

main()
{
int db, rc, i, d, noDate;
text szText;
char string[256];

int ddeHandle, i;
text topicList, sheetName;

   /* Establish a link to the spreadsheet. */
   if ((ddeHandle = ddeConnect("Excel","System")) == 0)
      messageBox("Please run Excel before exporting data.", program(), MB_OK | MB_ICONEXCLAMATION);
   else {
   
      /* Create a new work sheet. */
      ddeExec(ddeHandle,"[New(1)]");
   
      /* Retrieve a list of available spreadsheets. */
      topicList = ddeRequest(ddeHandle,"Selection");
   
      /* Disconnect */
      ddeDisconnect(ddeHandle);
   
      /* Isolate the first spreadsheet’s name. */
      /* Establish a new conversation on the name. */
      sheetName=substr(topicList,1,match(topicList,"!",1)-1);
      ddeHandle = ddeConnect("Excel",sheetName);

      /* Send over the field titles. */
      for(i = 1; i <= db.fields; i =i +1)
         if (db.access[i] & 1)
            ddePoke(ddeHandle,"R1C"+str(i),str(db.name[i]));

      /* Fill it with data. */
      d = docno(db);
      noDate = ctod("00/00/0000");
      cycle(db) {
         Message("Exporting "+str(docno(db))+" of "+str(count(db)), FALSE);
         for(i = 1; i <= db.fields; i = i + 1)
            if (db.access[i] & 1) {
               switch(db.type[i]) {
                  case 'P':
                              ddePoke(ddeHandle,"R"+str(docno(db)+1)+"C"+str(i),ConvertCRs(db->i));
                              break;
                  case 'T':
                              ddePoke(ddeHandle,"R"+str(docno(db)+1)+"C"+str(i),szText = db->i);
                              break;
                  case 'D':
                              ddePoke(ddeHandle,"R"+str(docno(db)+1)+"C"+str(i), (db->i == noDate) ? "" : dtoc(db->i));
                              break;
                  case 'N':
                              ddePoke(ddeHandle,"R"+str(docno(db)+1)+"C"+str(i),str(db->i, db.length[i], db.places[i], 0));
                              break;
               }
            }
      }
      goto(db, d);

      ddeDisconnect(ddeHandle);
   }
   return(0);

} /* main() */

/****************************************************************
* Name:         ConvertCRs                                      *
* Synopsis:     Converts hard carriage returns to plain text.   *
****************************************************************/
ConvertCRs(text szText)
{
int i, length;
text szResult;

   i = findline(szText, 1, length);
   while(i) {
      szResult = szResult + substr(szText, i, length);
      if (i = findnline(szText, i, length))
         szResult = szResult + "; ";
   }
   return(szResult);

} /* ConvertCRs() */

/****************************************************************
* Name:         Message                                         *
* Synopsis:     Displays error message and waits for key.       *
****************************************************************/
Message(text message; int wait)
{
text screen;
int key;

   cursoroff();
   if (wait)
      screen = save(5,13,8,69);

   box(5, 13, 7, 69, "3D", MenuColor_);
   puts(6,14,pad(message,'C',53),MenuColor_);

   if (wait)  {
      key = getkey();
      restore(5,13,screen);
   }

   return(asc(upper(chr(key))));
}  /* Message() */
/****************************************************************
* Name:         FileName                                        *
* Synopsis:     Trims the path from the file name.              *
****************************************************************/
FileName(text name)
{
int i;
   if (i = match(name,":",1))
      name = substr(name,i+1);
   while(i = match(name,"\",1))
      name = substr(name,i+1);
   return(name);
} /* FileName() */
/****************************************************************
* Name:         Path                                            *
* Synopsis:     Trims the file name from the path.              *
****************************************************************/
Path(text name)

{
int i, j;
char string[257];

   string = name;
   if (i = match(name,":",1))
      j = i;
   else
      i = j = 1;

   while(j = match(name,"\", i))
      i = j + 1;
   string[i - 1] = 0;
   return(string);
} /* Path() */

RGB(char red, grn, blu)
{
   return(((blu & 255) * 65536) | ((grn & 255) * 256) | (red & 255));
}