Saving Excel Files

Good Morning,

I am working on saving excel objects to a directory of my choosing. I am able to use a folder browser dialog to select the save location and the saveas method within the excel interop and i am able to create a file in the folder i select with the name i choose.

The issue is that when i later go to open that file it is blank. no workbooks no worksheets etc . Just a seemingly blank file in the proper location with the proper name.

It should be noted that if i single click on the report icon to open it the microsoft browser window does show a tiny preview of what seems like the correct report.

Code:

---

eapp.Worksheets(1).SaveAs(FileName:=patient_frm.savefolder & "\Lateral Postural Analysis")

---

Here is a working example of creating an Excel file and saving to disk as

This does not seem right

Code:

---

eapp.Worksheets(1).SaveAs(FileName:=patient_frm.savefolder & "\Lateral Postural Analysis")

---

Code:

---

Option Strict On
Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Public Class Form1
  Private PictureFile As String = IO.Path.Combine(Application.StartupPath, "AllLabels.bmp")

  Private Sub cmdRun_Click( _
      ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles cmdRun.Click

      Dim App As Excel.Application
      Dim WorkBook As Excel.Workbook
      Dim Sheet As Excel.Worksheet

      Dim misValue As Object = System.Reflection.Missing.Value

      Dim chartRange As Excel.Range

      App = New Excel.ApplicationClass

      WorkBook = App.Workbooks.Add(misValue)
      Sheet = CType(WorkBook.Sheets("sheet1"), Excel.Worksheet)

      If IO.File.Exists(PictureFile) Then

        Sheet.Shapes.AddPicture(PictureFile, _
                    Core.MsoTriState.msoCTrue, _
                    Core.MsoTriState.msoCTrue, _
                    0, 0, 100, 100)

      End If

      Sheet.Cells(4, 2) = ""
      Sheet.Cells(4, 3) = "Student1"
      Sheet.Cells(4, 4) = "Student2"
      Sheet.Cells(4, 5) = "Student3"

      Sheet.Cells(5, 2) = "Term1"
      Sheet.Cells(5, 3) = "80"
      Sheet.Cells(5, 4) = "65"
      Sheet.Cells(5, 5) = "45"

      Sheet.Cells(6, 2) = "Term2"
      Sheet.Cells(6, 3) = "78"
      Sheet.Cells(6, 4) = "72"
      Sheet.Cells(6, 5) = "60"

      Sheet.Cells(7, 2) = "Term3"
      Sheet.Cells(7, 3) = "82"
      Sheet.Cells(7, 4) = "80"
      Sheet.Cells(7, 5) = "65"

      Sheet.Cells(8, 2) = "Term4"
      Sheet.Cells(8, 3) = "75"
      Sheet.Cells(8, 4) = "82"
      Sheet.Cells(8, 5) = "68"

      Sheet.Cells(9, 2) = "Total"
      Sheet.Cells(9, 3) = "315"
      Sheet.Cells(9, 4) = "299"
      Sheet.Cells(9, 5) = "238"

      chartRange = Sheet.Range("b2", "e3")
      chartRange.Merge()

      chartRange.FormulaR1C1 = "MARK LIST"
      chartRange.HorizontalAlignment = 3
      chartRange.VerticalAlignment = 3
      chartRange = Sheet.Range("b4", "e4")
      chartRange.Font.Bold = True
      chartRange = Sheet.Range("b9", "e9")
      chartRange.Font.Bold = True
      chartRange = Sheet.Range("b2", "e9")

      chartRange.BorderAround( _
          Excel.XlLineStyle.xlContinuous, _
          Excel.XlBorderWeight.xlMedium, _
          Excel.XlColorIndex.xlColorIndexAutomatic, _
          Excel.XlColorIndex.xlColorIndexAutomatic _
      )

      '
      ' Replace static name with path and file name selected via a save dialog
      '
      Sheet.SaveAs(IO.Path.Combine(Application.StartupPath, "MyNewFile.xlsx"))

      WorkBook.Close()
      App.Quit()

      releaseObject(App)
      releaseObject(WorkBook)
      releaseObject(Sheet)

      MsgBox("File created !")
  End Sub
  Private Sub releaseObject(ByVal sender As Object)
      Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
        sender = Nothing
      Catch ex As Exception
        sender = Nothing
      Finally
        GC.Collect()
      End Try
  End Sub
End Class

---

I took a look at your code and felt compelled to post what i have. Im not directly importing the interop because im using late binding.

Everything seems to work except for the fact that when it creates the new excel file it appears there is nothing in it.

This is an example of one of the print functions.

Code:

---


 Public Function prn_post_ap()
        eapp = New Object

        eapp = GetObject("c:\Chiro Report\Anterior Postural Analysis.xls", )

        'Set Cells Workbook
        eapp.Worksheets(1).cells(28, 8) = wselect.weight

        eapp.Worksheets(1).cells(29, 8) = (Lat_Hd_Shift / 25.4)

        eapp.Worksheets(1).cells(33, 4) = Lat_Sld_Shift & " in"

        eapp.Worksheets(1).cells(34, 4) = Lat_Hip_Shift & " in"

        eapp.Worksheets(1).cells(35, 4) = Lat_Hd_Ang & " Degrees"

        eapp.Worksheets(1).cells(36, 4) = Lat_Sld_Ang & " Degrees"

        eapp.Worksheets(1).cells(37, 4) = Lat_Hip_Ang & " Degrees"

        eapp.Worksheets(1).cells(7, 2) = patient_frm.txt_fname.Text & " " & patient_frm.txt_lname.Text

        eapp.Worksheets(1).cells(10, 4) = patient_frm.EDate.Value.ToString

        eapp.Worksheets(1).shapes.addpicture("c:\chiro report\Post_Ap.bmp", True, True, 20, 230, 220, 305)
       
        'Print Document
        If patient_frm.save = False Then
            eapp.Worksheets(1).PrintOut()
        End If
        If patient_frm.save = True Then
            eapp.Worksheets(1).SaveAs(FileName:=patient_frm.savefolder & "Anterior Postural Analysis")

        End If

        'Close Document
        eapp.Close(False, False)

        eapp = Nothing

        Return 0
    End Function

---

What is the reason for late binding?

Im late binding because the end users are going to be using multiple versions of office and i was having an issue if i simply imported the interop i have locally from office 2010.

I dont really understand what the issue with this line of code is

Code:

---

eapp.Worksheets(1).SaveAs(FileName:=patient_frm.savefolder & "Anterior Postural Analysis.xls")

---

patient_frm.savefolder is simply a string that is the location of a user selected folder to save too. And i had to put "Anterior Postural Analysis.xls" in the mix so it would actually create an excel file.

The line of code seems to be working because when run it creates an excel file named Anterior Postural Analysis.xls in the specified folder. I just cant actually see the worksheet when i open the file.

Understand in regards to late binding but as you can see it makes it difficult to figure out what the problem is.

You should be saving at the workbook level.

I have altered the line of code so that it should be saving at the workbook level at this point. But i am still having the same issue. The file saves but when i open it it seems like it is blank. However if i look at the preview closely at the bottom of the window it has definately saved the output i am sending to the file. Its almost like it gets saved with the workbook hidden or invisible. Its wierd

Code:

---


eapp.SaveAs(FileName:=patient_frm.savefolder & "\Lateral Postural Analysis")

---

ok i figured part of it out....

It saves everything but it is saving the file with the workbook hidden. You have to manually go in and unhide the workbook.

There has to be a way to save this with the workbook visible.


Any Ideas ?

Quote:

---

Originally Posted by Deslyxia

ok i figured part of it out....

It saves everything but it is saving the file with the workbook hidden. You have to manually go in and unhide the workbook.

There has to be a way to save this with the workbook visible.


Any Ideas ?

---

Save it at the workbook level

I did some testing with this and I am pretty sure that the way i am saving it now is at the workbook level. I say that only because when i removed the .worksheets(1) from the line of code and replaced it with .workbook I reveived an error message stating that workbook is not a member of workbook.

This leads me to believe that in this case using eapp.saveas is saving it at the workbook level.

I read through this last night http://msdn.microsoft.com/en-us/library/Aa140947

And I see this at the end

Quote:

---

When you are using Automation to edit an Excel workbook, keep the following in mind.

Creating a new instance of Excel and opening a workbook results in an invisible instance of Excel and a hidden instance of the workbook. Therefore, if you edit the workbook and save it, the workbook is saved as hidden. The next time the user opens Excel manually, the workbook is invisible and the user has to click Unhide on the Window menu to view the workbook.

To avoid this behavior, your Automation code should unhide the workbook before editing it and saving it. Note that this does not mean Microsoft® Excel itself has to be visible.

---

Unfortunately i dont see how to do that

Quote:

---

Originally Posted by Deslyxia

I read through this last night http://msdn.microsoft.com/en-us/library/Aa140947

And I see this at the end

Unfortunately i dont see how to do that

---

This is why sometimes in cases like this a better option is using a third party library. I use Aspose Cells which a) does not require Excel installed to create/edit workbooks b) will create new files hidden but when viewed are not hidden in regards to worksheets c) extremely easy to use even for complex operations.

