This class can control Excel very well. Read the content.
/// <Summary>
/// Excel processing class
/// </Summary>
Public class excelhander
{
Public excelhander ()
{
//
// Todo: Add constructor logic here
//
}
Private string alist = "abcdefghijklmnopqrstuvwxyz ";
Public String getaix (int x, int y)
{
Char [] achars = alist. tochararray ();
If (x> = 26) {return "";}
String S = "";
S = S + achars [x-1]. tostring ();
S = S + Y. tostring ();
Return S;
}
Public void setvalue (int x, int y, string align, string text)
{
Excel. Range = sheet. get_range (this. getaix (x, y), Miss );
Range. set_value (Miss, text );
If (align. toupper () = "center ")
{
Range. horizontalalignment = excel. xlhalign. xlhaligncenter;
}
If (align. toupper () = "Left ")
{
Range. horizontalalignment = excel. xlhalign. xlhalignleft;
}
If (align. toupper () = "right ")
{
Range. horizontalalignment = excel. xlhalign. xlhalignright;
}
}
Public void setvalue (int x, int y, string text)
{
Excel. Range = sheet. get_range (this. getaix (x, y), Miss );
Range. set_value (Miss, text );
}
Public void setvalue (int x, int y, string text, system. Drawing. Font font, system. Drawing. color)
{
This. setvalue (X, Y, text );
Excel. Range = sheet. get_range (this. getaix (x, y), Miss );
Range. Font. size = font. size;
Range. Font. Bold = font. Bold;
Range. Font. Color = color;
Range. Font. Name = font. Name;
Range. Font. italic = font. italic;
Range. Font. Underline = font. Underline;
}
Public void insertrow (INT y)
{
Excel. Range = sheet. get_range (getaix (1, Y), getaix (25, y ));
Range. Copy (MISs );
Range. insert (Excel. xldirection. xldown, Miss );
Range. get_range (getaix (1, Y), getaix (25, y ));
Range. Select ();
Sheet. paste (Miss, miss );
}
Public void past ()
{
String S = "a, B, c, d, e, f, g ";
Sheet. paste (sheet. get_range (this. getaix (10, 10), Miss), S );
}
Public void setborder (INT X1, int Y1, int X2, int Y2, int width)
{
Excel. Range = sheet. get_range (this. getaix (x1, Y1), this. getaix (X2, Y2 ));
Range. Borders. Weight = width;
}
Public void mergecell (INT X1, int Y1, int X2, int Y2)
{
Excel. Range = sheet. get_range (this. getaix (x1, Y1), this. getaix (X2, Y2 ));
Range. Merge (true );
}
Public excel. Range getrange (INT X1, int Y1, int X2, int Y2)
{
Excel. Range = sheet. get_range (this. getaix (x1, Y1), this. getaix (X2, Y2 ));
Return range;
}
Private missing Miss = missing. value; // ignored parameter olenull
Public static missing missvalue = missing. value;
Private excel. Application m_objexcel; // Excel application instance
Private excel. workbooks m_objbooks; // worksheet set
Private excel. Workbook m_objbook; // worksheet of the current operation
Private excel. worksheet sheet; // table of the current operation
Public excel. worksheet currentsheet
{
Get
{
Return sheet;
}
Set
{
This. Sheet = value;
}
}
Public excel. workbooks currentworkbooks
{
Get
{
Return this. m_objbooks;
}
Set
{
This. m_objbooks = value;
}
}
Public excel. Workbook currentworkbook
{
Get
{
Return this. m_objbook;
}
Set
{
This. m_objbook = value;
}
}
Public void openexcelfile (string filename)
{
M_objexcel = new excel. Application ();
Usercontrol (false );
M_objexcel.workbooks.open (
Filename,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss,
Miss );
M_objbooks = (Excel. workbooks) m_objexcel.workbooks;
M_objbook = m_objexcel.activeworkbook;
Sheet = (Excel. worksheet) m_objbook.activesheet;
}
Public void usercontrol (bool usercontrol)
{
If (m_objexcel = NULL) {return ;}
M_objexcel.usercontrol = usercontrol;
M_objexcel.displayalerts = usercontrol;
M_objexcel.visible = usercontrol;
}
Public void createexcefile ()
{
M_objexcel = new excel. Application ();
Usercontrol (false );
M_objbooks = (Excel. workbooks) m_objexcel.workbooks;
M_objbook = (Excel. workbook) (m_objbooks.add (MISs ));
Sheet = (Excel. worksheet) m_objbook.activesheet;
}
Public void saveas (string filename)
{
M_objbook.saveas (filename, miss,
Miss, Excel. xlsaveasaccessmode. xlnochange,
Excel. xlsaveconflictresolution. xllocalsessionchanges,
Miss, miss );
// M_objbook.close (false, miss, miss );
}
Public void releaseexcel ()
{
M_objexcel.quit ();
Marshal. releasecomobject (m_objexcel );
Marshal. releasecomobject (m_objbooks );
Marshal. releasecomobject (m_objbook );
Marshal. releasecomobject (sheet );
GC. Collect ();
}
}