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
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();}
}
Comments
Post a Comment