C#导出Excel

原文来自：http://www.cnblogs.com/zhangqifeng/archive/2009/06/10/1500537.html

//使用命名空间 Microsoft.Office.Interop.Excel;

///

/// 数据导出到excel处理类

///

class OutputExcel

{

#region 自定义方法

///

/// 导出数据方法

///

/// 预导出的数据

/// 预导出的名字

/// 是否成功

public bool SaveAs(System.Data.DataTable Source, string fileName)

{

SaveFileDialog saveFileDialog = new SaveFileDialog();

saveFileDialog.Filter = "OutputExcel (*.xls)|*.xls";

saveFileDialog.FilterIndex = 0;

saveFileDialog.RestoreDirectory = true;

saveFileDialog.Title = "Output Path";

saveFileDialog.FileName = fileName+" Info";

if (saveFileDialog.ShowDialog() == DialogResult.OK)

{

string strName = saveFileDialog.FileName;

if (strName != null && strName.Trim() != String.Empty)

{

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

Excel.ApplicationClass excel = new Excel.ApplicationClass();

/*隐藏excel界面*/

excel.Visible = false;

if (excel == null)

{

MessageBox.Show("Excel start fail！", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

return false;

}

Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;

Excel.Workbook book = (Excel.Workbook)(books.Add(miss));

Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;

sheet.Name = "Mark Data";

SetSheet(sheet, Source);

try

{

sheet.SaveAs(strName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);

book.Close(false, miss, miss);

books.Close();

excel.Quit();

}

catch (Exception ex)

{

MessageBox.Show("Output data fail! \r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

return false;

}

finally

{

/*关闭资源*/

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

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

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

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

GC.Collect();

}

return true;

}

}

return false;

}

///

/// 设置表结构和数据

///

/// 指定表

/// 数据源

public void SetSheet(Excel.Worksheet sheet, System.Data.DataTable SourceDataTable)

{

sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 1]).ColumnWidth = 6;

sheet.Cells[1, 1] = "编号";

sheet.get_Range(sheet.Cells[1, 2], sheet.Cells[1, 2]).ColumnWidth = 13;

sheet.Cells[1, 2] = "日期";

sheet.get_Range(sheet.Cells[1, 3], sheet.Cells[1, 3]).ColumnWidth = 10;

sheet.Cells[1, 3] = "时间";

sheet.get_Range(sheet.Cells[1, 4], sheet.Cells[1, 4]).ColumnWidth = 50;

sheet.Cells[1, 4] = "内容";

sheet.get_Range(sheet.Cells[1, 5], sheet.Cells[1, 5]).ColumnWidth = 10;

sheet.Cells[1, 5] = "紧急";

sheet.get_Range(sheet.Cells[1, 6], sheet.Cells[1, 6]).ColumnWidth = 10;

sheet.Cells[1, 6] = "完成状态";

int rowNo = 2;

foreach (DataRow dr in SourceDataTable.Rows)

{

sheet.get_Range(sheet.Cells[rowNo, 1], sheet.Cells[rowNo, 1]).ColumnWidth = 6;

sheet.Cells[rowNo, 1] = dr["No"];

sheet.get_Range(sheet.Cells[rowNo, 2], sheet.Cells[rowNo, 2]).ColumnWidth = 13;

sheet.Cells[rowNo, 2] = dr["MarkTimeDay"];

sheet.get_Range(sheet.Cells[rowNo, 3], sheet.Cells[rowNo, 3]).ColumnWidth = 10;

sheet.Cells[rowNo, 3] = dr["MarkTimeSecond"];

sheet.get_Range(sheet.Cells[rowNo, 4], sheet.Cells[rowNo, 4]).ColumnWidth = 50;

sheet.get_Range(sheet.Cells[rowNo, 4], sheet.Cells[rowNo, 4]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

sheet.Cells[rowNo, 4] = dr["Info"];

sheet.get_Range(sheet.Cells[rowNo, 5], sheet.Cells[rowNo, 5]).ColumnWidth = 10;

sheet.Cells[rowNo, 5] = Convert.ToInt32(dr["IsWarn"]) == 1 ? "是" : "否";

sheet.get_Range(sheet.Cells[rowNo, 6], sheet.Cells[rowNo, 6]).ColumnWidth = 10;

sheet.Cells[rowNo, 6] = Convert.ToInt32(dr["State"]) == 1 ? "未完成" : "已完成";

rowNo++;

}

}

#endregion

}