Untitled

Exporting to Excel

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data

       

string lFilename = Leads.xls";

string lDistributorFolder = Server.MapPath(".") + "\Portals\0\Distributors\" + _currentUser.UserID.ToString() + "\";

string lTemplateFolder = System.Configuration.ConfigurationManager.AppSettings["Templates"];

System.IO.Directory.CreateDirectory(lDistributorFolder);

 

File.Copy(lTemplateFolder + lFilename, lDistributorFolder + lFilename, true);

string lConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lDistributorFolder + "\" + lFilename + ";Extended Properties="Excel 8.0;HDR=YES;"";

DbProviderFactory lFactory = DbProviderFactories.GetFactory("System.Data.OleDb");

int lSequence = 0;

 

using (DbConnection lConnection = lFactory.CreateConnection())

{

    lConnection.ConnectionString = lConnectionString;

    lConnection.Open();

 

foreach (GridDataItem lItem in grdLeadList.Items)

  {

    lSequence++;

 

    using (DbCommand lCommand = lConnection.CreateCommand())

      {

           lCommand.CommandText = "INSERT INTO [ColderLeads$] ";

           lCommand.CommandText += "(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21) ";

           lCommand.CommandText += "VALUES(";

           lCommand.CommandText += """ + lSequence.ToString() + "",";

           lCommand.CommandText += """ + lItem.Cells[_gLeadNumber].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gSource].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gAccountName].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gCreatedOn].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gContactFullName].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gPriority].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gStreet1].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gStreet2].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gZIP].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gCity].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += """ + lItem.Cells[_gState].Text.Replace(" ", " ") + "",";

           lCommand.CommandText += ")";

           lCommand.ExecuteNonQuery();

         }

      }

 

     lConnection.Close();

  }