asp.net导出excel

添加引用Microsoft.Office.Interop.Excel

object miss = System.Reflection.Missing.Value;
        ExcelTemplet templet = new ExcelTemplet();
        Microsoft.Office.Interop.Excel.Application excel = templet.excelObj;//创建excel对象
        Microsoft.Office.Interop.Excel.Workbook newWorkBook = excel.Workbooks.Add(miss);//添加新工作簿
        //Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)newWorkBook.Worksheets.Add(miss,miss,miss,miss);
        Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);//获得默认工作表Sheet1

对单元格的操作

 

//当前操作的单元格
    public Microsoft.Office.Interop.Excel.Range range;

    //用于产生Excel表中列标号
    private string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

	public ExcelTemplet()
	{
        this.excelObj = new Microsoft.Office.Interop.Excel.Application();
	}

    public ExcelTemplet(Microsoft.Office.Interop.Excel.Application excel)
    {
        this.excelObj = excel;
    }


    /// <summary>
    /// 获得描述区域的字符
    /// </summary>
    /// <param name="column">列号</param>
    /// <param name="row">行号</param>
    /// <returns></returns>
    private string getColumnMark(int column, int row)
    {
        char[] abcChars = abc.ToCharArray();

        if (column < 26)
        {
            return (abcChars[column - 1].ToString() + row.ToString());
        }
        else
        {
            int n = column / 26 - 1;
            int i = column % 26 - 1;
            string s = abcChars[n].ToString() + abcChars[i].ToString();
            s += row.ToString();

            return s;
        }
    }

    
    /// <summary>
    /// 获得单元格编辑区域
    /// </summary>
    /// <param name="row">行</param>
    /// <param name="column">列</param>
    public void getRange(int strRow, int strCol, int endRow, int endCol)
    {
        object strCell = getColumnMark(strCol,strRow);
        object endCell = getColumnMark(endCol, endRow);
        range = sheetObj.get_Range(strCell, endCell);
    }

    /// <summary>
    /// 单元格赋值
    /// </summary>
    /// <param name="column">列号</param>
    /// <param name="row">行号</param>
    /// <param name="value">值</param>
    public void setCellValue(int column, int row, string value)
    {
        object sell = getColumnMark(column, row);
        range = sheetObj.get_Range(sell,miss);
        range.set_Value(miss,value);
        //sheetObj.Cells[y, x] = value;

    }

    /// <summary>
    /// 单元格赋值1
    /// </summary>
    /// <param name="value">值</param>
    public void setCellValue1(string value)
    {
        range.set_Value(miss, value);
    }

    /// <summary>
    /// 设定单元格水平对齐方式
    /// </summary>
    /// <param name="align">对齐方式（CENTER、LEFT、RIGHT）</param>
    public void setCellHorizonalAlign(string align)
    {
        switch (align.ToUpper())
        {
            case "CENTER":
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                break;
            case "LEFT":
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                break;
            case "RIGHT":
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                break;
        }
    }

    /// <summary>
    /// 设定单元格垂直对齐方式
    /// </summary>
    /// <param name="align">对齐方式（CENTER, TOP, BOTTOM）</param>
    public void setCellVerticalAlign(string align)
    {
        switch (align.ToUpper())
        {
            case "CENTER":
                range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                break;
            case "TOP":
                range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
                break;
            case "BOTTOM":
                range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignBottom;
                break;
        }
    }


    /// <summary>
    /// 设置单元格字体格式和颜色
    /// </summary>
    /// <param name="font">字体样式包括字体样式大小等</param>
    /// <param name="color">字体颜色</param>
    public void setCellFontFormat(System.Drawing.Font font, System.Drawing.Color color)
    {
        range.Font.Size = font.Size;
        range.Font.Color = color;
        range.Font.Bold = font.Bold;
        range.Font.Name = font.Name;
        range.Font.Italic = font.Italic;
        range.Font.Underline = font.Underline;
    }


    /// <summary>
    /// 合并单元格
    /// </summary>
    /// <param name="strRow">起始行</param>
    /// <param name="strCol">起始列</param>
    /// <param name="endRow">终止行</param>
    /// <param name="endCol">终止列</param>
    public void mergeCells(int strRow, int strCol, int endRow, int endCol)
    {
        object strCell = getColumnMark(strCol, strRow);
        object endCell = getColumnMark(endCol, endRow);
        range = sheetObj.get_Range(strCell, endCell);
        range.MergeCells = true;
    }

保存生成的工作簿并释放资源

string savePath = Server.MapPath("..") + "\\temp\\" + DateTime.Now.ToString("yyyyMMddhhmmssf") + ".xlsx";
        //newWorkBook.SaveCopyAs("E:\\Project\\aaa\\Excel\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx");
        newWorkBook.SaveCopyAs(savePath);
        newWorkBook.Close(false, miss, miss);
        excel.Quit();

        System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorkBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);

        newWorkBook = null;
        excel = null;
        sheet = null;

        System.GC.Collect();//垃圾收集

 

下载excel

private void exportExcel()
    {
        FileInfo file = new FileInfo(path);

        HttpResponse responst = HttpContext.Current.Response;
        responst.Clear();
        responst.Buffer = true;
        responst.Charset = "UTF-8";
        responst.ContentEncoding = Encoding.UTF8;

        responst.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("供热运行日报表",Encoding.UTF8) + ".xlsx");
//文件名为汉字        responst.AppendHeader("Content-Length", file.Length.ToString());

        responst.ContentType = "application/ms-excel";

        responst.WriteFile(file.FullName);
        responst.Flush();

        //60秒后自动删除生成的Excel文件
        timer = new System.Timers.Timer(60000);
        timer.Elapsed += new ElapsedEventHandler(delExcelTemp);

        timer.AutoReset = false;//执行一次
        timer.Enabled = true;

        responst.End();

        

    }



    private void delExcelTemp(object source, ElapsedEventArgs e)
    {
        //Console.WriteLine("dddd");
        //if (File.Exists(path))
        //{
            //Console.WriteLine("aaaa");
            File.Delete(path);
        //}
    }

说明：1、用.net标准控件button 则直接将上面代码放到单击事件

           2、用jqery的ajax方式下载则需要在页面中增加<form></form>标签，ajax事件

           $.ajax({
                type: "post",
                url: "ajax/heatingReportAjax.aspx",
                data: "data=" + JSON.stringify(data),
                beforeSend: function (xmlHttp) {
                    xmlHttp.setRequestHeader("If-Modified-Since", "0");
                    xmlHttp.setRequestHeader("Cache-Control", "no-cache");
                },
                /*dataType:"application/ms-excel",*/
                success: function (json) {
                    //alert(json);
                    var url = "reports/export.aspx?path=" + json;
                    var f = document.getElementById("export");
                    //f.action = "\"" + json + "\"";
                    f.action = url;
                    f.submit();
                    //window.location.href = json;
                }
            });