using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace dt_to_excel
{
public partial class Form2 : Form
{
private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
private static Microsoft.Office.Interop.Excel.Application oXL;
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
if (File.Exists(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls"))
{
}
else
{
createNewFile();
}
}
public static void ReadExistingExcel()
{
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string path = @"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls";
if (File.Exists(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls"))
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Workbooks workbooks = oXL.Workbooks;
//mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
for (int index = 1; index <= 1; index++)
{
int rowNo = rowCount+1;
mWSheet1.Cells[rowCount + index, 1] = rowNo;
mWSheet1.Cells[rowCount + index, 2] = textBox1.Text;
mWSheet1.Cells[rowCount + index, 3] = textBox2.Text;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
else
{
createNewFile();
}
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
}
}
private void createNewFile()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Sheet 1 content";
xlWorkBook.SaveAs(@"C:\Users\deepika.W109\Documents\Visual Studio 2012\Projects\Websitechangesdemo1\dt to excel\Book2.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created..");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
No comments:
Post a Comment