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)."); }