Last post Feb 07, 2011 12:16 PM by developerlili
Jan 27, 2011 03:58 PM|developerlili|LINK
Hello I have been searching for forums for almost 3 hours and I have found a few threads that are similar but none that address my particular issue.
I am NOT using a Datagrid
I am NOT using a new/blank worksheet.
I am writing a query that pulls only 1 row of data from a Database. I need to bind the data to a worksheet that has pre-defined columns. The file is in a folder on the server. Each time i need to call the file and append new data to it to and replace
the current data.
After this point, I need to attach the worksheet to an email and send. It's not much but this is what I have so far.
protected void Generate_excelfile(object sender, EventArgs e)
arcWebSitePage pageAgent = new arcWebSitePage(Convert.ToInt32(Session["WebSitePageID"]));
arcWebSites WebSiteAgent = new arcWebSites(pageAgent.web_site_id);
string usersession = Session.SessionID;
int PageID = Convert.ToInt32(Session["WebSitePageID"]);
string connstring = ConfigurationManager.ConnectionStrings["ARCcon"].ToString();
SqlConnection sqlcon = new SqlConnection(connstring);
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
MemoryStream mStream = new MemoryStream();
StreamWriter sw = new StreamWriter(mStream);
sw.AutoFlush = true;
String ReturnValue1 = "";
string Command1 = "SELECT user_session, first_Name1, last_Name1, Phone1, Email1, Company1 from tbl_lead_data where user_session = '" + usersession + "' ";
SqlCommand sqlcom1 = new SqlCommand(Command1, sqlcon);
sqlcom1.CommandType = CommandType.Text;
ReturnValue1 = sqlcom1.ExecuteScalar().ToString();
string Subject = ReturnValue1;
excel generate dynamically c#
Jan 30, 2011 02:08 PM|Mazenx|LINK
Write to your excel sheet as the guy doing in this post:
and then use this guy's code to send the email with attachment
Feb 02, 2011 09:27 AM|developerlili|LINK
Thanks for the suggestion. I am following the steps suggested in that page. I set up the excel doc as a datasource and created an ODBC connection for it.
The issue now I am getting is when I go to run an insert statement i get an error saying "Keyword not supported: 'dsn'." I included the connection string in the webconfig file and tested the connection works fine.
Here is the code
string excelconnstring = ConfigurationManager.ConnectionStrings["ExtranetExcelsheet"].ToString();
SqlConnection excelsqlcon = new SqlConnection(excelconnstring);
String Command11 = "Insert into BulkImport ([First name + Last Name], [Address Line 1],[Address Line 2],[Postal Code],[City],[Country],[Phone],[Email],[Billing Reference (cost center/ Department)])"
+ "VALUES ('" + firstlast +"', '"+ Address1 +"', '" + Address2+"', '" + Zip + "', '"+ City+"','"+Country+"','"+Phone+"','"+Email+"', '"+Department+"')";
SqlCommand sqlcom11 = new SqlCommand(Command11, excelsqlcon);
sqlcom11.CommandType = CommandType.Text;
Feb 02, 2011 09:32 AM|Mazenx|LINK
This means that you're having a logic error in your system , this is crystal report for VIEWING data not inserting !
Feb 02, 2011 09:36 AM|Mazenx|LINK
I hope you didnt understand me wrong but the odbc is for the crystal report not for the excel , use regular connectionstring for the excel , search on google for connection string on excel sheet , the odbc is for crystal .
Feb 02, 2011 10:53 AM|developerlili|LINK
i went back and re-read the first link and it looks like I need to add the following classes
But they are not being recognized by intellisync for some reason.
It is recognizing only System.Data.Odbc ect. the Factory part is not being recognized by intellisync, andy ideas?
Feb 04, 2011 04:56 PM|developerlili|LINK
I managed to get the factories to be recognized by intellisync. The issue I am having now is a rights issue.
The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
I have given IUSER,IWAM, ASPNET and Everyone full rights to the file and I am still getting this error. Here is my code.
using (DbConnection connection = factory.CreateConnection())
connection.ConnectionString = excelconnstring;
using (DbCommand command = connection.CreateCommand())
command.CommandText = "Insert into BulkImport ([First name + Last Name], [Address Line 1],[Address Line 2],[Postal Code],[City],[Country],[Phone],[Email],[Billing Reference (cost center/ Department)])"
+ "VALUES ('" + firstlast + "', '" + Address1 + "', '" + Address2 + "', '" + Zip + "', '" + City + "','" + Country + "','" + Phone + "','" + Email + "', '" + Department + "')";
Feb 05, 2011 05:57 AM|Mazenx|LINK
are you sure your excel isnt opened during that test?
Feb 07, 2011 11:33 AM|developerlili|LINK
It's definately closed. I found that this may be a bug on microsoft website. So know I am thinking the best approach is to generate a new excel file each time, i just need to make sure it is generated with the exact header rows for the data. Looking for
an article for this.
Feb 07, 2011 11:42 AM|Mazenx|LINK
try using this connection string
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel
Feb 07, 2011 12:14 PM|developerlili|LINK
Hi I am getting the following error
An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.
Here is my connection string
<add name="Bulk_ImportConnectionString" connectionString="Data Source=C:\Inetpub\wwwroot\testsite\emailAttachments\Bulk_Import.xls;Persist Security Info=True;Mode=ReadWrite;Extended Properties="Excel 12.0 Xml;HDR=YES""
Feb 07, 2011 12:16 PM|developerlili|LINK
oops wait just noticed there was an extra quote. Let me try again.
same error even after removing extra quote.