C# 创建excel并保存

客户选择字段,然后你根据选择的字段生成SQL,查询出数据,然后把这些数据导到excel?

客户选择完字段之后~保存文件之后~
就根据客户选择的字段写成SQL语句并查一张表~再根据表的结构把数据导入到Excel中~

谢谢楼上的朋友
差不多就是这个意思啦
但是excel的标题我想自己来定义

利用excel的组件实现不会很困难吧~！

首先必须引入Excel的COM组件，然后才能进行下面的操作.   /// <summary>
   /// 将传入的DataSet数据导出至Excel文件
   /// </summary>
   /// <param name="ctl">DataGrid</param>
   public static void DataSet2Excel(DataSet ds)
   {
    int maxRow=ds.Tables[0].Rows.Count;
    string fileName=DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";//设置导出文件的名称    DataView dv=new DataView(ds.Tables[0]);//将DataSet转换成DataView
    string fileURL=string.Empty;
    //调用方法将文件写入服务器,并获取全部路径
    fileURL=DataView2ExcelBySheet(dv,fileName);
    //获取路径后从服务器下载文件至本地
    HttpContext curContext=System.Web.HttpContext.Current;
    curContext.Response.ContentType="application/vnd.ms-excel";
    curContext.Response.ContentEncoding=System.Text.Encoding.Default;
    curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + fileName));
    curContext.Response.Charset = "";    curContext.Response.WriteFile(fileURL);
    curContext.Response.Flush();
    curContext.Response.End();
   }   /// <summary>
   /// 分Sheet导出Excel文件
   /// </summary>
   /// <param name="dv">需导出的DataView</param>
   /// <returns>导出文件的路径</returns>
   private static string DataView2ExcelBySheet(DataView dv,string fileName)
   {
    int sheetRows=65535;//设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行
    int sheetCount = (dv.Table.Rows.Count - 1) / sheetRows + 1;//计算Sheet数    GC.Collect();//垃圾回收    Application excel;
    _Workbook xBk;
    _Worksheet xSt=null;
    excel = new ApplicationClass();
    xBk = excel.Workbooks.Add(true);

    //定义循环中要使用的变量
    int dvRowStart;
    int dvRowEnd;
    int rowIndex = 0;
    int colIndex = 0;
    //对全部Sheet进行操作
    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
    {
     //初始化Sheet中的变量
     rowIndex = 1;
     colIndex = 1;
     //计算起始行
     dvRowStart = sheetIndex * sheetRows;
     dvRowEnd = dvRowStart + sheetRows-1;
     if (dvRowEnd > dv.Table.Rows.Count-1)
     {
      dvRowEnd = dv.Table.Rows.Count - 1;
     }
     //创建一个Sheet
     if (null == xSt)
     {
      xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
     }
     else
     {
      xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
     }
     //设置Sheet的名称
     xSt.Name = "Expdata";
     if (sheetCount > 1)
     {
      xSt.Name += ((int)(sheetIndex + 1)).ToString();
     }
     //取得标题
     foreach (DataColumn col in dv.Table.Columns)
     {
      //设置标题格式
      xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐
      xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;//设置标题为粗体
      //填值，并进行下一列
      excel.Cells[rowIndex, colIndex++] = col.ColumnName;
     }
     //取得表格中数量
     int drvIndex;
     for(drvIndex=dvRowStart;drvIndex<=dvRowEnd;drvIndex++)
     {
      DataRowView row=dv[drvIndex];
      //新起一行，当前单元格移至行首
      rowIndex++;
      colIndex = 1;
      foreach (DataColumn col in dv.Table.Columns)
      {
       if (col.DataType == System.Type.GetType("System.DateTime"))
       {
        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
       }
       else if (col.DataType == System.Type.GetType("System.String"))
       {
        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
       }
       else
       {
        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
       }
       colIndex++;
      }
     }
     //使用最佳宽度
     Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex-1]);
     allDataWithTitleRange.Select();
     allDataWithTitleRange.Columns.AutoFit();
     allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框
    }
    //设置导出文件在服务器上的文件夹
    string exportDir="~/ExcelFile/";//注意:该文件夹您须事先在服务器上建好才行
    //设置文件在服务器上的路径
    string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(exportDir,fileName));
    xBk.SaveCopyAs(absFileName);
    xBk.Close(false, null, null);
    excel.Quit();    System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);    xBk = null;
    excel = null;
    xSt = null;
    GC.Collect();
    //返回写入服务器Excel文件的路径
    return absFileName;
   }

