Creating xlsx file from scratch?

Sep 8, 2011 at 8:07 AM

Hi,

I'm at the feasibility study stage... I'd like to generate an Excel xlsx file on a unix platform (Solaris).  libOPC sounds ideal, but how far along is it for Excel manipulation, starting from an empty file (and I'm just assuming portability to Solaris won't be much of an issue...)

Thanks for creating libOPC by the way.  It sounds so worthwhile (not that niche, surely - windows clients on a unix back-end is pretty common), I'm surprised there aren't more following the project.

Cheers,
Alan

Coordinator
Sep 8, 2011 at 11:21 AM

Hi Alan,

 

I think compiling on Solaris should be a simple ./configure && make. If not please let me know....

There is (currently) no specialized API to create Excel files, however there is the opc_generate tool (http://libopc.codeplex.com/wikipage?title=opc_generate) which will create all the boilerplate code for you. If you e.g. want to create a report document all you need to do it to create an "empty" Excel file use opc_generate to generate the "c" code and then tweak the code to generate the report.

So opc_generate is what you are looking for I guess.

Florian

Mar 5, 2012 at 9:35 AM

Hi

I am not getting the way to modify the .xlsl file. Is it possible to modify it? Can you help me on it? I want to add new sheet by using the data which is a part of another sheet.I want to add some contents to file from the code but am unable to do so,please help me...

 

Masood

Coordinator
Mar 5, 2012 at 11:26 AM
Hi Masood,

yes -- its possible to modify the OPC container.

Howver it sound like you need to do some XSL specific munching.

Do you have some code I can look at? Or can you send me a test
document plus some additional input?

Florian



On Mon, Mar 5, 2012 at 11:35 AM, masoodshaik <notifications@codeplex.com> wrote:
> From: masoodshaik
>
> Hi
>
> I am not getting the way to modify the .xlsl file. Is it possible to modify
> it? Can you help me on it? I want to add new sheet by using the data which
> is a part of another sheet.I want to add some contents to file from the code
> but am unable to do so,please help me...
>
>
>
> Masood
>
> Read the full discussion online.
>
> To add a post to this discussion, reply to this email
> ([email removed])
>
> To start a new discussion for this project, email
> [email removed]
>
> You are receiving this email because you subscribed to this discussion on
> CodePlex. You can unsubscribe or change your settings on codePlex.com.
>
> Please note: Images and attachments will be removed from emails. Any posts
> to this discussion will also be available online at codeplex.com
Mar 5, 2012 at 12:17 PM
Edited Mar 5, 2012 at 12:22 PM

Thanks for Reply

 

static void dumpSharedrows(mceTextReader_t *reader){

 xmlChar *ln = xmlStrdup(xmlTextReaderLocalName(reader->reader)); const char * lnvalue=(const char*)ln; if((strcmp(lnvalue,"si"))==0) {
         id3++; } mce_start_attributes(reader){ }mce_end_attributes(reader); mce_start_children(reader){ mce_start_element(reader,NULL,NULL){ dumpSharedrows(reader); }mce_end_element(reader); mce_start_text(reader){

remove((const char *)xmlTextReaderConstValue(reader->reader)); 

LOGI("%s", xmlTextReaderConstValue(reader->reader));

 }mce_end_text(reader); }mce_end_children(reader);

}

 

void sharedRows(){ opcInitLibrary();    opcContainer *c=opcContainerOpen(_X("/data/Command.xlsx"), OPC_OPEN_READ_WRITE, NULL, NULL);    if (NULL!=c) {        mceTextReader_t reader;        if (OPC_ERROR_NONE==opcXmlReaderOpen(c, &reader, _X("xl/sharedStrings.xml"), NULL, 0, 0)) {            mce_start_document(&reader) {                mce_start_element(&reader, NULL, NULL) {                 dumpSharedrows(&reader);                } mce_end_element(&reader);            } mce_end_document(&reader);            mceTextReaderCleanup(&reader);        }        opcContainerClose(c, OPC_CLOSE_NOW);    }    opcFreeLibrary();}

Header 1 Header 2
Apple Ball
row 2, cell 1 row 2, cell 2

This is i tried to do with your code but remove function is not working

Like this i also want to add new row to the .xlsx sheet. so how i'll do it

Masood

Coordinator
Mar 6, 2012 at 5:28 PM


Hi Masood,
 
Looks like you need to modify the Spreadsheet-Stream. To do so you need to "load" the spreadsheet data into main memory and then "save" them back again. The bad news is that libopc does not provide any kind of high level API for Spreadsheets, Wordprocessing Docs etc.
Is it possible for you to load the entire stream into an XML tree? Or is your spreadsheet too big for that?
Florian

Mar 7, 2012 at 6:00 AM

Hi Florian

 

Iam facing problem in finding the tags delText, moveFrom,ins,Insertion by,author,tr,trPr in the document.xml file.Please let me know  what should i do in order to generate these tags.

 

Masood

Coordinator
Mar 7, 2012 at 8:01 PM

Hi Masood,

I'm not quite sure what your problem is. However I think you might want to try this:

    opcInitLibrary();
    opcContainer *c=NULL;
    if (NULL!=(c=opcContainerOpen(_X(argv[1]), OPC_OPEN_READ_WRITE, NULL, NULL))) {
        opcContainerDump(c, stdout);

        const char *stream_name="xl/workbook.xml";

        xmlDocPtr doc=opcXmlReaderReadDoc(c, _X(stream_name), NULL, NULL, 0);

        // have fun with the document here...

        opcContainerOutputStream* stream=opcContainerCreateOutputStream(c, _X(stream_name), OPC_COMPRESSIONOPTION_NORMAL);
        xmlOutputBufferPtr out=xmlOutputBufferCreateIO((xmlOutputWriteCallback)opcContainerWriteOutputStream,
                                                       (xmlOutputCloseCallback)opcContainerCloseOutputStream,
                                                       stream,
                                                       NULL);
        xmlSaveFileTo(out, doc, NULL);
        xmlFreeDoc(doc);
        opcContainerClose(c, OPC_CLOSE_NOW);
    }
    opcFreeLibrary();

The above sample program will load the stream into an xmlDom tree (aka DOM) which you can modify and save back.

Florian

Nov 22, 2012 at 6:53 AM

hi,

I've tested Masood's code to get a xlsx file text content like this:

static void dumpSharedrows(mceTextReader_t *reader){
#if 0
 xmlChar *ln = xmlStrdup(xmlTextReaderLocalName(reader->reader));
 const char * lnvalue=(const char*)ln;
 if((strcmp(lnvalue,"si"))==0) {
         //id3++;
 }
#endif
 mce_skip_attributes(reader);
 //mce_start_attributes(reader){ }
 //mce_end_attributes(reader);

 mce_start_children(reader){
  mce_start_element(reader,NULL,NULL)
  { dumpSharedrows(reader); }
  mce_end_element(reader);

  mce_start_text(reader){
      for(const xmlChar *txt=xmlTextReaderConstValue(reader->reader);0!=*txt;txt++)
      putc(*txt, stdout);
      putc('\n', stdout);
 //remove((const char *)xmlTextReaderConstValue(reader->reader));

 //LOGI("%s", xmlTextReaderConstValue(reader->reader));

 }mce_end_text(reader);
 }
 mce_end_children(reader);
}

int __stdcall processxlsx(_TCHAR* inputFilename, _TCHAR* outputFilename)
{
#ifdef WIN32
     _CrtSetDbgFlag (_CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF);
#endif

opcInitLibrary();
opcContainer *c=opcContainerOpen(_X(inputFilename), OPC_OPEN_READ_ONLY, NULL, NULL);
if (NULL!=c) {
 mceTextReader_t reader;

 if (OPC_ERROR_NONE==opcXmlReaderOpen(c, &reader, _X("xl/sharedStrings"), NULL, 0, 0)) //  xl/worksheets/sheet1.xml
 { 
  mce_start_document(&reader)
  {
   mce_start_element(&reader, NULL, NULL)
   {
    dumpSharedrows(&reader);
   }
   mce_end_element(&reader);
  }
  mce_end_document(&reader); mceTextReaderCleanup(&reader);
 }
 opcContainerClose(c, OPC_CLOSE_NOW);
}
opcFreeLibrary();

#ifdef WIN32
    OPC_ASSERT(!_CrtDumpMemoryLeaks());
#endif
    return 0;
}

only part of the text content would be  received. I'd lovely to get whole text at a time,but it seems I must process xl/worksheets/sheet1.xml
,xl/worksheets/sheet2.xml...  is it ture?  how i can do that?

Another question, how can i get the whole text content from a pptx file? i am seeking of it.

Regards.

Cooor.