C#将Datatable导入到Excel

操作Excel注意事项：

1.  excel中大多数类型为接口，比如 Application,  worksheet，range等，所以使用该类对象的时候一定要注意对象是否存在

2. 设置excel的单元格式为文本：range.NumberFormatLocal = "@"; 如果想设置整张表的格式为文本，可以使用以下方法：Range range = sheet.Rows; range.NumberFormatLocal = "@";

3.  excel中集合的索引一般都是从1开始。

4.  缺少参数时可以使用private object _objOpt = System.Reflection.Missing.Value;补齐。

5.  如果为web应用，一定要注意配置asp.net运行帐户对excel的COM组件的访问权限。

6.  Response.End、Response.Redirect 或 Server.Transfer 方法可能会引发ThreadAbortException异常，所以在此类方法后不应该再执行其他任务。

7.  调用Workbook.Close(true, fileName, false)方法要比Workbook.SaveAs()好。

8.  把Excel.Application赋给静态变量，理论上要比每次创建Excel.Application好，因为减少了与进程外COM组件的通信过程，但是实际体验中并没有感觉快多少，这个方式有个明显的缺点就是，如果Excel进程崩溃了，将导致RPC服务不可用，即整个导出服务都无法使用，除非重新启动web应用程序。

代码示例：

根据项目要求，写了一个可以把datatabl导出为excel的类，可以满足web、win等形式的应用程序，满足自定义列，满足excel2003以上版本。代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.IO;
using System.Threading;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;


namespace MG.MES.Report.DataAccess
{
//--------------------------------------------
//
// 导出DataTable为Excel
//
// Author: YH
// Date: 2014-01-07
// Desc: web、winform
// 自定义列
// -------------------------------------------
public class ExportExcel : IDisposable
{

public enum ExcelType
{
Excel2003 = 11,
Excel2007 = 12,
Excel2010 = 14
};

public enum AppType
{
Web = 1,
Win = 2
};

private const int MAX_ROW_LENGTH_2003 = 65536;
private const int MAX_ROW_LENGTH_2007 = 1048576;

private const int MAX_COLUMN_LENGTH_2003 = 256;
private const int MAX_COLUMN_LENGTH_2007 = 16384;

private const string DEFAULT_SHEET_NAME = "数据表";

private const string FILE_EXTENTION_2003 = ".xls";
private const string FILE_EXTENTION_2007 = ".xlsx";

private bool _disposed = false;

private ExcelType _excelType = ExcelType.Excel2003;
private AppType _appType = AppType.Web;
private Application _app = null;
private Worksheet _currentSheet = null;
private Workbook _currentWorkbook = null;
private string _fileName = null;
private string _fileFullName = null;

private object _objOpt = System.Reflection.Missing.Value;

public ExportExcel()
{
Initialize(DEFAULT_SHEET_NAME);
}

public ExportExcel(string fileName)
{
if (string.IsNullOrEmpty(fileName.Trim()))
{
Initialize(DEFAULT_SHEET_NAME);
}
else
{
Initialize(fileName);
}
}

public bool Check(System.Data.DataTable table, int[] columnList, string[] headers)
{
if (null == table)
{
throw new Exception("数据表为NULL");
}

if (null != columnList && null != headers)
{
if (columnList.Length != headers.Length)
{
throw new Exception("导出列的长度必须和表头的长度一致");
}
else if (columnList.Length > table.Columns.Count || headers.Length > table.Columns.Count)
{
throw new Exception("导出列的长度、表头的长度不能超过数据表的长度");
}
}
else if (null == columnList && null != headers)
{
if (table.Columns.Count != headers.Length)
{
throw new Exception("导出列的长度必须和表头的长度一致");
}
else if (headers.Length > table.Columns.Count)
{
throw new Exception("表头的长度不能超过数据表的长度");
}
}
return true;
}

private int GetMaxRowCount(ExcelType excelType)
{
switch (excelType)
{
case ExcelType.Excel2003:
return MAX_ROW_LENGTH_2003;
case ExcelType.Excel2007:
case ExcelType.Excel2010:
return MAX_ROW_LENGTH_2007;
default:
return MAX_ROW_LENGTH_2003;
}
}

private int GetMaxColumnCount(ExcelType excelType)
{
switch (excelType)
{
case ExcelType.Excel2003:
return MAX_COLUMN_LENGTH_2003;
case ExcelType.Excel2007:
case ExcelType.Excel2010:
return MAX_COLUMN_LENGTH_2007;
default:
return MAX_COLUMN_LENGTH_2003;
}
}

private string GetFileFullName()
{
StringBuilder fileFullName = new StringBuilder();

if (AppType.Win == _appType)
{
fileFullName.Append(AppDomain.CurrentDomain.BaseDirectory).Append(_fileName);
}
else
{
fileFullName.Append(HttpContext.Current.Server.MapPath("~/Excel/")).Append(_fileName);
}

return fileFullName.ToString();
}

private string GetFileName(string fileName)
{
StringBuilder retVal = new StringBuilder(fileName);

if (ExcelType.Excel2007 <= _excelType)
{
if (AppType.Win == _appType)
{
retVal.Append("_").Append(System.DateTime.Now.ToString("yyyyMMddHHmmss")).Append(FILE_EXTENTION_2007);
}
else
{
retVal.Append("_").Append(System.DateTime.Now.ToString("yyyyMMddHHmmss")).Append(FILE_EXTENTION_2007);
}
}
else
{
if (AppType.Win == _appType)
{
retVal.Append("_").Append(System.DateTime.Now.ToString("yyyyMMddHHmmss")).Append(FILE_EXTENTION_2003);
}
else
{
retVal.Append("_").Append(System.DateTime.Now.ToString("yyyyMMddHHmmss")).Append(FILE_EXTENTION_2003);
}
}

if (retVal.Length == 0)
{
throw new Exception("文件名为空");
}

return retVal.ToString();
}

private void CreateFile()
{
try
{
// 调用Workbook.Close(true, fileName, false)方法要比Workbook.SaveAs()好
_currentWorkbook.Close(true, _fileFullName, false);
//:ATTE: 保存的是workbook，而不是application
// _currentWorkbook.SaveAs(_fileFullName, _objOpt, _objOpt, _objOpt, _objOpt, _objOpt, XlSaveAsAccessMode.xlNoChange, _objOpt, _objOpt, _objOpt, _objOpt, _objOpt);

}
catch
{
throw new Exception("保存Excel出错");
}
finally
{
RemoveUnmanagedObject();
}
}

private void Export(string fileName)
{

CreateFile();

if (AppType.Web == _appType)
{
Export_with_Web(fileName);
}
else
{
Export_with_Win(fileName);
}

//Clear(); //:ATTE: 很奇怪，不会运行到此处。
}

private void Clear()
{
if (File.Exists(_fileFullName))
{
File.Delete(_fileFullName);
}
}

private void CreateHeader(System.Data.DataTable table, int[] columnList, string[] headers)
{
if (null != columnList)
{
for (int columnIndex = 0; columnIndex < columnList.Length; columnIndex++)
{
if (null != headers)
{
_currentSheet.Cells[1, columnIndex + 1] = headers[columnIndex];
}
else
{
_currentSheet.Cells[1, columnIndex + 1] = table.Columns[columnList[columnIndex]].ColumnName;
}
}
}
else
{
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{ if (null != headers)
{
_currentSheet.Cells[1, columnIndex + 1] = headers[columnIndex];
}
else
{
_currentSheet.Cells[1, columnIndex + 1] = table.Columns[columnIndex].ColumnName;
}
}
}
}
private void FillData(System.Data.DataTable table, int[] columnList)
{
int rowCount = GetMaxRowCount(_excelType) > table.Rows.Count ? table.Rows.Count : GetMaxRowCount(_excelType);
int columnCount = GetMaxColumnCount(_excelType) > table.Columns.Count ? table.Columns.Count : GetMaxColumnCount(_excelType);

if (null != columnList)
{
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
for (int columnIndex = 0; columnIndex < columnList.Length; columnIndex++)
{
_currentSheet.Cells[rowIndex + 2, columnIndex + 1] = table.Rows[rowIndex][columnList[columnIndex]].ToString();
}
}
}
else
{
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
_currentSheet.Cells[rowIndex + 2, columnIndex + 1] = table.Rows[rowIndex][columnIndex].ToString();
}
}
}
}

