asp.net操作excel表

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using BLL;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace CheckNum
{
    public partial class Test1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {        }

        private void CreateExcel()
        {
            ExcelOperate excelOperate = new ExcelOperate();
            Excel.Application app = new Excel.Application();
            if (app == null)
            {
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Excel.Workbooks workbooks = app.Workbooks; //工作薄集合
            Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧) 工作薄
            Excel.Sheets sheets = workbook.Worksheets;//工作表集合
            Excel._Worksheet worksheet = (Excel._Worksheet)sheets.get_Item(1);//工作表
            worksheet.Copy(Missing.Value, workbook.Sheets.get_Item(1)); //复制工作表
            worksheet.Copy(Missing.Value, worksheet);//复制工作表
            Excel._Worksheet worksheet1 = (Excel._Worksheet)sheets.get_Item(2);//工作表2


            worksheet1.Name = "工作表2";
            if (worksheet == null)
            {
                return;
            }
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 4]).Merge(Missing.Value); //横向合并
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "导出EXCEL测试一";
            excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体
            excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
            excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色
            excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小
            excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
            worksheet.Cells[2, 1] = "序号";
            worksheet.Cells[2, 2] = "公司";
            worksheet.Cells[2, 3] = "部门";
            excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑体
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]);
            excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);//背景色
            int rowNum = 0;
            DataView dv = GetData001();
            for (int i = 0; i < dv.Count; i++)
            {
                rowNum = i + 1;
                worksheet.Cells[3 + i, 1] = rowNum;
                worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
                worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();


                excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
                excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
                worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);


            }        
            //excelOperate.SetColumnWidth(worksheet, "A", 10);
            //excelOperate.SetColumnWidth(worksheet, "B", 20);
            //excelOperate.SetColumnWidth(worksheet, "C", 20);
            worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[5, 1]).Merge(Missing.Value); //纵向合并
            worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[5, 1]).Value2 = "测试纵向合并";
            worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[5, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);


            worksheet.Name = "导出EXCEL测试一"; //表命名


            worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[1, 4]).Merge(Missing.Value);
            worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[1, 4]).Value2 = "工作表2";
            worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[1, 4]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            string tick = DateTime.Now.Ticks.ToString();


            //save_path = temp_path + "//" + tick + ".xls";
            string save_path = "~/ExcelFiles/" + tick + ".xls";
            workbook.SaveAs(Server.MapPath(save_path), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
            app.Quit();
        }
        public DataView GetData001()
        {
            DataTable newTable = new DataTable();
            newTable.Columns.Add("COM_NAME", typeof(string));
            newTable.Columns.Add("DEPT_NAME", typeof(string));
            for (int i = 0; i < 50; i++)
            {
                DataRow newRow = newTable.NewRow();
                newRow["COM_NAME"] = "金算盘集团新锐力" + i.ToString();
                newRow["DEPT_NAME"] = "研发部" + i.ToString();
                newTable.Rows.Add(newRow);
            }
            return newTable.DefaultView;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            CreateExcel();
        }
    }
}

二 ExcelOperate操作类

using System;
using System.Web;
using Excel=Microsoft.Office.Interop.Excel;


/// <summary>
/// ExcelOperate 的摘要说明。Excel操作函数
/// </summary>
public class ExcelOperate
{
private object mValue = System.Reflection.Missing.Value;


public ExcelOperate()
{
//
// TODO: 在此处添加构造函数逻辑
//
}


/// <summary>
/// 合并单元格
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void Merge(Excel._Worksheet CurSheet,object objStartCell,object objEndCell)
{
            CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);  
}
/// <summary>
/// 设置连续区域的字体大小
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strStartCell">开始单元格</param>
/// <param name="strEndCell">结束单元格</param>
/// <param name="intFontSize">字体大小</param>
public void SetFontSize(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize) 
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
            
}


/// <summary>
/// 横向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlLandscape(Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlLandscape;


}
/// <summary>
/// 纵向打印
/// </summary>
/// <param name="CurSheet"></param>
public void xlPortrait(Excel._Worksheet CurSheet)
{
CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlPortrait;
}




