using System;
using Excel;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.Reflection;
namespace KFXT.Util
{
public class ExcelOperate
{
static Excel.ApplicationClass myExcel ;
static Excel._Workbook xBk ; //工作薄
static Excel._Worksheet xSt; //工作Sheet
static Excel.AppEvents_WorkbookBeforeCloseEventHandler EventDel_BeforeBookClose;
public ExcelOperate()
{
if(myExcel==null)
{
myExcel = new Excel.ApplicationClass();
xBk = myExcel.Workbooks.Add(true);
}
else
{
try
{
myExcel.Sheets.Add(Missing.Value,Missing.Value,1,Excel.XlSheetType.xlWorksheet);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
myExcel = new Excel.ApplicationClass();
xBk = myExcel.Workbooks.Add(true);
}
}
xSt = (Excel._Worksheet)xBk.ActiveSheet;
}
public void WriteExcelTitle(string title,string subtitle,string st_zb,string st_end)
{
Object oo = false ;
try
{
Excel.Range xRange = xSt.get_Range(st_zb,st_end);
xRange.Merge(oo);
xRange.Value2 = title;
xRange.Font.Bold = true ;
xRange.HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
xRange.RowHeight = 50;
xRange.Font.Size = 18;
if (subtitle !="")
{
st_zb = this.getFrtString(st_zb) + (this.getLastNumber(st_zb) +1);
st_end = this.getFrtString(st_end) + (this.getLastNumber(st_end) +1);
xRange = xSt.get_Range(st_zb,st_end);
xRange.Merge(oo);
xRange.Value2 = subtitle;
xRange.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft ;
}
string strname="";
int shNum = 0;
string strt = "";
int p = 0;
bool flg = false;
for(int i=1;i<= xBk.Sheets.Count ;i++)
{
strname = ((Excel._Worksheet)(xBk.Sheets[i])).Name ;
if (strname.IndexOf(title,0)>=0)
{
if (shNum ==0) shNum =1;
p = this.getLastNumber(strname);
if (p >shNum) shNum = p;
}
}
if (shNum >0)
xSt.Name = title + (shNum+1 );
else
xSt.Name= title;
}
catch(Exception ex)
{
MessageBox.Show("写入标题时出错"+ex.Message);
return ;
}
}
public void WriteExcelHeader(ArrayList al)
{
Object oo = false ;
for(int m=0;m<al.Count;m++)
{
try
{
BbTitle bt = (BbTitle)al[m];
string startP = bt.getStart();
string endP = bt.getEnd();
Excel.Range xRange = xSt.get_Range(startP,endP);
xRange.Merge(oo);
xRange.Value2=bt.getTitle();
// if(bt.getBColor().Equals(Color.White))
// {
// }
// else
// {
// xSt.get_Range(bt.getStart(),bt.getEnd()).Interior.Color=System.Drawing.ColorTranslator.ToOle(bt.getBColor());
// }
//if(bt.getFontwz()!=null && bt.getFontwz().Equals("0"))
//{
// xRange.HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
//}
switch(bt.getFontwz())
{
case 0:xRange.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft ;break;
case 1:xRange.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;break;
case 2:xRange.HorizontalAlignment=Excel.XlHAlign.xlHAlignRight ;break;
}
if(bt.getFontStyle().Equals(true))
{
xRange.Font.Bold=bt.getFontStyle();
}
xRange.EntireColumn.AutoFit();
xRange.EntireRow.AutoFit();
}
catch(Exception ex)
{
MessageBox.Show("写入title时出错"+ex.Message);
return ;
}
}
}
public void WriteDataSet(DataSet ds,string dsname,int st_row,int st_col)
{
for(int i=0;i<ds.Tables[dsname].Rows.Count;i++)
{
for(int j=0;j<ds.Tables[dsname].Columns.Count;j++)
{
try
{
xSt.Cells[i+st_row,j+st_col]=ds.Tables[dsname].Rows[i][j].ToString().Trim();;
}
catch(Exception ex)
{
MessageBox.Show("写入数据出错"+ex.Message);
return ;
}
}
}
}
public void showExcel()
{
myExcel.Visible=true ;
}
public void Release()
{
EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose);
myExcel.WorkbookBeforeClose += EventDel_BeforeBookClose;
}
public void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel )
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
GC.Collect();
myExcel=null ;
}
public void WriteFomular(ArrayList lst)
{
for(int k=0;k<lst.Count;k++)
{
BbTitle bt = (BbTitle)lst[k];
try
{
xSt.get_Range(bt.getStart(),bt.getEnd()).Value2=bt.getFomular();
}
catch(Exception ex)
{
MessageBox.Show("写入公式时出错"+ex.Message);
return ;
}
}
}
/// <summary>
/// 设置格式
/// </summary>
/// <param name="st_pos"></param>
/// <param name="end_pos"></param>
/// <param name="cellformat"></param>
public void WriteFormat(string st_pos,string end_pos,string cellformat)
{
if (cellformat.Length==0) return ;
Excel.Range xRange = xSt.get_Range(st_pos,end_pos);
xRange.NumberFormatLocal=cellformat;
}
public void WriteBgColor(string st_pos,string end_pos,Color bgcolor)
{
Excel.Range xRange = xSt.get_Range(st_pos,end_pos);
xRange.Interior.Color =bgcolor.ToArgb() ;
}
/// <summary>
///框线
/// </summary>
/// <param name="st_pos"></param>
/// <param name="end_pos"></param>
/// <param name="weighted">线宽1:细线 3:粗线</param>
/// <param name="flg">标识位 0:内框线;1:外框线;5:斜线</param>
public void WriteBorder(string st_pos,string end_pos,int weighted,int flg )
{
Excel.Range srange = xSt.get_Range(st_pos,end_pos);
if (flg ==0)
{
srange.Borders[ Excel.XlBordersIndex.xlInsideHorizontal].LineStyle=Excel.XlLineStyle.xlContinuous;
srange.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle=Excel.XlLineStyle.xlContinuous;
}else if (flg == 5)
{
srange.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlContinuous;
}
else
{
srange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle=Excel.XlLineStyle.xlContinuous;
srange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight=weighted;
srange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle=Excel.XlLineStyle.xlContinuous;
srange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight=weighted;
srange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle=Excel.XlLineStyle.xlContinuous;
srange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight=weighted;
srange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle=Excel.XlLineStyle.xlContinuous;
srange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight=weighted;
}
}
int st_pos=0;
int end_pos=0;
string st="";
string end="";
string col="" ;
public void ExcelMergeCol(string col,int rownum)
{
this.col=col;
for(int i=1;i<rownum;i++)
{
this.st_pos=i;
this.end_pos=i;
if(xSt.get_Range(col+i.ToString(),col+i.ToString()).Value2!=null)
{
this.st = xSt.get_Range(col+i.ToString(),col+i.ToString()).Value2.ToString();
}
if(xSt.get_Range(col+(i+1),col+(i+1)).Value2!=null)
{
this.end=xSt.get_Range(col+(i+1),col+(i+1)).Value2.ToString();
}
GetEndPos(this.st,this.end);
if(i!=this.end_pos)
{
xSt.get_Range(col+i.ToString(),col+this.end_pos.ToString()).Merge(false);
i=this.end_pos;
}
}
}
public void setAutofit(string st_cell,string et_cell)
{
Excel.Range xRange = xSt.get_Range(st_cell,et_cell);
xRange.Columns.AutoFit();
xRange.Rows.AutoFit();
xRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter ;
}
public void ExcelMergeCol(string stDate,string endDate)
{
int srow =0,scol=0;
int erow =0,ecol=0;
string pos = "";
string preVal = "",strVal = "";
scol = this.transColNum(this.getFrtString(stDate));
srow = this.getLastNumber(stDate);
ecol = this.transColNum(this.getFrtString(endDate));
erow = this.getLastNumber(endDate);
int msr =srow,mer=0;
for(int i=scol;i<=ecol;i++)
{
msr = srow;
mer = 0;
for(int j=srow;j<=erow;j++)
{
pos = this.transColNum(i) + j.ToString();
Excel.Range xRange = xSt.get_Range(pos,pos);
object obj = xRange.Value2;
if (obj == null) continue;
strVal = xRange.Value2.ToString();
if (preVal == strVal)
{
mer = j;
xRange.Value2 = "";
if (j== erow)
{
xRange = xSt.get_Range(this.transColNum(i)+msr.ToString(),this.transColNum(i)+mer.ToString());
xRange.Merge(false);
}
}
else
{
preVal = strVal;
if (mer >0)
{
xRange = xSt.get_Range(this.transColNum(i)+msr.ToString(),this.transColNum(i)+mer.ToString());
xRange.Merge(false);
msr = mer +1;
mer =0;
}
}
}
}
}
public void GetEndPos(string st,string end)
{
if(this.st!="" && this.st.Equals(this.end))
{
this.end_pos=this.end_pos+1;
xSt.get_Range(this.col+this.end_pos.ToString(),this.col+this.end_pos.ToString()).Value2=null ;
if(xSt.get_Range(this.col+(this.end_pos+1),this.col+(this.end_pos+1)).Value2!=null)
{
this.end=xSt.get_Range(this.col+(this.end_pos+1),this.col+(this.end_pos+1)).Value2.ToString();
GetEndPos(this.st,this.end);
}
}
}
int stcol_pos=0;
int endcol_pos=0;
string stcon="";
string endcon="";
int row=0 ;
public void ExcelMergeRow(int row,int colnum)
{
this.row=row;
for(int i=1;i<colnum;i++)
{
this.stcol_pos=i;
this.endcol_pos=i;
if(xSt.get_Range(xSt.Cells[row,i],xSt.Cells[row,i]).Value2!=null)
{
this.stcon=xSt.get_Range(xSt.Cells[row,i],xSt.Cells[row,i]).Value2.ToString();
}
if(xSt.get_Range(xSt.Cells[row,i+1],xSt.Cells[row+1,i+1]).Value2!=null)
{
this.endcon=xSt.get_Range(xSt.Cells[row,i+1],xSt.Cells[row,i+1]).Value2.ToString();
}
this.GetEndColPos(this.stcon,this.endcon);
if(i!=this.endcol_pos)
{
xSt.get_Range(xSt.Cells[this.row,i],xSt.Cells[this.row,this.endcol_pos]).Merge(false);
xSt.get_Range(xSt.Cells[this.row,i],xSt.Cells[this.row,this.endcol_pos]).HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
i=this.endcol_pos;
}
}
}
public void GetEndColPos(string stcon,string endcon)
{
if(this.stcon!="" && this.stcon.Equals(this.endcon))
{
this.endcol_pos=this.endcol_pos+1;
xSt.get_Range(xSt.Cells[this.row,this.endcol_pos],xSt.Cells[this.row,this.endcol_pos]).Value2=null ;
if(xSt.get_Range(xSt.Cells[this.row,this.endcol_pos+1],xSt.Cells[this.row,this.endcol_pos+1]).Value2!=null)
{
this.endcon=xSt.get_Range(xSt.Cells[this.row,this.endcol_pos+1],xSt.Cells[this.row,this.endcol_pos+1]).Value2.ToString();
GetEndColPos(this.stcon,this.endcon);
}
}
}
private string getFrtString(string source)
{
string strVal ="";
int i =0;
for(i=0;i<source.Length;i++)
{
if ( Char.IsNumber(source,i))
{
break;
}
}
if (i==0)
strVal = "0";
else
strVal = source.Substring(0,i);
return strVal;
}
private int getLastNumber(string source)
{
string strVal ="";
bool flg = false;
int i=0;
for( i=(source.Length-1);i>=0;i--)
{
if (! Char.IsNumber(source,i))
break;
}
i++;//source.Length-1 -i;
if ( i==source.Length)
strVal = "0";
else
strVal = source.Substring(i,source.Length-i);
if (strVal == "") strVal = "0";
return int.Parse(strVal);
}
public string transColNum(int columnNum)
{
if (columnNum <= 0) return "";
int iFloor = (int) Math.Floor(columnNum / 27);
int iMod = columnNum % 26;
string strColNum = "" ;
if (iFloor > 0 )
strColNum += (char) (64 + iFloor);
if (iMod >0)
strColNum += (char) (64 + iMod);
return strColNum;
}
/// <summary>
/// 把excel列号转换为数字
/// </summary>
/// <param name="columnNum"></param>
/// <returns></returns>
public int transColNum(string columnNum)
{
if (columnNum.Length > 2) return -1;
int colN ;
char[] chColn ;
chColn = columnNum.ToCharArray();
if (chColn.Length ==2)
{
colN = (chColn[0] -64) * 26;
if (colN <= 0) return -1;
colN += (chColn[1] -64) ;
}
else
{
colN = chColn[0] -64 ;
}
return colN;
}
Excel.Range range = xSt.get_Range("A1","Q1");
range.Value2="wds";
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Color = System.Drawing.ColorTranslator.ToOle(Color.Blue);
range.Borders.Color=System.Drawing.ColorTranslator.ToOle(Color.Red);//边框色
range.Interior.Color=System.Drawing.ColorTranslator.ToOle(Color.Red);//背景色
//
range.HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
range.Font.Bold=true;
Excel.Range range = xSt.get_Range("A3","Q7");
//
range.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
}
}