public void BaseTestResize(IPicture input, IPicture Compare, double scaleX, double scaleY)
{
input.Resize(scaleX, scaleY);
IClientAnchor inpCA = input.ClientAnchor;
IClientAnchor cmpCA = Compare.ClientAnchor;
Size inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size cmpDim = ImageUtils.GetDimensionFromAnchor(Compare);
double emuPX = Units.EMU_PER_PIXEL;
Assert.AreEqual(inpDim.Height, cmpDim.Height, emuPX * 6, "the image height differs");
Assert.AreEqual(inpDim.Width, cmpDim.Width, emuPX * 6, "the image width differs");
Assert.AreEqual(inpCA.Col1, cmpCA.Col1, "the starting column differs");
Assert.AreEqual(inpCA.Dx1, cmpCA.Dx1, 1, "the column x-offset differs");
Assert.AreEqual(inpCA.Dy1, cmpCA.Dy1, 1, "the column y-offset differs");
Assert.AreEqual(inpCA.Col2, cmpCA.Col2, "the ending columns differs");
// can't compare row heights because of variable test heights
input.Resize();
inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size imgDim = input.GetImageDimension();
Assert.AreEqual(imgDim.Height, inpDim.Height / emuPX, 1, "the image height differs");
Assert.AreEqual(imgDim.Width, inpDim.Width / emuPX, 1, "the image width differs");
}
public void BaseTestResize(IPicture input, IPicture Compare, double scaleX, double scaleY)
{
input.Resize(scaleX, scaleY);
IClientAnchor inpCA = input.ClientAnchor;
IClientAnchor cmpCA = Compare.ClientAnchor;
Size inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size cmpDim = ImageUtils.GetDimensionFromAnchor(Compare);
double emuPX = Units.EMU_PER_PIXEL;
Assert.AreEqual(inpDim.Height, cmpDim.Height, emuPX * 6, "the image height differs");
Assert.AreEqual(inpDim.Width, cmpDim.Width, emuPX * 6, "the image width differs");
Assert.AreEqual(inpCA.Col1, cmpCA.Col1, "the starting column differs");
Assert.AreEqual(inpCA.Dx1, cmpCA.Dx1, 1, "the column x-offset differs");
Assert.AreEqual(inpCA.Dy1, cmpCA.Dy1, 1, "the column y-offset differs");
Assert.AreEqual(inpCA.Col2, cmpCA.Col2, "the ending columns differs");
// can't compare row heights because of variable test heights
input.Resize();
inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size imgDim = input.GetImageDimension();
Assert.AreEqual(imgDim.Height, inpDim.Height / emuPX, 1, "the image height differs");
Assert.AreEqual(imgDim.Width, inpDim.Width / emuPX, 1, "the image width differs");
}
/// <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("保存成功！");
}
}
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);
sheet.AddMergedRegion(merge);
var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";
byte[] data = ArquivosUtil.RetornarArquivo(caminho);
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 1;
anchor.Row1 = 1;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/
return(sheet);
}
/// <summary>
/// 在单元格中插入图片
/// </summary>
private static void InsertImage(ICell cell, byte[] picBytes, PictureType pt)
{
var row1 = cell.RowIndex;
var col1 = cell.ColumnIndex;
var workbook = cell.Sheet.Workbook;
var sheet = cell.Sheet;
/* Calcualte the image size */
MemoryStream ms = new MemoryStream(picBytes);
Image Img = Bitmap.FromStream(ms, true);
double ImageOriginalWidth = Img.Width;
double ImageOriginalHeight = Img.Height;
double scale = cell.Sheet.GetColumnWidth(cell.ColumnIndex) / 36.5 / ImageOriginalWidth;
cell.Row.Height = (short)(ImageOriginalHeight * scale * 15);
//cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)(ImageOriginalWidth * scale * 36.5));
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, pt); //添加图片
/* Create the drawing container */
IDrawing drawing = sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize(1);
picBytes = null;
}
/// <summary>
/// 处理图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="ExportPictures"></param>
/// <returns></returns>
private void ProcessPicture(ISheet sheet, IEnumerable <ExportPictureEntity> ExportPictures)
{
if (ExportPictures == null || ExportPictures.Any() == false)
{
return;
}
foreach (var item in ExportPictures)
{
IRow row = sheet.GetRow(item.RowIndex);
if (row == null)
{
row = sheet.CreateRow(item.RowIndex);
}
ICell cell = row.GetCell(item.ColIndex);
if (cell == null)
{
cell = row.CreateCell(item.ColIndex);
}
PictureEntity entity = cell.GetPictureData(item.Url, item.UrlType);
if (entity == null)
{
continue;
}
IPicture pic = sheet.CreateDrawingPatriarch().CreatePicture(entity.Anchor, entity.PictureIndex);
if (item.Scale != 1)
{
pic.Resize(item.Scale);
}
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
/// <summary>
/// 向excel添加图片
/// </summary>
/// <param name="XlsFilePath"></param>
/// <param name="pictureData"></param>
/// <param name="sheetName"></param>
public static void AddPicture(string XlsFilePath, byte[] pictureData, bool isNewSheet, string sheetName = "PicTure")
{
FileStream fs = File.OpenRead(XlsFilePath);
//IWorkbook workbook = new XSSFWorkbook(fs);
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); // (NPOI.XSSF.UserModel.XSSFWorkbook)WorkbookFactory.Create(XlsFilePath);
fs.Close();
int pictureIdx = book.AddPicture(pictureData, PictureType.JPEG);
NPOI.SS.UserModel.ISheet sheet = null;
if (!isNewSheet)
{
sheet = book.GetSheetAt(0);
}
else
{
sheet = book.CreateSheet(sheetName);
}
IDrawing patriarch = sheet.CreateDrawingPatriarch();
NPOI.XSSF.UserModel.XSSFClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
using (FileStream fileStream = File.Open(XlsFilePath,
FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
book.Write(fileStream);
fileStream.Close();
}
}
public void Test45829()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet();
IDrawing p1 = sh1.CreateDrawingPatriarch();
byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("45829.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
IPicture pic = p1.CreatePicture(new HSSFClientAnchor(), idx1);
pic.Resize();
}
/// <summary>
/// Carga la imagen con el nombre de archivo pasado por parametro en la imagen pasada por parametro.
/// </summary>
/// <remarks>
/// Si el tamanio de la imagen leida no coincide con la pasada por parametro, se cambia el tamanio
/// de la imagen pasada por parametro mediante la operacion <code>IPicture.Resize</code>.
/// </remarks>
/// <param name="name"></param>
/// <param name="picture"></param>
public void ReadIntoImage(String name, IPicture picture)
{
Debug.Assert(name != null, "El nombre de la imagen no debe ser nulo");
Debug.Assert(picture != null, "La imagen sobre la cual cargar los colores no debe ser nula");
Bitmap bitmap = new Bitmap(name);
picture.Resize(bitmap.Width, bitmap.Height);
BitmapData bitmapData = bitmap.LockBits(new Rectangle(0, 0,
bitmap.Width, bitmap.Height),
ImageLockMode.ReadWrite,
PixelFormat.Format32bppArgb);
ReadIntoImage(bitmapData, picture);
bitmap.UnlockBits(bitmapData);
}
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource)
{
IRow row = null;
ICell cell = null;
ICellStyle cellStyle = null;
IDrawing drawing = null;
IPicture picture = null;
cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++)
{
row = target.CreateRow(rowIndex + 1);
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
cell = row.CreateCell(columnIndex);
if (dataSource.Columns[columnIndex].DataType == typeof(byte[]))
{
byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[];
if (image != null && image.Length > 0)
{
int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG);
drawing = target.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1);
picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize();
}
}
else
{
cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString());
}
cell.CellStyle = cellStyle;
}
}
}
public override void Format(SheetAdapter sheetAdapter)
{
ISheet currentSheet = sheetAdapter.CurrentSheet;
List <object> shapes = this.PictureInfo.GetShapes(currentSheet);
bool flag = false;
if (currentSheet is HSSFSheet)
{
flag = true;
}
if (shapes == null || shapes.Count <= 0)
{
throw new Exception(string.Format("未能获取到工作薄[{0}]指定区域的图形对象列表！", currentSheet.SheetName));
}
byte[] pictureData = File.ReadAllBytes(this.PictureInfo.FilePath);
IClientAnchor anchor;
IDrawing drawing;
if (flag)
{
HSSFShape hSSFShape = shapes[0] as HSSFShape;
anchor = (hSSFShape.Anchor as IClientAnchor);
drawing = hSSFShape.Patriarch;
hSSFShape.LineStyle = LineStyle.None;
}
else
{
XSSFShape xSSFShape = shapes[0] as XSSFShape;
anchor = (xSSFShape.GetAnchor() as IClientAnchor);
drawing = xSSFShape.GetDrawing();
xSSFShape.LineStyle = LineStyle.None;
}
int pictureIndex = currentSheet.Workbook.AddPicture(pictureData, this.PictureInfo.PictureType);
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
if (this.PictureInfo.AutoSize)
{
picture.Resize();
}
}
private void handleResize(IWorkbook wb, ISheet sheet, IRow row)
{
IDrawing Drawing = sheet.CreateDrawingPatriarch();
ICreationHelper CreateHelper = wb.GetCreationHelper();
byte[] bytes = HSSFITestDataProvider.Instance.GetTestDataFileContent("logoKarmokar4.png");
row.HeightInPoints = (/*setter*/ GetImageSize(bytes).Y);
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
//add a picture shape
IClientAnchor anchor = CreateHelper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = (/*setter*/ 0);
anchor.Row1 = (/*setter*/ 0);
IPicture pict = Drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
}
public void BaseTestResize(IClientAnchor referenceAnchor)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh1 = wb.CreateSheet();
IDrawing p1 = sh1.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
byte[] pictureData = _testDataProvider.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
IPicture picture = p1.CreatePicture(factory.CreateClientAnchor(), idx1);
picture.Resize();
IClientAnchor anchor1 = picture.GetPreferredSize();
//assert against what would BiffViewer print if we insert the image in xls and dump the file
Assert.AreEqual(referenceAnchor.Col1, anchor1.Col1);
Assert.AreEqual(referenceAnchor.Row1, anchor1.Row1);
Assert.AreEqual(referenceAnchor.Col2, anchor1.Col2);
Assert.AreEqual(referenceAnchor.Row2, anchor1.Row2);
Assert.AreEqual(referenceAnchor.Dx1, anchor1.Dx1);
Assert.AreEqual(referenceAnchor.Dy1, anchor1.Dy1);
Assert.AreEqual(referenceAnchor.Dx2, anchor1.Dx2);
Assert.AreEqual(referenceAnchor.Dy2, anchor1.Dy2);
}
public static void drawWaterRemarkPath(HSSFWorkbook wb, string waterRemarkPath)
{
int startXCol = 0; // 水印起始列
int startYRow = 2; // 水印起始行
int betweenXCol = 1; // 水印横向之间间隔多少列
int betweenYRow = 2; // 水印纵向之间间隔多少行
int XCount = 0; // 横向共有水印多少个
int YCount = 0; // 纵向共有水印多少个
int waterRemarkWidth = 0; // 水印图片宽度为多少列
int waterRemarkHeight = 0; // 水印图片高度为多少行
// 校验传入的水印图片格式
if(!waterRemarkPath.EndsWith("png") && !waterRemarkPath.EndsWith("PNG"))
{
//throw new RuntimeException("向Excel上面打印水印，目前支持png格式的图片。");
}
// 加载图片
FileStream fs = new FileStream(waterRemarkPath, FileMode.Open, FileAccess.Read); //将图片以文件流的形式进行保存
BinaryReader br = new BinaryReader(fs);
byte[] imgBytesIn = br.ReadBytes((int)fs.Length); //将流读入到字节数组中
for (int i = 0; i < wb.NumberOfSheets; i++)
{ // wb.getNumberOfSheets()
ISheet sheet = wb.GetSheetAt(0);
sheet.ProtectSheet("Yelioa123");
try
{
XCount = (sheet.GetRow(0).LastCellNum);
if (XCount >= 0 && XCount <= 5)
{
XCount = 1;
}
if (XCount >= 5)
{
XCount = XCount / 2;
}
}
catch (Exception e)
{
XCount = 1;
}
try
{
YCount = sheet.LastRowNum / (betweenYRow); // 纵向共有水印多少个
if (YCount < 4)
{
YCount = 2;
}
}
catch (Exception e)
{
YCount = 50;
}
// 开始打水印
IDrawing drawing = sheet.CreateDrawingPatriarch();
// 按照共需打印多少行水印进行循环
for (int yCount = 0; yCount < YCount; yCount++)
{
// 按照每行需要打印多少个水印进行循环
for (int xCount = 0; xCount < XCount; xCount++)
{
// 创建水印图片位置
int xIndexInteger = startXCol + (xCount * 2) + (xCount * betweenXCol);
int yIndexInteger = startYRow + (yCount * 1) + (yCount * betweenYRow);
/*参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）； 第四个参数是（是y轴的结束节点）；
* 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）； 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
* 第七个参数是（图片宽度，共多少列）； 第8个参数是（图片高度，共多少行）；
* */
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, xIndexInteger, yIndexInteger,
xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
IPicture pic = drawing.CreatePicture(anchor,
wb.AddPicture(imgBytesIn, PictureType.PNG));
pic.Resize();
}
}
}
}
/// <summary>
/// 图片在单元格等比缩放居中显示
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="value">图片二进制流</param>
private void CellImage(ICell cell, byte[] value)
{
if (value.Length == 0)
{
return; //空图片处理
}
double scalx = 0; //x轴缩放比例
double scaly = 0; //y轴缩放比例
int Dx1 = 0; //图片左边相对excel格的位置(x偏移) 范围值为:0~1023,超过1023就到右侧相邻的单元格里了
int Dy1 = 0; //图片上方相对excel格的位置(y偏移) 范围值为:0~256,超过256就到下方的单元格里了
bool bOriginalSize = false; //是否显示图片原始大小 true表示图片显示原始大小 false表示显示图片缩放后的大小
///计算单元格的长度和宽度
double CellWidth = 0;
double CellHeight = 0;
int RowSpanCount = 1; //合并的单元格行数
int ColSpanCount = 1; //合并的单元格列数
int j = 0;
for (j = 0; j < RowSpanCount; j++)//根据合并的行数计算出高度
{
CellHeight += cell.Sheet.GetRow(cell.RowIndex + j).Height;
}
for (j = 0; j < ColSpanCount; j++)
{
CellWidth += cell.Row.Sheet.GetColumnWidth(cell.ColumnIndex + j);
}
//单元格长度和宽度与图片的长宽单位互换是根据实例得出
CellWidth = CellWidth / 35;
CellHeight = CellHeight / 15;
///计算图片的长度和宽度
MemoryStream ms = new MemoryStream(value);
Image Img = Bitmap.FromStream(ms, true);
double ImageOriginalWidth = Img.Width; //原始图片的长度
double ImageOriginalHeight = Img.Height; //原始图片的宽度
double ImageScalWidth = 0; //缩放后显示在单元格上的图片长度
double ImageScalHeight = 0; //缩放后显示在单元格上的图片宽度
if (CellWidth > ImageOriginalWidth && CellHeight > ImageOriginalHeight) //单元格的长度和宽度比图片的大，说明单元格能放下整张图片，不缩放
{
ImageScalWidth = ImageOriginalWidth;
ImageScalHeight = ImageOriginalHeight;
bOriginalSize = true;
}
else//需要缩放，根据单元格和图片的长宽计算缩放比例
{
bOriginalSize = false;
if (ImageOriginalWidth > CellWidth && ImageOriginalHeight > CellHeight) //图片的长和宽都比单元格的大的情况
{
double WidthSub = ImageOriginalWidth - CellWidth; //图片长与单元格长的差距
double HeightSub = ImageOriginalHeight - CellHeight; //图片宽与单元格宽的差距
if (WidthSub > HeightSub) //长的差距比宽的差距大时,长度x轴的缩放比为1，表示长度就用单元格的长度大小，宽度y轴的缩放比例需要根据x轴的比例来计算
{
scalx = 1;
scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;//计算y轴的缩放比例,CellWidth / ImageWidth计算出图片整体的缩放比例,然后 * ImageHeight计算出单元格应该显示的图片高度,然后/ CellHeight就是高度的缩放比例
}
else
{
scaly = 1;
scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
}
}
else if (ImageOriginalWidth > CellWidth && ImageOriginalHeight < CellHeight)//图片长度大于单元格长度但图片高度小于单元格高度，此时长度不需要缩放，直接取单元格的，因此scalx=1，但图片高度需要等比缩放
{
scalx = 1;
scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;
}
else if (ImageOriginalWidth < CellWidth && ImageOriginalHeight > CellHeight)//图片长度小于单元格长度但图片高度大于单元格高度，此时单元格高度直接取单元格的，scaly = 1,长度需要等比缩放
{
scaly = 1;
scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
}
ImageScalWidth = scalx * CellWidth;
ImageScalHeight = scaly * CellHeight;
}
Dx1 = Convert.ToInt32((CellWidth - ImageScalWidth) / CellWidth * 1023 / 2);
Dy1 = Convert.ToInt32((CellHeight - ImageScalHeight) / CellHeight * 256 / 2);
int pictureIdx = cell.Sheet.Workbook.AddPicture((Byte[])value, PictureType.PNG);
IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.AnchorType = AnchorType.MoveDontResize;
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 1;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 1;
anchor.Dy1 = Dy1; //图片下移量
anchor.Dx1 = Dx1; //图片右移量，通过图片下移和右移，使得图片能居中显示，因为图片不同文字，图片是浮在单元格上的，文字是钳在单元格里的
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
IPicture pic = patriarch.CreatePicture(anchor, pictureIdx);
if (bOriginalSize)
{
pic.Resize();//显示图片原始大小
}
else
{
pic.Resize(scalx, scaly);//等比缩放
}
ms.Close();
ms.Dispose();
}
/// <summary>
/// 将datatable导出为excel
/// 图片默认显示在excel 第二行最后一列
/// </summary>
/// <param name="table">数据源</param>
/// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
/// <param name="sheetName">工作簿名称</param>
/// <param name="picBytes">导出图片字节流</param>
/// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
/// <returns></returns>
public static MemoryStream ExportToExcel2007(DataTable table, List <Tuple <string, string, int> > excelInfo, string sheetName, byte[] picBytes, List <CellRangeAddress> mergedRegion)
{
MemoryStream ms = new MemoryStream();
try
{
using (table)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < excelInfo.Count; i++)
{
sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
}
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelInfo.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < excelInfo.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
}
rowIndex++;
}
//合并单元格
if (mergedRegion != null && mergedRegion.Count > 0)
{
foreach (CellRangeAddress cellRangeAddress in mergedRegion)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(cellRangeAddress);
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//将新的样式赋给单元格
var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
cell.CellStyle = style;
}
}
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
var row1 = 2;
var col1 = excelInfo.Count + 1;
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, PictureType.PNG); //添加图片
/* Create the drawing container */
IDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize();
picBytes = null;
}
workbook.Write(ms);
workbook.Close();
}
}
catch (Exception ex)
{
ms = null;
}
return(ms);
}
public static void writeExcel(List <Product> products)
{
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Products");
ws.SetColumnWidth(0, 6000);
IRow header = ws.CreateRow(0);
header.CreateCell(0).SetCellValue("Name");
header.CreateCell(1).SetCellValue("Price");
header.CreateCell(2).SetCellValue("Old Price");
header.CreateCell(3).SetCellValue("Difference");
header.CreateCell(4).SetCellValue("Category");
header.CreateCell(5).SetCellValue("Picture");
header.CreateCell(6).SetCellValue("ID");
header.CreateCell(7).SetCellValue("URL");
int rowcount = 1;
foreach (Product product in products)
{
IRow ProductRow = ws.CreateRow(rowcount);
ProductRow.CreateCell(0).SetCellValue(product.name);
ProductRow.CreateCell(1).SetCellValue(product.price);
ProductRow.CreateCell(2).SetCellValue(product.xprice);
ProductRow.CreateCell(3).SetCellValue(product.dif);
ProductRow.CreateCell(4).SetCellValue(product.category);
ProductRow.CreateCell(5);
ProductRow.CreateCell(6).SetCellValue(product.ID);
ProductRow.CreateCell(7).SetCellValue(product.URL);
ProductRow.Height = 1500;
if (File.Exists(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png"))
{
byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
int pictureIndex = wb.AddPicture(data, PictureType.PNG);
IDrawing patriarch = ws.CreateDrawingPatriarch();
IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = 5;
anchor.Row1 = rowcount;
anchor.AnchorType = AnchorType.MoveAndResize;
IPicture picture = patriarch.CreatePicture(anchor, pictureIndex);
picture.Resize(1);
//byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
//int pictureIndex = wb.AddPicture(data, PictureType.PNG);
//ICreationHelper helper = wb.GetCreationHelper();
//IDrawing drawing = ws.CreateDrawingPatriarch();
//IClientAnchor anchor = helper.CreateClientAnchor();
//anchor.Col1 = 5;//0 index based column
//anchor.Row1 = ProductRow.RowNum;//0 index based row
//IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
//picture.Resize();
}
rowcount++;
}
IRow row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Stop");
rowcount++;
row = ws.CreateRow(rowcount);
rowcount++;
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Skipped products");
rowcount++;
foreach (String url in Program.skippedURLs)
{
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue(url);
rowcount++;
}
Stream stream = new FileStream(ProductsExcelPath, FileMode.Create);
//Stream stream = new FileStream(@"C:\Users\email\Desktop\Hardware Hub\products.xlsx", FileMode.Open);
wb.Write(stream);
wb.Close();
stream.Close();
}
/// <summary>
/// 导出Excel DataTable
/// </summary>
/// <param name="records">records必须都为DataTable</param>
/// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param>
/// <returns></returns>
public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "")
{
Stopwatch sw = new Stopwatch();
sw.Start();
var workBook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
var sheet = workBook.CreateSheet();
var headerRow = sheet.CreateRow(0);
var cellIndex = 0;
Color lightGrey = Color.FromArgb(221, 221, 221);
ICellStyle cstyle = workBook.CreateCellStyle();
cstyle.Alignment = HorizontalAlignment.Center;
cstyle.IsLocked = true;
// cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed;
cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
foreach (var map in Maps)
{
var hcell = headerRow.CreateCell(cellIndex, CellType.String);
hcell.CellStyle = cstyle;
hcell.SetCellValue(map.Name);
cellIndex++;
}
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
var rowIndex = 1;
IDrawing patriarch = sheet.CreateDrawingPatriarch();
bool isimg = false;
foreach (DataRow record in records.Rows)
{
var dr = sheet.CreateRow(rowIndex);
for (int i = 0; i < Maps.Count; i++)
{
string drValue = record[Maps[i].Info.ToString()].ToString();
ICell cell = dr.CreateCell(i);
if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null)
{
ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()];
if (!string.IsNullOrEmpty(drValue))
{
if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList)
{
cell.SetCellValue(drValue);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(','));
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i);
XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);
sheet.AddValidationData(dataValidate);
}
else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage)
{
if (File.Exists(@"" + imgBasepath + drValue))
{
if (!isimg)
{
sheet.SetColumnWidth(i, 256 * 20);
isimg = true;
}
dr.HeightInPoints = 90;
byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue);
int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1,
rowIndex + 1);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(0.3);
}
else
{
cell.SetCellValue("");
}
}
else
{
cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans));
}
}
else
{
cell.SetCellValue(drValue);
}
}
else
{
switch (records.Columns[Maps[i].Info].DataType.ToString())
{
case "System.String": //字符串类型
cell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
//cell.CellStyle =
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}
rowIndex++;
}
workBook.Write(ms);
byte[] buffer = ms.ToArray();
ms.Close();
sw.Stop();
return(buffer);
}
protected override void Writing(object sender, WriteEventArgs args)
{
Cell cell = args.Entity as Cell;
ISheet exSheet = args.ExSheet;
if (cell == null)
{
return;
}
object tmpObject = this.GetValue();
if (tmpObject == null)
{
return;
}
IWorkbook book = exSheet.Workbook;
//IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();
IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();//只能有一个实例，否则只有最后一个对象生效
int rowIndex = this.RowIndex;
int colIndex = this.ColIndex;
IRow exRow = exSheet.GetRow(rowIndex) ?? exSheet.CreateRow(rowIndex);
//if (exRow != null)
//{
ICell exCell = exRow.GetCell(this.ColIndex) ?? exRow.CreateCell(this.ColIndex);
//if (exCell != null)
//{
//object tmpObject = sheet.IsDynamic ? sheet.GetValue(cell,sheetName) : dt.Rows[cell.DataIndex][cell.Field];
if (cell.ValueAppend)
{
tmpObject = exCell.StringCellValue + tmpObject;
}
if (tmpObject.GetType() == typeof(byte[]))//处理图片
{
CellRangeAddress region = NPOIExcelUtil.GetRange(exCell);
Image image = new Bitmap(new MemoryStream(tmpObject as byte[]));
Size size = image.Size;
if (size.IsEmpty)
{
return;
}
IClientAnchor anchor = region != null?
draw.CreateAnchor(0, 0, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, colIndex, rowIndex, colIndex + this.ColCount, rowIndex + this.RowCount);
IPicture pic = draw.CreatePicture(anchor, book.AddPicture((byte[])tmpObject, PictureType.JPEG));
switch (cell.FillType)
{
case FillType.Origin:
pic.Resize();
break;
case FillType.Scale:
float width = 0, height = 0;
for (int i = anchor.Col1; i < anchor.Col2; i++)
{
width += exSheet.GetColumnWidth(i) / 256f * 12;
}
for (int i = anchor.Row1; i < anchor.Row2; i++)
{
IRow row = exSheet.GetRow(i);
height += row != null ? row.HeightInPoints : exSheet.DefaultRowHeightInPoints;
}
float factor = Math.Min(width / (size.Width * 0.75f), height / (size.Height * 0.75f));
pic.Resize(factor);
break;
default:
break;
}
}
else
{
exCell.SetCellValue((tmpObject ?? "").ToString());
}
//}
//}
}