ASP.Net中使用Excel做报表

ASP.Net中使用Excel做报表

 

1.首先需要用Excel做一个模板，模板格式根据需求自行定义格式.

 

2.复制模板并填充数据，然后下载

           /// <summary>

           ///根据Excel模板文件,复制一个新的Excel文件并填充数据。

           /// </summary>

           ///<param name="dt">将要用于填充的数据</param>

           ///<param name="sNewFileName">新Excel文件的名称</param>

        public void CopyExcelToNew(DataTable dt, out string sNewFileName)

        {

            string sOldFileName = "MSC";

            sNewFileName = sOldFileName + String.Format("{0:yyyyMMddHHmmss}", DateTime.Now) + ".xls";

 

            string sSysDir = Server.MapPath("~/");//表示当前应用级程序的目录

            string sSysCurrDir = Server.MapPath("./");//表示所在页面的当前目录

 

                 //Excel模板所在的路径

            string sOldFilePath = sSysDir + string.Format(@"/WOImportTemplate/{0}.xls", sOldFileName);

           

            //新Excel文件所在的路径

            string sNewFilePath = sSysDir + @"/DataExchange/Excel/" + sNewFileName;

 

 

            #region copy file

            File.Copy(sOldFilePath, sNewFilePath, true);

            FileInfo fi = new FileInfo(sNewFilePath);

            fi.Attributes = FileAttributes.Normal;

 

            #endregion

 

            #region fill data into excel

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFilePath + ";Extended Properties=Excel 8.0;";

            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))

            {

                conn.Open();

                OleDbCommand objCmd = new OleDbCommand();

                objCmd.Connection = conn;

                string sql = string.Empty;

                int i = 1;

                foreach (DataRow row in dt.Rows)

                {

                    if (i == 1)

                    {

                        sql = string.Format("UPDATE [Sheet1$] SET [序号]={0},[库位编码]='{1}',[物料编码]='{2}',[物料名称]='{3}',[规格/型号]='{4}',[制造商/品牌]='{5}',[单位]='{6}',[库存数量]={7},[盘点数量]={7} WHERE [序号]=0;", 1, row["WarehouseNumberWCode"], row["MaterialCode"], row["MaterialName"].ToString().Replace("'", "''"), row["ModelNumberName"].ToString().Replace("'", "''"), row["ManufacturerName"].ToString().Replace("'", "''"), row["Unit"], row["Qty"]);

                        i++;

                    }

                    else

                    {

                        sql = "INSERT INTO [Sheet1$]([序号],[库位编码],[物料编码],[物料名称],[规格/型号],[制造商/品牌],[单位],[库存数量],[盘点数量])";

                        sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{7}');", i++, row["WarehouseNumberWCode"], row["MaterialCode"], row["MaterialName"].ToString().Replace("'", "''"), row["ModelNumberName"].ToString().Replace("'", "''"), row["ManufacturerName"].ToString().Replace("'", "''"), row["Unit"], row["Qty"]);

                    }

                    objCmd.CommandText = sql;

                    objCmd.ExecuteNonQuery();

                }

                if (i == 1)

                {

                    sql = "UPDATE [Sheet1$] SET [序号]=null,[库位编码]=null,[物料编码]=null,[物料名称]=null,[规格/型号]=null,[制造商/品牌]=null,[单位]=null,[库存数量]=null WHERE [序号]=0;";

                    objCmd.CommandText = sql;

                    objCmd.ExecuteNonQuery();

                }

 

                conn.Close();

            }

            #endregion

 

            #region

 

            #endregion

 

        }

        /// <summary>

        ///下载文件,这个方法可以通用，不需修改.

        /// </summary>

        ///<param name="excelFile">文件在服务器上的地址</param>

        ///<param name="excelName">文件名称</param>

        ///<param name="delFile">下载完成后是否删除服务器中的文件。</param>

        public static void DownloadExcel(string excelFile, string excelName, bool delFile)

        {

            FileInfo fi = new FileInfo(excelFile);//excelFile为文件在服务器上的地址

            HttpResponse contextResponse = HttpContext.Current.Response;

            contextResponse.Clear();

            contextResponse.Buffer = true;

            contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现

            contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", excelName)); //定义输出文件和文件名

            contextResponse.AppendHeader("Content-Length", fi.Length.ToString());

            contextResponse.ContentEncoding = Encoding.Default;

            contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。

 

            contextResponse.WriteFile(fi.FullName);

            contextResponse.Flush();

            if (delFile)

            {

                fi.Delete();

            }

            contextResponse.End();

        }

       

 3.调用方法

         DataTable dt = SqlHelper.ExecuteDataTable(global.ConnString, CommandType.Text, sql.ToString());

            if (dt.Rows.Count > 0)

            {

                string sNewFileName = string.Empty;

                CopyExcelToNew(dt, out sNewFileName);

 

                string jsNewFileName = Server.MapPath("~/") + @"/DataExchange/Excel/" + sNewFileName;

                new MSCExport().DownloadExcel(jsNewFileName, this.WONumber + ".xls");

            }

            else

            {

                this.Alert("没有检测到库存资料，请确认。");

            }