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 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);
}
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);
}
/**
* 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 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());
}
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 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);
}
/// <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出错！");
}
}
/// <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出错！");
}
}
/**
* 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());
}