//private static POILogger log = POILogFactory.GetLogger(typeof(HSSFSheet));
/// <summary>
/// Creates new HSSFSheet - called by HSSFWorkbook to create a _sheet from
/// scratch. You should not be calling this from application code (its protected anyhow).
/// </summary>
/// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
/// <see cref="Zephyr.Utils.NPOI.HSSF.UserModel.HSSFWorkbook.CreateSheet()"/>
public HSSFSheet(HSSFWorkbook workbook)
{
_sheet = InternalSheet.CreateSheet();
rows = new Dictionary<int, Zephyr.Utils.NPOI.SS.UserModel.IRow>();
this._workbook = workbook;
this.book = workbook.Workbook;
}
public HSSFCreationHelper(HSSFWorkbook wb)
{
workbook = wb;
// Create the things we only ever need one of
dataFormat = new HSSFDataFormat(workbook.Workbook);
}
private static ICellStyle GetHeadStyle(HSSFWorkbook workbook)
{
//表头样式
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.LEFT;//居中对齐
//表头单元格背景色
headStyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//表头单元格边框
headStyle.BorderTop = BorderStyle.THIN;
headStyle.TopBorderColor = HSSFColor.BLACK.index;
headStyle.BorderRight = BorderStyle.THIN;
headStyle.RightBorderColor = HSSFColor.BLACK.index;
headStyle.BorderBottom = BorderStyle.THIN;
headStyle.BottomBorderColor = HSSFColor.BLACK.index;
headStyle.BorderLeft = BorderStyle.THIN;
headStyle.LeftBorderColor = HSSFColor.BLACK.index;
//表头字体设置
var font = workbook.CreateFont();
font.FontHeightInPoints = 12;//字号
font.Boldweight = 600;//加粗
//font.Color = HSSFColor.WHITE.index;//颜色
headStyle.SetFont(font);
return headStyle;
}
/**
* Construct an escher graphics object.
*
* @param escherGroup The escher Group to Write the graphics calls into.
* @param workbook The workbook we are using.
* @param forecolor The foreground color to use as default.
* @param verticalPointsPerPixel The font multiplier. (See class description for information on how this works.).
*/
public EscherGraphics(HSSFShapeGroup escherGroup, HSSFWorkbook workbook, Color forecolor, float verticalPointsPerPixel)
{
this.escherGroup = escherGroup;
this.workbook = workbook;
this.verticalPointsPerPixel = verticalPointsPerPixel;
this.verticalPixelsPerPoint = 1 / verticalPointsPerPixel;
this.font = new Font("Arial", 10);
this.foreground = forecolor;
// background = backcolor;
}
/// <summary>
/// Initializes a new instance of the <see cref="HSSFConditionalFormatting"/> class.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="cfAggregate">The cf aggregate.</param>
public HSSFConditionalFormatting(HSSFWorkbook workbook, CFRecordsAggregate cfAggregate)
{
if (workbook == null)
{
throw new ArgumentException("workbook must not be null");
}
if (cfAggregate == null)
{
throw new ArgumentException("cfAggregate must not be null");
}
_workbook = workbook;
this.cfAggregate = cfAggregate;
}
//[Obsolete]
//public static void SetBorderLeft(Zephyr.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(Zephyr.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);
}
}
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;
}
public HSSFAutoFilter(string formula,HSSFWorkbook workbook)
{
//this.workbook = workbook;
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, workbook);
if (!(ptgs[0] is Area3DPtg))
throw new ArgumentException("incorrect formula");
Area3DPtg ptg = (Area3DPtg)ptgs[0];
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(ptg.ExternSheetIndex);
//look for the prior record
int loc = sheet.Sheet.FindFirstRecordLocBySid(DefaultColWidthRecord.sid) ;
CreateFilterModeRecord(sheet, loc+1);
CreateAutoFilterInfoRecord(sheet, loc + 2,ptg);
//look for "_FilterDatabase" NameRecord of the sheet
NameRecord name = workbook.Workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex+1);
if (name == null)
name = workbook.Workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1);
name.IsHiddenName = true;
name.NameDefinition = ptgs;
}
/**
* 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();
}
/// <summary>
/// Verifies that this style belongs to the supplied Workbook.
/// Will throw an exception if it belongs to a different one.
/// This is normally called when trying to assign a style to a
/// cell, to ensure the cell and the style are from the same
/// workbook (if they're not, it won't work)
/// </summary>
/// <param name="wb">The workbook.</param>
public void VerifyBelongsToWorkbook(HSSFWorkbook wb)
{
if (wb.Workbook != workbook)
{
throw new ArgumentException("This Style does not belong to the supplied Workbook. Are you trying to assign a style from one workbook to the cell of a differnt workbook?");
}
}
/// <summary>
/// Creates an HSSFSheet representing the given Sheet object. Should only be
/// called by HSSFWorkbook when reading in an exisiting file.
/// </summary>
/// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
/// <param name="sheet">lowlevel Sheet object this _sheet will represent</param>
/// <see cref="Zephyr.Utils.NPOI.HSSF.UserModel.HSSFWorkbook(Zephyr.Utils.NPOI.POIFS.FileSystem.DirectoryNode, bool)"/>
public HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)
{
this._sheet = sheet;
rows = new Dictionary<int, Zephyr.Utils.NPOI.SS.UserModel.IRow>();
this._workbook = workbook;
this.book = _workbook.Workbook;
SetPropertiesFromSheet(_sheet);
}
/// <summary>
/// Take a cell, and align it.
/// </summary>
/// <param name="cell">the cell to Set the alignment for</param>
/// <param name="workbook">The workbook that is being worked with.</param>
/// <param name="align">the column alignment to use.</param>
public static void SetAlignment(ICell cell, HSSFWorkbook workbook, short align)
{
SetCellStyleProperty(cell, workbook, ALIGNMENT, align);
}
/// <summary>
/// Clones the _sheet.
/// </summary>
/// <param name="workbook">The _workbook.</param>
/// <returns>the cloned sheet</returns>
public HSSFSheet CloneSheet(HSSFWorkbook workbook)
{
return new HSSFSheet(workbook, _sheet.CloneSheet());
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelExtractor"/> class.
/// </summary>
/// <param name="wb">The wb.</param>
public ExcelExtractor(HSSFWorkbook wb)
: base(wb)
{
this.wb = wb;
_formatter = new HSSFDataFormatter();
}
public CellPropertySetter(HSSFWorkbook workbook, String propertyName, int value)
{
_workbook = workbook;
_propertyName = propertyName;
_propertyValue = (short)value;
}
/* package */
internal HSSFName(HSSFWorkbook book, NameRecord name):this(book, name, null)
{
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j]) newPosition--;
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
for (int i = 21; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveExFormatRecord(
xfrs[i]
);
}
}
// Finally, update the cells to point at
// their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
public static Stream ListToExcel(object list, Dictionary<string, string> titles, bool IsExportAllCol)
{
const int startIndex = 0;
var fields = titles.Keys.ToList();
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
var row = sheet.CreateRow(startIndex);
var headStyle = GetHeadStyle(workbook);
EachHelper.EachListHeader(list, (i, name, type) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
var cellIndex = fields.IndexOf(name) + startIndex;
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(titles.ContainsKey(name)?titles[name]:name);
cell.CellStyle = headStyle;
sheet.AutoSizeColumn(cellIndex);
});
EachHelper.EachListRow(list, (rowIndex, dataRow) =>
{
row = sheet.CreateRow(rowIndex + 1);
EachHelper.EachObjectProperty(dataRow, (i, name, value) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
var cellIndex = fields.IndexOf(name) + startIndex;
var dataStyle = GetDataStyle(workbook);
var cell = row.CreateCell(cellIndex);
cell.CellStyle = dataStyle;
switch ((value??string.Empty).GetType().Name.ToLower())
{
case "int32":
case "int64":
case "decimal":
dataStyle.Alignment = HorizontalAlignment.RIGHT;
cell.SetCellValue(ZConvert.To<double>(value,0));
break;
default:
cell.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell.SetCellValue(ZConvert.ToString(value));
break;
}
});
});
var ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
return ms;
}
private static ICellStyle GetDataStyle(HSSFWorkbook workbook)
{
//数据样式
var dataStyle = workbook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
//数据单元格的边框
dataStyle.BorderTop = BorderStyle.THIN;
dataStyle.TopBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderRight = BorderStyle.THIN;
dataStyle.RightBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderBottom = BorderStyle.THIN;
dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderLeft = BorderStyle.THIN;
dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
//数据的字体
var datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
dataStyle.SetFont(datafont);
return dataStyle;
}
/// <summary>
/// Sets the format properties of the given style based on the given map.
/// </summary>
/// <param name="style">The cell style</param>
/// <param name="workbook">The parent workbook.</param>
/// <param name="properties">The map of format properties (String -> Object).</param>
private static void SetFormatProperties(
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (Zephyr.Utils.NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (Zephyr.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (Zephyr.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_LEFT);
style.BorderRight = (Zephyr.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (Zephyr.Utils.NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_TOP);
style.BottomBorderColor = (GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat = (GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern = (Zephyr.Utils.NPOI.SS.UserModel.FillPatternType)GetShort(properties, FILL_PATTERN);
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden = (GetBoolean(properties, HIDDEN));
style.Indention = (GetShort(properties, INDENTION));
style.LeftBorderColor = (GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked = (GetBoolean(properties, LOCKED));
style.RightBorderColor = (GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation = (GetShort(properties, ROTATION));
style.TopBorderColor = (GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment = (Zephyr.Utils.NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
style.WrapText = (GetBoolean(properties, WRAP_TEXT));
}
/**
* This method attempt to find an already existing HSSFCellStyle that matches
* what you want the style to be. If it does not find the style, then it
* Creates a new one. If it does Create a new one, then it applies the
* propertyName and propertyValue to the style. This is necessary because
* Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be
* changed.
*@param propertyValue The value of the property that is to be
* changed.
*@param cell The cell that needs it's style changes
*@exception NestableException Thrown if an error happens.
*/
public static void SetCellStyleProperty(Zephyr.Utils.NPOI.SS.UserModel.ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
{
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle originalStyle = cell.CellStyle;
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = null;
Hashtable values = GetFormatProperties(originalStyle);
values[propertyName] = propertyValue;
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle wbStyle = workbook.GetCellStyleAt(i);
Hashtable wbStyleMap = GetFormatProperties(wbStyle);
if (wbStyleMap.Equals(values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle = (newStyle);
}
/// <summary>
/// Take a cell, and apply a font to it
/// </summary>
/// <param name="cell">the cell to Set the alignment for</param>
/// <param name="workbook">The workbook that is being worked with.</param>
/// <param name="font">The HSSFFont that you want to Set...</param>
public static void SetFont(ICell cell, HSSFWorkbook workbook, HSSFFont font)
{
SetCellStyleProperty(cell, workbook, FONT, font);
}
/// <summary>
/// Creates new HSSFName - called by HSSFWorkbook to Create a sheet from
/// scratch.
/// </summary>
/// <param name="book">lowlevel Workbook object associated with the sheet.</param>
/// <param name="name">the Name Record</param>
/// <param name="comment"></param>
internal HSSFName(HSSFWorkbook book, NameRecord name, NameCommentRecord comment)
{
this.book = book;
this._definedNameRec = name;
_commentRec = comment;
}
/// <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)
{
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4) continue;
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4) continue;
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j]) newPosition--;
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
Zephyr.Utils.NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
if (cell.CellType == Zephyr.Utils.NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="HSSFCellStyle"/> class.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="rec">The record.</param>
/// <param name="workbook">The workbook.</param>
public HSSFCellStyle(short index, ExtendedFormatRecord rec, HSSFWorkbook workbook)
:this(index, rec, workbook.Workbook)
{
}
/// <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);
}
//[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 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);
}
/// <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);
Zephyr.Utils.NPOI.SS.UserModel.IRow row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
