Create a windows Form Application
Create a button while clicking button perform these operations
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.Office.Interop.Excel;
using System;
using System.Linq;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using System.Runtime.InteropServices;
using System.IO;
namespace ReadExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public static void get_ExcelFile_append_textFiile()
{
//Excel File
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\karthikeyan\Documents\sample.xlsx");
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string fileName = @"C:\Users\karthikeyan\Documents\sample.txt";
String[] str_array = new String[2];
// Traversal
for (int i = 1; i <= colCount; i++)
{
for (int j = 1; j <= rowCount; j++)
{
if (xlRange.Cells[j, i] != null && xlRange.Cells[j, i].Value2 != null)
{
str_array[0] += xlRange.Cells[j, i].Value2.ToString() + ",";
}
else if (xlRange.Cells[j, i].Value2 == null)
{
str_array[0] += xlRange.Cells[j, i].Value2 + ",";
}
}
using (StreamWriter stream = File.AppendText(fileName))
{
str_array[0] = str_array[0].Remove(str_array[0].LastIndexOf(','));
str_array[0] = str_array[0] + (";");
stream.WriteLine(str_array[0]);
Console.WriteLine(str_array[0]);
}
str_array[0] = null;
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
//Text File
public static void create_text_file()
{
string fileName = @"C:\Users\karthikeyan\Documents\sample.txt";
try
{
if (File.Exists(fileName))
File.Delete(fileName);
using (FileStream fs = File.Create(fileName))
{
Console.WriteLine("File Created Sucessfully");
}
}
catch (Exception Ex)
{
Console.WriteLine(Ex.ToString());
}
}
private void ReadExcel_Click(object sender, EventArgs e)
{
create_text_file();
get_ExcelFile_append_textFiile();
}
}
}
Create a button while clicking button perform these operations
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.Office.Interop.Excel;
using System;
using System.Linq;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using System.Runtime.InteropServices;
using System.IO;
namespace ReadExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public static void get_ExcelFile_append_textFiile()
{
//Excel File
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\karthikeyan\Documents\sample.xlsx");
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string fileName = @"C:\Users\karthikeyan\Documents\sample.txt";
String[] str_array = new String[2];
// Traversal
for (int i = 1; i <= colCount; i++)
{
for (int j = 1; j <= rowCount; j++)
{
if (xlRange.Cells[j, i] != null && xlRange.Cells[j, i].Value2 != null)
{
str_array[0] += xlRange.Cells[j, i].Value2.ToString() + ",";
}
else if (xlRange.Cells[j, i].Value2 == null)
{
str_array[0] += xlRange.Cells[j, i].Value2 + ",";
}
}
using (StreamWriter stream = File.AppendText(fileName))
{
str_array[0] = str_array[0].Remove(str_array[0].LastIndexOf(','));
str_array[0] = str_array[0] + (";");
stream.WriteLine(str_array[0]);
Console.WriteLine(str_array[0]);
}
str_array[0] = null;
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
//Text File
public static void create_text_file()
{
string fileName = @"C:\Users\karthikeyan\Documents\sample.txt";
try
{
if (File.Exists(fileName))
File.Delete(fileName);
using (FileStream fs = File.Create(fileName))
{
Console.WriteLine("File Created Sucessfully");
}
}
catch (Exception Ex)
{
Console.WriteLine(Ex.ToString());
}
}
private void ReadExcel_Click(object sender, EventArgs e)
{
create_text_file();
get_ExcelFile_append_textFiile();
}
}
}
No comments:
Post a Comment