Hi friends, in this article I will explain about how to save
each sheet in an Excel workbook to separate CSV files or saving data in Excel
in different sheets of a workbook.
I will explain through some example. I take one excel workbook
it contains 10 worksheets and worksheets names like c1 c2 c3 etc.
Open Microsoft visual studio
Open File-->
new-->project
And design the form like below. Take one label, two
text boxes and two buttons as shown
below.
I write the code as below.
in VB.NET:
|
Imports System.IO
Imports System.Data
Imports System.Windows.Forms
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim fn As [String] = ""
Dim excel
As New
Microsoft.Office.Interop.Excel.Application()
Dim
col_id, code, code1 As String
Dim
floor_count As Integer
Private Sub Form1_Load(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
code = "c:\windows\setup.txt"
code1 = Dir(code)
If
code1 = "" Then
Me.Close()
'MsgBox
(" !!! You are not the right
user !!! ")
'program
will not work if setup.txt is not present in specified path
Exit
Sub
End If
End Sub
Private Sub Button1_Click(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim
dlg As New
OpenFileDialog()
dlg.InitialDirectory = "d:\"
dlg.Filter = ".xls
files (*.xls)|*.xls|All files (*.*)|*.*"
If
dlg.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
'System.Threading.Thread.Sleep(500)
fn = dlg.FileName
End If
If fn
= "" Or
cid.Text = "" Then
If
fn = "" Then
MessageBox.Show("Select the File")
Else
MessageBox.Show("Enter Column ID")
End
If
Else
Dim
path1 As String
= fn
' path1
= "'" & path1 & "'"
Dim
wb As Microsoft.Office.Interop.Excel.Workbook
= excel.Workbooks.Open("" &
path1 & "")
Dim
ws = wb.Worksheets(cid.Text)
Dim
filename As String
col_id = ws.Cells(22, 6).Value
floor_count = ws.Cells(21,
6).Value
filename = "C:\temp\" & col_id & ".txt"
If
System.IO.File.Exists(filename) = True Then
System.IO.File.Delete(filename)
'MsgBox("delete")
End
If
Dim
swriter As StreamWriter
For
i As Integer
= 0 To (floor_count - 1)
swriter =
File.AppendText(filename)
swriter.WriteLine(ws.Cells(25
+ i, 1).Value & " ")
swriter.Close()
Next
wb.Close(True,
Type.Missing, Type.Missing)
excel.Quit()
MessageBox.Show("File Inserted")
Me.Close()
End If
End Sub
Private Sub Button2_Click(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
End Class
|
|
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
Public Class Form1
{
String fn
= "";
Microsoft.Office.Interop.Excel.Application
excel = new
Microsoft.Office.Interop.Excel.Application();
string
col_id;
string
code;
string
code1;
int floor_count;
private
void Form1_Load(System.Object sender, System.EventArgs e)
{
code = "c:\\windows\\setup.txt";
code1 = FileSystem.Dir(code);
if
(string.IsNullOrEmpty(code1)) {
this.Close();
//MsgBox (" !!! You
are not the right user !!! ")
//program will not work if
setup.txt is not
present in specified path
return;
}
}
private
void Button1_Click(System.Object sender, System.EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.InitialDirectory = "d:\\";
dlg.Filter = ".xls files (*.xls)|*.xls|All files (*.*)|*.*";
if
(dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
//System.Threading.Thread.Sleep(500)
fn = dlg.FileName;
}
if
(string.IsNullOrEmpty(fn) | string.IsNullOrEmpty(cid.Text)) {
if
(string.IsNullOrEmpty(fn)) {
MessageBox.Show("Select the File");
} else
{
MessageBox.Show("Enter Column ID");
}
} else
{
string
path1 = fn;
// path1 = "'" & path1 & "'"
Microsoft.Office.Interop.Excel.Workbook
wb = excel.Workbooks.Open("" +
path1 + "");
dynamic ws =
wb.Worksheets(cid.Text);
string
filename = null;
col_id = ws.Cells(22,
6).Value;
floor_count = ws.Cells(21,
6).Value;
filename = "C:\\temp\\" + col_id + ".txt";
if
(System.IO.File.Exists(filename) == true) {
System.IO.File.Delete(filename);
//MsgBox("delete")
}
StreamWriter swriter = null;
for
(int i = 0; i <= (floor_count - 1); i++) {
swriter =
File.AppendText(filename);
swriter.WriteLine(ws.Cells(25
+ i, 1).Value + " ");
swriter.Close();
}
wb.Close(true, Type.Missing, Type.Missing);
excel.Quit();
MessageBox.Show("File Inserted");
this.Close();
}
}
private
void Button2_Click(System.Object sender, System.EventArgs e)
{
this.Close();
}
Public
Form1()
{
Load += Form1_Load;
}
}
|
The below lines shows how to select the particular spreadsheet.
|
Dim wb As
Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("" & path1 & "")
Dim
ws = wb.Worksheets(cid.Text)
|
Dim ws =
wb.Worksheets(cid.Text) shows select the worksheet.cid.text is the textbox
value of enter column id textbox.
No comments:
Post a Comment