public void Test40520_3()
{
short font = 3;
HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");
// wrong order => will format 0-6
r.ApplyFont(0, 2, font);
r.ApplyFont(5, 7, font);
r.ApplyFont(0, 2, font);
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < 5; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
for (int i = 5; i < 7; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 7; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
}
public static void createConditionH(HSSFSheet sheet, int row, int lscol, string condition, HSSFCellStyle style, int height)
{
HSSFRow hsshRow = sheet.CreateRow(row) as HSSFRow;
hsshRow.Height = (short)(height * 256);
for (int i = 0; i < lscol; i++)
{
HSSFCell cell = hsshRow.CreateCell(i) as HSSFCell;
cell.CellStyle = style;
if (i == 0)
{
HSSFRichTextString richString;
if (condition == null)
{
richString = new HSSFRichTextString("");
}
else
{
richString = new HSSFRichTextString(condition);
}
cell.SetCellValue(richString);
}
else
{
cell.SetCellValue("");
}
}
ExcelUtilsForNPOI.SetCellRangeAddress(sheet, row, row, 0, lscol);
}
public void Test40520_2()
{
short font = 3;
HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
}
/// <summary>
/// 对单元格赋值，设置时间下划线
/// </summary>
/// <param name="workbook">需要添加下划线的工作簿</param>
/// <param name="row">单元格行号</param>
/// <param name="row">单元格列号</param>
/// <param name="cell"></param>
/// <param name="vaule"></param>
/// <param name="startTime">起始日期</param>
/// <param name="endTime">截止日期</param>
public static void SetDateTimeUnderline(this IWorkbook workbook, int row, int cell, string vaule, DateTime startTime, DateTime endTime)
{
string startYear = startTime.Year.ToString();
string startMonth = startTime.Month.ToString();
string startDay = startTime.Day.ToString();
string endYear = endTime.Year.ToString();
string endMonth = endTime.Month.ToString();
string endDay = endTime.Day.ToString();
string strValue = $"{startYear}年{startMonth}月{startDay}日至{endYear}年{endMonth}月{endDay}日";
HSSFRichTextString richtext = new HSSFRichTextString(strValue);
ApplyFont(workbook, richtext, 11);
var font2 = SetUnderline(workbook, richtext, 11, 0, 4);
int monthEndIndex = 5 + startMonth.Length;
richtext.ApplyFont(5, monthEndIndex, font2);
richtext.ApplyFont(monthEndIndex + 1, monthEndIndex + 1 + startDay.Length, font2);
richtext.ApplyFont(monthEndIndex + 1 + startDay.Length + 2, monthEndIndex + 1 + startDay.Length + 6, font2);
int monthEndIndex2 = monthEndIndex + 1 + startDay.Length + 7;
richtext.ApplyFont(monthEndIndex2, monthEndIndex2 + endMonth.Length, font2);
richtext.ApplyFont(monthEndIndex2 + endMonth.Length + 1, monthEndIndex2 + endMonth.Length + 1 + endDay.Length, font2);
ISheet sheet = workbook.GetSheetAt(0);
sheet.GetRow(row).GetCell(cell).SetCellValue(richtext);
}
public void TestLongRecords()
{
int[] length = { 1024, 2048, 4096, 8192, 16384 }; //test against strings of different length
for (int i = 0; i < length.Length; i++)
{
StringBuilder buff = new StringBuilder(length[i]);
for (int j = 0; j < length[i]; j++)
{
buff.Append("x");
}
NPOI.SS.UserModel.RichTextString str = new HSSFRichTextString(buff.ToString());
TextObjectRecord obj = new TextObjectRecord();
obj.Str = (str);
byte[] data = obj.Serialize();
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
TextObjectRecord record = new TextObjectRecord(is1);
str = record.Str;
Assert.AreEqual(buff.Length, str.Length);
Assert.AreEqual(buff.ToString(), str.String);
}
}
public void SetText(CellPos pos, string text)
{
List <TextPosition> positions = new List <TextPosition>();
while (text.IndexOf("<b>") > -1)
{
int start = text.IndexOf("<b>");
text = text.Substring(0, start) + text.Substring(start + 3);
int end = text.IndexOf("</b>");
text = text.Substring(0, end) + text.Substring(end + 4);
positions.Add(new TextPosition {
start = start, end = end
});
}
HSSFRichTextString richtext = new HSSFRichTextString(text);
IFont font1 = _parent.Internal.CreateFont();
font1.Boldweight = (short)FontBoldWeight.Bold;
foreach (TextPosition position in positions)
{
richtext.ApplyFont(position.start, position.end, font1);
}
ICell cell = GetCell(pos, true);
cell.SetCellValue(richtext);
}
private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
{
// Create the Drawing patriarch. This is the top level container for
// all shapes. This will clear out any existing shapes for that sheet.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();
// Create a couple of textboxes
HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));
textbox1.String = new HSSFRichTextString("This is a test");
HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));
textbox2.String = new HSSFRichTextString("Woo");
textbox2.SetFillColor(200, 0, 0);
textbox2.LineStyle = LineStyle.DotGel;
// Create third one with some fancy font styling.
HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
IFont font = wb.CreateFont();
font.IsItalic = true;
font.Underline = (byte)FontUnderlineType.DOUBLE;
HSSFRichTextString str = new HSSFRichTextString("Woo!!!");
str.ApplyFont(2, 5, font);
textbox3.String = str;
textbox3.FillColor = 0x08000030;
textbox3.LineStyle = LineStyle.None; // no line around the textbox.
textbox3.IsNoFill = true; // make it transparent
}
public void setTitle(DateTime date)
{
string title = string.Format(" 南宁 分公司(办事处) {0} 年 {1} 月 ", date.Year, date.Month);
HSSFRichTextString Richtitle = new HSSFRichTextString(title);
IFont font = hssfworkbook.CreateFont();
font.Underline = FontUnderlineType.Single;
font.FontHeightInPoints = 16;
font.FontName = "黑体";
font.IsBold = true;
IFont font1 = hssfworkbook.CreateFont();
font1.FontHeightInPoints = 16;
font1.FontName = "黑体";
font1.IsBold = true;
Richtitle.ApplyFont(0, 6, font);
Richtitle.ApplyFont(6, 15, font1);
Richtitle.ApplyFont(15, 22, font);
Richtitle.ApplyFont(22, 24, font1);
Richtitle.ApplyFont(24, 28, font);
Richtitle.ApplyFont(28, 30, font1);
ICell cell = hssfworkbook.GetSheet("Sheet1").GetRow(1).GetCell(1);
cell.SetCellValue(Richtitle);
}
/// <summary>
/// 对单元格赋值，设置字符串的下划线
/// </summary>
/// <param name="sheet">需要添加下划线的工作表</param>
/// <param name="row">单元格行号</param>
/// <param name="cell">单元格列号</param>
/// <param name="vaule">需要单元格所需传入的值</param>
/// <param name="startIndex">需要添加下划线的起始位置</param>
/// <param name="endIndex">需要添加下划线的末尾位置</param>
public static void SetUnderline(this ISheet sheet, int row, int cell, string vaule, int startIndex, int endIndex)
{
HSSFRichTextString richtext = new HSSFRichTextString(vaule);
ApplyFont(sheet, richtext, 11);
SetUnderline(sheet, richtext, 11, startIndex, endIndex);
sheet.GetRow(row).GetCell(cell).SetCellValue(richtext);
}
private Dictionary <short, List <string> > AnalysisCellString(HSSFCell cell)
{
Dictionary <short, List <string> > _cellList = new Dictionary <short, List <string> >();
HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns == 0)
{
return(_cellList);
}
IFont font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
string st2 = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));
if (_cellList.Keys.Where(t => t == font2.Color).Count() == 0)
{
_cellList.Add(font2.Color, new List <string>()
{
st2
});
}
else
{
_cellList[font2.Color].Add(st2);
}
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length;
if (i == formattingRuns - 1)
{
length = rich.Length - startIdx;
}
else
{
length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
}
string st = rich.String.Substring(startIdx, length);
short fontIndex = rich.GetFontOfFormattingRun(i);
IFont font = _workBook.GetFontAt(fontIndex);
if (_cellList.Keys.Where(t => t == font.Color).Count() == 0)
{
_cellList.Add(font.Color, new List <string>()
{
st
});
}
else
{
_cellList[font.Color].Add(st);
}
}
return(_cellList);
}
public void TestClearFormatting()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
r.ClearFormatting();
Assert.AreEqual(0, r.NumFormattingRuns);
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.OliveGreen.Index;
font2.IsStrikeout=true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
cell1.CellStyle = style1;
ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好！");
cell2.CellStyle = style2;
//cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = hssfworkbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3=hssfworkbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = HSSFColor.Blue.Index;
font3.FontHeightInPoints=8;
richtext.ApplyFont(16, 18,font3);
cell3.SetCellValue(richtext);
WriteToFile();
}
private static void CreateOneRowTwoColumnCellsWithColumn0FontColor(ISheet sheet, ref int rowIndex, string column0_text, string column1_text, IFont font)
{
IRow row = sheet.CreateRow(rowIndex);
ICell cell = row.CreateCell(0);
IRichTextString richString = new HSSFRichTextString(column0_text);
richString.ApplyFont(font);
cell.SetCellValue(richString);
cell = row.CreateCell(1);
cell.SetCellValue(column1_text);
rowIndex++;
}
/// <summary>
/// 设置单元格的字符串。
/// </summary>
/// <param name="cell">单元格。</param>
/// <param name="str">字符串。</param>
private static void SetCellString(ICell cell, string str)
{
//判断是否有换行
if (!string.IsNullOrEmpty(str) && str.IndexOf('\n') != -1)
{
var rich = new HSSFRichTextString(str);
cell.CellStyle.WrapText = true;
cell.SetCellValue(rich);
}
else
{
cell.SetCellValue(str);
}
}
public void TestAlignment()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet();
HSSFPatriarch patriarch = sh1.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 6, 4)) as HSSFTextbox;
HSSFRichTextString str = new HSSFRichTextString("Hello, World");
textbox.String = (str);
textbox.HorizontalAlignment = HorizontalAlignment.Center;
textbox.VerticalAlignment = VerticalAlignment.Center;
Assert.AreEqual(HorizontalAlignment.Center, textbox.HorizontalAlignment);
Assert.AreEqual(VerticalAlignment.Center, textbox.VerticalAlignment);
}
public void TestAlignment()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sh1 = wb.CreateSheet();
Drawing patriarch = sh1.CreateDrawingPatriarch();
Textbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 6, 4));
HSSFRichTextString str = new HSSFRichTextString("Hello, World");
textbox.String = (str);
textbox.HorizontalAlignment = (HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED);
textbox.VerticalAlignment = (HSSFTextbox.VERTICAL_ALIGNMENT_CENTER);
Assert.AreEqual(HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED, textbox.HorizontalAlignment);
Assert.AreEqual(HSSFTextbox.VERTICAL_ALIGNMENT_CENTER, textbox.VerticalAlignment);
}
private void AnalysisCellString(HSSFCell cell)
{
string st3 = string.Empty;
HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns == 0)
{
return;
}
IFont font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
string st2 = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));
if (font2.Color != IndexedColors.White.Index && font2.Color != 0)
{
st3 += st2;
}
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length;
if (i == formattingRuns - 1)
{
length = rich.Length - startIdx;
}
else
{
length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
}
string st = rich.String.Substring(startIdx, length);
short fontIndex = rich.GetFontOfFormattingRun(i);
IFont font = _workBook.GetFontAt(fontIndex);
if (font.Color != IndexedColors.White.Index && font.Color != 0)
{
font2 = font;
st3 += st;
}
}
HSSFRichTextString rich2 = new HSSFRichTextString(st3);
rich2.ApplyFont(font2);
cell.SetCellValue(rich2);
}
public void TestSetDeafultTextFormat()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox1 = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 3)) as HSSFTextbox;
HSSFRichTextString rt1 = new HSSFRichTextString("Hello, World!");
Assert.AreEqual(0, rt1.NumFormattingRuns);
textbox1.String = (rt1);
HSSFRichTextString rt2 = (HSSFRichTextString)textbox1.String;
Assert.AreEqual(1, rt2.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, rt2.GetFontOfFormattingRun(0));
}
public void TestApplyFont()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(6));
r.ApplyFont(6, 7, new HSSFFont((short)2, null));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(2, r.GetFontAtIndex(6));
r.ApplyFont(HSSFRichTextString.NO_FONT);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(2));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
r.ApplyFont(new HSSFFont((short)1, null));
Assert.AreEqual(1, r.GetFontAtIndex(0));
Assert.AreEqual(1, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(1, r.GetFontAtIndex(4));
Assert.AreEqual(1, r.GetFontAtIndex(5));
Assert.AreEqual(1, r.GetFontAtIndex(6));
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = HorizontalAlignment.Center;
record.VerticalTextAlignment = VerticalAlignment.Justify;
record.IsTextLocked = (true);
record.TextOrientation = TextOrientation.RotRight;
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
}
public static void createTitle(HSSFSheet sheet, int row, List <string> titles, HSSFCellStyle style)
{
HSSFRow hsshRow = sheet.CreateRow(row) as HSSFRow;
hsshRow.Height = 500;
for (int i = 0; i < titles.Count; i++)
{
HSSFCell cell = hsshRow.CreateCell(i) as HSSFCell;
cell.CellStyle = style;
String textValue = "";
if (titles[i] != null)
{
textValue = titles[i].ToString();
}
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.SetCellValue(richString);
}
freezePane(sheet, 1, row + 1);
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_CENTERED);
record.VerticalTextAlignment = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_JUSTIFY);
record.IsTextLocked = (true);
record.TextOrientation = (TextObjectRecord.TEXT_ORIENTATION_ROT_RIGHT);
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
}
public void TestClone()
{
String text = "Hello, World";
HSSFRichTextString str = new HSSFRichTextString(text);
TextObjectRecord obj = new TextObjectRecord();
obj.Str = (str);
TextObjectRecord cloned = (TextObjectRecord)obj.Clone();
Assert.AreEqual(obj.RecordSize, cloned.RecordSize);
Assert.AreEqual(obj.HorizontalTextAlignment, cloned.HorizontalTextAlignment);
Assert.AreEqual(obj.Str.String, cloned.Str.String);
//finally check that the serialized data is the same
byte[] src = obj.Serialize();
byte[] cln = cloned.Serialize();
Assert.IsTrue(NPOI.Util.Arrays.Equals(src, cln));
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/ str);
record.HorizontalTextAlignment = (/*setter*/ HorizontalTextAlignment.Left);
record.VerticalTextAlignment = (/*setter*/ VerticalTextAlignment.Top);
record.IsTextLocked = (/*setter*/ true);
record.TextOrientation = (/*setter*/ TextOrientation.None);
byte[] ser = record.Serialize();
Assert.AreEqual(ser.Length, simpleData.Length);
Assert.IsTrue(Arrays.Equals(simpleData, ser));
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(simpleData);
record = new TextObjectRecord(is1);
}
public void TestWriteEmpty()
{
HSSFRichTextString str = new HSSFRichTextString("");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/ str);
byte[] ser = record.Serialize();
int formatDataLen = LittleEndian.GetUShort(ser, 16);
Assert.AreEqual(0, formatDataLen, "formatDataLength");
Assert.AreEqual(22, ser.Length); // just the TXO record
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(ser);
record = new TextObjectRecord(is1);
Assert.AreEqual(0, record.Str.Length);
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/ str);
record.HorizontalTextAlignment = (/*setter*/ TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_LEFT_ALIGNED);
record.VerticalTextAlignment = (/*setter*/ TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_TOP);
record.IsTextLocked = (/*setter*/ true);
record.TextOrientation = (/*setter*/ TextObjectRecord.TEXT_ORIENTATION_NONE);
byte[] ser = record.Serialize();
Assert.AreEqual(ser.Length, simpleData.Length);
Assert.IsTrue(Arrays.Equals(simpleData, ser));
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(simpleData);
record = new TextObjectRecord(is1);
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (str);
record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_LEFT_ALIGNED);
record.VerticalTextAlignment = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_TOP);
record.IsTextLocked = (true);
record.TextOrientation = (TextObjectRecord.TEXT_ORIENTATION_NONE);
byte[] ser = record.Serialize();
//Assert.AreEqual(ser.Length , data.Length);
//Assert.IsTrue(Arrays.Equals(data, ser));
//Read again
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
record = new TextObjectRecord(is1);
}
public void TestWriteEmpty()
{
HSSFRichTextString str = new HSSFRichTextString("");
TextObjectRecord record = new TextObjectRecord();
record.Str = (str);
byte[] ser = record.Serialize();
int formatDataLen = NPOI.Util.LittleEndian.GetUShort(ser, 16);
Assert.AreEqual(0, formatDataLen, "formatDataLength");
Assert.AreEqual(22, ser.Length); // just the TXO record
//read again
RecordInputStream is1 = new RecordInputStream(new MemoryStream(ser));
is1.NextRecord();
record = new TextObjectRecord(is1);
Assert.AreEqual(0, record.Str.Length);
}
/// <summary>
/// An Arrow bug from 2007+ version
/// </summary>
/// <param name="cell"></param>
/// <param name="comment"></param>
/// <param name="author"></param>
public void AddCellComment(ICell cell, string comment, string author = "")
{
var patriach = cell.Sheet.CreateDrawingPatriarch();
IComment ccm;
IRichTextString text;
if (IsNewFormat)
{
var anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 4, cell.ColumnIndex + 4);
ccm = patriach.CreateCellComment(anchor);
text = new XSSFRichTextString(comment);
}
else
{
var anchor = new HSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 4, cell.ColumnIndex + 4);
ccm = patriach.CreateCellComment(anchor);
text = new HSSFRichTextString(comment);
}
ccm.String = text;
ccm.Author = author;
ccm.Visible = false;
cell.CellComment = ccm;
}
private static void D05(Project project, IWorkbook book)
{
IFont fUB14 = book.CreateFont();
fUB14.FontName = "黑体";
fUB14.FontHeightInPoints = 14;
fUB14.Underline = FontUnderlineType.Single;
IFont fB14 = book.CreateFont();
fB14.FontName = "黑体";
fB14.FontHeightInPoints = 14;
IRichTextString title;
int underLineLength = project.Name.Length;
if (book is HSSFWorkbook)
{
title = new HSSFRichTextString($"{project.Name}公路");
}
else
{
title = new XSSFRichTextString($"{project.Name}公路");
}
title.ApplyFont(fB14);
title.ApplyFont(0, underLineLength, fUB14);
for (int i = 1; i < book.NumberOfSheets; i++)
{
ISheet sheet = book.GetSheetAt(i);
if (sheet == null)
{
continue;
}
sheet.GetRow(0).GetCell(0).SetCellValue(title);
sheet.GetRow(2).GetCell(0).SetCellValue($"施工单位：{project.Builder}");
}
}
/// <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>
/// Set a string value for the cell. Please note that if you are using
/// full 16 bit Unicode you should call SetEncoding() first.
/// </summary>
/// <param name="value">value to Set the cell to. For formulas we'll Set the formula
/// 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.</param>
public void SetCellValue(IRichTextString value)
{
HSSFRichTextString hvalue = (HSSFRichTextString)value;
int row = record.Row;
int col = record.Column;
short styleIndex = record.XFIndex;
if (hvalue == null)
{
NotifyFormulaChanging();
SetCellType(CellType.BLANK, false, row, col, styleIndex);
return;
}
if (hvalue.Length > Zephyr.Utils.NPOI.SS.SpreadsheetVersion.EXCEL97.MaxTextLength)
{
throw new ArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
if (cellType == CellType.FORMULA)
{
// Set the 'pre-Evaluated result' for the formula
// note - formulas do not preserve text formatting.
FormulaRecordAggregate fr = (FormulaRecordAggregate)record;
fr.SetCachedStringResult(value.String);
// Update our local cache to the un-formatted version
stringValue = new HSSFRichTextString(value.String);
return;
}
if (cellType != CellType.STRING)
{
SetCellType(CellType.STRING, false, row, col, styleIndex);
}
int index = 0;
UnicodeString str = hvalue.UnicodeString;
index = book.Workbook.AddSSTString(str);
((LabelSSTRecord)record).SSTIndex = index;
stringValue = hvalue;
stringValue.SetWorkbookReferences(book.Workbook, ((LabelSSTRecord)record));
stringValue.UnicodeString = book.Workbook.GetSSTString(index);
}
/// <summary>
/// Set a string value for the cell. Please note that if you are using
/// full 16 bit Unicode you should call SetEncoding() first.
/// </summary>
/// <param name="value">value to Set the cell to. For formulas we'll Set the formula
/// 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.</param>
public void SetCellValue(String value)
{
HSSFRichTextString str = new HSSFRichTextString(value);
SetCellValue(str);
}
/// <summary>
/// Sets the cell type. The SetValue flag indicates whether to bother about
/// trying to preserve the current value in the new record if one is Created.
/// The SetCellValue method will call this method with false in SetValue
/// since it will overWrite the cell value later
/// </summary>
/// <param name="cellType">Type of the cell.</param>
/// <param name="setValue">if set to <c>true</c> [set value].</param>
/// <param name="row">The row.</param>
/// <param name="col">The col.</param>
/// <param name="styleIndex">Index of the style.</param>
private void SetCellType(CellType cellType, bool setValue, int row, int col, short styleIndex)
{
if (cellType > CellType.ERROR)
{
throw new Exception("I have no idea what type that Is!");
}
switch (cellType)
{
case CellType.FORMULA:
FormulaRecordAggregate frec = null;
if (cellType != this.cellType)
{
frec = sheet.Sheet.RowsAggregate.CreateFormula(row, col);
}
else
{
frec = (FormulaRecordAggregate)record;
}
frec.Column = col;
if (setValue)
{
frec.FormulaRecord.Value = NumericCellValue;
}
frec.XFIndex = styleIndex;
frec.Row = row;
record = frec;
break;
case CellType.NUMERIC:
NumberRecord nrec = null;
if (cellType != this.cellType)
{
nrec = new NumberRecord();
}
else
{
nrec = (NumberRecord)record;
}
nrec.Column = col;
if (setValue)
{
nrec.Value = NumericCellValue;
}
nrec.XFIndex = styleIndex;
nrec.Row = row;
record = nrec;
break;
case CellType.STRING:
LabelSSTRecord lrec = null;
if (cellType != this.cellType)
{
lrec = new LabelSSTRecord();
}
else
{
lrec = (LabelSSTRecord)record;
}
lrec.Column = col;
lrec.Row = row;
lrec.XFIndex = styleIndex;
if (setValue)
{
String str = ConvertCellValueToString();
int sstIndex = book.Workbook.AddSSTString(new UnicodeString(str));
lrec.SSTIndex = (sstIndex);
UnicodeString us = book.Workbook.GetSSTString(sstIndex);
stringValue = new HSSFRichTextString();
stringValue.UnicodeString = us;
}
record = lrec;
break;
case CellType.BLANK:
BlankRecord brec = null;
if (cellType != this.cellType)
{
brec = new BlankRecord();
}
else
{
brec = (BlankRecord)record;
}
brec.Column = col;
// During construction the cellStyle may be null for a Blank cell.
brec.XFIndex = styleIndex;
brec.Row = row;
record = brec;
break;
case CellType.BOOLEAN:
BoolErrRecord boolRec = null;
if (cellType != this.cellType)
{
boolRec = new BoolErrRecord();
}
else
{
boolRec = (BoolErrRecord)record;
}
boolRec.Column = col;
if (setValue)
{
boolRec.SetValue(ConvertCellValueToBoolean());
}
boolRec.XFIndex = styleIndex;
boolRec.Row = row;
record = boolRec;
break;
case CellType.ERROR:
BoolErrRecord errRec = null;
if (cellType != this.cellType)
{
errRec = new BoolErrRecord();
}
else
{
errRec = (BoolErrRecord)record;
}
errRec.Column = col;
if (setValue)
{
errRec.SetValue((byte)HSSFErrorConstants.ERROR_VALUE);
}
errRec.XFIndex = styleIndex;
errRec.Row = row;
record = errRec;
break;
}
if (cellType != this.cellType &&
this.cellType != CellType.Unknown) // Special Value to indicate an Uninitialized Cell
{
sheet.Sheet.ReplaceValueRecord(record);
}
this.cellType = cellType;
}
/// <summary>
/// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when
/// reading in cells from an existing sheet.
/// </summary>
/// <param name="book">Workbook record of the workbook containing this cell</param>
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
/// <param name="cval">the Cell Value Record we wish to represent</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval)
{
record = cval;
cellType = DetermineType(cval);
stringValue = null;
this.book = book;
this.sheet = sheet;
switch (cellType)
{
case CellType.STRING:
stringValue = new HSSFRichTextString(book.Workbook, (LabelSSTRecord)cval);
break;
case CellType.BLANK:
break;
case CellType.FORMULA:
stringValue = new HSSFRichTextString(((FormulaRecordAggregate)cval).StringValue);
break;
}
ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(cval.XFIndex);
CellStyle = new HSSFCellStyle((short)cval.XFIndex, xf, book);
}
public void DrawString(String str, int x, int y)
{
if (string.IsNullOrEmpty(str))
return;
using (Font excelFont = new Font(font.Name.Equals("SansSerif") ? "Arial" : font.Name, (int)(font.Size / verticalPixelsPerPoint), font.Style))
{
FontDetails d = StaticFontMetrics.GetFontDetails(excelFont);
int width = (int)((d.GetStringWidth(str) * 8) + 12);
int height = (int)((font.Size / verticalPixelsPerPoint) + 6) * 2;
y -= Convert.ToInt32((font.Size / verticalPixelsPerPoint) + 2 * verticalPixelsPerPoint); // we want to Draw the shape from the top-left
HSSFTextbox textbox = escherGroup.CreateTextbox(new HSSFChildAnchor(x, y, x + width, y + height));
textbox.IsNoFill = (true);
textbox.LineStyle = LineStyle.None;
HSSFRichTextString s = new HSSFRichTextString(str);
HSSFFont hssfFont = MatchFont(excelFont);
s.ApplyFont(hssfFont);
textbox.String = (s);
}
}
