private static void FormatDataArea(Excel.Range range, String data)
{
range.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Type.Missing,
Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name = data;
range.Select();
range.Worksheet.ListObjects[data].TableStyle = "TableStyleMedium2";
range.EntireColumn.AutoFit();
}
/// <summary>
/// 在Excel格式的LOT卡表中插入单体图图片并设置页面属性
/// </summary>
/// <param name="xlSheet">Excel格式的LOT卡表</param>
/// <param name="imgName">图片全路径</param>
/// <param name="strError">返回的错误</param>
/// <returns></returns>
internal static bool InsertDanTiTuImageToExcelSheet(
Excel.Worksheet xlSheet,
string imgName,
out string strError
)
{
//默认没有错误
strError = string.Empty;
try
{
//取得最大列数
int maxColNum = 15;
//检测最大行
int maxRowNum = xlSheet.Range["A65536"].End[Excel.XlDirection.xlUp].Row;
for (int i = 2; i < 16; i++)
{
int maxRowNum2 = ((Excel.Range)xlSheet.Cells[65536, i]).End[Excel.XlDirection.xlUp].Row;
if (maxRowNum < maxRowNum2) { maxRowNum = maxRowNum2; }
}
//删除多余的行
((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp);
//删除多余的行
((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp);
//图片是否存在
if (!File.Exists(imgName))
{
throw new Exception(string.Format("未找到生成的单体图图片{0}!", imgName));
}
//插入图片
xlSheet.Range["A" + (maxRowNum + 1)].Select();
var pic = ((Excel.Pictures)((Excel._Worksheet)xlSheet).Pictures()).Insert(imgName);
//取得列A-O的总宽度
double dTotalColWidth = 0d;
for (int i = 1; i < maxColNum + 1; i++)
{
dTotalColWidth += Convert.ToDouble(((Excel.Range)xlSheet.Cells[1, i]).ColumnWidth);
}
//取得全部行的总高度
double dTotalRowHeight = 0d;
for (int i = 1; i < maxRowNum + 1; i++)
{
dTotalRowHeight += Convert.ToDouble(((Excel.Range)xlSheet.Cells[i, 1]).RowHeight);
}
//宽度缩放比例
int iZoomWidth = Convert.ToInt32(Math.Floor(89d / dTotalColWidth * 100));
//高度缩放比例
int iZoomHeight = Convert.ToInt32(Math.Floor(790d / dTotalRowHeight * 100));
//取小的缩放比例
int iZoom = Math.Min(iZoomWidth, iZoomHeight);
//检测是否高度未使用完则找到第一页最后一行
if (iZoom < iZoomHeight)
{
//待插入的行数
int iCountRow = Convert.ToInt32(Math.Floor(790d - dTotalRowHeight * iZoom / 100) / 10) + 1;
//检测行数是否大于0
if (iCountRow > 0)
{
//需要下移的连续行字符串
string strRowNums = string.Format("{0}:{1}", maxRowNum + 1, maxRowNum + iCountRow - 1);
//插入多行
((Excel.Range)xlSheet.Rows[strRowNums]).Insert(Shift: Excel.XlDirection.xlDown);
//每行的行高
double dRowHeight = 10d / (iZoom / 100d);
//设置插入行行高
((Excel.Range)xlSheet.Rows[strRowNums]).RowHeight = dRowHeight;
//第一页行高
dTotalRowHeight += dRowHeight;
//最大行号
maxRowNum += iCountRow;
}
}
//原图片长宽
double dPicHeight = pic.Height;
double dPicWidth = pic.Width;
//按第一页总高度度得到新的图片长宽
double dPicHeightNew = dTotalRowHeight;
double dPicWidthNew = dPicWidth / dPicHeight * dPicHeightNew;
//宽度大于第一页宽度按照第一页总宽度得到图片长宽
if (dPicWidthNew > dTotalColWidth)
{
dPicWidthNew = dTotalColWidth;
dPicHeightNew = dPicHeight / dPicWidth * dPicWidthNew;
}
//图片按比例设置新的长宽
pic.Height = dPicHeightNew * 6.3d;
pic.Width = dPicWidthNew * 6.3d;
//设置打印区域
xlSheet.PageSetup.PrintArea = "$A:$O";
//设置打印比例
xlSheet.PageSetup.Zoom = iZoom;
//设置页边距
xlSheet.PageSetup.LeftMargin = xlSheet.Application.InchesToPoints(0.25d);
xlSheet.PageSetup.RightMargin = xlSheet.Application.InchesToPoints(0.19d);
xlSheet.PageSetup.TopMargin = xlSheet.Application.InchesToPoints(0.19d);
xlSheet.PageSetup.BottomMargin = xlSheet.Application.InchesToPoints(0.19d);
//总行数
maxRowNum = xlSheet.Range["A65536"].End[Excel.XlDirection.xlUp].Row; ;
for (int i = 2; i < 16; i++)
{
int maxRowNum2 = ((Excel.Range)xlSheet.Cells[65536, i]).End[Excel.XlDirection.xlUp].Row;
if (maxRowNum < maxRowNum2) { maxRowNum = maxRowNum2; }
}
xlSheet.Select();
xlSheet.PageSetup.PrintArea = "$A:$O";
xlSheet.PageSetup.FitToPagesWide = 1;
xlSheet.PageSetup.FitToPagesTall = 100;
xlSheet.ResetAllPageBreaks();
xlSheet.Application.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
xlSheet.HPageBreaks[1].Location = xlSheet.Range["A" + (maxRowNum + 1)];
xlSheet.PageSetup.PrintTitleRows = "";
xlSheet.PageSetup.PrintTitleColumns = "";
//直接返回成功
return true;
}
catch (Exception ex)
{
strError = ex.Message;
//直接返回失败
return false;
}
}
private void PopulateStatColumn(int column, Excel.Range start, Excel.Range end)
{
try
{
// 确定需要用数据填充的范围。
Excel.Range twoLines = start.get_Resize(2, 1);
twoLines.Merge(System.Type.Missing);
Excel.Range fillRange = this.worksheet.get_Range(start, end);
end.Select();
switch (column)
{
case (int)StatHeadings.DailySales:
// 填充日销售额列。
// 获取包含标准偏差和
// 平均值的单元格的地址。
Excel.Range average = start.Previous;
string averageAddress = average.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
Excel.Range standardDev = average.get_Offset(1, 0);
string standardDevAddress = standardDev.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
// 设置该列的公式。
start.Formula = "=" + averageAddress + "+ (2*" + standardDevAddress + ")";
// 格式“0.00”- 两个小数位。
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.Required:
// 填充所需的列。
// 确定包含预期销售额的
// 单元格的地址。
Excel.Range expectedSales = start.Previous;
string salesAddress = expectedSales.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
// 确定交货前需要多少
// 库存。
// 确定交货前的天数。
int waitDays = this.GetDaysToDelivery();
start.Formula = "=" + waitDays + "*" + salesAddress;
// 格式“0.00”- 两个小数位。
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.CurrentInventory:
// 填充当前库存列。
// 从日记中获取上一天的范围。
int count = (end.Row - start.Row + 1) / 2;
Excel.Range currentCell = start;
for (int row = 0; row < count; row += 1)
{
Excel.Range flavorCell = currentCell.get_Offset(0, 0 - 5);
string flavor = ExcelHelpers.GetValueAsString(flavorCell);
int inventory = Globals.DataSet.Sales.FindByDateFlavor(Globals.DataSet.MaxDate, flavor).Inventory;
currentCell.Value2 = inventory;
if (row != 0)
{
Excel.Range twoCells = currentCell.get_Resize(2, 1);
twoCells.Merge(System.Type.Missing);
currentCell = twoCells;
}
currentCell = currentCell.get_Offset(1, 0);
}
break;
case (int)StatHeadings.ProjectInventory:
// 获取预计销售额和
// 当前库存的地址。
Excel.Range currentInventory = start.Previous;
Excel.Range required = currentInventory.Previous;
string currentInventoryAddress = currentInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
string requiredAddress = required.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
// 确定交货日期的
// 预计库存。
start.Formula = "=MAX(0," + currentInventoryAddress + "-" + requiredAddress + ")";
// 格式“0.00”- 两个小数位。
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.OrderQuanity:
// 确定预计库存和
// 所需库存量的地址。
Excel.Range projectedInventory = start.Previous;
Excel.Range needed = projectedInventory.Previous.Previous;
string projectedInventoryAddress = projectedInventory.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
string neededAddress = needed.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
// 确定每一项所需的订货量。
start.Formula = "=" + neededAddress + "-" + projectedInventoryAddress;
// 格式“0.00”- 两个小数位。
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
default:
break;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
throw;
}
}
public void Execute(AnalyticsService service, XML.Objects.Organisation.View view, Excel.Range currentCell)
{
String metrics = MetricsToString();
Request = service.Data.Ga.Get(view.Tag, Start.ToString("yyyy-MM-dd"), End.ToString("yyyy-MM-dd"), metrics);
SetDimensions(Request);
SetSort(Request);
SetFilters(Request);
SetSegment(Request);
Request.MaxResults = 10000;
Request.PrettyPrint = true;
//Send the query
GaData result = null;
try
{
result = Request.Execute();
currentCell = Stubs.DisplayGaData(result, currentCell);
currentCell.Select();
}
catch (Google.GoogleApiException exception)
{
MessageBox.Show(exception.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void SelectColumnsInRange(Excel.Worksheet sheet, string columns)
{
if (ValidateColumnInput(sheet, columns, false))
{
// Make sure the provided sheet is selected
sheet.Select();
string[] columnArray = columns.ToUpper().Split(',');
StringBuilder columnRangeString = new StringBuilder();
foreach (string column in columnArray)
{
// A blank column will only occur if extra commas were placed after the columns
if (column == "")
continue;
columnRangeString.Append(column + ":" + column + ",");
}
columnRangeString.Length = columnRangeString.Length - 1;
Range range = sheet.get_Range(columnRangeString.ToString(), Type.Missing);
range.EntireColumn.Select();
}
}
public void copyRange(Excel.Worksheet source, Excel.Worksheet dest, String startCell, String endCell, String startDest)
{
// get the length of the range - this might need to change later, figured it would be
// easier if this just adapted by itself.
int end = System.Convert.ToInt32(endCell.Substring(1)) + System.Convert.ToInt32(startDest.Substring(1));
String col = startDest.Substring(0, 1);
String row = end.ToString();
String destEnd = col + row;
// select the source worksheet
source.Select();
// get the cell range
Excel.Range reel = source.get_Range(startCell, endCell);
// copy our data
reel.Copy();
// select the destination worksheet
dest.Select();
// pick our destination cell
Excel.Range targetCell = dest.get_Range(startDest, destEnd);
try
{
targetCell.Select();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message.ToString(), "Error - target cells not empty", System.Windows.Forms.MessageBoxButtons.OK);
return;
}
// paste our data
dest.Paste(targetCell);
}
private void copyRange(Excel.Worksheet origin, Excel.Worksheet dest, String startCell, String endCell, String startDest)
{
// get the length of the range - this might need to change later, figured it would be
// easier if this just adapted by itself.
int end = System.Convert.ToInt32(endCell.Substring(1)) + System.Convert.ToInt32(startDest.Substring(1));
String col = startDest.Substring(0, 1);
String row = end.ToString();
// select the source worksheet
this.source.Activate();
origin.Select();
// get the cell range
String blank = "\0";
Excel.Range firstBlank = origin.Cells.Find(blank, origin.get_Range(startCell), Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns);
if (firstBlank != null)
{
String temp = firstBlank.Cells.get_Address().ToString().Replace("$", "");
int endRow = System.Convert.ToInt32(temp.Substring(1));
row = endRow.ToString();
}
int destEndRow = (8 + end) - parseRow(startCell);
String destEnd = String.Concat(col, destEndRow);
String sourceEnd = String.Concat(startCell.Substring(0, 1), row);
Excel.Range reel = origin.get_Range(startCell, sourceEnd);
// copy our data
reel.Copy();
// select the destination worksheet
this.target.Activate();
this.targetSheet.Select();
// pick our destination cell
Excel.Range targetCell = dest.get_Range(startDest, destEnd);
try
{
targetCell.Select();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("Error code:\n" + e.Message.ToString(), "Error - target cells not empty", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
// paste our data
try
{
targetCell.PasteSpecial(Excel.XlPasteType.xlPasteValues);
}
catch (Exception pasteEx)
{
System.Windows.Forms.MessageBox.Show("Error code:\n" + pasteEx.Message.ToString(), "Error - Can't paste cells", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
}
private bool checkValue(Excel.Workbook book, Excel.Worksheet sheet, String cell)
{
book.Activate();
if (book.ActiveSheet != sheet)
sheet.Select();
Excel.Range test = sheet.get_Range(cell);
try
{
sheet.Select(test);
}
catch (Exception e)
{
// apparently, an empty cell is not valid for selection.
return true;
}
try
{
if (test.Value2 == "" || test.Value2 == null)
return false;
else
return true;
}
catch (Exception e)
{
return false;
}
}
