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();
}
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);
}
}
/// <summary>
/// Add a picture file named <paramref name="pictureFileName"/> on <paramref name="cell"/> and name <paramref name="name"/>.
/// </summary>
/// <param name="cell">The cell to add a picture.</param>
/// <param name="pictureFileName">File name of the picture.</param>
/// <param name="name">Excel name of the picture.</param>
public static void AddPicture(Excel.Range cell, string pictureFileName, string name)
{
cell.Activate();
dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Shapes shapes = sheet.Shapes;
Excel.Shape shape = shapes.AddPicture(
pictureFileName,
Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue,
(float)cell.Left, (float)cell.Top, (float)cell.Width, (float)cell.Height);
shape.Placement = Excel.XlPlacement.xlMove;
shape.Name = name;
// if you want to fit pictures with each cell, delte the following lines.
shape.ScaleHeight(1, Office.MsoTriState.msoTrue);
shape.ScaleWidth(1, Office.MsoTriState.msoTrue);
var xyRatioCell = (float)cell.Width / (float)cell.Height;
var xyRatioShape = shape.Width / shape.Height;
float scale = 1;
if (xyRatioCell > xyRatioShape)
{
// fit to cell.Height
scale = (float)cell.Height / shape.Height;
}
else
{
// fit to cell.Width;
scale = (float)cell.Width / shape.Width;
}
shape.ScaleHeight(scale, Office.MsoTriState.msoTrue);
shape.ScaleWidth(scale, Office.MsoTriState.msoTrue);
shape.LockAspectRatio = Office.MsoTriState.msoTrue;
}
public void insertDocument(string relativePath, string nodeRef)
{
object missingValue = Type.Missing;
object trueValue = true;
object falseValue = false;
try
{
// Suppress all app events during this process
m_SuppressAppEvents = true;
// Create a new document if no document currently open
if (m_ExcelApplication.ActiveWorkbook == null)
{
m_ExcelApplication.Workbooks.Add(missingValue);
}
// WebDAV or CIFS?
string strFullPath = m_ServerDetails.getFullPath(relativePath, m_ExcelApplication.ActiveWorkbook.FullName, true);
string strExtn = Path.GetExtension(relativePath).ToLower();
string fileName = Path.GetFileName(relativePath);
string nativeExtn = ".xls" + (m_SupportsXlsx ? "x" : "");
// If we're using WebDAV, then download file locally before inserting
if (strFullPath.StartsWith("http"))
{
// Need to unescape the the original path to get the filename
fileName = Path.GetFileName(Uri.UnescapeDataString(relativePath));
string strTempFile = Path.GetTempPath() + fileName;
if (File.Exists(strTempFile))
{
try
{
File.Delete(strTempFile);
}
catch (Exception)
{
strTempFile = Path.GetTempFileName();
}
}
WebClient fileReader = new WebClient();
string url = m_ServerDetails.WebClientURL + "download/direct/" + nodeRef.Replace(":/", "") + "/" + Path.GetFileName(relativePath);
fileReader.Headers.Add("Cookie: " + webBrowser.Document.Cookie);
fileReader.DownloadFile(url, strTempFile);
strFullPath = strTempFile;
}
Excel.Worksheet worksheet = (Excel.Worksheet)m_ExcelApplication.ActiveSheet;
Excel.Shapes shapes = worksheet.Shapes;
Excel.Range range = (Excel.Range)m_ExcelApplication.Selection;
object top = range.Top;
object left = range.Left;
if (".bmp .gif .jpg .jpeg .png".IndexOf(strExtn) != -1)
{
Excel.Shape picture = shapes.AddPicture(strFullPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,
1, 2, 3, 4);
picture.Top = Convert.ToSingle(range.Top);
picture.Left = Convert.ToSingle(range.Left);
picture.ScaleWidth(1, Microsoft.Office.Core.MsoTriState.msoTrue, missingValue);
picture.ScaleHeight(1, Microsoft.Office.Core.MsoTriState.msoTrue, missingValue);
}
else if (nativeExtn.IndexOf(strExtn) != -1)
{
// Workaround for KB210684 if clean, new workbook currently open
if (!docHasExtension() && m_ExcelApplication.ActiveWorkbook.Saved)
{
string workbookName = m_ExcelApplication.ActiveWorkbook.Name;
string templateName = Path.GetDirectoryName(strFullPath) + "\\" + workbookName + "." + Path.GetFileNameWithoutExtension(strFullPath);
m_ExcelApplication.ActiveWorkbook.Close(falseValue, missingValue, missingValue);
File.Move(strFullPath, templateName);
m_ExcelApplication.Workbooks.Add(templateName);
}
else
{
// Load the workbook
object neverUpdateLinks = 2;
object readOnly = true;
Excel.Workbook insertXls = m_ExcelApplication.Workbooks.Open(strFullPath, neverUpdateLinks, readOnly, missingValue, missingValue,
missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue,
missingValue, missingValue);
if (insertXls != null)
{
try
{
// Loop backwards through the worksheets copy-pasting them after the active sone
Excel.Worksheet sourceSheet;
for (int i = insertXls.Worksheets.Count; i > 0; i--)
{
sourceSheet = (Excel.Worksheet)insertXls.Worksheets[i];
sourceSheet.Copy(missingValue, worksheet);
}
}
catch (Exception e)
{
MessageBox.Show(Properties.Resources.UnableToInsert + ": " + e.Message, Properties.Resources.MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
insertXls.Close(falseValue, missingValue, missingValue);
}
}
}
}
else
{
object iconFilename = Type.Missing;
object iconIndex = Type.Missing;
object iconLabel = fileName;
string defaultIcon = Util.DefaultIcon(strExtn);
if (defaultIcon.Contains(","))
{
string[] iconData = defaultIcon.Split(new char[] { ',' });
iconFilename = iconData[0];
iconIndex = iconData[1];
}
object filename = strFullPath;
object size = 32;
Excel.Shape shape = shapes.AddOLEObject(missingValue, filename, falseValue, trueValue,
iconFilename, iconIndex, iconLabel, left, top, size, size);
}
}
catch (Exception e)
{
MessageBox.Show(Properties.Resources.UnableToInsert + ": " + e.Message, Properties.Resources.MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Restore app event processing
m_SuppressAppEvents = false;
}
}
/// <summary>
/// InsertPicture
/// </summary>
/// <param name="fileName"></param>
public static void InsertPicture(
string fileName,
float dx,
float dy,
float width,
float height,
float scale = -1)
{
if (LogCalls)
{
DebugLog.Message("ExcelOp InsertPicture " + fileName);
}
float left = (float)((double)XlApp.ActiveCell.Left) + dx;
float top = (float)((double)XlApp.ActiveCell.Top) + dy;
float origWidth = width;
if (width <= 0)
{
width = (float)((double)XlApp.ActiveCell.Width);
}
float origHeight = height;
if (height <= 0)
{
height = (float)((double)XlApp.ActiveCell.Height);
}
bool scaleShapeSize = false;
if (scale > 0)
{
left *= scale;
top *= scale;
width *= scale;
height *= scale;
float rowHeight = height;
if (scale > 1 && AdjustHighDpiRowHeight) // fudge to adjust row height better for relatively tall images
{
if (height < width)
{
float fFactor = ((width - height) / width); // varies 1 for no height to 0 for height == width
float scale2 = 1 + ((scale - 1) * fFactor);
rowHeight = origHeight * scale2;
}
else
{
rowHeight = origHeight;
}
}
RowHeight(rowHeight); // be sure row is at least this height
//scaleShapeSize = true; // don't turn on shape scaling, doesn't seem to help
}
else
{
scale = 1.0f;
}
MsoTriState linkToFile = MsoTriState.msoFalse;
MsoTriState saveToDocument = MsoTriState.msoTrue;
XlShape = XlSheet.Shapes.AddPicture(fileName, linkToFile, saveToDocument, left, top, width, height);
if (scaleShapeSize) // adjust scaling of the shape just created
{
MsoTriState scaleRelativeToOriginalSize = MsoTriState.msoTrue;
MsoScaleFrom scaleFrom = MsoScaleFrom.msoScaleFromTopLeft;
XlShape.LockAspectRatio = MsoTriState.msoTrue;
XlShape.ScaleWidth(scale, scaleRelativeToOriginalSize, scaleFrom);
//XlShape.ScaleHeight(scale, scaleRelativeToOriginalSize, scaleFrom);
}
ReleaseObject(XlShape);
}
private void CreateEvidence(TestContextImpl context, TestStep step)
{
ITakesScreenshot takesScreenshot = context.Driver as ITakesScreenshot;
if (null == takesScreenshot)
{
return;
}
bool isAlertPresent = false;
try
{
IAlert alert = context.Driver.SwitchTo().Alert();
isAlertPresent = true;
}
catch (OpenQA.Selenium.NoAlertPresentException)
{
isAlertPresent = false;
}
if (isAlertPresent)
{
return;
}
Excel.Workbook workbook = context.WorkbookContext.Workbook;
Excel.Worksheet evidenceWorksheet = context.WorkbookContext.AddEvidence();
Excel.Range range = ListRowHelper.Set(step.ListRow, ListRowHelper.ColumnIndex.Evidence, evidenceWorksheet.Name);
ExcelHelper.SetText(evidenceWorksheet, 1, 1, Properties.Resources.Evidence_Scenario, false).ColumnWidth = 20;
ExcelHelper.AddHyperLink(
step.Worksheet,
range,
evidenceWorksheet,
evidenceWorksheet.Cells[1, 1]);
ExcelHelper.AddHyperLink(
evidenceWorksheet,
evidenceWorksheet.Cells[1, 2],
step.Worksheet,
range);
ExcelHelper.SetText(evidenceWorksheet, 2, 1, Properties.Resources.Evidence_Command, false);
ExcelHelper.SetText(evidenceWorksheet, 2, 2, step.ToString(), false);
ExcelHelper.SetText(evidenceWorksheet, 3, 1, Properties.Resources.Evidence_ErrorMessage, false);
ExcelHelper.SetText(evidenceWorksheet, 3, 2, step.ErrorMessage, false);
ExcelHelper.SetText(evidenceWorksheet, 4, 1, Properties.Resources.Evidence_Browser, false);
ExcelHelper.SetText(evidenceWorksheet, 4, 2, App.Context.Settings.WebDriverType.ToString(), false);
ExcelHelper.SetText(evidenceWorksheet, 5, 1, Properties.Resources.Evidence_Title, false);
ExcelHelper.SetText(evidenceWorksheet, 5, 2, context.Driver.Title, false);
ExcelHelper.SetText(evidenceWorksheet, 6, 1, Properties.Resources.Evidence_Url, false);
ExcelHelper.SetText(evidenceWorksheet, 6, 2, context.Driver.Url, false);
ExcelHelper.SetText(evidenceWorksheet, 7, 1, Properties.Resources.Evidence_Time, false);
ExcelHelper.SetText(evidenceWorksheet, 7, 2, DateTime.Now.ToString(), false);
string path = Path.Combine(App.TempDir, Guid.NewGuid().ToString()) + ".jpg";
try
{
Screenshot screenshot = takesScreenshot.GetScreenshot();
screenshot.SaveAsFile(path, ImageFormat.Jpeg);
Excel.Shape shape = evidenceWorksheet.Shapes.AddPicture(
path,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue,
0,
150,
0,
0);
shape.ScaleWidth(1, Microsoft.Office.Core.MsoTriState.msoTrue);
shape.ScaleHeight(1, Microsoft.Office.Core.MsoTriState.msoTrue);
}
finally
{
File.Delete(path);
}
}
