利用Myxls导出并下载Excel

最新推荐文章于 2021-09-23 11:56:58 发布

转载最新推荐文章于 2021-09-23 11:56:58 发布 · 139 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/haiyidao/archive/2011/03/30/2000189.html

本文介绍了一种利用Myxls插件生成Excel报表的方法，并提供了一份详细的代码示例。此外，还解决了在UpdatePanel中使用Response.BinaryWrite()时可能出现的问题。

参考网络上的资料，利用Myxls这个第三方插件来生成Excel报表。

事件：

代码

1 public string Export(DataTable dt)
2 {
3 XlsDocument xls = new XlsDocument();
4 string fileName = DateTime.Now.ToString("yyyyMMddhhmmss");
5 xls.FileName = fileName;
6 int rowIndex = 1;
7 Worksheet sheet = xls.Workbook.Worksheets.Add("测试表");
8 Cells cells = sheet.Cells;
9 sheet.Cells.Merge(1, 1, 1, 2);
10 Cell cell = cells.Add(1, 1, "产品信息");
11 cell.Font.Bold = true;
12 cell = cells.Add(2, 1, "产品代码");
13 cell.Font.Bold = true;
14 cell = cells.Add(2, 2, "产品名称");
15 cell.Font.Bold = true;
16 foreach (DataRow row in dt.Rows)
17 {
18 cells.Add(rowIndex + 2, 1, "" + row["PRODUCTCODE"] + "");
19 cells.Add(rowIndex + 2, 2, "" + row["PRODUCTNAME"] + "");
20
21 rowIndex++;
22 }
23
24 cell.HorizontalAlignment = HorizontalAlignments.Centered;
25 string file = System.Web.HttpContext.Current.Server.MapPath("~/Excel/");
26 xls.Save(file);
27 //xls.Send();
28 return fileName;
29
30 }

生成Excel：

代码

下载：

代码

1 public static bool ResponseFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)
2 {
3 try
4 {
5 FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
6 BinaryReader br = new BinaryReader(myFile);
7 try
8 {
9 _Response.AddHeader("Accept-Ranges", "bytes");
10 _Response.Buffer = false;
11 long fileLength = myFile.Length;
12 long startBytes = 0;
13
14
15
16 double pack = 10240; //10K bytes
17 //int sleep = 200; //每秒5次即5*10K bytes每秒
18 int sleep = (int)Math.Floor(1000 * pack / _speed) + 1;
19 if (_Request.Headers["Range"] != null)
20 {
21 _Response.StatusCode = 206;
22 string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' });
23 startBytes = Convert.ToInt64(range[1]);
24 }
25 _Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
26 if (startBytes != 0)
27 {
28 //Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
29 }
30 _Response.AddHeader("Connection", "Keep-Alive");
31 _Response.ContentType = "application/octet-stream";
32 _Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(_fileName, System.Text.Encoding.UTF8));
33
34 br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
35 int maxCount = (int)Math.Floor((fileLength - startBytes) / pack) + 1;
36
37 for (int i = 0; i < maxCount; i++)
38 {
39 if (_Response.IsClientConnected)
40 {
41 _Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
42 Thread.Sleep(sleep);
43 }
44 else
45 {
46 i = maxCount;
47 }
48 }
49 }
50 catch(Exception)
51 {
52 return false;
53 }
54 finally
55 {
56 br.Close();
57
58 myFile.Close();
59 }
60 }
61 catch
62 {
63 return false;
64 }
65 return true;
66 }

没想到，别人调试通过的代码，还是报错：

Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled;

又经过一番Google，然后有点迷迷糊糊的猜测，是否是Response.BinaryWrite()与ajax冲突造成？

所以只须在UpdatePanel下设置“asp:PostBackTrigger”的“ControlID”为指定的控件名称即可，如：
<Triggers>
<asp:PostBackTrigger ControlID="btnSave" />
</Triggers>

然后 OK！

参考1：http://www.cnblogs.com/dongyongjing/archive/2007/03/20/681411.html

参考2：http://hi.baidu.com/%BA%CD%C4%E3%D2%BB%C6%F0%D7%D4%B1%A9/blog/item/577d28328ec47952ac4b5f5a.html

转载于:https://www.cnblogs.com/haiyidao/archive/2011/03/30/2000189.html