LOKESH MSBI

Mail merge in the ASP.NET

Steps :-

First add this reference

using Microsoft.Office.Interop.Word;

Microsoft Word 15.0 Object Library (or any related version).

Design Code

</head>

<body>

<div>

 <asp:Button ID="Button2" runat="server" onclick="Button2_Click"

Text="Button" />

</div>

</form>

</body>

</html>

. CS Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Word=Microsoft.Office.Interop.Word;

namespace SampleTestingCodes

{

public partial class MailMergeAsp : System.Web.UI.Page

{

Word.Application wrdApp;

Word._Document wrdDoc;

Object oMissing = System.Reflection.Missing.Value;

Object oFalse = false;

protected void Page_Load(object sender, EventArgs e)

{

}

private void InsertLines(int LineNum)

{

int iCount;

// Insert "LineNum" blank lines.

for (iCount = 1; iCount <= LineNum; iCount++)

{

wrdApp.Selection.TypeParagraph();

}

private void FillRow(Word._Document oDoc, int Row, string Text1,

string Text2, string Text3, string Text4)

{

// Insert the data into the specific cell.

oDoc.Tables[1].Cell(Row, 1).Range.InsertAfter(Text1);

oDoc.Tables[1].Cell(Row, 2).Range.InsertAfter(Text2);

oDoc.Tables[1].Cell(Row, 3).Range.InsertAfter(Text3);

oDoc.Tables[1].Cell(Row, 4).Range.InsertAfter(Text4);

}

private void CreateMailMergeDataFile()

{

Microsoft.Office.Interop.Word._Document oDataDoc;

int iCount;

Object oName = "C:\\DataDoc.doc";

Object oHeader = "FirstName, LastName, Address, CityStateZip";

wrdDoc.MailMerge.CreateDataSource(ref oName, ref oMissing,

ref oMissing, ref oHeader, ref oMissing, ref oMissing,

ref oMissing, ref oMissing, ref oMissing);

// Open the file to insert data.

oDataDoc = wrdApp.Documents.Open(ref oName, ref oMissing,

ref oMissing, ref oMissing, ref oMissing, ref oMissing,

ref oMissing/*, ref oMissing */);

for (iCount = 1; iCount <= 2; iCount++)

{

oDataDoc.Tables[1].Rows.Add(ref oMissing);

}

// Fill in the data.

FillRow(oDataDoc, 2, "Steve", "DeBroux",

"4567 Main Street", "Buffalo, NY 98052");

FillRow(oDataDoc, 3, "Jan", "Miksovsky",

"1234 5th Street", "Charlotte, NC 98765");

FillRow(oDataDoc, 4, "Brian", "Valentine",

"12348 78th Street Apt. 214",

"Lubbock, TX 25874");

// Save and close the file.

oDataDoc.Save();

oDataDoc.Close(ref oFalse, ref oMissing, ref oMissing);

}

protected void Button2_Click(object sender, EventArgs e)

{

Word.Selection wrdSelection;

Word.MailMerge wrdMailMerge;

Word.MailMergeFields wrdMergeFields;

Microsoft.Office.Interop.Word.Table wrdTable;

string StrToAdd;

// Create an instance of Word and make it visible.

wrdApp = new Word.Application();

wrdApp.Visible = true;

// Add a new document.

wrdDoc = wrdApp.Documents.Add(ref oMissing, ref oMissing,

ref oMissing, ref oMissing);

wrdDoc.Select();

wrdSelection = wrdApp.Selection;

wrdMailMerge = wrdDoc.MailMerge;

// Create a MailMerge Data file.

CreateMailMergeDataFile();

// Create a string and insert it into the document.

StrToAdd = "State University\r\nElectrical Engineering Department";

wrdSelection.ParagraphFormat.Alignment =

Word.WdParagraphAlignment.wdAlignParagraphCenter;

wrdSelection.TypeText(StrToAdd);

InsertLines(4);

// Insert merge data.

wrdSelection.ParagraphFormat.Alignment =

Word.WdParagraphAlignment.wdAlignParagraphLeft;

wrdMergeFields = wrdMailMerge.Fields;

wrdMergeFields.Add(wrdSelection.Range, "FirstName");

wrdSelection.TypeText(" ");

wrdMergeFields.Add(wrdSelection.Range, "LastName");

wrdSelection.TypeParagraph();

wrdMergeFields.Add(wrdSelection.Range, "Address");

wrdSelection.TypeParagraph();

wrdMergeFields.Add(wrdSelection.Range, "CityStateZip");

InsertLines(2);

// Right justify the line and insert a date field

// with the current date.

wrdSelection.ParagraphFormat.Alignment =

Word.WdParagraphAlignment.wdAlignParagraphRight;

Object objDate = "dddd, MMMM dd, yyyy";

wrdSelection.InsertDateTime(ref objDate, ref oFalse, ref oMissing,

ref oMissing, ref oMissing);

InsertLines(2);

// Justify the rest of the document.

wrdSelection.ParagraphFormat.Alignment =

Word.WdParagraphAlignment.wdAlignParagraphJustify;

wrdSelection.TypeText("Dear ");

wrdMergeFields.Add(wrdSelection.Range, "FirstName");

wrdSelection.TypeText(",");

InsertLines(2);

// Create a string and insert it into the document.

StrToAdd = "Thank you for your recent request for next " +

"semester's class schedule for the Electrical " +

"Engineering Department. Enclosed with this " +

"letter is a booklet containing all the classes " +

"offered next semester at State University. " +

"Several new classes will be offered in the " +

"Electrical Engineering Department next semester. " +

"These classes are listed below.";

wrdSelection.TypeText(StrToAdd);

InsertLines(2);

// Insert a new table with 9 rows and 4 columns.

wrdTable = wrdDoc.Tables.Add(wrdSelection.Range, 9, 4,

ref oMissing, ref oMissing);

// Set the column widths.

wrdTable.Columns[1].SetWidth(51, Word.WdRulerStyle.wdAdjustNone);

wrdTable.Columns[2].SetWidth(170, Word.WdRulerStyle.wdAdjustNone);

wrdTable.Columns[3].SetWidth(100, Word.WdRulerStyle.wdAdjustNone);

wrdTable.Columns[4].SetWidth(111, Word.WdRulerStyle.wdAdjustNone);

// Set the shading on the first row to light gray.

wrdTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex =

Word.WdColorIndex.wdGray25;

// Bold the first row.

wrdTable.Rows[1].Range.Bold = 1;

// Center the text in Cell (1,1).

wrdTable.Cell(1, 1).Range.Paragraphs.Alignment =

Word.WdParagraphAlignment.wdAlignParagraphCenter;

// Fill each row of the table with data.

FillRow(wrdDoc, 1, "Class Number", "Class Name",

"Class Time", "Instructor");

FillRow(wrdDoc, 2, "EE220", "Introduction to Electronics II",

"1:00-2:00 M,W,F", "Dr. Jensen");

FillRow(wrdDoc, 3, "EE230", "Electromagnetic Field Theory I",

"10:00-11:30 T,T", "Dr. Crump");

FillRow(wrdDoc, 4, "EE300", "Feedback Control Systems",

"9:00-10:00 M,W,F", "Dr. Murdy");

FillRow(wrdDoc, 5, "EE325", "Advanced Digital Design",

"9:00-10:30 T,T", "Dr. Alley");

FillRow(wrdDoc, 6, "EE350", "Advanced Communication Systems",

"9:00-10:30 T,T", "Dr. Taylor");

FillRow(wrdDoc, 7, "EE400", "Advanced Microwave Theory",

"1:00-2:30 T,T", "Dr. Lee");

FillRow(wrdDoc, 8, "EE450", "Plasma Theory",

"1:00-2:00 M,W,F", "Dr. Davis");

FillRow(wrdDoc, 9, "EE500", "Principles of VLSI Design",

"3:00-4:00 M,W,F", "Dr. Ellison");

// Go to the end of the document.

Object oConst1 = Word.WdGoToItem.wdGoToLine;

Object oConst2 = Word.WdGoToDirection.wdGoToLast;

wrdApp.Selection.GoTo(ref oConst1, ref oConst2, ref oMissing, ref oMissing);

InsertLines(2);

// Create a string and insert it into the document.

StrToAdd = "For additional information regarding the " +

"Department of Electrical Engineering, " +

"you can visit our Web site at ";

wrdSelection.TypeText(StrToAdd);

// Insert a hyperlink to the Web page.

Object oAddress = "http://www.ee.stateu.tld";

Object oRange = wrdSelection.Range;

wrdSelection.Hyperlinks.Add(oRange, ref oAddress, ref oMissing,

ref oMissing, ref oMissing, ref oMissing);

// Create a string and insert it into the document

StrToAdd = ". Thank you for your interest in the classes " +

"offered in the Department of Electrical " +

"Engineering. If you have any other questions, " +

"please feel free to give us a call at " +

"555-1212.\r\n\r\n" +

"Sincerely,\r\n\r\n" +

"Kathryn M. Hinsch\r\n" +

"Department of Electrical Engineering \r\n";

wrdSelection.TypeText(StrToAdd);

// Perform mail merge.

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;

wrdMailMerge.Execute(ref oFalse);

// Close the original form document.

wrdDoc.Saved = true;

wrdDoc.Close(ref oFalse, ref oMissing, ref oMissing);

// Release References.

wrdSelection = null;

wrdMailMerge = null;

wrdMergeFields = null;

wrdDoc = null;

wrdApp = null;

// Clean up temp file.

System.IO.File.Delete("C:\\DataDoc.doc");

}

Create one empty document with given or required location and execute

Regards

LOKESH

How to import excel with multiple sheets to the database using SSIS

Introduction

Generally we have task likr import the excel data to sal data table .. ??? we will use the ssis tool to overcome these tasks we know . if the same task we make some thing completex like single excel file having the multiple sheets (sheet1,steet2 ……) all the sheets table data need to import to database table

Look here the process

1. First create one excel file and name it as “Sampledata excel” and save it as 97-2003 excel file formate because my system have that version support for the reason .

2.creathe data like

SNo	Name	Salary
1	Krishn	10000

And select the data – got to insert tab – select the table – one popup will come juct click ok – and save – like that create all the sheets with different data to chek

SNo	Name	Salary
2	Krishn1	20000

SNo	Name	Salary
3	Krishn2	30000

3. Create one SSIS project and add new ssis package

Go to Control flow tab – right click here and select variables – add one variable and name it as “VariableTableName” – select data type as string – value is sheet name “Sheet1$”(don’t forgot to put he $ symbole to the value ).

4.In control flow tab drag and drop the For Each loop and – right click on the for each loop and click edit – in that go to collocation tab – select “For Each ADO.NET Schema ROOT Set ” in the enumerator – in enumerator configuration select connection as new connection – one popup will open – in that click new again one more popup will open – in that select the provider as –

“Microsoft Jet 4.0 OLE DB Provider” click ok – and browse excel .xls file – and go to ALL tab – in Advanced properties “Extended Provider as Excel 8.0” text the connection ok .