你的位置:首页 > ASP.net教程

[ASP.net教程]导出数据到EXCEL并生成多个Sheet


一、准备工作

引用:Microsoft.Office.Interop.Excel

准备多个DataTable数据添加到DataSet中。

二、代码

public void CreateExcel(DataSet ds, string filePath)    {      //创建excel运行环境      Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();      Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = null;      Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = null;      ExcelApp.Visible = true;      ExcelWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);      //创建excel的sheet名称      List<string> SheetNames = new List<string>();      SheetNames.Add("人力数据分析");      SheetNames.Add("性别");      SheetNames.Add("省份");      SheetNames.Add("城市");      SheetNames.Add("页面访问人数(每天)");      SheetNames.Add("每天具体数据(小时)");      for (int i = 1; i < ds.Tables.Count; i++)        ExcelWorkBook.Worksheets.Add(); //添加新的sheet到excel中      for (int i = 0; i < ds.Tables.Count; i++)      {        int r = 1; // 初始化excel的第一行Position=1        ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets[i + 1];        //样式设置        var range = ExcelWorkSheet.get_Range("A1", "K1");        range.Font.Size = 12;        range.Font.Name = "黑体";        range.ColumnWidth = 17;   //设置单元格的宽度        //把列的名字写进sheet内        for (int col = 1; col <= ds.Tables[i].Columns.Count; col++)          ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName;        r++;        //把每一行写进excel的sheet中        for (int row = 0; row < ds.Tables[i].Rows.Count; row++) //r是excelRow,col是excelColumn        {          //Excel的行和列开始位置写Row=1 ,Col=1          for (int col = 1; col <= ds.Tables[i].Columns.Count; col++)            ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString();          r++;        }        ExcelWorkSheet.Name = SheetNames[i];      }      ExcelWorkBook.SaveAs(filePath);      ExcelWorkBook.Close();      ExcelApp.Quit();      System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet);      System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook);      System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);      GC.Collect();      foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))        process.Kill();    }

三、实现跟谷歌类似文件下载后在最下方显示下载的文件

public ActionResult OutPutExcel()    {      ILog log = LogManager.GetLogger(typeof(SystemSetController));            try      {        DateTime beginTime = Convert.ToDateTime(Request.Form["LiveBeginTime"]);        DateTime endTime = Convert.ToDateTime(Request.Form["LiveEndTime"]);        long liveId = Convert.ToInt32(Request.Form["liveId"]);        //生成的Excel名称        string fileName = string.Format("{0}.xlsx", DateTime.Now.ToString(@"yyyy-MM-dd-HHmmss"));        //获取数据        DataSet ds = _SystemSetBLL.GetLiveAnalysis(beginTime, endTime, liveId);        var filePath = Server.MapPath("~/UploadFile/file/" + fileName);        //生成EXCEL        _SystemSetBLL.CreateExcel(ds, filePath);        Response.Clear();        Response.Charset = "utf-8";        Response.HeaderEncoding = Encoding.UTF8;        Response.AddHeader("content-type", "application/x-msdownload");        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));        Response.ContentType = "application/vnd.ms-excel";        Response.BinaryWrite(System.IO.File.ReadAllBytes(filePath));        Response.End();      }      catch (Exception e)      {        log.Error("导出数据失败:" + e.Message);      }      return null;    }

无它,本人学的计算机专业,但好久没做这一行业,贴出来的有些浅薄,只为帮助有需要的人和巩固一下。