.net导出到Excel固定模版中

各位大侠小弟最近遇到一点问题 麻烦各位大侠帮帮忙  
现在公司做了一个固定Excel模版  需要从数据库读取数据导入到固定Execl模版中去  列头大概有一百个字段  麻烦那位大侠能提供源代码 

解决方案 »

  1.   

    try
                {
                    SaveFileDialog fileDlg = new SaveFileDialog();
                    fileDlg.Filter = "csv files (*.xls)|*.xls|All files (*.*)|*.*";
                    fileDlg.FilterIndex = 1;
                    fileDlg.RestoreDirectory = true;
     
                    if (fileDlg.ShowDialog() == DialogResult.OK)
                    {
                        if (File.Exists(fileDlg.FileName))
                            File.Delete(fileDlg.FileName);
     
                        //这里加入你导出代码
     
                        string appPath = string.Empty;
                        if (appPath == string.Empty)
                        {
                            appPath = System.Windows.Forms.Application.StartupPath;
                            if (!appPath.EndsWith(@"\"))
                            {
                                appPath += @"\";
                            }
                        }
                        //调用的模板文件 正式出库单
                        FileInfo mode = new FileInfo(appPath + @"模板\newRecv.xlt");
                         //定义
                        Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
                        appExcel.Application.DisplayAlerts = false;
                        appExcel.Visible = false;
                        Workbook objBook = null;
                        object missing = System.Reflection.Missing.Value;
                        if (appExcel == null)
                        {
                            MessageBox.Show("error：无法创建Excel对象，可能您的机子未安装Excel");
                            return;
                        }
                        objBook = appExcel.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                        Worksheet objSheet = (Worksheet)objBook.Worksheets[1];
     
                        //写入值
     
                      for (int r = 0; r < this.gv_ReceiveGoods.DataRowCount; r++)
                      {
                                             objSheet.Cells[r + 4, 1] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["SteelSDCode"]).ToString();
                          objSheet.Cells[r + 4, 2] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["OrderState"]).ToString();
                          objSheet.Cells[r + 4, 3] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["ShapeName"]).ToString();
     
                          objSheet.Cells[r + 4, 4] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["TradeName"]).ToString();
                          objSheet.Cells[r + 4, 5] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["StandardName"]).ToString();
     
                          objSheet.Cells[r + 4, 6] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["Width"]).ToString();
                          objSheet.Cells[r + 4, 7] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["Length"]).ToString();
     
                          objSheet.Cells[r + 4, 8] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["FurbaNumber"]).ToString();
                          objSheet.Cells[r + 4, 9] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["TestSize"]).ToString();
     
                          objSheet.Cells[r + 4, 10] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["TestWond"]).ToString();
                          objSheet.Cells[r + 4, 11] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["ProductName"]).ToString();
     
                          objSheet.Cells[r + 4, 12] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["ProductCode"]).ToString();
                          objSheet.Cells[r + 4, 13] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["RecAmount"]).ToString();
     
                          objSheet.Cells[r + 4, 14] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["SingleWeight"]).ToString();
                          objSheet.Cells[r + 4, 15] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["Price"]).ToString();
     
                          objSheet.Cells[r + 4, 16] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["AcceptDate"]).ToString();
                          objSheet.Cells[r + 4, 17] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["AcceptCode"]).ToString();
                           objSheet.Cells[r + 4, 18] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["Operator"]).ToString();
                          objSheet.Cells[r + 4, 19] = "'" + gv_ReceiveGoods.GetRowCellValue(r, gv_ReceiveGoods.Columns["Accept_ID"]).ToString();
                       }
                        string fileName = fileDlg.FileName;
                        objSheet.Columns.EntireColumn.AutoFit();
                        objSheet.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, missing, missing, false, false, missing, missing, missing, missing);
                        objBook.Close(false, mode.FullName, missing);
                        appExcel.Workbooks.Close();
                        appExcel.Quit();
                        //File.Copy(mode.FullName, fileDlg.FileName, true);//模版路径，保存路径
                        MessageBox.Show("导出成功！");
                     }
     
                }
                catch (Exception ex)
                {
                }
    给分吧
      

  2.   

    推荐使用NPOI。
    http://npoi.codeplex.com/
    http://tonyqus.sinaapp.com/tutorial
      

  3.   

    EXcel的导入和导出例子（用之前要先添加引用Microsoft.Office.Interop.Excel）:XML/HTML code123456789101112131415161718192021222324252627282930 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>   <!DOCTYPE html>   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"  id="Head1"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>     <title></title> </head> <body>       <form id="form1" runat="server">     <div>     <input id="File1" type="file" runat="server" />         <asp:Button ID="Button1" runat="server" Text="导入" OnClick="btnUpload_Click" /><br />                  <asp:Button ID="cmdOpen" runat="server" Text="在线打开" CommandName="open" OnCommand="Button_Click" />&nbsp;             <asp:Button ID="cmdSave"                runat="server" Text="本地保存" CommandName="save" OnCommand="Button_Click" />             <asp:DropDownList ID="listType" runat="server">                 <asp:ListItem Value="excel">Excel</asp:ListItem>                 <asp:ListItem Value="word">Word</asp:ListItem>             </asp:DropDownList>          <br />         数据源:         <asp:GridView ID="GridView1" runat="server">     </asp:GridView>     </div>     </form> </body> </html>  C# code123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109 using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.Sql; using System.Reflection; using System.IO; using Microsoft.Office.Interop.Excel;   public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         //FillGridView();     }     /**/    /// <summary>     /// 实现excel中的数据导入     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void btnUpload_Click(object sender, EventArgs e)     {         if (File1.Value == string.Empty || File1.Value == "")         {             Response.Write("<Script>alert('选择路径');</Script>");         }         else        {                          FileInfo file = new FileInfo(File1.PostedFile.FileName.ToString());             string sConnectionString;             string extension = file.Extension;             switch (extension)             {                 case ".xls":                     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                     break;                 case ".xlsx":                     sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                     break;                 default:                     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                     break;             }             OleDbConnection objConn = new OleDbConnection(sConnectionString);             objConn.Open();             OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("select * from [Sheet1$]", objConn);             DataSet objDataset1 = new DataSet();             objAdapter1.Fill(objDataset1, "XLData");             GridView1.DataSource = objDataset1.Tables[0];             GridView1.DataBind();             objConn.Close();           }     }     private void OutPut(string fileType, string strType)     {         Response.Clear();         Response.Buffer = true;         Response.Charset = "GB2312";         Response.AppendHeader("Content-Disposition", fileType);         Response.ContentType = strType;         this.EnableViewState = false;         System.IO.StringWriter swOut = new System.IO.StringWriter();         HtmlTextWriter hTw = new HtmlTextWriter(swOut);         GridView1.RenderControl(hTw);         Response.Write(swOut.ToString());         Response.End();     }     protected void Button_Click(object sender, CommandEventArgs e)     {         switch (e.CommandName)         {             case "save":                 switch (listType.SelectedValue)                 {                     case "excel":                         OutPut("attachment;filename=out.xls", "application/ms-excel");                         break;                     case "word":                         OutPut("attachment;filename=out.doc", "application/ms-word");                         break;                 }                 break;             case "open":                 switch (listType.SelectedValue)                 {                     case "excel":                         OutPut("online;filename=out.xls", "application/ms-excel");                         break;                     case "word":                         OutPut("online;filename=out.doc", "application/ms-word");                         break;                 }                 break;         }     }     public override void VerifyRenderingInServerForm(Control control)     {     } } 
      

  4.   

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>   <!DOCTYPE html>   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"  id="Head1"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>     <title></title> </head> <body>       <form id="form1" runat="server">     <div>     <input id="File1" type="file" runat="server" />         <asp:Button ID="Button1" runat="server" Text="导入" OnClick="btnUpload_Click" /><br />                  <asp:Button ID="cmdOpen" runat="server" Text="在线打开" CommandName="open" OnCommand="Button_Click" />&nbsp;             <asp:Button ID="cmdSave"                runat="server" Text="本地保存" CommandName="save" OnCommand="Button_Click" />             <asp:DropDownList ID="listType" runat="server">                 <asp:ListItem Value="excel">Excel</asp:ListItem>                 <asp:ListItem Value="word">Word</asp:ListItem>             </asp:DropDownList>          <br />         数据源:         <asp:GridView ID="GridView1" runat="server">     </asp:GridView>     </div>     </form> </body> </html>  
    using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.Sql; using System.Reflection; using System.IO; using Microsoft.Office.Interop.Excel;   public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         //FillGridView();     }     /**/    /// <summary>     /// 实现excel中的数据导入     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void btnUpload_Click(object sender, EventArgs e)     {         if (File1.Value == string.Empty || File1.Value == "")         {             Response.Write("<Script>alert('选择路径');</Script>");         }         else        {                          FileInfo file = new FileInfo(File1.PostedFile.FileName.ToString());             string sConnectionString;             string extension = file.Extension;             switch (extension)             {                 case ".xls":                     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                     break;                 case ".xlsx":                     sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                     break;                 default:                     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                     break;             }             OleDbConnection objConn = new OleDbConnection(sConnectionString);             objConn.Open();             OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("select * from [Sheet1$]", objConn);             DataSet objDataset1 = new DataSet();             objAdapter1.Fill(objDataset1, "XLData");             GridView1.DataSource = objDataset1.Tables[0];             GridView1.DataBind();             objConn.Close();           }     }     private void OutPut(string fileType, string strType)     {         Response.Clear();         Response.Buffer = true;         Response.Charset = "GB2312";         Response.AppendHeader("Content-Disposition", fileType);         Response.ContentType = strType;         this.EnableViewState = false;         System.IO.StringWriter swOut = new System.IO.StringWriter();         HtmlTextWriter hTw = new HtmlTextWriter(swOut);         GridView1.RenderControl(hTw);         Response.Write(swOut.ToString());         Response.End();     }     protected void Button_Click(object sender, CommandEventArgs e)     {         switch (e.CommandName)         {             case "save":                 switch (listType.SelectedValue)                 {                     case "excel":                         OutPut("attachment;filename=out.xls", "application/ms-excel");                         break;                     case "word":                         OutPut("attachment;filename=out.doc", "application/ms-word");                         break;                 }                 break;             case "open":                 switch (listType.SelectedValue)                 {                     case "excel":                         OutPut("online;filename=out.xls", "application/ms-excel");                         break;                     case "word":                         OutPut("online;filename=out.doc", "application/ms-word");                         break;                 }                 break;         }     }     public override void VerifyRenderingInServerForm(Control control)     {     } }
      

  5.   

    同意ngar_hyq的，NPOI挺好用，项目中一直在用到
      

  6.   

    http://bbs.csdn.net/topics/390272880