查看: 824|回复: 0

[.NET开发] asp.net excel导出功能

发表于 2018-3-22 08:00:01
以下是我在项目开发中所做的关于Excel导出功能,不足之处还望大家指正,相互学习
  1. protected void btn_Export_Click(object sender, EventArgs e)
  2. {
  3. string FileID = "";
  4. if (Request.QueryString["fujian"] != null && Request.QueryString["fujian"].ToString() != "")
  5. {
  6. FileID = Request.QueryString["fujian"].ToString();
  7. }
  8. string title = GetFileTile(FileID);//文件标题
  9. Workbook workbook = new Workbook(); //工作簿
  10. Worksheet sheet = workbook.Worksheets[0]; //工作表
  11. Cells cells = sheet.Cells; //单元格
  12. //列宽
  13. cells.SetColumnWidth(0, 20.00);
  14. cells.SetColumnWidth(1, 30.00);
  15. cells.SetColumnWidth(2, 30.00);
  16. cells.Merge(0, 0, 1, 3);//合并单元格
  17. cells[0, 0].PutValue(title + "/文件已学人员名单");
  18. cells[0, 1].PutValue("");
  19. cells[0, 2].PutValue("");
  20. cells[1, 0].PutValue("序号");
  21. cells[1, 1].PutValue("姓名");
  22. cells[1, 2].PutValue("时间");
  23. string sql = "order by StudyTime";
  24. ds = PublishBLL.GetCommentCount(FileID, sql);
  25. if (ds.Tables[0].Rows.Count > 0)
  26. {
  27. for (int i = 1; i < ds.Tables[0].Rows.Count + 1; i++)
  28. {
  29. //Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式
  30. //styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
  31. //styleTitle.Font.Name = "宋体";//文字字体
  32. //styleTitle.Font.Size = 18;//文字大小
  33. //styleTitle.Font.IsBold = true;//粗体
  34. cells[i + 1, 0].PutValue("" + i.ToString().PadLeft(3, '0') + "");
  35. cells[i + 1, 1].PutValue("" + ds.Tables[0].Rows[i - 1]["StudyName"].ToString() + "");
  36. cells[i + 1, 2].PutValue("" + ds.Tables[0].Rows[i - 1]["StudyTime"].ToString() + "");
  37. }
  38. }
  39. string filename = "统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
  40. string path = ConfigurationManager.AppSettings["StudyFile"] + @"\" + filename + "";
  41. workbook.Save(path);//保存到硬盘
  42. #region 下载
  43. System.IO.MemoryStream ms1 = workbook.SaveToStream();//生成数据流
  44. byte[] bt1 = ms1.ToArray();
  45. string fileName = "统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";//客户端保存的文件名
  46. //以字符流的形式下载文件
  47. Response.ContentType = "application/vnd.ms-excel"; //通知浏览器下载文件而不是打开
  48. Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
  49. Response.BinaryWrite(bt1);
  50. Response.Flush();
  51. Response.End();
  52. #endregion
  53. }
复制代码

  



回复

使用道具 举报