操作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 代码做部分修改