c# 导出操作思想

导出——很多人都做过，但是只要真正领悟的人才知道究竟是怎么实现的：

核心思想： 将数据从数据库里取出来，然后赋值输出，在市面上的导出更是五花八门，我同事用npoi导出时需要经过大量的计算，行数列数也相当的多，导致速度超过了10分钟，项目经理通过另一种方式将导出优化，稍后将方法给出，今天主要写个基础的。
首先自己写一个方法：

把DataTable数据转化为Excel文件流

        /// <summary>
        /// 把DataTable数据转化为Excel文件流
        /// </summary>
        /// <param name="dt">DataTable源数据</param>
        /// <param name="sheetName"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static MemoryStream DataToExcelAsFile(DataTable dt, string sheetName, string fileName)
        {
            int sheetCount = 1;
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //XSSFWorkbook book = new XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = null;//book.CreateSheet(sheetName + "1");
            int columnsLength = dt.Columns.Count;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i % 50000 == 0)
                {
                    sheet = book.CreateSheet(string.Format("{0}({1})", sheetName, sheetCount));
                    sheetCount++;
                }
                int index = i - ((sheetCount - 2) * 50000);
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(index);
                var rw = dt.Rows[i];

                for (int j = 0; j < columnsLength; j++)
                {
                    if ((rw[j] is DBNull) || rw[j] == null)
                    {
                        row.CreateCell(j).SetCellValue("");
                    }
                    else
                    {
                        row.CreateCell(j).SetCellValue(rw[j].ToString());
                    }
                }
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;
            return ms;
        }

 

在使用的时候：

		public ActionResult Getdataexport()
		{
			string citys = Request["citys"];
			string region = Request["region"];
			string StartDate = Request["StartDate"];
			string EntDate = Request["EntDate"];
			List<B_BARCODE> barcode = RetailerHelpClass.GetBarCode();
			List<SalesScaleClass> sales = RetailerHelpClass.GetSalesScale(citys, region, StartDate, EntDate);
			List<B_BARCODE> bartitle = RetailerHelpClass.GetBarCodeTitle();


			//标题
			List<string> stu = new List<string>() { "代表处", "省份", "市区", "铺货商", };
			foreach (var item in bartitle)
			{
				stu.Add(item.B_item_code);
			}
			DataTable tbbody = new DataTable("tb");
			foreach (var item in stu)
				tbbody.Columns.Add(item);
			var rw = tbbody.NewRow();
			for (int i = 0; i < stu.Count; i++)
				rw[i] = stu[i];
			tbbody.Rows.Add(rw);
			//标题

			int k = 0;
			foreach (var item in sales)
			{
				int pps = 0;
				var r = tbbody.NewRow();
				r[pps++] = item.regionname;
				r[pps++] = item.PROVINCENAME;
				r[pps++] = item.cityname;
				r[pps++] = item.COMPANYNAME;
				string sal = item.COMPANYNAME;
				foreach (var title in bartitle)
				{
					var tab = barcode.Where(o => o.DITRIBUTION_NAME == sal && o.B_item_code == title.B_item_code).ToList();
					r[pps++] = tab.Count();
				}
				tbbody.Rows.Add(r);
				k++;
			}
			string filename = "数据指标导出" + DateTime.Now.ToString("yyyyMMdd");
			var ms = ExcelClass.DataToExcelAsFile(tbbody, "Sheet1", filename + ".xls");
			ms.Position = 0;
			Dictionary<string, string> files = new Dictionary<string, string>();
			//files.Add("E:\\" + "\\" + filename + ".xls", filename + ".xls");
			return File(ms, "application/x-msdownload;Charset=utf-8", filename + ".xls");
		}