using System;
using System.IO;
using System.Web;
using System.Text;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Runtime.InteropServices;
using System.Data;
/// <summary>
///ExportToExcel 的摘要说明
/// </summary>
public class ExportToExcel
{
#region Interop
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// execl 导出
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2014-7-24 18:49:54
/// </remarks>
/// <param name="source">数据源</param>
/// <param name="fileName">文件名称</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="path">路径</param>
public static void ExportExcel(System.Data.DataTable source, string title, string fileName, string sheetName)
{
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel != null)
{
// 获取缺少的object类型值
object missing = System.Reflection.Missing.Value;
// 不显示提示对话框
excel.Application.DisplayAlerts = false;
// 创建工作薄对象
Microsoft.Office.Interop.Excel._Workbook wb = excel.Application.Workbooks.Add(1);
// 删除多余工作表
if (wb.Sheets.Count > 0) wb.Sheets.Delete();
// 获取工作表
Microsoft.Office.Interop.Excel._Worksheet sheet = (Microsoft.Office.Interop.Excel._Worksheet)excel.Worksheets.get_Item(1);
// 工作表名称
sheet.Name = sheetName;
if (sheet != null)
{
//sheet.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
// 添加表头
string[] titles = title.Split('#');
// 列数
int cols = titles.Length;
for (int i = 0; i < cols; i++)
{
//设置标题格式
//sheet.get_Range(sheet.Cells[1, i + 1], sheet.Cells[1, i + 1]).Font.Bold = true;
//sheet.get_Range(sheet.Cells[5 + i, 1], sheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
sheet.Cells[1, i + 1] = titles[i];
// 加粗
sheet.Cells[1, i + 1].Font.Bold = true;
// 字体大小
sheet.Cells[1, i + 1].Font.Size = 10;
// 列宽
sheet.Cells[1, i + 1].ColumnWidth = titles[i].Length * 2.5;
// 行高
sheet.Cells[1, i + 1].RowHeight = 30;
// 边框颜色
sheet.Cells[1, i + 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// 自动换行
sheet.Cells[1, i + 1].WrapText = true;
// 水平居右
sheet.Cells[1, i + 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// 垂直居中
sheet.Cells[1, i + 1].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// NET 4.0
Microsoft.Office.Interop.Excel.Range range = sheet.Range[sheet.Cells[1, i + 1], sheet.Cells[2, i + 1]];
range.MergeCells = true;
}
int rows = source.Rows.Count;
Microsoft.Office.Interop.Excel.Range sheetRange = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rows, cols + 1]];
sheetRange.Borders.LineStyle = 1;
sheetRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < rows + 2; i++)
{
for (int j = 2; j < cols + 2; j++)
{
sheet.Cells[i, j - 1] = source.Rows[i - 2][j - 2].ToString();
try
{
string a = source.Rows[i - 2][j - 2].ToString();
string b = source.Rows[i - 1][j - 2].ToString();
if (a == b)
{
sheet.Range[sheet.Cells[i, j - 1], sheet.Cells[i - 1, j - 1]].MergeCells = true;
sheet.Range[sheet.Cells[i, j - 1], sheet.Cells[i - 1, j - 1]].Value = sheet.Cells[i, j - 1];
}
}
catch { }
}
}
}
// 获取桌面路径
string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
string path = dir + "\\" + fileName + ".xls";
if (File.Exists(path))
File.Delete(path);
wb.SaveAs(path, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
// 清除Excel进程
KillExcel(excel);
}
else
{
}
}
/// <summary>
/// 清除Excel进程
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2014-7-24 19:30:57
/// </remarks>
/// <param name="excel">清除Excel进程</param>
private static void KillExcel(Microsoft.Office.Interop.Excel.Application excel)
{
// 获取Excel窗口句柄
IntPtr hwnd = new IntPtr(excel.Hwnd);
if (hwnd != IntPtr.Zero)
{
// Excel线程ID
int threadID = 0;
GetWindowThreadProcessId(hwnd, out threadID);
// 获取Excel进程
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(threadID);
// 清除
p.Kill();
}
}
#endregion
}
/// <summary>
/// 导出辅助类
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2014-7-24 19:37:32
/// </remarks>
public class UserExport
{
private System.Data.DataTable _source;
/// <summary>
/// 数据源
/// </summary>
public System.Data.DataTable source
{
get { return _source; }
set { _source = value; }
}
private string _title;
/// <summary>
/// 表头名称 以#分割
/// </summary>
public string title
{
get { return _title; }
set { _title = value; }
}
private string _fileName;
/// <summary>
/// 文件名称
/// </summary>
public string fileName
{
get { return _fileName; }
set { _fileName = value; }
}
private string _sheetName;
/// <summary>
/// 工作薄名称
/// </summary>
public string sheetName
{
get { return _sheetName; }
set { _sheetName = value; }
}
/// <summary>
/// 数据数据导出
/// </summary>
public void ExportExcel()
{
ExportToExcel.ExportExcel(this._source, this._title, this._fileName, this._sheetName);
}
}
下载地址ExportExcel.rar