private void BuildReportTemplateEFPFlexi(string strTargetFile)
{
string fileName = strTargetFile;
const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string[] alph = new string[676]; // 676 = 26*26
for (int i = 0; i < 26; i++)
{
alph[i] = alphabet.Substring(i, 1);
}
for (int i = 0; i < 26; i++)
{
for (int j = 0; j < 26; j++)
{
alph[26 + i + j] = alphabet.Substring(i, 1) + alphabet.Substring(j, 1);
}
}
var tableTargets = new List <TableInfo>();
using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = workbook.WorkbookPart;
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements <SharedStringItem>().ToArray();
foreach (WorksheetPart worksheetpart in workbookPart.WorksheetParts)
{
foreach (var sheetData in worksheetpart.Worksheet.Elements <SheetData>())
{
// reset accross sheets
tableTargets.Clear();
#region TextPopulate
foreach (var cell in sheetData.Descendants <Cell>())
{
if (cell.CellValue != null)
{
if (cell.CellFormula != null)
{
// force recompute
cell.CellValue.Remove();
}
else if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
var index = int.Parse(cell.CellValue.Text);
if (values[index].InnerText.StartsWith(FLEXI_PREFIX))
{
string strBlockTypeAndName = values[index].InnerText.Substring(FLEXI_PREFIX.Length);
BlockConfiguration config = GetBlockConfiguration(strBlockTypeAndName);
if (TextBlock.IsMatching(config.Type))
{
TextBlock instance = BlockHelper.GetAssociatedBlockInstance <TextBlock>(config.Name);
if (instance != null)
{
SetCellValue(cell, instance.GetContent(reportData, config.Options));
}
}
else if (TableBlock.IsMatching(config.Type))
{
TableBlock instance = BlockHelper.GetAssociatedBlockInstance <TableBlock>(config.Name);
if (instance != null)
{
tableTargets.Add(new TableInfo
{
cell = cell,
table = instance.GetContent(reportData, config.Options)
});
}
}
}
}
}
}
#endregion TextPopulate
#region TablePopulate
foreach (var tableInfo in tableTargets)
{
var _finaleCell = tableInfo.cell;
var _finaleTable = tableInfo.table;
int intColumns = _finaleTable.NbColumns;
// TODO: handle cell references after 'ZZ' (AA1, AB1...)
// TODO: current limitation: the generated cells must be in the range "A-ZZ"
string firstLetter = _finaleCell.CellReference.InnerText[0].ToString();
int firstColIdx = alph.ToList().IndexOf(firstLetter) + 1;
int lastColIdx = firstColIdx + intColumns - 1;
int curColIdx = firstColIdx;
uint firstRowIdx = uint.Parse(_finaleCell.CellReference.InnerText.Substring(1));
uint curRowIdx = firstRowIdx;
// create first row
Row curRow = new Row();
foreach (var result in _finaleTable.Data)
{
// append cell to current row
Cell c = new Cell();
SetCellValue(c, result);
// to avoid crash when too many columns generated
if (curColIdx > 676)
{
continue;
}
c.CellReference = alph[curColIdx - 1] + curRowIdx.ToString();
c.StyleIndex = 0;
// ReSharper disable once PossiblyMistakenUseOfParamsMethod
curRow.Append(c);
if (curColIdx == lastColIdx)
{
// add row to current worksheet
InsertRow(curRowIdx, worksheetpart, curRow);
// create new row for next data
curRow = new Row();
// first cell on next row
curRowIdx++;
curColIdx = firstColIdx;
}
else
{
// next cell
curColIdx++;
}
}
_finaleCell.Parent.RemoveChild(_finaleCell);
}
workbookPart.Workbook.Save();
#endregion TablePopulate
}
}
}
}
private void BuildReportTemplateEFPFlexi(string strTargetFile)
{
string fileName = strTargetFile;
const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var tableTargets = new List <TableInfo>();
using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(fileName, true))
{
var workbookPart = workbook.WorkbookPart;
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements <SharedStringItem>().ToArray();
foreach (WorksheetPart worksheetpart in workbookPart.WorksheetParts)
{
foreach (var sheetData in worksheetpart.Worksheet.Elements <SheetData>())
{
// reset accross sheets
//Cell FinaleCell = null;
//TableDefinition FinaleTable = null;
tableTargets.Clear();
#region TextPopulate
foreach (var cell in sheetData.Descendants <Cell>())
{
if (cell.CellValue != null)
{
if (cell.CellFormula != null)
{
// force recompute
cell.CellValue.Remove();
}
else if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
var index = int.Parse(cell.CellValue.Text);
if (values[index].InnerText.StartsWith(FLEXI_PREFIX))
{
string strBlockTypeAndName = values[index].InnerText.Substring(FLEXI_PREFIX.Length);
BlockConfiguration config = GetBlockConfiguration(strBlockTypeAndName);
if (TextBlock.IsMatching(config.Type))
{
TextBlock instance = BlockHelper.GetAssociatedBlockInstance <TextBlock>(config.Name);
if (instance != null)
{
SetCellValue(cell, instance.GetContent(reportData, config.Options));
}
}
else if (TableBlock.IsMatching(config.Type))
{
TableBlock instance = BlockHelper.GetAssociatedBlockInstance <TableBlock>(config.Name);
if (instance != null)
{
tableTargets.Add(new TableInfo
{
cell = cell,
table = instance.GetContent(reportData, config.Options)
});
//FinaleCell = cell;
//FinaleTable = instance.GetContent(reportData, config.Options);
}
}
}
}
}
}
#endregion TextPopulate
#region TablePopulate
foreach (var tableInfo in tableTargets)
{
var FinaleCell = tableInfo.cell;
var FinaleTable = tableInfo.table;
int intColumns = FinaleTable.NbColumns;
int intRows = FinaleTable.NbRows;
// TODO: handle cell references after 'Znn' (AA1, AB1...)
// TODO: current limitation: the generated cells must be in the range "A-Z"
char firstLetter = FinaleCell.CellReference.InnerText[0];
int firstColIdx = alphabet.IndexOf(firstLetter) + 1;
int lastColIdx = firstColIdx + intColumns - 1;
int curColIdx = firstColIdx;
uint firstRowIdx = uint.Parse(FinaleCell.CellReference.InnerText.Substring(1));
uint curRowIdx = firstRowIdx;
// create first row
Row curRow = new Row();
foreach (var result in FinaleTable.Data)
{
// append cell to current row
Cell c = new Cell();
SetCellValue(c, result.ToString());
c.CellReference = alphabet[curColIdx - 1] + curRowIdx.ToString();
c.StyleIndex = 0;
curRow.Append(c);
if (curColIdx == lastColIdx)
{
// add row to current worksheet
InsertRow(curRowIdx, worksheetpart, curRow, false);
// create new row for next data
curRow = new Row();
// first cell on next row
curRowIdx++;
curColIdx = firstColIdx;
}
else
{
// next cell
curColIdx++;
}
}
FinaleCell.Parent.RemoveChild(FinaleCell);
}
workbookPart.Workbook.Save();
#endregion TablePopulate
}
}
}
}