public static void WriteTestData(string filename)
{
FileInfo fi = new FileInfo(filename);
if (fi.Exists)
{
fi.Delete();
fi = new FileInfo(filename);
}
ExcelDataReaderHandler source = new ExcelDataReaderHandler();
source.LoadExelFile(@"..\..\TestFiles\CLE_140509_140506_300L_GC1_LC1.xlsx");
EPPlusExcelHandler dest = new EPPlusExcelHandler();
dest.LoadExelFile(filename);
//ExcelWorksheet ws = package.Workbook.Worksheets.Add("Test");
for (int i = 0; i < source.NumberOfSheets; i++)
{
IExcelSheet sourcesheet = source.GetSheet(i);
IExcelSheet destsheet = dest.CreateSheet(sourcesheet.Name);
for (int r = 1; r <= sourcesheet.RowCount; r++)
{
for (int c = 1; c <= sourcesheet.ColumnCount; c++)
{
string value = sourcesheet.GetCellValue(r, c);
destsheet.SetCellValue(r, c, value);
}
}
}
dest.Save(filename);
}
/// <summary>
/// Creates the concrete IExcelHandler implementation according to the file extension
/// </summary>
/// <param name="filename">Excel file physical location (.xls or .xlsx) according to implementation.</param>
/// <returns>An instance of the IExcelHandler according to the file extension specified.</returns>
/// <exception cref="ArgumentNullException">The file name can not be null.</exception>
/// <exception cref="InvalidOperationException">if the file is encrypted, this operation is not supported.</exception>
/// <exception cref="ArgumentException">The only file extensions supported are .xls and .xlsx</exception>
public IExcelHandler Create(string filename)
{
if (filename == null)
{
throw new ArgumentNullException("filename", "The file name can not be null");
}
try
{
if (filename.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase))
{
var exhnd = new NpoiExcelHandler();
exhnd.LoadExelFile(filename);
return exhnd;
}
if (filename.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
var exhnd = new EPPlusExcelHandler();
exhnd.LoadExelFile(filename);
return exhnd;
}
if (filename.EndsWith(".xlsm", StringComparison.InvariantCultureIgnoreCase))
{
var exhnd = new EPPlusExcelHandler();
exhnd.LoadExelFile(filename);
return exhnd;
}
}
catch (ObjectDisposedException ex)
{
System.Diagnostics.Debug.WriteLine("Exception Thrown: The file is encrypted and it's impossible to open it");
throw new InvalidOperationException("The file is encrypted and it's impossible to open it", ex);
}
catch (Exception ex)
{
if (ex.InnerException is FileFormatException)
{
System.Diagnostics.Debug.WriteLine("Exception Thrown: The file is encrypted and it's impossible to open it.");
throw new InvalidOperationException("The file is encrypted and it's impossible to open it", ex);
}
throw;
}
System.Diagnostics.Debug.WriteLine("Exception Thrown: Excel file extension not supported (only .xls or .xlsx).");
throw new ArgumentException("Excel file extension not supported (only .xls or .xlsx).");
}