/// <summary>
/// 在指定单元格插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="Cell">单元格 如Cells[1,1]</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteCell(Excel._Worksheet CurSheet,object objCell, object objValue) 
{
CurSheet.get_Range(objCell, mValue).Value2 = objValue;


}


/// <summary>
/// 在指定Range中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="StartCell">开始单元格</param>
/// <param name="EndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteRange(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
}

/// <summary>
/// 合并单元格，并在合并后的单元格中插入指定的值
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="objValue">文本、数字等值</param>
public void WriteAfterMerge(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue)
{
CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;


}


/// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Excel._Worksheet CurSheet,object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}




/// <summary>
/// 单元格自动换行
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void AutoWrapText(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell,objEndCell).WrapText=true;
}


/// <summary>
/// 设置整个连续区域的字体颜色
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="clrColor">颜色</param>
public void SetColor(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, System.Drawing.Color clrColor)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
}


        /// <summary>
        /// 设置整个连续区域的单元格背景色
        /// </summary>
        /// <param name="CurSheet"></param>
        /// <param name="objStartCell"></param>
        /// <param name="objEndCell"></param>
        /// <param name="clrColor"></param>
        public void SetBgColor(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        }


/// <summary>
/// 设置连续区域的字体名称
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
public void SetFontName(Excel._Worksheet CurSheet,object objStartCell, object objEndCell,string fontname)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Name=fontname;
}


/// <summary>
/// 设置连续区域的字体为黑体
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBold(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
}
       

/// <summary>
/// 设置连续区域的边框：上下左右都为黑色连续边框
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBorderAll(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;

CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;

CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;

CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;


}


/// <summary>
/// 设置连续区域水平居中
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignCenter(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}

/// <summary>
/// 设置连续区域水平居左
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignLeft(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}


/// <summary>
/// 设置连续区域水平居右
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignRight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment=Excel.XlHAlign.xlHAlignRight;
}




/// <summary>
/// 设置连续区域的显示格式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="strNF">如"#,##0.00"的显示格式</param>
public void SetNumberFormat(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, string strNF)
{
CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
}

/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="strColID">列标识，如A代表第一列</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Excel._Worksheet CurSheet,string strColID, double dblWidth)
{
((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item",System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns,new object[]{(strColID + ":" + strColID).ToString()})).ColumnWidth = dblWidth;
}


/// <summary>
/// 设置列宽
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblWidth">宽度</param>
public void SetColumnWidth(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblWidth)
{
CurSheet.get_Range(objStartCell,objEndCell).ColumnWidth=dblWidth;
}




/// <summary>
/// 设置行高
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="dblHeight">行高</param>
public void SetRowHeight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblHeight)
{
CurSheet.get_Range(objStartCell,objEndCell).RowHeight=dblHeight;
}



/// <summary>
/// 为单元格添加超级链接
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strAddress">链接地址</param>
/// <param name="strTip">屏幕提示</param>
/// <param name="strText">链接文本</param>
public void AddHyperLink(Excel._Worksheet CurSheet,object objCell, string strAddress, string strTip, string strText)
{
CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell),strAddress, mValue, strTip, strText);
}


/// <summary>
/// 另存为xls文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void Save(Excel._Workbook CurBook,string strFilePath)
{
CurBook.SaveCopyAs(strFilePath);
}


/// <summary>
/// 保存文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Excel._Workbook CurBook,string strFilePath)
{
            CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
}


/// <summary>
/// 另存为html文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveHtml(Excel._Workbook CurBook,string strFilePath)
{
            CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}




/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet,Excel._Workbook CurBook,Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;


CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
           
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch(System.Exception ex)
{
    HttpContext.Current.Response.Write( "在释放Excel内存空间时发生了一个错误:"+ex);
}
finally
{
foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))                    
                 //if (pro.StartTime < DateTime.Now)
                 pro.Kill();
            }
            System.GC.SuppressFinalize(this);


}
}