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 static void setPicture(HSSFWorkbook workbook, HSSFSheet worksheet, int row, int col, string fileName)
{
try
{
byte[] bytes = File.ReadAllBytes(fileName);
if (!string.IsNullOrEmpty(fileName))
{
int pictureIdx = 0;
if (fileName.EndsWith(".jpg"))
{
pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
}
else if (fileName.EndsWith(".png"))
{
pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
}
HSSFPatriarch patriarch = (HSSFPatriarch)worksheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为50，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
catch (Exception ex)
{
throw ex;
}
}
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 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);
}
/// <summary>
/// .xls后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSExcel(string excelPath, string imgPath)
{
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(hssfworkbook.GetSheetName(0));
int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 5, 2, 10, 3);//(255, 125, 1023, 150, 5, 2, 10, 3);//(0, 0, 0, 0, 5, 2, 10, 3);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
FileStream file = new FileStream(excelPath, FileMode.Create);
hssfworkbook.Write(file);
hssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// 向sheet插入图片
///
///
///
private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col, string mappath)
{
try
{
if (string.IsNullOrEmpty(fileurl))
{
return;
}
string path = mappath + fileurl.Replace("/", @"\");
string FileName = path;
if (!File.Exists(FileName))
{
return;
}
byte[] bytes = File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, row, col + 1, row + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
// pict.Resize();这句话一定不要，这是用图片原始大小来显示
}
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
//向excel中提交图片
private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
//由于File类只能读取本地资源，所以在配置文件中配置了物理路径的前半部分
string DiscPath = ConfigurationManager.AppSettings["PictureDiscPath"];
// string FileName = DiscPath.Replace("\\", "/") + fileurl.Replace("http://www.bolioptics.com/", "");
string FileName = fileurl;
FileInfo file = new FileInfo(FileName);
if (file.Exists == true)
{
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将图片导出到Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="xssfworkbook"></param>
/// <param name="imageName"></param>
private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook, string imageName)
{
try
{
string picName = string.IsNullOrEmpty(imageName) ? @"Image\sjlogo.png" : imageName;
var picType = string.IsNullOrEmpty(imageName) ? PictureType.PNG : GetPicType(imageName);
Image imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), picName));
//Image imgOutput = System.Drawing.Bitmap.FromStream()
Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero);
//图片转换为文件流
MemoryStream ms = new MemoryStream();
img.Save(ms, ImageFormat.Bmp);
BinaryReader br = new BinaryReader(ms);
var picBytes = ms.ToArray();
ms.Close();
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
int pictureIdx = xssfworkbook.AddPicture(picBytes, picType); //添加图片
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(200, 90, 240, 200, 0, 0, 1, 0);// 90 200 图片高度110
// new HSSFClientAnchor(X1, Y1, X2, Y2, 列索引1,行索引1 , 列索引2, 行索引2); 行列索引从0开始 ,行列索引指的是 图片左上角所在单元格的行列和 图片右下角所在单元格的行列
//X: 0-1024 Y:0-256 ； X1\X2相对本单元格，距离Y轴的偏移量，最大值1023；Y1\Y2相对本单元格，距离X轴的偏移量，最大值255；
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//picture.Resize(); //使图像恢复到原始大小
picBytes = null;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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);
}
//导出excel
public ActionResult GetExcel(int jiedian, int data, DateTime today)
{
string title = today.ToString("yyyy-MM-dd ") + jiedian + "号节点" + "数据统计图";
title = title + Guid.NewGuid().ToString();
IWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("智能水产数据");
string path = Server.MapPath("1.jpg");
path = path.Replace("Home\\", "");
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 10, 20);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
string writepath = Server.MapPath("upload/" + title + ".xls");
writepath = writepath.Replace("Home\\", "");
using (FileStream write = System.IO.File.OpenWrite(writepath))
{
hssfworkbook.Write(write);
}
return(Content("/upload/" + title + ".xls"));
}
/// <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);
}
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路径</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的img数据</param>
/// <param name="coluid">需更新的列号</param>
/// <param name="rowid">需更新的开始行号</param>
/// <param name="IsTrue">true：以横向输出; false：以横向输出</param>
public static void UpdateImgToExcel(string outputFile, string sheetname, byte[] updateData, int beginCol, int beginRow, int endCol, int endRow)
{
try
{
FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
int pictureIdx = hssfworkbook.AddPicture(updateData, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(30, 30, 30, 30, beginCol, beginRow, endCol, endRow);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
readfile.Close();
FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());
}
}
/// <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 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, NPOI.SS.UserModel.PictureType.JPEG));
}
private void ExportDeliveryInfo(IDictionary <string, List <Order> > receiversByAccounts, Stream stm)
{
HSSFWorkbook book = new HSSFWorkbook();
var topBorderStyle = book.CreateCellStyle();
topBorderStyle.BorderTop = BorderStyle.DashDot;
var noteBorderStyle = book.CreateCellStyle();
noteBorderStyle.BorderTop = BorderStyle.DashDot;
noteBorderStyle.TopBorderColor = HSSFColor.Grey25Percent.Index;
noteBorderStyle.BorderBottom = BorderStyle.DashDot;
foreach (var ra in receiversByAccounts)
{
var sheet = book.CreateSheet(ra.Key);
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 100 * 256);
var row = 0;
foreach (var o in ra.Value)
{
var r = o.Receiver;
{
var bytes = this.GetBarcodeBytes(r.OrderNO);
int pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
var patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(250, 0, 500, 100, 1, row + 3, 1, row + 5);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();
}
var tmpNo = string.Format("{0} {1} {2} {3}", o.AccountOf.QuickCode, Regex.Replace(r.OrderNO, @"(.{4})", "$1 "), r.Country.ZhName, r.CountryCode);
this.WriteField(sheet, ref row, "NO.", tmpNo, topBorderStyle);
this.WriteField(sheet, ref row, "Name", r.Name);
//var addr = string.Join(", ", (new string[] { r.Address, r.City, r.Province, r.Country }).Where(s => !string.IsNullOrEmpty(s)));
//var addr = string.Join(" ", r.FullAddress, r.Country.ZhName);
this.WriteField(sheet, ref row, "Address", r.FullAddress);
this.WriteField(sheet, ref row, "Postcode", r.ZipCode);
this.WriteField(sheet, ref row, "Phone", r.Mobi);
this.WriteField(sheet, ref row, "Tel", r.Phone);
this.WriteField(sheet, ref row, "Note", string.Format("[{0}] {1}", r.OrderNO, o.Note != null ? o.Note.Note : ""), noteBorderStyle);
row += 2;
}
}
this.ExportOrderList(
receiversByAccounts.Values
.SelectMany(o => o)
.Select(o => o.OrderNO).ToList(), book.CreateSheet("本次导出的订单号码列表"));
book.Write(stm);
}
public static int LoadImage(string path, HSSFWorkbook wb)
{
//FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] buffer = GetBytesFromUrl(path);
return(wb.AddPicture(buffer, PictureType.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);
}
}
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 void SetPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, IMGOffet mGOffet)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(mGOffet.StartOffsetX, mGOffet.StartOffsetY, mGOffet.EndOffsetX, mGOffet.EndOffsetY, mGOffet.StartCellCol, mGOffet.StartCellRow, mGOffet.EndCellCol, mGOffet.EndCellRow);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
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);
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wbBack.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);
HSSFWorkbook wbBack2 = HSSFTestDataSamples.WriteOutAndReadBack(wbBack);
sh = wbBack2.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");
wb.Close();
wbBack.Close();
wbBack2.Close();
}
/// <summary>
/// 将DataTable转换成流
/// </summary>
public static Stream RenderDataTableToExcelAndImg(DataTable sourceTable, string path)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("Sheet1");
HSSFRow hssfRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
hssfRow.HeightInPoints = 120;
HSSFFont cellfont = (HSSFFont)workbook.CreateFont();
cellfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
byte[] picByte = File.ReadAllBytes(path);
workbook.AddPicture(picByte, PictureType.PNG);
IRow headerRow = sheet.CreateRow(0);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//picture.Resize();
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
if (column.Ordinal == 0)
{
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 10, 1023, 0, 2 + (column.Ordinal), 0, 2,
1 + (column.Ordinal));
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, 1);
dataRow.CreateCell(column.Ordinal).CellStyle = cellStyle;
}
else
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
public static int LoadImageExcel(string path, HSSFWorkbook workbook)
{
try
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[file.Length];
file.Read(buffer, 0, Convert.ToInt32(file.Length));
return(workbook.AddPicture(buffer, PictureType.PNG));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 下载图片加载到execl
/// </summary>
/// <param name="sheet"></param>
/// <param name="workbook"></param>
/// <param name="fileurl"></param>
/// <param name="row"></param>
/// <param name="col"></param>
private static void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
var uploadFilePath = AppDomain.CurrentDomain.BaseDirectory + "ExportImage";
//判断文件夹是否存在
if (!Directory.Exists(uploadFilePath))
{
Directory.CreateDirectory(uploadFilePath);
}
string imgFileName = uploadFilePath + Path.DirectorySeparatorChar + Guid.NewGuid().ToString() + "jpg";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(fileurl);
HttpWebResponse response = request.GetResponse() as HttpWebResponse;
Stream responseStream = response.GetResponseStream();
Stream stream = new FileStream(imgFileName, FileMode.Create);
byte[] bArr = new byte[1024];
int size = responseStream.Read(bArr, 0, bArr.Length);
while (size > 0)
{
stream.Write(bArr, 0, size);
size = responseStream.Read(bArr, 0, bArr.Length);
}
stream.Close();
responseStream.Close();
byte[] bytes = System.IO.File.ReadAllBytes(imgFileName);
if (!string.IsNullOrEmpty(fileurl))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
File.Delete(imgFileName);//直接删除文件
}
catch (Exception ex)
{
LogHelper.logError("AddPieChart异常：" + ex.Message + "____" + ex.ToString());
}
}
private void genExcel(Bitmap bitmap, int index)
{
int column_index = (index / 4) * 3;
int rowline = index % 4;
IRow row = sheet.CreateRow(column_index);
//设置行高 ,excel行高度每个像素点是1/20
row.Height = 60 * 20;
byte[] bytes = Bitmap2Byte(bitmap);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, rowline, column_index, rowline + 1, column_index + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
/// <summary>
/// 向sheet插入图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="workbook"></param>
/// <param name="fileurl"></param>
/// <param name="row"></param>
/// <param name="col"></param>
private static void AddPieChart(HSSFSheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
byte[] bytes = System.IO.File.ReadAllBytes(fileurl);
if (!string.IsNullOrEmpty(fileurl))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 50, 25, col, row, col + 1, row + 1);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
// pict.Resize();这句话一定不要，这是用图片原始大小来显示
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 向sheet插入图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="workbook"></param>
/// <param name="fileurl"></param>
/// <param name="row"></param>
/// <param name="col"></param>
private async Task AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
var imageStream = await _fileUploader.GetFileStreamAsync(fileurl);
if (imageStream != null && imageStream.Length > 0)
{
byte[] bytes = new byte[imageStream.Length];
await imageStream.ReadAsync(bytes);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void TestResize()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sh1 = wb.CreateSheet();
Drawing p1 = sh1.CreateDrawingPatriarch();
byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
Picture picture1 = p1.CreatePicture(new HSSFClientAnchor(), idx1);
ClientAnchor anchor1 = picture1.GetPreferredSize();
//assert against what would BiffViewer print if we insert the image in xls and dump the file
Assert.AreEqual(0, anchor1.Col1);
Assert.AreEqual(0, anchor1.Row1);
Assert.AreEqual(1, anchor1.Col2);
Assert.AreEqual(9, anchor1.Row2);
Assert.AreEqual(0, anchor1.Dx1);
Assert.AreEqual(0, anchor1.Dy1);
Assert.AreEqual(848, anchor1.Dx2);
Assert.AreEqual(240, anchor1.Dy2);
}
private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook)
{
try
{
//Assembly _assembly = Assembly.GetExecutingAssembly();
//string[] resNames = _assembly.GetManifestResourceNames();
Image imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), @"Image\excelLogo.jpg"));
//Image imgOutput = System.Drawing.Bitmap.FromStream()
Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero);
//图片转换为文件流
MemoryStream ms = new MemoryStream();
img.Save(ms, ImageFormat.Bmp);
BinaryReader br = new BinaryReader(ms);
var picBytes = ms.ToArray();
ms.Close();
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
int pictureIdx = xssfworkbook.AddPicture(picBytes, PictureType.JPEG); //添加图片
//XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);// 0 行0列
//XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);// 0 行0列
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//picture.Resize();
picBytes = null;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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");
}
