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