导出Excel的四种方法

//取得整个报表的标题

mySheet.Cells[2,2] = title;

//设置整个报表的标题格式

mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Bold = true;

mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Size = 22;

//设置报表表格为最适应宽度

mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Select();

mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Columns.AutoFit();

//设置整个报表的标题为跨列居中

mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).Select();

mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;

//绘制边框

mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Borders.LineStyle = 1;

mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗

mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗

mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗

mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗

myBook.Save();

myBook.Close( true,outFilePath,true);

System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);

System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);

System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

myApp.Quit();

GC.Collect();

}

#endregion

}

4.另外，这就是另外一种电脑小技巧了，建一个SqlServer的数据源，利用Excel的外部数据源让Excel自己从数据库取数据：

以下是代码片段：

public void ExportToExcel(string pstrSql)

{

Excel.Application pApplication;

Excel._Workbook xBk;

Excel._Worksheet xSt;

Excel._QueryTable xQt;

string ExcelConn = "ODBC;DRIVER=SQL Server;SERVER=localhost;UID=sa;PWD=;APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=me;DATABASE=pubs";

pApplication = new Excel.ApplicationClass();

xBk = pApplication.Workbooks.Add(true);

xSt = (Excel._Worksheet)xBk.ActiveSheet;

pApplication.Cells[2,2] = this.title;

xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Bold = true;

xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Name = "黑体";

xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Size = 22;

xQt = xSt.QueryTables.Add(ExcelConn,xSt.get_Range(pApplication.Cells[4,2],pApplication.Cells[4,2]),pstrSql);

xQt.Name = "导出EXCEL";

xQt.FieldNames = true;

xQt.RowNumbers = false;

xQt.FillAdjacentFormulas = false;

xQt.PreserveFormatting = false;

xQt.BackgroundQuery = true;

xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;

xQt.AdjustColumnWidth = true;

xQt.RefreshPeriod = 0;

xQt.PreserveColumnInfo = true;

xQt.Refresh(xQt.BackgroundQuery);

pApplication.Visible = true;

}

    这里的pstrSql指的是sql语句。

　　本文来自 http://www.qihang56.com 谢谢支持

上一页  [1] [2]