Asp.net操作Excel

来源：互联网发布：linux无法解压zip 编辑：程序博客网时间：2024/05/16 10:38
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
0 0