public void TestBug56835CellComment()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
// first comment works
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
Assert.IsNotNull(comment);
try
{
Drawing.CreateCellComment(anchor);
Assert.Fail("Should fail if we try to add the same comment for the same cell");
}
catch (ArgumentException)
{
// expected
}
}
finally
{
wb.Close();
}
}
private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
public void TestReadTextBox()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
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;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
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);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[4] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[4];
Assert.AreEqual("Sheet with various pictures\n(jpeg, png, wmf, emf and pict)", textbox.Text);
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();
}
private void check58325(XSSFWorkbook wb, int expectedShapes)
{
XSSFSheet sheet = wb.GetSheet("MetasNM001") as XSSFSheet;
Assert.IsNotNull(sheet);
StringBuilder str = new StringBuilder();
str.Append("sheet " + sheet.SheetName + " - ");
XSSFDrawing drawing = sheet.GetDrawingPatriarch();
//drawing = ((XSSFSheet)sheet).createDrawingPatriarch();
List <XSSFShape> shapes = drawing.GetShapes();
str.Append("drawing.Shapes.size() = " + shapes.Count);
IEnumerator <XSSFShape> it = shapes.GetEnumerator();
while (it.MoveNext())
{
XSSFShape shape = it.Current;
str.Append(", " + shape.ToString());
str.Append(", Col1:" + ((XSSFClientAnchor)shape.GetAnchor()).Col1);
str.Append(", Col2:" + ((XSSFClientAnchor)shape.GetAnchor()).Col2);
str.Append(", Row1:" + ((XSSFClientAnchor)shape.GetAnchor()).Row1);
str.Append(", Row2:" + ((XSSFClientAnchor)shape.GetAnchor()).Row2);
}
Assert.AreEqual(expectedShapes, shapes.Count,
"Having shapes: " + str);
}
public void TestAddMultipleParagraphs()
{
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));
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 1");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 2");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 3");
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(4, paras.Count); // this should be 4 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added 3 paragraphs
Assert.AreEqual("Line 1\nLine 2\nLine 3", shape.Text);
checkRewrite(wb);
wb.Close();
}
public void TestSetAddMultipleParagraphs()
{
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));
shape.SetText("Line 1");
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 2");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 3");
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(3, paras.Count); // this should be 3 as we overwrote the default paragraph with SetText, then Added 2 new paragraphs
Assert.AreEqual("Line 1\nLine 2\nLine 3", shape.Text);
checkRewrite(wb);
wb.Close();
}
public void TestReadTextBox2()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox2.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0];
String extracted = textbox.Text;
StringBuilder sb = new StringBuilder();
sb.Append("1. content1A\n");
sb.Append("\t1. content1B\n");
sb.Append("\t2. content2B\n");
sb.Append("\t3. content3B\n");
sb.Append("2. content2A\n");
sb.Append("\t3. content2BStartAt3\n");
sb.Append("\t\n\t\n\t");
sb.Append("4. content2BStartAt3Incremented\n");
sb.Append("\t\n\t\n\t\n\t");
Assert.AreEqual(sb.ToString(), extracted);
checkRewrite(wb);
wb.Close();
}
public void Create()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
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();
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
public void Test53568()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53568.xlsx");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.IsNotNull(pictures);
Assert.AreEqual(4, pictures.Count);
XSSFSheet sheet1 = wb.GetSheetAt(0) as XSSFSheet;
List <XSSFShape> shapes1 = (sheet1.CreateDrawingPatriarch() as XSSFDrawing).GetShapes();
Assert.IsNotNull(shapes1);
Assert.AreEqual(5, shapes1.Count);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
XSSFSheet sheet = wb.GetSheetAt(i) as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (XSSFShape shape in Drawing.GetShapes())
{
if (shape is XSSFPicture)
{
XSSFPicture pic = (XSSFPicture)shape;
XSSFPictureData picData = pic.PictureData as XSSFPictureData;
Assert.IsNotNull(picData);
}
}
}
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing Drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
byte[] wmfData = Encoding.UTF8.GetBytes("test wmf data");
byte[] pngData = Encoding.UTF8.GetBytes("test png data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
int wmfIdx = wb.AddPicture(wmfData, PictureType.WMF);
Assert.AreEqual(2, pictures.Count);
Assert.AreEqual("wmf", ((XSSFPictureData)pictures[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures[wmfIdx]).Data));
int pngIdx = wb.AddPicture(pngData, PictureType.PNG);
Assert.AreEqual(3, pictures.Count);
Assert.AreEqual("png", ((XSSFPictureData)pictures[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures[pngIdx]).Data));
//TODO finish usermodel API for XSSFPicture
XSSFPicture p1 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), jpegIdx);
Assert.IsNotNull(p1);
XSSFPicture p2 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), wmfIdx);
Assert.IsNotNull(p1);
XSSFPicture p3 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), pngIdx);
Assert.IsNotNull(p1);
//check that the Added pictures are accessible After write
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
IList pictures2 = wb.GetAllPictures();
Assert.AreEqual(3, pictures2.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures2[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures2[jpegIdx]).Data));
Assert.AreEqual("wmf", ((XSSFPictureData)pictures2[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures2[wmfIdx]).Data));
Assert.AreEqual("png", ((XSSFPictureData)pictures2[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures2[pngIdx]).Data));
}
/// <summary>
/// 样表
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
private ActionResult ImportDownload(BF_IMPORT.Entity entity)
{
try
{
List <string> commentList = new List <string>();
DataTable dt = BF_IMPORT.GetSampleTable(entity, out commentList);
string filename = HttpUtility.UrlEncode(string.Format("{0}_{1}.xlsx", entity.NAME, DateTime.Now.ToString("yyyyMMddHHmmss")), Encoding.UTF8);
string path = System.Web.HttpContext.Current.Server.MapPath("~/tmp/");
Library.Export.ExcelFile export = new Library.Export.ExcelFile(path);
string fullName = export.ToExcel(dt);
if (string.IsNullOrWhiteSpace(fullName) == true || System.IO.File.Exists(fullName) == false)
{
return(ShowAlert("未生成Excel文件"));
}
IWorkbook workBook;
using (FileStream fs = System.IO.File.OpenRead(fullName))
{
workBook = new XSSFWorkbook(fs);
ISheet sheet = workBook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
for (int c = 0; c < row.Cells.Count; c++)
{
if (string.IsNullOrWhiteSpace(commentList[c]) == false)
{
IComment comment = patriarch.CreateCellComment(new XSSFClientAnchor(0, 50, 0, 50, c, 0, c + 3, 5));
comment.Author = "编辑提示";
comment.String = new XSSFRichTextString("【编辑提示】\r\n" + commentList[c]);
row.Cells[c].CellComment = comment;
}
}
}
//重写文件
using (FileStream fs = System.IO.File.Create(fullName))
{
workBook.Write(fs);
fs.Close();
}
System.Web.HttpContext.Current.Response.Buffer = true;
System.Web.HttpContext.Current.Response.Clear();//清除缓冲区所有内容
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
System.Web.HttpContext.Current.Response.WriteFile(fullName);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
//删除文件
export.Delete(fullName);
}
catch (Exception ex)
{
ShowAlert("生成样表出错：" + ex.Message);
}
return(View());
}
public List <object> GetShapes(ISheet sheet)
{
List <object> list = new List <object>();
IDrawing drawingPatriarch = sheet.DrawingPatriarch;
if (sheet is HSSFSheet)
{
HSSFShapeContainer hSSFShapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
if (drawingPatriarch == null)
{
return(list);
}
IList <HSSFShape> children = hSSFShapeContainer.Children;
using (IEnumerator <HSSFShape> enumerator = children.GetEnumerator())
{
while (enumerator.MoveNext())
{
HSSFShape current = enumerator.Current;
if (current != null && current.Anchor is HSSFClientAnchor)
{
HSSFClientAnchor hSSFClientAnchor = current.Anchor as HSSFClientAnchor;
if (this.IsInternalOrIntersect(this.ShapeRange.MinRow, this.ShapeRange.MaxRow, this.ShapeRange.MinColumn, this.ShapeRange.MaxColumn, hSSFClientAnchor.Row1, hSSFClientAnchor.Row2, hSSFClientAnchor.Col1, hSSFClientAnchor.Col2, true))
{
list.Add(current);
}
}
}
return(list);
}
}
List <POIXMLDocumentPart> relations = (sheet as XSSFSheet).GetRelations();
foreach (POIXMLDocumentPart current2 in relations)
{
if (current2 is XSSFDrawing)
{
XSSFDrawing xSSFDrawing = (XSSFDrawing)current2;
List <XSSFShape> shapes = xSSFDrawing.GetShapes();
foreach (XSSFShape current3 in shapes)
{
XSSFAnchor anchor = current3.GetAnchor();
if (current3 != null && anchor is XSSFClientAnchor)
{
XSSFClientAnchor xSSFClientAnchor = anchor as XSSFClientAnchor;
if (this.IsInternalOrIntersect(this.ShapeRange.MinRow, this.ShapeRange.MaxRow, this.ShapeRange.MinColumn, this.ShapeRange.MaxColumn, xSSFClientAnchor.Row1, xSSFClientAnchor.Row2, xSSFClientAnchor.Col1, xSSFClientAnchor.Col2, true))
{
list.Add(current3);
}
}
}
}
}
return(list);
}
public void Resize()
{
XSSFWorkbook wb = XSSFITestDataProvider.instance.OpenSampleWorkbook("resize_Compare.xlsx") as XSSFWorkbook;
XSSFDrawing dp = wb.GetSheetAt(0).CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> pics = dp.GetShapes();
XSSFPicture inpPic = (XSSFPicture)pics[(0)];
XSSFPicture cmpPic = (XSSFPicture)pics[(0)];
BaseTestResize(inpPic, cmpPic, 2.0, 2.0);
wb.Close();
}
/// <summary>
/// XLSX类型Excle文件添加图片
/// </summary>
/// <param name="intRowIndex">插入图片的行数</param>
/// <param name="pictureIndex">图片的顺序</param>
private void XSSFCreatePicture(int intRowIndex, int pictureIndex)
{
XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.Row1 = intRowIndex;
//anchor.AnchorType = AnchorType2;
XSSFPicture picture = (XSSFPicture)xssfDrawing.CreatePicture(anchor, pictureIndex);
picture.LineStyle = LineStyle.Solid;
picture.Resize();//显示图片的原始尺寸
}
private NPOI.SS.UserModel.IComment GetComment(ISheet sheet, string result)
{
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
NPOI.SS.UserModel.IComment comment1 = drawing.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 10, 3, 12, 6));
// set text in the comment
comment1.String = (new XSSFRichTextString(result));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("IVM");
return(comment1);
}
/// <summary>
/// 将Base64字符串转换为图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="bmp">图片</param>
/// <param name="col1">图片起始列</param>
/// <param name="row1">图片起始行</param>
/// <param name="col2">图片结束列</param>
/// <param name="row2">图片结束行</param>
/// <returns></returns>
public static void AddImageToExcel(ISheet sheet, Bitmap bmp, int col1, int row1, int col2, int row2)
{
string base64 = ImgToBase64String(bmp);
byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+"));
int pictureIdx = sheet.Workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//##处理照片位置，【图片左上角为（6, 2）第2+1行6+1列，右下角为（8, 6）第6+1行8+1列】
XSSFClientAnchor anchor = new XSSFClientAnchor(100 * 10000, 0, 100, 100, col1, row1, col2, row2);
patriarch.CreatePicture(anchor, pictureIdx);
}
private void setPic(XSSFWorkbook workbook, XSSFDrawing patriarch, string path, ISheet sheet, int rowline, int col, int rowMergerPlus = 0, int colMergerPlus = 0)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, rowline, col + colMergerPlus, rowline + rowMergerPlus);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
public void TestAccessMethods()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30) as XSSFClientAnchor;
XSSFChart chart = Drawing.CreateChart(anchor) as XSSFChart;
XSSFCategoryAxis axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom) as XSSFCategoryAxis;
axis.Crosses = (AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void multiRelationShips()
{
XSSFWorkbook wb = new XSSFWorkbook();
byte[] pic1Data = Encoding.UTF8.GetBytes("test jpeg data");
byte[] pic2Data = Encoding.UTF8.GetBytes("test png data");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(0, pictures.Count);
int pic1 = wb.AddPicture(pic1Data, XSSFWorkbook.PICTURE_TYPE_JPEG);
int pic2 = wb.AddPicture(pic2Data, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFSheet sheet1 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape1 = drawing1.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
XSSFPicture shape2 = drawing1.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFSheet sheet2 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape3 = drawing2.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFPicture shape4 = drawing2.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
Assert.AreEqual(2, pictures.Count);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(2, pictures.Count);
sheet1 = wb.GetSheetAt(0) as XSSFSheet;
drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape11 = (XSSFPicture)drawing1.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape1.PictureData.Data, shape11.PictureData.Data));
XSSFPicture shape22 = (XSSFPicture)drawing1.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape2.PictureData.Data, shape22.PictureData.Data));
sheet2 = wb.GetSheetAt(1) as XSSFSheet;
drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape33 = (XSSFPicture)drawing2.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape3.PictureData.Data, shape33.PictureData.Data));
XSSFPicture shape44 = (XSSFPicture)drawing2.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape4.PictureData.Data, shape44.PictureData.Data));
}
public void TestGetChartAxisBug57362()
{
//Load existing excel with some chart on it having primary and secondary axis.
IWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("57362.xlsx");
ISheet sh = workbook.GetSheetAt(0);
XSSFSheet xsh = (XSSFSheet)sh;
XSSFDrawing Drawing = xsh.CreateDrawingPatriarch() as XSSFDrawing;
XSSFChart chart = Drawing.GetCharts()[(0)];
List <IChartAxis> axisList = chart.GetAxis();
Assert.AreEqual(4, axisList.Count);
Assert.IsNotNull(axisList[(0)]);
Assert.IsNotNull(axisList[(1)]);
Assert.IsNotNull(axisList[(2)]);
Assert.IsNotNull(axisList[(3)]);
}
/// <summary>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 创建标题批注
/// </summary>
/// <param name="sheet">表单</param>
/// <param name="titleRow">行</param>
/// <param name="header"></param>
/// <param name="commnet"></param>
private void createTitleComment(ISheet sheet, IRow titleRow)
{
ICell newCellHeader = titleRow.CreateCell(0);
newCellHeader.SetCellValue(header);
newCellHeader.CellStyle = TitleStyle;
if (string.IsNullOrEmpty(this.header) || string.IsNullOrEmpty(this.commnet))
{
return;
}
XSSFDrawing xdraw = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFComment commnet = (XSSFComment)xdraw.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
commnet.SetString(this.commnet);
newCellHeader.CellComment = commnet;
}
public void TestXSSFSimpleShapeCausesNPE56514()
{
XSSFWorkbook wb1 = XSSFTestDataSamples.OpenSampleWorkbook("56514.xlsx");
XSSFSheet sheet = wb1.GetSheetAt(0) as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
wb2.Close();
}
/**
* Gets the first chart from the named sheet in the workbook.
*/
private XSSFChart GetChartFromWorkbook(IWorkbook wb, String sheetName)
{
ISheet sheet = wb.GetSheet(sheetName);
if (sheet is XSSFSheet)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
XSSFDrawing drawing = xsheet.CreateDrawingPatriarch() as XSSFDrawing;
if (drawing != null)
{
List <XSSFChart> charts = drawing.GetCharts();
if (charts != null && charts.Count > 0)
{
return(charts[0]);
}
}
}
return(null);
}
static void CreateChart(ISheet sheet)
{
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 0, 10, 15);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
IBarChartData <string, double> data = chart.ChartDataFactory.CreateBarChartData <string, double>();
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
bottomAxis.MajorTickMark = AxisTickMark.None;
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
leftAxis.SetCrossBetween(AxisCrossBetween.Between);
IChartDataSource <string> categories = DataSources.FromStringCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 0, 0));
// Abono
IChartDataSource <double> xAbono = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 1, 1));
// Isento
IChartDataSource <double> xIsento = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 2, 2));
// Violação
IChartDataSource <double> xViolacao = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 3, 3));
var s1 = data.AddSeries(categories, xAbono);
s1.SetTitle("Abono");
var s2 = data.AddSeries(categories, xIsento);
s2.SetTitle("Isento");
var s3 = data.AddSeries(categories, xViolacao);
s3.SetTitle("Violação");
chart.Plot(data, bottomAxis, leftAxis);
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
//the sheet has one relationship and it is XSSFDrawing
List <RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
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);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFPicture);
Assert.IsTrue(shapes[(1)] is XSSFPicture);
Assert.IsTrue(shapes[(2)] is XSSFPicture);
Assert.IsTrue(shapes[(3)] is XSSFPicture);
Assert.IsTrue(shapes[(4)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(5)] is XSSFPicture);
foreach (XSSFShape sh in shapes)
{
Assert.IsNotNull(sh.GetAnchor());
}
checkRewrite(wb);
wb.Close();
}
public void TestAddChartsToNewWorkbook()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
XSSFDrawing d1 = (XSSFDrawing)s1.CreateDrawingPatriarch();
XSSFClientAnchor a1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart c1 = (XSSFChart)d1.CreateChart(a1);
Assert.AreEqual(1, d1.GetCharts().Count);
Assert.IsNotNull(c1.GetGraphicFrame());
Assert.IsNotNull(c1.GetOrCreateLegend());
XSSFClientAnchor a2 = new XSSFClientAnchor(0, 0, 0, 0, 1, 11, 10, 60);
XSSFChart c2 = (XSSFChart)d1.CreateChart(a2);
Assert.IsNotNull(c2);
Assert.AreEqual(2, d1.GetCharts().Count);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
