public void DoExport() { ExcelHelper.CreateExcelSheet(1, 1, _dataExportContainer); _dataExportContainer = null; this.Dispatcher.Invoke((Action)(() => { IsExportEnabled = true; })); }
public void AppendDataForExport(IList <object> data, IDictionary <string, List <string> > propertiesUsedByImport = null) { if (_dataExportContainer == null) { _dataExportContainer = new DataExportContainer(this, propertiesUsedByImport); IsExportEnabled = false; } if (_dataExportContainer != null) { _dataExportContainer.AppendData(data); } }
public static void CreateExcelSheet(int x, int y, DataExportContainer dataContainer) { Excel.Application excelApp = null; Workbook workbook = null; Sheets sheets = null; Worksheet firstSheet = null; Worksheet allDataSheet = null; CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); bool makeImportTemplate = true; try { if (dataContainer.ImportTemplateNames.Count == 0) { makeImportTemplate = false; } #region Setup Excel, Workbook and sheet excelApp = new Excel.Application(); excelApp.Visible = false; excelApp.DisplayAlerts = false; workbook = excelApp.Workbooks.Add(Type.Missing) as Excel.Workbook; sheets = workbook.Sheets; int totalNumOfSheets = 2; if (makeImportTemplate) { totalNumOfSheets += dataContainer.ImportTemplateNames.Count; } int sheetsToAdd = totalNumOfSheets - sheets.Count; if (sheetsToAdd < 0) { for (int i = 0; i < Math.Abs(sheetsToAdd); i++) { ((Worksheet)sheets[1]).Delete(); } sheetsToAdd = totalNumOfSheets - sheets.Count; } if (sheetsToAdd > 0) { sheets.Add(Type.Missing, Type.Missing, sheetsToAdd); } firstSheet = (Worksheet)sheets[1]; allDataSheet = (Worksheet)sheets[2]; #endregion string title = dataContainer.GetTitle(false); if (!string.IsNullOrEmpty(title)) { firstSheet.Name = AdjustSheetName(title); } string titleAllDataSheet = dataContainer.GetTitle(true); if (!string.IsNullOrEmpty(titleAllDataSheet)) { allDataSheet.Name = AdjustSheetName(titleAllDataSheet); } if (makeImportTemplate) { int i = 2; foreach (string templateName in dataContainer.ImportTemplateNames) { i++; string caption = "Import Template (" + templateName.Split(' ')[0] + ")"; if (dataContainer.ImportTemplateNames.Count > 1) { if (caption.Length > 30) { caption = "Import Template (" + i.ToString() + ")"; } } else { caption = "Import Template"; } ((Worksheet)sheets[i]).Name = caption; } } FillSheet(firstSheet, x, y, dataContainer, false, false); FillSheet(allDataSheet, x, y, dataContainer, true, false); if (makeImportTemplate) { int i = 2; foreach (string templateName in dataContainer.ImportTemplateNames) { i++; FillSheet((Worksheet)sheets[i], x, y, dataContainer, true, true, templateName); } } excelApp.Visible = true; } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } finally { if (firstSheet != null) { Marshal.ReleaseComObject(firstSheet); } if (allDataSheet != null) { Marshal.ReleaseComObject(allDataSheet); } if (makeImportTemplate) { int i = 2; foreach (string templateName in dataContainer.ImportTemplateNames) { i++; Marshal.ReleaseComObject((Worksheet)sheets[i]); } } if (sheets != null) { Marshal.ReleaseComObject(sheets); } if (workbook != null) { Marshal.ReleaseComObject(workbook); } if (excelApp != null) { Marshal.ReleaseComObject(excelApp); } firstSheet = null; sheets = null; workbook = null; excelApp = null; Thread.CurrentThread.CurrentCulture = oldCulture; GC.Collect(); } }
private static void FillSheet(Worksheet theSheet, int x, int y, DataExportContainer dataContainer, bool allData, bool importTemplate, string importTemplateName = "") { int headerRow = y; int captionRow = y + 1; int dataColumnCaptionRow = y + 2; int firstDataRow = y + 3; if (importTemplate) { dataColumnCaptionRow = 0; captionRow = y; firstDataRow = y + 1; } IEnumerable <IEnumerable <object> > dataRows = dataContainer.GetDataRows(allData, importTemplate, importTemplateName); if (dataRows.Count() > 0) { List <string> columnFormats = new List <string>(dataContainer.GetColumnFormatStrings(allData, importTemplate, importTemplateName)); object[,] data = new object[dataRows.Count(), columnFormats.Count()]; int rowRange = dataRows.Count(); if (importTemplate) { if (rowRange < 5000) { rowRange = 5000; } } // Set visual styles for dataarea for (int i = 0; i < columnFormats.Count; i++) { SetCellColumnVisuals(theSheet, x + i, firstDataRow, rowRange, columnFormats[i], Brushes.White.Color, Brushes.Black.Color, 10, false); } // Create array of data int rowIdx = 0; foreach (IEnumerable <object> row in dataRows) { int colIdx = 0; foreach (object columnValue in row) { object value = columnValue; if (value is string) { value = string.Format("'{0}", columnValue); } data[rowIdx, colIdx] = value; colIdx++; } rowIdx++; } // Set the values in the sheet SetRangeValues(theSheet, x, firstDataRow, (x + columnFormats.Count() - 1), (firstDataRow + dataRows.Count() - 1), data); SetTableName(theSheet, x, captionRow, (x + columnFormats.Count() - 1), (firstDataRow + dataRows.Count() - 1), "DataTable"); } // Set Captions on columns and autofit columns IEnumerable <string> headerCaptions = null; IEnumerable <string> dataColumnHeaderCaptions = null; headerCaptions = dataContainer.GetHeaderCaptions(allData, importTemplate, importTemplate, importTemplateName); if (!importTemplate) { dataColumnHeaderCaptions = dataContainer.GetDataColumnHeaderCaptions(allData); } int columnIndex = x; foreach (string caption in headerCaptions) { SetCellValueAndVisuals(theSheet, columnIndex, captionRow, caption, "", Brushes.LightGray.Color, Brushes.Black.Color, 10, true); columnIndex++; } if (!importTemplate) { columnIndex = x; foreach (string dataCaption in dataColumnHeaderCaptions) { SetCellValueAndVisuals(theSheet, columnIndex, dataColumnCaptionRow, dataCaption, "", Brushes.LightGray.Color, Brushes.Black.Color, 10, true); AutoFitColumn(theSheet, columnIndex); columnIndex++; } // Set heading SetCellValueAndVisuals(theSheet, x, y, theSheet.Name, "", new Color() { R = 0, G = 112, B = 192 }, Brushes.White.Color, 14, true); MergeCells(theSheet, x, y, x + headerCaptions.Count() - 1, y, Brushes.Black.Color); } theSheet.EnableAutoFilter = true; }