public static bool ExportToExcel(RevisionData selected,
RevOrderMgr om)
{
List <List <string> > data =
AggregateData(selected, om, REV_SORT_ITEM_DESC);
int row = TITLE_ROW;
string outFile = SetUpOutputFile(Setg.TemplatePathAndFileName,
Setg.ExcelPathAndFileName);
X.Application excel = new X.Application();
if (excel == null)
{
return(false);
}
X.Workbook wb = excel.Workbooks.Open(outFile);
X.Worksheet wsData =
wb.Sheets[Setg.ExcelDataWorksheetName] as X.Worksheet;
if (wsData == null)
{
return(false);
}
excel.Visible = false;
ExportColumnTitles(wsData, row, om);
row++;
// startRow row count
X.Range range = GetRange(wsData, row, data.Count,
// startCol colCount
1, om.ColumnOrder.Count);
FormatDataCells(range);
ExportToExcel(data, row, wsData, om);
AdjustColumnWidthInRange(range.Columns, 1.5);
X.Worksheet wsPivot =
wb.Sheets[Setg.ExcelPivotWorksheetName] as X.Worksheet;
X.PivotTable pivotTable =
(X.PivotTable)wsPivot.PivotTables(Setg.ExcelPivotTableName);
pivotTable.RefreshTable();
excel.Visible = true;
return(true);
}
private static List <List <string> > AggregateData(RevisionData selected,
RevOrderMgr om, DataItems.DataEnum field)
{
// rows x columns
List <List <string> > rawTableData = FormatTableData(selected, om);
List <List <string> > finalTableData = new List <List <string> >();
int newRow = 0;
int maxRawRows = rawTableData.Count;
finalTableData.Add(rawTableData[0].Clone());
for (int i = 1; i < rawTableData.Count; i++)
{
bool result = true;
foreach (DataItems.DataEnum item in om.SortOrder.Columns)
{
if (item.Equals(field))
{
continue;
}
if (!rawTableData[i - 1][item.DataIdx].
Equals(rawTableData[i][item.DataIdx]))
{
finalTableData.Add(rawTableData[i].Clone());
newRow++;
result = false;
break;
}
}
if (result)
{
if (!finalTableData[newRow][field.DataIdx]
.Equals(rawTableData[i][field.DataIdx]))
{
finalTableData[newRow][field.DataIdx] +=
nl + rawTableData[i][field.DataIdx];
}
}
}
Console.WriteLine("raw table data");
ListTableData(rawTableData);
Console.Write(nl);
Console.WriteLine("final table data");
ListTableData(finalTableData);
return(finalTableData);
}
private static void ExportToExcel(RevisionData selected,
int startRow, X.Worksheet ws, RevOrderMgr om)
{
int row = startRow;
foreach (RevisionDataFields rdf in selected.GetEnumerable())
{
int col = 1;
foreach (DataItems.DataEnum d in om.ColumnOrder.Iterate())
{
ExportAnItem(rdf[d.DataIdx], d, row, col++, ws);
}
row++;
}
}
private static void ExportToExcel(List <List <string> > data,
int startRow, X.Worksheet ws, RevOrderMgr om)
{
int row = startRow;
foreach (List <string> field in data)
{
int col = 1;
foreach (DataItems.DataEnum d in om.ColumnOrder.Iterate())
{
ws.Cells[row, col++] = field[d.DataIdx];
}
row++;
}
}
private static void ExportColumnTitles(X.Worksheet ws, int row, RevOrderMgr om)
{
int col = 1;
foreach (DataItems.DataEnum item in om.ColumnOrder.Iterate())
{
ExportARowTitle(item, row, col++, ws);
}
X.Range range = ws.Range[ws.Cells[row, 1],
ws.Cells[row, om.ColumnOrder.Count]];
range.Font.Bold = true;
range.Cells.HorizontalAlignment = xlHAlignCenter;
range.Cells.WrapText = true;
}
public static bool SortSelected(RevOrderMgr om)
{
if (om.SortOrder.Count == 0)
{
return(false);
}
foreach (RevisionDataFields rdf in _selected)
{
string key = GetKey(rdf, om);
rdf.SortKey = key;
}
_selected.Sort();
return(true);
}
private static List <List <string> > FormatTableData(RevisionData selected,
RevOrderMgr om)
{
// rows x columns
List <List <string> > tableData = new List <List <string> >();
foreach (RevisionDataFields rdf in selected.GetEnumerable())
{
List <string> rowData = new List <string>(new string[selected.Count]);
foreach (DataItems.DataEnum d in om.ColumnOrder.itemize())
{
rowData[d.DataIdx] = (string.Format(d.Display.FormatString,
rdf[d.DataIdx] ?? ""));
}
tableData.Add(rowData);
}
return(tableData);
}
// public static bool SortSelected(params ISortable[] d)
// {
// if (_selected.Count == 0) return false;
//
// foreach (RevisionDataFields rdf in _selected)
// {
// string key = GetKey(rdf, d);
//
// rdf.SortKey = key;
// }
//
// _selected.Sort();
//
// return true;
// }
public static string GetKey(RevisionDataFields items,
RevOrderMgr om)
{
string key = null;
int i = 0;
foreach (DataItems.ISortable so in om.SortOrder.Iterate())
{
DataItems.DataEnum d = ((DataItems.DataEnum)so);
key += RevisionFormat.FormatForKey(items[d.DataIdx], d.Display);
if (++i != om.SortOrder.Count)
{
key += "|";
}
}
return(key);
}
public static bool ExportToExcel(RevOrderMgr om)
{
return(RevisionExportExcel.ExportToExcel(_selected, om));
}