C#中如何將數據庫數據直接導出到Excel？

/// 導出Excel格式

/// </summary>

/// <param name="gridView"></param>

/// <param name="filename"></param>

private static void ExportGridViewToExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView, string filename)

{

SaveFileDialog sfd = new SaveFileDialog();

filename += DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();

sfd.FileName = filename;

sfd.Filter = "Excel files (*xls) | *.xls";

sfd.RestoreDirectory = true;

if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null)

{

int rowIndex = 2;

int colIndex = 0;

int colNum = gridView.Columns.Count;

System.Reflection.Missing miss = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application xlapp = new

Microsoft.Office.Interop.Excel.Application(true);

xlapp.Visible = true;

Microsoft.Office.Interop.Excel.Workbooks mBooks =

(Microsoft.Office.Interop.Excel.Workbooks)xlapp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook mBook =

(Microsoft.Office.Interop.Excel.Workbook)mBooks.Add(miss);

Microsoft.Office.Interop.Excel.Worksheet mSheet =

(Microsoft.Office.Interop.Excel.Worksheet)mBook.ActiveSheet;

Microsoft.Office.Interop.Excel.Range mRange = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value);

//設置對齊方式

mSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;

//設置文字自動換行

//mSheet.Cells.WrapText = true;

//設置標題行高度

((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20;

//設置數據行高度

((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["2:" + gridView.RowCount + 1, System.Type.Missing]).RowHeight = 16;

//設置字體大小（10號字體）

mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]].Font.Size = 10;

//設置單元格邊框

Microsoft.Office.Interop.Excel.Range range1 =gridView.RowCount>17?mSheet.Range[mSheet.Cells[1, 2], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]]:mSheet.Range[mSheet.Cells[1, 2], mSheet.Cells[19, gridView.Columns.Count]];

range1.Borders.LineStyle = 1;

//設置標題顏色

mSheet.Range[mSheet.Cells[2, 1], mSheet.Cells[1, gridView.Columns.Count]].Font.Color =Color.Blue;

//將-￥000格式化成￥-000

string InOutInvSum = Convert.ToDecimal(gridView.GetRowCellValue(irow, gridView.Columns[icol])).ToString("C").Replace("￥", "").Replace("全角￥", "");

mSheet.Cells[rowIndex, colIndex] = "￥" + InOutInvSum;

Microsoft.Office.Interop.Excel.Range rH = mSheet.get_Range("C1","L1");

rH.Merge(0);

//報表的標題vlookup

mSheet.Cells[1,3] = "";

//寫標題

for (int row = 2; row <= gridView.Columns.Count; row++ )

{

mSheet.Cells[2, row] = gridView.Columns[row - 1].GetTextCaption();

}

try

{

for (int irow = 0; irow < gridView.Columns.Count; irow++)

{

rowIndex++;

colIndex = 1;

for (int icol = 1; icol < gridView.Columns.Count; icol++)

{

colIndex++;

mSheet.Cells[rowIndex, colIndex] = gridView.GetRowCellValue(irow, gridView.Columns[icol]);

}//end 列

}//end 行

mBook.SaveAs(sfd.FileName,

Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss,

Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

miss, miss, miss, miss, miss);

//retuen true;

}//end TRY

catch (Exception ex)

{

throw new Exception(ex.Message);

}

finally

{

mBook.Close(false, miss, miss);

mBooks.Close();

xlapp.Quit();

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mRange);

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mSheet);

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mBook);

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mBooks);

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlapp);

GC.Collect();

}

}

}