在ASP.NET中将数据库中数据导出为Excel表格

在ASP.NET中，经常会遇到数据库中数据导出为Excel表格的问题。以下代码为页面“导出”按钮的后台响应事件，以后类似事件可以如此写。

注意：其中副标题“时间”的计算，本算法通过将数据库中year和month合并为一个整型数（1~9月份要改为01~09），然后记录最大值和最小值。最后最小值为起始年月，最大值为终止年月。

protected void btnExport_Click(object sender, EventArgs e)
    {
        string cChangming = ddlDepartment.SelectedItem.Value.ToString();
        string cYear = ddlYearlist.SelectedItem.Value.ToString();
        string cMonth = ddlMonthlist.SelectedItem.Value.ToString();
        object missing = Missing.Value;

        DateTime beforeTime;
        DateTime afterTime;

        string cWherestr = " 1=1 ";
        if (cChangming != "全部")
        {
            cWherestr = cWherestr + " and changmingid = '" + cChangming + "'";
        }
        if (cYear != "全部")
        {
            cWherestr = cWherestr + " and year = " + cYear;
        }
        if (cMonth != "全部")
        {
            cWherestr = cWherestr + " and month = " + cMonth;
        }

        DataSet cds = DataAccess.GetDataList_Export(cWherestr);//获取数据

        int rowCount = cds.Tables[0].Rows.Count;  //源DataTable行数
        int colCount = cds.Tables[0].Columns.Count; //源DataTable列数

        beforeTime = DateTime.Now;
        Excel.Application cexcelapp = new Excel.Application();
        cexcelapp.Visible = false;
        afterTime = DateTime.Now;

        String templetFile = System.Configuration.ConfigurationSettings.AppSettings["TempletFilePath"]+ "test.xls";

Excel.Workbook workBook = cexcelapp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                                missing, missing, missing, missing, missing, missing, missing, missing, missing);
       
        //得到WorkSheet对象
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
        Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
        sheet.Name = "XXXX月报";

        /*表头添加时间段.****************************Start*********************************
         *Add by woo, 20101128
         *将年月合并为一个整型数，进而判断最大日期和最小日期.
         *注意：对于1~9月在合并为整数时，需要变为01~09。Modified by woo,20110101*/
        int minDate = 205012;
        int maxDate = 190001;
       
        for (int k = 0; k < rowCount; k++)
        {
            string strYear = cds.Tables[0].Rows[k][0].ToString();
            string strMonth = cds.Tables[0].Rows[k][1].ToString();

            if (1 == strMonth.Trim().Length)
            {
                strMonth = "0" + strMonth;
            }

            string temp;
            temp = strYear + strMonth;
            if (int.Parse(temp) < minDate)
            {
                minDate = int.Parse(temp);
            }
            if (int.Parse(temp) > maxDate)
            {
                maxDate = int.Parse(temp);
            }
        }

        string timeLab = "";
        if (minDate != maxDate)
        {
            //截取minDate和maxDate，前四位为年，后两位为月
            timeLab = minDate.ToString().Substring(0, 4) + "年" + minDate.ToString().Substring(4, 2) + "月---";
            timeLab += maxDate.ToString().Substring(0, 4) + "年" + maxDate.ToString().Substring(4, 2) + "月";
        }
        else
        {
            timeLab = minDate.ToString().Substring(0, 4) + "年" + minDate.ToString().Substring(4, 2) + "月";
        }    
       
        sheet.Cells[2, 1] = "时间:" + timeLab;

        /*****************************************End*****************************************/

        int startRow = 7;
        int startColumn = 1;

        for (int i = 0; i < rowCount; i++)
        {
            for (int j = 0; j < colCount; j++)
            {
                string cstr = cds.Tables[0].Rows[i][j].ToString();
                //sheet.Cells[startRow + i, startColumn +j] = cstr;
                if (cds.Tables[0].Rows[i][j].GetType() == System.Type.GetType("System.Double"))
                {
                    sheet.Cells[startRow + i, startColumn + j] = Convert.ToDouble(cstr);
                }
                else if (cds.Tables[0].Rows[i][j].GetType() == System.Type.GetType("System.Int32"))
                {
                    sheet.Cells[startRow + i, startColumn + j] = Convert.ToInt32(cstr);
                }
                else
                {
                    sheet.Cells[startRow + i, startColumn + j] = cstr;
                }
            }
        }

        //Borders.LineStyle 单元格边框线
        Excel.Range excelRange = sheet.get_Range(sheet.Cells[7, 1], sheet.Cells[rowCount+6, 32]);
        //单元格边框线类型(线型,虚线型)
        excelRange.Borders.LineStyle = 1;

        string newid = Guid.NewGuid().ToString();
        string outputFile = System.Configuration.ConfigurationSettings.AppSettings["SaveFilePath"] + newid + ".xls";

        //输出Excel文件并退出
        try
        {
            workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
            workBook.Close(null, null, null);
            cexcelapp.Workbooks.Close();
            cexcelapp.Application.Quit();
            cexcelapp.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(cexcelapp);

            workBook = null;
            cexcelapp = null;

            GC.Collect();
        }
        catch(Exception e1)
        {
            throw e1;
        }
        finally
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;

                if (startTime > beforeTime && startTime < afterTime)
                {
                    myProcess.Kill();
                }
            }
        }

        FileInfo DownloadFile = new FileInfo(outputFile);
        Response.Clear();
        Response.ClearHeaders();
        Response.Buffer = false;
        Response.ContentType = "application/octet-stream";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("XXXX月报.xls", System.Text.Encoding.UTF8));
        Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
        Response.WriteFile(outputFile);
        Response.Flush();

        System.IO.File.Delete(outputFile); //在服务器端删除导出的Excel文件

        Response.End();

    }