public void AppendToExcelDocument(MainForm frm) { PFExcelInterop excelDoc = null; DataSet ds = null; //string filename = @""; string sheetName = string.Empty; try { _msg.Length = 0; _msg.Append("AppendToExcelDocument started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); ds = new DataSet(); ds.ReadXml(@"C:\Testfiles\C1Testing\RandomNames100Lines.xml"); ds.Tables[0].TableName = "RandomNames"; if (frm.optXLSXFormat.Checked) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2007, @"c:\temp\ExcelInteropRandomNamesInput.xlsx", "RandomNames", false, false); } else if (frm.optXLSFormat.Checked) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2003, @"c:\temp\ExcelInteropRandomNamesInput.xls", "RandomNames", false, false); } else { excelDoc = new PFExcelInterop(enExcelOutputFormat.CSV, @"c:\temp\ExcelInteropRandomNamesInput.csv", "ExcelInteropRandomNamesInput", false, false); } excelDoc.SheetName = "RandomNames"; excelDoc.AppendDataToExistingSheet(ds.Tables[0]); } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (excelDoc != null) { excelDoc = null; } if (ds != null) { ds = null; } _msg.Length = 0; _msg.Append("\r\n... AppendToExcelDocument finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
/// <summary> /// ExportDataToDataTable /// </summary> /// <param name="startRow">First row of Excel range.</param> /// <param name="startCol">First column of Excel range.</param> /// <param name="endRow">Last row of Excel range.</param> /// <param name="endCol">Last column of Excel range.</param> /// <param name="columnNamesInFirstRow">If true, then export will assume first row contains column names.</param> /// <returns>DataTable object that contains the values in the given range of Excel cells.</returns> /// <remarks>Row and col values are zero based. e.g. 0,0 to 99,99 for a range of 100 rows and 100 columns.</remarks> public DataTable ExportExcelDataToDataTable(int startRow, int startCol, int endRow, int endCol, bool columnNamesInFirstRow) { DataTable dt = null; PFExcelInterop excelDoc = null; try { if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2007) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2007, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } else if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2003) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2003, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } else { excelDoc = new PFExcelInterop(enExcelOutputFormat.CSV, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } dt = excelDoc.ExportExcelDataToDataTable(startRow, startCol, endRow, endCol, columnNamesInFirstRow); } catch (System.Exception ex) { _msg.Length = 0; _msg.Append("Attempt to output Excel data to DataTable failed."); _msg.Append(Environment.NewLine); _msg.Append(PFTextProcessor.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (excelDoc != null) { excelDoc = null; } } return(dt); }
/// <summary> /// Writes data contained in ADO.NET DataTable object to path stored in DocumentFilePath property. /// </summary> /// <param name="dt">DataTable object containing data to be imported.</param> /// <returns>True if output operation is successful. False if write fails.</returns> /// <remarks>Ext version of WriteDataToDocument has extra logic for modifying existing documents.</remarks> public bool WriteDataToDocumentExt(DataTable dt) { bool success = true; PFExcelInterop excelDoc = null; try { if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2007) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2007, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } else if (this.ExcelOutputFormat == enExcelOutputFormat.Excel2003) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2003, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } else { excelDoc = new PFExcelInterop(enExcelOutputFormat.CSV, this.DocumentFilePath, this.SheetName, this.ReplaceExistingFile, this.ReplaceExistingSheet); } success = excelDoc.WriteDataToDocumentExt(dt); } catch (System.Exception ex) { success = false; _msg.Length = 0; _msg.Append("Attempt to import DataTable into Excel document failed."); _msg.Append(Environment.NewLine); _msg.Append(PFTextProcessor.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (excelDoc != null) { excelDoc = null; } } return(success); }
public void ExportExcelDocument(MainForm frm) { PFExcelInterop excelDoc = null; DataTable dt = null; try { _msg.Length = 0; _msg.Append("ExportExcelDocument started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); if (frm.optXLSXFormat.Checked) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2007, @"C:\Testfiles\C1Testing\ExcelRandomNamesForExport.xlsx", "RandomNames", false, false); } else if (frm.optXLSFormat.Checked) { excelDoc = new PFExcelInterop(enExcelOutputFormat.Excel2003, @"C:\Testfiles\C1Testing\ExcelRandomNamesForExport.xls", "RandomNames", false, false); } else { excelDoc = new PFExcelInterop(enExcelOutputFormat.CSV, @"C:\Testfiles\C1Testing\ExcelRandomNamesForExport.csv", "ExcelRandomNamesForExport", false, false); } _msg.Length = 0; if (frm.optRowCol.Checked) { _msg.Append("Export by row and column method."); Program._messageLog.WriteLine(_msg.ToString()); dt = excelDoc.ExportExcelDataToDataTable(1, 1, 101, 16, true); } else { //frm.optNamedRange.Checked _msg.Append("Export by named range method."); Program._messageLog.WriteLine(_msg.ToString()); dt = excelDoc.ExportExcelDataToDataTable("PersonData", true); } if (dt != null) { excelDoc.DocumentFilePath = @"c:\temp\ExportedDataTableValues.xlsx"; excelDoc.ExcelOutputFormat = enExcelOutputFormat.Excel2007; excelDoc.ReplaceExistingFile = true; excelDoc.WriteDataToDocumentExt(dt); } } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { _msg.Length = 0; _msg.Append("\r\n... ExportExcelDocument finished."); Program._messageLog.WriteLine(_msg.ToString()); } }