DataSet导出EXcel

最新推荐文章于 2024-09-30 12:13:19 发布
ldy592
最新推荐文章于 2024-09-30 12:13:19 发布
阅读量321
点赞数
分类专栏： ASP.NET 文章标签： dataset string microsoft excel office null
ASP.NET 专栏收录该内容
12 篇文章 0 订阅
订阅专栏
 System.Data.DataTable dt = null;
            DataSet ds = null;
            GridView gv = null;
            bool bIsEmpty = true;
            int dataType = 0;
            if (fileName == null || fileName.Length == 0)
            {
                fileName = "无标题文件";
            }
            if (obj != null)
            {
                if (obj is DataSet)
                {
                    ds = (DataSet)obj;
                    dataType = 0;
                    if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        dt = ds.Tables[0];
                        bIsEmpty = false;
                    }
                }
                else
                {
                    gv = (GridView)obj;
                    dataType = 1;
                    if (gv.Rows.Count > 0)
                    {
                        bIsEmpty = false;
                    }
                }
            }
            if (bIsEmpty)
            {
                dt = new System.Data.DataTable();
                DataColumn dc = dt.Columns.Add("nodata");
                DataRow dr = dt.NewRow();
                dr[0] = "对不起，没有附合条件的数据！";
                dt.Rows.Add(dr);
            }
            #region write head
            /*HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" 
         + HttpUtility.UrlEncode("" + fileName + ".xls") + "\""); 
             HttpContext.Current.Response.Charset = "GB2312"; 
             HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; 
             HttpContext.Current.Response.ContentType = "application/ms-excel";*/
            #endregion

            StringBuilder builder = new StringBuilder();
            #region write html head
            builder.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\">\n <head>\n <meta http-equiv=Content-Type content=\"text/html; charset=gb2312\">\n <meta name=ProgId content=Excel.Sheet>\n <meta name=Generator content=\"Microsoft Excel 11\">\n <!--[if gte mso 9]> <xml>\n <x:ExcelWorkbook>\n <x:ExcelWorksheets>\n <x:ExcelWorksheet>\n <x:Name>Sheet1 </x:Name>\n <x:WorksheetOptions>\n <x:Panes>\n <x:Pane>\n <x:Number>3 </x:Number>\n </x:Pane>\n </x:Panes>\n </x:WorksheetOptions>\n </x:ExcelWorksheet>\n </x:ExcelWorkbook>\n </xml> <![endif]-->\n </head>\n <body>\n");
            #endregion

            #region write table head
            builder.Append(" <table cellspacing=\"0\" cellpadding=\"1\" rules=\"all\" border=1 x:str>\n");
            #endregion
            int colCount = 1;

            if (!bIsEmpty)
            {
                //ds 
                if (dataType == 0)
                {
                    colCount = dt.Columns.Count;
                }
                //dv 
                else
                {
                    colCount = gv.Rows[0].Cells.Count;
                }
            }
            #region 写入标题
            builder.Append(" <tr>\n");
            builder.Append(" <td colspan='" + colCount + "' style='text-align:center;'>" + fileName + " </td>");
            builder.Append(" </tr>\n");
            #endregion

            #region 写列
            if (!bIsEmpty)
            {
                builder.Append(" <tr>\n");
                for (int i = 0; i < colCount; i++)
                {
                    builder.Append(" <td style='text-align:center;'>");
                    if (dataType == 0)
                    {
                        builder.Append(dt.Columns[i].ColumnName);
                    }
                    else
                    {
                        builder.Append(gv.HeaderRow.Cells[i].Text);
                    }
                    builder.Append(" </td>");
                    //builder.Append(" <td style='background-color:#" + color + "; text-align:center;'>" + excelHead[i].Title + " </td>"); 
                }
                builder.Append(" </tr>\n");
            }
            #endregion

            #region write content
            if (!bIsEmpty)
            {
                int rowCount = dataType == 0 ? dt.Rows.Count : gv.Rows.Count;
                for (int i = 0; i < rowCount; i++)
                {
                    builder.Append(" <tr>\n");
                    builder.Append(" <td align=\"left\">");
                    if (dataType == 0)
                    {
                        builder.Append(dt.Rows[i][0].ToString());
                    }
                    else
                    {
                        builder.Append(gv.Rows[i].Cells[0].Text);
                    }
                    builder.Append(" </td>");
                    for (int j = 1; j < colCount; j++)
                    {
                        builder.Append(" <td align=\"right\">");
                        if (dataType == 0)
                        {
                            builder.Append(dt.Rows[i][j].ToString());
                        }
                        else
                        {
                            builder.Append(gv.Rows[i].Cells[j].Text);
                        }
                        builder.Append(" </td>");
                    }
                    builder.Append(" </tr>\n");
                }
            }
            #endregion

            #region write table end
            builder.Append(" </table>\n");
            #endregion

            #region write html end
            builder.Append(" </body> </html>\n");
            #endregion

            string tempFolder = "ExcelExport";
            string timeFolder = DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss");
            string strFolder = HttpContext.Current.Server.MapPath(tempFolder) + "\\";
            if (Directory.Exists(strFolder))
            {
                string[] folderList = Directory.GetDirectories(strFolder);
                foreach (string folder in folderList)
                {
                    if (Directory.GetCreationTime(folder).AddDays(3) < DateTime.Now)
                    {
                        Directory.Delete(folder, true);
                    }
                }
            }
            if (!Directory.Exists(strFolder + timeFolder))
            {
                Directory.CreateDirectory(strFolder + timeFolder);
            }

            string strLocation = HttpContext.Current.Request.Url.AbsoluteUri;

            strLocation = strLocation.Substring(0, strLocation.LastIndexOf("/") + 1);
            strLocation += tempFolder + "/" + timeFolder + "/" + fileName + ".xls";

            File.WriteAllText(strFolder + timeFolder + "\\" + fileName + ".xls", builder.ToString(), System.Text.Encoding.Default);

            HttpContext.Current.Response.Redirect(strLocation);

            //HttpContext.Current.Response.Write(builder.ToString()); 
            //HttpContext.Current.Response.End();