public void CriteriaToSqlTest()
{
string xpoCriteriaText = "[Activity] Is Not Null Or [ActionOwner.Name] Like 'UNDEFINED'";
var criteria = CriteriaEditorHelper.GetCriteriaOperator(
xpoCriteriaText, typeof(BankStmt), ObjectSpace);
var sqlCriteriaText = CriteriaToWhereClauseHelper.GetOracleWhere(XpoCriteriaFixer.Fix(criteria));
Console.WriteLine(sqlCriteriaText);
}
private void BuildCriteriaExpression_Execute(object sender, SimpleActionExecuteEventArgs e)
{
var obj = (IMappingCriteriaGenerator)View.CurrentObject;
var xpoCriteriaText = obj.Criteria;
var criteria = CriteriaEditorHelper.GetCriteriaOperator(
xpoCriteriaText, obj.CriteriaObjectType, ObjectSpace);
obj.CriteriaExpression = CriteriaToWhereClauseHelper.GetOracleWhere(XpoCriteriaFixer.Fix(criteria));
}
private void bwExportExcel_DoWork(object sender, DoWorkEventArgs e)
{
e.Result = true;
var fname = e.Argument as string;
var fnameWithoutExt = Path.GetFileNameWithoutExtension(fname);
var fileExt = Path.GetExtension(fname);
var outputDir = Path.GetDirectoryName(fname);
try
{
if (File.Exists(fname))
{
File.Delete(fname);
}
using (FileStream zipToOpen = new FileStream(fname, FileMode.OpenOrCreate))
{
using (ZipArchive archive = new ZipArchive(zipToOpen, ZipArchiveMode.Create))
{
// Dirs
var xlDir = $@"xl";
var worksheetDir = $@"{xlDir}\worksheets";
var relsDir = $@"{xlDir}\_rels";
var generalRelsDir = $@"_rels";
// FNames
var contentTypesFileName = $@"[Content_Types].xml";
var workbookFileName = $@"{xlDir}\workbook.xml";
var sharedStringFileName = $@"{xlDir}\sharedStrings.xml";
var stylesFileName = $@"{xlDir}\styles.xml";
var relsFileName = $@"{relsDir}\workbook.xml.rels";
var generalRelsFileName = $@"{generalRelsDir}\.rels";
// Create workbook, styles and other files
ZipArchiveEntry readmeEntry = archive.CreateEntry(stylesFileName, CompressionLevel.Optimal);
using (var stylesFile = new StreamWriter(readmeEntry.Open()))
{
stylesFile.Write(excelTemplateStyles, excelTemplateStylesDateFormat);
}
readmeEntry = archive.CreateEntry(generalRelsFileName, CompressionLevel.Optimal);
using (var generalRelsFile = new StreamWriter(readmeEntry.Open()))
{
generalRelsFile.Write(excelTemplateGeneralRels);
}
readmeEntry = archive.CreateEntry(sharedStringFileName, CompressionLevel.Optimal);
using (var sharedStringFile = new StreamWriter(readmeEntry.Open()))
{
sharedStringFile.Write(excelTemplateSharedString);
}
var wbSheetsTemplBuilder = new StringBuilder();
var wbSheetsTemplSheetDefNameBuilder = new StringBuilder();
var ctWorkSheetOverrideBuilder = new StringBuilder();
var relTemplBuilder = new StringBuilder();
// Create worksheets
var worksheetsCount = _gridView.RowCount / _maxRowsInFile + 1;
using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var SQLWhereFromGrid = CriteriaToWhereClauseHelper.GetOracleWhere(_gridView.ActiveFilterCriteria);
var SQLColumns = string.Join(",", _gridView.VisibleColumns.Select(x => x.Name.Substring(3)));
if (SQLColumns != "")
{
var txtQuery = $"SELECT {SQLColumns} FROM {_tableName} {(SQLWhereFromGrid != "" ? ($"WHERE {SQLWhereFromGrid}") : "") }";
using (SQLiteCommand cmd = new SQLiteCommand(txtQuery, conn))
using (SQLiteDataReader rd = cmd.ExecuteReader())
for (var worksheetIndex = 0; worksheetIndex < worksheetsCount; worksheetIndex++)
{
var worksheetFileName = $@"{worksheetDir}\sheet{worksheetIndex + 1}.xml";
var wsName = worksheetsCount > 1
? $"Billing #{worksheetIndex + 1}"
: "Billing";
readmeEntry = archive.CreateEntry(worksheetFileName, CompressionLevel.Optimal);
using (var worksheetFile = new StreamWriter(readmeEntry.Open()))
{
var startRowInGrid = worksheetIndex * _maxRowsInFile;
var rowsInCurrentFile =
_gridView.RowCount - startRowInGrid > _maxRowsInFile
? _maxRowsInFile
: _gridView.RowCount - startRowInGrid;
var endColumnsRange = NumberToLetters(_gridView.VisibleColumns.Count);
// Add in builder cur sheet
wbSheetsTemplBuilder.AppendFormat(excelTemplateWorkbookSheet, wsName, worksheetIndex + 1, worksheetIndex + 3);
wbSheetsTemplSheetDefNameBuilder.AppendFormat(excelTemplateWorkbookSheetDefName, worksheetIndex, wsName, endColumnsRange, rowsInCurrentFile + 1);
ctWorkSheetOverrideBuilder.AppendFormat(excelTemplateContentTypesWorkSheetOverride, worksheetIndex + 1);
relTemplBuilder.AppendFormat(excelTemplateRelsForWorksheet, worksheetIndex + 3, worksheetIndex + 1);
// Create header, coldata and open body in worksheet
worksheetFile.Write(excelTemplateWorksheetHeader, endColumnsRange, rowsInCurrentFile + 1, 0);
worksheetFile.Write(excelTemplateWorksheetColumn, _gridView.VisibleColumns.Count);
worksheetFile.Write(excelTemplateWorksheetBeginBody);
// Write header
var j = 0;
var rowBuilder = new StringBuilder();
foreach (string colName in _gridView.VisibleColumns.Select(x => x.Name))
{
j++;
rowBuilder.Append(string.Format(excelTemplateWorksheetCol, NumberToLetters(j), 1, 2, "t =\"inlineStr\"", $"<is><t>{System.Net.WebUtility.HtmlEncode(colName.Substring(3))}</t></is>"));
}
worksheetFile.Write(excelTemplateWorksheetRow, 1, rowBuilder.ToString());
// Export data rows
var curProgress = 0;
for (var i = 0; i < rowsInCurrentFile; i++)
{
var res = rd.Read();
if (!res)
{
break;
}
rowBuilder = new StringBuilder();
for (j = 0; j < rd.FieldCount; j++)
{
var value = rd.GetValue(j);
var valueType = rd.GetFieldType(j);
// <c r ="{0}{1}" s="{2}" {3}>{4}</c>
var isDate = (valueType == typeof(DateTime));
var isInt = (valueType == typeof(int));
var useInlineStr = !isDate && !isInt;
rowBuilder.Append(string.Format(excelTemplateWorksheetCol,
NumberToLetters(j + 1), i + 2,
isDate
? 3
: isInt
? 4
: 1,
useInlineStr
? "t =\"inlineStr\""
: "",
useInlineStr
? $"<is><t>{(value == null || value.GetType() == typeof(System.DBNull) ? "" : System.Net.WebUtility.HtmlEncode(value as string))}</t></is>"
: $"<v>{ (value == null || value.GetType() == typeof(System.DBNull) ? "" : (isDate ? ((DateTime)value).ToOADate() : value))}</v>"
));
}
worksheetFile.Write(excelTemplateWorksheetRow, i + 2, rowBuilder.ToString());
if (bwExportExcel.CancellationPending)
{
e.Result = false;
return;
}
_pauseManager.WaitOne();
if (100 * (startRowInGrid + i) / _gridView.RowCount > curProgress)
{
curProgress = 100 * (startRowInGrid + i) / _gridView.RowCount;
bwExportExcel.ReportProgress(curProgress);
}
}
worksheetFile.Write(excelTemplateWorksheetEndBody, endColumnsRange, rowsInCurrentFile + 1);
}
}
}
}
readmeEntry = archive.CreateEntry(contentTypesFileName, CompressionLevel.Optimal);
using (var contentTypesFile = new StreamWriter(readmeEntry.Open()))
{
contentTypesFile.Write(excelTemplateContentTypes, ctWorkSheetOverrideBuilder.ToString());
}
readmeEntry = archive.CreateEntry(workbookFileName, CompressionLevel.Optimal);
using (var workbookFile = new StreamWriter(readmeEntry.Open()))
{
workbookFile.Write(excelTemplateWorkbook, wbSheetsTemplBuilder.ToString(), wbSheetsTemplSheetDefNameBuilder.ToString());
}
readmeEntry = archive.CreateEntry(relsFileName, CompressionLevel.Optimal);
using (var relsFile = new StreamWriter(readmeEntry.Open()))
{
relsFile.Write(excelTemplateRels, relTemplBuilder);
}
bwExportExcel.ReportProgress(100);
}
}
}
catch (Exception ex)
{
e.Result = ex;
}
}