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(), 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 New CoXlsEngine

        ' 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
VBScript code
  ' xlsgen_clientside_read.vbs
  ' to run this code, double-click on the .vbs file
  '
  ' demonstrates how to update an existing .xls file in memory using VBScript
  ' for the purpose of the sample, the output buffer is written back to a file

  Dim szProcessingFolder
  szProcessingFolder = "c:\"

  Dim engine
  Set engine = CreateObject("ExcelGenerator.ARsTdesign")
  
  ' create a memory buffer host
  Dim lb
  lb = engine.helpers.ILockBytes_New

  ' open an existing Excel file, and retrieve a workbook
  Dim wbk
  Set wbk = engine.OpenInMemory(szProcessingFolder & "input.xls", lb, 3)

  ' create a new worksheet
  Dim wksht
  Set wksht = wbk.AddWorksheet("sheet1")
  
  ' sample code
  wksht.Label(9,1) = "hello world!"
  ' your code begins here
  ' ...

  wbk.Close

  ' convert the output to a regular memory buffer (byte array)
  Dim byteArrayBuffer
  byteArrayBuffer = engine.helpers.ILockBytes_Write(lb)

  ' for the purpose of the sample, write back to a regular file
  engine.helpers.WriteFile szProcessingFolder & "myfile.xls", byteArrayBuffer

  ' free the memory
  Set engine = Nothing

C# code
using System.Runtime.InteropServices;
using System.IO;
using xlsgen;

[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.CoXlsEngine engine = new xlsgen.CoXlsEngine();

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;

}
Delphi code
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, xlsgen_TLB, ComOBJ, ComServ, ActiveX;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  engine : IXlsEngine ;
  wbk : IXlsWorkbook ;
  wksht : IXlsWorksheet ;
  output : ILockBytes;
  outputFile: file;
  statstg : TStatStg;
  filelen : Integer;
begin
   OleCheck(CoCreateInstance(
      Class_CoXlsEngine,
      nil,
      CLSCTX_ALL,
      IXlsEngine,
      engine));

   ActiveX.CreateILockBytesOnHGlobal(0, True, output);

   // we open input file 'input.xls', make changes,
   // and make sure the output is a memory buffer

   wbk := engine.OpenInMemory('input.xls', output, xlsgen_TLB.excelversion_2007);

   wksht := wbk.AddWorksheet('Sheetsss2');
   wksht.Label_[2,3] := 'Hello World' ;
   wbk.Close();

   output.Stat(statstg, ActiveX.STATFLAG_DEFAULT);

   filelen := statstg.cbSize;

   // the output is a memory buffer,
   // we can for instance write it back to a regular file on disk

   // ...
   
   engine := Nil ;

end;

end.

 

OpenFromMemory()

Java code
XlsEngine engine = new XlsEngine("./../../../xlsgen.dll");

XlsWorkbook workbook = null;

int outputLockBytes = engine.NewILockBytes();

try
{
    // for the sake of the example, we build a byte[] from the contents of a .XLS file
    // but the point is to pass a byte[] to OpenFromMemory()

    FileInputStream fis = new FileInputStream("input.xls");
    int fileSize = fis.available();
    byte[] inputBuffer = new byte[fileSize];
    fis.read(inputBuffer);
    fis.close();

    workbook = engine.OpenFromMemory(inputBuffer, outputLockBytes, xlsgen.excelversion_2003);
}
catch(Exception e)
{
}

try
{
    if (workbook != null)
    {
        // dummy update of the workbook
        workbook.getWorksheetByIndex(1).putLabel(2,1, "Inserted comment");

        workbook.Close();
    }
}
catch(Exception e)
{
}

// obtain the output byte[]
byte[] outputBytes = engine.GetBytesFromILockBytes(outputLockBytes);

// write it back to a file. Again that is only for the sake of the example
try
{
    FileOutputStream fos = new FileOutputStream("output.xls");
    fos.write(outputBytes);
    fos.close();
}
catch(Exception e)
{
}

// do not forget to release the working buffer
engine.ReleaseILockBytes(outputLockBytes);

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

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()

        ' 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()

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

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

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

        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

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




    End Sub

End Module

VBScript code

  ' xlsgen_clientside.vbs
  ' to run this code, double-click on the .vbs file

  Dim xlsgen
  Set xlsgen = CreateObject("ExcelGenerator.ARsTdesign")
  


  ' phase 1 : build a memory buffer from an Excel file
  '
  Dim inputStreamer
  Set inputStreamer = CreateObject("ADODB.Stream")
  inputStreamer.Type = 1 'adTypeBinary
  inputStreamer.Open

  inputStreamer.LoadFromFile "c:\Book1.xls"

  Dim inputByteArray 
  inputByteArray = inputStreamer.Read

  inputStreamer.Position = 0

  ' bogus method call only meant to create a byte array internally
  Dim outputByteArray
  outputByteArray = inputStreamer.Read

  inputStreamer.Close 
  Set inputStreamer = Nothing


  ' phase 2 : update the spreadsheet
  '

  Dim wbk
  Set wbk = xlsgen.OpenFromMemory(inputByteArray, outputByteArray, 3)

  Dim wksht
  Set wksht = wbk.AddWorksheet("new_sheet")
  
  ' sample code
  wksht.Label(9,1) = "hello world!"
  ' your code begins here
  ' ...

  wbk.Close



  ' phase 3 : write back to a file
  '


  Dim outputStreamer
  Set outputStreamer = CreateObject("ADODB.Stream")
  outputStreamer.Type = 1 'adTypeBinary
  outputStreamer.Open

  outputStreamer.Write outputByteArray

  outputStreamer.SaveToFile "c:\Book1_updated.xls"

  outputStreamer.Close 
  Set outputStreamer = Nothing

C# code

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

class Program
{
    // 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.

    [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);
    }

    static void Main(string[] args)
    {

    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();

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


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

    IXlsWorkbook wbk = engine.OpenFromMemory(arrInputBytes, lockbytes,   xlsgen.enumExcelTargetVersion.excelversion_2000);

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

    wbk.Close();

    // how big is the memory buffer?
    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 fs2 = new FileStream(@"c:\Book1_updated.xls", FileMode.Create, FileAccess.Write);
    BinaryWriter w = new BinaryWriter(fs2);
    w.Write(fileBytes);
    w.Close();
    fs2.Close();

    Marshal.FreeHGlobal(buf);

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

    }

}

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);

                 {
                     ILockBytes* lockbytes = NULL;

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

                      _variant_t voutput;
                      voutput.vt = VT_UNKNOWN;
                      voutput.punkVal = lockbytes;

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

                    HANDLE hFile = ::CreateFile("C:\\Book1_updated.xls",
                                                GENERIC_WRITE, 0, 
                                                NULL, // default security descriptor
                                                CREATE_ALWAYS, 
                                                FILE_ATTRIBUTE_NORMAL, NULL);
					if (!hFile)
					{
					}
					else
					{
                        BYTE buf[4096 + 1];
                        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(hFile2, lpBuf + nOffset, nCount, &nWritten, NULL) )
                                   {
                                        nbTries++; // retry
                                        continue;
                                   }

                                   nCount -= nWritten;
                                   nOffset += nWritten;
                              }
                         } // end while	            

                         ::CloseHandle(hFile);
                    }

                }

                   SafeArrayDestroy(pSA);
             }

             ::GlobalUnlock(hGlobal);
        }

        ::CloseHandle(hFile);

    }

}

 

xlsgen documentation. © ARsT Design all rights reserved.