Memory generation of Excel files

xlsgen > overview > Memory generation of Excel files

There is basically 3 method calls related to working with Excel spreadsheets in memory rather than actual files :

NewInMemory() : it's a variant of New() where the parameter is a memory buffer for output, instead of a filename. Use this method call to create a new Excel spreadsheet from scratch, in memory.
OpenInMemory() : it's a variant of Open() where the output parameter is a memory buffer for output, instead of a filename
OpenFromMemory() : it's a variant of OpenInMemory() where the input parameter is a memory buffer for input. Use this method call to read/update an existing Excel spreadsheet stored in memory.

NewInMemory(), OpenInMemory()

Usually the notion of creating an Excel workbook ends up materializing as a file in the file system. Whether you are creating an Excel workbook from scratch or reuse an existing one, the deliverable of a generator is a file. Depending on your deployment, you may be using the generator under an environment which provides no write access anywhere on the file system. This is a blocking problem unless you target a network fileshare instead of your own file system. Examples of such are constrained ASP/PHP/JSP applications that, for security reasons, are not given any permission to write on the file system.

Well, it's no more a problem. xlsgen has two new API methods that write in a byte array, and it's up to the client application to do whatever it wants with it, including perhaps writing the byte array back in a file!

The nice thing about the byte array used in the API is that it is a growable buffer, so the client application needs not worry about whether the size will fit the content of the workbook. This growable byte array capabilities is provided by the Windows OLE ILockBytes interface.

Note : this method can target Excel97, 2000, XP, 2003 and 2007 versions (with the 2007 version being a drastically different underlying file format).

VB.NET code

Imports System.Runtime.InteropServices
Imports System.IO
Imports xlsgen

Public Class Win32
    <DllImport("ole32.dll")> _
    Public Shared Function CreateILockBytesOnHGlobal(ByVal hGlobal As IntPtr, _
                                                      ByVal fDeleteOnRelease As Boolean, _
                                                      <Out()> ByRef ppLkbyt As IlockBytes) _
                                                      As Integer
    End Function
End Class

<InterfaceType(ComInterfaceType.InterfaceIsIUnknown), _
Guid("0000000a-0000-0000-C000-000000000046")> _
Public Interface IlockBytes
    Function ReadAt(ByVal ulOffset As Long, ByVal pv As IntPtr, ByVal cv As Integer, <Out()> ByRef pcbRead As Long) As Integer
    Function WriteAt(ByVal ulOffset As Long, ByVal pv As IntPtr, ByVal cb As Integer) As UIntPtr
    Sub Flush()
    Sub SetSize(ByVal cb As Long)
    Sub LockRegion(ByVal libOffset As Long, ByVal cb As Long, ByVal dwLockType As Integer)
    Sub UnlockRegion(ByVal libOffset As Long, ByVal cb As Long, ByVal dwLockType As Integer)
    Sub Stat(ByRef pstatstg As STATSTG, ByVal grfStatFlag As Integer)
End Interface



Module Module1


    Sub Main()

        Dim engine As CoXlsEngine
        engine = CreateObject("ExcelGenerator.ARsTDesign")

        ' create a buffer in memory
        Dim lockbytes As IlockBytes = Nothing
        Dim hr As Integer
        hr = Win32.CreateILockBytesOnHGlobal(IntPtr.Zero, True, lockbytes)

        ' create a simple Excel file in memory
        Dim wbk As IXlsWorkbook
        wbk = engine.NewInMemory(lockbytes, enumExcelTargetVersion.excelversion_97)
        Dim wksht As IXlsWorksheet
        wksht = wbk.AddWorksheet("samplesheet")
        wksht.Label(1, 2) = "Hello world!"
        wbk.Close()

        ' read the resulting buffer
        Dim statstg As New STATSTG
        lockbytes.Stat(statstg, 0)
        Dim offset As Int64
        Dim buf As IntPtr = Marshal.AllocHGlobal(&H2000)
        Dim fileBytes As Byte() = New Byte(statstg.cbSize - 1) {}
        Dim dwRead As Long
        Do While (lockbytes.ReadAt(offset, buf, &H2000, dwRead) = 0)
            If dwRead = 0 Then
                Exit Do
            End If
            Marshal.Copy(buf, fileBytes, CInt(offset), CInt(dwRead))
            offset = (offset + dwRead)
        Loop

        ' write the buffer in a file
        Dim fs As New FileStream("myfile.xls", FileMode.CreateNew)
        Dim bw As New BinaryWriter(fs)
        bw.Write(fileBytes)
        bw.Close()
        fs.Close()
        Marshal.FreeHGlobal(buf)

    End Sub

End Module

C# code


[DllImport("ole32.dll")]
static extern int CreateILockBytesOnHGlobal(IntPtr hGlobal, 
                                            bool fDeleteOnRelease, 
                                            out ILockBytes ppLockbytes);

