Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

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
Avatar of Kalpesh Chhatrala
Kalpesh Chhatrala
Flag of India image

Send me your Excel Helper Class. i will prepare sample for you.
Avatar of zimmer9

ASKER

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);
                }
            }
        }
Avatar of zimmer9

ASKER

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.
Avatar of zimmer9

ASKER

I've requested that this question be deleted for the following reason:

I rephrased the question to make it clearer.
ASKER CERTIFIED SOLUTION
Avatar of Kalpesh Chhatrala
Kalpesh Chhatrala
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial