时间戳

首页 > 技术文章 > Excel导入导出，生成和下载Excel报表、附件、数据库表等操作--ASP.NET

Excel导入导出，生成和下载Excel报表、附件、数据库表等操作--ASP.NET

elves 2014-03-03 15:33 原文

文件通用输出方法 文件流输出：Response.OutputStream.Write、Response.BinaryWrite、Response.WriteFile，并添加返回文件格式、编码等；

public class OutExcel
{
    public static void OutExcel_bb(DataTable dt, string thepath, string temppath, int TitleNum, string Title1)
    {
        //通过调用Excel的查询来实现数据的导出，按固定格式
        //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名
        //TitleNum：表头列的行数
        Excel.Application excel;
        Excel._Workbook xBk;
        Excel._Worksheet xSt;

        //string Conn = "OLEDB;Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=xdtz;User ID=sa;Password=sa;Max Pool Size = 512";
        string path = thepath;
        excel = new Excel.Application();
        excel.Visible = false;
        excel.UserControl = true;
        xBk = excel.Workbooks.Add(path);
        xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1);

        xSt.Cells[2, 1] = Title1;
        int StartRow = TitleNum;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                xSt.Cells[StartRow + i, j + 1] = dt.Rows[i][j].ToString();
            }
        }

        xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        excel.Visible = false;

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xSt = null;
        xBk.Close(false, null, null);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        xBk = null;

        excel.Workbooks.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        excel = null;
        System.GC.Collect();
    }

    public static void OutExcel_bb(List<List<string>> lsts, string thepath, string temppath, string TitleName)
    {
        //通过调用Excel的查询来实现数据的导出，按固定格式
        //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名
        //TitleNum：表头列的行数
        Excel.Application excel;
        Excel._Workbook xBk;
        Excel._Worksheet xSt;
        
        string path = thepath;
        excel = new Excel.Application();
        excel.Visible = true;
        excel.UserControl = true;
        xBk = excel.Workbooks.Add(path);
        xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1);

        xSt.Cells[2, 1] = TitleName;
        int StartRow = 3;
        
        for (int i = 0; i < lsts.Count; i++)
        {
            List<string> lst = lsts[i];
            int colspan = 0;
            for (int j = 0; j < lst.Count; j++)
            {
                int ri = StartRow + i, ci = (j + 1) + colspan;
                if (lst[j].Contains("colspan"))
                {
                    int cp = Convert.ToInt16(lst[j].Split(':')[1]);
                    xSt.get_Range(xSt.Cells[ri, ci - 1], xSt.Cells[ri, (ci - 1) + (cp - 1)]).Merge(true);
                    colspan += cp - 2;
                }
                else
                {
                    xSt.Cells[ri, ci] = lst[j];
                } 
            }
        }
       // xSt.get_Range(xSt.Cells[3, 1], xSt.Cells[3, 2]).Merge(true);
      //  xSt.get_Range(xSt.Cells[4, 1], xSt.Cells[4, 2]).Merge(true);

        xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        excel.Visible = true;

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xSt = null;
        xBk.Close(false, null, null);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        xBk = null;

        excel.Workbooks.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        excel = null;
        System.GC.Collect();

    }
    public static void OutExecl_cc(string Str,string Title)
    {
        StringWriter sw = new StringWriter();
        sw.WriteLine(Title);
        sw.WriteLine(Str);
        sw.Close();

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(Title, System.Text.Encoding.UTF8) + ".xls");//中文                
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        HttpContext.Current.Response.Write(sw);
        HttpContext.Current.Response.End();
    }
}

