Question

怎么用C#把数据库中的数据导出到EXCEL

Answer 1

首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。
public class DataChangeExcel
{
/// <summary>
/// <b style="color:#000;background:#ffcc99">数据库</b>转为<b style="color:#fff;background:#6600cc">excel</b>表格
/// </summary>
/// <param name="dataTable"><b style="color:#000;background:#ffcc99">数据库</b><b style="color:#fff;background:#cc0000">数据</b></param>
/// <param name="SaveFile"><b style="color:#fff;background:#0033cc">导出</b><b style="color:#000;background:#ff66ff">的</b><b style="color:#fff;background:#6600cc">excel</b>文件</param>
public static void DataSetToExcel(DataTable dataTable, string SaveFile)
{
<b style="color:#fff;background:#6600cc">Excel</b>.Application <b style="color:#fff;background:#6600cc">excel</b>;

<b style="color:#fff;background:#6600cc">Excel</b>._Workbook workBook;

<b style="color:#fff;background:#6600cc">Excel</b>._Worksheet workSheet;

object misValue = System.Reflection.Missing.Value;

<b style="color:#fff;background:#6600cc">excel</b> = new <b style="color:#fff;background:#6600cc">Excel</b>.ApplicationClass();

workBook = <b style="color:#fff;background:#6600cc">excel</b>.Workbooks.Add(misValue);

workSheet = (<b style="color:#fff;background:#6600cc">Excel</b>._Worksheet)workBook.ActiveSheet;

int rowIndex = 1;

int colIndex = 0;

//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;

<b style="color:#fff;background:#6600cc">excel</b>.Cells[1, colIndex] = col.ColumnName;
}

//取得表格<b style="color:#000;background:#66ff99">中</b><b style="color:#000;background:#ff66ff">的</b><b style="color:#fff;background:#cc0000">数据</b>
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;

colIndex = 0;

foreach (DataColumn col in dataTable.Columns)
{
colIndex++;

<b style="color:#fff;background:#6600cc">excel</b>.Cells[rowIndex, colIndex] =

row[col.ColumnName].ToString().Trim();

//设置表格内容居中对齐
workSheet.get_Range(<b style="color:#fff;background:#6600cc">excel</b>.Cells[rowIndex, colIndex],

<b style="color:#fff;background:#6600cc">excel</b>.Cells[rowIndex, colIndex]).HorizontalAlignment =

<b style="color:#fff;background:#6600cc">Excel</b>.XlVAlign.xlVAlignCenter;
}
}

<b style="color:#fff;background:#6600cc">excel</b>.Visible = false;

workBook.SaveAs(SaveFile, <b style="color:#fff;background:#6600cc">Excel</b>.XlFileFormat.xlWorkbookNormal, misValue,

misValue, misValue, misValue, <b style="color:#fff;background:#6600cc">Excel</b>.XlSaveAsAccessMode.xlExclusive,

misValue, misValue, misValue, misValue, misValue);

dataTable = null;

workBook.Close(true, misValue, misValue);

<b style="color:#fff;background:#6600cc">excel</b>.Quit();

PublicMethod.Kill(<b style="color:#fff;background:#6600cc">excel</b>);//调用kill当前<b style="color:#fff;background:#6600cc">excel</b>进程

releaseObject(workSheet);

releaseObject(workBook);

releaseObject(<b style="color:#fff;background:#6600cc">excel</b>);

}

private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
}