using System;
namespace Microsoft.ExpOffice02
{
/// <summary>
/// Summary description for Class1.
/// </summary>
public class ExportExcel : IDisposable
{
private Excel.ApplicationClass ExcelApp = null;
private Excel.Workbooks myWorkbooks = null;
private Excel.Workbook myWorkbook = null;
private Excel.Worksheet myWorksheet = null;
// Track whether Dispose has been called.
private bool disposed = false;
public ExportExcel()
{
//no initialization as of yet...
ExcelApp = new Excel.ApplicationClass();
myWorkbooks = ExcelApp.Workbooks;
myWorkbook = myWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
myWorksheet = (Excel.Worksheet) ExcelApp.ActiveSheet;
}
// Implement IDisposable.
// Do not make this method virtual.
// A derived class should not be able to override this method.
private void ComDestroy(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch {}
finally
{
o = null;
}
}
public void Dispose()
{
Dispose(true);
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}
// Dispose(bool disposing) executes in two distinct scenarios.
// If disposing equals true, the method has been called directly
// or indirectly by a users code. Managed and unmanaged resources
// can be disposed.
// If disposing equals false, the method has been called by the
// runtime from inside the finalizer and you should not reference
// other objects. Only unmanaged resources can be disposed.
private void Dispose(bool disposing)
{
// Check to see if Dispose has already been called.
if(!this.disposed)
{
// If disposing equals true, dispose all managed
// and unmanaged resources.
if(disposing)
{
// Dispose managed resources.
}
// Call the appropriate methods to clean up
// unmanaged resources here.
// If disposing is false,
// only the following code is executed
ComDestroy(myWorksheet);
myWorkbook.Close(false, null, null);
ComDestroy(myWorkbook);
myWorkbooks.Close();
ComDestroy(myWorkbooks);
ExcelApp.Quit();
ComDestroy(ExcelApp);
GC.Collect();
}
disposed = true;
}
// Use C# destructor syntax for finalization code.
// This destructor will run only if the Dispose method
// does not get called.
// It gives your base class the opportunity to finalize.
// Do not provide destructors in types derived from this class.
~ExportExcel()
{
// Do not re-create Dispose clean-up code here.
// Calling Dispose(false) is optimal in terms of
// readability and maintainability.
Dispose(false);
}
public void Show()
{
ExcelApp.Visible = true;
}
public void Hide()
{
ExcelApp.Visible = false;
}
public void WriteCell(int row, int column, string val)
{
WriteCell(row, column, val, false, false, false);
}
public void WriteCell(int row, int column, string val, bool bold, bool underline, bool italic)
{
/* It doesnt look like this would be right, but you actually AREE
* selecting the cell of the cells... it all gets into how you call
* things and keep from building up threads... you could directly
* call myWorksheet.Cells[1, 1]="SomeValue"; but this would result
* in an instance of EXCEL.EXE remaining in the task manager because
* you are orphaning two instances of Excel.Range, the first being the
* myWorksheet.Cells, the second becing the [1, 1] which is a range
* of its own... weird I know, but this is by Microsoft design and
* if you want to keep your thread clean, do it this way! */
//Rows and Columns are Base 1;
if(row < 1 || column < 1)
throw new System.ArgumentOutOfRangeException("Row/Column", "Row or Column must be 1 or greater!");
Excel.Range myCells = (Excel.Range) myWorksheet.Cells;
Excel.Range myCell = (Excel.Range) myCells[row, column];
Excel.Font myCellFont = myCell.Font;
myCellFont.Bold = bold;
myCellFont.Underline = underline;
myCellFont.Italic = italic;
myCell.Value = val;
ComDestroy(myCellFont);
ComDestroy(myCell);
ComDestroy(myCells);
}
public void AutoFitColumn(int column)
{
if(column < 1)
throw new System.ArgumentOutOfRangeException("Column", "Column must be 1 or greater!");
Excel.Range myCells = (Excel.Range) myWorksheet.Cells;
Excel.Range myCell = (Excel.Range) myCells[1, column];
Excel.Range myColumn = (Excel.Range) myCell.EntireColumn;
myColumn.AutoFit();
ComDestroy(myColumn);
ComDestroy(myCell);
ComDestroy(myCells);
}
public void Save(string path, bool overwrite)
{
bool exists = System.IO.File.Exists(path);
if(exists==true && overwrite==false)
{
throw new System.IO.IOException("A file already exists under the given name and overwrite is set to false, unable to save!");
}
if(exists==true && overwrite==true)
{
System.IO.File.Delete(path);
}
myWorkbook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, false, null, null);
}
}
}