例：

 protected void btnsearch_Click(object sender, EventArgs e)
    {
        AspNetPager1.CurrentPageIndex = 1;
        DataLoad(1);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (TiaoJian == "")
            TiaoJian = " 1=1 ";// and state = 1 

        if (this.hiddept.Value != "")
            TiaoJian += " and deptid in(select id from dbo.Department where parentdept=" + this.hiddept.Value + ")";
        if (this.txtname.Value != "")
            TiaoJian += " and username like '%" + this.txtname.Value + "%'";
        if (this.drop1.SelectedValue != "")
            TiaoJian += " and state = " + this.drop1.SelectedValue + "";

        DataSet ds = new BLL.Users().GetHMC(TiaoJian);
        string tick = DateTime.Now.ToString("yyyyMMddHHmmssff");
        string thepath = "Upload/temp/hmcgs.xls";
        string temppath = MapPath("Upload/temp/") +  tick + ".xls";
        OutExcel(ds, thepath, temppath,2,"花名册");
        System.GC.Collect();
        GetExcelFile(temppath,"花名册");
    }
    public void OutExcel(DataSet ds, string thepath, string temppath, int TitleNum, string Title1)
    {
        //通过调用Excel的查询来实现数据的导出，按固定格式
        //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名
        //TitleNum：表头列的行数
        Excel.Application excel;
        Excel._Workbook xBk;
        Excel._Worksheet xSt;

        //string Conn = "OLEDB;Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=xdtz;User ID=sa;Password=sa;Max Pool Size = 512";
        string path = MapPath(thepath);
        excel = new Excel.Application();
        excel.Visible = false;
        excel.UserControl = true;
        xBk = excel.Workbooks.Add(path);
        xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1);

        //xSt.Cells[2, 1] = Title1;
        int StartRow = TitleNum + 1;
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            xSt.Cells[StartRow + i, 1] = (i + 1);
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                xSt.Cells[StartRow + i, j + 2] = ds.Tables[0].Rows[i][j].ToString();
            }
        }

        xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        excel.Visible = false;
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xSt = null;
        xBk.Close(false, null, null);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        xBk = null;

        excel.Workbooks.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        excel = null;
        System.GC.Collect();
    }

    private void GetExcelFile(string temppath, string FileName)
    {
        System.IO.FileInfo file = new System.IO.FileInfo(temppath);
        if (file.Exists)
        {
            Response.Clear();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName + "(" + DateTime.Now.ToString("yyyyMMddHHmmss") + ").xls"));
            // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
            Response.AddHeader("Content-Length", file.Length.ToString());
            // 指定返回的是一个不能被客户端读取的流，必须被下载 
            Response.ContentType = "application/ms-excel";
            // 把文件流发送到客户端 
            Response.WriteFile(file.FullName);
            // 停止页面的执行 
            Response.End();
        }

    }

 

———————————————————————————————————————————————————————————————————————————

公共类：

