public void Test44410()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
// =index(C:C,2,1) -> 2
IRow rowIDX = sheet.GetRow(3);
// =sum(C:C) -> 6
IRow rowSUM = sheet.GetRow(4);
// =sum(C:D) -> 66
IRow rowSUM2D = sheet.GetRow(5);
// Test the sum
ICell cellSUM = rowSUM.GetCell(0);
FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord;
Ptg[] ops = frec.FormulaRecord.ParsedExpression;
Assert.AreEqual(2, ops.Length);
Assert.AreEqual(typeof(AreaPtg), ops[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType());
// Actually stored as C1 to C65536
// (last row is -1 === 65535)
AreaPtg ptg = (AreaPtg)ops[0];
Assert.AreEqual(2, ptg.FirstColumn);
Assert.AreEqual(2, ptg.LastColumn);
Assert.AreEqual(0, ptg.FirstRow);
Assert.AreEqual(65535, ptg.LastRow);
Assert.AreEqual("C:C", ptg.ToFormulaString());
// Will show as C:C, but won't know how many
// rows it covers as we don't have the sheet
// to hand when turning the Ptgs into a string
Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula);
// But the evaluator knows the sheet, so it
// can do it properly
Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0);
// Test the index
// Again, the formula string will be right but
// lacking row count, Evaluated will be right
ICell cellIDX = rowIDX.GetCell(0);
Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula);
Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0);
// Across two colums
ICell cellSUM2D = rowSUM2D.GetCell(0);
Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula);
Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0);
wb.Close();
}
public void TestHSSFSetArrayFormula_SingleCell()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
Assert.AreEqual(1, cells.Length);
// sheet.SetArrayFormula Creates rows and cells for the designated range
Assert.IsNotNull(sheet.GetRow(2));
ICell cell = sheet.GetRow(2).GetCell(2);
Assert.IsNotNull(cell);
Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), cell.ArrayFormulaRange.FormatAsString());
FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord);
Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString());
Assert.IsTrue(agg.IsPartOfArrayFormula);
}
public void TestArrayFormulas()
{
int rownum = 4;
int colnum = 4;
FormulaRecord fr = new FormulaRecord();
fr.Row = (rownum);
fr.Column = ((short)colnum);
FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty());
Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) };
agg.SetParsedExpression(ptgsForCell);
String formula = "SUM(A1:A3*B1:B3)";
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.Array, 0);
agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs);
Assert.IsTrue(agg.IsPartOfArrayFormula);
Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString());
Ptg[] ptg = agg.FormulaTokens;
String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg);
Assert.AreEqual(formula, fmlaSer);
agg.RemoveArrayFormula(rownum, colnum);
Assert.IsFalse(agg.IsPartOfArrayFormula);
}
private static void Process(IRow row, HSSFFormulaEvaluator eval)
{
IEnumerator it = row.GetEnumerator();
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
if (cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
FormulaRecordAggregate record = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
FormulaRecord r = record.FormulaRecord;
Ptg[] ptgs = r.ParsedExpression;
String cellRef = new CellReference(row.RowNum, cell.ColumnIndex, false, false).FormatAsString();
#if !HIDE_UNREACHABLE_CODE
if (false && cellRef.Equals("BP24"))
{
Console.Write(cellRef);
Console.WriteLine(" - has " + ptgs.Length + " ptgs:");
for (int i = 0; i < ptgs.Length; i++)
{
String c = ptgs[i].GetType().ToString();
Console.WriteLine("\t" + c.Substring(c.LastIndexOf('.') + 1));
}
Console.WriteLine("-> " + cell.CellFormula);
}
#endif
NPOI.SS.UserModel.CellValue evalResult = eval.Evaluate(cell);
Assert.IsNotNull(evalResult);
}
}
public void TestExtraStringRecord_bug46213()
{
FormulaRecord fr = new FormulaRecord();
fr.Value = (2.0);
StringRecord sr = new StringRecord();
sr.String = ("NA");
SharedValueManager svm = SharedValueManager.CreateEmpty();
FormulaRecordAggregate fra;
try
{
fra = new FormulaRecordAggregate(fr, sr, svm);
}
catch (RecordFormatException e)
{
if ("String record was supplied but formula record flag is not set".Equals(e.Message))
{
throw new AssertionException("Identified bug 46213");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc = new TestCases.HSSF.UserModel.RecordInspector.RecordCollector();
fra.VisitContainedRecords(rc);
Record[] vraRecs = rc.Records;
Assert.AreEqual(1, vraRecs.Length);
Assert.AreEqual(fr, vraRecs[0]);
}
public void SetCellFormula(String formula)
{
if (IsPartOfArrayFormulaGroup)
{
NotifyArrayFormulaChanging();
}
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
if (string.IsNullOrEmpty(formula))
{
NotifyFormulaChanging();
SetCellType(CellType.Blank, false, row, col, styleIndex);
return;
}
int sheetIndex = book.GetSheetIndex(_sheet);
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.Cell, sheetIndex);
SetCellType(CellType.Formula, false, row, col, styleIndex);
FormulaRecordAggregate agg = (FormulaRecordAggregate)_record;
FormulaRecord frec = agg.FormulaRecord;
frec.Options = ((short)2);
frec.Value = (0);
//only set to default if there is no extended format index already set
if (agg.XFIndex == (short)0)
{
agg.XFIndex = ((short)0x0f);
}
agg.SetParsedExpression(ptgs);
}
private Ptg[] getPtgs(HSSFCell cell)
{
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(typeof(FormulaRecordAggregate), cell.CellValueRecord.GetType());
FormulaRecordAggregate agg = (FormulaRecordAggregate)cell.CellValueRecord;
FormulaRecord rec = agg.FormulaRecord;
return(rec.ParsedExpression);
}
public void TestBasic()
{
FormulaRecord f = new FormulaRecord();
f.SetCachedResultTypeString();
StringRecord s = new StringRecord();
s.String = ("abc");
FormulaRecordAggregate fagg = new FormulaRecordAggregate(f, s, SharedValueManager.CreateEmpty());
Assert.AreEqual("abc", fagg.StringValue);
}
public static Ptg[] GetPtgs(Cell cell)
{
CellValueRecordInterface vr = ((HSSFCell)cell).CellValueRecord;
if (!(vr is FormulaRecordAggregate))
{
throw new ArgumentException("Not a formula cell");
}
FormulaRecordAggregate fra = (FormulaRecordAggregate)vr;
return(fra.FormulaRecord.ParsedExpression);
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// return FormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet));
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return(fr.FormulaTokens);
}
internal void SetCellArrayFormula(CellRangeAddress range)
{
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
SetCellType(CellType.Formula, false, row, col, styleIndex);
// Billet for formula in rec
Ptg[] ptgsForCell = { new ExpPtg(range.FirstRow, range.FirstColumn) };
FormulaRecordAggregate agg = (FormulaRecordAggregate)_record;
agg.SetParsedExpression(ptgsForCell);
}
private String ConvertCellValueToString()
{
switch (cellType)
{
case CellType.Blank:
return("");
case CellType.Boolean:
return(((BoolErrRecord)_record).BooleanValue ? "TRUE" : "FALSE");
case CellType.String:
int sstIndex = ((LabelSSTRecord)_record).SSTIndex;
return(book.Workbook.GetSSTString(sstIndex).String);
case CellType.Numeric:
return(NumberToTextConverter.ToText(((NumberRecord)_record).Value));
case CellType.Error:
return(HSSFErrorConstants.GetText(((BoolErrRecord)_record).ErrorValue));
case CellType.Formula:
// should really evaluate, but Cell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new InvalidDataException("Unexpected cell type (" + cellType + ")");
}
FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
FormulaRecord fr = fra.FormulaRecord;
switch (fr.CachedResultType)
{
case CellType.Boolean:
return(fr.CachedBooleanValue ? "TRUE" : "FALSE");
case CellType.String:
return(fra.StringValue);
case CellType.Numeric:
return(NumberToTextConverter.ToText(fr.Value));
case CellType.Error:
return(HSSFErrorConstants.GetText(fr.CachedErrorValue));
}
throw new InvalidDataException("Unexpected formula result type (" + cellType + ")");
}
/// <summary>
/// Set a string value for the cell. Please note that if you are using
/// full 16 bit Unicode you should call SetEncoding() first.
/// </summary>
/// <param name="value">value to Set the cell to. For formulas we'll Set the formula
/// string, for String cells we'll Set its value. For other types we will
/// Change the cell to a string cell and Set its value.
/// If value is null then we will Change the cell to a Blank cell.</param>
public void SetCellValue(IRichTextString value)
{
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
if (value == null)
{
NotifyFormulaChanging();
SetCellType(CellType.Blank, false, row, col, styleIndex);
return;
}
if (value.Length > NPOI.SS.SpreadsheetVersion.EXCEL97.MaxTextLength)
{
throw new ArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
if (cellType == CellType.Formula)
{
// Set the 'pre-Evaluated result' for the formula
// note - formulas do not preserve text formatting.
FormulaRecordAggregate fr = (FormulaRecordAggregate)_record;
fr.SetCachedStringResult(value.String);
// Update our local cache to the un-formatted version
stringValue = new HSSFRichTextString(value.String);
return;
}
if (cellType != CellType.String)
{
SetCellType(CellType.String, false, row, col, styleIndex);
}
int index = 0;
HSSFRichTextString hvalue = (HSSFRichTextString)value;
UnicodeString str = hvalue.UnicodeString;
index = book.Workbook.AddSSTString(str);
((LabelSSTRecord)_record).SSTIndex = index;
stringValue = hvalue;
stringValue.SetWorkbookReferences(book.Workbook, ((LabelSSTRecord)_record));
stringValue.UnicodeString = book.Workbook.GetSSTString(index);
}
public Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
{
ICell cell = ((HSSFEvaluationCell)evalCell).HSSFCell;
//if (false)
//{
// // re-parsing the formula text also works, but is a waste of time
// // It is useful from time to time to run all unit tests with this code
// // to make sure that all formulas POI can evaluate can also be parsed.
// try
// {
// return HSSFFormulaParser.Parse(cell.CellFormula, _uBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.GetSheet()));
// }
// catch (FormulaParseException e)
// {
// // Note - as of Bugzilla 48036 (svn r828244, r828247) POI is capable of evaluating
// // IntesectionPtg. However it is still not capable of parsing it.
// // So FormulaEvalTestData.xls now contains a few formulas that produce errors here.
// logger.Log(POILogger.ERROR, e.Message);
// }
//}
FormulaRecordAggregate fr = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
return(fr.FormulaTokens);
}
//Grid填充
protected static void DoGridFill(HSSFWorkbook book, DataSet dataset)
{
HSSFSheet sheetFillDefine = (HSSFSheet)book.GetSheet("GridFill");
if (sheetFillDefine == null)
{
return;
}
GridDefineCollection gridDefines = new GridDefineCollection();
GridDefine gridDefine = null;
object curSection = null;
for (int r = 1; r <= sheetFillDefine.LastRowNum; r++)
{
HSSFRow excelRow = (HSSFRow)sheetFillDefine.GetRow(r);
if (excelRow == null)
{
continue;
}
//获得FixFill中的一设定行
string[] values = new string[12];
for (int i = 0; i <= values.Length - 1 && i <= excelRow.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)excelRow.GetCell(i);
if (cell == null)
{
continue;
}
string value;
if (cell.CellType == CellType.NUMERIC)
{
value = cell.NumericCellValue.ToString();
}
else
{
value = cell.StringCellValue;
}
if (value != null)
{
value = value.Trim();
}
values[i] = value;
}
string sheetName = values[0]; //填充到的sheet
string startRow = values[1]; //填充到的Cell
string blockRows = values[2]; //替换Cell中的一部分
string tableName = values[3]; //用那个表中的数据填充
string sectionName = values[4]; //用那列数据填充
string minRows = values[11]; //至少几行
//应用缺省值
if (String.IsNullOrEmpty(sheetName))
{
sheetName = book.GetSheetName(0);
}
if (!String.IsNullOrEmpty(startRow))
{
gridDefine = new GridDefine();
gridDefines.Add(gridDefine);
gridDefine.SheetName = sheetName;
gridDefine.Sheet = (HSSFSheet)book.GetSheet(sheetName);
gridDefine.StartRow = Int32.Parse(startRow) - 1;
gridDefine.BlockRowCount = Int32.Parse(blockRows);
gridDefine.FillTableName = tableName;
gridDefine.MinRows = 0;
Int32.TryParse(minRows, out gridDefine.MinRows);
}
if (gridDefine == null)
{
continue;
}
if (!String.IsNullOrEmpty(sectionName))
{
if (String.Compare(sectionName, "Fill", true) == 0)
{
curSection = gridDefine.GridCellFills;
}
else if (String.Compare(sectionName, "Template", true) == 0)
{
curSection = gridDefine.GridBlockTemplates;
}
else if (String.Compare(sectionName, "Group", true) == 0)
{
curSection = gridDefine.Groups;
}
else
{
curSection = null;
}
}
else
{
if (curSection is GridCellFillCollection)
{
GridCellFillCollection fills = curSection as GridCellFillCollection;
GridCellFill fill = new GridCellFill();
fills.Add(fill);
string cellName = values[5];
string cellVarName = values[6];
string fillColumnName = values[7];
string renderFunction = values[8];
string emptyText = values[9];
//应用缺省值
if (String.IsNullOrEmpty(renderFunction))
{
renderFunction = "DefaultRender";
}
Point cellPos = YZExcelHelper.CellNameToIndex(cellName);
fill.RowOffset = cellPos.Y - gridDefine.StartRow;
fill.ColumnIndex = cellPos.X;
fill.CellVarName = cellVarName;
fill.FillColumnName = fillColumnName;
fill.RenderFunction = renderFunction;
fill.EmptyText = emptyText;
}
else if (curSection is GridBlockTemplateCollection)
{
string templateName = values[5];
string startRowIndexStr = values[6];
string condition = values[7];
int startRowIndex = 0;
if (Int32.TryParse(startRowIndexStr, out startRowIndex))
{
GridBlockTemplateCollection templates = curSection as GridBlockTemplateCollection;
GridBlockTemplate template = new GridBlockTemplate(gridDefine);
templates.Add(template);
template.Name = templateName;
template.StartRow = startRowIndex - 1;
template.Condition = condition;
}
}
else if (curSection is GroupCollection)
{
string columnName = values[5];
if (!String.IsNullOrEmpty(columnName))
{
GroupCollection groups = curSection as GroupCollection;
Group group = new Group();
groups.Add(group);
group.ColumnName = columnName;
}
}
else
{
}
}
}
gridDefines.Sort();
foreach (GridDefine grid in gridDefines)
{
HSSFSheet sheetfill = grid.Sheet;
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
for (int i = 0; i < grid.BlockRowCount; i++)
{
HSSFRow row = (HSSFRow)sheetfill.GetRow(template.StartRow + i);
template.Rows.Add(row);
}
for (int i = 0; i < sheetfill.NumMergedRegions; i++)
{
CellRangeAddress region = sheetfill.GetMergedRegion(i);
if (region.FirstRow >= template.StartRow && region.LastRow <= template.StartRow + grid.BlockRowCount - 1)
{
region = region.Copy();
region.FirstRow -= template.StartRow;
region.LastRow -= template.StartRow;
template.MergedRegions.Add(region);
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (HSSFChart chart in HSSFChart.GetSheetCharts(sheet))
{
foreach (HSSFChart.HSSFSeries series in chart.Series)
{
if (template.Contains(sheet, series))
{
SeriesTemplate seriesTemplate = new SeriesTemplate();
template.SeriesTemplates.Add(seriesTemplate);
seriesTemplate.Chart = chart;
seriesTemplate.Series = series;
}
}
}
}
}
if (grid.GridBlockTemplates.Count != 0)
{
grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1].Condition = null;
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (CellValueRecordInterface recInferface in sheet.Sheet.RowsAggregate.GetValueRecords())
{
if (recInferface is FormulaRecordAggregate)
{
FormulaRecordAggregate fraInterface = recInferface as FormulaRecordAggregate;
FormulaRecord formulaRecord = fraInterface.FormulaRecord;
if (formulaRecord.IsSharedFormula)
{
fraInterface.UnlinkSharedFormula();
}
}
}
}
foreach (GridDefine grid in gridDefines)
{
//创建空Grid
HSSFSheet sheetfill = grid.Sheet;
DataTable dataTable = dataset.Tables[grid.FillTableName];
int gridTagBlockCount = Math.Max(dataTable.Rows.Count, grid.MinRows);
int gridTagRowCount = grid.BlockRowCount * gridTagBlockCount;
//GroupData(dataTable);
//DataView view1 = new DataView(dataTable);
//view1.Sort = "City asc,Shop asc";
//dataTable = view1.ToTable();
if (dataTable != null && dataTable.Rows.Count != 0)
{
if (dataTable.Rows.Count != 0 && sheetfill.LastRowNum >= grid.TemplateEndRow + 1)
{
sheetfill.ShiftRows(grid.TemplateEndRow + 1, sheetfill.LastRowNum, gridTagRowCount, true, false);
}
for (int i = 0; i < sheetfill.NumMergedRegions; i++)
{
CellRangeAddress region = sheetfill.GetMergedRegion(i);
if (region.FirstRow <= grid.StartRow && region.LastRow >= grid.TemplateEndRow)
{
region.LastRow += Math.Max(dataTable.Rows.Count, grid.MinRows);
}
}
HSSFSheet hssfsheet = sheetfill as HSSFSheet;
foreach (CellValueRecordInterface recInferface in hssfsheet.Sheet.RowsAggregate.GetValueRecords())
{
if (recInferface is FormulaRecordAggregate)
{
FormulaRecord formulaRecord = ((FormulaRecordAggregate)recInferface).FormulaRecord;
Ptg[] ptgs = formulaRecord.ParsedExpression;
foreach (Ptg ptg in ptgs)
{
List <int> rowIndexs = new List <int>();
List <int> newRowIndexs = new List <int>();
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
rowIndexs.Add(refPtg.Row);
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
rowIndexs.Add(aptg.FirstRow);
rowIndexs.Add(aptg.LastRow);
}
foreach (int rowIndex in rowIndexs)
{
int newRowIndex = rowIndex;
if (formulaRecord.Row < grid.StartRow || formulaRecord.Row > grid.TemplateEndRow)
{
if (rowIndex == grid.StartRow)
{
newRowIndex = grid.TemplateEndRow + 1;
}
if (rowIndex == grid.TemplateEndRow)
{
newRowIndex = grid.TemplateEndRow + gridTagRowCount;
}
}
newRowIndexs.Add(newRowIndex);
}
for (int i = 0; i < rowIndexs.Count; i++)
{
int rowIndex = rowIndexs[i];
int newRowIndex = newRowIndexs[i];
if (newRowIndex != rowIndex)
{
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
refPtg.Row = newRowIndex;
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
if (i == 0)
{
aptg.FirstRow = newRowIndex;
}
else
{
aptg.LastRow = newRowIndex;
}
}
formulaRecord.ParsedExpression = ptgs;
}
}
}
}
}
for (int i = 0; i < book.NumberOfSheets; i++)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
continue;
}
foreach (RecordBase recbase in sheet.Sheet.Records)
{
if (recbase is LinkedDataRecord)
{
LinkedDataRecord link = recbase as LinkedDataRecord;
Ptg[] ptgs = ptgs = link.FormulaOfLink;
foreach (Ptg ptg in ptgs)
{
HSSFSheet sheetptg = PtgCollection.GetPtgSheet(sheet, ptg);
if (sheetptg == sheetfill)
{
if (ptg is RefPtgBase)
{
RefPtgBase refPtg = ptg as RefPtgBase;
if (refPtg.Row > grid.TemplateEndRow)
{
refPtg.Row += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
}
if (ptg is AreaPtgBase)
{
AreaPtgBase areaPtg = ptg as AreaPtgBase;
if (areaPtg.FirstRow <= grid.StartRow && areaPtg.LastRow >= grid.TemplateEndRow)
{
areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
else if (areaPtg.FirstRow > grid.TemplateEndRow)
{
areaPtg.FirstRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1);
link.FormulaOfLink = ptgs;
}
}
}
}
}
}
}
dataTable.Columns.Add("_gridTemplateIndex", typeof(object));
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
if (String.IsNullOrEmpty(template.Condition))
{
foreach (DataRow dataRow in dataTable.Rows)
{
if (Convert.IsDBNull(dataRow["_gridTemplateIndex"]))
{
dataRow["_gridTemplateIndex"] = template;
}
}
break;
}
DataView view = new DataView(dataTable);
view.RowFilter = template.Condition.Replace("$totalRow", dataTable.Rows.Count.ToString());
DataTable rvTable = view.ToTable();
foreach (DataRow dataRow in rvTable.Rows)
{
int rowIndex = Convert.ToInt32(dataRow["RowNum"]);
DataRow dataRowSrc = dataTable.Rows[rowIndex - 1];
dataRowSrc["_gridTemplateIndex"] = template;
}
}
for (int i = 0; i < gridTagBlockCount; i++)
{
GridBlockTemplate template;
if (i >= dataTable.Rows.Count)
{
template = grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1];
}
else
{
template = dataTable.Rows[i]["_gridTemplateIndex"] as GridBlockTemplate;
}
int startRowIndex = grid.TemplateEndRow + 1 + i * grid.BlockRowCount;
for (int j = 0; j < grid.BlockRowCount; j++)
{
int newRowIndex = startRowIndex + j;
HSSFRow newRow = (HSSFRow)sheetfill.GetRow(newRowIndex);
if (newRow == null)
{
newRow = (HSSFRow)sheetfill.CreateRow(newRowIndex);
}
if (template != null)
{
HSSFRow srcRow = template.Rows[j];
CopyRow(grid.BlockRowCount, j, srcRow, newRow);
}
}
foreach (CellRangeAddress region in template.MergedRegions)
{
sheetfill.AddMergedRegion(new CellRangeAddress(startRowIndex + region.FirstRow, startRowIndex + region.FirstRow, region.FirstColumn, region.LastColumn));
}
foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates)
{
seriesTemplate.CloneSeries(sheetfill, i * grid.BlockRowCount - (template.StartRow - grid.StartRow));
}
}
foreach (GridBlockTemplate template in grid.GridBlockTemplates)
{
foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates)
{
seriesTemplate.Chart.RemoveSeries(seriesTemplate.Series);
}
}
}
//删除模板
int addedExcelRowCount = 0;
if (dataTable != null)
{
addedExcelRowCount = dataTable.Rows.Count * grid.BlockRowCount;
}
for (int i = grid.StartRow; i <= grid.TemplateEndRow; i++)
{
sheetfill.CreateRow(i);
}
int firstRow = grid.StartRow;
int lastRow = grid.TemplateEndRow;
ShiftRows(sheetfill, grid.TemplateEndRow + 1, -(grid.TemplateEndRow - grid.StartRow + 1));
//填充数据
if (dataTable != null)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
foreach (GridCellFill fill in grid.GridCellFills)
{
Point cellPos = new Point();
cellPos.X = fill.ColumnIndex;
cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount;
//获得要填充的cell的行列序号
HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y);
if (rowfill == null)
{
continue;
}
//获得要填充的cell
HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X);
if (cellfill == null)
{
cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X);
}
//获得填充值
object valuefill = null;
if (dataTable.Columns.Contains(fill.FillColumnName))
{
valuefill = row[fill.FillColumnName];
}
//执行填充
DoRender(fill.RenderFunction, cellfill, fill.CellVarName, dataset, valuefill, fill.EmptyText);
}
}
for (int i = dataTable.Rows.Count; i < grid.MinRows; i++)
{
foreach (GridCellFill fill in grid.GridCellFills)
{
Point cellPos = new Point();
cellPos.X = fill.ColumnIndex;
cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount;
//获得要填充的cell的行列序号
HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y);
if (rowfill == null)
{
continue;
}
//获得要填充的cell
HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X);
if (cellfill == null)
{
cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X);
}
//获得填充值
cellfill.SetCellValue("");
}
}
MergeCells(sheetfill, grid, grid.Groups, 0, grid.StartRow, gridTagRowCount);
}
}
}
private void GetChildren()
{
RecordAggregate record = (RecordAggregate)this.Record;
if (record is RowRecordsAggregate)
{
IEnumerator recordenum = ((RowRecordsAggregate)record).GetEnumerator();
while (recordenum.MoveNext())
{
if (recordenum.Current is RowRecord)
{
this.Nodes.Add(new RecordTreeNode((RowRecord)recordenum.Current));
}
}
CellValueRecordInterface[] valrecs = ((RowRecordsAggregate)record).GetValueRecords();
for (int j = 0; j < valrecs.Length; j++)
{
CellValueRecordTreeNode cvrtn = new CellValueRecordTreeNode(valrecs[j]);
if (valrecs[j] is FormulaRecordAggregate)
{
FormulaRecordAggregate fra = ((FormulaRecordAggregate)valrecs[j]);
cvrtn.ImageKey = "Folder";
if (fra.FormulaRecord != null)
{
cvrtn.Nodes.Add(new RecordTreeNode(fra.FormulaRecord));
}
if (fra.StringRecord != null)
{
cvrtn.Nodes.Add(new RecordTreeNode(fra.StringRecord));
}
}
this.Nodes.Add(cvrtn);
}
}
else if (record is ColumnInfoRecordsAggregate)
{
IEnumerator recordenum = ((ColumnInfoRecordsAggregate)record).GetEnumerator();
while (recordenum.MoveNext())
{
if (recordenum.Current is ColumnInfoRecord)
{
this.Nodes.Add(new RecordTreeNode((ColumnInfoRecord)recordenum.Current));
}
}
}
else if (record is PageSettingsBlock)
{
PageSettingsBlock psb = (PageSettingsBlock)record;
MockRecordVisitor rv = new MockRecordVisitor();
psb.VisitContainedRecords(rv);
foreach (Record rec in rv.Records)
{
this.Nodes.Add(new RecordTreeNode(rec));
}
}
else if (record is MergedCellsTable)
{
foreach (CellRangeAddress subRecord in ((MergedCellsTable)record).MergedRegions)
{
this.Nodes.Add(new CellRangeAddressTreeNode(subRecord));
}
}
else if (record is ConditionalFormattingTable)
{
ConditionalFormattingTable cft = (ConditionalFormattingTable)record;
for (int j = 0; j < cft.Count; j++)
{
CFRecordsAggregate cfra = cft.Get(j);
AbstractRecordTreeNode headernode = new RecordTreeNode(cfra.Header);
this.Nodes.Add(headernode);
for (int k = 0; k < cfra.NumberOfRules; k++)
{
this.Nodes.Add(new RecordTreeNode(cfra.GetRule(k)));
}
}
}
else if (record is WorksheetProtectionBlock)
{
WorksheetProtectionBlock wpb = (WorksheetProtectionBlock)record;
MockRecordVisitor rv = new MockRecordVisitor();
wpb.VisitContainedRecords(rv);
foreach (Record rec in rv.Records)
{
this.Nodes.Add(new RecordTreeNode(rec));
}
}
}
/// <summary>
/// Sets the cell type. The SetValue flag indicates whether to bother about
/// trying to preserve the current value in the new record if one is Created.
/// The SetCellValue method will call this method with false in SetValue
/// since it will overWrite the cell value later
/// </summary>
/// <param name="cellType">Type of the cell.</param>
/// <param name="setValue">if set to <c>true</c> [set value].</param>
/// <param name="row">The row.</param>
/// <param name="col">The col.</param>
/// <param name="styleIndex">Index of the style.</param>
private void SetCellType(CellType cellType, bool setValue, int row, int col, short styleIndex)
{
if (cellType > CellType.Error)
{
throw new Exception("I have no idea what type that Is!");
}
switch (cellType)
{
case CellType.Formula:
FormulaRecordAggregate frec = null;
if (cellType != this.cellType)
{
frec = _sheet.Sheet.RowsAggregate.CreateFormula(row, col);
}
else
{
frec = (FormulaRecordAggregate)_record;
}
frec.Column = col;
if (setValue)
{
frec.FormulaRecord.Value = NumericCellValue;
}
frec.XFIndex = styleIndex;
frec.Row = row;
_record = frec;
break;
case CellType.Numeric:
NumberRecord nrec = null;
if (cellType != this.cellType)
{
nrec = new NumberRecord();
}
else
{
nrec = (NumberRecord)_record;
}
nrec.Column = col;
if (setValue)
{
nrec.Value = NumericCellValue;
}
nrec.XFIndex = styleIndex;
nrec.Row = row;
_record = nrec;
break;
case CellType.String:
LabelSSTRecord lrec = null;
if (cellType != this.cellType)
{
lrec = new LabelSSTRecord();
}
else
{
lrec = (LabelSSTRecord)_record;
}
lrec.Column = col;
lrec.Row = row;
lrec.XFIndex = styleIndex;
if (setValue)
{
String str = ConvertCellValueToString();
int sstIndex = book.Workbook.AddSSTString(new UnicodeString(str));
lrec.SSTIndex = (sstIndex);
UnicodeString us = book.Workbook.GetSSTString(sstIndex);
stringValue = new HSSFRichTextString();
stringValue.UnicodeString = us;
}
_record = lrec;
break;
case CellType.Blank:
BlankRecord brec = null;
if (cellType != this.cellType)
{
brec = new BlankRecord();
}
else
{
brec = (BlankRecord)_record;
}
brec.Column = col;
// During construction the cellStyle may be null for a Blank cell.
brec.XFIndex = styleIndex;
brec.Row = row;
_record = brec;
break;
case CellType.Boolean:
BoolErrRecord boolRec = null;
if (cellType != this.cellType)
{
boolRec = new BoolErrRecord();
}
else
{
boolRec = (BoolErrRecord)_record;
}
boolRec.Column = col;
if (setValue)
{
boolRec.SetValue(ConvertCellValueToBoolean());
}
boolRec.XFIndex = styleIndex;
boolRec.Row = row;
_record = boolRec;
break;
case CellType.Error:
BoolErrRecord errRec = null;
if (cellType != this.cellType)
{
errRec = new BoolErrRecord();
}
else
{
errRec = (BoolErrRecord)_record;
}
errRec.Column = col;
if (setValue)
{
errRec.SetValue((byte)HSSFErrorConstants.ERROR_VALUE);
}
errRec.XFIndex = styleIndex;
errRec.Row = row;
_record = errRec;
break;
}
if (cellType != this.cellType &&
this.cellType != CellType.Unknown) // Special Value to indicate an Uninitialized Cell
{
_sheet.Sheet.ReplaceValueRecord(_record);
}
this.cellType = cellType;
}
