//[Obsolete]
//public static void SetBorderLeft(LF.Utils.NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderLeft(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the left border for a region of cells by manipulating the cell style
/// of the individual cells on the left
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderLeft(LF.Utils.NPOI.SS.UserModel.BorderStyle border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
private void CreateFilterModeRecord(HSSFSheet sheet,int insertPos)
{
//look for the FilterModeRecord
LF.Utils.NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(FilterModeRecord.sid);
// this local variable hides the class one: FilterModeRecord filtermode;
//if not found, add a new one
if (record == null)
{
filtermode = new FilterModeRecord();
sheet.Sheet.Records.Insert(insertPos, filtermode);
}
}
public CellEvaluationFrame(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
{
if (workbook == null)
{
throw new ArgumentException("workbook must not be null");
}
if (sheet == null)
{
throw new ArgumentException("sheet must not be null");
}
_workbook = workbook;
_sheet = sheet;
_srcRowNum = srcRowNum;
_srcColNum = srcColNum;
}
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg)
{
//look for the AutoFilterInfo Record
LF.Utils.NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
AutoFilterInfoRecord info;
if (record == null)
{
info = new AutoFilterInfoRecord();
sheet.Sheet.Records.Insert(insertPos, info);
}
else
{
info = record as AutoFilterInfoRecord;
}
info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1);
}
public HSSFAutoFilter(HSSFSheet sheet)
{
_sheet = sheet;
}
/// <summary>
/// Creates new Cell - Should only be called by HSSFRow. This Creates a cell
/// from scratch.
/// When the cell is initially Created it is Set to CellType.BLANK. Cell types
/// can be Changed/overwritten by calling SetCellValue with the appropriate
/// type as a parameter although conversions from one type to another may be
/// prohibited.
/// </summary>
/// <param name="book">Workbook record of the workbook containing this cell</param>
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
/// <param name="row">the row of this cell</param>
/// <param name="col">the column for this cell</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col)
: this(book, sheet, row, col, CellType.BLANK)
{
}
//[Obsolete]
//public static void SetRightBorderColor(short color, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetRightBorderColor(color, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the rightBorderColor attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="color">The color of the border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The workbook that the region is on.</param>
/// <param name="workbook">The sheet that the region is on.</param>
public static void SetRightBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.LastColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.RIGHT_BORDER_COLOR, color);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
/// <summary>
/// Creates an HSSFRow from a low level RowRecord object. Only HSSFSheet should do
/// this. HSSFSheet uses this when an existing file is Read in.
/// </summary>
/// <param name="book">low-level Workbook object containing the sheet that Contains this row</param>
/// <param name="sheet"> low-level Sheet object that Contains this Row</param>
/// <param name="record">the low level api object this row should represent</param>
///<see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/>
public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, RowRecord record)
{
this.book = book;
this.sheet = sheet;
row = record;
RowNum=(record.RowNumber);
// Don't trust colIx boundaries as read by other apps
// set the RowRecord empty for the moment
record.SetEmpty();
}
public DVRecord CreateDVRecord(HSSFSheet sheet)
{
LF.Utils.NPOI.HSSF.UserModel.DVConstraint.FormulaPair fp = _constraint.CreateFormulas(sheet);
return new DVRecord(_constraint.GetValidationType(),
_constraint.Operator,
_errorStyle, _emptyCellAllowed, SuppressDropDownArrow,
_constraint.GetValidationType() == ValidationType.LIST && _constraint.ExplicitListValues != null,
_ShowPromptBox, _prompt_title, _prompt_text,
_ShowErrorBox, _error_title, _error_text,
fp.Formula1, fp.Formula2,
_regions);
}
/**
* @return The Parsed token array representing the formula or value specified.
* Empty array if both formula and value are <code>null</code>
*/
private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFSheet sheet)
{
if (formula == null)
{
if (double.IsNaN(value))
{
return Ptg.EMPTY_PTG_ARRAY;
}
return new Ptg[] { new NumberPtg(value), };
}
if (!double.IsNaN(value))
{
throw new InvalidOperationException("Both formula and value cannot be present");
}
IWorkbook wb = sheet.Workbook;
return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)wb, FormulaType.CELL, wb.GetSheetIndex(sheet));
}
/// <summary>
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
/// </summary>
/// <param name="rowCounter">The 0 based row number</param>
/// <param name="sheet">The sheet that the row is part of.</param>
/// <returns>The row indicated by the rowCounter</returns>
public static LF.Utils.NPOI.SS.UserModel.IRow GetRow(int rowCounter, HSSFSheet sheet)
{
LF.Utils.NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
/// <summary>
/// Creates the patriarch.
/// </summary>
/// <param name="sheet">the sheet this patriarch is stored in.</param>
/// <param name="boundAggregate">The bound aggregate.</param>
public HSSFPatriarch(HSSFSheet sheet, EscherAggregate boundAggregate)
{
this._boundAggregate = boundAggregate;
this._sheet = sheet;
}
/// <summary>
/// Create an HSSFSheet for this HSSFWorkbook, Adds it to the sheets and
/// returns the high level representation. Use this to Create new sheets.
/// </summary>
/// <param name="sheetname">sheetname to set for the sheet.</param>
/// <returns>HSSFSheet representing the new sheet.</returns>
public ISheet CreateSheet(String sheetname)
{
if (sheetname == null)
{
throw new ArgumentException("sheetName must not be null");
}
if (workbook.ContainsSheetName(sheetname, _sheets.Count))
throw new ArgumentException("The workbook already contains a sheet of this name");
HSSFSheet sheet = new HSSFSheet(this);
workbook.SetSheetName(_sheets.Count, sheetname);
_sheets.Add(sheet);
bool isOnlySheet = _sheets.Count == 1;
sheet.IsSelected = isOnlySheet;
sheet.IsActive = isOnlySheet;
return sheet;
}
/// <summary>
/// Create an HSSFSheet for this HSSFWorkbook, Adds it to the sheets and returns
/// the high level representation. Use this to Create new sheets.
/// </summary>
/// <returns>HSSFSheet representing the new sheet.</returns>
public ISheet CreateSheet()
{
HSSFSheet sheet = new HSSFSheet(this);
_sheets.Add(sheet);
workbook.SetSheetName(_sheets.Count - 1, "Sheet" + (_sheets.Count - 1));
bool IsOnlySheet = _sheets.Count == 1;
sheet.IsSelected = (IsOnlySheet);
sheet.IsActive = (IsOnlySheet);
return sheet;
}
private void RemoveFilterModeRecord(HSSFSheet sheet)
{
if(filtermode!=null)
sheet.Sheet.Records.Remove(filtermode);
}
/**
* @return both Parsed formulas (for expression 1 and 2).
*/
/* package */
public FormulaPair CreateFormulas(HSSFSheet sheet)
{
Ptg[] formula1;
Ptg[] formula2;
if (IsListValidationType)
{
formula1 = CreateListFormula(sheet);
formula2 = Ptg.EMPTY_PTG_ARRAY;
}
else
{
formula1 = ConvertDoubleFormula(_formula1, _value1, sheet);
formula2 = ConvertDoubleFormula(_formula2, _value2, sheet);
}
return new FormulaPair(formula1, formula2);
}
private Ptg[] CreateListFormula(HSSFSheet sheet)
{
if (_explicitListValues == null)
{
IWorkbook wb = sheet.Workbook;
// formula is Parsed with slightly different RVA rules: (root node type must be 'reference')
return HSSFFormulaParser.Parse(_formula1, (HSSFWorkbook)wb, FormulaType.DATAVALIDATION_LIST, wb.GetSheetIndex(sheet));
// To do: Excel places restrictions on the available operations within a list formula.
// Some things like union and intersection are not allowed.
}
// explicit list was provided
StringBuilder sb = new StringBuilder(_explicitListValues.Length * 16);
for (int i = 0; i < _explicitListValues.Length; i++)
{
if (i > 0)
{
sb.Append('\0'); // list delimiter is the nul char
}
sb.Append(_explicitListValues[i]);
}
return new Ptg[] { new StringPtg(sb.ToString()), };
}
/**
* Returns all the charts for the given sheet.
*
* NOTE: You won't be able to do very much with
* these charts yet, as this is very limited support
*/
public static HSSFChart[] GetSheetCharts(HSSFSheet sheet)
{
List<HSSFChart> charts = new List<HSSFChart>();
HSSFChart lastChart = null;
HSSFSeries lastSeries = null;
// Find records of interest
IList records = sheet.Sheet.Records;
foreach (RecordBase r in records)
{
if (r is ChartRecord)
{
lastSeries = null;
lastChart = new HSSFChart(sheet, (ChartRecord)r);
charts.Add(lastChart);
}
else if (r is LegendRecord)
{
lastChart.legendRecord = (LegendRecord)r;
}
else if (r is SeriesRecord)
{
HSSFSeries series = new HSSFSeries((SeriesRecord)r);
lastChart.series.Add(series);
lastSeries = series;
}
else if (r is ChartTitleFormatRecord)
{
lastChart.chartTitleFormat =
(ChartTitleFormatRecord)r;
}
else if (r is SeriesTextRecord)
{
// Applies to a series, unless we've seen
// a legend already
SeriesTextRecord str = (SeriesTextRecord)r;
if (lastChart.legendRecord == null &&
lastChart.series.Count > 0)
{
HSSFSeries series = (HSSFSeries)
lastChart.series[lastChart.series.Count - 1];
series.seriesTitleText = str;
}
else
{
lastChart.chartTitleText = str;
}
}
else if (r is LinkedDataRecord)
{
LinkedDataRecord linkedDataRecord = (LinkedDataRecord)r;
if (lastSeries != null)
{
lastSeries.InsertData(linkedDataRecord);
}
}
else if (r is ValueRangeRecord)
{
lastChart.valueRanges.Add((ValueRangeRecord)r);
}
else if (r is Record)
{
if (lastChart != null)
{
Record record = (Record)r;
foreach (int type in Enum.GetValues(typeof(HSSFChartType)))
{
if (type == 0)
{
continue;
}
if (record.Sid == type)
{
lastChart.type = (HSSFChartType)type;
break;
}
}
}
}
}
return (HSSFChart[])
charts.ToArray();
}
/* package */
//public HSSFSheetConditionalFormatting(HSSFWorkbook workbook, InternalSheet sheet)
//{
// _workbook = workbook;
// _conditionalFormattingTable = sheet.ConditionalFormattingTable;
//}
public HSSFSheetConditionalFormatting(HSSFSheet sheet)
{
_sheet = sheet;
_conditionalFormattingTable = sheet.Sheet.ConditionalFormattingTable;
}
private HSSFChart(HSSFSheet sheet, ChartRecord chartRecord)
{
this.chartRecord = chartRecord;
this.sheet = sheet;
}
/// <summary>
/// Creates new HSSFRow from scratch. Only HSSFSheet should do this.
/// </summary>
/// <param name="book">low-level Workbook object containing the sheet that Contains this row</param>
/// <param name="sheet">low-level Sheet object that Contains this Row</param>
/// <param name="rowNum">the row number of this row (0 based)</param>
///<see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/>
public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum):this(book, sheet, new RowRecord(rowNum))
{
}
/**
* Creates a bar chart. API needs some work. :)
*
* NOTE: Does not yet work... checking it in just so others
* can take a look.
*/
public void CreateBarChart(HSSFWorkbook workbook, HSSFSheet sheet)
{
List<Record> records = new List<Record>();
records.Add(CreateMSDrawingObjectRecord());
records.Add(CreateOBJRecord());
records.Add(CreateBOFRecord());
records.Add(new HeaderRecord(string.Empty));
records.Add(new FooterRecord(string.Empty));
records.Add(CreateHCenterRecord());
records.Add(CreateVCenterRecord());
records.Add(CreatePrintSetupRecord());
// unknown 33
records.Add(CreateFontBasisRecord1());
records.Add(CreateFontBasisRecord2());
records.Add(new ProtectRecord(false));
records.Add(CreateUnitsRecord());
records.Add(CreateChartRecord(0, 0, 30434904, 19031616));
records.Add(CreateBeginRecord());
records.Add(CreateSCLRecord((short)1, (short)1));
records.Add(CreatePlotGrowthRecord(65536, 65536));
records.Add(CreateFrameRecord1());
records.Add(CreateBeginRecord());
records.Add(CreateLineFormatRecord(true));
records.Add(CreateAreaFormatRecord1());
records.Add(CreateEndRecord());
records.Add(CreateSeriesRecord());
records.Add(CreateBeginRecord());
records.Add(CreateTitleLinkedDataRecord());
records.Add(CreateValuesLinkedDataRecord());
records.Add(CreateCategoriesLinkedDataRecord());
records.Add(CreateDataFormatRecord());
// records.Add(CreateBeginRecord());
// unknown
// records.Add(CreateEndRecord());
records.Add(CreateSeriesToChartGroupRecord());
records.Add(CreateEndRecord());
records.Add(CreateSheetPropsRecord());
records.Add(CreateDefaultTextRecord((short)CategoryDataType.ALL_TEXT_CHARACTERISTIC0));
records.Add(CreateAllTextRecord());
records.Add(CreateBeginRecord());
// unknown
records.Add(CreateFontIndexRecord(5));
records.Add(CreateDirectLinkRecord());
records.Add(CreateEndRecord());
records.Add(CreateDefaultTextRecord((short)3)); // eek, undocumented text type
records.Add(CreateUnknownTextRecord());
records.Add(CreateBeginRecord());
records.Add(CreateFontIndexRecord((short)6));
records.Add(CreateDirectLinkRecord());
records.Add(CreateEndRecord());
records.Add(CreateAxisUsedRecord((short)1));
CreateAxisRecords(records);
records.Add(CreateEndRecord());
records.Add(CreateDimensionsRecord());
records.Add(CreateSeriesIndexRecord(2));
records.Add(CreateSeriesIndexRecord(1));
records.Add(CreateSeriesIndexRecord(3));
records.Add(EOFRecord.instance);
sheet.InsertChartRecords(records);
workbook.InsertChartRecord();
}
public HSSFEvaluationSheet(HSSFSheet hs)
{
_hs = hs;
}
/**
* Notifies this evaluation tracker that evaluation of the specified cell Is
* about to start.<br/>
*
* In the case of a <c>true</c> return code, the caller should
* continue evaluation of the specified cell, and also be sure to call
* <c>endEvaluate()</c> when complete.<br/>
*
* In the case of a <c>false</c> return code, the caller should
* return an evaluation result of
* <c>ErrorEval.CIRCULAR_REF_ERROR</c>, and not call <c>endEvaluate()</c>.
* <br/>
* @return <c>true</c> if the specified cell has not been visited yet in the current
* evaluation. <c>false</c> if the specified cell Is alReady being evaluated.
*/
public bool StartEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
{
CellEvaluationFrame cef = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum);
if (_evaluationFrames.Contains(cef))
{
return false;
}
_evaluationFrames.Add(cef);
return true;
}
/// <summary>
/// Sets the topBorderColor attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="color">The color of the border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetTopBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.FirstRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.TOP_BORDER_COLOR, color);
LF.Utils.NPOI.SS.UserModel.IRow row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
/**
* Notifies this evaluation tracker that the evaluation of the specified
* cell Is complete. <p/>
*
* Every successful call to <c>startEvaluate</c> must be followed by a
* call to <c>endEvaluate</c> (recommended in a finally block) to enable
* proper tracking of which cells are being evaluated at any point in time.<p/>
*
* Assuming a well behaved client, parameters to this method would not be
* required. However, they have been included to assert correct behaviour,
* and form more meaningful error messages.
*/
public void EndEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
{
int nFrames = _evaluationFrames.Count;
if (nFrames < 1)
{
throw new InvalidOperationException("Call to endEvaluate without matching call to startEvaluate");
}
nFrames--;
CellEvaluationFrame cefExpected = (CellEvaluationFrame)_evaluationFrames[nFrames];
CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum);
if (!cefActual.Equals(cefExpected))
{
throw new Exception("Wrong cell specified. "
+ "Corresponding startEvaluate() call was for cell {"
+ cefExpected.FormatAsString() + "} this endEvaluate() call Is for cell {"
+ cefActual.FormatAsString() + "}");
}
// else - no problems so pop current frame
_evaluationFrames.Remove(nFrames);
}
/// <summary>
/// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when
/// reading in cells from an existing sheet.
/// </summary>
/// <param name="book">Workbook record of the workbook containing this cell</param>
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
/// <param name="cval">the Cell Value Record we wish to represent</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval)
{
record = cval;
cellType = DetermineType(cval);
stringValue = null;
this.book = book;
this.sheet = sheet;
switch (cellType)
{
case CellType.STRING:
stringValue = new HSSFRichTextString(book.Workbook, (LabelSSTRecord)cval);
break;
case CellType.BLANK:
break;
case CellType.FORMULA:
stringValue = new HSSFRichTextString(((FormulaRecordAggregate)cval).StringValue);
break;
}
ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(cval.XFIndex);
CellStyle = new HSSFCellStyle((short)cval.XFIndex, xf, book);
}
public HSSFDataValidationHelper(HSSFSheet sheet)
: base()
{
this.sheet = sheet;
}
/// <summary>
/// Creates new Cell - Should only be called by HSSFRow. This Creates a cell
/// from scratch.
/// </summary>
/// <param name="book">Workbook record of the workbook containing this cell</param>
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
/// <param name="row">the row of this cell</param>
/// <param name="col">the column for this cell</param>
/// <param name="type">CellType.NUMERIC, CellType.STRING, CellType.FORMULA, CellType.BLANK,
/// CellType.BOOLEAN, CellType.ERROR</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col,
CellType type)
{
CheckBounds(col);
cellType = CellType.Unknown; // Force 'SetCellType' to Create a first Record
stringValue = null;
this.book = book;
this.sheet = sheet;
short xfindex = sheet.Sheet.GetXFIndexForColAt(col);
SetCellType(type, false, row, col, xfindex);
}
/**
* TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
* this call will produce the wrong results if the formula contains any cell references
* One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
* Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
*
* @return <code>null</code> if <c>formula</c> was null.
*/
private static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
{
if (formula == null)
{
return null;
}
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.CELL, sheetIndex);
}
