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);
}
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));
}
/// 向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;
}
}
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 static void inserirImagem(string imgPath)
{
//Wait the page to complete
//Browser.Wait.Until(wd => Browser.JSexec.ExecuteScript("return document.readyState") == "complete");
ISheet sheet1 = XLSUtils.hssfworkbook.GetSheet("Telas");
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
int larguraImg = 2 * 9;
int alturaImg = 2 * 16;
//anchor = new HSSFClientAnchor(0, (alturaImg * imgsQtd) + 3, larguraImg, (alturaImg * (imgsQtd + 1)) + 3, 0, (alturaImg * imgsQtd) + 3, larguraImg, (alturaImg * (imgsQtd + 1)) + 3);
anchor = new HSSFClientAnchor(0, 0, larguraImg, alturaImg, 0, alturaImg * imgsQtd, larguraImg, alturaImg * (imgsQtd + 1));
anchor.Row1 = anchor.Row1 + 3;
anchor.AnchorType = AnchorType.MoveAndResize;
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, XLSUtils.LoadImage(imgPath, XLSUtils.hssfworkbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
FunctionalTestCase.imgsQtd++;
sheet1.CreateRow(anchor.Row1 - 2).CreateCell(0).SetCellValue("Tela " + imgsQtd.ToString("00"));
XLSUtils.WriteToFile(currentFile);
registrarTela();
}
//导出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"));
}
public PictureToExcel()
{
InitializeWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
TechStoreDocumentsDT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(@"SELECT TOP (2000) TechStoreName, TechStoreDocumentID FROM TechStore
LEFT JOIN TechStoreDocuments ON TechStore.TechStoreID = TechStoreDocuments.TechID AND DocType = 0
WHERE TechStoreSubGroupID = 30 ORDER BY TechStoreName", ConnectionStrings.CatalogConnectionString))
{
using (DataTable DT = new DataTable())
{
DA.Fill(TechStoreDocumentsDT);
}
}
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
HSSFCell Cell1;
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
int RowIndex = 0;
for (int i = 0; i < TechStoreDocumentsDT.Rows.Count; i++)
{
string TechStoreName = TechStoreDocumentsDT.Rows[i]["TechStoreName"].ToString();
Cell1 = sheet1.CreateRow(RowIndex).CreateCell(0);
Cell1.SetCellValue(TechStoreName);
Cell1.CellStyle = ReportCS1;
if (TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"] != DBNull.Value)
{
int TechStoreDocumentID = Convert.ToInt32(TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"]);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 2, RowIndex, 5, RowIndex + 7)
{
AnchorType = 2
};
HSSFPicture picture = patriarch.CreatePicture(anchor, GetTechStoreImage(TechStoreDocumentID, hssfworkbook));
//picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
}
RowIndex = RowIndex + 8;
}
WriteToFile();
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
//向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);
}
}
/// <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);
}
}
/// <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());
}
}
public void TableToExcel(DataTable dt, Image img)
{
if (Path.GetExtension(fileName) == ".xls")
{
workbook = new HSSFWorkbook();
}
else if (Path.GetExtension(fileName) == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else
{
return;
}
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 2, 2, 14, 34);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, workbook.AddPicture(ms.ToArray(), PictureType.PNG));
pict.Resize();
ms.Close();
//表头
IRow row = sheet.CreateRow(anchor.Row2 + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//行数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow ir = sheet.CreateRow(i + anchor.Row2 + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = ir.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
public void insertPicture(string sheetName, Models.RowData modelRowData, System.Windows.Forms.TextBox textBox)
{
//删除空白的实例
for (int i = modelRowData.instances.Count - 1; i >= 0; i--)
{
bool isDelete = true;
Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i];
for (int j = 0; j < model_Instance.pictures.Count; j++)
{
if (model_Instance.pictures[j] != null)
{
isDelete = false;
}
}
if (isDelete)
{
modelRowData.instances.RemoveAt(i);
}
}
//delete sheet if exist
ISheet sheet = null;
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.CreateSheet(sheetName);
}
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
for (int i = 0; i < modelRowData.instances.Count; i++)
{
Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i];
for (int j = 0; j < model_Instance.pictures.Count; j++)
{
if (model_Instance.pictures[j] != null)
{
string picturePath = model_Instance.pictures[j].ToString();
//读取图片
byte[] bytes = System.IO.File.ReadAllBytes(picturePath);
int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, 4 + (j * 12), 2 + (i * 32), 14 + (j * 12), 10 + (i * 32));
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
textBox.AppendText(" " + picturePath.Substring(picturePath.LastIndexOf("\\") + 1) + "\r\n");
}
}
}
fs = new FileStream(fileName, FileMode.Open, FileAccess.Write);
workbook.Write(fs);
fs.Close();
textBox.AppendText("Insert pictures succeed\r\n");
}
/// <summary>
/// XLS类型Excle文件添加图片
/// </summary>
/// <param name="intRowIndex">插入图片的行数</param>
/// <param name="pictureIndex">图片的顺序</param>
private void HSSFCreatePicture(int intRowIndex, int pictureIndex)
{
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.Row1 = intRowIndex;
//anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIndex);
picture.LineStyle = LineStyle.Solid;
picture.Resize();//显示图片的原始尺寸
}
private static void setPic(IWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col, string webRootPath)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(webRootPath + path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, rowline, col + 1, rowline + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
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);
}
//插入base64图片数据
public void Insertbase64Picture(int k, int startrow, int startcol, int lastrow, int lastcol, int anchorType, string base64)
{
if (wb == null)
{
return;
}
k = k - 1;
ISheet sheet = wb.GetSheetAt(k);
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(24, 24, 0, 0, startcol, startrow, lastcol, lastrow);
anchor.AnchorType = (AnchorType)2;
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
byte[] arr = Convert.FromBase64String(base64);
MemoryStream ms = new MemoryStream(arr);
System.Drawing.Bitmap bmp = new Bitmap(ms);
MemoryStream ms2 = new MemoryStream();
bmp.Save(ms2, System.Drawing.Imaging.ImageFormat.Png);
byte[] buffer = ms2.GetBuffer();
ms2.Close();
int pictureIndex = wb.AddPicture(buffer, PictureType.PNG);
switch (anchorType)
{
case 0:
anchor.AnchorType = AnchorType.MoveAndResize;
break;
case 2:
anchor.AnchorType = AnchorType.MoveDontResize;
break;
case 3:
anchor.AnchorType = AnchorType.DontMoveAndResize;
break;
default:
break;
}
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIndex);
picture.IsNoFill = false;
//picture.
}
/// <summary>
/// Metodo usado para cargar una imagen de encabezado en cada reporte
/// </summary>
/// <param name="sheet1">Hoja dondde se insertará la imagen</param>
/// <param name="cols">Columna donde se insertará la imagen</param>
private void CargarLogoUltimaFila(Sheet sheet1, int cols)
{
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0, 0, 255, 255, cols, sheet1.LastRowNum + 2, cols, sheet1.LastRowNum + 2);
anchor.AnchorType = 0;
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, this.LoadImage(System.Windows.Forms.Application.StartupPath + @"/ClipArt/LAN.bmp", _workbook));//@"C:\Users\Rodolfo\PUC\postgrado\SimuLAN2\Code\InterfazSimuLAN\Reportes\lan.jpg", this.workbook));
//Reset the image to the original size.
picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_NONE;
}
/// <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);
}
///// <summary>
///// 将Base64字符串转换为图片
///// </summary>
///// <param name="workbook"></param>
///// <param name="base64">图片base64码</param>
///// <param name="tempPath">模板路径</param>
///// <param name="outputPath">输出路径</param>
///// <param name="col1">图片起始列</param>
///// <param name="row1">图片起始行</param>
///// <param name="col2">图片结束列</param>
///// <param name="row2">图片结束行</param>
///// <returns></returns>
//public static string AddImageToExcel(HSSFWorkbook workbook, string base64, string tempPath, string outputPath, int col1, int row1, int col2, int row2)
//{
// byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+"));
// int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
// HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
// HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// //##处理照片位置，【图片左上角为（6, 2）第2+1行6+1列，右下角为（8, 6）第6+1行8+1列】
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
// HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
// string outFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");
// string resPath = outputPath + outFileName + ".xls";
// return resPath;
//}
/// <summary>
/// 将Base64字符串转换为图片
/// </summary>
/// <param name="workbook"></param>
/// <param name="base64">图片base64码</param>
/// <param name="tempPath">模板路径</param>
/// <param name="outputPath">输出路径</param>
/// <param name="col1">图片起始列</param>
/// <param name="row1">图片起始行</param>
/// <param name="col2">图片结束列</param>
/// <param name="row2">图片结束行</param>
/// <returns></returns>
public static string AddImageToExcel(IWorkbook workbook, string base64, string tempPath, string outputPath, int col1, int row1, int col2, int row2)
{
byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+"));
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
ISheet sheet = workbook.GetSheetAt(0);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//##处理照片位置，【图片左上角为（6, 2）第2+1行6+1列，右下角为（8, 6）第6+1行8+1列】
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
string outFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");
string resPath = outputPath + outFileName + ".xls";
return(resPath);
}
/// <summary>
/// 建立新位元流並嵌入圖片.
/// </summary>
/// <param name="PicFileName">Name of the pic file.</param>
/// <param name="IsOriginalSize">if set to <c>true</c> [is original size].</param>
/// <param name="RowPosition">The row position.</param>
/// <returns></returns>
public static void EmbedImage(HSSFWorkbook wb, HSSFSheet sheet, string PicFileName, bool IsOriginalSize, int[] RowPosition)
{
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0, 0, 0, 0,
RowPosition[0], RowPosition[1], RowPosition[2], RowPosition[3]);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
HSSFPicture picture = patriarch.CreatePicture(anchor, LoadImage(PicFileName, wb)) as HSSFPicture;
//Reset the image to the original size.
if (IsOriginalSize == true)
{
picture.Resize();
}
}
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>
/// 下载图片加载到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());
}
}
/// <summary>
/// 添加图片
/// </summary>
/// <param name="wb">工作簿对象</param>
/// <param name="sheet">工作表对象</param>
/// <param name="pics">图片列表</param>
public void AddPicture(IWorkbook wb, ISheet sheet, List <ExcelPictureModel> pics)
{
var pictureIdx = 0;
var pic = new ExcelPictureModel();
for (int i = 0; i < pics.Count; i++)
{
pic = pics[i];
pictureIdx = wb.AddPicture(pic.Datas, pic.PicType);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, pic.Weight, pic.Height, pic.StartCol, pic.StartRow, pic.EndCol, pic.EndRow);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
if (pic.IsResize)
{
pict.Resize();
}
}
}
public void InsertImg(string path)
{
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
// anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 2, 4, 4);
// anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 0, 5, 4);
//anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 0, 4, 4);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 0, 3, 3);
anchor.AnchorType = AnchorType.MoveAndResize;// AnchorType.MoveDontResize|
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(path, hssfworkbook));
//Reset the image to the original size.
picture.LineStyle = LineStyle.None;
}
/// <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>
/// 插入图片,切设置图片显示范围。
/// </summary>
/// <param name = "PicturePath">图片的绝对物理路径</param>
/// <param name = "pictureType">图片类型</param>
/// <param name = "startColNo">左上角所在列号(从0开始)</param>
/// <param name = "startRowNo">左上角所在行号(从0开始)</param>
/// <param name = "endColNo">右下角所在列号(从0开始)</param>
/// <param name = "endRowNo">右下角所在行号(从0开始)</param>
/// <param name = "originalSize">是否按图片原始大小显示,如果是的话传入的右下角参数无效</param>
public bool InsertPicture(string picturePath, PictureType pictureType, int startColNo, int startRowNo, int endColNo, int endRowNo, int iSheetNO, bool originalSize = false)
{
try
{
if (!File.Exists(picturePath))
{
return(false);
}
if (originalSize)
{
endColNo = startColNo + 1;
endRowNo = startRowNo + 1;
}
//将图片加入Workbook
byte[] bytes = System.IO.File.ReadAllBytes(picturePath);
int pictureIdx1 = workbook.AddPicture(bytes, PictureType.JPEG);
//获取存在的Sheet，必须在AddPicture之后
if (patriarch == null)
{
patriarch = (HSSFPatriarch)listSheet[iSheetNO].CreateDrawingPatriarch();
}
//插入图片
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, startColNo, startRowNo, endColNo, endRowNo);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx1);
if (originalSize)
{
pict.Resize();
}
}
catch (System.Exception ex)
{
LogHelper.Instance.WriteError("导出Excel插入图片失败", ex);
return(false);
}
return(true);
}
public void Render(
jp.co.systembase.report.renderer.xls.component.Page page,
jp.co.systembase.report.renderer.xls.component.Shape shape)
{
createImage();
int index;
if ((bool)this.data)
{
index = page.Renderer.GetImageIndex(checkedImage);
}
else
{
index = page.Renderer.GetImageIndex(noCheckedImage);
}
if (index > 0)
{
HSSFPatriarch p = (HSSFPatriarch)page.Renderer.Sheet.DrawingPatriarch;
p.CreatePicture(shape.GetHSSFClientAnchor(page.TopRow), index);
}
}
/// <summary>
/// excel中插入图片,支持jpeg
/// </summary>
/// <param name="Sheetindex"></param>
/// <param name="RowIdex"></param>
/// <param name="CellIndex"></param>
/// <param name="picpath">图片地址</param>
/// <param name="dx1">图片坐标</param>
/// <param name="dy1"></param>
/// <param name="dx2"></param>
/// <param name="dy2"></param>
/// <param name="col1">表格</param>
/// <param name="row1"></param>
/// <param name="col2"></param>
/// <param name="row2"></param>
public void InsertPicturesInXls(int Sheetindex, int RowIdex, int CellIndex, string picpath
, int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(picpath, hssfworkbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
WriteToFile();
}