Quote:

---

Originally Posted by Deslyxia

I did some testing with this and I am pretty sure that the way i am saving it now is at the workbook level. I say that only because when i removed the .worksheets(1) from the line of code and replaced it with .workbook I reveived an error message stating that workbook is not a member of workbook.

This leads me to believe that in this case using eapp.saveas is saving it at the workbook level.

---

1,3 and 6 were all at the worksheet level and did not notice you changed it as I was focused on getting you to change it and you did.

Im downloading a trial version of aspose cells now. Is there anything special that is going to change regarding how i save the file?

So problem number one is that as soon as i import Aspose.cells into my solution i now have an error with this function.

It is telling me that type drawing.bitmap is not defined. This has always worked in the past but now it seems to be conflicting with aspose.

Code:

---

Private Function SaveImage(ByVal name As String)

        Dim bmp As New Drawing.Bitmap(xray.Image.Width, xray.Image.Height)
        xray.DrawToBitmap(bmp, New Rectangle(0, 0, bmp.Width, bmp.Height))
        bmp.Save("c:\Chiro Report\" & name)

        Return 0

    End Function

---

Aspose uses its own routines and not Excel's routines. For instance in the code below assigning a value to a cell uses PutValue function.

Adding a picture

Creates a new workbook, one worksheet, changes styling of cells then placed data from a DataTable into the worksheet.

Code:

---

Imports Aspose.Cells
. . .
      Dim wb As Workbook = New Workbook()
      wb.Worksheets.Add()

      Dim ws As Worksheet = wb.Worksheets(0)

      ws.Name = "Jenkins data"

      Dim currentRow As Integer = 5
      Dim cells As Cells = ws.Cells

      Dim styles As Styles = wb.Styles

      Dim styleIndex As Integer = styles.Add()

      Dim styleNames As Style = styles(styleIndex)
      styleNames.Font.Size = 12
      styleNames.Font.Color = Color.Blue
      styleNames.Font.IsBold = True
      styleNames.Font.Name = "Times New Roman"

      styleIndex = styles.Add()
      Dim styleInvoice As Style = styles(styleIndex)
      styleInvoice.Font.IsBold = True
      styleInvoice.Font.IsItalic = True
      styleInvoice.ForegroundColor = Color.LightGray
      styleInvoice.Pattern = BackgroundType.Solid


      For Row As Integer = 0 To dt.Rows.Count - 1
        currentRow += 1
        cells(currentRow, 1).Style = styleInvoice
        cells(currentRow, 1).PutValue(dt.Rows(Row).Item("vendor_invoice_number_txt").ToString)

        cells(currentRow, 2).Style = styleNames
        cells(currentRow, 2).PutValue(dt.Rows(Row).Item("first_name").ToString) ' may be empty

        cells(currentRow, 3).Style = styleNames
        cells(currentRow, 3).PutValue(dt.Rows(Row).Item("last_name").ToString) ' may be empty

        cells(currentRow, 4).PutValue(dt.Rows(Row).Item("zip_code_10_text").ToString)
      Next

      ws.AutoFitColumns()
      wb.Save(My.Settings.ExcelFolder & "\Jenkins_1_2003.xls")

---

When saving data you can set the version of Excel

Setting the version to 2003

Code:

---

Book.Save(OutputFile, FileFormatType.Excel2003)

---

Best to review their documentation http://www.aspose.com/documentation/...net/index.html

I use the following (good once you register Cells) to initialize the library for the paid version. Note the last section DesignerFile which points to a predefined template that I simply have Aspose bookmarkers to populate data. Designer files are way cool.

Code:

---

Module AsposeSupport
  Private Const LicenseFile As String = "Aspose.Cells.lic"
  <System.Diagnostics.DebuggerStepThrough()> _
  Public Function PrepareCellsLibrary() As Boolean
      Dim Sections As New AppSections(frmMainForm.ConfigurationFile)
      Dim Result As Boolean = False
      If IO.File.Exists(LicenseFile) Then
        Dim license As Aspose.Cells.License = New Aspose.Cells.License
        license.SetLicense(LicenseFile)
        Result = True
      End If
      If Sections.DefaultEnvironment.Equals("Production") Then
        If Not Result Then
            frmMainForm.AsposeLicFileAvailable = False
        End If
      ElseIf Sections.DefaultEnvironment.Equals("Development") Then
        frmMainForm.AsposeLicFileAvailable = Result
      ElseIf Sections.DefaultEnvironment.Equals("QAC") Then
        frmMainForm.AsposeLicFileAvailable = Result
      End If

      Return Result

  End Function
  <System.Diagnostics.DebuggerStepThrough()> _
  Public Function DesignerFile(ByVal TemplateName As String) As String
      Return (From T In XDocument.Load(Application.StartupPath & "\Excel\AsposeSettings.xml")...<Report> _
                  Where T.<Name>.Value = TemplateName _
                  Select T.<Template>.Value).First
  End Function
End Module

---

The problem is that where i had the issue actually had nothing to do with excel or aspose. The code i reference below is used simply to flatten and save the image in the picturebox to a .bmp file.

Im betting that the term Drawing is used within the Aspose methods and thats what was screwing me up there.

Code:

---

Private Function SaveImage(ByVal name As String)

        Dim bmp As New Drawing.Bitmap(xray.Image.Width, xray.Image.Height)
        xray.DrawToBitmap(bmp, New Rectangle(0, 0, bmp.Width, bmp.Height))
        bmp.Save("c:\Chiro Report\" & name)

        Return 0

    End Function

---

The attached demo (VS2008, Option Strict On) shows adding data to a cell, adding a image directly below the cell and finally saves the file. The save operation uses an enumeration so you can control the versioning.

In regards to the image issue, see the last bit of code which I compiled w/o issues pointing to a PictureBox as I do not have you class.

Note PrepareCellsLibrary code is only for when you have a paid version of Cells but does no harm if you keep it in the project.

Code:

---

Public Class frmDemo
    Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Test1.xls")
    Private Sub cmdCloseForm_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles cmdCloseForm.Click
        Close()
    End Sub
    Private Sub Button1_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim Book As Workbook = New Workbook()
        Dim Sheet As Worksheet = Book.Worksheets(0)
        Dim styles As Styles = Book.Styles
        Dim styleInvoice As Style = GetStyleInvoice(styles)

        Sheet.Name = "Just Created"
        Sheet.Cells("B4").PutValue("My simple image")
        Sheet.Cells("B4").Style = styleInvoice
        Sheet.Pictures.Add(4, 1, IO.Path.Combine(Application.StartupPath, "AllLabels.bmp"))
        '
        ' You could easily make this dynamic in regards to the versioning
        '
        Book.Save(FileName, FileFormatType.Excel2003)   
End Sub
    Private Shared Function GetStyleInvoice(ByVal styles As Styles) As Style
        Dim styleIndex As Integer = styles.Add()
        Dim styleInvoice As Style = styles(styleIndex)

        styleInvoice.Font.Color = Color.Blue
        styleInvoice.Font.IsBold = True
        styleInvoice.Font.IsItalic = True
        styleInvoice.ForegroundColor = Color.LightGray
        styleInvoice.Pattern = BackgroundType.Solid

        Return styleInvoice
    End Function
    Private Sub Form1_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

        PrepareCellsLibrary()

    End Sub

    Private Function SaveImage(ByVal name As String) As Integer
        Dim bmp As New Drawing.Bitmap(PictureBox1.Image.Width, PictureBox1.Image.Height)
        PictureBox1.DrawToBitmap(bmp, New Rectangle(0, 0, bmp.Width, bmp.Height))
        bmp.Save("c:\Chiro Report\" & name)

        Return 0
    End Function
End Class

---

I noticed you are note indicating the type returned from your function which means you have option Strict set to Off (my guess is for the late binding). You should make sure all code that does not require late binding to be compiled with Option Strict On but placing said code into another module and place the statement Option Strict On at the top of the module.

Quote:

---

Originally Posted by Deslyxia

The problem is that where i had the issue actually had nothing to do with excel or aspose. The code i reference below is used simply to flatten and save the image in the picturebox to a .bmp file.

Im betting that the term Drawing is used within the Aspose methods and thats what was screwing me up there.

Code:

---

Private Function SaveImage(ByVal name As String)

        Dim bmp As New Drawing.Bitmap(xray.Image.Width, xray.Image.Height)
        xray.DrawToBitmap(bmp, New Rectangle(0, 0, bmp.Width, bmp.Height))
        bmp.Save("c:\Chiro Report\" & name)

        Return 0

    End Function

---

---