internal static void LoadExcelData(string templateDestinationPath, QueryProcessResponse queryProcessResponse)
{
var application = new Application
{
Visible = false,
DisplayAlerts = false,
WindowState = XlWindowState.xlMinimized
};
var workbook = application.Workbooks.Open(templateDestinationPath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
LoadWorkbookData(workbook, queryProcessResponse);
//workbook.Save();
workbook.Close(true, templateDestinationPath, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
application.Quit();
}
private static void LoadWorkbookData(Workbook workbook, QueryProcessResponse queryProcessResponse)
{
var dataSource = queryProcessResponse.Items.ToList();
ManageWorkbook(workbook, dataSource);
var deleteSheetList = new List<int>();
for (int sheetNumber = workbook.Sheets.Count; sheetNumber > 0; sheetNumber--)
{
var worksheet = workbook.Sheets[sheetNumber] as Worksheet;
if (worksheet != null)
{
var checkWhereRange = worksheet.Cells[1, 3] as Range;
var whereRange = worksheet.Cells[2, 3] as Range;
if (checkWhereRange != null)
{
var checkWhere = checkWhereRange.Value as string;
if (whereRange != null)
{
var where = whereRange.Value as string;
checkWhereRange.EntireRow.Delete(Type.Missing);
whereRange.EntireRow.Delete(Type.Missing);
var checkWhereCount = dataSource.Count(i => i.Match(checkWhere));
if (checkWhereCount.Equals(0))
deleteSheetList.Add(sheetNumber);
else
{
var sheetDataSource = dataSource.Where(i => i.Match(@where)).ToList();
if (sheetDataSource.Count.Equals(0))
deleteSheetList.Add(sheetNumber);
else
{
foreach (var domain in sheetDataSource)
dataSource.Remove(domain);
var infoCellList = GetInfoCellList(worksheet);
var dynamicColumns = CreateDynamicColumns(worksheet, infoCellList,
sheetDataSource[0].Domains.Where(
x => x.CanRead && x.Visible).ToList());
if (dynamicColumns)
infoCellList = GetInfoCellList(worksheet);
FillFirstInfoCell(worksheet, infoCellList, sheetDataSource.First());
FillItemInfoCell(worksheet, infoCellList, sheetDataSource, dynamicColumns);
}
}
}
}
}
}
if (deleteSheetList.Count.Equals(workbook.Sheets.Count))
{
workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count], Type.Missing, Type.Missing);
}
foreach (var index in deleteSheetList)
{
var deleteWorksheet = workbook.Sheets[index] as Worksheet;
if (deleteWorksheet != null) deleteWorksheet.Delete();
}
for (int sheetNumber = workbook.Sheets.Count; sheetNumber > 0; sheetNumber--)
{
var worksheet = workbook.Sheets[sheetNumber] as Worksheet;
if (worksheet != null)
{
worksheet.Name = string.Format("Sheet {0}", sheetNumber);
worksheet.Activate();
}
}
}
internal static void LoadPdfData(string templateDestinationPath, QueryProcessResponse queryProcessResponse)
{
var application = new Application
{
Visible = false,
DisplayAlerts = false,
WindowState = XlWindowState.xlMinimized
};
var workbook = application.Workbooks.Open(templateDestinationPath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
LoadWorkbookData(workbook, queryProcessResponse);
workbook.ExportAsFixedFormat(
XlFixedFormatType.xlTypePDF,
templateDestinationPath.Replace("xlsx", "pdf"),
XlFixedFormatQuality.xlQualityStandard,
true,
true,
Missing.Value,
Missing.Value,
false,
Missing.Value);
workbook.Save();
//workbook.SaveCopyAs(templateDestinationPath);
workbook.Close(true, templateDestinationPath, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
application.Quit();
}