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!"; }
private static void CreateChartDataSheet(Stream stream, IWorkbook workbook) { var sheet = workbook.CreateSheet("Chart"); var drawing = sheet.CreateDrawingPatriarch(); var data = new byte[stream.Length]; stream.Read(data, 0, (int)stream.Length); var picInd = workbook.AddPicture(data, PictureType.PNG); XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper; XSSFClientAnchor anchor = helper?.CreateClientAnchor() as XSSFClientAnchor; if (anchor != null) { anchor.Col1 = 0; anchor.Row1 = 0; var pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture; pict?.Resize(); } }
public IClientAnchor CreateClientAnchor() { return(helper.CreateClientAnchor()); }
public void TestCommentShowsBox() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet(); XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0); XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper(); XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 3; anchor.Row1 = cell.RowIndex; anchor.Row2 = cell.RowIndex + 5; XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor); XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment"); comment.String = str; cell.CellComment = comment; XSSFVMLDrawing vml = sheet.GetVMLDrawing(false); CT_Shapetype shapetype = null; ArrayList items = vml.GetItems(); foreach (object o in items) { if (o is CT_Shapetype) { shapetype = (CT_Shapetype)o; } } Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked); Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled); using (MemoryStream ws = new MemoryStream()) { wb.Write(ws); using (MemoryStream rs = new MemoryStream(ws.GetBuffer())) { wb = new XSSFWorkbook(rs); sheet = (XSSFSheet)wb.GetSheetAt(0); vml = sheet.GetVMLDrawing(false); shapetype = null; items = vml.GetItems(); foreach (object o in items) { if (o is CT_Shapetype) { shapetype = (CT_Shapetype)o; } } //wb.Write(new FileStream("comments.xlsx", FileMode.Create)); //using (MemoryStream ws2 = new MemoryStream()) //{ // vml.Write(ws2); // throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer())); //} Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked); Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled); } } }