//Загрузка информации из текущей выдененной фигуры
private void LoadShape()
{
var sh = _book.ActiveShape();
if (sh == null)
{
ShapeType.Text = null;
ShapeId.Text = null;
ShapeName.Text = null;
ShapeFormula.Text = null;
ShapeLink.Text = null;
ShapeFormula.Enabled = false;
_curShape = null;
}
else if (sh != _curShape)
{
ShapeType.Text = sh.Type.ToString();
ShapeId.Text = sh.ID.ToString();
ShapeName.Text = sh.Name;
ShapeFormula.Text = MakeEnters(sh.AlternativeText);
ShapeLink.Text = sh.Title;
ShapeFormula.Enabled = sh.Type == MsoShapeType.msoGroup;
_curShape = sh;
}
}
private void PrintTextEffectsMenuItem_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
System.Drawing.Size size = TextRenderer.MeasureText("Text Effect ", new System.Drawing.Font("Arial", 20));
Excel.Worksheet sheet = _app.ActiveWorkbook.ActiveSheet;
Excel.Shapes shapes = sheet.Shapes;
int top = 0;
for (int i = 0; i < 50; i++)
{
Excel.Shape shape = shapes.AddTextEffect((Office.MsoPresetTextEffect)i,
string.Format("Text Effect {0}", i + 1),
"Arial",
20,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoFalse,
Convert.ToSingle(_app.ActiveWindow.VisibleRange.Width / 2) - size.Width / 2,
top
);
top += 30;
_textEffects.Add(shape);
}
return;
}
private void btnAddImage_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.FileName = "*";
dlg.DefaultExt = "bmp";
dlg.ValidateNames = true;
dlg.Filter = "Bitmap Image (.bmp)|*.bmp|Gif Image (.gif)|*.gif|JPEG Image (.jpeg)|*.jpeg|Png Image (.png)|*.png";
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Bitmap dImg = new Bitmap(dlg.FileName);
Excel.Shape IamgeAdd = Globals.ThisAddIn.Application.ActiveSheet.Shapes.AddPicture(dlg.FileName,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoCTrue,
20, 30, dImg.Width, dImg.Height);
}
//we should also clear the clip board
System.Windows.Forms.Clipboard.Clear();
}
private void ExtractShapesContents(List <string> contents, Excel.Shape shape)
{
if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoGroup)
{
//To check group or not, use only shape.Type or shape.AutoShapeType,
//because other ways like shape.GroupItem.Count & shape.Ungroup thow an exception when shape is not a group.
var groupShapes = shape.GroupItems;
foreach (Excel.Shape subShape in shape.GroupItems)
{
ExtractShapesContents(contents, subShape);
}
//for (int i = 1; i <= groupShapes.Count; i++)
//{
// ExtractShapesContents(contents, groupShapes.Item(i));
//}
}
else if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoCanvas)
{
foreach (Excel.Shape subShape in shape.CanvasItems)
{
ExtractShapesContents(contents, subShape);
}
}
else
{
var text = shape.TextFrame?.Characters()?.Text;
if (!String.IsNullOrEmpty(text))
{
contents.Add(text);
}
}
}
private void toolStripMenuItem1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlBook;
Excel.Worksheet xlSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Add(misValue);
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
int i = 1;
foreach (ListViewItem item in lvwFiles.Items)
{
xlSheet.Cells[i, 1] = "Image";
xlSheet.Cells[i, 2] = item.Text;
xlSheet.Cells[i, 3] = "'" + item.SubItems[1].Text;
xlSheet.Cells[i, 4] = "'" + item.SubItems[2].Text;
Excel.Shape shape = xlSheet.Shapes.AddPicture(item.Text, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, xlSheet.Cells[i, 1].left, xlSheet.Cells[i, 1].top, xlSheet.Cells[i, 1].width, xlSheet.Cells[i, 1].height);
shape.LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoCTrue;
i++;
}
xlBook.SaveAs(String.Format("{0}\\csharp-Excel.xls", System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlSheet);
releaseObject(xlBook);
releaseObject(xlApp);
}
private Excel.Shape[] Sort(Excel.ShapeRange shapeRange, Func <Excel.Shape, Excel.Shape, float> testFunc)
{
var sorted = new Excel.Shape[shapeRange.Count];
for (int i = 0; i < sorted.Length; i++)
{
sorted[i] = shapeRange.Item(i + 1);
}
// FIXME: GroupByで一発でソートする
bool swapped = true;
while (swapped)
{
swapped = false;
for (int i = 0, length = sorted.Length; i < length - 1; i++)
{
if (testFunc(sorted[i], sorted[i + 1]) > 0)
{
var tmp = sorted[i + 1];
sorted[i + 1] = sorted[i];
sorted[i] = tmp;
swapped = true;
}
}
}
return(sorted);
}
private void InsertImageIntoRange(ImageSource source, Excel.Worksheet currentSheet, Excel.Range range, int maxHeight = -1)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
var encoder = new System.Windows.Media.Imaging.BmpBitmapEncoder();
encoder.Frames.Add(System.Windows.Media.Imaging.BitmapFrame.Create(source as System.Windows.Media.Imaging.BitmapSource));
encoder.Save(ms);
ms.Flush();
System.Windows.Forms.Clipboard.SetDataObject(System.Drawing.Image.FromStream(ms), true);
currentSheet.Paste(range);
// Resize image to fit range
Excel.Shape shape = (Excel.Shape)currentSheet.Shapes.Item(currentSheet.Shapes.Count);
shape.LockAspectRatio = Office.MsoTriState.msoCTrue;
if (maxHeight == -1)
{
shape.Height = (int)range.Height;
}
else
{
shape.Height = (int)((source.Height / (double)maxHeight) * range.Height);
}
shape.Line.Visible = Office.MsoTriState.msoTrue;
ms.Close();
}
private void convertButton_Click(object sender, RibbonControlEventArgs e)
{
Excel.Worksheet currentSheet = Globals.ThisAddIn.GetActiveWorksheet();
// Excel.Workbook workbook = Globals.ThisAddIn.GetActiveWorkbook();
Excel.Shapes shapes = currentSheet.Shapes;
string a = null;
foreach (Excel.Shape item in shapes)
{
a = item.Name;
}
Excel.Shape shape = shapes.Item(a);
SmartArt smart = shape.SmartArt;
SmartArtNodes nodes = smart.AllNodes;
List <TextFrame2> textFrame2s = new List <TextFrame2>();
foreach (SmartArtNode node in nodes)
{
textFrame2s.Add(node.TextFrame2);
}
TextRange2 range;
List <string> strings = new List <string>();
foreach (TextFrame2 textframe2 in textFrame2s)
{
range = textframe2.TextRange;
strings.Add(range.Text);
}
}
/// <summary>
/// Get Selected Shape
/// </summary>
/// <returns>Return Selected Shape</returns>
private MyShape GetShapeSelected()
{
Excel.Shape selectedShape = null;
MyShape myselectedShape = null;
Excel.ShapeRange selectedShapeRange = null;
try
{
selectedShapeRange = this.Application.Selection.ShapeRange;
}
catch
{
if (selecteTypeNameNow != selectedTypeNameLastTime)
{
customTaskPanel.AddMessage("No shape selected");
}
}
if (selectedShapeRange != null)
{
selectedShape = selectedShapeRange.Item(1) as Excel.Shape;
if (selectedShape != null)
{
myselectedShape = new MyShape(selectedShape);
return(myselectedShape);
}
}
return(myselectedShape);
}
protected Excel.Shape AddText(double left, double top, Font font, string text)
{
Excel.Shape box = AddRectangle(new Rect(left, top, 1, 1), Color.Transparent);
box.Line.Visible = GetState(false);
box.TextFrame2.MarginBottom = 0f;
box.TextFrame2.MarginTop = 0f;
box.TextFrame2.MarginLeft = 0f;
box.TextFrame2.MarginRight = 0f;
box.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
box.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
box.TextFrame.Characters().Font.Name = font.Name;
box.TextFrame.Characters().Font.Size = font.Size;
box.TextFrame.Characters().Font.Bold = font.Style == FontStyle.Bold;
box.TextFrame.Characters().Font.Color = Color.Black.ToRgb();
box.TextFrame.Characters().Text = text;
if (!String.IsNullOrEmpty(text))
{
box.TextFrame.AutoSize = true;
}
box.Left = (float)left;
box.Top = (float)top;
return(box);
}
private void button4_Click(object sender, EventArgs e)
{
string _strFileName = @"r:\abcd.xls";
MSOffice.Application App = new MSOffice.Application();
MSOffice.Workbook Book = App.Workbooks.Add(MSOffice.XlWBATemplate.xlWBATWorksheet);
MSOffice.Worksheet Sheet = (MSOffice.Worksheet)Book.ActiveSheet;
MSOffice.Worksheets sheets = (MSOffice.Worksheets)Book.Sheets;
App.Visible = false;
App.DisplayAlerts = false;
object obj = App.Caller[1];
obj = App.Charts;
obj = App.ClipboardFormats;
obj = App.CutCopyMode;
obj = App.StatusBar;
obj = App.Workbooks[1];
obj = Book.ActiveChart;
obj = Sheet.PageSetup.FirstPage.CenterFooter.Picture.Application;
obj = Sheet.Range["", ""].Font.Application;
obj = Sheet.Range["", ""].Borders[MSOffice.XlBordersIndex.xlDiagonalUp].Application;
obj = App.Windows[""].SheetViews[""];
obj = App.Charts;
obj = Sheet.Names;
obj = Sheet.Names.Item(1);
Sheet.Copy();
MSOffice.Areas a = null;
obj = a.Application;
// MSOffice.Hyperlink
App.Workbooks.Open("");
// _xlsApp.Selection
App.Windows[1].Zoom = 150;
//xlsSheet.Rows[1, 1];
MSOffice.Range rng = Sheet.Range[1, 2];
MSOffice.Shape shape = Sheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 0, 48, 72, 60);
shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
shape.Shadow.Obscured = Microsoft.Office.Core.MsoTriState.msoCTrue;
shape.Shadow.Type = Microsoft.Office.Core.MsoShadowType.msoShadow18;
shape.Fill.UserPicture(@"r:\test.jpg");
//xlsSheet.Shapes.AddOLEObject
//shape.GroupItems.Item
//shape.Glow.Color
Sheet.get_Range("");
App.Windows[1].Height = 100;
textBox1.Text = App.Version;
App.Quit();
}
// Update Shape Position and Size
public void Update(Excel.Shape shape)
{
top = shape.Top;
left = shape.Left;
height = shape.Height;
width = shape.Width;
name = shape.Name;
}
private void ReportExcel2()
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wb = app.Workbooks.Add();
Excel.Worksheet ws = wb.Worksheets[1];
ws.Columns[1].ColumnWidth = 40;
ws.Columns[2].ColumnWidth = 20;
ws.Columns["A:B"].WrapText = true;
Excel.Range rng = ws.Range["A1:B1"];
ws.Cells[1, 1].value = "ОТЧЕТ О ПРОДАЖАХ ЗА " +
dateTimePicker1.Value.ToShortDateString() +
" - " + dateTimePicker2.Value.ToShortDateString();
rng.Font.Bold = true;
rng.Font.Size = 14;
rng.MergeCells = true;
ws.Range["A1:B2"].HorizontalAlignment =
Excel.Constants.xlCenter;
ws.Cells[2, 1].value = "Поставщик";
ws.Cells[2, 2].value = "Сумма продаж";
int i = 3;
report2TableAdapter1.Fill(kondirDataSet1.Report2,
dateTimePicker1.Value, dateTimePicker2.Value);
foreach (KondirDataSet.Report2Row r in kondirDataSet1.Report2)
{
ws.Cells[i, 1].value = r.SupplierName;
ws.Cells[i, 2].value = r.Total;
i++;
}
ws.Cells[i, 1].value = "ИТОГО";
ws.Cells[i, 2].FormulaR1C1 = "=SUM(R[-" + (i - 3) + "]C:R[-1]C)";
rng = ws.Range[ws.Cells[2, 1], ws.Cells[i, 2]];
rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle =
Excel.XlLineStyle.xlContinuous;
rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =
Excel.XlLineStyle.xlContinuous;
Excel.Shape sh =
ws.Shapes.AddChart(Excel.XlChartType.xlPie);
sh.Left = 0;
sh.Top = (float)ws.Rows[i + 2].Top;
float scale = (float)ws.Columns[3].Left / sh.Width;
sh.ScaleWidth(scale, MsoTriState.msoFalse,
MsoScaleFrom.msoScaleFromTopLeft);
Excel.Chart ch = sh.Chart;
ch.SetSourceData(ws.Range[ws.Cells[2, 1], ws.Cells[i - 1,
2]]);
ch.SeriesCollection(1).ApplyDataLabels();
}
// イベントハンドラー
protected override void Draw(double beginX, double beginY, double endX, double endY)
{
this.shape?.Delete();
var shapes = this.Field.Cell.Worksheet.Shapes;
this.shape = shapes.AddLine((float)beginX, (float)beginY, (float)endX, (float)endY);
this.shape.Line.ForeColor.SetRgb(this.Parent.Parent.Color);
this.shape.Line.Transparency = (float)this.Parent.Transparency;
}
// Initialize Shapes List
private void InitializeListOfShapes(Excel.Shapes shapes)
{
myShapes = new List <MyShape>();
for (int i = 1; i <= shapes.Count; i++)
{
Excel.Shape shape = shapes.Item(i);
MyShape myShape = new MyShape(shape);
myShapes.Add(myShape);
}
}
protected void SetShapeLine(Excel.Shape shape, LineOptions options)
{
shape.Line.Visible = GetState(false);
if (options.Visible)
{
shape.Line.Visible = GetState(options.Visible);
shape.Line.Weight = (float)options.Weight;
shape.Line.ForeColor.RGB = options.Color.ToRgb();
}
}
// 実行方法
// コマンドライン実行
// 画像とメモが保存されているディレクトリを渡すと、エクセルにまとめます。
// C:\Users\yu-kimura\MemoToExcel.exe C:\Users\yu-kimura\Documents\xxx
static void Main(string[] args)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//エクセルを非表示
ExcelApp.Visible = false;
//エクセルファイルのオープン
Microsoft.Office.Interop.Excel.Workbook WorkBook = ExcelApp.Workbooks.Add();
//1シート目の選択
Microsoft.Office.Interop.Excel.Worksheet sheet = WorkBook.Worksheets[1];
string[] pngFiles = Directory.GetFiles(args[0], "*.png");
int i = 0;
//string[,] data = new string[pngFiles.Length, 1];
int imageMaxW = 640, imageMaxH = 480, shapeMaxW = 320, interval = imageMaxH + 10, imageX = shapeMaxW + 20;
foreach (string png in pngFiles)
{
Bitmap bmpSrc = new Bitmap(png);
//元画像の縦横サイズを調べる
int width = bmpSrc.Width;
int height = bmpSrc.Height;
if (width < imageMaxW || height < imageMaxH)
{
sheet.Shapes.AddPicture(png, MsoTriState.msoFalse, MsoTriState.msoTrue, imageX, interval * i, width, height);
}
else
{
sheet.Shapes.AddPicture(png, MsoTriState.msoFalse, MsoTriState.msoTrue, imageX, interval * i, imageMaxW, imageMaxH);
}
Microsoft.Office.Interop.Excel.Shape s = sheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 0, interval * i, shapeMaxW, imageMaxH);
string memo = File.ReadAllText(png + ".txt", Encoding.Unicode);
s.TextFrame.Characters(0, 0).Text = memo;
if (memo.StartsWith("*"))
{
s.TextFrame.Characters(0, memo.Length).Font.Color = Color.Red;
}
i++;
//data[i++, 0] = File.ReadAllText(png + ".txt", Encoding.Unicode);
}
//Microsoft.Office.Interop.Excel.Range range = sheet.Range[sheet.Cells[1,1], sheet.Cells[i,1]];
// range.Value2 = data;
//workbookを閉じる
WorkBook.SaveAs(args[0] + ".xlsx");
WorkBook.Close();
//エクセルを閉じる
ExcelApp.Quit();
}
public void test(object[,] setValue)
{
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add();
xlSheets = xlBook.Worksheets;
xlSheet = xlSheets[1];
xlApp.Visible = true;
int iColCnt = 0;
int iRowCnt = 0;
Range xlCellsFrom = null;
Range xlRangeFrom = null;
Range xlCellsTo = null;
Range xlRangeTo = null;
Range xlTarget = null;
//配列の要素数取得
iRowCnt = setValue.GetLength(0) - 1;
iColCnt = setValue.GetLength(1) - 1;
// 貼り付け位置
int col = 4;
int row = 5;
xlCellsFrom = xlSheet.Cells;
xlRangeFrom = xlCellsFrom[row, col];
xlCellsTo = xlSheet.Cells;
xlRangeTo = xlCellsTo[row + iRowCnt, col + iColCnt];
xlTarget = xlSheet.Range[xlRangeFrom, xlRangeTo];
xlTarget.Value = setValue;
// 画像貼付
string imagefile = @"TestPath";
double Left = xlRangeFrom.Left;
double Top = xlRangeFrom.Top;
float Width = 0;
float Height = 0;
Microsoft.Office.Interop.Excel.Shape shape = xlSheet.Shapes.AddPicture(imagefile, MsoTriState.msoTrue, MsoTriState.msoTrue, xlRangeTo.Left, xlRangeTo.Top, Width, Height);
shape.ScaleHeight(0.5F, Microsoft.Office.Core.MsoTriState.msoCTrue);
shape.ScaleWidth(0.5F, Microsoft.Office.Core.MsoTriState.msoCTrue);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
this.ReleaseExcelComObject(EnumReleaseMode.App);
}
}
//Добавляет примечание comment в фигуру
private void AddShapeLink(string comment, Transaction tlist, Shape shape)
{
var t = new TransactionShape(shape)
{
NewLink = comment
};
shape.Title = comment;
tlist.AddShape(t);
GetCurrentLink(comment, shape.Name);
}
protected Excel.Shape AddShape(MsoAutoShapeType shapeType, Rect rect, Color color)
{
Excel.Shape shape = Chart.Shapes.AddShape(shapeType,
(float)rect.Left, (float)rect.Top,
(float)rect.Width, (float)rect.Height);
shape.Fill.ForeColor.RGB = color.ToRgb();
shape.Fill.Transparency = color.GetAlpha();
Shapes.Add(shape);
return(shape);
}
private void DrawGradientLegendText(ParametersBase Parameters, double position, double text)
{
Excel.Shape legend = null;
switch (Parameters.LegendPosition)
{
case Position.Left:
case Position.Right:
legend = AddText(LegendArea.Left + LegendArea.Width + SmallMargin, LegendArea.Top + position * LegendArea.Height,
DefaultFont, Parameters.LegendTextFormater.Format(text));
break;
case Position.Top:
case Position.Bottom:
legend = AddText(LegendArea.Left + position * LegendArea.Width, LegendArea.Top + LegendArea.Height + SmallMargin,
DefaultFont, Parameters.LegendTextFormater.Format(text));
break;
}
switch (Parameters.LegendPosition)
{
case Position.Left:
case Position.Right:
if (position == 0)
{
legend.Top = (float)LegendArea.Top;
}
else if (position == 1)
{
legend.Top = (float)(LegendArea.Top + LegendArea.Height) - legend.Height;
}
else
{
legend.Top = (float)(LegendArea.Top + position * LegendArea.Height) - legend.Height / 2;
}
break;
case Position.Top:
case Position.Bottom:
if (position == 0)
{
legend.Left = (float)LegendArea.Left;
}
else if (position == 1)
{
legend.Left = (float)(LegendArea.Left + LegendArea.Width) - legend.Width;
}
else
{
legend.Left = (float)(LegendArea.Left + position * LegendArea.Width) - legend.Width / 2;
}
break;
}
}
/// <summary>
/// Chn 插入图片
/// </summary>
/// <param name="workSheet"></param>
/// <param name="picturePath"></param>
/// <param name="picLeft"></param>
/// <param name="picTop"></param>
/// <returns></returns>
public static Excel.Shape InsertPictureByShape(this Excel.Worksheet workSheet, string picturePath, float picLeft, float picTop)
{
Size picSize = ImageHelper.GetDimensions(picturePath);
int picWidth = picSize.Width;
int picHeight = picSize.Height;
Excel.Shape pic = workSheet.Shapes.AddPicture(picturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue,
picLeft, picTop, picWidth, picHeight);
return(pic);
}
public ShapePosition(ExcelInterop.Shape shape)
{
var topLeftCell = shape.TopLeftCell;
var bottomRightCell = shape.BottomRightCell;
TopLeftRow = topLeftCell.Row;
TopLeftColumn = topLeftCell.Column;
BottomRightRow = bottomRightCell.Row;
BottomRightColumn = bottomRightCell.Column;
Marshal.ReleaseComObject(topLeftCell);
Marshal.ReleaseComObject(bottomRightCell);
}
protected void DrawTitle(ParametersBase parameters)
{
if (!parameters.ShowTitle)
{
return;
}
Excel.Shape shape = AddRectangle(TitleArea, Color.Transparent);
shape.Line.Visible = GetState(false);
shape.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
shape.TextFrame.Characters().Text = parameters.Title;
shape.TextFrame.Characters().Font.Size = 20;
shape.TextFrame.Characters().Font.Color = Color.Black.ToRgb();
}
public void CreateCloudHatch()
{
Excel.Shape cloudPart = null;
Excel.Shape hatchArea = null;
Excel.ShapeRange shapeRange = null;
try
{
if (ErrorHandler.IsEnabled(true) == false)
{
return;
}
ErrorHandler.CreateLogRecord();
string shapeName = AssemblyInfo.Title.ToLower();
double x = Globals.ThisAddIn.Application.Selection.Left;
double y = Globals.ThisAddIn.Application.Selection.Top;
double h = Globals.ThisAddIn.Application.Selection.Height;
double w = Globals.ThisAddIn.Application.Selection.Width;
cloudPart = CreateCloudPart("ALL");
hatchArea = CreateHatchArea(x, y, h, w);
if (cloudPart != null && hatchArea != null)
{
object[] shapes = { cloudPart.Name, hatchArea.Name };
shapeRange = Globals.ThisAddIn.Application.ActiveSheet.Shapes.Range(shapes); //.Group();
shapeRange.Group();
shapeRange.Name = shapeName + new string(' ', 1) + DateTime.Now.ToString(Properties.Settings.Default.Markup_ShapeDateFormat);
Properties.Settings.Default.Markup_LastShapeName = shapeRange.Name;
Marshal.FinalReleaseComObject(cloudPart);
Marshal.FinalReleaseComObject(hatchArea);
}
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
finally
{
if (cloudPart != null)
{
Marshal.ReleaseComObject(cloudPart);
}
if (hatchArea != null)
{
Marshal.ReleaseComObject(hatchArea);
}
if (shapeRange != null)
{
Marshal.ReleaseComObject(shapeRange);
}
}
}
/// <summary>
/// Wenn der Shape ein Shape mit Barcode ist, wird die verbundene Adresse des Shape zurück gegeben.
/// </summary>
/// <param name="shape"></param>
/// <returns></returns>
internal static string GetAddress(Excel.Shape shape)
{
//Prüfe den Shape
if (CheckShapeName(shape))
{
//Adresse ermitteln, also z.B. Barcode($A$1), dann wird alles zwischen den Klammern zurückgegeben
var addressCurShape = shape.Name.Split('(')?[1].Split(')')?[0];
//Adresse zurückgeben
if (addressCurShape != null)
{
return(addressCurShape);
}
}
return(null);
}
/// <summary>
/// 获取图片的原始宽度和高度
/// </summary>
/// <param name="shtPic">工作表</param>
/// <param name="rngPic">单元格</param>
/// <param name="picPath">图片地址</param>
public void GetPicWidthHeight(Excel.Worksheet shtPic, Excel.Range rngPic, string picPath)
{
sh = shtPic.Shapes.AddPicture(
picPath,
Office.MsoTriState.msoFalse,
Office.MsoTriState.msoCTrue,
rngPic.Left,
rngPic.Top,
-1,
-1
);
this.GetPicHeight = sh.Height;
this.GetPicWidth = sh.Width;
sh.Delete();
}
public Excel.Shape CreateCloudLine(double x1, double y1, double x2, double y2)
{
Excel.Shape cloudArc = null;
Excel.Shape cloudLine = null;
Excel.ShapeRange shapeRange = null;
try
{
string shapeName = AssemblyInfo.Title.ToLower();
double length = Properties.Settings.Default.Markup_ShapeLineSpacing;
int i = 0;
double x = 0;
double y = 0;
double dx = x2 - x1;
double dy = y2 - y1;
double d = Math.Sqrt(dx * dx + dy * dy);
double segments = Math.Ceiling(d / length);
if (segments < 2)
{
segments = 2;
}
double deltax = (dx / segments);
double deltay = (dy / segments);
double xp = x1;
double yp = y1;
object[] shapes = new object[Convert.ToInt32(segments)];
for (i = 1; i <= Convert.ToInt32(segments); i++)
{
x = xp + deltax;
y = yp + deltay;
cloudArc = CreateArc(xp, yp, x, y, length);
shapes[i - 1] = cloudArc.Name;
xp = x;
yp = y;
}
shapeRange = Globals.ThisAddIn.Application.ActiveSheet.Shapes.Range(shapes); //.Group();
shapeRange.Group();
shapeRange.Name = shapeName + new string(' ', 1) + DateTime.Now.ToString(Properties.Settings.Default.Markup_ShapeDateFormat) + LineNbr.ToString();
LineNbr += 1;
cloudLine = Globals.ThisAddIn.Application.ActiveSheet.Shapes(shapeRange.Name);
Properties.Settings.Default.Markup_LastShapeName = shapeRange.Name;
return(cloudLine);
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex, true);
return(null);
}
}
private void DrawColorGradientLegend(ParametersBase Parameters)
{
Excel.Shape shape = AddRectangle(LegendArea, Color.White);
SetShapeLine(shape, Parameters.LegendBorder);
ColorGradient gradient = Parameters.Color as ColorGradient;
Excel.FillFormat fill = shape.Fill;
float startPosition = 1;
float midPosition = 0;
float endPosition = 0;
switch (Parameters.LegendPosition)
{
case Position.Left:
case Position.Right:
fill.TwoColorGradient(MsoGradientStyle.msoGradientHorizontal, 1);
break;
case Position.Top:
case Position.Bottom:
fill.TwoColorGradient(MsoGradientStyle.msoGradientVertical, 1);
startPosition = 0;
endPosition = 1;
break;
}
fill.GradientStops[1].Position = startPosition;
fill.GradientStops[1].Color.RGB = gradient.Stops.First().Color.ToRgb();
fill.GradientStops[2].Position = endPosition;
fill.GradientStops[2].Color.RGB = gradient.Stops.Last().Color.ToRgb();
DrawGradientLegendText(Parameters, startPosition, gradient.Stops.First().Value);
DrawGradientLegendText(Parameters, endPosition, gradient.Stops.Last().Value);
if (gradient.Stops.Count == 3)
{
midPosition = (float)((gradient.Stops[1].Value - gradient.Stops.Last().Value) / (gradient.Stops.First().Value - gradient.Stops.Last().Value));
if (Parameters.LegendPosition == Position.Bottom || Parameters.LegendPosition == Position.Top)
{
midPosition = 1 - midPosition;
}
fill.GradientStops.Insert(gradient.Stops[1].Color.ToRgb(), midPosition, Index: 2);
DrawGradientLegendText(Parameters, midPosition, gradient.Stops[1].Value);
}
}
/// <summary>
/// Prüft den Shape auf null und den Namen.
/// </summary>
/// <param name="shape"></param>
/// <returns></returns>
internal static bool CheckShapeName(Excel.Shape shape)
{
//Shape auf null prüfen
if (shape != null)
{
//Name des Shapes
var name = shape.Name;
//Wenn der Shapename Barcode enthält und nicht null ist
if (name?.IndexOf("barcode", StringComparison.OrdinalIgnoreCase) != -1)
{
//Gib true zurück
return(true);
}
}
//Sonst false
return(false);
}
//Add some WordArt objecs to the Excel worksheet
private void AddAutoShapesToExcel()
{
//Method fields
float txtSize = 80;
float Left = 100.0F;
float Top = 100.0F;
//Have 2 objects
int[] numShapes = new int[2];
Microsoft.Office.Interop.Excel.Shape[] myShapes = new Microsoft.Office.Interop.Excel.Shape[numShapes.Length];
try
{
//loop through the object count
for (int i = 0; i < numShapes.Length; i++)
{
//Add the object to Excel
myShapes[i] = worksheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect1, "DRAFT", "Arial Black",
txtSize, MsoTriState.msoFalse, MsoTriState.msoFalse, (Left * (i * 3)), Top);
//Manipulate the object settings
myShapes[i].Rotation = 45F;
myShapes[i].Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
myShapes[i].Fill.Transparency = 0F;
myShapes[i].Line.Weight = 1.75F;
myShapes[i].Line.DashStyle = MsoLineDashStyle.msoLineSolid;
myShapes[i].Line.Transparency = 0F;
myShapes[i].Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
myShapes[i].Line.ForeColor.RGB = (0 << 16) | (0 << 8) | 0;
myShapes[i].Line.BackColor.RGB = (255 << 16) | (255 << 8) | 255;
}
}
catch (Exception ex)
{
}
}
