Friday, August 26, 2011

How to copy workSheet to Same workbook?

Posted by Rahul Kharde at 12:18 AM

This article features code samples to add new worksheet in the same workbook. The article also helps understand the basics behind the Excel application object and its usage for creating a Workbook and inserting Worksheets in it.

The code is pretty simple and straight forward. As a pre-requisite you should have Excel installed on your system. I have used C#.Net as my base language. Now Create a excel sheet (default worksheet name “Sheet1” must be exist in it) and store it some were in your machine.

In order to start using the code, add a reference to the COM object Microsoft Excel Object Library. Since I had Microsoft Office 2003 installed on my system, it was Microsoft Excel 11.0(Microsoft Office 2007 used Microsoft Excel 12.0) Object Library in my case. Now import the namespaces for the Excel library and InterpServices into your code

using Excel = Microsoft.Office.Interop.Excel;

ReleaseObject() is used to clean object resources form the memory

Now copy the following code to your Code behind file and HTML Code.

After running web page Click “Browse” button and select the excel sheet from your machine, and then click submit button, so it create copy of worksheet(named “CopiedSheet”) into your workbook, now it will open popup to save or open newly changed excel sheet

Download

HTML

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<div style="float: left; width: 100%;">

<div style="float: left; width: 100%; height: 25px;">

<div style="float: left; width: 15%;">

Upload File

</div>

<div style="float: right; width: 82%;">

<asp:FileUpload ID="FileUpload1" runat="server"/>

</div>

</div>

<div style="float: left; width: 100%; height: 20px;">

</div>

<div style="float: left; width: 100%;">

<asp:Button ID="Button1" runat="server" Text="Submits" OnClick="Button1_Click" />

</div>

</div>

</div>

</form>

</body>

</html>

Code Behind

protected void Button1_Click(object sender, EventArgs e)

{

try

{

Excel.Application excelApp = new Excel.Application();

String sheetPath = FileUpload1.PostedFile.FileName.ToString();

Excel.Workbook excelBook = excelApp.Workbooks.Open(sheetPath, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, true, false);

Excel._Worksheet excelWorksheet = (Excel._Worksheet)excelBook.Worksheets.get_Item("Sheet1");

excelWorksheet.Copy(missing, (Excel._Worksheet)excelBook.Worksheets.get_Item("Sheet1"));

excelWorksheet = (Excel._Worksheet)excelBook.Worksheets.get_Item(2);

excelWorksheet.Name = "CopiedSheet";

excelBook.Save();

excelApp.DisplayAlerts = true;

excelBook.Saved = true;

excelBook.Save();

excelBook.Close(true, null, null);

excelApp.Workbooks.Close();

excelApp.Quit();

ReleaseObject(excelWorksheet);

ReleaseObject(excelBook);

ReleaseObject(excelApp);

// Create New instance of FileInfo class to get the properties of the file being downloaded

FileInfo file = new FileInfo(sheetPath);

// Checking if file exists

if (file.Exists)

{

// Clear the content of the response

HttpContext.Current.Response.ClearContent();

// LINE1: Add the file name and attachment, which will force the open/cance/save dialog to show, to the header

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);

// Add the file size into the response header

HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());

// Set the ContentType

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)

HttpContext.Current.Response.TransmitFile(file.FullName);

// End the response

HttpContext.Current.Response.End();

}

}

catch (Exception ex)

{

}

}

private void ReleaseObject(object o)

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(o);

}

catch (Exception ex)

{

o = null;

}

finally

{

GC.Collect();

}

}



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top