public Worksheet(XmlNode worksheetNode, ExcelExportProvider provider, string refWorksheetId, string refWorkSheetName, string newWorkSheetId, string newWorkSheetName)
{
Name = XmlUtility.getNodeAttributeStringValue(worksheetNode, "name");
Title = XmlUtility.getNodeAttributeStringValue(worksheetNode, "title");
Row = XmlUtility.getNodeAttributeIntValue(worksheetNode, "row");
Column = XmlUtility.getNodeAttributeIntValue(worksheetNode, "col");
ID = XmlUtility.getNodeAttributeStringValue(worksheetNode, "id");
RefSheetId = refWorksheetId;
RefSheetName = refWorkSheetName;
if (!string.IsNullOrEmpty(newWorkSheetId))
{
ID = newWorkSheetId;
}
if (!string.IsNullOrEmpty(newWorkSheetName))
{
Name = newWorkSheetName;
}
GetMergedConfigs(worksheetNode);
GetGroupConfigs(worksheetNode);
InitAutoSizeColumn(worksheetNode);
ExcelExportProvider = provider;
Formulas = GetFormulas(worksheetNode);
Areas = GetAreas(worksheetNode);
HSSFSheetXmlNode = worksheetNode;
}
protected string GetRowFormula(ICell hssfCell, string[] operations)
{
List <string> result = new List <string>();
Dictionary <string, Area> dictArea = new Dictionary <string, Area>();
Dictionary <string, Worksheet> dictWorksheet = new Dictionary <string, Worksheet>();
foreach (string operation in operations)
{
string[] areaDotCell = operation.Split('.');
if (areaDotCell.Length == 1)
{
string columnNum = (Area as RepeatArea).DataColumns.First(p => p.ID == operation).ExcelColumnNum;
int rowIndex = hssfCell.RowIndex + 1;
result.Add(columnNum + rowIndex.ToString());
}
else
{
ExcelExportProvider provider = Area.Worksheet.ExcelExportProvider;
if (!dictWorksheet.ContainsKey(areaDotCell[0]))
{
dictWorksheet.Add(areaDotCell[0], provider.Worksheets.First(p => p.ID == areaDotCell[0]));
}
Worksheet worksheet = dictWorksheet[areaDotCell[0]];
if (!dictArea.ContainsKey(areaDotCell[1]))
{
dictArea.Add(areaDotCell[1], worksheet.Areas.First(p => p.ID == areaDotCell[1]));
}
Area area = dictArea[areaDotCell[1]];
if (area is StaticArea)
{
if (worksheet.ID == Area.Worksheet.ID)
{
result.Add((area as StaticArea).
DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName);
}
else
{
result.Add(worksheet.Name + "!" + (area as StaticArea).
DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName);
}
}
}
}
return(string.Format(Formula.FormulaText, result.ToArray()));
}
public CellStyle(XmlNode cellStyleNode, ExcelExportProvider excelExportProvider)
: base(cellStyleNode)
{
XmlNode XNode = cellStyleNode.SelectSingleNode("Font");
if (XNode != null)
{
Font = new Font(XNode, this);
}
XNode = cellStyleNode.SelectSingleNode("DataFormat");
if (XNode != null)
{
DataFormat = new DataFormat(XNode, this);
}
this.ExcelExportProvider = excelExportProvider;
}
private void Initialize()
{
XmlNode rootNode = GetConfigSection("/ExportConfigs");
foreach (XmlNode childNode in rootNode.ChildNodes)
{
switch (childNode.Name.ToLower())
{
case "excel":
if (!string.IsNullOrEmpty(XmlUtility.getNodeAttributeStringValue(childNode, "typename")))
{
ExcelExportProvider providerType = new ExcelExportProvider(childNode);
_excels.Add(providerType);
}
break;
}
}
}
protected string GetCellFormula(string[] operations)
{
List <string> result = new List <string>();
Dictionary <string, StaticArea> dictArea = new Dictionary <string, StaticArea>();
Dictionary <string, Worksheet> dictWorksheet = new Dictionary <string, Worksheet>();
foreach (string operation in operations)
{
string[] areaDotCell = operation.Split('.');
ExcelExportProvider provider = Area.Worksheet.ExcelExportProvider;
if (!dictWorksheet.ContainsKey(areaDotCell[0]))
{
dictWorksheet.Add(areaDotCell[0], provider.Worksheets.First(p => p.ID == areaDotCell[0]));
}
Worksheet worksheet = dictWorksheet[areaDotCell[0]];
if (!dictArea.ContainsKey(areaDotCell[1]))
{
dictArea.Add(areaDotCell[1], worksheet.Areas.First(p => p.ID == areaDotCell[1]) as StaticArea);
}
if (worksheet.ID == Area.Worksheet.ID)
{
result.Add(dictArea[areaDotCell[1]].
DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName);
}
else
{
result.Add(worksheet.Name + "!" + dictArea[areaDotCell[1]].
DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName);
}
}
return(string.Format(Formula.FormulaText, result.ToArray()));
}
protected string GetColumnFormula(string[] operations, DataRow dataRow, bool isColumnAndRow)
{
List <string> result = new List <string>();
Dictionary <string, Area> dictArea = new Dictionary <string, Area>();
Dictionary <string, Worksheet> dictWorksheet = new Dictionary <string, Worksheet>();
string[] rowsFields = (dataRow[Formula.RowsField] + "").Split(',');
int[] rowsFieldInt = new int[rowsFields.Length];
int index = 0;
foreach (string item in rowsFields)
{
rowsFieldInt[index++] = int.Parse(item);
}
Array.Sort <int>(rowsFieldInt);
string resultFormulaText = "", tempFormulaText;
if (isColumnAndRow)
{
tempFormulaText = Formula.ColumnFormulaText;
}
else
{
tempFormulaText = Formula.FormulaText;
}
bool[] isRepeat = new bool[operations.Length];
string[][] operas = new string[operations.Length][];
for (int iLoop = 0; iLoop < operations.Length; iLoop++)
{
string operation = operations[iLoop];
string[] areaDotCell = operation.Split('.');
ExcelExportProvider provider = Area.Worksheet.ExcelExportProvider;
if (!dictWorksheet.ContainsKey(areaDotCell[0]))
{
dictWorksheet.Add(areaDotCell[0], provider.Worksheets.First(p => p.ID == areaDotCell[0]));
}
Worksheet worksheet = dictWorksheet[areaDotCell[0]];
if (!dictArea.ContainsKey(areaDotCell[1]))
{
dictArea.Add(areaDotCell[1], worksheet.Areas.First(p => p.ID == areaDotCell[1]));
}
Area area = dictArea[areaDotCell[1]];
if (area is StaticArea)
{
isRepeat[iLoop] = false;
operas[iLoop] = new string[1];
string cellName;
if (worksheet.ID == Area.Worksheet.ID)
{
cellName = (area as StaticArea).DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName;
}
else
{
cellName = worksheet.Name + "!" + (area as StaticArea).DataCells.First(p => p.ID == areaDotCell[2]).ExcelCellName;
}
operas[iLoop][0] = cellName;
}
else if (area is RepeatArea)
{
isRepeat[iLoop] = true;
RepeatArea repeatArea = area as RepeatArea;
if (repeatArea.ID != Area.ID || Area.Worksheet.ID != worksheet.ID)
{
result.Add("");
continue;
}
string columnNum = repeatArea.DataColumns.First(p => p.ID == areaDotCell[2]).ExcelColumnNum;
int topRowSpan = repeatArea.TopRowSpan + repeatArea.Row;
operas[iLoop] = new string[rowsFieldInt.Length];
for (int jLoop = 0; jLoop < rowsFieldInt.Length; jLoop++)
{
operas[iLoop][jLoop] = columnNum + (rowsFieldInt[jLoop] + topRowSpan).ToString();
}
}
}
if (Formula.ColumnPriority == ColumnPriorityEnums.Row)
{
for (int iLoop = 0; iLoop < rowsFieldInt.Length; iLoop++)
{
string[] param = new string[isRepeat.Length];
for (int jLoop = 0; jLoop < operas.Length; jLoop++)
{
if (!isRepeat[jLoop])
{
param[jLoop] = operas[jLoop][0];
}
else
{
param[jLoop] = operas[jLoop][iLoop];
}
}
resultFormulaText += string.Format(tempFormulaText, param) + "+";
}
resultFormulaText = resultFormulaText.Remove(resultFormulaText.Length - 1);
}
else
{
string[] param = new string[operas.Length];
for (int iLoop = 0; iLoop < operas.Length; iLoop++)
{
for (int jLoop = 0; jLoop < operas[iLoop].Length; jLoop++)
{
param[iLoop] += operas[iLoop][jLoop] + "+";
}
param[iLoop] = param[iLoop].Remove(param[iLoop].Length - 1);
}
resultFormulaText = string.Format(tempFormulaText, param);
}
return(resultFormulaText);
}
public Worksheet(XmlNode worksheetNode, ExcelExportProvider provider)
: this(worksheetNode, provider, "", "", "", "")
{
}