Excel Operation

using System;
using System.Collections;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace forecast.components
{
/// <summary>
/// Summary description for Class1.
/// </summary>
///
public enum COLOR
{
SpringGreen= 0x7FFF00,
LightTur= 0xFFFFCC,
White= 0xFFFFFF,
Red= 0x0000FF,
Yellow= 0x00FFFF,
Green= 0x66CC99,
Blue= 0xFF0000,
Gray1= 0x808080,
Gray2       = 0x999999,
Gray3= 0xDCDCDC,
DarkGray= 0xA9A9A9,
Black= 0x000000,
YellGreen= 0x32CD9A,
RoyalBlue= 0xE16941,
}

public enum POSITION
{
HCenter= 0,
HLeft= 1,
HRight= 2,
VCenter = 3,
VTop    = 4,
VBottom= 5,
}

public class ExcelOperate
{
private bool m_bolFlag;
protected Application m_appExcel;
protected Workbooks m_wkbs;
protected Workbook m_wkb;
protected Sheets m_sheets;
protected Worksheet m_wksheet;
private string m_strFileName;

public ExcelOperate()
{
m_strFileName = "";
m_bolFlag = false;
}

public bool Open()
{
bool bol;

bol = true;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

try
{
GC.Collect();// clean up any other excel guys hangin' around...
m_appExcel = new Application();
m_appExcel.Visible = false;
m_wkbs = m_appExcel.Workbooks;
m_wkb = m_wkbs.Add(XlWBATemplate.xlWBATWorksheet);
m_sheets = m_wkb.Worksheets;
}
catch
{
MemoryClear();
bol = false;
}
return bol;
}

public bool Open (string p_strFileName)
{
bool bol;
this.m_strFileName = p_strFileName;
bol = true;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

try
{
GC.Collect();// clean up any other excel guys hangin' around...
m_appExcel = new Application();
m_appExcel.Visible = false;
m_wkbs = m_appExcel.Workbooks;
m_wkb = m_wkbs.Open(p_strFileName,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value);
m_sheets = m_wkb.Worksheets;
m_wksheet = (Worksheet)m_sheets.get_Item(1);

}
catch
{
MemoryClear();
bol = false;
}
return bol;
}

public void SetProtection(string p_strPassword)
{
try
{
m_wksheet.Protect(p_strPassword,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}

}

public void SetRangeProtection(string p_strRangeBegin,string p_strRangeEnd,bool p_blnProtection)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Locked = p_blnProtection;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public string GetCellData(string strPosition)
{
try
{
string strvalue = "";
Range rng = m_wksheet.get_Range(strPosition,strPosition);
if (rng.value2 != null)
strvalue = Convert.ToString(rng.value2);
return strvalue;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void Close()
{
//m_wkbs.Close();
//if(m_strFileName != "")
//m_wkb.Close(true,m_strFileName,Missing.value);

if(m_appExcel != null)
MemoryClear();

}

protected void MemoryClear()
{

m_appExcel.Quit();
if (m_appExcel != null)System.Runtime.InteropServices.Marshal.ReleaseComObject (m_appExcel);
if (m_wksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (m_wksheet);
if (m_wkb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (m_wkb);
m_wkbs = null;
m_wkb = null;
m_sheets = null;
m_appExcel = null;
m_wksheet = null;
GC.Collect();  // force final cleanup!
}

public void AddNextSheet()
{
try
{
m_sheets.Add(Missing.value,Missing.value,1,XlSheetType.xlWorksheet);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void GetWorkSheet(string p_strName)
{
try
{
if(m_bolFlag == true)
m_sheets.Add(Missing.value,Missing.value,1,XlSheetType.xlWorksheet);
m_wksheet = (Worksheet)m_sheets.get_Item(1);
m_wksheet.Name = p_strName;
m_bolFlag = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SaveExcel(string p_strFileName)
{
try
{
File.Delete(p_strFileName);
m_wkb.SaveAs(p_strFileName,XlFileformat.xlWorkbookNormal,
Missing.value,Missing.value,false,false,
XlSaveAsAccessMode.xlShared,Missing.value,Missing.value,
Missing.value,Missing.value,Missing.value);
m_strFileName = p_strFileName;
m_wkb.Close(true,p_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SaveExcelExclusive(string p_strFileName)
{
try
{
File.Delete(p_strFileName);
m_wkb.SaveAs(p_strFileName,XlFileformat.xlWorkbookNormal,
Missing.value,Missing.value,false,false,
XlSaveAsAccessMode.xlExclusive,Missing.value,Missing.value,
Missing.value,Missing.value,Missing.value);
m_strFileName = p_strFileName;
m_wkb.Close(true,p_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void CloseWorkbook()
{
try
{
m_wkb.Close(false,this.m_strFileName,Missing.value);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void WriteLineData(string p_strRangeBegin,string p_strRangeEnd,Array p_aryData)
{
object[] args = new object[1];
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
args[0] = p_aryData;
range.GetType().InvokeMember("value",BindingFlags.SetProperty,null,range,args);
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeBackColor(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Interior.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontName(string p_strRangeBegin,string p_strRangeEnd,string p_strFontName)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Name = p_strFontName;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeSummary(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;

try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.value2 = "=SUM(" + p_strRangeBegin + ":" + p_strRangeEnd + ")";
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeHyperLink(string p_strRangeBegin,string p_strRangeEnd,string p_strHyperLink)
{
Range range;
string strvalue;
strvalue = "";
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
strvalue = Convert.ToString(range.value2);
range.Hyperlinks.Add(range,"",p_strHyperLink,"",strvalue);
range.Font.Name = "Arial";
range.Font.Bold = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontColor(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontBold(string p_strRangeBegin,string p_strRangeEnd,bool p_bolBold)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Bold = p_bolBold;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontSize(string p_strRangeBegin,string p_strRangeEnd,int p_intSize)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Size = p_intSize;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWrapText(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.WrapText = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetSheetZoom(int p_intZoom,string p_strName)
{
try
{
m_wksheet.Application.ActiveWindow.Zoom = p_intZoom;
m_wksheet.Cells.Font.Name = p_strName;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeFontPosition(string p_strRangeBegin,string p_strRangeEnd,POSITION p_enuHType,POSITION p_enuVType)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
//Horizontal
switch(p_enuHType)
{
case POSITION.HCenter:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case POSITION.HLeft:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;
break;
case POSITION.HRight:
range.Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
default:
break;
}
//Vertical
switch(p_enuVType)
{
case POSITION.VCenter:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;
break;
case POSITION.VTop:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignTop;
break;
case POSITION.VBottom:
range.Cells.VerticalAlignment = XlVAlign.xlVAlignBottom;
break;
default:
break;
}
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeHeight(string p_strRangeBegin,string p_strRangeEnd,double p_fltHeight)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.RowHeight = p_fltHeight;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWidth(string p_strRangeBegin,string p_strRangeEnd,double p_fltWidth)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.ColumnWidth = p_fltWidth;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}
      
public void SetRangeformatType(string p_strRangeBegin,string p_strRangeEnd,string p_strformat)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.Numberformat = p_strformat;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeWidthHeight(string p_strRangeBegin,string p_strRangeEnd,double p_fltWidth,double p_fltHeight)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.ColumnWidth = p_fltWidth;
range.RowHeight = p_fltHeight;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeBorders(string p_strRangeBegin,string p_strRangeEnd,COLOR p_enuColor)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Cells.Borders.Color = p_enuColor;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeItatic(string p_strRangeBegin,string p_strRangeEnd)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Font.Italic = true;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetRangeCell(string p_strRangeBegin,string p_strRangeEnd,double p_fltHeight,string p_strFontName,object p_strvalue,bool p_bolBold,int p_intSize)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.RowHeight = p_fltHeight;
range.Font.Name = p_strFontName;
range.value2 = p_strvalue;
range.Font.Bold = p_bolBold;
range.Font.Size = p_intSize;
}
catch(Exception err)
{
MemoryClear();
throw err;
}
}

public void SetMergeRange(string p_strRangeBegin,string p_strRangeEnd,
COLOR p_enuBackColor,COLOR p_enuFondColor,COLOR p_enuBrold,int p_intFontSize,
bool p_bolBold,object p_strvalue,POSITION p_enuHType,POSITION p_enuVType)
{
Range range;
try
{
range = m_wksheet.get_Range(p_strRangeBegin,p_strRangeEnd);
range.Merge(Missing.value);
range.value2 = p_strvalue;
switch(p_enuHType)
{
case POSITION.HCenter:
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case POSITION.HLeft:
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
break;
case POSITION.HRight:
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
default:
break;
}
//Vertical
switch(p_enuVType)
{
case POSITION.VCenter:
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
break;
case POSITION.VTop:
range.VerticalAlignment = XlVAlign.xlVAlignTop;
break;
case POSITION.VBottom:
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
break;
default:
break;
}
range.Borders.Color = p_enuBrold;
range.Font.Bold = p_bolBold;
range.Interior.Color = p_enuBackColor;
range.Font.Color = p_enuFondColor;
range.Font.Size = p_intFontSize;
range.Font.Name  = "Arial";

}
catch(Exception err)
{
MemoryClear();
throw err;
}
}
}
}