其实你这个就是实现excel的报表功能看看下面的文章C#操作Excel开发报表系列整理

还有一篇文章C#操作excel(多种方法导入导出比较)

真的很感谢这么多朋友的帮忙~ 谢谢
但是我使用 “gohappy2008” 给出的代码后发现最后下载的时候是 aspx 文件
而且本地并没有xls文件请问是什么原因呀？能否请给位再帮帮我啊

你说你要客户选字段。。那你可以做个功能，就是把表所有的字段显示出来，让客户勾选。选好后，直接根据字段生成导出的语句。这个类足够你导出EXCEL了。using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Drawing;
using System.IO;/// <summary>
///ExcelData 的摘要说明
/// </summary>
public class ExcelData
{
public ExcelData()
{
//
//TODO: 在此处添加构造函数逻辑
//
  }
  public static void Export(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)
  {
    System.Web.HttpResponse httpResponse = page.Response;
    DataGrid dataGrid = new DataGrid();
    dataGrid.DataSource = tab.DefaultView;
    dataGrid.AllowPaging = false;
    dataGrid.HeaderStyle.ForeColor = Color.White;
    dataGrid.HeaderStyle.BackColor = Color.FromName("#aaaadd");
    dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
    dataGrid.HeaderStyle.Font.Bold = true;
    dataGrid.DataBind();
    httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" +
      HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";
    httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
    httpResponse.ContentType = "application/ms-excel";
    System.IO.StringWriter tw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
    dataGrid.RenderControl(hw);    string filePath = page.Server.MapPath(" .") + "\\Files\\" + FileName;
    if (!Directory.Exists(Path.GetDirectoryName(filePath)))
      Directory.CreateDirectory(Path.GetDirectoryName(filePath));    System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
    sw.Write(tw.ToString());
    sw.Close();    DownFile(httpResponse, FileName, filePath);    httpResponse.End();
  }
  private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
  {
    try
    {
      Response.ContentType = "application/octet-stream";      Response.AppendHeader("Content-Disposition", "attachment;filename=" +
        HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
      System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
      long fLen = fs.Length;
      int size = 102400;//每100K同时下载数据
      byte[] readData = new byte[size];//指定缓冲区的大小
      if (size > fLen) size = Convert.ToInt32(fLen);
      long fPos = 0;
      bool isEnd = false;
      while (!isEnd)
      {
        if ((fPos + size) > fLen)
        {
          size = Convert.ToInt32(fLen - fPos);
          readData = new byte[size];
          isEnd = true;
        }
        fs.Read(readData, 0, size);//读入一个压缩块
        Response.BinaryWrite(readData);
        fPos += size;
      }
      fs.Close();
      System.IO.File.Delete(fullPath);
      return true;
    }
    catch
    {
      return false;
    }
  }
}

我是直接用的 9楼朋友的代码~

以前都没有弄过Excel 组件  所以很多地方都不知道该这么弄
请问楼上的朋友  你有没有解决呀？

类 xxx.csusing System;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;namespace DataTableToExcel
{
    public class ExcelExport
    {
        public ExcelExport(string SQLCOndition, string title)//这个title就是excel的标题
        {            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook xBk;
            Microsoft.Office.Interop.Excel._Worksheet xSt;
            Microsoft.Office.Interop.Excel._QueryTable xQt;            SqlConnection tmpUpdConn = new SqlConnection(@"Server=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";Initial Catalog=" + ConfigurationManager.AppSettings["DBName"].ToString() + ";Uid=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";Pwd=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";");
            tmpUpdConn.Open();
            SqlCommand tmpUpdCmd = new SqlCommand(SQLCOndition, tmpUpdConn);
            tmpUpdCmd.CommandTimeout = 6000;
            SqlDataAdapter tmpada = new SqlDataAdapter(tmpUpdCmd);
            DataSet tmpds = new DataSet();
            tmpada.Fill(tmpds);
            System.Data.DataTable tmpt = tmpds.Tables[0];
            tmpUpdConn.Close();
            CreateExcel_51.App_Code.Global.C += tmpt.Rows.Count;
            if (tmpt.Rows.Count <= 0)//数据为空返回
            {
                CreateExcel_51.App_Code.Global.isNull = true;
                GC.Collect();
                tmpds.Dispose();
                tmpada.Dispose();
                tmpt.Dispose();
                return;
            }            string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";UID=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";PWD=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";DATABASE=" + ConfigurationManager.AppSettings["DBName"].ToString() + "";
            string Select = SQLCOndition;
            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
            xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), Select);
            xQt.Name = "";
            xQt.FieldNames = true;
            xQt.RowNumbers = false;
            xQt.FillAdjacentFormulas = false;
            xQt.PreserveFormatting = false;
            xQt.BackgroundQuery = false;
            xQt.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
            xQt.AdjustColumnWidth = true;
            xQt.RefreshPeriod = 0;
            xQt.PreserveColumnInfo = true;
            xQt.Refresh(xQt.BackgroundQuery);
            string Dir = "f:\\test\\" + ConfigurationManager.AppSettings["ExcelDir"].ToString() + "\\myfile_";
            string FileName = CreateExcel_51.App_Code.Global.n + "_Profile.xls";            string ExcelFileName = Dir + FileName;
            while (File.Exists(ExcelFileName))
            {
                FileName = CreateExcel_51.App_Code.Global.n + 1 + "_Profile.xls";
                ExcelFileName = Dir + FileName;
                CreateExcel_51.App_Code.Global.n += 1;
            }
            excel.ActiveWorkbook.SaveCopyAs(ExcelFileName);
            excel.DisplayAlerts = false;
            excel.Quit();
            GC.Collect();
            Console.WriteLine("Create Excel");
        }
    }
}
调用public void CreateExcel()
        {
                for (; ; )
                {
                    //先定义全局S E分别为 -200,0 为的是取数据时只取ID为X X+200区间的,比top快,ID为主键
                    S += 200;
                    E += 200;
                    DataTableToExcel.ExcelExport ex = new DataTableToExcel.ExcelExport(@"SELECT * FROM tab WHERE U.userID>='" + S + "' AND U.userID<'" + E + "'", "myexcel");
                    if (CreateExcel_51.App_Code.Global.C >= Convert.ToInt32((string)CreateExcel_51.App_Code.HATable.htCities[t]))//这不用管它,我是从1个hatab里取这段数据的总数
                    {
                        S = -200;
                        E = 0;
                        CreateExcel_51.App_Code.Global.C = 0;
                        CreateExcel_51.App_Code.Global.n = 1;
                        break;
                    }
                    if (CreateExcel_51.App_Code.Global.isNull)
                    {
                        Console.WriteLine(t + " " + "SID:{0} EID:{1} DataTable isNULL,已经写入的数据行数:{2}", S, E, CreateExcel_51.App_Code.Global.C);
                        CreateExcel_51.App_Code.Global.isNull = false;
                    }
                    if (CreateExcel_51.App_Code.Global.isOutOfMem)//这也别理它,因为数据非常大,循环特别多时会溢出,我就暂时先记下ID以后处理
                    {
                        Console.WriteLine(t + " " + "SID:{0} EID:{1} DataTable OutOfMemory", S, E);
                        FileStream fs = new FileStream("f:\\text\\" + ConfigurationManager.AppSettings["ExcelDir"].ToString() + "\\Log.txt", FileMode.Append);
                        StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                        sw.Write("OutOfMemory: " + S + " " + E + " " + CreateExcel_51.App_Code.Global.n + "_profile.xls" + "\r\n");
                        sw.Close();
                        fs.Close();
                        CreateExcel_51.App_Code.Global.isOutOfMem = false;
                    }
                }
            }
            Console.WriteLine(ConfigurationManager.AppSettings["DBName"].ToString() + " Create Excel Complated");
            Console.ReadKey();
        }
大致就是这样,我就不仔细查了,因为有些东西你可能没用,我也就不一点点再改了,你自己试试,肯定是没问题的,而且速度不慢

你只要把数据查询出来，返回DataTable 传到方法里面就可以用了。。

调试易

C# 创建excel并保存

解决方案 »