Re: Generating Excel reports by using .Net - C# Discussion Boards

Re: Generating Excel reports by using .Net

Charith Jayasundara19-Sep-07 21:43

19-Sep-07 21:43

Dear Paul,

Thank you very much for the reply.

I just noticed another more important fact. I'll receive all the required data through a stored procedure. So I don't want to use excel functions, I can calculate everything in C# and post the calculated value on the cell.

So "Excel automation library" would be suitable for that, isn't it?

Thanks Again!

Re: Generating Excel reports by using .Net

pmarfleet19-Sep-07 21:56

pmarfleet

19-Sep-07 21:56

Yes, but you're still better off using the ODBC/Jet driver rather than Excel automation for the reasons I mentioned in my previous post. This is what I recently used when creating a data export facility for an ASP.NET web app.

Paul

Re: Generating Excel reports by using .Net

Charith Jayasundara19-Sep-07 22:13

Charith Jayasundara

19-Sep-07 22:13

Hi,

ok, is ODBC/Jet driver functionality depends on the excel version (2003 or 2007)?

Thanks!

Re: Generating Excel reports by using .Net

pmarfleet20-Sep-07 0:13

pmarfleet

20-Sep-07 0:13

No. You don't need Excel installed on the server, only the driver.

Paul

Re: Generating Excel reports by using .Net

Charith Jayasundara20-Sep-07 0:36

Charith Jayasundara

20-Sep-07 0:36

Hi Paul,

Could you please provide me a good C# example for odbc/jet driver thing?
I tried on the internet but still I couldn't find a proper one. I could find several nice article for "Excel Automation Library".

Thanks for your kind help!
Good Luck!!!

jayasshc

Re: Generating Excel reports by using .Net

pmarfleet20-Sep-07 2:48

pmarfleet

20-Sep-07 2:48

Try something like this:

//create random file so as not to conflict
string dataFile = "<path to your excel file>";
		
//make sure the temp file is not read only (copied from template) as this stops the OLE connection from updating
File.SetAttributes(dataFile, FileAttributes.Normal);

//inject data
System.Data.OleDb.OleDbConnection tempExcelConnection = new System.Data.OleDb.OleDbConnection();
tempExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
tempExcelConnection.ConnectionString += "Data Source=" + dataFile + ";";
tempExcelConnection.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes;"";";
System.Data.OleDb.OleDbCommand tempExcelCommand = new System.Data.OleDb.OleDbCommand();
tempExcelCommand.Connection = tempExcelConnection;
			
System.Data.SqlClient.SqlDataReader dr = null;

tempExcelCommand.Connection.Open();

// TODO: Get data for SqlDataReader

if (dr.HasRows)
{
	while(dr.Read())
	{
		//NB. Ths query expects a named range called "mynamedrange" containing 5 columns
		//the data will be inserted after the named range so should only include the column headings
		tempExcelCommand.CommandText = String.Format("INSERT INTO [mynamedrange] VALUES ({0}, {1}, {2}, {3}, {4})", dr[0], dr[1], dr[2], dr[3], dr[4]);
		tempExcelCommand.ExecuteNonQuery();
	}
}

dr.Close();
tempExcelCommand.Connection.Close();

Regards

Paul

Re: Generating Excel reports by using .Net

Urs Enzler19-Sep-07 23:04

Urs Enzler

19-Sep-07 23:04

This article shows you another approach: http://www.bbv.ch/pdf_files/system_event/2005_XMLto%20Excel.pdf

XSLT is used to create Excel files.

The advantage is that you do not need Excel itself to create the files.

The disadvantage is that you have a Excel-xml file in the end. If you need to have an xls then you still have to use Excel to transform the xml to a xls file. But this is mus easier then program all data into Excel with interop.

-^-^-^-^-^-
no risk no funk ................... please vote ------>

Re: Generating Excel reports by using .Net

Charith Jayasundara19-Sep-07 23:45

Charith Jayasundara

19-Sep-07 23:45

Thanks!

jayasshc

Re: Generating Excel reports by using .Net

eoe19-Sep-07 23:17

eoe

19-Sep-07 23:17

Hi,

if you are looking for a solution for a large project, you should evaluate the libraries offered by aspose http://www.aspose.com[^]. I started a project using COM/INTEROP to create an interface to the office world. Then it was necessary to support the generation of word documents on a server without having any MS office application installed. We decided to use aspose. Finally we were able to improve our applications noticeably.

Erik

How to get now month?

somsakchoto19-Sep-07 20:29

somsakchoto

19-Sep-07 20:29

i want get now month from system. how to get it.

thank you

Re: How to get now month?

JoeSharp19-Sep-07 20:34

JoeSharp

19-Sep-07 20:34

hi
int month = DateTime.Now.Month;

regards

Searching a Record

Imranlogi19-Sep-07 20:22

Imranlogi

19-Sep-07 20:22

Can anyone tell me how to search a particular record from the MS Access database using C#. I want to use a combobox on which primary key of the table will be there and clicking on any particular primary key value, all the records related to that primary key value should be displayed in respective text boxes.
Help appriciated...
Thanks

Imran

Re: Searching a Record

~~_{lost in transition}~~ 20-Sep-07 12:36

~~_{lost in transition}~~

20-Sep-07 12:36

Can you clarify a little more what you need to do?
Do you need to search one table for a PK or the entire DB?

ImranTech wrote:
all the records related to that primary key value

Normally a PK is unique within the table.

God Bless,
Jason

I am not perfect but I try to be better than those before me.
So those who come after me will be better than I am.

Pass-Thru Authentication

classNoob19-Sep-07 15:17

classNoob

19-Sep-07 15:17