private ExcelType GetExcelType()
{
ExcelType retVal;
int versionNum = Convert.ToInt32(Convert.ToSingle(_app.Version));

try
{
retVal = (ExcelType)versionNum;
}
catch
{
throw new ApplicationException("未知Excel版本");
}
return retVal;
}

public void Initialize(string fileName)
{
if (null == HttpContext.Current)
{
_appType = AppType.Win;
}

_app = new ApplicationClass();

_app.Visible = false;

_excelType = GetExcelType();

_app.Workbooks.Add(true);

_currentWorkbook = _app.Workbooks[1];
_currentSheet = _app.Worksheets[1] as Worksheet;

_currentSheet.Name = fileName;
_fileName = GetFileName(fileName);
_fileFullName = GetFileFullName();

Range range = _currentSheet.Columns;
range.NumberFormatLocal = "@";
}

private void Export_with_Web(string fileName)
{
FileStream fs = null;
try
{
HttpResponse response = HttpContext.Current.Response;

response.Clear();
response.Buffer = true;

response.ContentType = "application/octet-stream";
response.AppendHeader("content-disposition", "attachment; filename=" + HttpContext.Current.Server.UrlPathEncode(fileName));

fs = new FileStream(_fileFullName, System.IO.FileMode.Open);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, (int)fs.Length);

response.BinaryWrite(buffer);

response.End(); //:ATTE: 可能引发ThreadAbortException异常，而导致线程结束，不要在response.End()方法后再执行其他代码，否则有可能不会被执行
}
catch (ThreadAbortException ex)
{
string errMsg = ex.Message;
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Close();
//Delete the file from the Server, in order to not create too many files
FileInfo file = new FileInfo(_fileFullName);
if (file.Exists)
{
file.Delete(); //删除单个文件
}

#if DEBUG
#else
Clear();
#endif
}

}

private void Export_with_Win(string fileName)
{

}

public void Dispose()
{
if (!_disposed)
{
Dispose(true);
GC.SuppressFinalize(this);
}
_disposed = true;
}

private void Dispose(bool disposing)
{
if (disposing)
{
// managed resource
}
RemoveUnmanagedObject();
}

~ExportExcel()
{
Dispose(false);
}

private void RemoveUnmanagedObject()
{
if (null != _app)
{
_app.Quit(); //:ATTE: Quit不一定代表进程结束，或者释放了对文件的引用。
Marshal.FinalReleaseComObject(_app);
_app = null;
}
_currentSheet = null;
_currentWorkbook = null;
}

public void Export(System.Data.DataTable table)
{
this.Export(table, null, null);
}

public void Export(System.Data.DataTable table, int[] columnList)
{
this.Export(table, columnList, null);
}

// index start from 0
public void Export(System.Data.DataTable table, int[] columnList, string[] headers)
{
if (Check(table, columnList, headers))
{

CreateHeader(table, columnList, headers);

FillData(table, columnList);

Export(_fileName);
}
}
}
}

转自：http://www.cnblogs.com/ningj3/archive/2010/04/19/1715283.html  代码做部分修改