public void TestClearFormatting()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.AreEqual("Apache POI", rt.String);
Assert.AreEqual(false, rt.HasFormatting());
rt.ClearFormatting();
CT_Rst st = rt.GetCTRst();
Assert.IsTrue(st.IsSetT());
Assert.AreEqual("Apache POI", rt.String);
Assert.AreEqual(0, rt.NumFormattingRuns);
Assert.AreEqual(false, rt.HasFormatting());
XSSFFont font = new XSSFFont();
font.IsBold = true;
rt.ApplyFont(7, 10, font);
Assert.AreEqual(2, rt.NumFormattingRuns);
Assert.AreEqual(true, rt.HasFormatting());
rt.ClearFormatting();
Assert.AreEqual("Apache POI", rt.String);
Assert.AreEqual(0, rt.NumFormattingRuns);
Assert.AreEqual(false, rt.HasFormatting());
}
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook parent = (XSSFWorkbook)this.GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(parent.GetStylesSource());
CT_TextParagraph ctTextParagraph = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun ctRegularTextRun = ctTextParagraph.AddNewR();
CT_TextCharacterProperties characterProperties = ctRegularTextRun.AddNewRPr();
characterProperties.lang = "en-US";
characterProperties.sz = 1100;
ctRegularTextRun.t = str.String;
}
else
{
for (int index = 0; index < str.GetCTRst().sizeOfRArray(); ++index)
{
CT_RElt rarray = str.GetCTRst().GetRArray(index);
CT_RPrElt pr = rarray.rPr ?? rarray.AddNewRPr();
CT_RegularTextRun ctRegularTextRun = ctTextParagraph.AddNewR();
CT_TextCharacterProperties rPr = ctRegularTextRun.AddNewRPr();
rPr.lang = "en-US";
XSSFSimpleShape.ApplyAttributes(pr, rPr);
ctRegularTextRun.t = rarray.t;
}
}
this.ctShape.txBody.SetPArray(new CT_TextParagraph[1]
{
ctTextParagraph
});
}
/**
* 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;
}
}
public void TestGetFonts()
{
XSSFRichTextString rt = new XSSFRichTextString();
XSSFFont font1 = new XSSFFont();
font1.FontName = ("Arial");
font1.IsItalic = (true);
rt.Append("The quick", font1);
XSSFFont font1FR = (XSSFFont)rt.GetFontOfFormattingRun(0);
Assert.AreEqual(font1.IsItalic, font1FR.IsItalic);
Assert.AreEqual(font1.FontName, font1FR.FontName);
XSSFFont font2 = new XSSFFont();
font2.FontName = ("Courier");
font2.IsBold = (true);
rt.Append(" brown fox", font2);
XSSFFont font2FR = (XSSFFont)rt.GetFontOfFormattingRun(1);
Assert.AreEqual(font2.IsBold, font2FR.IsBold);
Assert.AreEqual(font2.FontName, font2FR.FontName);
}
public void TestApplyFont()
{
XSSFRichTextString rt = new XSSFRichTextString();
rt.Append("123");
rt.Append("4567");
rt.Append("89");
Assert.AreEqual("123456789", rt.String);
XSSFFont font1 = new XSSFFont();
font1.IsBold = (true);
rt.ApplyFont(2, 5, font1);
Assert.AreEqual(4, rt.NumFormattingRuns);
Assert.AreEqual(0, rt.GetIndexOfFormattingRun(0));
Assert.AreEqual("12", rt.GetCTRst().GetRArray(0).t);
Assert.AreEqual(2, rt.GetIndexOfFormattingRun(1));
Assert.AreEqual("345", rt.GetCTRst().GetRArray(1).t);
Assert.AreEqual(5, rt.GetIndexOfFormattingRun(2));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(2));
Assert.AreEqual("67", rt.GetCTRst().GetRArray(2).t);
Assert.AreEqual(7, rt.GetIndexOfFormattingRun(3));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(3));
Assert.AreEqual("89", rt.GetCTRst().GetRArray(3).t);
}
/**
* Creates a new XSSFRichTextString for you.
*/
public IRichTextString CreateRichTextString(String text)
{
XSSFRichTextString rt = new XSSFRichTextString(text);
rt.SetStylesTableReference(workbook.GetStylesSource());
return(rt);
}
public IRichTextString CreateRichTextString(string text)
{
XSSFRichTextString xssfRichTextString = new XSSFRichTextString(text);
xssfRichTextString.SetStylesTableReference(this.workbook.GetStylesSource());
return((IRichTextString)xssfRichTextString);
}
public void TestApplyFont_usermodel()
{
String text = "Apache Software Foundation";
XSSFRichTextString str = new XSSFRichTextString(text);
XSSFFont font1 = new XSSFFont();
XSSFFont font2 = new XSSFFont();
XSSFFont font3 = new XSSFFont();
str.ApplyFont(font1);
Assert.AreEqual(1, str.NumFormattingRuns);
str.ApplyFont(0, 6, font1);
str.ApplyFont(6, text.Length, font2);
Assert.AreEqual(2, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software Foundation", str.GetCTRst().GetRArray(1).t);
str.ApplyFont(15, 26, font3);
Assert.AreEqual(3, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software", str.GetCTRst().GetRArray(1).t);
Assert.AreEqual(" Foundation", str.GetCTRst().GetRArray(2).t);
str.ApplyFont(6, text.Length, font2);
Assert.AreEqual(2, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software Foundation", str.GetCTRst().GetRArray(1).t);
}
public void TestApplyFont()
{
XSSFRichTextString rt = new XSSFRichTextString();
rt.Append("123");
rt.Append("4567");
rt.Append("89");
Assert.AreEqual("123456789", rt.String);
XSSFFont font1 = new XSSFFont();
font1.IsBold = (true);
rt.ApplyFont(2, 5, font1);
Assert.AreEqual(4, rt.NumFormattingRuns);
Assert.AreEqual(0, rt.GetIndexOfFormattingRun(0));
Assert.AreEqual("12", rt.GetCTRst().GetRArray(0).t);
Assert.AreEqual(2, rt.GetIndexOfFormattingRun(1));
Assert.AreEqual("345", rt.GetCTRst().GetRArray(1).t);
Assert.AreEqual(5, rt.GetIndexOfFormattingRun(2));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(2));
Assert.AreEqual("67", rt.GetCTRst().GetRArray(2).t);
Assert.AreEqual(7, rt.GetIndexOfFormattingRun(3));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(3));
Assert.AreEqual("89", rt.GetCTRst().GetRArray(3).t);
Assert.AreEqual(-1, rt.GetIndexOfFormattingRun(9999));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(9999));
Assert.IsNull(rt.GetFontAtIndex(9999));
}
public void TestRichText()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 128, 128)));
font.IsItalic = (true);
font.IsBold = (true);
font.Underline = FontUnderlineType.Single;
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.p[0];
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.r[0].rPr;
Assert.AreEqual(true, rPr.b);
Assert.AreEqual(true, rPr.i);
Assert.AreEqual(ST_TextUnderlineType.sng, rPr.u);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestEmpty()
{
XSSFRichTextString rt = new XSSFRichTextString();
Assert.AreEqual(0, rt.GetIndexOfFormattingRun(9999));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(9999));
Assert.IsNull(rt.GetFontAtIndex(9999));
}
/**
* Chooses a new bool value for the cell when its type is changing.<p/>
*
* Usually the caller is calling SetCellType() with the intention of calling
* SetCellValue(bool) straight afterwards. This method only exists to give
* the cell a somewhat reasonable value until the SetCellValue() call (if at all).
* TODO - perhaps a method like SetCellTypeAndValue(int, Object) should be introduced to avoid this
*/
private bool ConvertCellValueToBoolean()
{
CellType cellType = CellType;
if (cellType == CellType.Formula)
{
cellType = GetBaseCellType(false);
}
switch (cellType)
{
case CellType.Boolean:
return(TRUE_AS_STRING.Equals(_cell.v));
case CellType.String:
int sstIndex = Int32.Parse(_cell.v);
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.GetEntryAt(sstIndex));
String text = rt.String;
return(Boolean.Parse(text));
case CellType.Numeric:
return(Double.Parse(_cell.v, CultureInfo.InvariantCulture) != 0);
case CellType.Error:
case CellType.Blank:
return(false);
}
throw new RuntimeException("Unexpected cell type (" + cellType + ")");
}
public void Test47278()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.CreateWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
IRow row = sheet.CreateRow(0);
SharedStringsTable sst = wb.GetSharedStringSource();
Assert.AreEqual(0, sst.Count);
//case 1. cell.SetCellValue(new XSSFRichTextString((String)null));
ICell cell_0 = row.CreateCell(0);
XSSFRichTextString str = new XSSFRichTextString((String)null);
Assert.IsNull(str.String);
cell_0.SetCellValue(str);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_0.CellType);
//case 2. cell.SetCellValue((String)null);
ICell cell_1 = row.CreateCell(1);
cell_1.SetCellValue((String)null);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_1.CellType);
}
public void TestRichTextFontAndColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 128, 128)));
font.FontName = ("Arial");
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.GetPArray(0);
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.GetRArray(0).rPr;
Assert.AreEqual("Arial", rPr.latin.typeface);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
checkRewrite(wb);
wb.Close();
}
private CT_Rst buildCTRst(string text, SortedDictionary <int, CT_RPrElt> formats)
{
if (text.Length != this.GetLastKey(formats.Keys))
{
throw new ArgumentException("Text length was " + (object)text.Length + " but the last format index was " + (object)this.GetLastKey(formats.Keys));
}
CT_Rst ctRst = new CT_Rst();
int startIndex = 0;
SortedDictionary <int, CT_RPrElt> .KeyCollection.Enumerator enumerator = formats.Keys.GetEnumerator();
while (enumerator.MoveNext())
{
int current = enumerator.Current;
CT_RElt ctRelt = ctRst.AddNewR();
string str = text.Substring(startIndex, current - startIndex);
ctRelt.t = str;
XSSFRichTextString.PreserveSpaces(ctRelt.t);
CT_RPrElt format = formats[current];
if (format != null)
{
ctRelt.rPr = format;
}
startIndex = current;
}
return(ctRst);
}
public void TestBug57294()
{
IWorkbook wb = SXSSFITestDataProvider.instance.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
IRichTextString str = new XSSFRichTextString("Test rich text string");
str.ApplyFont(2, 4, (short)0);
Assert.AreEqual(3, str.NumFormattingRuns);
cell.SetCellValue(str);
IWorkbook wbBack = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb);
wb.Close();
// re-read after serializing and reading back
ICell cellBack = wbBack.GetSheetAt(0).GetRow(0).GetCell(0);
Assert.IsNotNull(cellBack);
IRichTextString strBack = cellBack.RichStringCellValue;
Assert.IsNotNull(strBack);
Assert.AreEqual(3, strBack.NumFormattingRuns);
Assert.AreEqual(0, strBack.GetIndexOfFormattingRun(0));
Assert.AreEqual(2, strBack.GetIndexOfFormattingRun(1));
Assert.AreEqual(4, strBack.GetIndexOfFormattingRun(2));
wbBack.Close();
}
public void SetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='"+XSSFRelation.NS_SPREADSHEETML+"' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
font1.FontHeight = 8.5;
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='"+XSSFRelation.NS_SPREADSHEETML+"' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestEmpty()
{
XSSFRichTextString rt = new XSSFRichTextString();
Assert.AreEqual(0, rt.GetIndexOfFormattingRun(9999));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(9999));
Assert.IsNull(rt.GetFontAtIndex(9999));
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestToString()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.IsNotNull(rt.ToString());
// TODO: normally ToString() should never return null, should we adjust this?
rt = new XSSFRichTextString();
Assert.IsNull(rt.ToString());
}
public void TestApplyFontIndex()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
rt.ApplyFont(0, 10, (short)1);
rt.ApplyFont((short)1);
Assert.IsNotNull(rt.GetFontAtIndex(0));
}
public void TestUtfDecode()
{
CT_Rst st = new CT_Rst();
st.t = ("abc_x000D_2ef_x000D_");
XSSFRichTextString rt = new XSSFRichTextString(st);
//_x000D_ is Converted into carriage return
Assert.AreEqual("abc\r2ef\r", rt.String);
}
private String ConvertCellValueToString()
{
CellType cellType = CellType;
switch (cellType)
{
case CellType.Blank:
return("");
case CellType.Boolean:
return(TRUE_AS_STRING.Equals(_cell.v) ? "TRUE" : "FALSE");
case CellType.String:
int sstIndex = Int32.Parse(_cell.v);
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.GetEntryAt(sstIndex));
return(rt.String);
case CellType.Numeric:
case CellType.Error:
return(_cell.v);
case CellType.Formula:
// should really Evaluate, but HSSFCell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new InvalidOperationException("Unexpected cell type (" + cellType + ")");
}
cellType = GetBaseCellType(false);
String textValue = _cell.v;
switch (cellType)
{
case CellType.Boolean:
if (TRUE_AS_STRING.Equals(textValue))
{
return("TRUE");
}
if (FALSE_AS_STRING.Equals(textValue))
{
return("FALSE");
}
throw new InvalidOperationException("Unexpected bool cached formula value '"
+ textValue + "'.");
case CellType.String:
case CellType.Numeric:
case CellType.Error:
return(textValue);
}
throw new InvalidOperationException("Unexpected formula result type (" + cellType + ")");
}
/**
* make sure we insert xml:space="preserve" attribute
* if a string has leading or trailing white spaces
*/
public void TestPreserveSpaces()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache");
CT_Rst ct = rt.GetCTRst();
string t = ct.t;
Assert.AreEqual("<t>Apache</t>", ct.XmlText);
rt.String = " Apache";
Assert.AreEqual("<t xml:space=\"preserve\"> Apache</t>", ct.XmlText);
rt.Append(" POI");
rt.Append(" ");
Assert.AreEqual(" Apache POI ", rt.String);
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> Apache</t>", rt.GetCTRst().GetRArray(0).xmlText());
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> POI</xml-fragment>", rt.getCTRst().getRArray(1).xgetT().xmlText());
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> </xml-fragment>", rt.getCTRst().getRArray(2).xgetT().xmlText());
}
public void TestCreate()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.AreEqual("Apache POI", rt.String);
CT_Rst st = rt.GetCTRst();
Assert.IsTrue(st.IsSetT());
Assert.AreEqual("Apache POI", st.t);
rt.Append(" is cool stuff");
Assert.AreEqual(2, st.sizeOfRArray());
Assert.IsFalse(st.IsSetT());
Assert.AreEqual("Apache POI is cool stuff", rt.String);
}
public void TestCreate()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.AreEqual("Apache POI", rt.String);
CT_Rst st = rt.GetCTRst();
Assert.IsTrue(st.IsSetT());
Assert.AreEqual("Apache POI", st.t);
rt.Append(" is cool stuff");
Assert.AreEqual(2, st.sizeOfRArray());
Assert.IsFalse(st.IsSetT());
Assert.AreEqual("Apache POI is cool stuff", rt.String);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth = 3;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
}
public IFont GetFontOfFormattingRun(int index)
{
if (this.st.sizeOfRArray() == 0)
{
return((IFont)null);
}
for (int index1 = 0; index1 < this.st.sizeOfRArray(); ++index1)
{
CT_RElt rarray = this.st.GetRArray(index1);
if (index1 == index)
{
XSSFFont xssfFont = new XSSFFont(XSSFRichTextString.ToCTFont(rarray.rPr));
xssfFont.SetThemesTable(this.GetThemesTable());
return((IFont)xssfFont);
}
}
return((IFont)null);
}
public void Test59008Font()
{
XSSFFont font = new XSSFFont(new CT_Font());
XSSFRichTextString rts = new XSSFRichTextString();
rts.Append("This is correct ");
int s1 = rts.Length;
rts.Append("This is Bold Red", font);
int s2 = rts.Length;
rts.Append(" This uses the default font rather than the cell style font");
int s3 = rts.Length;
Assert.AreEqual("<xml-fragment/>", rts.GetFontAtIndex(s1 - 1).ToString());
Assert.AreEqual(font, rts.GetFontAtIndex(s2 - 1));
Assert.AreEqual("<xml-fragment/>", rts.GetFontAtIndex(s3 - 1).ToString());
}
/**
* Set a single paragraph of text on the shape. Note this will replace all existing paragraphs Created on the shape.
* @param str rich text string representing the paragraph text
*/
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook wb = (XSSFWorkbook)GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(wb.GetStylesSource());
CT_TextParagraph p = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
rPr.sz = (/*setter*/ 1100);
r.t = (/*setter*/ str.String);
}
else
{
for (int i = 0; i < str.GetCTRst().SizeOfRArray(); i++)
{
CT_RElt lt = str.GetCTRst().GetRArray(i);
CT_RPrElt ltPr = lt.rPr;
if (ltPr == null)
{
ltPr = lt.AddNewRPr();
}
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
ApplyAttributes(ltPr, rPr);
r.t = (/*setter*/ lt.t);
}
}
ClearText();
ctShape.txBody.SetPArray(new CT_TextParagraph[] { p });
_paragraphs.Add(new XSSFTextParagraph(ctShape.txBody.GetPArray(0), ctShape));
}
/**
* Add a new paragraph run to this shape, Set to the provided rich text string
*
* @return Created paragraph run
*/
public XSSFTextParagraph AddNewTextParagraph(XSSFRichTextString str)
{
NPOI.OpenXmlFormats.Dml.Spreadsheet.CT_TextBody txBody = ctShape.txBody;
CT_TextParagraph p = txBody.AddNewP();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
rPr.sz = (/*setter*/ 1100);
r.t = (/*setter*/ str.String);
}
else
{
for (int i = 0; i < str.GetCTRst().SizeOfRArray(); i++)
{
CT_RElt lt = str.GetCTRst().GetRArray(i);
CT_RPrElt ltPr = lt.rPr;
if (ltPr == null)
{
ltPr = lt.AddNewRPr();
}
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
ApplyAttributes(ltPr, rPr);
r.t = (/*setter*/ lt.t);
}
}
// Note: the XSSFTextParagraph constructor will create its required XSSFTextRuns from the provided CTTextParagraph
XSSFTextParagraph paragraph = new XSSFTextParagraph(p, ctShape);
_paragraphs.Add(paragraph);
return(paragraph);
}
public void Append(string text, XSSFFont font)
{
if (this.st.sizeOfRArray() == 0 && this.st.IsSetT())
{
CT_RElt ctRelt = this.st.AddNewR();
ctRelt.t = this.st.t;
XSSFRichTextString.PreserveSpaces(ctRelt.t);
this.st.unsetT();
}
CT_RElt ctRelt1 = this.st.AddNewR();
ctRelt1.t = text;
XSSFRichTextString.PreserveSpaces(ctRelt1.t);
CT_RPrElt pr = ctRelt1.AddNewRPr();
if (font == null)
{
return;
}
this.SetRunAttributes(font.GetCTFont(), pr);
}
public short GetFontAtIndex(int index)
{
if (this.st.sizeOfRArray() == 0)
{
return(-1);
}
int num = 0;
for (int index1 = 0; index1 < this.st.sizeOfRArray(); ++index1)
{
CT_RElt rarray = this.st.GetRArray(index1);
if (index >= num && index < num + rarray.t.Length)
{
XSSFFont xssfFont = new XSSFFont(XSSFRichTextString.ToCTFont(rarray.rPr));
xssfFont.SetThemesTable(this.GetThemesTable());
return(xssfFont.Index);
}
num += rarray.t.Length;
}
return(-1);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth=3;
c1.LineStyle= SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill=(true);
}
public void TestBug56511_values()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56511.xlsx");
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(0);
// verify the values to ensure future Changes keep the returned information equal
XSSFRichTextString rt = (XSSFRichTextString)row.GetCell(0).RichStringCellValue;
Assert.AreEqual(0, rt.NumFormattingRuns);
Assert.IsNull(rt.GetFontOfFormattingRun(0));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(0));
rt = (XSSFRichTextString)row.GetCell(1).RichStringCellValue;
Assert.AreEqual(0, row.GetCell(1).RichStringCellValue.NumFormattingRuns);
Assert.IsNull(rt.GetFontOfFormattingRun(1));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(1));
rt = (XSSFRichTextString)row.GetCell(2).RichStringCellValue;
Assert.AreEqual(2, rt.NumFormattingRuns);
Assert.IsNotNull(rt.GetFontOfFormattingRun(0));
Assert.AreEqual(4, rt.GetLengthOfFormattingRun(0));
Assert.IsNotNull(rt.GetFontOfFormattingRun(1));
Assert.AreEqual(9, rt.GetLengthOfFormattingRun(1));
Assert.IsNull(rt.GetFontOfFormattingRun(2));
rt = (XSSFRichTextString)row.GetCell(3).RichStringCellValue;
Assert.AreEqual(3, rt.NumFormattingRuns);
Assert.IsNull(rt.GetFontOfFormattingRun(0));
Assert.AreEqual(1, rt.GetLengthOfFormattingRun(0));
Assert.IsNotNull(rt.GetFontOfFormattingRun(1));
Assert.AreEqual(3, rt.GetLengthOfFormattingRun(1));
Assert.IsNotNull(rt.GetFontOfFormattingRun(2));
Assert.AreEqual(9, rt.GetLengthOfFormattingRun(2));
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("some comments");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new XSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new XSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
IComment comment2 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
//comment2.SetFillColor(204, 236, 255);
XSSFRichTextString str = new XSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = workbook.CreateFont();
font.FontName = ("Arial");
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = HSSFColor.Red.Index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="pperformance">个人绩效内存数据</param>
/// <param name="path">附件所在路径</param>
/// <param name="scores">排序后的分数列表</param>
public static void Create(PersonalPerformance pperformance, string path, List<float> scores)
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
CreateStyle(workbook);
ISheet sheet = workbook.CreateSheet("Sheet1");
//设置列宽
sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256);
sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256);
sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256);
sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256);
sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256);
//创建行和列
//行一
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 30;
ICell cell = row.CreateCell(0);
XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单");
richText.ApplyFont(_normalFont);
cell.CellStyle = _aquaCellStyle;
cell.SetCellValue(richText);
SetCellRangeAddress(sheet, 0, 0, 0, 1);
cell = row.CreateCell(2);
cell.CellStyle = _aquaCellStyle;
cell = row.CreateCell(3);
cell.CellStyle = _aquaCellStyle;
cell.SetCellValue(pperformance.Department);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
//行二
row = sheet.CreateRow(1);
row.HeightInPoints = 90;
cell = row.CreateCell(0);
string g = string.Empty;
float score = pperformance.Person.Score;
if (!string.IsNullOrEmpty(pperformance.Person.Grade))
{
string[] gs = pperformance.Person.Grade.Split('-');
g = gs[0];
}
string str = string.Format(" {0} {1}月绩效等级{2}，绩效分值{3}", pperformance.Person.Name, pperformance.Month, g, score);
cell.CellStyle = _normalLeftCellStyle;
cell.SetCellValue(str);
SetCellRangeAddress(sheet, 1, 1, 0, 4);
//行三
row = sheet.CreateRow(2);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
int index = scores.FindIndex(sc => sc == score) + 1;
float winRate = 0;
if (index != 1)
{
winRate = ((float)index / (float)(scores.Count)) * 100f;
}
str = string.Format(" 你打败了{0}", pperformance.Department);
richText = new XSSFRichTextString(str);
richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont);
richText.Append("的成员", _normalFont as XSSFFont);
cell.SetCellValue(richText);
cell.CellStyle = _aquaCellStyle;
SetCellRangeAddress(sheet, 2, 2, 0, 4);
//行四
row = sheet.CreateRow(3);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
cell.SetCellValue("序号");
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("任务名称");
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(2);
SetCellRangeAddress(sheet, 3, 3, 1, 2);
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("任务得分");
SetCellRangeAddress(sheet, 3, 3, 3, 4);
cell.CellStyle = _greycellStyle;
//添加任务
int i = 1;
int rowIndex = 4;
foreach (var task in pperformance.Tasks)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
cell.SetCellValue(i);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(1);
cell.SetCellValue(task.Name);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(2);
SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(3);
cell.SetCellValue(task.Score);
SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
i++;
rowIndex++;
}
//简评
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
cell.SetCellValue(" 简评");
cell.CellStyle = _aquaCellStyle;
SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4);
row = sheet.CreateRow(++rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
str = string.Format(" {0}月工作包数量{1}，平均分值{2}", pperformance.Month, pperformance.Person.WorkBagNum, pperformance.Person.AvgScore);
cell.SetCellValue(str);
cell.CellStyle = _normalLeftCellStyle;
SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4);
row = sheet.CreateRow(++rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
str = string.Format(" {0}月加减分值{1}，加减分值说明：{2}", pperformance.Month, pperformance.Person.AddSubScore, pperformance.Person.AddSubExplain);
cell.SetCellValue(str);
cell.CellStyle = _normalLeftCellStyle;
SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4);
row = sheet.CreateRow(++rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
str = string.Format("领导评语：{0}", pperformance.Person.LeadComment);
cell.SetCellValue(str);
cell.CellStyle = _normalLeftCellStyle;
SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4);
row = sheet.CreateRow(++rowIndex);
row.HeightInPoints = 40;
cell = row.CreateCell(0);
str = "说明：绩效分值参考工作包平均分值采取10分制：A级10分，B级8分；C级5分；D级0分；加减分项在此基础上执行。";
cell.SetCellValue(str);
cell.CellStyle = _justifyLeftCellStyle;
SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4);
using (FileStream stream = File.OpenWrite(path))
{
workbook.Write(stream);
stream.Close();
}
}
catch (Exception)
{
throw new Exception("创建Excel出错！");
}
}
public void Test47278()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.CreateWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
IRow row = sheet.CreateRow(0);
SharedStringsTable sst = wb.GetSharedStringSource();
Assert.AreEqual(0, sst.Count);
//case 1. cell.SetCellValue(new XSSFRichTextString((String)null));
ICell cell_0 = row.CreateCell(0);
XSSFRichTextString str = new XSSFRichTextString((String)null);
Assert.IsNull(str.String);
cell_0.SetCellValue(str);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_0.CellType);
//case 2. cell.SetCellValue((String)null);
ICell cell_1 = row.CreateCell(1);
cell_1.SetCellValue((String)null);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_1.CellType);
}
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook wb = (XSSFWorkbook)GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(wb.GetStylesSource());
CT_TextParagraph p = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = ("en-US");
rPr.sz = (1100);
r.t = str.String;
}
else
{
for (int i = 0; i < str.GetCTRst().sizeOfRArray(); i++)
{
CT_RElt lt = str.GetCTRst().GetRArray(i);
CT_RPrElt ltPr = lt.rPr;
if (ltPr == null) ltPr = lt.AddNewRPr();
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = ("en-US");
ApplyAttributes(ltPr, rPr);
r.t = (lt.t);
}
}
ctShape.txBody.SetPArray(new CT_TextParagraph[] { p });
}
public void TestRichText()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 128, 128)));
font.IsItalic = (true);
font.IsBold=(true);
font.Underline=FontUnderlineType.Single;
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.p[0];
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.r[0].rPr;
Assert.AreEqual(true, rPr.b);
Assert.AreEqual(true, rPr.i);
Assert.AreEqual(ST_TextUnderlineType.sng, rPr.u);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
}
public void TestApplyFont_usermodel()
{
String text = "Apache Software Foundation";
XSSFRichTextString str = new XSSFRichTextString(text);
XSSFFont font1 = new XSSFFont();
XSSFFont font2 = new XSSFFont();
XSSFFont font3 = new XSSFFont();
str.ApplyFont(font1);
Assert.AreEqual(1, str.NumFormattingRuns);
str.ApplyFont(0, 6, font1);
str.ApplyFont(6, text.Length, font2);
Assert.AreEqual(2, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software Foundation", str.GetCTRst().GetRArray(1).t);
str.ApplyFont(15, 26, font3);
Assert.AreEqual(3, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software", str.GetCTRst().GetRArray(1).t);
Assert.AreEqual(" Foundation", str.GetCTRst().GetRArray(2).t);
str.ApplyFont(6, text.Length, font2);
Assert.AreEqual(2, str.NumFormattingRuns);
Assert.AreEqual("Apache", str.GetCTRst().GetRArray(0).t);
Assert.AreEqual(" Software Foundation", str.GetCTRst().GetRArray(1).t);
}
public void TestSetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
//font1.FontHeight = (short)8.5;
font1.SetFontHeight(8.5);
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/"Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].IsBullet = (false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
//Assert.AreEqual("null", shape.Text);
Assert.AreEqual(String.Empty, shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
//Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual("test-other-text\nrtstring\n", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
private String ConvertCellValueToString()
{
CellType cellType = CellType;
switch (cellType)
{
case CellType.Blank:
return "";
case CellType.Boolean:
return TRUE_AS_STRING.Equals(_cell.v) ? "TRUE" : "FALSE";
case CellType.String:
int sstIndex = Int32.Parse(_cell.v);
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.GetEntryAt(sstIndex));
return rt.String;
case CellType.Numeric:
case CellType.Error:
return _cell.v;
case CellType.Formula:
// should really Evaluate, but HSSFCell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new InvalidOperationException("Unexpected cell type (" + cellType + ")");
}
cellType = GetBaseCellType(false);
String textValue = _cell.v;
switch (cellType)
{
case CellType.Boolean:
if (TRUE_AS_STRING.Equals(textValue))
{
return "TRUE";
}
if (FALSE_AS_STRING.Equals(textValue))
{
return "FALSE";
}
throw new InvalidOperationException("Unexpected bool cached formula value '"
+ textValue + "'.");
case CellType.String:
case CellType.Numeric:
case CellType.Error:
return textValue;
}
throw new InvalidOperationException("Unexpected formula result type (" + cellType + ")");
}
/// <summary>
/// Set the cells type (numeric, formula or string)
/// </summary>
/// <param name="cellType"></param>
public void SetCellType(CellType cellType)
{
CellType prevType = CellType;
if (IsPartOfArrayFormulaGroup)
{
NotifyArrayFormulaChanging();
}
if (prevType == CellType.Formula && cellType != CellType.Formula)
{
((XSSFWorkbook)Sheet.Workbook).OnDeleteFormula(this);
}
switch (cellType)
{
case CellType.Blank:
SetBlank();
break;
case CellType.Boolean:
String newVal = ConvertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING;
_cell.t= (ST_CellType.b);
_cell.v= (newVal);
break;
case CellType.Numeric:
_cell.t = (ST_CellType.n);
break;
case CellType.Error:
_cell.t = (ST_CellType.e);
break;
case CellType.String:
if (prevType != CellType.String)
{
String str = ConvertCellValueToString();
XSSFRichTextString rt = new XSSFRichTextString(str);
rt.SetStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
_cell.v= sRef.ToString();
}
_cell.t= (ST_CellType.s);
break;
case CellType.Formula:
if (!_cell.IsSetF())
{
CT_CellFormula f = new CT_CellFormula();
f.Value = "0";
_cell.f = (f);
if (_cell.IsSetT()) _cell.unsetT();
}
break;
default:
throw new ArgumentException("Illegal cell type: " + cellType);
}
if (cellType != CellType.Formula && _cell.IsSetF())
{
_cell.unsetF();
}
}
/**
* Chooses a new bool value for the cell when its type is changing.<p/>
*
* Usually the caller is calling SetCellType() with the intention of calling
* SetCellValue(bool) straight afterwards. This method only exists to give
* the cell a somewhat reasonable value until the SetCellValue() call (if at all).
* TODO - perhaps a method like SetCellTypeAndValue(int, Object) should be introduced to avoid this
*/
private bool ConvertCellValueToBoolean()
{
CellType cellType = CellType;
if (cellType == CellType.Formula)
{
cellType = GetBaseCellType(false);
}
switch (cellType)
{
case CellType.Boolean:
return TRUE_AS_STRING.Equals(_cell.v);
case CellType.String:
int sstIndex = Int32.Parse(_cell.v);
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.GetEntryAt(sstIndex));
String text = rt.String;
return Boolean.Parse(text);
case CellType.Numeric:
return Double.Parse(_cell.v, CultureInfo.InvariantCulture) != 0;
case CellType.Error:
case CellType.Blank:
return false;
}
throw new RuntimeException("Unexpected cell type (" + cellType + ")");
}
/**
* make sure we insert xml:space="preserve" attribute
* if a string has leading or trailing white spaces
*/
public void TestPreserveSpaces()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache");
CT_Rst ct = rt.GetCTRst();
string t=ct.t;
Assert.AreEqual("<t>Apache</t>", ct.XmlText);
rt.String = " Apache";
Assert.AreEqual("<t xml:space=\"preserve\"> Apache</t>", ct.XmlText);
rt.Append(" POI");
rt.Append(" ");
Assert.AreEqual(" Apache POI ", rt.String);
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> Apache</t>", rt.GetCTRst().GetRArray(0).xmlText());
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> POI</xml-fragment>", rt.getCTRst().getRArray(1).xgetT().xmlText());
//Assert.AreEqual("<xml-fragment xml:space=\"preserve\"> </xml-fragment>", rt.getCTRst().getRArray(2).xgetT().xmlText());
}
public void XSSFTextParagraph_()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/"Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List<XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet);
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (/*setter*/TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (/*setter*/TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (/*setter*/TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (/*setter*/TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = (/*setter*/"Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (/*setter*/".");
Assert.AreEqual(".", text.BulletCharacter);
//Assert.IsNull(text.BulletFontColor);
Assert.AreEqual(Color.Empty, text.BulletFontColor);
text.BulletFontColor = (/*setter*/color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/-2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/-4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/-8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (/*setter*/1);
Assert.AreEqual(1, text.Level);
text.Level = (/*setter*/4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (false);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (true);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
//Assert.AreEqual(0, text.BulletAutoNumberStart);
//This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
Assert.AreEqual(1, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public void TestGetFonts()
{
XSSFRichTextString rt = new XSSFRichTextString();
XSSFFont font1 = new XSSFFont();
font1.FontName = ("Arial");
font1.IsItalic = (true);
rt.Append("The quick", font1);
XSSFFont font1FR = (XSSFFont)rt.GetFontOfFormattingRun(0);
Assert.AreEqual(font1.IsItalic, font1FR.IsItalic);
Assert.AreEqual(font1.FontName, font1FR.FontName);
XSSFFont font2 = new XSSFFont();
font2.FontName = ("Courier");
font2.IsBold = (true);
rt.Append(" brown fox", font2);
XSSFFont font2FR = (XSSFFont)rt.GetFontOfFormattingRun(1);
Assert.AreEqual(font2.IsBold, font2FR.IsBold);
Assert.AreEqual(font2.FontName, font2FR.FontName);
}
public void TestApplyFontIndex()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
rt.ApplyFont(0, 10, (short)1);
rt.ApplyFont((short)1);
Assert.IsNotNull(rt.GetFontAtIndex(0));
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List<XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
}
/**
* Creates a new XSSFRichTextString for you.
*/
public IRichTextString CreateRichTextString(String text)
{
XSSFRichTextString rt = new XSSFRichTextString(text);
rt.SetStylesTableReference(workbook.GetStylesSource());
return rt;
}
public void TestToString()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.IsNotNull(rt.ToString());
// TODO: normally ToString() should never return null, should we adjust this?
rt = new XSSFRichTextString();
Assert.IsNull(rt.ToString());
}
public void TestUtfDecode()
{
CT_Rst st = new CT_Rst();
st.t = ("abc_x000D_2ef_x000D_");
XSSFRichTextString rt = new XSSFRichTextString(st);
//_x000D_ is Converted into carriage return
Assert.AreEqual("abc\r2ef\r", rt.String);
}
public void TestApplyFontException()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
rt.ApplyFont(0, 0, (short)1);
try
{
rt.ApplyFont(11, 10, (short)1);
Assert.Fail("Should catch Exception here");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("11"));
}
try
{
rt.ApplyFont(-1, 10, (short)1);
Assert.Fail("Should catch Exception here");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("-1"));
}
try
{
rt.ApplyFont(0, 555, (short)1);
Assert.Fail("Should catch Exception here");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("555"));
}
}
/// <summary>
/// Creates an excel comment in each cell with an associated error
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="sheet"></param>
private void HighlightErrors(ISheet excelSheet, ICOBieSheet<COBieRow> sheet)
{
//sort by row then column
var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);
// The patriarch is a container for comments on a sheet
IDrawing patr = excelSheet.CreateDrawingPatriarch();
int sheetCommnetCount = 0;
foreach (var error in errors)
{
if (error.Row > 0 && error.Column >= 0)
{
if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
//limit comments to 1000 per sheet
if (sheetCommnetCount == 999)
break;
IRow excelRow = excelSheet.GetRow(error.Row);
if (excelRow != null)
{
ICell excelCell = excelRow.GetCell(error.Column);
if (excelCell != null)
{
string description = error.ErrorDescription;
if(hasErrorLevel)
{
if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
description = "Warning: " + description;
else
description = "Error: " + description;
}
if (excelCell.CellComment == null)
{
try
{
// A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
// Create a comment 3 columns wide and 3 rows height
IClientAnchor anchor = null;
if (IsXlsx)
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
else
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
IComment comment = patr.CreateCellComment(anchor);
IRichTextString str = null;
if (IsXlsx)
{
str = new XSSFRichTextString(description);
}
else
{
str = new HSSFRichTextString(description);
}
comment.String = str;
comment.Author = "XBim";
excelCell.CellComment = comment;
_commentCount++;
sheetCommnetCount++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
if (IsXlsx)
{
((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
}
else
{
description = excelCell.CellComment.String.ToString() + " Also " + description;
excelCell.CellComment.String = new HSSFRichTextString(description);
}
}
}
}
}
}
}
public void TestApplyFontWithStyles()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
StylesTable tbl = new StylesTable();
rt.SetStylesTableReference(tbl);
try
{
rt.ApplyFont(0, 10, (short)1);
Assert.Fail("Fails without styles in the table");
}
catch (ArgumentOutOfRangeException e)
{
// expected
}
tbl.PutFont(new XSSFFont());
rt.ApplyFont(0, 10, (short)1);
rt.ApplyFont((short)1);
}
public void TestClearFormatting()
{
XSSFRichTextString rt = new XSSFRichTextString("Apache POI");
Assert.AreEqual("Apache POI", rt.String);
rt.ClearFormatting();
CT_Rst st = rt.GetCTRst();
Assert.IsTrue(st.IsSetT());
Assert.AreEqual("Apache POI", rt.String);
Assert.AreEqual(0, rt.NumFormattingRuns);
XSSFFont font = new XSSFFont();
font.IsBold = true;
rt.ApplyFont(7, 10, font);
Assert.AreEqual(2, rt.NumFormattingRuns);
rt.ClearFormatting();
Assert.AreEqual("Apache POI", rt.String);
Assert.AreEqual(0, rt.NumFormattingRuns);
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="pperformance">个人绩效内存数据</param>
/// <param name="path">附件所在路径</param>
/// <param name="scores">排序后的分数列表</param>
public static void Create(PersonalPerformance pperformance, string path, List<int> scores)
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
CreateStyle(workbook);
ISheet sheet = workbook.CreateSheet("Sheet1");
//设置列宽
sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256);
sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256);
sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256);
sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256);
sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256);
//创建行和列
//行一
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 30;
ICell cell = row.CreateCell(0);
XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单");
richText.ApplyFont(_normalFont);
cell.CellStyle = _aquaCellStyle;
cell.SetCellValue(richText);
SetCellRangeAddress(sheet, 0, 0, 0, 1);
cell = row.CreateCell(2);
cell.CellStyle = _aquaCellStyle;
cell = row.CreateCell(3);
cell.CellStyle = _aquaCellStyle;
cell.SetCellValue(pperformance.Department);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
//行二
row = sheet.CreateRow(1);
row.HeightInPoints = 90;
cell = row.CreateCell(0);
int totalScore = 0;
pperformance.Tasks.ForEach(task => totalScore += task.Score);
string value = string.Format(" {0} {1}月得分{2}", pperformance.Person.Name, pperformance.Month, totalScore);
int startIndex = value.IndexOf('分') + 1;
int endIndex = value.Length;
richText = new XSSFRichTextString(value);
richText.ApplyFont(startIndex, endIndex, _scoreFont);
cell.CellStyle = _normalLeftCellStyle;
cell.SetCellValue(richText);
SetCellRangeAddress(sheet, 1, 1, 0, 4);
//行三
row = sheet.CreateRow(2);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
int index = scores.FindIndex(s => s == totalScore);
float winRate = ((float)index / (float)scores.Count) * 100f;
//value = string.Format(" 你打败了{0}{1}%的成员", pperformance.Department, winRate.ToString("f0"));
string str1 = string.Format(" 你打败了{0}", pperformance.Department);
richText = new XSSFRichTextString(str1);
richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont);
richText.Append("的成员", _normalFont as XSSFFont);
cell.SetCellValue(richText);
cell.CellStyle = _aquaCellStyle;
SetCellRangeAddress(sheet, 2, 2, 0, 4);
//行四
row = sheet.CreateRow(3);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
cell.SetCellValue("序号");
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("任务名称");
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(2);
SetCellRangeAddress(sheet, 3, 3, 1, 2);
cell.CellStyle = _greycellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("任务得分");
SetCellRangeAddress(sheet, 3, 3, 3, 4);
cell.CellStyle = _greycellStyle;
//添加任务
int i = 1;
int rowIndex = 4;
foreach (var task in pperformance.Tasks)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = 30;
cell = row.CreateCell(0);
cell.SetCellValue(i);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(1);
cell.SetCellValue(task.Name);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(2);
SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
cell = row.CreateCell(3);
cell.SetCellValue(task.Score);
SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4);
if (i % 2 == 0)
{
cell.CellStyle = _greycellStyle;
}
else
{
cell.CellStyle = _normalCenterCellStyle;
}
i++;
rowIndex++;
}
using (FileStream stream = File.OpenWrite(path))
{
workbook.Write(stream);
stream.Close();
}
}
catch (Exception)
{
throw new Exception("创建Excel出错！");
}
}