zimmer9
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\S MRWebForm\
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.ConvertExcelTo Csv(@"U:\V isual Studio 2010\Projects\SMRWebForm\S MRWebForm\ 105 Internal 08-28-2013.xls", @"U:\Visual Studio 2010\Projects\SMRWebForm\S MRWebForm\ 105 Internal 08-28-2013.csv", 7);
string fileName = @"U:\Visual Studio 2010\Projects\SMRWebForm\S MRWebForm\ 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 [sheetNumb er];
ws.SaveAs(destination, XlFileFormat.xlCSV);
Marshal.ReleaseComObject(w s);
}
finally
{
xl.DisplayAlerts = false;
xl.Quit();
Marshal.ReleaseComObject(x l);
}
}
}
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\S MRWebForm\
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
I have a directory titled U:\Visual Studio 2010\Projects\SMRWebForm\S
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.ConvertExcelTo
string fileName = @"U:\Visual Studio 2010\Projects\SMRWebForm\S
public static class ExcelHelper
{
public static void ConvertExcelToCsv(string source, string destination, int sheetNumber = 1)
{
if (File.Exists(destination))
Application xl = new Application();
try
{
Workbook workbook = xl.Workbooks.Open(source);
Worksheet ws = (Worksheet)workbook.Sheets
ws.SaveAs(destination, XlFileFormat.xlCSV);
Marshal.ReleaseComObject(w
}
finally
{
xl.DisplayAlerts = false;
xl.Quit();
Marshal.ReleaseComObject(x
}
}
}
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\S
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
Send me your Excel Helper Class. i will prepare sample for you.
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 [sheetNumb er];
ws.SaveAs(destination, XlFileFormat.xlCSV);
Marshal.ReleaseComObject(w s);
}
finally
{
xl.DisplayAlerts = false;
xl.Quit();
Marshal.ReleaseComObject(x l);
}
}
}
public static class ExcelHelper
{
public static void ConvertExcelToCsv(string source, string destination, int sheetNumber = 1)
{
if (File.Exists(destination))
Application xl = new Application();
try
{
Workbook workbook = xl.Workbooks.Open(source);
Worksheet ws = (Worksheet)workbook.Sheets
ws.SaveAs(destination, XlFileFormat.xlCSV);
Marshal.ReleaseComObject(w
}
finally
{
xl.DisplayAlerts = false;
xl.Quit();
Marshal.ReleaseComObject(x
}
}
}
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.
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.
ASKER
I've requested that this question be deleted for the following reason:
I rephrased the question to make it clearer.
I rephrased the question to make it clearer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.