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 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 NpoiExcelCreator()
{
try
{
logger = Logger.getInstance;
consoleLogger = ConsoleLogger.getInstance;
//iworkbook = new HSSFWorkbook();
workbook = new XSSFWorkbook();
XSSFFont ffont = (XSSFFont)workbook.CreateFont();
ffont.FontHeight = fontSize * fontSize;
ffont.SetColor(new XSSFColor(fontRgb));
ffont.FontName = fontStyle;
defaultCellStyle = (XSSFCellStyle)workbook.GetCellStyleAt(0);
}
catch (Exception ex)
{
logger.logException(ex);
}
//defaultCellStyle.SetFillForegroundColor(new XSSFColor(cellRgb));
//defaultCellStyle.FillPattern = FillPattern.SolidForeground;
//defaultCellStyle.SetFont(ffont);
//defaultCellStyle.BorderBottom = BorderStyle.Thin;
//defaultCellStyle.BorderTop = BorderStyle.Thin;
//defaultCellStyle.BorderLeft = BorderStyle.Thin;
//defaultCellStyle.BorderRight= BorderStyle.Thin;
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().RGB[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().RGB[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().RGB[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().RGB[3]);
xssfFont.Color = ((short)23);
byte[] bytes = Encoding.ASCII.GetBytes(HexDump.ToHex(0xF1F1F1));
color.rgb = (bytes);
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.RGB[2]);
CollectionAssert.AreEqual(bytes, xssfFont.GetXSSFColor().RGB);
Assert.AreEqual(0, xssfFont.Color);
}
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);
}
private IFont Getfont(int size, XSSFColor color)
{
XSSFFont titleFont = _wb.CreateFont() as XSSFFont;
titleFont.FontHeight = size;
titleFont.SetColor(color);
titleFont.IsBold = true;
return(titleFont);
}
public void SetToFont(XSSFFont font)
{
font.FontName = FontName;
font.FontHeightInPoints = (short)FontSize;
font.IsBold = FontWeight > 400;
font.IsItalic = IsItalic;
font.IsStrikeout = IsStrikeout;
font.Underline = IsUnderline ? FontUnderlineType.Single : FontUnderlineType.None;
font.SetColor(FontColor);
}
public void setFontSize(int size)
{
fontSize = size;
XSSFFont ffont = (XSSFFont)workbook.CreateFont();
ffont.FontHeight = fontSize * fontSize;
ffont.SetColor(new XSSFColor(fontRgb));
ffont.FontName = fontStyle;
defaultCellStyle.SetFont(ffont);
}
public bool CrearArchivoEmail()
{
bool resultado = true;
try
{
string tempFile = Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
XSSFWorkbook wb = new XSSFWorkbook();
using (var ms = new MemoryStream()) {
ISheet sheet = wb.CreateSheet("Destinatarios");
int indexCabecera = 0;
var rowCabecera = sheet.CreateRow(indexCabecera);
XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
cellStyle.FillBackgroundColor = HSSFColor.DarkBlue.Index;
XSSFFont font = (XSSFFont)wb.CreateFont();
var color = new XSSFColor(new byte[] { 255, 255, 255 });
font.SetColor(color);
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
var cell1 = rowCabecera.CreateCell(0);
var cell2 = rowCabecera.CreateCell(1);
cell1.CellStyle = cellStyle;
cell1.SetCellValue(Enumeradores.EXCEL_CABECERA_CORREO);
cell2.CellStyle = cellStyle;
cell2.SetCellValue(Enumeradores.EXCEL_CABECERA_NOMBRE);
wb.Write(ms);
File.WriteAllBytes(tempFile, ms.ToArray());
}
System.Diagnostics.Process.Start(tempFile);
}
catch (Exception)
{
throw;
}
return(resultado);
}
public void setFontColor(byte red, byte green, byte blue)
{
fontRgb[0] = red;
fontRgb[1] = green;
fontRgb[2] = blue;
XSSFFont ffont = (XSSFFont)workbook.CreateFont();
ffont.FontHeight = fontSize * fontSize;
ffont.SetColor(new XSSFColor(fontRgb));
ffont.FontName = fontStyle;
defaultCellStyle.SetFont(ffont);
}
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 FilesCommentViewPartControl()
{
InitializeComponent();
_initialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
InlineRegularFont.FontName = "Segoe UI";
InlineCommentFont.IsBold = true;
InlineCommentFont.FontName = "Segoe UI";
InlineCommentFont.SetColor(new XSSFColor(Color.Blue));
sourceRTB.LanguageOption = RichTextBoxLanguageOptions.UIFonts;
targetRTB.LanguageOption = RichTextBoxLanguageOptions.UIFonts;
commentEntryBindingSource.DataSource = _commentEntries;
fileEntryBindingSource.DataSource = _fileEntries;
loadingProgressBar.Visible = false;
//GetFilesController().SelectedFilesChanged += CommentViewPartControl_SelectedFilesChanged;
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontfamily">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <param name="bold">是否加粗</param>
/// <param name="typeOffset">上下标</param>
/// <returns></returns>
public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize, FontBoldWeight bold = FontBoldWeight.Normal, FontSuperScript typeOffset = FontSuperScript.None)
{
XSSFFont xfont = hssfworkbook.CreateFont() as XSSFFont;
if (string.IsNullOrEmpty(fontfamily))
{
xfont.FontName = fontfamily;
}
if (fontcolor != null)
{
xfont.SetColor(fontcolor);
}
xfont.IsItalic = false;
xfont.FontHeightInPoints = (short)fontsize;
xfont.Boldweight = (short)bold;
xfont.TypeOffset = typeOffset;
return(xfont);
}
/// <summary>
/// Get a specific cell style
/// </summary>
/// <param name="workbook">Workbook</param>
/// <param name="reportStyle">ReportStyleCell</param>
/// <returns>XSSFCellStyle</returns>
private XSSFCellStyle GetStyle(IWorkbook workbook, ReportStyleCell reportStyle)
{
ICreationHelper creationHelper = workbook.GetCreationHelper();
//Set up column style
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
if (reportStyle == null)
{
return(cellStyle);
}
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)reportStyle.FontWeight;
//DataFormat
if (!string.IsNullOrEmpty(reportStyle.DataFormat))
{
cellStyle.SetDataFormat(creationHelper.CreateDataFormat().GetFormat(reportStyle.DataFormat));
cellStyle.Alignment = reportStyle.HorizontalAlignment;
}
//BackgroundColor
if (!string.IsNullOrEmpty(reportStyle.BackgroundColor))
{
XSSFColor backgroundColor = new XSSFColor(Color.FromName(reportStyle.BackgroundColor));
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(backgroundColor);
}
//Foreground Color
if (!string.IsNullOrEmpty(reportStyle.FontColor))
{
XSSFColor foregroundColor = new XSSFColor(Color.FromName(reportStyle.FontColor));
font.SetColor(foregroundColor);
}
//Wrap Text
cellStyle.SetFont(font);
cellStyle.WrapText = reportStyle.WrapText;
return(cellStyle);
}
private XSSFCellStyle createHeaderStyle(XSSFWorkbook workbook)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = false;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
XSSFColor color = new XSSFColor(new byte[] { 255, 0, 0 });
font.SetColor(color);
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
return(style);
}
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 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 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 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();
}
}
/// <summary>
/// 通过模板生成EXCEL
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataList">sheet集合</param>
/// <param name="temName">模板名称</param>
/// <returns></returns>
public static byte[] writeExcelToFile(XSSFWorkbook workbook, List <ExcelSheetModel> dataList, String temName)
{
// 列宽一个像素的固定值
//decimal pixCellConst = 31.94888178913738m;
// 行宽一个像素的固定值
//decimal pixRowConst = 15.15151515151515m;
String location = null;
XSSFRow row = null;
foreach (ExcelSheetModel dataSheet in dataList)
{
if (!string.IsNullOrEmpty(dataSheet.sheetType))
{
}
else
{
#region 自定义模式
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(dataSheet.sheetName);
// 自动更新公式
sheet.ForceFormulaRecalculation = true;
foreach (ExcelCellModel dataCell in dataSheet.dataList)
{
if (!string.IsNullOrEmpty(dataCell.location))
{
location = dataCell.location.ToUpper();
int dataNum = 0;
int dataRow = 0;
string rowStr = "";
string columnStr = "";
for (int i = 0; i < location.Length; i++)
{
var locationArr = location.ToCharArray();
if (locationArr[i] >= 48 && locationArr[i] <= 57)
{
columnStr += locationArr[i];
}
if (locationArr[i] >= 65 && locationArr[i] <= 90)
{
rowStr += locationArr[i];
}
}
for (int i = 0; i < rowStr.Length; i++)
{
var rowStrArr = rowStr.ToCharArray();
char ch = rowStrArr[rowStr.Length - i - 1];
dataNum = (int)(ch - 'A' + 1);
dataNum *= int.Parse(Math.Pow(26, i).ToString());
dataRow += dataNum;
}
dataCell.rowNum = int.Parse(columnStr) - 1;
dataCell.cellNum = dataRow - 1;
}
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->Strat");
XSSFCellStyle setBorder = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)workbook.CreateFont();
XSSFColor fontColor = null;
XSSFColor backColor = null;
if (row == null || row.RowNum != dataCell.rowNum)
{
row = (XSSFRow)sheet.GetRow(dataCell.rowNum);
}
XSSFCell cell = (XSSFCell)row.CreateCell(dataCell.cellNum);
// check合并单元格
if (dataCell.regionCell != null)
{
if (dataCell.regionCell.Length == 2)
{
if (dataCell.rowNum > dataCell.regionCell[0])
{
//logger.Debug("RegionCella结束行小于当前行");
}
else if (dataCell.cellNum > dataCell.regionCell[1])
{
//logger.Debug("RegionCella结束列小于当前列");
}
else
{
sheet.AddMergedRegion(
new CellRangeAddress(dataCell.rowNum, dataCell.regionCell[0],
dataCell.cellNum, dataCell.regionCell[1]));
}
}
else
{
//logger.Debug("RegionCell参数异常");
}
}
// check列宽
if (dataCell.cellWidth != 0)
{
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* int.Parse(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())).ToString()));
* //*/
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* Convert.ToInt32(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString()))));
* //*/
sheet.SetColumnWidth(dataCell.cellNum, dataCell.cellWidth);
}
// check行高
if (dataCell.cellHeight != 0)
{
row.Height = dataCell.cellHeight;
}
// check字体颜色
if (dataCell.cellColor != null)
{
if (dataCell.cellColor.Length == 3)
{
if (((dataCell.cellColor[0] >= 0) || (dataCell.cellColor[0] <= 255)) &&
((dataCell.cellColor[1] >= 0) || (dataCell.cellColor[1] <= 255)) &&
((dataCell.cellColor[2] >= 0) || (dataCell.cellColor[2] <= 255)))
{
fontColor = new XSSFColor(Color.FromArgb(dataCell.cellColor[0],
dataCell.cellColor[1], dataCell.cellColor[2]));
font.SetColor(fontColor);
}
}
}
// check背景色
if (dataCell.backColor != null)
{
if (dataCell.backColor.Length == 3)
{
if (((dataCell.backColor[0] >= 0) || (dataCell.backColor[0] <= 255)) &&
((dataCell.backColor[1] >= 0) || (dataCell.backColor[1] <= 255)) &&
((dataCell.backColor[2] >= 0) || (dataCell.backColor[2] <= 255)))
{
backColor = new XSSFColor(Color.FromArgb(dataCell.backColor[0],
dataCell.backColor[1], dataCell.backColor[2]));
setBorder.FillPattern = FillPattern.SolidForeground;
setBorder.SetFillForegroundColor(backColor);
}
}
}
// check边框
if (dataCell.borderLine != null)
{
if (dataCell.borderLine.Length == 4)
{
setBorder.BorderTop = dataCell.borderLine[0];
setBorder.BorderBottom = dataCell.borderLine[1];
setBorder.BorderLeft = dataCell.borderLine[2];
setBorder.BorderRight = dataCell.borderLine[3];
}
}
// check字体
if (!string.IsNullOrEmpty(dataCell.fontName))
{
font.FontName = dataCell.fontName;
}
else
{
font.FontName = "宋体";
}
// check字体大小
if (dataCell.fontSize != 0)
{
font.FontHeightInPoints = dataCell.fontSize;
}
else
{
font.FontHeightInPoints = (short)10;
}
// 字体是否加粗
if (dataCell.Boldweight)
{
font.IsBold = true;
}
// 是否自动换行
if (dataCell.wrapText)
{
setBorder.WrapText = true;
}
// 单元格的值
if (!string.IsNullOrEmpty(dataCell.cellValue))
{
cell.SetCellValue(dataCell.cellValue);
}
else
{
cell.SetCellValue("");
}
// 水平类型
setBorder.Alignment = dataCell.horizontalAlignment;
// 垂直类型
setBorder.VerticalAlignment = dataCell.verticalAlignment;
// 图片
if (dataCell.excelPictureModel != null)
{
int col2 = 0;
int row2 = 0;
byte[] arr = null;
if (dataCell.excelPictureModel.CodeType == PrintStructFlag.QRCODE)
{//二维码
MemoryStream pictureIS = QRCodeUtil.GetQRCode(dataCell.excelPictureModel.qrCode);
arr = new byte[pictureIS.Length];
pictureIS.Position = 0;
pictureIS.Read(arr, 0, (int)pictureIS.Length);
pictureIS.Close();
col2 = dataCell.excelPictureModel.endColNum + 1;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
else if (dataCell.excelPictureModel.CodeType == PrintStructFlag.BARCODE)
{ //一维码
BarCodeClass bc = new BarCodeClass();
Image image = bc.ZXCreateBarCode(dataCell.excelPictureModel.qrCode);
ImageConverter imgconv = new ImageConverter();
arr = (byte[])imgconv.ConvertTo(image, typeof(byte[]));
col2 = dataCell.excelPictureModel.endColNum + 4;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
int pIndex = workbook.AddPicture(arr, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, dataCell.cellNum,
dataCell.rowNum,
col2,
row2);
patriarch.CreatePicture(anchor, pIndex);
}
setBorder.SetFont(font);
cell.CellStyle = setBorder;
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->End");
}
#endregion
}
}
string filePath = System.Threading.Thread.GetDomain().BaseDirectory + "\\";
var new_tempFileName = "to_" + temName + ".xlsx";
FileStream files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.Create);
workbook.Write(files);
files.Close();
files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.OpenOrCreate);
byte[] bytes = new byte[files.Length];
files.Read(bytes, 0, bytes.Length);
files.Close();
workbook.Close();
return(bytes);
}