/**
* Set a string value for the cell.
*
* @param str value to Set the cell to. For formulas we'll Set the 'pre-Evaluated result 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.
*/
public void SetCellValue(IRichTextString str)
{
if (str == null || str.String == null)
{
SetCellType(CellType.BLANK);
return;
}
CellType cellType = CellType;
switch (cellType)
{
case CellType.FORMULA:
_cell.v = (str.String);
_cell.t = (ST_CellType.str);
break;
default:
if (_cell.t == ST_CellType.inlineStr)
{
//set the 'pre-Evaluated result
_cell.v = str.String;
}
else
{
_cell.t = ST_CellType.s;
XSSFRichTextString rt = (XSSFRichTextString)str;
rt.SetStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
_cell.v = sRef.ToString();
}
break;
}
}
/**
* Set a string value for the cell.
*
* @param str value to Set the cell to. For formulas we'll Set the 'pre-Evaluated result 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.
*/
public void SetCellValue(IRichTextString str)
{
if (str == null || string.IsNullOrEmpty(str.String))
{
SetCellType(CellType.Blank);
return;
}
if (str.Length > SpreadsheetVersion.EXCEL2007.MaxTextLength)
{
throw new ArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
CellType cellType = CellType;
switch (cellType)
{
case CellType.Formula:
_cell.v = (str.String);
_cell.t = (ST_CellType.str);
break;
default:
if (_cell.t == ST_CellType.inlineStr)
{
//set the 'pre-Evaluated result
_cell.v = str.String;
}
else
{
_cell.t = ST_CellType.s;
XSSFRichTextString rt = (XSSFRichTextString)str;
rt.SetStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
_cell.v = sRef.ToString();
}
break;
}
}
public void WriteCell(int columnIndex, ICell cell)
{
if (cell == null)
{
return;
}
string cellRef = new CellReference(RowNum, columnIndex).FormatAsString();
WriteAsBytes(OutputStream, "<c r=\"" + cellRef + "\"");
if (cell.CellStyle.Index != 0)
{
// need to convert the short to unsigned short as the indexes can be up to 64k
// ideally we would use int for this index, but that would need changes to some more
// APIs
WriteAsBytes(OutputStream, " s=\"" + (cell.CellStyle.Index & 0xffff) + "\"");
}
switch (cell.CellType)
{
case CellType.Blank:
{
WriteAsBytes(OutputStream, ">");
break;
}
case CellType.Formula:
{
WriteAsBytes(OutputStream, ">");
WriteAsBytes(OutputStream, "<f>");
OutputQuotedString(cell.CellFormula);
WriteAsBytes(OutputStream, "</f>");
switch (cell.GetCachedFormulaResultTypeEnum())
{
case CellType.Numeric:
double nval = cell.NumericCellValue;
if (!Double.IsNaN(nval))
{
WriteAsBytes(OutputStream, "<v>" + nval + "</v>");
}
break;
default:
break;
}
break;
}
case CellType.String:
{
if (_sharedStringSource != null)
{
XSSFRichTextString rt = new XSSFRichTextString(cell.StringCellValue);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
WriteAsBytes(OutputStream, " t=\"" + ST_CellType.s + "\">");
WriteAsBytes(OutputStream, "<v>");
WriteAsBytes(OutputStream, sRef.ToString());
WriteAsBytes(OutputStream, "</v>");
}
else
{
WriteAsBytes(OutputStream, " t=\"inlineStr\">");
WriteAsBytes(OutputStream, "<is><t");
if (HasLeadingTrailingSpaces(cell.StringCellValue))
{
WriteAsBytes(OutputStream, " xml:space=\"preserve\"");
}
WriteAsBytes(OutputStream, ">");
OutputQuotedString(cell.StringCellValue);
WriteAsBytes(OutputStream, "</t></is>");
}
break;
}
case CellType.Numeric:
{
WriteAsBytes(OutputStream, " t=\"n\">");
WriteAsBytes(OutputStream, "<v>" + cell.NumericCellValue + "</v>");
break;
}
case CellType.Boolean:
{
WriteAsBytes(OutputStream, " t=\"b\">");
WriteAsBytes(OutputStream, "<v>" + (cell.BooleanCellValue ? "1" : "0") + "</v>");
break;
}
case CellType.Error:
{
FormulaError error = FormulaError.ForInt(cell.ErrorCellValue);
WriteAsBytes(OutputStream, " t=\"e\">");
WriteAsBytes(OutputStream, "<v>" + error.String + "</v>");
break;
}
default:
{
throw new InvalidOperationException("Invalid cell type: " + cell.CellType);
}
}
WriteAsBytes(OutputStream, "</c>");
OutputStream.Flush();
}
public void TestCreateNew()
{
SharedStringsTable sst = new SharedStringsTable();
CT_Rst st;
int idx;
// Check defaults
Assert.IsNotNull(sst.Items);
Assert.AreEqual(0, sst.Items.Count);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(0, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Hello, World!");
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(1, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(2, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
//and again
idx = sst.AddEntry(st);
Assert.AreEqual(0, idx);
Assert.AreEqual(3, sst.Count);
Assert.AreEqual(1, sst.UniqueCount);
st = new CT_Rst();
st.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(4, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
//add the same entry again
idx = sst.AddEntry(st);
Assert.AreEqual(1, idx);
Assert.AreEqual(5, sst.Count);
Assert.AreEqual(2, sst.UniqueCount);
st = new CT_Rst();
CT_RElt r = st.AddNewR();
CT_RPrElt pr = r.AddNewRPr();
pr.AddNewColor().SetRgb(new byte[] { (byte)0xFF, 0, 0 }); //red
pr.AddNewI().val = (true); //bold
pr.AddNewB().val = (true); //italic
r.t = ("Second string");
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(6, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
idx = sst.AddEntry(st);
Assert.AreEqual(2, idx);
Assert.AreEqual(7, sst.Count);
Assert.AreEqual(3, sst.UniqueCount);
//OK. the sst table is Filled, check the contents
Assert.AreEqual(3, sst.Items.Count);
Assert.AreEqual("Hello, World!", new XSSFRichTextString(sst.GetEntryAt(0)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(1)).ToString());
Assert.AreEqual("Second string", new XSSFRichTextString(sst.GetEntryAt(2)).ToString());
}
