(1)插入一定数量的行数
((Excel._Worksheet)excel.Sheets [1]).get_Range (excel.Cells [1,1],excel.Cells[4,1]).EntireRow .Insert (missing,missing);
(2)输出报表,如果Report1存在,则把模板存为Report2.xls
string tempPath2,tempPath1= Application.StartupPath .Trim ()+"//Export//Report";
for(int k=1;;k++)
{
tempPath2=tempPath1+k.ToString() +".xls";
if(File.Exists(tempPath2)==false)
{
break;
}
}
(3)把模板拷贝到Template目录下,路径和文件名为tempPath2
FileInfo mode=new FileInfo(Application.StartupPath.Trim()+"//Template//template.xls");
mode.CopyTo(tempPath2,true);
(4)打开这个从模板拷贝到Template目录的文件
Excel.Application myExcel=new Excel.ApplicationClass();
object missing=Missing.Value;
myExcel.Application.Workbooks.Open (tempPath2,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
(5)用Excel自带的查询分析器快速填充数据
Excel._Workbook xBk;
Excel._Worksheet xSt;
Excel._QueryTable xQt;
string Conn = "ODBC;DRIVER=SQL Server;SERVER=ServerName;UID=sa;PWD=;APP=WinXP;WSID=ClientName;DATABASE=DatabaseName";
string SelectString = "SELECT * from tablename";
xBk = myExcel.Workbooks[1];
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xQt = xSt.QueryTables.Add(Conn,xSt.get_Range(myExcel.Cells[11,2],myExcel.Cells[11,2]),SelectString);
xQt.Name = "";
xQt.FieldNames = false;
xQt.RowNumbers = false;
xQt.FillAdjacentFormulas = false;
xQt.PreserveFormatting = true;
xQt.BackgroundQuery = true;
xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
xQt.AdjustColumnWidth = false;
xQt.RefreshPeriod = 0;
xQt.PreserveColumnInfo = true;
xQt.Refresh(xQt.BackgroundQuery);
(6)设置边框
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
(7)增加sheet并命名sheet
private void Button1_Click(object sender, System.EventArgs e)
{
Excel.Application myExcel=new Excel .ApplicationClass ();
myExcel.Visible =true;
myExcel.Workbooks .Add (true);
for(int i=0;i<5;i++)//增加多个SHEET
{
myExcel.Sheets .Add (Missing.Value,Missing.Value,Missing.Value,Missing.Value );
}
for(int i=1;i<=6;i++)
{
for(int j=1;j<4;j++)
myExcel.Cells[i,j]=i+j;
((Excel.Worksheet)myExcel.Sheets[i]).Name =i.ToString ();//修改SHEET名
}
}
(8)插入图片(例如插入D盘根目录下的tt.bmp,必须添加引用:Microsoft.Office Object Library对象库,并在窗体最前面有using Office = Microsoft.Office.Core;)
xSt.Shapes .AddPicture ("D://tt.bmp",Microsoft.Office .Core .MsoTriState.msoFalse ,Microsoft.Office .Core .MsoTriState.msoTrue ,10,10,150,150);