I have come up with an authentication scheme that I wanted to run past everyone to see if you all agree with my logic. Here is the process:

1) The clients windows login credentials are automatically passed (manual option also provided) with the webservice call using:

WebService.Credentials = System.Net.CredentialCache.DefaultCredentials

2) The web.config is set to impersonate the user.

3) At this point I thought I could assume that the client is who they say they are since the user was able to log in to IIS. My plan is to use the username and SID to retrieve the information for that user as opposed to the traditional username and password scenario.

4) If the user's SID is ever out of sync I will provide the admin a sync option. Basically the same option I would have given them when they set up the user.

Unsure | :~

I guess I am worried about two things. Can I rely on these SID's as long as the user is not recreated in the domain and hopefully I can store the SID's in a database. I am not sure how "variable" in length these SID's can be. If I stored it in a character field at 255 will I be safe?

Working with a strange data type

Klazen19-Sep-07 14:04

Klazen

19-Sep-07 14:04

Hey all,
I am trying to load and parse a MIDI file in C#. So far, I have been able to read the header and make it to the first track. However, I am now confused. Some values in the file are kept as a variable length format (link to the page I am looking at: MIDI File Format[^]), and I am not sure how to manipulate this. Basically, it shifts bits around like so: C8 (11001000) becomes 8148 (10000001 01001000). The top bit is used to signal that another 7 bits of information follow. So... I guess I need to know how to move the bits back into a format that I can use. Is there a way to handle individual bits in C#?

Re: Working with a strange data type [modified]

ChrisKo19-Sep-07 14:45

ChrisKo

19-Sep-07 14:45

Maybe this will help.

http://www.codeproject.com/csharp/CSBitWiseHelper.asp?df=100&forumid=263394&exp=0&select=1362446[^]

http://www.codeproject.com/cs/media/MIDIToolkit.asp[^]

http://www.codeproject.com/cs/media/ProjectMIDI.asp[^]

-- modified at 20:01 Wednesday 19th September, 2007

Re: Working with a strange data type

Klazen19-Sep-07 16:37

Klazen

19-Sep-07 16:37

Thanks a bunch for the reply. I tried using the first link, but got confused. The last link seems to be very helpful, almost exactly what I need. Thanks again Big Grin | :-D

Messages to a hidden window?

hauptmatter19-Sep-07 13:15

hauptmatter

19-Sep-07 13:15

I have an application that logs data based on messages sent to it from another app. It works great.

Now I want to hide it from the taskbar and live in the system tray (Notify Icon area).

When I call "ShowInTaskbar = false;" for my mainwindow, it stops recieving the messages.

I am registering the message in question and sending it to my app with a HWND_BROADCAST.

Any Ideas?

Re: Messages to a hidden window?

mav.northwind19-Sep-07 20:13

mav.northwind

19-Sep-07 20:13

Hi!
I'm using a similar approach in one of my projects (although the hidden window is created from C++) and it works nicely, so it shouldn't be a general problem.
Could it relate to the fact that switching ShowInTaskbar recreates the window handle?

Regards,
mav

--
Black holes are the places where God divided by 0...

ReportDocument and printtoprinter

Saamir19-Sep-07 12:10

Saamir

19-Sep-07 12:10

Hi guys,
I tried googling but no luck, please help, I need to confirm that a particular report was printed after printtoprinter is called. Please advice on how to achieve this.

sasa

Clean up while Session State is set to SQLServer

f_coy19-Sep-07 11:16

f_coy

19-Sep-07 11:16

I am trying to remove some images in a temperary directory when a user loses their session. Session_OnEnd is ignored when the Session State is set to "SQLServer". Does anyone have any ideas on how I can do this when the application is set to SQLServer mode? Thanks!

Performance question when generating a large batch file

Dan Neely19-Sep-07 9:52

Dan Neely

19-Sep-07 9:52

I figured I'd ask before rewriting to test the alternate implementation.

Is there any significant performance difference between:

for (i = 1; i < bignum; i++)
  myStreamWriter.WriteLine(GetNextLineOfData))

and

for (i = 1; i < bignum; i++)
  myStringBuilder.Append(GetNextLineOfData())
myStreamWriter.Write(myStringBuilder.ToString())

--
If you view money as inherently evil, I view it as my duty to assist in making you more virtuous.

Re: Performance question when generating a large batch file

Dave Kreskowiak19-Sep-07 9:58

Dave Kreskowiak

19-Sep-07 9:58

Yes, the second one is using a StringBuild to build a very large string, then calling ToString on it to create a new String object, then your writing the new string out to a file.

The two pieces of code are no doing the same thing. Your appending some data to a StringBuild, then creating a new String of all the data, appending some more data, creating a new String of all that data, ..., .... The string is getting bigger and bigger and bigger and your recreating the beginning part of the string over and over again with each pass. Your writing out something akin to:

1111 11112222 111122223333 1111222233334444 11112222333344445555 111122223333444455556666

whereas the first little snippet is doing this:

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Performance question when generating a large batch file

Luc Pattyn19-Sep-07 10:49

Luc Pattyn

19-Sep-07 10:49

Hi Dave,

Dave Kreskowiak wrote:
The two pieces of code are no doing the same thing

Indentation suggests and absence of curly brackets causes the output to be the same.

But I fully agree, the big string (and the iterative growth of the StringBuilder) are
bad for performance.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

this weeks tips:
- make Visual display line numbers: Tools/Options/TextEditor/...
- show exceptions with ToString() to see all information
- before you ask a question here, search CodeProject, then Google

Re: Performance question when generating a large batch file

Dave Kreskowiak19-Sep-07 11:03

Dave Kreskowiak

19-Sep-07 11:03

No semicolons either, so I really couldn't do anything other than guess...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.