public class ToExcell
    {
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="PropName">存储过程名</param>
        /// <param name="_params">参数</param>
        /// <returns></returns>
        public DataSet GetDataSet(string PropName, SqlParameter[] _params,int Id)
        {
           return DBUtility.NewDbHelperSQL.RunProcedure(PropName, _params, "tb1", Id);
        }

        #region 下载报表
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds">查询出的数据</param>
        /// <param name="ExcelFileName">excel名称</param>
        public void MyXlsToExcelByDataSet(DataSet ds, string ExcelFileName)
        {
            DataTable dt = ds.Tables[0];
            XlsDocument xlsDocument = new org.in2bits.MyXls.XlsDocument();
            xlsDocument.FileName = HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(ExcelFileName)) + "_" + string.Format("{0:yyyyMMddHHmmss}", DateTime.Now) + ".xls";
            Worksheet worksheet = xlsDocument.Workbook.Worksheets.Add("sheet1");//Excel工作表名称
            Cells cells = worksheet.Cells;
            int columns = dt.Columns.Count;
            for (int i = 0; i < columns; i++)//列名
            {
                cells.Add(1, (i + 1), dt.Columns[i].ColumnName.ToString().Trim());
            }
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                for (int k = 0; k < columns; k++)
                {
                    cells.Add(j + 2, (k + 1), dt.Rows[j][k].ToString().Trim());
                }
            }
            xlsDocument.Send();
        }
        #endregion

        #region 下载报表
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds">查询出的数据</param>
        /// <param name="ExcelFileName">excel名称</param>
        /// <param name="rowName">对应的列名</param>
        public void MyXlsToExcelByDataSet(DataSet ds, string ExcelFileName, string[] rowName)
        {
            DataTable dt = ds.Tables[0];
            XlsDocument xlsDocument = new org.in2bits.MyXls.XlsDocument();
            xlsDocument.FileName = HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(ExcelFileName)) + "_" + string.Format("{0:yyyyMMddHHmmss}", DateTime.Now) + ".xls";
            Worksheet worksheet = xlsDocument.Workbook.Worksheets.Add("sheet1");//Excel工作表名称
            Cells cells = worksheet.Cells;
            int columns = dt.Columns.Count;
            for (int i = 0; i < columns; i++)//列名
            {
                cells.Add(1, (i + 1), rowName[i].Trim());
            }
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                for (int k = 0; k < columns; k++)
                {
                    cells.Add(j + 2, (k + 1), dt.Rows[j][k].ToString().Trim());
                }
            }
            xlsDocument.Send();
        }
        #endregion


        #region 附件下载
        /// <summary>
        /// 附件下载方法
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="path">文件路径</param>
        public void DownLoadFile(string fileName, string path)
        {
            HttpContext.Current.Response.BufferOutput = false;

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + DisposeFileName(fileName)); //HttpContext.Current.Server.UrlEncode(fileName));//防止中文名出现乱码
            HttpContext.Current.Response.ContentType = "application/octstream";
            HttpContext.Current.Response.CacheControl = "Private";
            Stream stream = new FileStream(HttpContext.Current.Server.MapPath(path), FileMode.Open, FileAccess.Read, FileShare.Read);
            HttpContext.Current.Response.AppendHeader("Content-length", stream.Length.ToString());

            BinaryReader br = new BinaryReader(stream);

            byte[] bytes;

            for (int n = 0; n < (br.BaseStream.Length / 4096 + 1); n++)
            {
                bytes = br.ReadBytes(4096);
                HttpContext.Current.Response.BinaryWrite(bytes);
                System.Threading.Thread.Sleep(5); //休息一下,防止耗用带宽太多。
            }

            stream.Close();
        }
        #endregion
        #region 文件下载时文件名处理
        /// <summary>
        /// 文件下载时文件名处理
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        protected string DisposeFileName(string FileName)
        {
            FileName = FileName.Replace(" ", "");//去掉空格
            return FileName;
        }

        #endregion
    }

 

.NET读取Excel文件内容--导入，简单示例：

<%-- 前台--%>
<div>  

       <%-- 文件上传控件  用于将要读取的文件上传 并通过此控件获取文件的信息--%>  

       <asp:FileUpload ID="fileSelect" runat="server" />    

         <%-- 点击此按钮执行读取方法--%>  

       <asp:Button ID="btnRead" runat="server" Text="ReadStart" />  

</div>
后台获取
//声明变量（属性）  
string currFilePath = string.Empty; //待读取文件的全路径 string currFileExtension = string.Empty;  //文件的扩展名    
//Page_Load事件 注册按钮单击事件 
protected void Page_Load(object sender, EventArgs e) { this.btnRead.Click += new EventHandler(btnRead_Click); } //按钮单击事件   //里面的3个方法将在下面给出 
protected void btnRead_Click(object sender, EventArgs e)
{
    Upload();  //上传文件方法    
    if (this.currFileExtension == ".xlsx" || this.currFileExtension == ".xls")
    {
        DataTable dt = ReadExcelToTable(currFilePath);  //读取Excel文件（.xls和.xlsx格式）       
    }
    else if (this.currFileExtension == ".csv")
    {
        DataTable dt = ReadExcelWidthStream(currFilePath);  //读取.csv格式文件        
    }
}

 

 下面列出按钮单击事件中的3个方法

///<summary>
///上传文件到临时目录中 
///</ummary>
private void Upload()
{
     HttpPostedFile file = this.fileSelect.PostedFile;
     string fileName = file.FileName;
     string tempPath = System.IO.Path.GetTempPath();   //获取系统临时文件路径
      fileName = System.IO.Path.GetFileName(fileName); //获取文件名（不带路径）
     this.currFileExtension = System.IO.Path.GetExtension(fileName);   //获取文件的扩展名
     this.currFilePath = tempPath + fileName; //获取上传后的文件路径 记录到前面声明的全局变量
     file.SaveAs(this.currFilePath);  //上传
}


