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()); } //} //} }