public void Test49658()
{
// 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 TestMacPicture()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("53446.xls");
List <HSSFPictureData> lst = (List <HSSFPictureData>)wb.GetAllPictures();
Assert.AreEqual(1, lst.Count);
HSSFPictureData pict = lst[(0)];
String ext = pict.SuggestFileExtension();
if (!ext.Equals("png"))
{
Assert.Fail("Expected a PNG.");
}
//try to read image data using javax.imageio.* (JDK 1.4+)
byte[] data = pict.Data;
using (MemoryStream ms = new MemoryStream(data))
{
Image png = Image.Load(ms);
Assert.IsNotNull(png);
Assert.AreEqual(78, png.Width);
Assert.AreEqual(76, png.Height);
Assert.AreEqual((int)PictureType.PNG, pict.Format);
Assert.AreEqual("image/png", pict.MimeType);
}
}
public void TestPictures()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithImages.xls");
IList lst = wb.GetAllPictures();
//Assert.AreEqual(2, lst.Count);
for (IEnumerator it = lst.GetEnumerator(); it.MoveNext();)
{
HSSFPictureData pict = (HSSFPictureData)it.Current;
String ext = pict.SuggestFileExtension();
byte[] data = pict.Data;
if (ext.Equals("jpeg"))
{
//try to read image data using javax.imageio.* (JDK 1.4+)
Image jpg = Image.Load(new MemoryStream(data));
Assert.IsNotNull(jpg);
Assert.AreEqual(192, jpg.Width);
Assert.AreEqual(176, jpg.Height);
}
else if (ext.Equals("png"))
{
//try to read image data using javax.imageio.* (JDK 1.4+)
Image png = Image.Load(new MemoryStream(data));
Assert.IsNotNull(png);
Assert.AreEqual(300, png.Width);
Assert.AreEqual(300, png.Height);
}
else
{
//TODO: Test code for PICT, WMF and EMF
}
}
}
static void Main(string[] args)
{
try
{
string itemFile = @"D:\ADMIN_LUUKHO.xls";
// Opening the Excel template...
FileStream fs = new FileStream(itemFile, FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.GetSheet("例");
var lst = templateWorkbook.GetAllPictures();
int i = 0;
foreach (var item in lst)
{
var pic = (HSSFPictureData)item;
byte[] data = pic.Data;
BinaryWriter writer = new BinaryWriter(File.OpenWrite(String.Format("{0}.jpeg", i)));
writer.Write(data);
writer.Flush();
writer.Close();
i++;
}
}
catch { }
}
public void TestImageCopy()
{
HSSFWorkbook srcBook = HSSFTestDataSamples.OpenSampleWorkbook("Images.xls");
HSSFWorkbook destBook = new HSSFWorkbook();
HSSFSheet sheet1 = srcBook.GetSheetAt(0) as HSSFSheet;
sheet1.CopyTo(destBook, "First Sheet", true, true);
using (MemoryStream ms = new MemoryStream())
{
destBook.Write(ms);
ms.Position = 0;
HSSFWorkbook sanityCheck = new HSSFWorkbook(ms);
//Assert that only one image got copied, because only one image was used on the first page
Assert.IsTrue(sanityCheck.GetAllPictures().Count == 1);
}
HSSFSheet sheet2 = srcBook.GetSheetAt(1) as HSSFSheet;
sheet2.CopyTo(destBook, "Second Sheet", true, true);
using (MemoryStream ms = new MemoryStream())
{
destBook.Write(ms);
ms.Position = 0;
HSSFWorkbook sanityCheck = new HSSFWorkbook(ms);
//2nd sheet copied, make sure we have two images now, because sheet 2 had one image
Assert.IsTrue(sanityCheck.GetAllPictures().Count == 2);
}
}
public void TestEmbeding()
{
// This used to break, until bug #43116 was fixed
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("ole2-embedding.xls");
// Check we can get at the Escher layer still
workbook.GetAllPictures();
}
public void deletePicture(int k, int i)
{
if (wb == null)
{
return;
}
i = i - 1;
ISheet sheet = wb.GetSheetAt(k);
//sheet.GetAllPictures();
System.Collections.IList pictures = wb.GetAllPictures();
int w = pictures.Count;
Console.WriteLine("Count=" + w);
pictures.Remove(i);
//foreach (HSSFPictureData pic in pictures)
//{
// pic.
//}
}
static void Main(string[] args)
{
InitializeWorkbook();
IList pictures = hssfworkbook.GetAllPictures();
int i = 0;
foreach (HSSFPictureData pic in pictures)
{
string ext = pic.SuggestFileExtension();
if (ext.Equals("jpeg"))
{
Image jpg = Image.FromStream(new MemoryStream(pic.Data));
jpg.Save(string.Format("pic{0}.jpg", i++));
}
else if (ext.Equals("png"))
{
Image png = Image.FromStream(new MemoryStream(pic.Data));
png.Save(string.Format("pic{0}.png", i++));
}
}
}
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));
}
//static void LoadDocumentPart(HWPFDocument hwpf,TreeNode parent,string partName,FieldsDocumentPart part)
//{
// TreeNode mainNode = new TreeNode(partName);
// mainNode.ImageKey = "Folder";
// mainNode.SelectedImageKey = "Folder";
// parent.Nodes.Add(mainNode);
// Fields fields = hwpf.GetFields();
// foreach (Field field in fields.GetFields(part))
// {
// TreeNode tn = new TreeNode("Field");
// mainNode.Nodes.Add(tn);
// }
//}
static void HandleWorkbook(TreeNode treeNode, HSSFWorkbook hssfworkbook)
{
if (hssfworkbook.NumberOfSheets > 0)
{
treeNode.ImageKey = "Folder";
treeNode.SelectedImageKey = "Folder";
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
string sheettext = string.Format("Sheet {0}", i + 1);
TreeNode sheetnode =
treeNode.Nodes.Add(sheettext, sheettext, "Folder", "Folder");
HSSFSheet hssfsheet = ((HSSFSheet)hssfworkbook.GetSheetAt(i));
EscherAggregate ea = hssfsheet.DrawingEscherAggregate;
IEnumerator iterator1 = hssfsheet.Sheet.Records.GetEnumerator();
while (iterator1.MoveNext())
{
if (iterator1.Current is Record)
{
Record record = (Record)iterator1.Current;
sheetnode.Nodes.Add(new RecordTreeNode(record));
}
else if (iterator1.Current is RecordAggregate)
{
RecordAggregate record = (RecordAggregate)iterator1.Current;
sheetnode.Nodes.Add(new RecordAggregateTreeNode(record));
}
}
//RecordTreeNode rtn = new DirectoryTreeNode();
if (ea != null)
{
foreach (EscherRecord er in ea.EscherRecords)
{
sheetnode.Nodes.Add(new EscherRecordTreeNode(er));
}
}
}
}
else
{
treeNode.ImageKey = "Binary";
}
IEnumerator iterator2 = hssfworkbook.Workbook.Records.GetEnumerator();
while (iterator2.MoveNext())
{
if (iterator2.Current is Record) //&& !(iterator2.Current is UnknownRecord))
{
Record record = (Record)iterator2.Current;
if (record is DrawingGroupRecord)
{
hssfworkbook.GetAllPictures();
}
treeNode.Nodes.Add(new RecordTreeNode(record));
}
else if (iterator2.Current is RecordBase)
{
RecordBase record = (RecordBase)iterator2.Current;
treeNode.Nodes.Add(record.GetType().Name);
}
}
}
/// <summary>
/// 读取excel内容,填入DataTable
/// </summary>
/// <param name="startRowIndex">起始行,从0开始.上面的row忽略.</param>
/// <param name="xslFileStream">Excel文件流</param>
/// <param name="onlyCreateSchedule">只创建结构,不填充数据.</param>
/// <param name="allPictures">excel文件内的所图片</param>
/// <returns></returns>
public DataTable CreateFromXsl(Stream xslFileStream, int startRowIndex, bool onlyCreateSchedule, out IList allPictures)
{
HSSFWorkbook book = new HSSFWorkbook(xslFileStream);
allPictures = book.GetAllPictures();
var sheet = book.GetSheetAt(0);
DataTable dt = new DataTable();
//起始行单元格内的值作为datatable的列名.
var row = sheet.GetRow(startRowIndex);
for (int i = 0; i < row.LastCellNum; i++)
{
var columnName = row.GetCell(i);
//空白列导致出错
string strColName = string.Empty;
if (columnName == null)
{
strColName = Guid.NewGuid().ToString();
}
else
{
strColName = columnName.ToString();
}
DataColumn col = new DataColumn(strColName, typeof(String));
dt.Columns.Add(col);
}
if (!onlyCreateSchedule)
{
IEnumerator rowEnumer = sheet.GetRowEnumerator();
while (rowEnumer.MoveNext())
{
var currentRow = (HSSFRow)rowEnumer.Current;
if (currentRow.RowNum < startRowIndex + 1)
{
continue;
}
//防止其遍历到没有数据的row
if (currentRow.LastCellNum < row.Cells.Count)
{
// break;
}
//空白行判断
bool isEnd = true;
foreach (var cell in currentRow.Cells)
{
if (!string.IsNullOrEmpty(NLibrary.StringHelper.ReplaceSpace(cell.StringCellValue)))
{
isEnd = false;
break;
}
}
if (isEnd)
{
break;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
var cell = currentRow.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
private List <EmCostumePhoto> ExcelToImage(string sourcePath, string savepath)
{
List <EmCostumePhoto> listPhotos = new List <EmCostumePhoto>();
try
{
using (FileStream fsReader = File.OpenRead(path))
{
HSSFWorkbook wk = new HSSFWorkbook(fsReader);
IList pictures = wk.GetAllPictures();
int i = 0;
foreach (HSSFPictureData pic in pictures)
{
EmCostumePhoto cutPhoto = new EmCostumePhoto();
//if (pic.Data.Length == 19504) //跳过不需要保存的图片，其中pic.data有图片长度
// continue;
string ext = pic.SuggestFileExtension();//获取扩展名
string path = string.Empty;
byte[] photo = null;
Image img = null;
if (ext.Equals("icon") || ext.Equals("jpeg") || ext.Equals("bmp") || ext.Equals("gif") || ext.Equals("png"))
{
img = Image.FromStream(new MemoryStream(pic.Data)); //从pic.Data数据流创建图片
path = Path.Combine(savepath, string.Format("pic{0}." + ext, i++));
img.Save(path);//保存
photo = pic.Data;
// ShowMessage(pic.Data.Length.ToString());
}
img = JGNet.Core.ImageHelper.GetNewSizeImage(img, 800);
MemoryStream stream = new MemoryStream();
img.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
photo = stream.ToArray();
Image thumbnailImage = img.GetThumbnailImage(64, 64, null, new IntPtr());
EmCostumePhoto emCostumePhoto = null;
String newFileName = JGNet.Core.ImageHelper.GetNewFileName(Path.GetFileName(path));
String savePath = GlobalUtil.EmallDir + newFileName;
Core.ImageHelper.Compress(img, savePath, 50);
path = savePath;
emCostumePhoto = new EmCostumePhoto()
{
Image = img,
SavePath = path,
Bytes = photo,
// CostumeID = this.skinTextBox_ID.Text,
PhotoName = newFileName
};
emCostumePhoto.Image = img;
// cutPhoto.Photo = photo;
listPhotos.Add(emCostumePhoto);;
}
}
}
catch (Exception ex)
{
return(null);
}
return(listPhotos);
}
public DataTable Read(out string errMsg)
{
StringBuilder sbErrMsg = new StringBuilder();
HSSFWorkbook book = new HSSFWorkbook(InputStream);
if (NeedGetAllPictures)
{
AllPictures = book.GetAllPictures();
}
var sheet = book.GetSheetAt(SheetIndex);
DataTable dt = new DataTable();
//起始行单元格内的值作为datatable的列名.
var row = sheet.GetRow(RowNumberBegin);
for (int i = 0; i < row.LastCellNum; i++)
{
var columnName = row.GetCell(i);
//空白列导致出错
string strColName = string.Empty;
if (columnName == null)
{
strColName = Guid.NewGuid().ToString();
}
else
{
strColName = columnName.ToString();
}
DataColumn col = new DataColumn(strColName, typeof(String));
dt.Columns.Add(col);
}
if (!OnlyCreateSchemar)
{
IEnumerator rowEnumer = sheet.GetRowEnumerator();
while (rowEnumer.MoveNext())
{
var currentRow = (HSSFRow)rowEnumer.Current;
if (currentRow.RowNum < RowNumberBegin + 1)
{
continue;
}
//防止其遍历到没有数据的row
if (currentRow.LastCellNum < row.Cells.Count)
{
// break;
}
//空白行判断:如果所有的cell都没有可见数据,则为空白行.
bool isEnd = true;
foreach (var cell in currentRow.Cells)
{
if (!string.IsNullOrEmpty(NLibrary.StringHelper.ReplaceSpace(cell.ToString())))
{
isEnd = false;
break;
}
}
if (isEnd)
{
break;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
var cell = currentRow.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
errMsg = sbErrMsg.ToString();
return(dt);
}