///<summary>
///读取xls\xlsx格式的Excel文件的方法 
///</ummary>
///<param name="path">待读取Excel的全路径</param>
///<returns></returns>
private DataTable ReadExcelToTable(string path)
{
//连接字符串
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";   // Office 07及以上版本 不能出现多余的空格 而且分号注意
//string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串  可根据自己的情况选择 或者程序判断要用哪一个连接字符串
using(OleDbConnection conn = new OleDbConnection(connstring))
{
   conn.Open();
   DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});  //得到所有sheet的名字
    string firstSheetName = sheetsName.Rows[0][2].ToString();   //得到第一个sheet的名字
    string sql = string.Format("SELECT * FROM [{0}],firstSheetName);  //查询字符串
     OleDbDataAdapter ada =new OleDbDataAdapter(sql,connstring);
     DataSet set = new DataSet();
     ada.Fill(set);
     return set.Tables[0];
     
}
}


///<summary>
///读取csv格式的Excel文件的方法 
///</ummary>
///<param name="path">待读取Excel的全路径</param>
///<returns></returns>
private DataTable ReadExcelWithStream(string path)
{
   DataTable dt = new DataTable();
   bool isDtHasColumn = false;   //标记DataTable 是否已经生成了列
   StreamReader reader = new StreamReader(path,System.Text.Encoding.Default);  //数据流
   while(!reader.EndOfStream)
    {
       string meaage = reader.ReadLine();
       string[] splitResult = message.Split(new char[]{','},StringSplitOption.None);  //读取一行 以逗号分隔 存入数组
       DataRow row = dt.NewRow();
       for(int i = 0;i<splitResult.Length;i++)
          {
              if(!isDtHasColumn) //如果还没有生成列
                  {
                        dt.Columns.Add("column" + i,typeof(string));
                    }
                    row[i] = splitResult[i];
             }
            dt.Rows.Add(row);  //添加行
            isDtHasColumn = true;  //读取第一行后 就标记已经存在列   再读取以后的行时，就不再生成列
     }
    return dt;
}

 

示例完整代码，上传EXCEL并导入

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.OleDb;

