/// 向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;
}
}
//导出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 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 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();
}
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中提交图片
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="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);
}
}
/// <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="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);
}
/// <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");
}
public void TestDefaultPictureSettings()
{
HSSFPicture picture = new HSSFPicture(null, new HSSFClientAnchor());
Assert.AreEqual(picture.LineWidth, HSSFShape.LINEWIDTH_DEFAULT);
Assert.AreEqual(picture.FillColor, HSSFShape.FILL__FILLCOLOR_DEFAULT);
Assert.AreEqual(picture.LineStyle, HSSFShape.LINESTYLE_NONE);
Assert.AreEqual(picture.LineStyleColor, HSSFShape.LINESTYLE__COLOR_DEFAULT);
Assert.AreEqual(picture.IsNoFill, false);
Assert.AreEqual(picture.PictureIndex, -1);//not Set yet
}
public void Resize()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("resize_Compare.xls");
HSSFPatriarch dp = wb.GetSheetAt(0).CreateDrawingPatriarch() as HSSFPatriarch;
IList <HSSFShape> pics = dp.Children;
HSSFPicture inpPic = (HSSFPicture)pics[(0)];
HSSFPicture cmpPic = (HSSFPicture)pics[(1)];
BaseTestResize(inpPic, cmpPic, 2.0, 2.0);
//wb.Close();
}
public void TestReadExistingImage()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("picture") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFPicture picture = (HSSFPicture)Drawing.Children[0];
Assert.AreEqual(picture.FileName, "test");
}
public static void AddPicture(this ISheet ISheet1, Stream imageStream, PictureStyle style)
{
var patriarch = GetPatriarch(ISheet1);
var anchor = CreateClientAnchor(style.Dx1, style.Dy1, style.Dx2, style.Dy2, style.Col1, style.Row1, style.Col2, style.Row2, AnchorType.MoveDontResize);
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(imageStream, ISheet1.Workbook));
style.SetStyle(picture);
//Reset the image to the original size.
//picture.Resize();
//picture.LineStyle = HSSFPicture.LINESTYLE_NONE;
}
/// <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 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);
}
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);
}
//插入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>
/// 获取指定sheet的图片
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public IList <PictureInfo> GetPictures(string sheetName)
{
ISheet sheet = this._workbook.GetSheet(sheetName);
IDrawing a = sheet.DrawingPatriarch;
IList <PictureInfo> result = new List <PictureInfo>();
if (sheet == null)
{
throw new Exception($"名为{sheetName}的sheet不存在");
}
if (sheet is XSSFSheet)
{
List <POIXMLDocumentPart> documentParts = (sheet as XSSFSheet).GetRelations();
foreach (var part in documentParts)
{
if (part is XSSFDrawing)
{
List <XSSFShape> shapes = (part as XSSFDrawing).GetShapes();
foreach (var shap in shapes)
{
if (shap is XSSFPicture)
{
XSSFPicture picture = shap as XSSFPicture;
IClientAnchor anchor = picture.ClientAnchor;
result.Add(new PictureInfo(anchor.Row1, anchor.Row1, anchor.Col1, anchor.Col1, picture.PictureData.Data));
}
}
}
}
}
else if (sheet is HSSFSheet)
{
HSSFShapeContainer shanpContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
if (shanpContainer != null)
{
foreach (HSSFShape shape in shanpContainer.Children ?? new List <HSSFShape>())
{
if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
{
HSSFPicture picture = shape as HSSFPicture;
HSSFClientAnchor anchor = shape.Anchor as HSSFClientAnchor;
result.Add(new PictureInfo(anchor.Row1, anchor.Row1, anchor.Col1, anchor.Col1, picture.PictureData.Data));
}
}
}
}
return(result);
}
/// <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);
}
public void SetStyle(HSSFPicture picture)
{
if (OriginalSize)
{
picture.Resize();
}
if (Border)
{
//picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;//虚线，有效果
picture.LineStyle = LineStyle.Solid;//似乎看不出效果
}
else
{
picture.LineStyle = LineStyle.None;
}
}
/// <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();
}
}
/// <summary>
/// Creates the lowerlevel escher records for this shape.
/// </summary>
/// <param name="hssfShape">The HSSF shape.</param>
/// <param name="shapeId">The shape id.</param>
/// <returns></returns>
private EscherContainerRecord CreateSpContainer(HSSFSimpleShape hssfShape, int shapeId)
{
HSSFPicture shape = (HSSFPicture)hssfShape;
EscherContainerRecord spContainer = new EscherContainerRecord();
EscherSpRecord sp = new EscherSpRecord();
EscherOptRecord opt = new EscherOptRecord();
EscherRecord anchor;
EscherClientDataRecord clientData = new EscherClientDataRecord();
spContainer.RecordId = EscherContainerRecord.SP_CONTAINER;
spContainer.Options = (short)0x000F;
sp.RecordId = EscherSpRecord.RECORD_ID;
sp.Options = (short)((EscherAggregate.ST_PICTUREFRAME << 4) | 0x2);
sp.ShapeId = shapeId;
sp.Flags = EscherSpRecord.FLAG_HAVEANCHOR | EscherSpRecord.FLAG_HASSHAPETYPE;
opt.RecordId = EscherOptRecord.RECORD_ID;
// opt.AddEscherProperty( new EscherBoolProperty( EscherProperties.PROTECTION__LOCKAGAINSTGROUPING, 0x00800080 ) ;
opt.AddEscherProperty(new EscherSimpleProperty(EscherProperties.BLIP__BLIPTODISPLAY, false, true, shape.PictureIndex));
// opt.AddEscherProperty( new EscherComplexProperty( EscherProperties.BLIP__BLIPFILENAME, true, new byte[] { (byte)0x74, (byte)0x00, (byte)0x65, (byte)0x00, (byte)0x73, (byte)0x00, (byte)0x74, (byte)0x00, (byte)0x00, (byte)0x00 } ) ;
// opt.AddEscherProperty( new EscherSimpleProperty( EscherProperties.Fill__FillTYPE, 0x00000003 ) ;
AddStandardOptions(shape, opt);
HSSFAnchor userAnchor = shape.Anchor;
if (userAnchor.IsHorizontallyFlipped)
{
sp.Flags = sp.Flags | EscherSpRecord.FLAG_FLIPHORIZ;
}
if (userAnchor.IsVerticallyFlipped)
{
sp.Flags = sp.Flags | EscherSpRecord.FLAG_FLIPVERT;
}
anchor = CreateAnchor(userAnchor);
clientData.RecordId = EscherClientDataRecord.RECORD_ID;
clientData.Options = (short)0x0000;
spContainer.AddChildRecord(sp);
spContainer.AddChildRecord(opt);
spContainer.AddChildRecord(anchor);
spContainer.AddChildRecord(clientData);
return(spContainer);
}
public void TestReadExistingImage()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("pictures") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFPicture picture = (HSSFPicture)Drawing.Children[0];
Assert.AreEqual(picture.PictureIndex, 2);
Assert.AreEqual(picture.LineStyleColor, HSSFShape.LINESTYLE__COLOR_DEFAULT);
Assert.AreEqual(picture.FillColor, 0x5DC943);
Assert.AreEqual(picture.LineWidth, HSSFShape.LINEWIDTH_DEFAULT);
Assert.AreEqual(picture.LineStyle, HSSFShape.LINESTYLE_DEFAULT);
Assert.AreEqual(picture.IsNoFill, false);
picture.PictureIndex = (2);
Assert.AreEqual(picture.PictureIndex, 2);
}
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);
}
