使用XML模板导出EXCEL

首先配置XML模板

private void CreatSwissMedicExcel(DataTable dt,string fileName)
  {
   //string eccelTemplate = File.r("../Common/111/");
   System.IO.StreamReader stream = System.IO.File.OpenText(Server.MapPath("../Common/111.xml"));
   string text = stream.ReadToEnd();

   int startIndex = text.ToString().IndexOf("$DataRows:{");
   int ennIndex = text.ToString().IndexOf("}DataRows$");
   int length = ennIndex - startIndex  -11;
   if (length > 0)
   {
    string dataTemplate = text.ToString().Substring(startIndex +11 ,length);
    text = text.Remove(startIndex,length + 20);

    string dataRow ="";
      foreach (DataRow dr in dt.Rows)
      {
      
        string data = dataTemplate.Replace("$it.ID$",dr["ID"].ToString());
       data =   data.Replace("$it.ProjectName$",dr["ProjectName"].ToString());
       data = data.Replace("$it.ProjectMoney$",dr["ProjectMoney"].ToString());
          data = data.Replace("$it.Facilities$",dr["Facilities"].ToString());
          data = data.Replace("$it.FLMonthExpense$",dr["FLMonthExpense"].ToString());
          data = data.Replace("$it.FLAccumulateExpense$",dr["FLAccumulateExpense"].ToString());
          data = data.Replace("$it.CEngineering$",dr["CEngineering"].ToString());
          data = data.Replace("$it.BLMonthExpense$",dr["BLMonthExpense"].ToString());
          data = data.Replace("$it.BLAccumulateExpense$",dr["BLAccumulateExpense"].ToString());
          data = data.Replace("$it.ClassII$",dr["ClassII"].ToString());
          data = data.Replace("$it.CIIMonthExpense$",dr["CIIMonthExpense"].ToString());
          data = data.Replace("$it.CIIAccumulateExpense$",dr["CIIAccumulateExpense"].ToString());
          data = data.Replace("$it._SUM$",dr["_SUM"].ToString());
          data = data.Replace("$it.CSpending$",dr["CSpending"].ToString());
          data = data.Replace("$it.Interest$",dr["Interest"].ToString());
          data = data.Replace("$it.Demand$",dr["Demand"].ToString());
          data = data.Replace("$it.EditName$",dr["EditName"].ToString());
          data = data.Replace("$it.EditDate$",dr["EditDate"].ToString());  
       dataRow += data;
    
      }
    text = text.Insert(startIndex,dataRow);
   }
    ResponseExcel(fileName,text);
  }

 

 

 

private void ResponseExcel(string filename,string text)
  {
   Response.Clear();
   Response.Buffer = true;
   Response.Charset = "UTF-8";
   Response.AppendHeader("Content-Disposition","attachment;filename="+ filename);
   Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
   Response.ContentType = "application/ms-excel";
   this.EnableViewState = false;
   Response.Write(text.ToString());
   Response.End();
  }

 

private void Button3_Click(object sender, System.EventArgs e)
  {
   DataTable _dt = new DataTable();
   if (Inquiry()!="")
   {
    _dt = FundsManagement.Common.SQLHelps.SelectFundsReport(Inquiry()).Tables[0];
   }
   else
   {
    _dt = FundsManagement.Common.SQLHelps.GetFundsRepor().Tables[0];
   }
      
   CreatSwissMedicExcel(_dt,"工程资金使用情况.xml");
  }