Excel导出，数据源DataTable，不使用DCOM组件，生成超链接

[FollowCN]

具体描述：
数据源：DataTable。
要求：
把DataTable的内容导出到EXCEL，
服务器不安装OFFCICE EXCEL以及其他任何相关组件，
生成列中会有某列是超链接到某地址，列和超链接地址是确定的。

求解决方案。

[cpp2017]

服务器上有一个目录,有写入权限.
里面有一个模板excel文件,里面只有各字段。

方案：
导出前copy该模板成另一个文件，通过oledb方式连接excel将datatable的数据用sql insert到excel中。

[cpp2017]

我的回复不见了?

[cpp2017]

 DataTable dt = this.GetDataSource();
            DataView dv = dt.DefaultView;
            dv.Sort = "UserName,VoucherNo";
            if (dv.Count == 0)
            {
                CCClient.AlertMessage("没有可以导出的资料!");
                return;
            }

            if (!Directory.Exists(Server.MapPath("Files/" + DateTime.Now.ToString("yyyyMM"))))
            {
                Directory.CreateDirectory(Server.MapPath("Files/" + DateTime.Now.ToString("yyyyMM")));
            }

            string GetSeqNo = this.GetSeqNo();
            string FileName = DateTime.Now.ToString("yyyyMMdd") + "第 "+GetSeqNo+" 批.xls";
            string sNewFileName = "Files/" + DateTime.Now.ToString("yyyyMM") + "/" + FileName ;

            string sNewFile = Server.MapPath(sNewFileName);
            try
            {
                File.Copy(Server.MapPath("Files/format.xls"), sNewFile);
            }
            catch (Exception er)
            {
                CCClient.AlertMessage(er.Message);
                return;
            }

            //取Email地址
            string Email = new BLL.Admin.cSetting().GetConfigValue("Email");

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFile + ";Extended Properties=Excel 8.0;";
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            OleDbCommand cmd = null;
            System.Data.OleDb.OleDbTransaction tran = null;

            string ErrMsg = "";
            int iCount = 0;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                string strSQL   =  "INSERT INTO [A$] ([证件类型], [证件编号], [姓名], [地址], [电话], [邮件地址], [两码发放方式], [证书类型]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                
                cmd = new OleDbCommand(strSQL, conn,tran);

                for (int i = 0; i < 8; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                cmd.Parameters[5].Value = Email;
                cmd.Parameters[6].Value = "1";
                
                string Ids = "";
             
                string LastUserName = "";
                bool bHasRows = false;

                
                foreach (DataRowView row in dv)
                {
                    string Name = row["UserName"].ToString().Trim();

                    if (Name == LastUserName)
                    {
                     
                        continue;
                    }
                    LastUserName = Name;
                    if (row["ActionStatus"].ToString() == "1") //处理中
                    {
                        continue;
                    }

                    iCount++;
                    cmd.Parameters[0].Value = row["UserIdType"].ToString();
                    cmd.Parameters[1].Value = row["UserIdNo"].ToString();
                    cmd.Parameters[2].Value = Name;
                    cmd.Parameters[3].Value = row["Address"].ToString();
                    cmd.Parameters[4].Value = row["Phone"].ToString();
                    //cmd.Parameters[5].Value = row["Email"].ToString();
                    cmd.Parameters[7].Value = row["CerType"].ToString();
                    cmd.ExecuteNonQuery();
                    Ids += row["seqNo"].ToString() + ",";
                    bHasRows = true;

                    //sb.Append(row["SeqNo"].ToString() + ",");
                }

                if (!bHasRows)
                {
                    CCClient.AlertMessage("没有可以导出的资料");
                    conn.Close();
                    File.Delete(sNewFile);
                    return;
                }
                if (Ids.Length > 0)
                {
                    Ids = Ids.Substring(0, Ids.Length - 1);
                }

                if (!new BLL.Cer.cNewCer().UpdateCerInfoStatus(Ids,"1", CurrentUser.UserCode, ref ErrMsg))
                {
                    tran.Rollback();
                    CCClient.AlertMessage("更新状态时错误,本次操作取消" + ErrMsg);
                    return;
                }
                tran.Commit();
                
            }
            catch (Exception er)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                CCClient.AlertMessage("生成错误"+er.Message);
                return;
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                }
                conn.Dispose();
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
            
            Response.Redirect(sNewFileName);

[Eddie005]

http://dotnet.aspx.cc/article/700bd3fa-a17f-41dc-b258-0dc572625700/read.aspx

[Eddie005]

http://dotnet.aspx.cc/article/bf0a54f9-c7c7-4200-bd9a-802ac1f5de50/read.aspx

[shoushii]

using System;
using System.Text;
using System.IO;

namespace Test
{
    public partial class XmlExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //存放模板文件内容
            string fileContent = string.Empty;
            //模板文件位置
            string modleFileName = Server.MapPath(".") + "\\ExcelModleFile.xls";
            //生成文件位置
            string renderFileName = Server.MapPath(".") + "\\ExcelFile.xls";

            //读出并保存模板文件内容
            StreamReader sr = new StreamReader(modleFileName, System.Text.Encoding.GetEncoding("gb2312"));
            fileContent = sr.ReadToEnd();
            sr.Close();

            //循环生成数据行
            StringBuilder sbRowsText = new StringBuilder(1024);
            sbRowsText.Append("<Row ss:AutoFitHeight=\"0\">");
            sbRowsText.Append("<Cell ss:StyleID=\"s24\" ss:HRef=\"");
            //设置超链接地址
            sbRowsText.Append("http://www.126.com/");
            sbRowsText.Append("\"><Data ss:Type=\"String\">View</Data></Cell>");
            sbRowsText.Append("<Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">");
            //设置内容
            sbRowsText.Append("Content");
            sbRowsText.Append("</Data></Cell>");
            sbRowsText.Append("</Row>");

            //保存完整Excel内容的字符串
            StringBuilder sbRender = new StringBuilder();
            //获得模板内容
            sbRender.Append(fileContent);
            //设置Excel数据行
            sbRender.Replace(@"[RowCount]", "3");
            //设置Excel标题
            sbRender.Replace(@"[Header]", "Title");
            //添加数据行
            sbRender.Replace(@"[DataRows]", sbRowsText.ToString());

            lblXml.Text = sbRender.ToString();

            //将内容写入文件
            StreamWriter sw = new StreamWriter(renderFileName);
            sw.Write(sbRender.ToString());
            sw.Close();

            //将文件输出到客户端
            Response.Charset = "GB2312";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(renderFileName));
            // 指定返回的是一个不能被客户端读取的流，必须被下载
            Response.ContentType = "application/ms-excel";
            // 把文件流发送到客户端 
            Response.WriteFile(renderFileName);
            Response.End();
        }
    }
}

[FollowCN]

请问超链是怎么形成的呢？

[geggegeda]

mark