Hi all,
Can anyone please help. I want to enter some data into an excel file
throgh my code. I know this can be done very easily if I have an excel file
with column name already present. But, I want to insert column name
dynamically. Can someone help?
Here is the code that I am using...
//Added by Animesh - 20.04.2005
//Inserts data into xl file.
private void export_xl()
{
int rcount =0; //counter for records.
int sheetCtr = 1; //counter for worksheets.
//Refreshing Excel file for exporting data.
xlsCreate();
OleDbConnection xlscnn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fname +
";Extended Properties=Excel 8.0;");
OleDbConnection oracnn = new OleDbConnection();
oracnn.ConnectionString = get_con_string(); //Get connection string
from xml file.
oracnn.Open();
xlscnn.Open();
//Inserting data into the Excel file
try
{
OleDbCommand cmd1 = new OleDbCommand(PrepareSQL(),oracnn);
OleDbDataReader dr = cmd1.ExecuteReader();
while (dr.Read())
{
//One sheet will have only 60000 records.
if (rcount <= 60000)
{
SQL= "insert into [sheet" + sheetCtr +
"$](AGENTNAME,CREATED_BY_GROUP,SRNUMBER,SRTYPE,SRSUBTYPE,SRCREATIONDATE,MOBILENO,CUSTSEGMENT,MODEOFINTERACTION,QST1YES,QST1NO,QST1BLANK,QST2YES,QST2NO,QST2BLANK,QST3YES,QST3NO,QST3BLANK,QST4YES,QST4NO,QST4BLANK
) values( " ;
SQL = SQL + "'" +
dr["AGENTNAME"] + "','" + dr["CREATED_BY_GROUP"] + "'," + dr["SRNUMBER"] + ",";
SQL = SQL + "'" + dr["SRTYPE"]
+ "','" + dr["SRSUBTYPE"] + "','" + dr["SRCREATIONDATE"] + "',";
SQL = SQL + "'" +
dr["MOBILENO"] + "','" + dr["CUSTSEGMENT"] + "','" + dr["MODEOFINTERACTION"] +
"','" + dr["QST1YES"] + "',";
SQL = SQL + "'" + dr["QST1NO"]
+ "','" + dr["QST1BLANK"] + "','" + dr["QST2YES"] + "',";
SQL = SQL + "'" + dr["QST2NO"]
+ "','" + dr["QST2BLANK"] + "','" + dr["QST3YES"] + "',";
SQL = SQL + "'" + dr["QST3NO"]
+ "','" + dr["QST3BLANK"] + "','" + dr["QST4YES"] + "','"+dr["QST4NO"] +
"','"+dr["QST4BLANK"]+"')";
OleDbCommand cmd = new
OleDbCommand(SQL,xlscnn);
cmd.ExecuteNonQuery();
cmd.Dispose();
rcount++;
}
//Change sheet if previous sheet is
full.
else
{
rcount = 0;
sheetCtr++;
continue;
}
}
cmd1.Dispose();
xlscnn.Close();
oracnn.Close();
HL.Visible = true;
dgrep3.Visible = false;
btnExport.Visible = false;
HL.Text = HL.Text + " Total no. of records in
the file = "+ (rcount + 60000*(sheetCtr-1));
}
catch (Exception e)
{
xlscnn.Close();
oracnn.Close();
HL.Visible = true;
TextBox1.Visible = true;
HL.Text = HL.Text + " Total no. of records in
the file = "+ (rcount + 60000*(sheetCtr-1));
TextBox1.Text = e.Message.ToString();
}
}
//Added by Animesh - 20.04.2005
//Creates xl file "CSAT_REPORT_ddMMMyyyy_HHMIss.xls"
private void xlsCreate()
{
string fdate = ""; //stores sysdate.
fdate = System.DateTime.Now.ToString("ddMMMyyy_HHMIss");
fname = fname + fdate + ".xls";
System.IO.File.Copy("C:\\CS.xls",fname,true);
HL.NavigateUrl = fname; //Hyperlink to point the file.
}
�
On Wed, 04 May 2005 Animesh Mishra wrote :
>Hi Nicholas,
>�� Thanx 4 a nice solution, but sorry to say that this is not what I exactly
>wanted. I mean I want to know the code to roam through the directory, i. e. to
>pick up the files one by one. Hope u understand wat I need.
>W8ing 4 ur prompt response
>Animesh
>On Wed, 04 May 2005 Driver,Nicholas wrote :
>>Hi Animesh
>>I was working on something very similar using VBScript but have since given
>>up (the website I was working on will almost certainly be replaced in a new
>>redevelopment.
>>Here's the pseudo code for what I was working on (hope it helps)
>>Define MyDate as 'Date'
>>����� MyDate = 04/05/2005
>>Change MyDate to 8-digit number (replace "/" with "")
>>����� MyDate = 04052005
>>Crop MyDate
>>MyDay = left(MyDate, 2)
>>MyMonth = mid(MyDate, 3,2)
>>MyYear = right(MyDate, 4)
>>'����� MyDay = 04
>>'����� MyMonth = 05
>>'����� MyYear = 2005
>>'Recompose MyDate as "international"
>>MyDate = MyYear & MyMonth & MyDay
>>����� MyDate = 20050504
>>Filename = (prefix & MyDate & suffix)
>>����� Filename = file_20050504.xls
>>'list through the directory
>>Crop Filename (mid (Filename, 9, 2)
>>����� sortfile = 05
>>If sortfile < current date then FSO Delete
>>Let me know what you think
>>cheers
>>Nick
>>Date: Tue, 03 May 2005 09:19:17 -0000
>>�� From: "Animesh K. Mishra" <[EMAIL PROTECTED]>
>>Subject: Fiding file with a particular naming convention.
>>Hi,
>>��� I have an application in C# that creates excel files with a
>>particular naming convention (Filename_Date_time.xls)and saves it to
>>the disc.For space management I want to add one more function to it
>>that will search for the files more than 24 hours old and delete them.
>>Can anyone please help... Its urgent.
>>Waiting impatiently for ur response
>>Animesh
>>--------------------------------------------------------------------------------
>>This E-Mail is intended only for the addressee. Its use is limited to that
>>intended by the author at the time and it is not to be distributed without the
>>author's consent. Unless otherwise stated, the State of Queensland accepts no
>>liability for the contents of this E-Mail except where subsequently confirmed
>>in
>>writing. The opinions expressed in this E-Mail are those of the author and do
>>not necessarily represent the views of the State of Queensland. This E-Mail is
>>confidential and may be subject to a claim of legal privilege.
>>If you have received this E-Mail in error, please notify the author
>>and delete this message immediately.
>>--------------------------------------------------------------------------------
>>!DEPTSTAMP1!
>>Yahoo! Groups Links
>>To visit your group on the web, go to:
>>
>http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/
>>
>>
>>To unsubscribe from this group, send an email to:
>>[EMAIL PROTECTED]
>>
>>
>>Your use of Yahoo! Groups is subject to the
>>Yahoo! Terms of Service
>>.
>>
>[Non-text portions of this message have been removed]
>Yahoo! Groups Links
>To visit your group on the web, go to:
>http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/
>�
>
>To unsubscribe from this group, send an email to:
>[EMAIL PROTECTED]
>�
>
>Your use of Yahoo! Groups is subject to the
>Yahoo! Terms of Service
>.
>
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
