文章导航绿软下载站软件下载安卓资源苹果资源专题

您当前所在位置:必赢亚洲www565net首页网络编程.Net编程 → .net导出海量数据到execl文件

.net导出海量数据到execl文件

时间:2015/6/28来源:必赢亚洲www565net作者:网管联盟我要评论(0)

  对于导出Execl文件大量时 一般做法时间会很长, 下面个人收藏方法,可导海量数据

  protected void CreateExecl(string swhere,string title) {

  string saveFileName = Server.MapPath("http://www.cnblogs.com/uploads/file/" + title);

  bool fileSaved = false;

  Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

  if (xlApp == null) {

  return;

  }

  Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

  Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

  Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

  System.Data.DataTable table = new System.Data.DataTable();

  table.Columns.Add("tagno", typeof(string));

  table.Columns.Add("AddDate", typeof(string));

  SqlDataReader dr = mydb.ExecuteReaderSQL("SELECT tagno,AddDate FROM tagno WHERE " + swhere + " ORDER BY tagnoid ASC", null);

  while (dr.Read()) {

  table.Rows.Add(dr["tagno"].ToString(), dr["AddDate"].ToString());

  }

  dr.Close();

  long rows = table.Rows.Count;

  Int32 _pagecount = 60000;

  if (rows > 60000) {

  long pageRows = _pagecount;//定义每页显示的行数,行数必须小于

  int scount = (int)(rows / pageRows);

  if (scount * pageRows < table.Rows.Count)//当总行数不被pageRows整除时,经过四舍五入可能页数不准

  {

  scount = scount + 1;

  }

  for (int sc = 1; sc <= scount; sc++) {

  if (sc > 1) {

  object missing = System.Reflection.Missing.Value;

  worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet

  }

  else {

  worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1

  }

  string[,] datas = new string[pageRows + 1, 2];

  datas[0, 0] = "吊牌号";

  datas[0, 1] = "生成时间";

  Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);

  range.Interior.ColorIndex = 15;//15代表灰色

  range.Font.Bold = true;

  range.Font.Size = 9;

  int init = int.Parse(((sc - 1) * pageRows).ToString());

  int r = 0;

  int index = 0;

  Int32 result;

  if (pageRows * sc >= table.Rows.Count) {

  result = table.Rows.Count;

  }

  else {

  result = int.Parse((pageRows * sc).ToString());

  }

  for (r = init; r < result; r++) {

  index = index + 1;

  for (int i = 0; i < 2; i++) {

  object obj = table.Rows[r][i];

  datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();

  }

  }

  Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index +1, 2]);

  fchR.Value2 = datas;

  worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

  range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, 2]);

  //15代表灰色

  range.Font.Size = 9;

  range.RowHeight = 14.25;

  range.Borders.LineStyle = 1;

  range.HorizontalAlignment = 1;

  }

  }

  else {

  string[,] datas = new string[table.Rows.Count + 2, 2];

  datas[0, 0] = "吊牌号";

  datas[0, 1] = "生成时间";

  Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);

  range.Interior.ColorIndex = 15;//15代表灰色

  range.Font.Bold = true;

  range.Font.Size = 9;

  int r = 0;

  for (r = 0; r < table.Rows.Count; r++) {

  for (int i = 0; i < 2; i++) {

  //if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))

  //{

  object obj = table.Rows[r][i];

  datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();

  //}

  }

  }

  Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, 2]);

  fchR.Value2 = datas;

  worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

  range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, 2]);

  //15代表灰色

  range.Font.Size = 9;

  range.RowHeight = 14.25;

  range.Borders.LineStyle = 1;

  range.HorizontalAlignment = 1;

  }

  if (saveFileName != "") {

  //try

  //{

  workbook.Saved = true;

  workbook.SaveCopyAs(saveFileName);

  fileSaved = true;

  //}

  //catch (Exception ex)

  //{

  //    fileSaved = false;

  //}

  }

  else {

  fileSaved = false;

  }

  xlApp.Quit();

  GC.Collect();//强行销毁

  if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

  }

关键词标签:.net,execl

相关阅读

文章评论
发表评论

热门文章 在.NET环境下为网站增加IP过滤功能在.NET环境下为网站增加IP过滤功能用Asp.net扩展ExtJS用Asp.net扩展ExtJS手把手教你用好LINQ to SQL手把手教你用好LINQ to SQLAsp.net关于动态输出服务器控件的应用Asp.net关于动态输出服务器控件的应用

相关软件

人气排行 asp.net表单提交方法GET\POST在ASP.NET中如何判断用户IE浏览器的版本Asp.net中messagebox的实现方法Asp.net中的web.config配置在ASP.NET MVC中实现大文件异步上传c#.Net经典面试题目用Asp.net扩展ExtJSasp.net获取URL和IP地址