Pastebin.vip

Export irregular table data to excel file

From , 3 Years ago, written in C#, viewed 99 times.
URL https://pastebin.vip/view/a9ad5f28
  1. public void OutputExcel(DataView dv,string str)
  2. {
  3.    //dv为要输出到Excel的数据，str为标题名称
  4.    GC.Collect();
  5.    Application excel;// = new Application();
  6.    int rowIndex=4;
  7.    int colIndex=1;
  8.  
  9.    _Workbook xBk;
  10.    _Worksheet xSt;
  11.  
  12.    excel= new ApplicationClass();
  13.    
  14.    xBk = excel.Workbooks.Add(true);
  15.    
  16.    xSt = (_Worksheet)xBk.ActiveSheet;
  17.  
  18.    //
  19.    //取得标题
  20.    //
  21.    foreach(DataColumn col in dv.Table.Columns)
  22.    {
  23.     colIndex++;
  24.     excel.Cells[4,colIndex] = col.ColumnName;
  25.     xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
  26.    }
  27.  
  28.    //
  29.    //取得表格中的数据
  30.    //
  31.    foreach(DataRowView row in dv)
  32.    {
  33.     rowIndex ++;
  34.     colIndex = 1;
  35.     foreach(DataColumn col in dv.Table.Columns)
  36.     {
  37.      colIndex ++;
  38.      if(col.DataType == System.Type.GetType("System.DateTime"))
  39.      {
  40.       excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
  41.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
  42.      }
  43.      else
  44.       if(col.DataType == System.Type.GetType("System.String"))
  45.      {
  46.       excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
  47.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
  48.      }
  49.      else
  50.      {
  51.       excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
  52.      }
  53.     }
  54.    }
  55.    //
  56.    //加载一个合计行
  57.    //
  58.    int rowSum = rowIndex + 1;
  59.    int colSum = 2;
  60.    excel.Cells[rowSum,2] = "合计";
  61.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
  62.    //
  63.    //设置选中的部分的颜色
  64.    //
  65.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
  66.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
  67.    //
  68.    //取得整个报表的标题
  69.    //
  70.    excel.Cells[2,2] = str;
  71.    //
  72.    //设置整个报表的标题格式
  73.    //
  74.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
  75.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
  76.    //
  77.    //设置报表表格为最适应宽度
  78.    //
  79.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
  80.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
  81.    //
  82.    //设置整个报表的标题为跨列居中
  83.    //
  84.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
  85.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
  86.    //
  87.    //绘制边框
  88.    //
  89.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
  90.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
  91.    xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
  92.    xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
  93.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
  94.    //
  95.    //显示效果
  96.    //
  97.    excel.Visible=true;
  98.  
  99.    //xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
  100.    xBk.SaveCopyAs(Server.MapPath(".")+""+this.xlfile.Text+".xls");
  101.  
  102.    ds = null;
  103.             xBk.Close(false, null,null);
  104.    
  105.             excel.Quit();
  106.             System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
  107.             System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
  108.     System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
  109.             xBk = null;
  110.             excel = null;
  111.    xSt = null;
  112.             GC.Collect();
  113.    string path = Server.MapPath(this.xlfile.Text+".xls");
  114.  
  115.    System.IO.FileInfo file = new System.IO.FileInfo(path);
  116.    Response.Clear();
  117.    Response.Charset="GB2312";
  118.    Response.ContentEncoding=System.Text.Encoding.UTF8;
  119.    // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
  120.    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
  121.    // 添加头信息，指定文件大小，让浏览器能够显示下载进度
  122.    Response.AddHeader("Content-Length", file.Length.ToString());
  123.    
  124.    // 指定返回的是一个不能被客户端读取的流，必须被下载
  125.    Response.ContentType = "application/ms-excel";
  126.    
  127.    // 把文件流发送到客户端
  128.    Response.WriteFile(file.FullName);
  129.    // 停止页面的执行
  130.    
  131.    Response.End();
  132. }
  133.  
  134.  
  135. //csharp/7260

Reply to "Export irregular table data to excel file"

Here you can reply to the paste above

captcha

https://burned.cc - Burn After Reading Website
Powered by Pastebin.vip paste tool since 2022!