[Guid("0000000a-0000-0000-C000-000000000046"), 
	InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
	public interface ILockBytes
{
  int ReadAt([In] UInt64 olOffset, [In] IntPtr pv, [In] uint cb, [Out] out uint pcbRead);
  int WriteAt([In] UInt64 ulOffset, [In] IntPtr pv, [In] uint cb, [Out] out uint pcbWritten);
  int Flush();
  int SetSize([In] UInt64 cb);
  int LockRegion([In] UInt64 libOffset, [In] UInt64 cb, [In] int dwLockType);
  int UnlockRegion([In] UInt64 libOffset, [In] UInt64 cb, [In] int dwLockType);
  int Stat([Out, MarshalAs(UnmanagedType.Struct)] out STATSTG pstatstg, [In] int grfStatFlag);
}

// this samples creates an Excel workbook in memory,
//  and then, as an example, writes it back into a file.

xlsgen.CoXlsEngineClass engine = new xlsgen.CoXlsEngineClass();

ILockBytes lockbytes = null;
int hr = CreateILockBytesOnHGlobal(IntPtr.Zero, true, out lockbytes);

IXlsWorkbook book = engine.NewInMemory(lockbytes, enumExcelTargetVersion.excelversion_2003);
IXlsWorksheet sheet = book.AddWorksheet("sheet1");

// add some content to our worksheet
for (int curRow = 0; curRow < 8000; curRow++)
{
	sheet.set_Label(5 + curRow, 1, "Hello");
	sheet.set_Number(5 + curRow, 2, 5 + curRow + (int)(Math.Pow(-1d, (double)curRow)));
}
book.Close();

// how big is the file?
STATSTG statstg = new STATSTG();
lockbytes.Stat(out statstg, 0);

UInt64 offset = 0;
IntPtr buf = Marshal.AllocHGlobal(8192);
uint dwRead;
byte[] fileBytes = new byte[statstg.cbSize];
while (lockbytes.ReadAt(offset, buf, 8192, out dwRead) == 0 && dwRead > 0)
{
	Marshal.Copy(buf, fileBytes, (int)offset, (int)dwRead);
	offset += dwRead;
}

FileStream fs = new FileStream(@"sample.xls", FileMode.CreateNew);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(fileBytes);
bw.Close();
fs.Close();

Marshal.FreeHGlobal(buf);

System.Runtime.InteropServices.Marshal.ReleaseComObject(engine);

System.Diagnostics.Process.Start( @"sample.xls" );

C++ code

{

  // the following code creates a simple Excel workbook in memory
  //   with xlsgen, then writes the buffer into a file.

  ILockBytes* lockbytes = NULL;

  do
  {

    // make a Windows API call to create a ILockBytes instance
    //  see : http://msdn.microsoft.com/library/en-us/stg/stg/createilockbytesonhglobal.asp

    // the ILockBytes interface is defined here :
    //  http://msdn.microsoft.com/library/en-us/stg/stg/ilockbytes.asp
    

    HRESULT hr = CreateILockBytesOnHGlobal(NULL, // HGLOBAL
                                           TRUE, // fDeleteOnRelease
                                           &lockbytes);

    if (!lockbytes)
        break;


    //
    // Excel workbook creation
    //

    xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

    xlsgen::IXlsWorkbookPtr wbk;
    wbk = engine->NewInMemory( lockbytes, xlsgen::excelversion_2003 );

    xlsgen::IXlsWorksheetPtr wksht;
    wksht = wbk->AddWorksheet( L"Sheet1" );

    //
    // Worksheet "Sheet1"
    //

    // a very simple workbook...
    wksht->Label[5][1] = L"Hello";
    for (long r = 5; r < 100; r++)
       for (long c = 2; c < 5; c++)
          wksht->Number[r][c] = r * c;

    //
    // Excel workbook epilogue
    //

    wbk->Close();


    //
    // sample usage : write the byte array in a file
    //

    STATSTG statstg;
    ::ZeroMemory(&statstg, sizeof(STATSTG));
    hr = lockbytes->Stat(&statstg, STATFLAG_DEFAULT);

    HANDLE hFile = ::CreateFile("myfile.xls",
                                GENERIC_WRITE, 
                                0 /*exclusive access*/, 
                                NULL, // default security descriptor
                                CREATE_ALWAYS, 
                                FILE_ATTRIBUTE_NORMAL, 
                                NULL);
    if (!hFile)
	  break;

    BYTE buf[4096];
    DWORD dwRead = 0;
    ULARGE_INTEGER offset;
    offset.QuadPart = 0;

    while( SUCCEEDED(lockbytes->ReadAt(offset,buf,4096,&dwRead)) && (dwRead > 0) )
    {
         offset.QuadPart += dwRead;

         LPBYTE lpBuf = buf;
         DWORD nCount = dwRead;
         long nOffset = 0;
         int nbTries = 0;

         // write chunk by chunk (let the underlying I/O decide the size of the chunk)
         while (nCount > 0 && nbTries < 3)
         {
             DWORD nWritten = 0;
             if ( !::WriteFile(hFile, lpBuf + nOffset, nCount, &nWritten, NULL) )
             {
                  nbTries++; // retry
                  continue;
             }

             nCount  -= nWritten;
             nOffset += nWritten;
         }

    } // end while

    ::CloseHandle(hFile);

  }
  while (0) ;

  if (lockbytes)
      lockbytes->Release();
  lockbytes = NULL;

}

OpenFromMemory()

VB code


Sub demo()

' this sample code works with buffers to read and update a spreadsheet
' the input parameter is a memory buffer (a byte array), and the output
' parameter is a memory buffer as well (a byte array)
'
' for the sake of filling the input parameter with an actual spreadsheet, we read a file
' but it should be obvious that this scenario would also work if this function
' is being passed a memory buffer in parameter, for instance in a server scenario
' with no access to files.


Dim Line As String * 4096
Dim s As String

' read a spreadsheet in memory
Open "C:\Book1.xls" For Binary Access Read As #1
Do While Not EOF(1)
Get #1, , Line
s = s & Line
Loop
Close #1

Dim buffer_input() As Byte
ReDim buffer_input(Len(s))

For i = 1 To Len(s)
  buffer_input(i - 1) = Asc(Mid(s, i, 1))
Next

' the output should be large enough to store the updated spreadsheet
Dim buffer_output(32768) As Byte


' Create an xlsgen engine instance
Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.OpenFromMemory(buffer_input, buffer_output, enumExcelTargetVersion.excelversion_2000)

Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)
wksht.Label(2, 1) = "Some new label"

