Introduction
Sometimes, we need to read huge amounts of data from text or CSV files and convert or put the data into Excel. To do that, we need to create an Excel file first. We create the xlexcel application for opening Excel. And then, we create xlWorkbook
and xlWorksheet
. When we open a particular Excel workbook and worksheet, we need to set the range of where exactly the data needs to be posted and in which sheet the data needs to be presented, like sheet1 or sheet2 or so on.
After pasting the data, we would save the file with the required extension. We then close the particular file. If you want to watch the file, then we have to comment xlexcel.Quit()
and uncomment xlexcel.Visible = true
.
Then we can see the Excel file too.
Creating the Form
Creating xlsConvert
private void btnXlsConvert_Click(object sender, EventArgs e)
{
StreamReader objReader = new StreamReader(@"received.txt");
string sLine = "";
ArrayList arrText = new ArrayList();
while (sLine != null)
{
sLine = objReader.ReadLine();
if (sLine != null)
arrText.Add(sLine);
}
callExcel(arrText, true);
}
Creating xlsxConvert
private void btnXlsxConvert_Click(object sender, EventArgs e)
{
try
{
StreamReader objReader = new StreamReader(@"received.txt");
string sLine = "";
ArrayList arrText = new ArrayList();
while (sLine != null)
{
sLine = objReader.ReadLine();
if (sLine != null)
arrText.Add(sLine);
}
callExcel(arrText, false);
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void callExcel(ArrayList arrText, bool value)
{
try
{
String textString = null;
foreach (var item in arrText)
{
textString = textString + item + Environment.NewLine;
}
Clipboard.SetText(textString);
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application();
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.Paste(CR, false);
if (value == true)
{
try
{
xlWorkSheet.SaveAs(@"C:\Users\U0153056\Desktop\receivedNew.xls");
}
catch (Exception)
{
MessageBox.Show("File already exist");
}
}
else
{
try
{
xlWorkSheet.SaveAs(@"C:\Users\U0153056\Desktop\receivedNew.xlsx");
}
catch (Exception)
{
MessageBox.Show("File already exist");
}
}
xlexcel.Quit();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
Before you execute this application, you are supposed to include using the Excel= Microsoft.Office.Interop.Excel
namespace. For this, we need to add the namespace in the references (right click on References and click on Add Reference and then select Microsoft.Office.Interop.Excel
).
I am a 29 year old software Web Developer from Hyderabad, India. I have been working since approximately age 25. Where as in IT Development industry since 27. I am Microsoft Certified Technology Specialist.
I have taught myself in development, beginning with Microsoft's technologies ASP.NET, Approximately 3 years ago, I was given an opportunity to work as a freelance in the tech field. Now I am working as a web developer where my roles make me purely in web based technology solutions which manage and control access to applications and patient information stored in legacy systems, client-server applications.
I too had an opportunity to train some IT professionals with technical skills in development area. Which became my passion.
I have worked on various .NET framework versions(2.0 , 3.5, 4.0) and have been learning every new technology being introduced. Currently, I am looking forward to working in R & D in .Net to create distributed, reusable applications.