public DataTable CreateFromXsl(Stream xslFileStream, bool onlyCreateSchedule, out IList allPictures)
{
HSSFWorkbook book = new HSSFWorkbook(xslFileStream);
allPictures = book.GetAllPictures();
var sheet = book.GetSheetAt(0);
DataTable dt = new DataTable();
var row = sheet.GetRow(1);
foreach (var cell in row.Cells)
{
DataColumn col = new DataColumn(cell.ToString(), typeof(String));
// dt.Columns.Add(col);
}
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 < 2) continue;
//防止其遍历到没有数据的row
if (currentRow.LastCellNum < row.Cells.Count)
{
// 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;
}
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 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 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 DataTable Read(out string errMsg)
{
StringBuilder sbErrMsg = new StringBuilder();
HSSFWorkbook book = new HSSFWorkbook(InputStream);
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;
}
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));
}
//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;
}