wbk.Close

' at this point, the "buffer_output" structure holds the updated spreadsheet

' for the sake of doing something with it, we
' save the updated buffer back to a file
Open "c:\Book1_updated.xls" For Binary As #1
Put #1, 1, buffer_output
Close #1

End Sub

VB.NET code


Imports System.IO
Imports System.Runtime.InteropServices
Imports xlsgen

' this sample code demonstrates the use of the OpenFromMemory() method
' where byte arrays are used as input and output parameters.
' For the sake of filling the input parameter with meaningful content, we
' load an existing Excel spreadsheet in memory, but that's just one example
' (accessing the file system defeats the point of using OpenFromMemory() in
' the first place).
' Likewise, after xlsgen is done with the updated spreadsheet, we simply create
' a file from the byte array.

Dim inputFile As String = "c:\Book1.xls"

Dim fileSize As Integer = New FileInfo(inputFile).Length

Dim arrInputBytes(fileSize) As Byte

Dim fs As New FileStream(inputFile, FileMode.Open, FileAccess.Read)
Dim r As New BinaryReader(fs)
r.Read(arrInputBytes, 0, fileSize)
fs.Close()

Dim rawOutputBuffer As IntPtr = Marshal.AllocHGlobal(32768)

' --- actual xlsgen related code begins here

Dim engine As New xlsgen.CoXlsEngineClass
Dim wbk As IXlsWorkbook = engine.OpenFromMemory(arrInputBytes, rawOutputBuffer, xlsgen.enumExcelTargetVersion.excelversion_2000)

wbk.WorksheetByIndex(1).Label(2, 1) = "some new label"

wbk.Close()

' --- actual xlsgen related code ends here

Dim arrOutputBytes(32768) As Byte
Marshal.Copy(rawOutputBuffer, arrOutputBytes, 0, 32768)
Marshal.FreeHGlobal(rawOutputBuffer)

Dim fs2 As New FileStream("c:\Book1_updated.xls", FileMode.Create, FileAccess.Write)
Dim w As New BinaryWriter(fs2)
w.Write(arrOutputBytes)
w.Close()
fs2.Close()

C# code


using System.IO;
using System.Runtime.InteropServices;
using xlsgen;

// this sample code demonstrates the use of the OpenFromMemory() method
// where byte arrays are used as input and output parameters.
// For the sake of filling the input parameter with meaningful content, we
// load an existing Excel spreadsheet in memory, but that's just one example
// (accessing the file system defeats the point of using OpenFromMemory() in
// the first place).
// Likewise, after xlsgen is done with the updated spreadsheet, we simply create
// a file from the byte array.

String inputFile = @"c:\Book1.xls";

int fileSize = (int) new FileInfo(inputFile).Length;

Byte[] arrInputBytes = new Byte[fileSize];

