Microsoft.Office.Interop.Excel.Application ?

Question

User410767947 posted

i have sqlserver as database and asp.net vb 2005 web application . i want to write a select query with group by or sum and display in the excel sheet . How to do it . I want to align the data as i need .

Answer 1

User-1171043462 posted

you can use ADO.Net to insert data row by row if you want I can provide the code

Answer 2

User410767947 posted

s can u please send me the code . i am using asp.net VB  webapplication with sqlserver 2005

Answer 3

User410767947 posted

I have a table

PID	SUBHEAD	SUBHEAID	LINEITEMDESC	UNITOFMEASURE	QTY	RATE	LINEID
7	DRILLING1	SH1	1	UOM1	1	1	SH1/1
7	PIPING	SH2	2	UOM1	2	2	SH2/1
7	LABOUR REQUIRED	SH3	3	UOM1	3.123	3	SH3/1
7	DIGGING	SH4	4	UOM1	4	4	SH4/1

i want to get excel like this

 

ITEM REF	ITEM DESCRIPTION	QTY	UNIT	RATE WORDS	RATE FIGURES	AMOUNT
	DIGGING
SH4/1	4	4	UOM1
	SUM	4
ITEM REF	ITEM DESCRIPTION	QTY	UNIT	RATE WORDS	RATE FIGURES	AMOUNT
	DRILLING1
SH1/1	1	1	UOM1
	SUM	1
ITEM REF	ITEM DESCRIPTION	QTY	UNIT	RATE WORDS	RATE FIGURES	AMOUNT
	LABOUR REQUIRED
SH3/1	3	3.123	UOM1
	SUM	3.123
ITEM REF	ITEM DESCRIPTION	QTY	UNIT	RATE WORDS	RATE FIGURES	AMOUNT
	PIPING
SH2/1	2	2	UOM1			</FORM>
	SUM	2
	TOTAL SUM	10.123

Can u please tell me how to do this ?

Answer 4

User-1171043462 posted
private Boolean AddExcelRow(String strFileName, String strOrderID, String strClientRefNum, String strReportType, String strSubjectType, String strSubjectName, String strCountry, String strOtherDetails, int intPrimary)

{

if (!File.Exists(strFileName)) return false;

String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=" + strFileName + ";" +

"Extended Properties='Excel 8.0;HDR=Yes'";

String strPrimary;

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

try

{

connExcel.Open();

cmdExcel.Connection = connExcel;

if (intPrimary == 1)

strPrimary = "Yes";

else

strPrimary = "No";

//Add New Row to Excel File

cmdExcel.CommandText = "INSERT INTO [OrderDetails$] (OrderID, ClientReferenceNumber, ReportType, " +

"SubjectType, SubjectName, SubjectCountry, PrimarySubject, OtherDetails) " +

"values ('" + strOrderID + "', '" + strClientRefNum + "', '" + strReportType + "', '" + strSubjectType +"', '" + strSubjectName + "', '" + strCountry + "', '" + strPrimary + "', '" + strOtherDetails + "')";

cmdExcel.ExecuteNonQuery();

return true;

}

catch

{

return false;

}

finally

{

connExcel.Close();

cmdExcel.Dispose();

connExcel.Dispose();

}

}

Answer 5

User410767947 posted

How to create a excel sheet in the specified format ? . Like Group by , sum etc, each column heading  ?

Answer 6

User-1171043462 posted

in this you can only transfer data in bulk to excel

You will have to create a template excel file with all headers and then use the same templete file each time to fill data

Else you use Interop Excel to create an excel file with formatting

http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx

http://www.xtremedotnettalk.com/showthread.php?t=91694

http://dotnetperls.com/Content/Excel-Interop-Performance.aspx

Answer 7

User410767947 posted
all threads  has c# code . can u please send me asp.net VB code .

Answer 8

User-1171043462 posted

here is some sample code

Imports Microsoft.Office.Interop

Dim oExcel As Excel.ApplicationClass

Dim oBook As Excel.WorkbookClass

Dim oBooks As Excel.Workbooks

'Start Excel and open the workbook.

oExcel = New Excel.ApplicationClass

oExcel.Visible = True

oBooks = oExcel.Workbooks

oBook = oBooks.Open("c:\InputSheet-M.xls")Dim oSheet As Excel.Sheets

oSheet = oBook.Sheets()

'Run the macros.

'oExcel.Run("Macro3")

 

'Clean-up: Close the workbook and quit Excel.

oBook.Close(False)

System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)

oBook = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)

oBooks = Nothing

oExcel.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

oExcel = Nothing

 

Also some link

http://www.codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

http://www.dynamicajax.com/fr/Creating_Excel_From_VB_DOT_NET-.html