C#导出Excel（转载）

最新推荐文章于 2024-07-01 14:54:48 发布

原创最新推荐文章于 2024-07-01 14:54:48 发布 · 114 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

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

C# 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种使用Microsoft.Office.Interop.Excel将数据导出到Excel文件的方法，包括创建工作簿、设置表结构和数据填充。

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

/// <summary>
/// 数据导出到excel处理类
/// </summary>
class OutputExcel
{
#region 自定义方法
/// <summary>
/// 导出数据方法
/// </summary>
/// <param name="Source">预导出的数据</param>
/// <param name="fileName">预导出的名字</param>
/// <returns>是否成功</returns>
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;
}

/// <summary>
/// 设置表结构和数据
/// </summary>
/// <param name="sheet">指定表</param>
/// <param name="SourceDataTable">数据源</param>
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
}