public static int LoadImage(string path, HSSFWorkbook wb) { FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, (int)file.Length); return wb.AddPicture(buffer, PictureType.JPEG); }
public void Test44916() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet(); // 1. Create drawing patriarch IDrawing patr = sheet.CreateDrawingPatriarch(); // 2. Try to re-get the patriarch IDrawing existingPatr; try { existingPatr = sheet.DrawingPatriarch; } catch (NullReferenceException) { throw new AssertionException("Identified bug 44916"); } // 3. Use patriarch HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2); anchor.AnchorType = (AnchorType)(3); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); patr.CreatePicture(anchor, idx1); // 4. Try to re-use patriarch later existingPatr = sheet.DrawingPatriarch; Assert.IsNotNull(existingPatr); }
public void Test45829() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet(); IDrawing p1 = sh1.CreateDrawingPatriarch(); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("45829.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); IPicture pic = p1.CreatePicture(new HSSFClientAnchor(), idx1); pic.Resize(); }
public void CopyTo(HSSFWorkbook dest, String name, Boolean copyStyle, Boolean keepFormulas) { int maxColumnNum = 0; HSSFSheet newSheet = (HSSFSheet)dest.CreateSheet(name); newSheet._sheet = Sheet.CloneSheet(); Dictionary<short, short> paletteMap = new Dictionary<short, short>(); if (dest.NumberOfSheets == 1) { //Replace the color palette with the palette from the source, since this is the first sheet dest.Workbook.CustomPalette.ClearColors(); paletteMap = MergePalettes(Workbook as HSSFWorkbook, dest); } else if (dest != Workbook) { paletteMap = MergePalettes(Workbook as HSSFWorkbook, dest); } IDictionary<Int32, HSSFCellStyle> styleMap = (copyStyle) ? new Dictionary<Int32, HSSFCellStyle>() : null; for (int i = FirstRowNum; i <= LastRowNum; i++) { HSSFRow srcRow = (HSSFRow)GetRow(i); HSSFRow destRow = (HSSFRow)newSheet.CreateRow(i); if (srcRow != null) { CopyRow(this, newSheet, srcRow, destRow, styleMap, paletteMap, keepFormulas); if (srcRow.LastCellNum > maxColumnNum) { maxColumnNum = srcRow.LastCellNum; } } } for (int i = 0; i < maxColumnNum; i++) { newSheet.SetColumnWidth(i, GetColumnWidth(i)); } newSheet.ForceFormulaRecalculation = true; newSheet.PrintSetup.Landscape = PrintSetup.Landscape; newSheet.PrintSetup.HResolution = PrintSetup.HResolution; newSheet.PrintSetup.VResolution = PrintSetup.VResolution; newSheet.SetMargin(MarginType.LeftMargin, GetMargin(MarginType.LeftMargin)); newSheet.SetMargin(MarginType.RightMargin, GetMargin(MarginType.RightMargin)); newSheet.SetMargin(MarginType.TopMargin, GetMargin(MarginType.TopMargin)); newSheet.SetMargin(MarginType.BottomMargin, GetMargin(MarginType.BottomMargin)); newSheet.PrintSetup.HeaderMargin = PrintSetup.HeaderMargin; newSheet.PrintSetup.FooterMargin = PrintSetup.FooterMargin; newSheet.Header.Left = Header.Left; newSheet.Header.Center = Header.Center; newSheet.Header.Right = Header.Right; newSheet.Footer.Left = Footer.Left; newSheet.Footer.Center = Footer.Center; newSheet.Footer.Right = Footer.Right; newSheet.PrintSetup.Scale = PrintSetup.Scale; newSheet.PrintSetup.FitHeight = PrintSetup.FitHeight; newSheet.PrintSetup.FitWidth = PrintSetup.FitWidth; EscherAggregate escher = DrawingEscherAggregate; if (escher != null) { if (dest.Workbook.DrawingManager == null) { dest.Workbook.CreateDrawingGroup(); } EscherAggregate destEscher = newSheet.DrawingEscherAggregate; //Note: This logic assumes that image id's go from 1 to N in the source document. It usually does //Note: This logic assumes that no images are shared between sheets of the source document. If they //are and you're copying multiple sheets, the file may be larger than expected due to duplicates. IEnumerable<int> usedImages = FindUsedPictures(escher.EscherRecords); Dictionary<int,int> remap = new Dictionary<int, int>(); IList pics = Workbook.GetAllPictures(); foreach (int imgId in usedImages) { if (imgId <= pics.Count) { HSSFPictureData pic = (HSSFPictureData)pics[imgId - 1]; int dstIdx = dest.AddPicture(pic.Data, (PictureType)pic.Format); remap.Add(imgId, dstIdx); } } //Apply the new image Id's the destination foreach (EscherRecord escherRecord in destEscher.EscherRecords) { ApplyEscherRemap(escherRecord, remap); } } }
public void TestAddPictures() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet("Pictures"); IDrawing dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); IPicture p1 = dr.CreatePicture(anchor, idx1); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data)); // register another one byte[] data2 = new byte[] { 4, 5, 6 }; int idx2 = wb.AddPicture(data2, PictureType.JPEG); Assert.AreEqual(2, idx2); IPicture p2 = dr.CreatePicture(anchor, idx2); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data)); // confirm that HSSFPatriarch.Children returns two picture shapes Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // Write, read back and verify that our pictures are there wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst2 = wb.GetAllPictures(); Assert.AreEqual(2, lst2.Count); Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data)); // confirm that the pictures are in the Sheet's Drawing sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // add a third picture byte[] data3 = new byte[] { 7, 8, 9 }; // picture index must increment across Write-read int idx3 = wb.AddPicture(data3, PictureType.JPEG); Assert.AreEqual(3, idx3); IPicture p3 = dr.CreatePicture(anchor, idx3); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data)); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); // write and read again wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst3 = wb.GetAllPictures(); // all three should be there Assert.AreEqual(3, lst3.Count); Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); // forth picture byte[] data4 = new byte[] { 10, 11, 12 }; int idx4 = wb.AddPicture(data4, PictureType.JPEG); Assert.AreEqual(4, idx4); dr.CreatePicture(anchor, idx4); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst4 = wb.GetAllPictures(); Assert.AreEqual(4, lst4.Count); Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); }
public void Test53432() { IWorkbook wb = new HSSFWorkbook(); //or new HSSFWorkbook(); wb.AddPicture(new byte[] { 123, 22 }, PictureType.JPEG); Assert.AreEqual(wb.GetAllPictures().Count, 1); wb = new HSSFWorkbook(); wb = WriteOutAndReadBack((HSSFWorkbook)wb); Assert.AreEqual(wb.GetAllPictures().Count, 0); wb.AddPicture(new byte[] { 123, 22 }, PictureType.JPEG); Assert.AreEqual(wb.GetAllPictures().Count, 1); wb = WriteOutAndReadBack((HSSFWorkbook)wb); Assert.AreEqual(wb.GetAllPictures().Count, 1); }
public void TestRemoveShapes() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor()); rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE; int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG); patriarch.CreatePicture(new HSSFClientAnchor(), idx); patriarch.CreateCellComment(new HSSFClientAnchor()); HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor()); polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 }); patriarch.CreateTextbox(new HSSFClientAnchor()); HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor()); group.CreateTextbox(new HSSFChildAnchor()); group.CreatePicture(new HSSFChildAnchor(), idx); Assert.AreEqual(patriarch.Children.Count, 6); Assert.AreEqual(group.Children.Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 6); group = (HSSFShapeGroup)patriarch.Children[5]; group.RemoveShape(group.Children[0]); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); group = (HSSFShapeGroup)patriarch.Children[(5)]; patriarch.RemoveShape(group); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 5); HSSFShape shape = patriarch.Children[0]; patriarch.RemoveShape(shape); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 4); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 4); HSSFPicture picture = (HSSFPicture)patriarch.Children[0]; patriarch.RemoveShape(picture); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 3); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 3); HSSFComment comment = (HSSFComment)patriarch.Children[0]; patriarch.RemoveShape(comment); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 2); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 2); polygon = (HSSFPolygon)patriarch.Children[0]; patriarch.RemoveShape(polygon); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 1); HSSFTextbox textbox = (HSSFTextbox)patriarch.Children[0]; patriarch.RemoveShape(textbox); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); }
public void TestSetGetProperties() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet; HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFClientAnchor anchor = new HSSFClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture; Assert.AreEqual(p1.FileName, ""); p1.FileName = ("aaa"); Assert.AreEqual(p1.FileName, "aaa"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wb.GetSheet("Pictures") as HSSFSheet; dr = sh.DrawingPatriarch as HSSFPatriarch; p1 = (HSSFPicture)dr.Children[0]; Assert.AreEqual(p1.FileName, "aaa"); }
public void TestBSEPictureRef() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet; HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFClientAnchor anchor = new HSSFClientAnchor(); InternalSheet ish = HSSFTestHelper.GetSheetForTest(sh); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture; EscherBSERecord bse = wb.Workbook.GetBSERecord(idx1); Assert.AreEqual(bse.Ref, 1); dr.CreatePicture(new HSSFClientAnchor(), idx1); Assert.AreEqual(bse.Ref, 2); HSSFShapeGroup gr = dr.CreateGroup(new HSSFClientAnchor()); gr.CreatePicture(new HSSFChildAnchor(), idx1); Assert.AreEqual(bse.Ref, 3); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText = "统计数据", string strImageFileName = "") { IWorkbook workbook = new HSSFWorkbook(); #region 填充图片 if (strImageFileName != "") { ISheet sheet1 = workbook.CreateSheet("图标"); byte[] bytes = System.IO.File.ReadAllBytes(strImageFileName); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch(); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 20, 20); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //rowIndex += 20; } #endregion if (strHeaderText == "") { strHeaderText = "统计数据"; } ISheet sheet = workbook.CreateSheet(strHeaderText); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 50 ? 50 : arrColWidth[item.Ordinal]; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j] && intTemp < 50) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } if (strHeaderText != "") { #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion rowIndex = 1; } else { rowIndex = 0; } #region 列头及样式 { IRow headerRow = sheet.CreateRow(rowIndex); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex++; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return ms; } }
public void TestAddShapesToGroup() { HSSFWorkbook wb = new HSSFWorkbook(); // create a sheet with a text box HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor()); int index = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG); group.CreatePicture(new HSSFChildAnchor(), index); HSSFPolygon polygon = group.CreatePolygon(new HSSFChildAnchor()); polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 }); group.CreateTextbox(new HSSFChildAnchor()); group.CreateShape(new HSSFChildAnchor()); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(1, patriarch.Children.Count); Assert.IsTrue(patriarch.Children[0] is HSSFShapeGroup); group = (HSSFShapeGroup)patriarch.Children[0]; Assert.AreEqual(group.Children.Count, 4); Assert.IsTrue(group.Children[0] is HSSFPicture); Assert.IsTrue(group.Children[1] is HSSFPolygon); Assert.IsTrue(group.Children[2] is HSSFTextbox); Assert.IsTrue(group.Children[3] is HSSFSimpleShape); HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor()); index = wb.AddPicture(new byte[] { 2, 2, 2 }, PictureType.JPEG); group2.CreatePicture(new HSSFChildAnchor(), index); polygon = group2.CreatePolygon(new HSSFChildAnchor()); polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 }); group2.CreateTextbox(new HSSFChildAnchor()); group2.CreateShape(new HSSFChildAnchor()); group2.CreateShape(new HSSFChildAnchor()); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(2, patriarch.Children.Count); group = (HSSFShapeGroup)patriarch.Children[1]; Assert.AreEqual(group.Children.Count, 5); Assert.IsTrue(group.Children[0] is HSSFPicture); Assert.IsTrue(group.Children[1] is HSSFPolygon); Assert.IsTrue(group.Children[2] is HSSFTextbox); Assert.IsTrue(group.Children[3] is HSSFSimpleShape); Assert.IsTrue(group.Children[4] is HSSFSimpleShape); int shapeid = group.ShapeId; }
public void TestAddToExistingFile() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.PNG); HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.Column = (5); comment.String = new HSSFRichTextString("comment1"); comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)10, 10)) as HSSFComment; comment.Row = (5); comment.String = new HSSFRichTextString("comment2"); comment.SetBackgroundImage(idx); Assert.AreEqual(comment.GetBackgroundImageId(), idx); Assert.AreEqual(patriarch.Children.Count, 2); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wb.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[(1)]; Assert.AreEqual(comment.GetBackgroundImageId(), idx); comment.ResetBackgroundImage(); Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 2); comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.String = new HSSFRichTextString("comment3"); Assert.AreEqual(patriarch.Children.Count, 3); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wb.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[1]; Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 3); Assert.AreEqual(((HSSFComment)patriarch.Children[0]).String.String, "comment1"); Assert.AreEqual(((HSSFComment)patriarch.Children[1]).String.String, "comment2"); Assert.AreEqual(((HSSFComment)patriarch.Children[2]).String.String, "comment3"); }
public void Bug49658() { // test if inserted EscherMetafileBlip will be read again IWorkbook wb = new HSSFWorkbook(); byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf"); int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF); byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png"); int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG); byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf"); int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; ICreationHelper ch = wb.GetCreationHelper(); IClientAnchor anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/2); anchor.Col2 = (/*setter*/5); anchor.Row1 = (/*setter*/1); anchor.Row2 = (/*setter*/6); patriarch.CreatePicture(anchor, indexEmf); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/2); anchor.Col2 = (/*setter*/5); anchor.Row1 = (/*setter*/10); anchor.Row2 = (/*setter*/16); patriarch.CreatePicture(anchor, indexPng); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/6); anchor.Col2 = (/*setter*/9); anchor.Row1 = (/*setter*/1); anchor.Row2 = (/*setter*/6); patriarch.CreatePicture(anchor, indexWmf); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut)); byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22]; Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22); pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut)); }
public void ExportImg2Xls(System.Web.HttpResponse Response, byte[] imgBytes, string filename,int imgHeight,int imgWidth) { //byte[] bytes = System.IO.File.ReadAllBytes(@"E:\mineown\mine\TrainerEvaluate20140929\untitled.png"); hssfworkbook=new HSSFWorkbook(); // File.WriteAllBytes(@"d:\11.png",imgBytes); int pictureIdx = hssfworkbook.AddPicture(imgBytes, PictureType.PNG); //create sheet var sheet = hssfworkbook.CreateSheet("课程满意度分布"); // Create the drawing patriarch. This is the top level container for all shapes. var patriarch = sheet.CreateDrawingPatriarch(); //add a picture var anchor = new HSSFClientAnchor(0, 0,1023, 255, 0, 0,10, 18); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(1); Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", HttpUtility.UrlPathEncode(filename))); MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); file.WriteTo(Response.OutputStream); Response.End(); }
/// <summary> /// 在指定位置插入图片,跨Column。ColumnIndex2需大于或等于ColumnIndex1 /// </summary> public static void WirtePic(HSSFWorkbook workbook, HSSFSheet sheet, HSSFPatriarch patriarch, int columnIndex1, int columnIndex2, int rowIndex, string picPath) { if (!File.Exists(picPath)) { return; } if (columnIndex2 < columnIndex1) { throw new Exception("ColumnIndex2需大于或等于ColumnIndex1"); } int dx2 = 1023; int dy2 = 255; HSSFRow row = sheet.GetRow(rowIndex); if (row != null) { int cWidth = 0; for (int i = columnIndex1; i < columnIndex2; i++) { cWidth += sheet.GetColumnWidth(columnIndex1); } using (Image img = Image.FromFile(picPath)) { double w = cWidth * 7.0 / 256.0; double h = (row.Height * 1.32 / 20.0); if (((double)img.Width / (double)img.Height) > (cWidth * 7.0 / 256 / (row.Height * 1.32 / 20))) { double h1 = w * img.Height / img.Width; dy2 = (int)(h1 * 255 / h); if (dy2 < 0) dy2 = 0; if (dy2 > 255) dy2 = 255; } else { double w1 = h * img.Width / img.Height; dx2 = (int)(w1 * 1023 / w); if (dy2 < 0) dy2 = 0; if (dy2 > 1023) dy2 = 1023; } } } HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, dx2, dy2, columnIndex1, rowIndex, columnIndex2, rowIndex); byte[] buff = File.ReadAllBytes(picPath); int pic = workbook.AddPicture(buff, HSSFWorkbook.PICTURE_TYPE_JPEG); anchor.AnchorType = 2; patriarch.CreatePicture(anchor, pic); }
public void TestReallyEmbedSomething() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); byte[] picturePPT = POIDataSamples.GetSlideShowInstance().ReadFile("clock.jpg"); int imgIdx = wb.AddPicture(pictureData, PictureType.PNG); POIFSFileSystem pptPoifs = GetSamplePPT(); int pptIdx = wb.AddOlePackage(pptPoifs, "Sample-PPT", "sample.ppt", "sample.ppt"); POIFSFileSystem xlsPoifs = GetSampleXLS(); int imgPPT = wb.AddPicture(picturePPT, PictureType.JPEG); int xlsIdx = wb.AddOlePackage(xlsPoifs, "Sample-XLS", "sample.xls", "sample.xls"); int txtIdx = wb.AddOlePackage(GetSampleTXT(), "Sample-TXT", "sample.txt", "sample.txt"); int rowoffset = 5; int coloffset = 5; ICreationHelper ch = wb.GetCreationHelper(); HSSFClientAnchor anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(2 + coloffset), 1 + rowoffset, 0, 0, (short)(3 + coloffset), 5 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, pptIdx, imgPPT); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(5 + coloffset), 1 + rowoffset, 0, 0, (short)(6 + coloffset), 5 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, xlsIdx, imgIdx); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(3 + coloffset), 10 + rowoffset, 0, 0, (short)(5 + coloffset), 11 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, txtIdx, imgIdx); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(1 + coloffset), -2 + rowoffset, 0, 0, (short)(7 + coloffset), 14 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); HSSFSimpleShape circle = patriarch.CreateSimpleShape(anchor); circle.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_OVAL); circle.IsNoFill = (/*setter*/true); if (false) { FileStream fos = new FileStream("embed.xls", FileMode.Create); wb.Write(fos); fos.Close(); } wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); MemoryStream bos = new MemoryStream(); HSSFObjectData od = wb.GetAllEmbeddedObjects()[0]; Ole10Native ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); bos = new MemoryStream(); pptPoifs.WriteFileSystem(bos); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray())); od = wb.GetAllEmbeddedObjects()[1]; ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); bos = new MemoryStream(); xlsPoifs.WriteFileSystem(bos); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray())); od = wb.GetAllEmbeddedObjects()[2]; ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, GetSampleTXT())); }