public void ExportDataFromExcelDocument()
{
PFExcelDocument excelDoc = null;
PFExcelDocument excelDoc2 = null;
DataSet ds = null;
try
{
_msg.Length = 0;
_msg.Append("ExportDataFromExcelDocument started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
ds = new DataSet();
excelDoc = new PFExcelDocument(enExcelOutputFormat.Excel2007, @"C:\Testfiles\C1Testing\ExcelRandomNames.xlsx", true);
excelDoc.SheetName = "RandomNames";
DataTable dt = excelDoc.ExportExcelDataToDataTable("RandomNamesList", true);
ds.Tables.Add(dt);
ds.WriteXml(@"c:\temp\RandomNamesOut.xml", XmlWriteMode.WriteSchema);
excelDoc = null;
excelDoc2 = new PFExcelDocument(enExcelOutputFormat.Excel2007, @"C:\Testfiles\C1Testing\ExcelRandomNamesForExport.xlsx", true);
excelDoc2.SheetName = "RandomNames";
DataTable dt2 = excelDoc2.ExportExcelDataToDataTable(1, 1, 101, 16, true);
dt2.TableName = "RandomNamesListTable";
dt2.WriteXml(@"c:\temp\RandomNamesOutTable.xml", XmlWriteMode.WriteSchema);
excelDoc2 = null;
}
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 (excelDoc2 != null)
{
excelDoc2 = null;
}
if (ds != null)
{
ds = null;
}
_msg.Length = 0;
_msg.Append("\r\n... ExportDataFromExcelDocument finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
/// <summary>
/// Writes data contained in ADO.NET DataTable object to path stored in OutputFileName property.
/// </summary>
/// <param name="dt">DataTable object containing data to be output.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
/// <remarks>Non Ext version of WriteDataToOutput will report an error if ReplaceExistingFile is false and file exists.</remarks>
public bool WriteDataToOutput(DataTable dt)
{
bool success = true;
try
{
if (File.Exists(_outputFileName))
{
if (_replaceExistingFile)
{
File.SetAttributes(_outputFileName, FileAttributes.Normal);
File.Delete(_outputFileName);
}
else
{
_msg.Length = 0;
_msg.Append("File exists and ReplaceExistingFile set to False. Write to Output has failed.");
throw new System.Exception(_msg.ToString());
}
}
enExcelOutputFormat outputFormat = enExcelOutputFormat.NotSpecified;
switch (this.ExcelVersion)
{
case enExcelVersion.Excel2007:
outputFormat = enExcelOutputFormat.Excel2007;
break;
case enExcelVersion.Excel2003:
outputFormat = enExcelOutputFormat.Excel2003;
break;
case enExcelVersion.CSV:
outputFormat = enExcelOutputFormat.CSV;
break;
default:
outputFormat = enExcelOutputFormat.CSV;
break;
}
PFExcelDocument excelDoc = new PFExcelDocument(outputFormat, this.OutputFileName, this.SheetName, this.ReplaceExistingFile);
excelDoc.WriteDataToDocument(dt);
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
;
}
return(success);
}
//application routines
public void OutputToExcelDocument()
{
PFExcelDocument excelDoc = null;
DataSet ds = null;
try
{
_msg.Length = 0;
_msg.Append("OutputToExcelDocument started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
ds = new DataSet();
ds.ReadXml(@"C:\Testfiles\C1Testing\RandomNames.xml");
ds.Tables[0].TableName = "RandomNames";
excelDoc = new PFExcelDocument(enExcelOutputFormat.Excel2007, @"c:\temp\ExcelRandomNames.xlsx", true);
excelDoc.SheetName = "RandomNames";
excelDoc.WriteDataToDocument(ds.Tables[0]);
excelDoc = new PFExcelDocument(enExcelOutputFormat.Excel2003, @"c:\temp\ExcelRandomNames.xls", true);
excelDoc.SheetName = "RandomNames";
excelDoc.WriteDataToDocument(ds.Tables[0]);
excelDoc = new PFExcelDocument(enExcelOutputFormat.CSV, @"c:\temp\ExcelRandomNames.csv", true);
excelDoc.SheetName = "RandomNames";
excelDoc.WriteDataToDocument(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... OutputToExcelDocument finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
