将制定内容输出为压缩后的xls文件

在项目中，我们常常有一些需求，要将一些数据输出为excel文件格式，通常我们不会去调用VSTO的dll，而是采用以下的简单代码，请看以下代码
1.最简单的，一般用于小数据量，列比较少的情况

LogManager caseLogMG = new LogManager();
            List<OperationStatEn> lstItem = caseLogMG.GetOperationStat(BeginDate, EndDate);

            if (lstItem.Count > 0)
            {
                StringWriter sw = new StringWriter();
                sw.WriteLine("操作类型\t操作次数");

                foreach (OperationStatEn item in lstItem)
                {
                    sw.WriteLine(item.Message.Trim() + "\t" + item.Quantity);
                }

                sw.Close();
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=OperateStat.xls");
                Response.ContentType = "application/ms-excel";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.Write(sw);
                Response.End();

            }

嗯，很简单，单元格之间用"\t"空开。

2.通常的做法，对列和数据量无限制，当然，数据量和列不能超过xls文件自身的限制

        /// <summary>
        /// 导出到xls文件
        /// </summary>
        /// <param name="lisItems"></param>
        private void ExportXls(List<DSCardRecoveryEn> lisItems)
        {
            if (lisItems.Count == 0)
            {
                return;
            }

            StringWriter sw = new StringWriter();
            sw.WriteLine("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>");
            sw.WriteLine("<div>");
            sw.WriteLine("<table border='1'>");
            sw.WriteLine("<tr>");
            sw.WriteLine("<th style='vnd.ms-excel.numberformat:@'>编号</th><th>姓名</th><th style='vnd.ms-excel.numberformat:@'>证件序列号</th><th>处理结果</th><th>回收方式</th><th>提货点编号</th><th>起始时间</th><th>最后修改时间</th>");
            sw.WriteLine("</tr>");

            foreach (DSCardRecoveryEn item in lisItems)
            {
                sw.WriteLine("<tr>");
                sw.WriteLine("<td style='vnd.ms-excel.numberformat:@'>" + item.DsId.Trim()
                    + "</td><td>" + item.ConsultantName.Trim()
                    + "</td><td style='vnd.ms-excel.numberformat:@'>" + item.DsCardId.Trim()
                    + "</td><td>" + StringHelper.ShowState(item.State).Trim()
                    + "</td><td>" + StringHelper.ShowRecoveryType(item.RecoveryType).Trim()
                    + "</td><td>" + StringHelper.ShowOPP(item.OppId).Trim()
                    + "</td><td>" + StringHelper.ToStantardDateTime(item.CreateDate)
                    + "</td><td>" + StringHelper.ToStantardDateTime(item.LastEditDate) + "</td>");
                sw.WriteLine("</tr>");
            }
            sw.WriteLine("</table>");
            sw.WriteLine("</div>");

            sw.Close();

            Response.Clear();
            Response.AddHeader("Content-Disposition", "attachment; filename=ListInfo.xls");
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.Write(sw);
            Response.End();
        }

也比较简单，就是当做一个html页输出了，记得，顶头的sw.WriteLine("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>");一定要有，不然可能会出现乱码问题。

3.如果数据量比较大，也可以先将xls文件压缩成zip后再输出给用户

        protected void btnExportOperationDetail_Click(object sender, EventArgs e)
        {
            try
            {
                string uniqueTag = Guid.NewGuid().ToString();
                ID5CaseLogManager caseLogMG = new ID5CaseLogManager();
                List<ID5CaseLogEn> lstItem = caseLogMG.GetCaseIdByDate(BeginDate, EndDate);

                if (lstItem.Count != 0)
                {
                    string path = Server.MapPath("Upload/");
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }

                    string zipedpath = path + uniqueTag + ".zip";

                    if (!File.Exists(zipedpath))
                    {
                        SaveDataTableAsFile(path + uniqueTag + ".xls", lstItem);
                        string[] pathArray = {path + uniqueTag + ".xls"};
                        Function.ZipFile(pathArray, zipedpath, 3, 3);

                        File.Delete(path + uniqueTag + ".xls");
                    }

                    Response.Clear();
                    Response.AppendHeader("Content-Disposition",
                                          "attachment; filename=" +
                                          HttpUtility.UrlEncode(uniqueTag + ".zip",
                                                                System.Text.Encoding.GetEncoding("GB2312")));
                    Response.WriteFile(zipedpath);
                    Response.Flush();
                    File.Delete(zipedpath);
                    Response.End();
                }
            }
            catch(Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }

/// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="lstItem"></param>
        private void SaveDataTableAsFile(string path, List<CaseLogEn> lstItem)
        {
            StringWriter sw = new StringWriter();
            sw.WriteLine("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>");
            sw.WriteLine("<div>");
            sw.WriteLine("<table border='1'>");
            sw.WriteLine("<tr>");
            sw.WriteLine("<th style='vnd.ms-excel.numberformat:@'>Id</th><th>处理结果</th><th>备注</th><th  style='vnd.ms-excel.numberformat:@'>编号</th><th  style='vnd.ms-excel.numberformat:@'>经销商编号</th><th>log创建日期</th><th>log创建人</th>");
            sw.WriteLine("</tr>");
            foreach (CaseLogEn item in lstItem)
            {
                string createDate;
                try
                { createDate = item.Createdate.Value.ToString("yyyy-MM-dd HH:mm:ss"); }
                catch
                { createDate = string.Empty; }

                sw.WriteLine("<tr>");
                sw.WriteLine("<td style='vnd.ms-excel.numberformat:@'>" + item.Id
                    + "</td><td >" + item.Message.Trim()
                    + "</td><td >" + item.Note.Trim()
                    + "</td><td style='vnd.ms-excel.numberformat:@'>" + item.DirectSellerID.Trim()
                    + "</td><td style='vnd.ms-excel.numberformat:@'>" + item.DirId.Trim()
                    + "</td><td >" + createDate
                    + "</td><td >" + item.Editby.Trim() + "</td>");
                sw.WriteLine("</tr>");
            }
            sw.WriteLine("</table>");
            sw.WriteLine("</div>");

            sw.Close();

            if (File.Exists(path))
            {
                File.Delete(path);
            }

            byte[] textByte = System.Text.Encoding.GetEncoding("GB2312").GetBytes(sw.ToString());

            FileStream fileStream = File.Create(path);
            fileStream.Write(textByte, 0, textByte.Length);
            fileStream.Close();
        }