FileStream fs = new FileStream(inputFile, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
r.Read(arrInputBytes, 0, fileSize);
fs.Close();

IntPtr rawOutputBuffer = Marshal.AllocHGlobal(32768);

// --- actual xlsgen related code begins here

xlsgen.CoXlsEngineClass engine = new xlsgen.CoXlsEngineClass();
IXlsWorkbook wbk = engine.OpenFromMemory(arrInputBytes, rawOutputBuffer, xlsgen.enumExcelTargetVersion.excelversion_2000);

wbk.get_WorksheetByIndex(1).set_Label(2,1, "some new label");

wbk.Close();

// --- actual xlsgen related code ends here

Byte[] arrOutputBytes = new Byte[32768];
Marshal.Copy(rawOutputBuffer, arrOutputBytes, (int)0, (int)32768);
Marshal.FreeHGlobal(rawOutputBuffer);

FileStream fs2 = new FileStream(@"c:\Book1_updated.xls", FileMode.Create, FileAccess.Write);
BinaryWriter w = new BinaryWriter(fs2);
w.Write(arrOutputBytes);
w.Close();
fs2.Close();

C++ code


xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

HANDLE hFile = ::CreateFile("C:\\Book1.xls", 
                            FILE_READ_DATA,
                            FILE_SHARE_READ,
                            NULL, 
                            OPEN_EXISTING,
                            FILE_ATTRIBUTE_NORMAL,
                            NULL);
if ( hFile )
{
    DWORD len = ::GetFileSize( hFile, NULL); // only 32-bit of the actual file size is retained
    if (len != 0)
    {
        HGLOBAL hGlobal = ::GlobalAlloc(GMEM_MOVEABLE | GMEM_NODISCARD, len);
        if ( !hGlobal )
        {
             ::CloseHandle(hFile);
        }
        else
        {
             char* lpBuffer = reinterpret_cast<char*> ( ::GlobalLock(hGlobal) );
             DWORD dwBytesRead = 0;

             char* p = lpBuffer;

             while ( ::ReadFile(hFile, p, len, &dwBytesRead, NULL) )
             {
                  p += dwBytesRead;
                  if (dwBytesRead == 0)
                       break;
                  dwBytesRead = 0;
             }

             SAFEARRAY* pSA = ::SafeArrayCreateVector(VT_UI1, 0, len);
             BYTE HUGEP *plong = NULL;

             ::SafeArrayAccessData(pSA, (void HUGEP**)&plong);
             if (plong)
             {
                 memcpy(plong, lpBuffer, len);

                 ::SafeArrayUnaccessData(pSA);

                 VARIANT vinput;
                 vinput.vt = VT_ARRAY | VT_UI1;
                 vinput.parray = pSA;

                 _variant_t v(vinput);

                 {

                      SAFEARRAY* pSAOutput = ::SafeArrayCreateVector(VT_UI1, 0, 32768);

                      VARIANT voutput;
                      voutput.vt = VT_ARRAY | VT_UI1;
                      voutput.parray = pSAOutput;

                      _variant_t voutput_memory(voutput);


                      xlsgen::IXlsWorkbookPtr wbk;
                      wbk = engine->OpenFromMemory(v, voutput_memory, xlsgen::excelversion_2000);
                      wbk->Close();

                      // note : the _variant_t creates a copy of the SAFEARRAY

                      int nbDimensions = SafeArrayGetDim(voutput_memory.parray);

                      long lbound = 0;
                      long ubound = 0;
                      SafeArrayGetLBound(voutput_memory.parray, 1, &lbound);
                      SafeArrayGetUBound(voutput_memory.parray, 1, &ubound);

                      long len = 1 + ubound - lbound;
                      if (len > 0)
                      {
                            HANDLE hFile = ::CreateFile("C:\\Book1_updated.xls",
                                                        GENERIC_WRITE, 0, 
                                                        NULL, // default security descriptor
                                                        CREATE_ALWAYS, 
                                                        FILE_ATTRIBUTE_NORMAL, NULL);
                            if (!hFile)
                            {
                            }
                            else
                            {
                                 LPBYTE lpBuf = NULL;
                                 ::SafeArrayAccessData(voutput_memory.parray, (void HUGEP**)&lpBuf);
                                 long nOffset = 0;

                                 while (len > 0)
                                 {
                                        DWORD nWritten = 0;

                                        ::WriteFile(hFile, lpBuf + nOffset, len, &nWritten, NULL);
                                        if (nWritten == 0)
                                        {
                                            _ASSERT(0);
                                            break;
                                        }

                                        len -= nWritten;
                                        nOffset += nWritten;
                                }

                                ::CloseHandle(hFile);

                                ::SafeArrayUnaccessData(voutput_memory.parray);
                           }

                        }

                        ::SafeArrayDestroy(pSAOutput);
                   }

                   SafeArrayDestroy(pSA);
             }

             ::GlobalUnlock(hGlobal);
        }

        ::CloseHandle(hFile);

    }

}