public partial class Tools_test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //资料导入点击事件
    protected void Button1_Click(object sender, EventArgs e)
    {
        string clientid = Session["fileName"].ToString();
        string CustTypeMax ="";
        string CustTypeMin = "";
        string CustTypeSub = "";
        string sbResult = ExeclData(clientid, CustTypeMax, CustTypeMin, CustTypeSub);
        alert("导入成功!");
    }
    private void alert(string str)
    {

        Page.ClientScript.RegisterStartupScript(this.GetType(), " ", "<script>alert('" + HttpUtility.UrlDecode(str) + "')</script>");
    }

    //上传文件
    protected void Button2_Click(object sender, EventArgs e)
    {
        string strFileName = InputAffixFile.Value.Trim();//文件名
        string strFileSize = (Convert.ToInt32(InputAffixFile.PostedFile.ContentLength.ToString()) / 1024).ToString();//文件大小
        string strFileType = strFileName.Substring(strFileName.LastIndexOf(".") + 1).ToLower();//文件类型
        string fileName = "";
        string FilePath = "";
        if (strFileName != "")
        {
            if (strFileType == "jpg" || strFileType == "psd" || strFileType == "swf" || strFileType == "gif " || strFileType == "bmp " || strFileType == "png " || strFileType == "xls" || strFileType == "doc" || strFileType == "pdf" || strFileType == "rar" || strFileType == "zip" || strFileType == "txt" || strFileType == "chm" || strFileType == "rtf" || strFileType == "docx" || strFileType == "wps" || strFileType == "xlsx" || strFileType == "et" || strFileType == "ppt" || strFileType == "pptx" || strFileType == "dps")
            {
                fileName = DateTime.Now.ToString("yyyMMddHHmmss") + "." + strFileType; ;//文件重命名
                Session["fileName"] = fileName;
                FilePath = System.Web.HttpContext.Current.Server.MapPath("~") + "//Upload//file";
                InputAffixFile.PostedFile.SaveAs(FilePath + "/" + fileName);
                Response.Write("<Script Language=JavaScript>alert(\"上传文件成功！\")</Script>");
            }
            else
            {
                Response.Write("<Script Language=JavaScript>alert(\"上传文件失败！\")</Script>");
            }
        }
        else
        {
            Response.Write("<Script Language=JavaScript>alert(\"请选择上传文件！\")</Script>");
        }
    }
    //资料导入方法
    public static string ExeclData(string clientid, string CustTypeMax, string CustTypeMin, string CustTypeSub)
    {
        {
            //获取上传的菜单名称和路径
            string tempMenPath = System.Web.HttpContext.Current.Server.MapPath("~") + "Upload\\file\\" + clientid;//
            StringBuilder sbResult = new StringBuilder("");
            string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + tempMenPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
            //string strconn = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + tempMenPath + "; Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strconn);
            conn.Open();
            string sql;
            sql = "SELECT * FROM [Sheet1$]";
            DataSet objDS = new DataSet();
            OleDbDataAdapter objadp = new OleDbDataAdapter(sql, conn);
            objadp.Fill(objDS);
            DataTable MenDt = objDS.Tables[0];
            conn.Close();
            int result = 0;
            foreach (DataRow dr in MenDt.Select())
            {
                try
                {
                    Cms.BLL.C_article bllarticle = new Cms.BLL.C_article();
                    Cms.Model.C_article modelarticle = new Cms.Model.C_article();
                    modelarticle.parentId = Convert.ToInt32(dr[3].ToString());//栏目id
                    modelarticle.title = dr[1].ToString();//标题
                    modelarticle.englishtitle = "";//英文标题
                    modelarticle.orderNumber = Convert.ToInt32(dr[13].ToString());//排序


                    modelarticle.artFrom = "";
                    modelarticle.photoUrl = dr[12].ToString();//缩略图
                    modelarticle.intro = dr[14].ToString();//简介
                    modelarticle.content = dr[5].ToString();//内容

                    modelarticle.seoTitle = dr[1].ToString();//seo标题
                    modelarticle.seoKeyword = dr[9].ToString();//seo关键词
                    modelarticle.seoDescription = dr[10].ToString();//seo描述
                    modelarticle.isRecommend = Convert.ToInt32(dr[11].ToString());//推荐
                    modelarticle.isChannel = "";//栏目推荐

                    modelarticle.isHidden = Convert.ToInt32(dr[8].ToString());//是否隐藏
                    modelarticle.isCheck = Convert.ToInt32(dr[8].ToString());//是否审核发布
                    modelarticle.isHot = Convert.ToInt32(dr[8].ToString());//是否热门文章

                    modelarticle.isTop = Convert.ToInt32(dr[8].ToString());//是否置顶
                    modelarticle.hits = Convert.ToInt32(dr[6].ToString());//点击量
                    modelarticle.expClass = "";//
                    modelarticle.editTime = DateTime.Now;//最后编辑时间
                    modelarticle.updateTime = DateTime.Now;//添加时间

                    modelarticle.txtLinkUrl = "";//URL链接
                    modelarticle.txtsource = dr[2].ToString();//信息来源
                    modelarticle.txtauthor = dr[2].ToString();//文章作者

                    if (dr[0].ToString() == "")
                    {

                        result = bllarticle.Add(modelarticle);
                        sbResult.Append("[" + dr[0].ToString() + "]增加成功 <br />");
                    }
                    else
                    {
                        result = bllarticle.Add(modelarticle);
                        if (result > 1)
                        {
                            sbResult.Append("增加成功 <br />");
                        }
                        else
                        {
                            sbResult.Append("增加失败 <br />");
                        }
                    }
                }
                catch
                {
                    continue;
                }
            }

            return sbResult.ToString();
            //更新到数据库中
        }
    }
}

 

推荐阅读