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()));
}
