/// <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);
}
}
/// 向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 void TestClientAnchorFromEscher()
{
EscherClientAnchorRecord escher = new EscherClientAnchorRecord();
escher.Col1 = ((short)11);
escher.Col2 = ((short)12);
escher.Row1 = ((short)13);
escher.Row2 = ((short)14);
escher.Dx1 = ((short)15);
escher.Dx2 = ((short)16);
escher.Dy1 = ((short)17);
escher.Dy2 = ((short)18);
HSSFClientAnchor anchor = new HSSFClientAnchor(escher);
Assert.AreEqual(anchor.Col1, 11);
Assert.AreEqual(escher.Col1, 11);
Assert.AreEqual(anchor.Col2, 12);
Assert.AreEqual(escher.Col2, 12);
Assert.AreEqual(anchor.Row1, 13);
Assert.AreEqual(escher.Row1, 13);
Assert.AreEqual(anchor.Row2, 14);
Assert.AreEqual(escher.Row2, 14);
Assert.AreEqual(anchor.Dx1, 15);
Assert.AreEqual(escher.Dx1, 15);
Assert.AreEqual(anchor.Dx2, 16);
Assert.AreEqual(escher.Dx2, 16);
Assert.AreEqual(anchor.Dy1, 17);
Assert.AreEqual(escher.Dy1, 17);
Assert.AreEqual(anchor.Dy2, 18);
Assert.AreEqual(escher.Dy2, 18);
}
private static void DrawLinesToCenter(HSSFPatriarch patriarch)
{
// Draw some lines from and to the corners
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 0, 0, (short)2, 2, 512, 128);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 512, 128, (short)2, 2, 1023, 0);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)1, 1, 0, 0, (short)1, 1, 512, 100);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)1, 1, 512, 100, (short)1, 1, 1023, 0);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
}
/// <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());
}
}
//导出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 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();
}
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 int LoadImage(string path, ISheet sheet, int row, int col, string name)
{
var w = (int)(sheet.GetColumnWidth(col) / 36.56) - 1;//36.56
var h = (int)(sheet.GetRow(row).Height / 15) - 1;
var fileExtension = Path.GetExtension(path + name);
var pPath = string.Format("{0}{1:yyyyMMddHHmmssfff}{2}", path, DateTime.Now, fileExtension);
// bool b = Picture.GetPicThumbnail_Filling(path + name, pPath, h, w, 90);
var id = 0;
using (var file = new FileStream(pPath, FileMode.Open, FileAccess.Read))
{
var buffer = new byte[file.Length];
file.Read(buffer, 0, (int)file.Length);
id = sheet.Workbook.AddPicture(buffer, PictureType.JPEG);
var patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//create the anchor
var anchor = new HSSFClientAnchor(10, 4, 1023, 255, col, row, col, row);
anchor.AnchorType = (AnchorType)1;
//load the picture and get the picture index in the workbook
var picture = (HSSFPicture)patriarch.CreatePicture(anchor, id);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
//picture.LineStyle = LineStyle.DashDotGel;
}
//File.Delete(pPath);
return(id);
}
/// <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);
}
}
/**
* Generates the escher shape records for this shape.
*/
private EscherContainerRecord CreateSpContainer(HSSFSimpleShape shape, int shapeId)
{
EscherContainerRecord spContainer = new EscherContainerRecord();
EscherSpRecord sp = new EscherSpRecord();
EscherOptRecord opt = new EscherOptRecord();
EscherClientDataRecord clientData = new EscherClientDataRecord();
spContainer.RecordId = (EscherContainerRecord.SP_CONTAINER);
spContainer.Options = ((short)0x000F);
sp.RecordId = (EscherSpRecord.RECORD_ID);
sp.Options = ((short)((EscherAggregate.ST_HOSTCONTROL << 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, 17039620));
opt.AddEscherProperty(new EscherBoolProperty(EscherProperties.TEXT__SIZE_TEXT_TO_FIT_SHAPE, 0x00080008));
opt.AddEscherProperty(new EscherBoolProperty(EscherProperties.LINESTYLE__NOLINEDRAWDASH, 0x00080000));
opt.AddEscherProperty(new EscherSimpleProperty(EscherProperties.GROUPSHAPE__PRINT, 0x00020000));
HSSFClientAnchor userAnchor = (HSSFClientAnchor)shape.Anchor;
userAnchor.AnchorType = 1;
EscherRecord 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);
}
/// <summary>
/// Creates the anchor.
/// </summary>
/// <param name="userAnchor">The user anchor.</param>
/// <returns></returns>
public static EscherRecord CreateAnchor(HSSFAnchor userAnchor)
{
if (userAnchor is HSSFClientAnchor)
{
HSSFClientAnchor a = (HSSFClientAnchor)userAnchor;
EscherClientAnchorRecord anchor = new EscherClientAnchorRecord();
anchor.RecordId = EscherClientAnchorRecord.RECORD_ID;
anchor.Options = (short)0x0000;
anchor.Flag = (short)a.AnchorType;
anchor.Col1 = (short)Math.Min(a.Col1, a.Col2);
anchor.Dx1 = (short)a.Dx1;
anchor.Row1 = (short)Math.Min(a.Row1, a.Row2);
anchor.Dy1 = (short)a.Dy1;
anchor.Col2 = (short)Math.Max(a.Col1, a.Col2);
anchor.Dx2 = (short)a.Dx2;
anchor.Row2 = (short)Math.Max(a.Row1, a.Row2);
anchor.Dy2 = (short)a.Dy2;
return(anchor);
}
else
{
HSSFChildAnchor a = (HSSFChildAnchor)userAnchor;
EscherChildAnchorRecord anchor = new EscherChildAnchorRecord();
anchor.RecordId = EscherChildAnchorRecord.RECORD_ID;
anchor.Options = (short)0x0000;
anchor.Dx1 = (short)Math.Min(a.Dx1, a.Dx2);
anchor.Dy1 = (short)Math.Min(a.Dy1, a.Dy2);
anchor.Dx2 = (short)Math.Max(a.Dx2, a.Dx1);
anchor.Dy2 = (short)Math.Max(a.Dy2, a.Dy1);
return(anchor);
}
}
public void TestCreateClientAnchorFromContainer()
{
EscherContainerRecord container = new EscherContainerRecord();
EscherClientAnchorRecord escher = new EscherClientAnchorRecord();
escher.Flag = ((short)3);
escher.Col1 = ((short)11);
escher.Col2 = ((short)12);
escher.Row1 = ((short)13);
escher.Row2 = ((short)14);
escher.Dx1 = ((short)15);
escher.Dx2 = ((short)16);
escher.Dy1 = ((short)17);
escher.Dy2 = ((short)18);
container.AddChildRecord(escher);
HSSFClientAnchor anchor = (HSSFClientAnchor)HSSFAnchor.CreateAnchorFromEscher(container);
Assert.AreEqual(anchor.Col1, 11);
Assert.AreEqual(escher.Col1, 11);
Assert.AreEqual(anchor.Col2, 12);
Assert.AreEqual(escher.Col2, 12);
Assert.AreEqual(anchor.Row1, 13);
Assert.AreEqual(escher.Row1, 13);
Assert.AreEqual(anchor.Row2, 14);
Assert.AreEqual(escher.Row2, 14);
Assert.AreEqual(anchor.Dx1, 15);
Assert.AreEqual(escher.Dx1, 15);
Assert.AreEqual(anchor.Dx2, 16);
Assert.AreEqual(escher.Dx2, 16);
Assert.AreEqual(anchor.Dy1, 17);
Assert.AreEqual(escher.Dy1, 17);
Assert.AreEqual(anchor.Dy2, 18);
Assert.AreEqual(escher.Dy2, 18);
}
public void TestCanHaveRowUpTo65535()
{
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 65535, (short)0, 65535);
Assert.AreEqual(65535, anchor.Row1);
Assert.AreEqual(65535, anchor.Row2);
}
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);
}
public void TestGetAnchorHeightInPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
float p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.7, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (14);
a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(13.945, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(6.945, p, 0.001);
a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(0.054, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(14.0, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (12);
a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.372, p, 0.001);
}
/// <summary>
/// 导出按钮监听
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_ExTWO_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = @"Excel|*.xls";
sfd.ShowDialog();
string path = sfd.FileName;
PlotModel pm = plotView1.Model;
var pngExporter = new PngExporter {
Width = 1000, Height = 400, Background = OxyColors.White
};
Bitmap bitmap = pngExporter.ExportToBitmap(pm);
Image image = bitmap;
MemoryStream ms = new MemoryStream();
image.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
byte[] bytes = ms.ToArray();
IWorkbook workbook = new HSSFWorkbook();
workbook.CreateSheet("sheet1");
if (path != "")
{
using (FileStream fs = File.Create(path))
{
ISheet sheet = workbook.GetSheetAt(0);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10);
int pic = workbook.AddPicture(bytes, PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
IPicture ipic = patriarch.CreatePicture(anchor, pic);
ipic.Resize();
workbook.Write(fs);
}
MessageBox.Show("保存成功！");
}
}
public void 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 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 TestCanHaveRowGreaterThan32767()
{
// Maximum permitted row number should be 65535.
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 32768, (short)0, 32768);
Assert.AreEqual(32768, anchor.Row1);
Assert.AreEqual(32768, anchor.Row2);
}
/// <summary>
/// create the anchor
/// </summary>
/// <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>
/// <param name="anchorType"></param>
/// <returns></returns>
private static HSSFClientAnchor CreateClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2, AnchorType anchorType)
{
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
anchor.AnchorType = anchorType;
return(anchor);
}
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 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 TestPassIncompatibleTypeIsFalse()
{
HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 1, 2, 3, (short)4, 5, (short)6, 7);
Assert.AreNotSame(clientAnchor, "wrongType");
HSSFChildAnchor childAnchor = new HSSFChildAnchor(0, 1, 2, 3);
Assert.AreNotSame(childAnchor, "wrongType");
}
public void TestNullReferenceIsFalse()
{
HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 1, 2, 3, (short)4, 5, (short)6, 7);
Assert.IsFalse(clientAnchor.Equals(null), "Passing null to equals should return false");
HSSFChildAnchor childAnchor = new HSSFChildAnchor(0, 1, 2, 3);
Assert.IsFalse(childAnchor.Equals(null), "Passing null to equals should return false");
}
public static void CreateCellImage(HSSFWorkbook templateWorkbook, ISheet sheet, string url, int row1, int row2)
{
var patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, row1, 0, row2);
var picture = patriarch.CreatePicture(anchor, LoadImage(url, templateWorkbook));
picture.Resize();
}
