/// <summary> /// Adds the custom shape. /// </summary> /// <param name="ws">Worksheet</param> /// <param name="colIndex">Column Index</param> /// <param name="rowIndex">Row Index</param> /// <param name="shapeStyle">Shape style</param> /// <param name="text">Text for the shape</param> private static void AddCustomShape(ExcelWorksheet ws, int colIndex, int rowIndex, eShapeStyle shapeStyle, string text) { ExcelShape shape = ws.Drawings.AddShape("cs" + rowIndex.ToString() + colIndex.ToString(), shapeStyle); shape.From.Column = colIndex; shape.From.Row = rowIndex; shape.From.ColumnOff = Pixel2MTU(5); shape.SetSize(100, 100); shape.RichText.Add(text); }
/// <summary> /// Addre Custom Shape with text to WorkSheet /// </summary> /// <param name="excelWorksheet"></param> /// <param name="colIndex"></param> /// <param name="rowIndex"></param> /// <param name="shapeStyle">eShapeStyle</param> /// <param name="text">Shape Content inside</param> /// <param name="shapeSize">Size of Shape</param> /// <returns></returns> public ExcelWorksheet AddCustomShape(ExcelWorksheet excelWorksheet, int colIndex, int rowIndex, eShapeStyle shapeStyle, string text, Size shapeSize) { ExcelShape shape = excelWorksheet.Drawings.AddShape("cs" + rowIndex.ToString() + colIndex.ToString(), shapeStyle); shape.From.Column = colIndex; shape.From.Row = rowIndex; shape.From.ColumnOff = Pixel2MTU(5); shape.SetSize(shapeSize.Width, shapeSize.Height); shape.RichText.Add(text); return(excelWorksheet); }
/// <summary> /// Adding custom shape or object in specifed cell of specified excel sheet /// </summary> /// <param name="oSheet">The ExcelWorksheet object</param> /// <param name="rowIndex">The row number of the cell where the object will put</param> /// <param name="colIndex">The column number of the cell where the object will put</param> /// <param name="shapeStyle">The style of the shape of the object</param> /// <param name="text">Text inside the object</param> private void AddCustomObject(ExcelWorksheet oSheet, int rowIndex, int colIndex, eShapeStyle shapeStyle, string text) { ExcelShape excelShape = oSheet.Drawings.AddShape("Custom Object", shapeStyle); excelShape.From.Column = colIndex; excelShape.From.Row = rowIndex; excelShape.SetSize(100, 100); // 5x5 px space for better alignment excelShape.From.RowOff = Pixel2MTU(5); excelShape.From.ColumnOff = Pixel2MTU(5); // Adding text into the shape excelShape.RichText.Add(text); }
private static void SetExcelStyle(ExcelPackage package) { ExcelWorksheet sheet = package.Workbook.Worksheets.Add("test"); sheet.Cells[1, 1].Value = "Project Name"; sheet.Cells[1, 2].Value = "Project Price"; sheet.Cells[1, 3].Value = "Sale Percent"; sheet.Cells[2, 1].Value = "Rice"; sheet.Cells[2, 2].Value = 65; sheet.Cells[2, 3].Value = 100; sheet.Cells["D2:D2"].Formula = "B2*C2"; sheet.Cells[3, 1].Value = "玉米"; sheet.Cells[3, 2].Value = 45; sheet.Cells[3, 3].Value = 150; sheet.Cells["D3:D3"].Formula = "B3*C3"; sheet.Cells[4, 1].Value = "小米"; sheet.Cells[4, 2].Value = 38; sheet.Cells[4, 3].Value = 130; sheet.Cells["D4:D4"].Formula = "B4*C4"; sheet.Cells[5, 1].Value = "糯米"; sheet.Cells[5, 2].Value = 22; sheet.Cells[5, 3].Value = 200; sheet.Cells["D5:D5"].Formula = "B5*C5"; // auto sum //sheet.Cells[6, 2, 6, 4].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 2, 5, 2).Address); // set cell formula, and keep two decimals. sheet.Cells[5, 3].Style.Numberformat.Format = "#,##0.00"; sheet.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[1, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[1, 4, 1, 5].Merge = true; // merge cell sheet.Cells.Style.WrapText = true; // auto new line // set backgroud sheet.Cells[1, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; sheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128)); // set border sheet.Cells[1, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); sheet.Cells[1, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); // set cell row height and column width sheet.Cells.Style.ShrinkToFit = true; // auto adapter sheet.Row(1).Height = 15; // set row height sheet.Row(1).CustomHeight = true; // auto adapter row height sheet.Column(1).Width = 15; // set column width. // set sheet background sheet.View.ShowGridLines = false; // remove grid line sheet.Cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; sheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray); // set backgroud color sheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg"); // set backgroud image // insert image ExcelPicture picture = sheet.Drawings.AddPicture("Logo", Image.FromFile(@"firstbg.jpg")); picture.SetPosition(100, 100);// set picture position picture.SetSize(100, 100); // insert shape ExcelShape shape = sheet.Drawings.AddShape("shape", eShapeStyle.Rect); shape.Font.Color = Color.Red; shape.Font.Size = 15; shape.Font.Bold = true; shape.Fill.Style = eFillStyle.NoFill; shape.Border.Fill.Style = eFillStyle.NoFill; shape.SetPosition(200, 300); shape.SetSize(80, 30); shape.Text = "Text"; // add Hyperlink for image picture = sheet.Drawings.AddPicture("Logo", Image.FromFile(@"firstbg.jpg"), new ExcelHyperLink("http://www.cnblogs.com", UriKind.Relative)); // add Hyperlink for cell sheet.Cells[1, 1].Hyperlink = new ExcelHyperLink("Http://www.cnblogs.com", UriKind.Relative); // hide sheet sheet.Hidden = eWorkSheetHidden.Hidden; sheet.Column(1).Hidden = true; sheet.Row(1).Hidden = true; }