Sunday, 21 July 2013

how to save each sheet in an Excel workbook to separate CSV files or saving data in Excel in different sheets of a workbook

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

in C#:

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.

If you like my blog Aspdotnet-Roja then why are you waiting like my blog through facebook page Aspdotnet-Roja

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.