导出excel并且给导出的excel加上链接

 public SqlConnection con()
    {
        return new SqlConnection("server=192.168.11.3;uid=sa;pwd=1qaz2WSX;database=bjkwSQ");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //Web.Config配置的路径   
string str3 = ConfigurationManager.AppSettings["oldpath"].ToString();
        string newpath = ConfigurationManager.AppSettings["newpath"].ToString();
        string targetxls = ConfigurationManager.AppSettings["targetxls"].ToString();
        SqlConnection Connection = con();
              MSExcel.Application excelApp;  //Excel应用程序  
                MSExcel.Workbook workbook;  //Excel文档 

                excelApp = new MSExcel.ApplicationClass();
             
                Object nothing = Missing.Value;
                workbook = excelApp.Workbooks.Add(nothing);
                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
                MSExcel.Worksheet worksheet = (MSExcel.Worksheet)excelApp.Worksheets.get_Item(1);
                excelApp.Visible = false;
               excelApp.UserControl = true;
               string sql = "select s_name ,s_brithDay,s_education,s_professionalTitle,s_telphone,s_email,t_applyName,(SELECT c_name FROM t_category_group where c_id=t_applyClassA ) as t_applyClassA ,(SELECT c_name FROM t_category_group where c_id=t_applyClassB ) as t_applyClassB,(select o_name from t_organ where  t_organ.o_id=t_applySubject.o_id ) as o_id,t_applyDirect,t_operater,t_filePath from t_applySubject,t_SubjectMan where  t_applySubject.s_id=t_SubjectMan.s_id  AND t_applyType='000003' and t_applyAuditState='000004' ";
            
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand(sql, Connection);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                System.Data.DataTable dt = ds.Tables[0];
                #region保存文件到一个特定的文件夹里面
                 if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                       
                      String sourcePath = str3 + dr["t_filePath"].ToString();//文件的原始路径
                        String targetPath = newpath + dr["t_filePath"].ToString();//文件要保存的路径
                        bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之

                        System.IO.File.Copy(sourcePath, targetPath, isrewrite);
                    }
                    Response.Write("<script>alert('导出成功')</script>");
                }
                #endregion
                #region导出excel代码并给excel加上链接
                if (worksheet == null)
                    return;  //工作薄中没有工作表.

                int rowCount = dt.Rows.Count;
                int excelrow =1;

                //写入数据，Excel索引从1开始。
               
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        worksheet.Cells[excelrow, 1] = "序号";
                        worksheet.Cells[excelrow, 2] = "课题名称";
                        worksheet.Cells[excelrow, 3] = "申请人姓名";
                        worksheet.Cells[excelrow, 4] = "机构名称";
                        worksheet.Cells[excelrow, 5] = "出生日期";
                        worksheet.Cells[excelrow, 6] = "学历";
                        worksheet.Cells[excelrow, 7] = "技术职称";
                        worksheet.Cells[excelrow, 8] = "手机";
                        worksheet.Cells[excelrow, 9] = "电子邮件";
                        worksheet.Cells[excelrow, 10] = "一级学科";
                        worksheet.Cells[excelrow, 11] = "二级学科";
                        worksheet.Cells[excelrow, 12] = "研究方向";
                        worksheet.Cells[excelrow, 13] = "实施方案";
                        excelrow++;
                    }
                    int m = 1;

                    foreach (DataRow dr in dt.Rows)
                    {
                        worksheet.Cells[excelrow, 1] = m;
                        worksheet.Cells[excelrow, 2] = dr["t_applyName"].ToString();
                        worksheet.Cells[excelrow, 3] = dr["s_name"].ToString();
                        worksheet.Cells[excelrow, 4] = dr["o_id"].ToString();
                        worksheet.Cells[excelrow, 5] = dr["s_brithDay"].ToString();
                        worksheet.Cells[excelrow, 6] = dr["s_education"].ToString();
                        worksheet.Cells[excelrow, 7] = dr["s_professionalTitle"].ToString();
                        worksheet.Cells[excelrow, 8] = dr["s_telphone"].ToString();
                        worksheet.Cells[excelrow, 9] = dr["s_email"].ToString();
                        worksheet.Cells[excelrow, 10] = dr["t_applyClassA"].ToString();
                        worksheet.Cells[excelrow, 11] = dr["t_applyClassB"].ToString();
                        worksheet.Cells[excelrow, 12] = dr["t_applyDirect"].ToString();
                        worksheet.Cells[excelrow, 13] = dr["t_filePath"].ToString();

                        Range tempRange = worksheet.get_Range(worksheet.Cells[excelrow, 13], worksheet.Cells[excelrow, 13]);//获取excel中的单元格
                        worksheet.Hyperlinks.Add(tempRange, newpath + dr["t_filePath"].ToString(), Missing.Value, Missing.Value, Missing.Value);//加上超链接
                        m++;
                        excelrow++;

                    }

                    Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 13]);//获取到导出excel的表头
                    range.RowHeight = 30;//设置高度
                    range.Font.Bold = true;//设置字体加粗
                    range.Interior.ColorIndex = 20;//设置填充颜色
                    Range range1 = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount+1,13]);
                    range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);
                    range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线

                    range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线
                    range1.EntireColumn.AutoFit();//设置单元格的宽度根据单元格的内容来调整
                    workbook.SaveAs(targetxls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            NAR(worksheet);
            NAR(sheets);
            excelApp.Quit();
            NAR(excelApp);
        #endregion
    }

    private void NAR(object o)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        }
        catch
        { }
        finally
        {
            o = null;
        }
    }

 

转载于:https://www.cnblogs.com/8968wang/archive/2013/03/08/2949351.html