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);
}
private IRichTextString BuildFormattedSessionCellContent(XSSFWorkbook workbook, ScheduleDetailDTO session)
{
string className = session.Schedule.Class.Name;
string classBranch = className + " - " + session.Schedule.Branch;
string song = "♪ " + session.Schedule.Song;
string numberOfSessions = $"Buổi {session.SessionNo}/{session.Schedule.Sessions}";
XSSFFont classBranchFont = (XSSFFont)workbook.CreateFont();
classBranchFont.FontName = "Times New Roman";
classBranchFont.FontHeightInPoints = 18;
classBranchFont.IsBold = true;
if (session.SessionNo == 1)
{
classBranchFont.SetColor(new XSSFColor(new byte[] { 192, 0, 0 }));
}
IFont songFont = workbook.CreateFont();
songFont.FontName = "Times New Roman";
songFont.FontHeightInPoints = 18;
songFont.IsBold = false;
IRichTextString formattedCellContent;
bool isYoga = string.Equals(className, "Yoga", StringComparison.CurrentCultureIgnoreCase);
bool isCardioDance = string.Equals(className, "Cardio Dance", StringComparison.CurrentCultureIgnoreCase);
if (isYoga || isCardioDance)
{
if (isYoga)
{
if (classBranch.Contains("Q3"))
{
classBranch += ", LVS";
}
else if (classBranch.Contains("LVS"))
{
classBranch += ", Q3";
}
}
else
{
classBranch = className;
}
formattedCellContent = new XSSFRichTextString(classBranch);
formattedCellContent.ApplyFont(0, classBranch.Length, classBranchFont);
}
else
{
formattedCellContent = new XSSFRichTextString(classBranch + "\n" + song + "\n" + numberOfSessions);
formattedCellContent.ApplyFont(0, classBranch.Length, classBranchFont);
formattedCellContent.ApplyFont(classBranch.Length + 1, (classBranch + "\n" + song).Length, songFont);
formattedCellContent.ApplyFont((classBranch + "\n" + song).Length + 1, (classBranch + "\n" + song + "\n" + numberOfSessions).Length, classBranchFont);
}
return(formattedCellContent);
}
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 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 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 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.FromRgb(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));
checkRewrite(wb);
wb.Close();
}
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.FromRgb(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();
}
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.FontHeightInPoints = 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 static XSSFRichTextString CreateRichTextString(string text, IFont font)
{
var richText = new XSSFRichTextString(text);
richText.ApplyFont(font);
return(richText);
}
static XSSFRichTextString style(string x, IFont index)
{
var text = new XSSFRichTextString(x);
text.ApplyFont(0, x.Length, index);
return(text);
}
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)
{
// expected
}
tbl.PutFont(new XSSFFont());
rt.ApplyFont(0, 10, (short)1);
rt.ApplyFont((short)1);
}
public void TestRgbColor()
{
const string testText = "Apache";
XSSFRichTextString rt = new XSSFRichTextString(testText);
XSSFFont font = new XSSFFont {
FontName = "Times New Roman", FontHeightInPoints = 11
};
font.SetColor(new XSSFColor(Color.Red));
rt.ApplyFont(0, testText.Length, font);
CT_Rst ct = rt.GetCTRst();
Assert.AreEqual("<r><rPr><color rgb=\"FF0000\"/><rFont val=\"Times New Roman\"/><sz val=\"11\"/></rPr><t>Apache</t></r>", ct.XmlText);
}
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"));
}
}
public void TestApplyFont()
{
XSSFRichTextString rt = new XSSFRichTextString();
rt.Append("123");
rt.Append("4567");
rt.Append("89");
Assert.AreEqual("123456789", rt.String);
Assert.AreEqual(false, rt.HasFormatting());
XSSFFont font1 = new XSSFFont();
font1.IsBold = (true);
rt.ApplyFont(2, 5, font1);
Assert.AreEqual(true, rt.HasFormatting());
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 TestSetTextSingleParagraph()
{
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));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
Assert.AreEqual("Test String", paras[0].Text);
List <XSSFTextRun> runs = paras[0].TextRuns;
Assert.AreEqual(1, runs.Count);
Assert.AreEqual("Arial", runs[0].FontFamily);
var clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
public void TestAddNewTextParagraphWithRTS()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String");
XSSFFont font = wb1.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
XSSFFont midfont = wb1.CreateFont() as XSSFFont;
midfont.SetColor(new XSSFColor(Color.FromRgb(0, 255, 0)));
rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font
XSSFTextParagraph para = shape.AddNewTextParagraph(rt);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = sshape.TextParagraphs;
Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that.
List <XSSFTextRun> runs = para.TextRuns;
Assert.AreEqual(3, runs.Count);
// first run properties
Assert.AreEqual("Test ", runs[0].Text);
Assert.AreEqual("Arial", runs[0].FontFamily);
var clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
// second run properties
Assert.AreEqual("Rich Text", runs[1].Text);
Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily);
clr = runs[1].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
// third run properties
Assert.AreEqual(" String", runs[2].Text);
Assert.AreEqual("Arial", runs[2].FontFamily);
clr = runs[2].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb2);
wb2.Close();
}
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();
}
}
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 TestNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb1.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.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
String drawingId = rp.Relationship.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 = LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
Assert.IsNotNull(c2);
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, wb1.CreateFont());
rt.ApplyFont(5, 6, wb1.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
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.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\""));
checkRewrite(wb2);
wb2.Close();
}
/// <summary>
/// 构建一个Sheet
/// </summary>
/// <param name="sheet"></param>
public void CreteSheet(NPOIExcelSheet sheet)
{
ISheet curSheet = _book.CreateSheet(sheet.SheetName);
//表格
int rowIndex = 0;
if (sheet.Rows != null && sheet.Rows.Count > 0)
{
foreach (var row in sheet.Rows)
{
IRow curRow = curSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (var cell in row.Cells)
{
ICell curCell = curRow.CreateCell(cellIndex, cell.Type);
cell.Value = cell.Value ?? "";
if (cell.Style != null)
{
curCell.CellStyle = cell.Style;
}
//富文本设置
if (cell.RichTextSettings != null && cell.RichTextSettings.Any())
{
if (_excelType == NPOIExcelType.XLSX)
{
//SXSSFWorkbook
XSSFRichTextString xSSF = new XSSFRichTextString(cell.Value);
cell.RichTextSettings.ForEach(setting => { xSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
curCell.SetCellValue(xSSF);
}
else
{
HSSFRichTextString hSSF = new HSSFRichTextString(cell.Value);
cell.RichTextSettings.ForEach(setting => { hSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
curCell.SetCellValue(hSSF);
}
}
else
{
curCell.SetCellValue(cell.Value);
}
//自动宽度
if (cell.Width >= 0)
{
if (cell.Width == 0)
{
AutoWidth(curSheet, cellIndex);
}
else
{
curSheet.SetColumnWidth(cellIndex, (int)cell.Width * 256);
}
}
cellIndex++;
}
curRow.Height = (short)Convert.ToInt32(row.Height * 20);
//合并单元格策略
if (row.Regions.Any())
{
int maxRow = 0;
foreach (var region in row.Regions)
{
//if (maxRow < rowIndex + region.RowCount - 1)
//{
// maxRow = rowIndex + region.RowCount - 1;
//}
curSheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + region.RowCount - 1, region.StartCol, region.EndCol));
}
// rowIndex += maxRow > 0 ? maxRow : 1;
}
rowIndex++;
rowIndex += row.MaginButton;
}
}
//图表
if (sheet.Chart != null && sheet.Chart.Count > 0)
{
int startRow = rowIndex + 1;
foreach (var curChart in sheet.Chart)
{
int endRow = startRow + curChart.MarginBottom;
CreateChart(curChart, curSheet, startRow, endRow);
startRow = endRow + 1;
}
}
}
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 <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出错！");
}
}
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 Run()
{
IWorkbook workbook = new XSSFWorkbook();
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
// font Test용 Sheet생성
ISheet sheet1 = workbook.CreateSheet("Font_Test Sheet");
//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);
/*
* BorderStryle
* */
ISheet sheet3 = workbook.CreateSheet("BorderStype");
IRow row = sheet3.CreateRow(1);
// Create a cell and put a value in it.
cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
//create border diagonal
style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
cell.CellStyle = style;
// Create a cell and put a value in it.
cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.Medium;
style2.BorderDiagonal = BorderDiagonal.Backward;
style2.BorderDiagonalColor = IndexedColors.Red.Index;
cell2.CellStyle = style2;
using (FileStream fs = File.Create(@"C:\00.Dev\temp\HyprtLink_Font.xlsx"))
{
workbook.Write(fs);
}
}
/// <summary>
/// Genera el archivo excel
/// </summary>
/// <param name="datos"></param>
/// <param name="tituloReporte"></param>
/// <param name="nombreOrganizacion"></param>
/// <param name="fecha"></param>
/// <returns></returns>
private bool GenerarReporteExcel(List <ReporteTabularDisponibilidadSemanaInfo> datos, string tituloReporte, string nombreOrganizacion, DateTime fecha)
{
bool retValue = false;
XSSFWorkbook hssfworkbook = new XSSFWorkbook();
//Manejo del formato
IDataFormat format = hssfworkbook.CreateDataFormat();
//Estilo para renglon en general encabezado claro
var esRowGeneral = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esRowGeneral.SetFillForegroundColor(GrisClaro);
esRowGeneral.FillPattern = FillPattern.SolidForeground;
esRowGeneral.FillBackgroundColor = IndexedColors.White.Index;
//Creamos la hora del archivo
ISheet sheet1 = hssfworkbook.CreateSheet(Properties.Resources.RecepcionReporteTabular_RptNomHoja);
//indices
int indexColumn = 0;
int indexRow = 6;
int indexNumeroSemana = 0;
int maxCorrales = 0;
foreach (var semana in datos)
{
if (maxCorrales < semana.TotalCorrales)
{
maxCorrales = semana.TotalCorrales;
}
}
//Renglones de encabezado
ICellStyle styleEncabezado = hssfworkbook.CreateCellStyle();
var i = 0;
//Creamos los renglones para alojar el numero de corrales
for (; i < maxCorrales + indexRow + 5; i++)
{
IRow rengloi = sheet1.CreateRow(i);
}
//fuente para todos los encabezados y totales
IFont fontEncabezado = hssfworkbook.CreateFont();
fontEncabezado.FontHeightInPoints = 10;
fontEncabezado.Boldweight = (short)FontBoldWeight.Bold;
IRow renglonEncabezado1 = sheet1.GetRow(indexRow + 1);
IRow renglonNsemana = sheet1.CreateRow(indexRow + 2);
IRow renglonEncabezadoDetalle = sheet1.CreateRow(indexRow + 3);
IRow renglonTotal = sheet1.CreateRow(i);
//Creamos los registros de semanas
foreach (var semana in datos)
{
var indexAux = indexColumn;
//Total Cabezas
XSSFRichTextString lblTotal = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncTotal);
lblTotal.ApplyFont(0, lblTotal.Length, fontEncabezado);
ICell cellLblTotal = renglonTotal.CreateCell(indexAux);
cellLblTotal.SetCellValue(lblTotal);
//Total Cabezas
ICellStyle esTotal = hssfworkbook.CreateCellStyle();
esTotal.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
XSSFRichTextString txtTotal = new XSSFRichTextString(semana.TotalCabezas.ToString());
txtTotal.ApplyFont(0, txtTotal.Length, fontEncabezado);
ICell cellTxtTotal = renglonTotal.CreateCell(indexAux + 3);
cellTxtTotal.CellStyle = esTotal;
cellTxtTotal.SetCellValue(txtTotal);
//LblSem
var esSem = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esSem.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
esSem.SetFillForegroundColor(GrisClaro);
esSem.FillPattern = FillPattern.SolidForeground;
esSem.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString lblSemana = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncSemN);
lblSemana.ApplyFont(0, lblSemana.Length, fontEncabezado);
ICell cellLblSemana = renglonEncabezado1.CreateCell(indexAux);
cellLblSemana.CellStyle = esSem;
cellLblSemana.SetCellValue(lblSemana);
//////merged cells on mutiple rows
//CellRangeAddress regionSemana = new CellRangeAddress(indexRow, indexRow, indexColumn, indexColumn+=2);
//sheet1.AddMergedRegion(regionSemana);
//LblAc
var esAc = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esAc.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esAc.SetFillForegroundColor(GrisClaro);
esAc.FillPattern = FillPattern.SolidForeground;
esAc.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString lblAc = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncAC);
lblAc.ApplyFont(0, lblAc.Length, fontEncabezado);
ICell cellLblAc = renglonEncabezado1.CreateCell(indexAux + 3);
cellLblAc.CellStyle = esAc;
cellLblAc.SetCellValue(lblAc);
//CellRangeAddress regionAc = new CellRangeAddress(indexRow, indexRow, indexColumn, indexColumn + 1);
//sheet1.AddMergedRegion(regionAc);
//Renglon 2 valor de semana y primer lunes de la semana
indexAux = indexColumn;
//Valor de numero de semana
var estxtSemana = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estxtSemana.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estxtSemana.SetFillForegroundColor(GrisClaro);
estxtSemana.FillPattern = FillPattern.SolidForeground;
estxtSemana.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString txtSemana = new XSSFRichTextString(indexNumeroSemana.ToString());
txtSemana.ApplyFont(0, txtSemana.Length, fontEncabezado);
ICell cellTxtSemana = renglonNsemana.CreateCell(indexAux);
cellTxtSemana.CellStyle = estxtSemana;
cellTxtSemana.SetCellValue(txtSemana);
var cellx1 = renglonNsemana.CreateCell(indexAux + 1);
cellx1.CellStyle = estxtSemana;
var cellx2 = renglonNsemana.CreateCell(indexAux + 2);
cellx2.CellStyle = estxtSemana;
//Valor de numero de semana
var esTxtLunes = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esTxtLunes.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esTxtLunes.SetFillForegroundColor(GrisClaro);
esTxtLunes.FillPattern = FillPattern.SolidForeground;
esTxtLunes.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString txtPrimerLunes =
new XSSFRichTextString(semana.FechaInicioSemana.ToString("MMM dd").ToUpper());
txtPrimerLunes.ApplyFont(0, txtPrimerLunes.Length, fontEncabezado);
ICell cellTxtLunes = renglonNsemana.CreateCell(indexAux + 3);
cellTxtLunes.CellStyle = esTxtLunes;
cellTxtLunes.SetCellValue(txtPrimerLunes);
var cellx3 = renglonNsemana.CreateCell(indexAux + 4);
cellx3.CellStyle = estxtSemana;
indexAux = indexColumn;
//Valor de enc corral
var esEncCorr = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esEncCorr.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esEncCorr.SetFillForegroundColor(GrisClaro);
esEncCorr.FillPattern = FillPattern.SolidForeground;
esEncCorr.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encCorral = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncCorral);
encCorral.ApplyFont(0, encCorral.Length, fontEncabezado);
ICell cellEncCorral = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncCorral.CellStyle = esEncCorr;
cellEncCorral.SetCellValue(encCorral);
//Valor disp
var esDis = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esDis.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esDis.SetFillForegroundColor(GrisClaro);
esDis.FillPattern = FillPattern.SolidForeground;
esDis.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 3 * 256);
XSSFRichTextString encDispoManual = new XSSFRichTextString("");
encDispoManual.ApplyFont(0, encDispoManual.Length, fontEncabezado);
ICell cellDispManual = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellDispManual.CellStyle = esDis;
cellDispManual.SetCellValue(encDispoManual);
//Valor de enc tipo
var esTipo = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esTipo.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
esTipo.SetFillForegroundColor(GrisClaro);
esTipo.FillPattern = FillPattern.SolidForeground;
esTipo.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 20 * 256);
XSSFRichTextString encTipo = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncTipo);
encTipo.ApplyFont(0, encTipo.Length, fontEncabezado);
ICell cellEncTipo = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncTipo.CellStyle = esTipo;
cellEncTipo.SetCellValue(encTipo);
//Valor de enc cabezas
var esCab = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esCab.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esCab.SetFillForegroundColor(GrisClaro);
esCab.FillPattern = FillPattern.SolidForeground;
esCab.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encCabezas = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncCab);
encCabezas.ApplyFont(0, encCabezas.Length, fontEncabezado);
ICell cellEncCabezas = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncCabezas.CellStyle = esCab;
cellEncCabezas.SetCellValue(encCabezas);
//encabezado formula
var esForm = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esForm.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esForm.SetFillForegroundColor(GrisClaro);
esForm.FillPattern = FillPattern.SolidForeground;
esForm.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encForm = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncFor);
encForm.ApplyFont(0, encForm.Length, fontEncabezado);
ICell cellEncFormula = renglonEncabezadoDetalle.CreateCell(indexAux);
cellEncFormula.CellStyle = esForm;
cellEncFormula.SetCellValue(encForm);
//colocamos los corrales
var indexCorral = indexRow + 4;
foreach (var corral in semana.Corrales)
{
indexAux = indexColumn;
IRow rowCorral = sheet1.GetRow(indexCorral);
XSSFRichTextString rwCorral = new XSSFRichTextString(corral.Codigo.Trim());
ICell cellCorral = rowCorral.CreateCell(indexAux++);
cellCorral.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellCorral.SetCellValue(rwCorral);
XSSFRichTextString rwDispo =
new XSSFRichTextString(corral.DisponibilidadManual == 1 ? "*" : "");
ICell cellDispo = rowCorral.CreateCell(indexAux++);
cellDispo.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellDispo.SetCellValue(rwDispo);
XSSFRichTextString rwTipo = new XSSFRichTextString(corral.Descripcion);
ICell cellTipo = rowCorral.CreateCell(indexAux++);
cellTipo.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellTipo.SetCellValue(rwTipo);
XSSFRichTextString rwCabezas = new XSSFRichTextString(corral.Cabezas.ToString());
ICell cellCabezas = rowCorral.CreateCell(indexAux++);
ICellStyle esRwCabezas = hssfworkbook.CreateCellStyle();
esRwCabezas.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esRwCabezas.DataFormat = format.GetFormat("0.00");
cellCabezas.CellStyle = esRwCabezas;
cellCabezas.SetCellValue(rwCabezas);
XSSFRichTextString rwFormula = new XSSFRichTextString(corral.FormulaIDServida.ToString());
ICell cellFormula = rowCorral.CreateCell(indexAux);
ICellStyle esRwFormula = hssfworkbook.CreateCellStyle();
esRwFormula.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esRwFormula.DataFormat = format.GetFormat("0.00");
cellFormula.CellStyle = esRwFormula;
cellFormula.SetCellValue(rwFormula);
indexCorral++;
}
indexColumn += 6;
sheet1.SetColumnWidth(indexColumn - 1, 2 * 256);
for (int renglonR = indexRow; renglonR < maxCorrales + indexRow + 5; renglonR++)
{
var renglon = sheet1.GetRow(renglonR);
ICell celda = renglon.GetCell(indexColumn - 1);
if (celda == null)
{
celda = renglon.CreateCell(indexColumn - 1);
}
celda.CellStyle = esRowGeneral;
}
indexNumeroSemana++;
if (indexColumn > 10000)
{
indexColumn = 0;
indexRow = maxCorrales + 10;
}
}
//Agregamos el logo
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, 0, 0, 7, 5);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("Imagenes/skLogo.png", hssfworkbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.None;
for (int indexRowTitulos = 0; indexRowTitulos <= 7; indexRowTitulos++)
{
IRow renglon = sheet1.GetRow(indexRowTitulos);
if (renglon != null)
{
for (int ix = 0; ix <= indexColumn - 1; ix++)
{
ICell celda = renglon.GetCell(ix);
if (celda == null)
{
celda = renglon.CreateCell(ix);
}
celda.CellStyle = esRowGeneral;
}
}
}
for (int ix = 0; ix <= indexColumn - 1; ix++)
{
//ICell celda = renglonEncabezado1.GetCell(ix);
//if (celda == null)
// celda = renglonEncabezado1.CreateCell(ix);
ICell celdaTotal = renglonTotal.GetCell(ix);
if (celdaTotal == null)
{
celdaTotal = renglonTotal.CreateCell(ix);
}
//celda.CellStyle = esRowGeneral;
celdaTotal.CellStyle = esRowGeneral;
}
//Encabezado de empresa
var estiloOrganizacion = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloOrganizacion.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloOrganizacion.SetFillForegroundColor(GrisClaro);
estiloOrganizacion.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtOrganizacion = new XSSFRichTextString(nombreOrganizacion);
IFont fontOrg = hssfworkbook.CreateFont();
fontOrg.FontHeightInPoints = 20;
fontOrg.Boldweight = (short)FontBoldWeight.Bold;
txtOrganizacion.ApplyFont(0, txtOrganizacion.Length, fontOrg);
ICell cell = sheet1.GetRow(1).CreateCell(14);
cell.CellStyle = estiloOrganizacion;
cell.SetCellValue(txtOrganizacion);
//Titulo reporte
var estiloTituloReporte = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloTituloReporte.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloTituloReporte.SetFillForegroundColor(GrisClaro);
estiloTituloReporte.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtTitulo = new XSSFRichTextString(tituloReporte);
IFont fontTitulo = hssfworkbook.CreateFont();
fontTitulo.FontHeightInPoints = 14;
fontTitulo.Boldweight = (short)FontBoldWeight.Bold;
txtTitulo.ApplyFont(0, txtTitulo.Length, fontTitulo);
ICell cellTitulo = sheet1.GetRow(3).CreateCell(14);
cellTitulo.CellStyle = estiloTituloReporte;
cellTitulo.SetCellValue(txtTitulo);
//fecha
var estiloFecha = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloFecha.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloFecha.SetFillForegroundColor(GrisClaro);
estiloFecha.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtFecha = new XSSFRichTextString(fecha.ToString("dd/MM/yyyy"));
IFont fontFecha = hssfworkbook.CreateFont();
fontFecha.FontHeightInPoints = 10;
fontFecha.Boldweight = (short)FontBoldWeight.Bold;
txtFecha.ApplyFont(0, txtFecha.Length, fontFecha);
ICell cellFecha = sheet1.GetRow(4).CreateCell(14);
cellFecha.CellStyle = estiloFecha;
cellFecha.SetCellValue(txtFecha);
retValue = EscribirArchivo(hssfworkbook, Properties.Resources.RecepcionReporteTabular_RptFileName);
return(retValue);
}
public void Run()
{
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.IsBold = true;
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;
sheet.CreateRow(0).CreateCell(1).SetCellValue(123);
sheet.Header.Left = HSSFHeader.Page; //Page is a static property of HSSFHeader and HSSFFooter
sheet.Header.Center = "This is a test sheet";
//set footer text
sheet.Footer.Left = "Copyright NPOI Team";
sheet.Footer.Right = "created by Tony Qu（瞿杰）";
using (var fs = File.Create(@"C:\00.Dev\temp\CreateCommentInXlsx.xlsx"))
{
workbook.Write(fs);
}
}