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());
}
}
