How to generate 4 individual CSV files from an Excel 2003 workbook, in C#, using VS2010?

I am developing a C# web application using VS2010.

I have a directory titled U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\
that contains Excel 2003 workbooks made up of multiple worksheets in each workbook.

I would like to create a CSV file for each of 4 particular worksheets in each of the workbooks. The workbooks have other worksheets but I am only interested in creating
CSV files for 4 particular worksheets in each of the workbooks.

The 4 worksheets in each workbook that I would like to create CSV files for have the following worksheet names:

Bank Credits (A)
Bank Debits (B)
Book Debits (C)
Book Credits (D)

I use the following code in an Import click event to create a CSV file:

 protected void Import_Click(object sender, EventArgs e)
        {
            ExcelHelper.ConvertExcelToCsv(@"U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\105 Internal  08-28-2013.xls", @"U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\105 Internal  08-28-2013.csv", 7);

            string fileName = @"U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\105 Internal  08-28-2013.csv";


 public static class ExcelHelper
        {
            public static void ConvertExcelToCsv(string source, string destination, int sheetNumber = 1)
            {
                if (File.Exists(destination)) File.Delete(destination);
                Application xl = new Application();
                try
                {
                    Workbook workbook = xl.Workbooks.Open(source);
                    Worksheet ws = (Worksheet)workbook.Sheets[sheetNumber];
                    ws.SaveAs(destination, XlFileFormat.xlCSV);
                    Marshal.ReleaseComObject(ws);
                }
                finally
                {
                    xl.DisplayAlerts = false;
                    xl.Quit();
                    Marshal.ReleaseComObject(xl);
                }
            }
        }

Do you know how this C# code could be revised to read ALL of the XLS files in the directory U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\
and for each XLS file read, create 4 individual CSV files?

For this XLS file titled 105 Internal  08-28-2013.xls
I would like to generate 4 individual CSV files titled:

105_Bank Credits (A).csv
105_Bank Debits (B).csv
105_Book Debits (C).csv
105_Book Credits (D).csv

And then assume the next XLS file in the directory is titled "222 Internal  08-28-2013.xls"

I would like to generate 4 CSV files titled as follows:

222_Bank Credits (A).csv
222_Bank Debits (B).csv
222_Book Debits (C).csv
222_Book Credits (D).csv

Thus, if there are 20 XLS files in the directory, the application should generated 80 CSV files.

I have attached a sample workbook.
105-Internal--08-28-2013.xls
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kalpesh ChhatralaSoftware ConsultantCommented:
Send me your Excel Helper Class. i will prepare sample for you.
0
zimmer9Author Commented:
The Excel Helper Class is:

public static class ExcelHelper
        {
            public static void ConvertExcelToCsv(string source, string destination, int sheetNumber = 1)
            {
                if (File.Exists(destination)) File.Delete(destination);
                Application xl = new Application();
                try
                {
                    Workbook workbook = xl.Workbooks.Open(source);
                    Worksheet ws = (Worksheet)workbook.Sheets[sheetNumber];
                    ws.SaveAs(destination, XlFileFormat.xlCSV);
                    Marshal.ReleaseComObject(ws);
                }
                finally
                {
                    xl.DisplayAlerts = false;
                    xl.Quit();
                    Marshal.ReleaseComObject(xl);
                }
            }
        }
0
zimmer9Author Commented:
This class has a single static method called ConvertExcelToCsv, which takes an Excel file and creates a CSV file.

Since Excel files can have multiple sheets, there is the optional parameter of specifying what sheet to write as the CSV file. If you leave it blank it will default to the first one in the workbook.
0
zimmer9Author Commented:
I've requested that this question be deleted for the following reason:

I rephrased the question to make it clearer.
0
Kalpesh ChhatralaSoftware ConsultantCommented:
Sorry for late response. i am not getting any mail from EE.

below is sample code. you can modify as per your requirement.

        protected void Import_Click(object sender, EventArgs e)
        {
            String[] oFiles = Directory.GetFiles(@"U:\Visual Studio 2010\Projects\SMRWebForm\SMRWebForm\","*.Xls");

            for (int j = 0; j < oFiles.Length; j++)
            {
                ExcelHelper.ConvertExcelToCsv(oFiles[j],@"C:\MyCSVOutput\");
            }
        }

public static class ExcelHelper
    {
        public static void ConvertExcelToCsv(string source, string destination, int sheetNumber = 1)
        {
            FileInfo oFile = new FileInfo(source);


            if (File.Exists(destination)) File.Delete(destination);
            Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Workbook workbook = xl.Workbooks.Open(source);
                for (int i = 1; i <= workbook.Worksheets.Count; i++)
                {
                    Worksheet ws = (Worksheet)workbook.Sheets[i];

                    String cDestFile = destination + "_" + oFile.FullName.Replace(".xls", "").Replace(".xlsx", "") + "_" + ws.Name+".csv";
                    ws.SaveAs(cDestFile, XlFileFormat.xlCSV);
                    Marshal.ReleaseComObject(ws);
                }
            }
            finally
            {
                xl.DisplayAlerts = false;
                xl.Quit();
                Marshal.ReleaseComObject(xl);
            }
        }
    }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.