static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = AnchorType.MoveDontResize;
//load the picture and get the picture index in the workbook
//first picture
int imageId = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
picture.Resize();
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw);
}
}
/// <summary>
/// create xls
/// using NPOI
/// </summary>
/// <param name="svgDocs"></param>
/// <param name="stream"></param>
public static void CreateExcelStreamBySvgs(List <SvgDocument> svgDocs, Stream stream)
{
using (stream)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IDrawing patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
IPicture pic;
IRow row = null;
for (int i = 0; i < svgDocs.Count; i++)
{
using (MemoryStream ms = new MemoryStream())
{
using (System.Drawing.Bitmap image = svgDocs[i].Draw())
{
image.Save(ms, ImageFormat.Bmp);
ms.Seek(0, SeekOrigin.Begin);
int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG);
row = sheet.CreateRow(i);
row.HeightInPoints = convertPixelToPoints(image.Height) + 10;
anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, i, 0, i);
pic = patriarch.CreatePicture(anchor, index);
pic.Resize();
}
}
}
workbook.Write(stream);
}
}
private IWorkbook OpenExcel(String filePath, params Byte[][] imageData)
{
IWorkbook workBook = null;
if (File.Exists(filePath))
{
using (FileStream stream = new FileStream(filePath, FileMode.Open,
FileAccess.ReadWrite, FileShare.ReadWrite, 8096,
FileOptions.WriteThrough))
{
workBook = GenerationWorkBook(filePath, stream);
Int32 index = workBook.AddPicture(imageData[0], PictureType.PNG);
ISheet sheet = workBook.GetSheetAt(0);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
Int32 lastRowNum = sheet.LastRowNum;
IClientAnchor anchor = GenerationClientAnchor(filePath, 0, 0, 0, 0, 1, lastRowNum + 2, 8, lastRowNum + 25);
IPicture pic = patriarch.CreatePicture(anchor, index);
File.Delete(filePath);
using (FileStream newStream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
{
workBook.Write(newStream);
}
}
}
return(workBook);
}
/*泛类型行不通
* public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol)
* // where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧，无法执行类型约束为两个类，因为类的约束必须放在第一个
* {
*
* XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture;
* HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette;
* //XSSFPicture，HSSFPalette是类，只能有一种类型，正好是泛类型要解决的
* //方法和使用一样，但是T的类型取决类申城的Isheet的类型
* //应该使用重载
* if (true)
* {
*
* }
* else
* {
* return null;
* }
* workbook.AddPicture(pictureNPOI.)
* }
*/
//重载
public ISheet pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int startRow, int startCol, int endRow, int endCol)
{
/*将实际图片转换为pictureData时使用，但是pictureNPOI本身就是picture
* byte[] pictureByte=
* workbook.AddPicture(, PictureType.PNG);
*/
//判断是否有sheet
//无，则创建
if (sheet == null)
{
sheet = this.workbook.CreateSheet();
}
//执行向sheet写图片
//创建DrawingPatriarch，存放的容器
IDrawing patriarch = sheet.CreateDrawingPatriarch();
///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。”
/// HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch();
/// 根据报错改为如下
// IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, startCol, startRow, endCol, endRow);
//将图片文件读入workbook，用索引指向该文件
int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);
//根据读入图片和anchor把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
return(sheet);
}
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 = (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);
}
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);
sheet.AddMergedRegion(merge);
var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";
byte[] data = ArquivosUtil.RetornarArquivo(caminho);
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 1;
anchor.Row1 = 1;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/
return(sheet);
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="value">值</param>
public static void SetValue(this ICell cell, object value)
{
if (cell == null)
{
return;
}
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
if (value.GetType().FullName.Equals("System.Byte[]"))
{
var pictureIndex = cell.Sheet.Workbook.AddPicture((byte[])value, PictureType.PNG);
IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColumnSpan;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
IDrawing drawing = cell.Sheet.CreateDrawingPatriarch();
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
return;
}
TypeCode valueTypeCode = Type.GetTypeCode(value.GetType());
switch (valueTypeCode)
{
case TypeCode.String:
cell.SetCellValue(Convert.ToString(value));
break;
case TypeCode.DateTime:
cell.SetCellValue(Convert.ToDateTime(value));
break;
case TypeCode.Boolean:
cell.SetCellValue(Convert.ToBoolean(value));
break;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Byte:
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
cell.SetCellValue(Convert.ToDouble(value));
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
/// <summary>
/// 在单元格中插入图片
/// </summary>
private static void InsertImage(ICell cell, byte[] picBytes, PictureType pt)
{
var row1 = cell.RowIndex;
var col1 = cell.ColumnIndex;
var workbook = cell.Sheet.Workbook;
var sheet = cell.Sheet;
/* Calcualte the image size */
MemoryStream ms = new MemoryStream(picBytes);
Image Img = Bitmap.FromStream(ms, true);
double ImageOriginalWidth = Img.Width;
double ImageOriginalHeight = Img.Height;
double scale = cell.Sheet.GetColumnWidth(cell.ColumnIndex) / 36.5 / ImageOriginalWidth;
cell.Row.Height = (short)(ImageOriginalHeight * scale * 15);
//cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)(ImageOriginalWidth * scale * 36.5));
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, pt); //添加图片
/* Create the drawing container */
IDrawing drawing = sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize(1);
picBytes = null;
}
public static byte[] ExportByTemplateOption2(string templateFileName, ExcellExportOption option, List <SetExcelDto> setList, int insertPicRowIndex, byte[] picBytes, int insertTableRowIndex, DataTable table)
{
var workbook = OpenExcel(templateFileName);
var sheet = workbook.GetSheetAt(0);
FillSheet(sheet, table, insertTableRowIndex);
SetupByOption(workbook, option);
//指定单元格赋值
if (setList != null)
{
SetExcelValue(workbook, setList);
}
//插入图片
if (picBytes != null)
{
int pictureIndex = workbook.AddPicture(picBytes, PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(50, 50, 225, 225, 0, insertPicRowIndex, 1, insertPicRowIndex + 5);
IPicture pict = patriarch.CreatePicture(anchor, pictureIndex);
}
byte[] data = null;
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
data = ms.ToArray();
ms.Close();
ms.Dispose();
}
workbook.Close();
return(data);
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
/// <summary>
/// 向excel添加图片
/// </summary>
/// <param name="XlsFilePath"></param>
/// <param name="pictureData"></param>
/// <param name="sheetName"></param>
public static void AddPicture(string XlsFilePath, byte[] pictureData, bool isNewSheet, string sheetName = "PicTure")
{
FileStream fs = File.OpenRead(XlsFilePath);
//IWorkbook workbook = new XSSFWorkbook(fs);
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); // (NPOI.XSSF.UserModel.XSSFWorkbook)WorkbookFactory.Create(XlsFilePath);
fs.Close();
int pictureIdx = book.AddPicture(pictureData, PictureType.JPEG);
NPOI.SS.UserModel.ISheet sheet = null;
if (!isNewSheet)
{
sheet = book.GetSheetAt(0);
}
else
{
sheet = book.CreateSheet(sheetName);
}
IDrawing patriarch = sheet.CreateDrawingPatriarch();
NPOI.XSSF.UserModel.XSSFClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
using (FileStream fileStream = File.Open(XlsFilePath,
FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
book.Write(fileStream);
fileStream.Close();
}
}
/// <summary>
/// 导出按钮监听
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_ExTWO_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = @"Excel|*.xls";
sfd.ShowDialog();
string path = sfd.FileName;
PlotModel pm = plotView1.Model;
var pngExporter = new PngExporter {
Width = 1000, Height = 400, Background = OxyColors.White
};
Bitmap bitmap = pngExporter.ExportToBitmap(pm);
Image image = bitmap;
MemoryStream ms = new MemoryStream();
image.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
byte[] bytes = ms.ToArray();
IWorkbook workbook = new HSSFWorkbook();
workbook.CreateSheet("sheet1");
if (path != "")
{
using (FileStream fs = File.Create(path))
{
ISheet sheet = workbook.GetSheetAt(0);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10);
int pic = workbook.AddPicture(bytes, PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
IPicture ipic = patriarch.CreatePicture(anchor, pic);
ipic.Resize();
workbook.Write(fs);
}
MessageBox.Show("保存成功！");
}
}
public void DrawGraphs(IWorkbook workBook, ISheet sheet,
IClientAnchor anchor,
Byte[] graphData,
PictureType type = DefaultPictureType)
{
Int32 index = workBook.AddPicture(graphData, type);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
patriarch.CreatePicture(anchor, index);
}
protected override void SetValue(ICell cell, object source)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题
}
/// <summary>
/// create xls with table
/// using NPOI
/// </summary>
/// <param name="svgDoc"></param>
/// <param name="stream"></param>
/// <param name="table"></param>
public static void CreateExcelWithTableStreamBySvg(SvgDocument svgDoc, Stream stream, FileTable table)
{
using (stream)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IDrawing patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
IPicture pic;
ICellStyle head_style = createHeadCellStyle(workbook);
ICellStyle cell_style = createCellBorderStyle(workbook);
IRow head_row = null;
head_row = sheet.CreateRow(0);
ICell blank = head_row.CreateCell(0);
blank.CellStyle = head_style;
for (int i = 0; i < table.series.Count; i++)
{
ICell head_cell = head_row.CreateCell(i + 1);
head_cell.SetCellValue(table.series[i]);
head_cell.CellStyle = head_style;
}
// create vertical yAxis
int _ycount = 1;
foreach (string key in table.xAxis.Keys)
{
IRow y_row = sheet.CreateRow(_ycount);
ICell cell = y_row.CreateCell(0);
cell.SetCellValue(table.xAxis[key]);
cell.CellStyle = head_style;
for (int i = 0; i < table.series.Count; i++)
{
ICell value_cell = y_row.CreateCell(i + 1);
value_cell.SetCellValue(table.rows[i][key]);
value_cell.CellStyle = cell_style;
}
_ycount++;
}
using (MemoryStream ms = new MemoryStream())
{
using (System.Drawing.Bitmap image = svgDoc.Draw())
{
image.Save(ms, ImageFormat.Bmp);
ms.Seek(0, SeekOrigin.Begin);
int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG);
anchor = new HSSFClientAnchor(0, 0, 0, 0, table.series.Count + 2, 0, 100, 100);
pic = patriarch.CreatePicture(anchor, index);
pic.Resize();
}
}
workbook.Write(stream);
}
}
/// <summary>
/// 在单元格中添加图片
/// </summary>
/// <param name="col1"></param>
/// <param name="row1"></param>
/// <param name="col2"></param>
/// <param name="row2"></param>
/// <param name="dx1"></param>
/// <param name="dy1"></param>
/// <param name="dx2"></param>
/// <param name="dy2"></param>
/// <param name="picture"></param>
/// <param name="sheetName"></param>
public void InsertPictureInCell(int col1, int row1, int col2, int row2, int dx1, int dy1, int dx2, int dy2, byte[] picture, string sheetName)
{
int pictureIdx1 = this._workbook.AddPicture(picture, PictureType.PNG);
ISheet sheet = this._workbook.GetSheet(sheetName);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
anchor.AnchorType = AnchorType.MoveDontResize;
IPicture pict1 = patriarch.CreatePicture(anchor, pictureIdx1);
}
public static ICell AddPicture(this ICell cell, byte[] bytes)
{
int pictureIdx = cell.Sheet.Workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
if (cell is HSSFCell)
{
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
XSSFClientAnchor anchor = new XSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
return(cell);
}
public void Render(ICell cell)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
PicArea != null?
draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
}
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();
}
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource)
{
IRow row = null;
ICell cell = null;
ICellStyle cellStyle = null;
IDrawing drawing = null;
IPicture picture = null;
cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++)
{
row = target.CreateRow(rowIndex + 1);
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
cell = row.CreateCell(columnIndex);
if (dataSource.Columns[columnIndex].DataType == typeof(byte[]))
{
byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[];
if (image != null && image.Length > 0)
{
int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG);
drawing = target.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1);
picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize();
}
}
else
{
cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString());
}
cell.CellStyle = cellStyle;
}
}
}
public void Render(
jp.co.systembase.report.renderer.xlsx.component.Page page,
jp.co.systembase.report.renderer.xlsx.component.Shape shape)
{
createImage();
int index;
if ((bool)this.data)
{
index = page.Renderer.GetImageIndex(checkedImage);
}
else
{
index = page.Renderer.GetImageIndex(noCheckedImage);
}
if (index >= 0)
{
IDrawing p = page.Renderer.Sheet.CreateDrawingPatriarch();
p.CreatePicture(shape.GetXSSFClientAnchor(page.TopRow), index);
}
}
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column)
{
try
{
int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1)
{
AnchorType = AnchorType.MoveAndResize
};
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
}
catch
{
//ignore
}
}
/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="value">值</param>
public static void SetValue(this ICell cell, object value)
{
if (null == cell)
{
return;
}
if (null == value)
{
cell.SetCellValue(string.Empty);
return;
}
if (value is IRichTextString)
{
cell.SetCellValue((IRichTextString)value);
}
else if (value is Image)
{
int pictureIdx = cell.Sheet.Workbook.AddPicture(((Image)value).ToBuffer(), PictureType.PNG);
IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColSpan;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
IPicture pic = patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
value.GetType()
.Case(typeof(String)).Do(() => cell.SetCellValue(Convert.ToString(value)))
.Case(typeof(DateTime)).Do(() => cell.SetCellValue(Convert.ToDateTime(value)))
.Case(typeof(Boolean)).Do(() => cell.SetCellValue(Convert.ToBoolean(value)))
.Case(typeof(Int16), typeof(Int32), typeof(Int64),
typeof(Byte), typeof(Single), typeof(Double), typeof(Decimal),
typeof(UInt16), typeof(UInt32), typeof(UInt64)).Do(() => cell.SetCellValue(Convert.ToDouble(value)));
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("../../image/HumpbackWhale.jpg", workbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
private void handleResize(IWorkbook wb, ISheet sheet, IRow row)
{
IDrawing Drawing = sheet.CreateDrawingPatriarch();
ICreationHelper CreateHelper = wb.GetCreationHelper();
byte[] bytes = HSSFITestDataProvider.Instance.GetTestDataFileContent("logoKarmokar4.png");
row.HeightInPoints = (/*setter*/ GetImageSize(bytes).Y);
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
//add a picture shape
IClientAnchor anchor = CreateHelper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = (/*setter*/ 0);
anchor.Row1 = (/*setter*/ 0);
IPicture pict = Drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
}
/// <summary>
/// 添加图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="picInfo"></param>
public static void AddPicture(this ISheet sheet, PictureInfo picInfo)
{
int pictureIdx = sheet.Workbook.AddPicture(picInfo.PictureData, PictureType.PNG);
IClientAnchor anchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = picInfo.MinCol;
anchor.Col2 = picInfo.MaxCol;
anchor.Row1 = picInfo.MinRow;
anchor.Row2 = picInfo.MaxRow;
anchor.Dx1 = picInfo.PicturesStyle.AnchorDx1;
anchor.Dx2 = picInfo.PicturesStyle.AnchorDx2;
anchor.Dy1 = picInfo.PicturesStyle.AnchorDy1;
anchor.Dy2 = picInfo.PicturesStyle.AnchorDy2;
anchor.AnchorType = 2;
IDrawing drawing = sheet.CreateDrawingPatriarch();
IPicture pic = drawing.CreatePicture(anchor, pictureIdx);
if (sheet is HSSFSheet)
{
var shape = (HSSFShape)pic;
shape.FillColor = picInfo.PicturesStyle.FillColor;
shape.IsNoFill = picInfo.PicturesStyle.IsNoFill;
shape.LineStyle = picInfo.PicturesStyle.LineStyle;
shape.LineStyleColor = picInfo.PicturesStyle.LineStyleColor;
shape.LineWidth = (int)picInfo.PicturesStyle.LineWidth;
}
else if (sheet is XSSFSheet)
{
var shape = (XSSFShape)pic;
shape.FillColor = picInfo.PicturesStyle.FillColor;
shape.IsNoFill = picInfo.PicturesStyle.IsNoFill;
shape.LineStyle = picInfo.PicturesStyle.LineStyle;
//shape.LineStyleColor = picInfo.PicturesStyle.LineStyleColor;
shape.LineWidth = picInfo.PicturesStyle.LineWidth;
}
}
/// <summary>
/// 添加图片
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="pictureInfo">图片信息</param>
public static void AddPicture(this ISheet sheet, PictureInfo pictureInfo)
{
int pictureIndex = sheet.Workbook.AddPicture(pictureInfo.Data, PictureType.PNG);
IClientAnchor anchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = pictureInfo.FirstColumn;
anchor.Col2 = pictureInfo.LastColumn;
anchor.Row1 = pictureInfo.FirstRow;
anchor.Row2 = pictureInfo.LastRow;
anchor.Dx1 = pictureInfo.Style.AnchorDx1;
anchor.Dx2 = pictureInfo.Style.AnchorDx2;
anchor.Dy1 = pictureInfo.Style.AnchorDy1;
anchor.Dy2 = pictureInfo.Style.AnchorDy2;
anchor.AnchorType = AnchorType.MoveDontResize;
IDrawing drawing = sheet.CreateDrawingPatriarch();
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
if (sheet is HSSFSheet)
{
var shape = (HSSFShape)picture;
shape.FillColor = pictureInfo.Style.FillColor;
shape.IsNoFill = pictureInfo.Style.IsNoFill;
//shape.LineStyle = pictureInfo.Style.LineStyle;
shape.LineStyleColor = pictureInfo.Style.LineStyleColor;
shape.LineWidth = (int)pictureInfo.Style.LineWidth;
}
else if (sheet is XSSFSheet)
{
var shape = (XSSFShape)picture;
shape.FillColor = pictureInfo.Style.FillColor;
shape.IsNoFill = pictureInfo.Style.IsNoFill;
//shape.LineStyle = pictureInfo.Style.LineStyle;
//shape.LineStyleColor = pictureInfo.Style.LineStyleColor;
shape.LineWidth = (int)pictureInfo.Style.LineWidth;
}
}
public void BaseTestResize(IClientAnchor referenceAnchor)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh1 = wb.CreateSheet();
IDrawing p1 = sh1.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
byte[] pictureData = _testDataProvider.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
IPicture picture = p1.CreatePicture(factory.CreateClientAnchor(), idx1);
picture.Resize();
IClientAnchor anchor1 = picture.GetPreferredSize();
//assert against what would BiffViewer print if we insert the image in xls and dump the file
Assert.AreEqual(referenceAnchor.Col1, anchor1.Col1);
Assert.AreEqual(referenceAnchor.Row1, anchor1.Row1);
Assert.AreEqual(referenceAnchor.Col2, anchor1.Col2);
Assert.AreEqual(referenceAnchor.Row2, anchor1.Row2);
Assert.AreEqual(referenceAnchor.Dx1, anchor1.Dx1);
Assert.AreEqual(referenceAnchor.Dy1, anchor1.Dy1);
Assert.AreEqual(referenceAnchor.Dx2, anchor1.Dx2);
Assert.AreEqual(referenceAnchor.Dy2, anchor1.Dy2);
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="inputSheet"></param>
/// <param name="lstInputRequest"></param>
/// <returns></returns>
public static bool TryInsertPictures(ISheet inputSheet, List <PictureInsertRequest> lstInputRequest)
{
try
{
//获取/创建
IDrawing patriarch = inputSheet.DrawingPatriarch;
if (null == patriarch)
{
patriarch = inputSheet.CreateDrawingPatriarch();
}
foreach (var oneRequest in lstInputRequest)
{
//第一步：将图片读入二进制数组
MemoryStream mStream = new MemoryStream();
oneRequest.UseImage.Save(mStream, System.Drawing.Imaging.ImageFormat.Png);
byte[] bytePic = mStream.GetBuffer();
//第二步：图片加载进workbook
int pictureIdx = inputSheet.Workbook.AddPicture(bytePic, PictureType.PNG);
//第三步：设置锚点
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, oneRequest.StartColumn, oneRequest.StartRow, oneRequest.EndColumn, oneRequest.EndRow);
//第四步：创建图片
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
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));
}
