transfer contends from database table to excel

[carobee]

hi,
i want to transfer the contends of a table from database to excel. can i declare a excel sheet in c#

[LizR]

Have an ask of google. Google knows the answers

[carobee]

done dat. am able to do it now. but i want the column names also. Is that possible?

[LizR]

f you need to putput it, and keep the names, put the names into an array you can output as a header line.

[carobee]

Also if i want to save it somewhere of my wish what would the code be like. i have defined a workbook also populated the values. now while saving it is saving in the same place where project is created. i want to save it in some other place. my code is
wb.SaveAs(FileName,Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,
false,false,null,null,null);
what changes do i make?

[LizR]

Change the filename.. or is there some hidden part to that question

[carobee]

wb.SaveAs(FileName,Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,
false,false,null,null,null);

this is my code ? can you suggest any solution

[LizR]

Now you really are being stupid

the contents of "Filename" has to be what you wanted..

[carobee]

The contents of "Filename" that i can provide is just the any name for the file, not like "C:\....". it is getting saved in my documents folder.
Please give a proper solution

[LizR]

Sorry, but you are being stupid,

Put the path in your filename, and it will be saved where you tell it..... If you dont it would be saved in whats the "current directory"

[Jugortha]

If you try to export data from sql server to excel then use this stored procedure as bellow
Cretae an excel file in C: and name it Test.xls
CREATE proc EXP_FROM_SQLServer_TO_Excel
(
@File_Name as varchar(100) = ' ', - -The file content that would be exported from sql server
@Query AS VARCHAR(100) - -What you want exactly put in the excel sheet
)
as
BEGIN

--Provider
DECLARE @Provider VARCHAR(100)
–Excel Connection string
DECLARE @ECS VARCHAR(100)
--Command
DECLARE @Cmd VARCHAR(100)
–file name
DECLARE @FN VARCHAR(100)
Select @FN = 'C:\' + @File_Name + '.xls'
SELECT @Cmd = 'Copy C:\Test.xls ' + @ FN
EXEC MASTER..XP_CMDSHELL @Cmd, NO_OUTPUT
SET @Provider = 'Microsoft.Jet.OLEDB.4.0'
SET @ESC = 'Excel 8.0;Database=' + @FN
EXEC('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT Field1, …….., Fieldn FROM [Sheet2$]'') –replace the Sheet2$ by your sheet name

select Field1, …….., Fieldn from YourDataTable')

SET NOCOUNT OFF
END

Then you can call this stored procedure from SQL Server or even from C# code as follow

SqlConnection oConnection = new SqlConnection("ConnectionString");
SqlCommand oCommand = new SqlCommand();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.CommandText = "EXP_FROM_SQLServer_TO_Excel";
SqlParameter FileName = new SqlParameter("File_Name", yourInputValue);
SqlParameter Query = new SqlParameter("Query", yourinputvalue2);
oCommand.Parameters.Add(FileName);
oCommand.Parameters.Add(Query);
oCommand.ExecuteNonQuery();

I tried it and It works for sure

[carobee]

i have the procedure i have called it also. its running all well. only thing that is the hurdle is that i am not bale to save it anywhere. It gets stored in my documents folder only

[carobee]

Sorry, but you are being stupid,

Put the path in your filename, and it will be saved where you tell it..... If you dont it would be saved in whats the "current directory"

can you be more specific and write the code instead of suggesting what is wrong in my code.it will be more help if u would code the line for saving the file.

[LizR]

I cant express how angry you make me.

If you are incapable of changing the value of a variable or setting a variable to a value and your home work is to do th is.. You are on the wrong course.

[Jugortha]

Could you precise from where you are attemding to transfer the data