//</Snippet36>
//---------------------------------------------------------------------
//<Snippet49>
private void AutoFill()
{
Excel.Range rng = this.Application.get_Range("B1");
rng.AutoFill(this.Application.get_Range("B1", "B5"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = this.Application.get_Range("C1");
rng.AutoFill(this.Application.get_Range("C1", "C5"),
Excel.XlAutoFillType.xlFillMonths);
rng = this.Application.get_Range("D1", "D2");
rng.AutoFill(this.Application.get_Range("D1", "D5"),
Excel.XlAutoFillType.xlFillSeries);
}
/// <summary>
/// Обновление значений урв 12
/// </summary>
/// <param name="pb"></param>
internal void UpdateUrv12()
{
pb.SetMainBarVolum(4);
pb.MainBarTick("Обновление формул \"Урв 12\"");
int colPaste = 1;
ProjectWorkbook projectWorkbook = new ProjectWorkbook();
Excel.Range dataRange = projectWorkbook.GetAnalysisRange();
List <OfferColumns> addresses = new ProjectWorkbook().OfferColumns;
pb.Writeline("Копирование заголовков");
PrintTitlesOffers12(addresses);
string letterNumber = projectWorkbook.GetLetter(StaticColumns.Number);
Excel.Range cellNumber = _AnalisysSheet.Range[$"${letterNumber}{_project.RowStart}"];
int columnCellNumber = cellNumber.Column;
// int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "ОБЩАЯ СУММА РАСХОДОВ (без НДС)").Row;
int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "НДС, 20%").Row - 1;
int lastRow = rowBottomTotal - 2;
pb.SetSubBarVolume(addresses.Count);
//// Вывод и форматирование значений
foreach (OfferColumns address in addresses)
{
if (pb.IsAborted)
{
throw new AddInException("Процесс остановлен");
}
pb.SubBarTick();
colPaste += 5;
pb.Writeline($"Формулы: {address.ParticipantName}");
string textCost = SheetUrv12.Cells[_rowStart, colPaste].Value?.ToString() ?? "";
if (!string.IsNullOrEmpty(textCost))
{
continue; // Пропустить заполненные КП
}
string formulaSumm = "";
for (int row = _rowStart; row <= lastRow; row++)
{
string number = SheetUrv12.Cells[row, 2].Value?.ToString() ?? "";
if (string.IsNullOrEmpty(number))
{
continue;
}
number = number.Trim(new char[] { ' ', '.' });
int levelNum = number.Split('.').Length;
if (levelNum == 1)
{
formulaSumm += (string.IsNullOrEmpty(formulaSumm)) ? $"={SheetUrv12.Cells[row, colPaste].Address}" :
$"+{SheetUrv12.Cells[row, colPaste].Address}";
}
// Формат строки по уровню
Dictionary <string, Excel.Range> pallets = ExcelReader.ReadPallet(_SheetPalette);
string keyLvl = levelNum.ToString();
if (pallets.TryGetValue(keyLvl, out Excel.Range pallet))
{
ExcelHelper.SetCellFormat(SheetUrv12.Range[SheetUrv12.Cells[row, colPaste], SheetUrv12.Cells[row, colPaste + 4]], pallet);
}
}
int col = address.ColCostTotalOffer - columnCellNumber + 1;
//РУБ. РФ
SheetUrv12.Cells[_rowStart, colPaste].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения материалы
col = address.ColDeviationMaterials - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 1].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения работы
col = address.ColDeviationWorks - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 2].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
// % отклонения всего
string letterOutTotalDiff = ExcelHelper.GetColumnLetter(SheetUrv12.Cells[_rowStart, colPaste]);
SheetUrv12.Cells[_rowStart, colPaste + 3].Formula = $"=${letterOutTotalDiff}{_rowStart}/$D{_rowStart}-1";
//КОММЕНТАРИИ К СТОИМОСТИ
col = address.ColComments - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 4].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
Excel.Range rng = SheetUrv12.Range[SheetUrv12.Cells[_rowStart, colPaste], SheetUrv12.Cells[_rowStart, colPaste + 4]];
Excel.Range destination = SheetUrv12.Range[SheetUrv12.Cells[_rowStart, colPaste], SheetUrv12.Cells[lastRow, colPaste + 4]];
rng.AutoFill(destination, Excel.XlAutoFillType.xlFillValues);
destination.Columns[2].NumberFormat = "0%";
destination.Columns[3].NumberFormat = "0%";
destination.Columns[4].NumberFormat = "0%";
if (!string.IsNullOrEmpty(formulaSumm))
{
SheetUrv12.Cells[rowBottomTotal, colPaste].Formula = formulaSumm;
SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}*0.2";
SheetUrv12.Cells[rowBottomTotal + 2, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}+" +
$"{SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Address}";
}
}
pb.Writeline("Условное форматирование");
SetConditionFormat12();
pb.MainBarTick("Формулы итогов");
TotalFormuls12();
pb.MainBarTick("Формат ячеек");
SetNumberFormat12(addresses.Count);
pb.MainBarTick("Общие комментарии");
new CommonComments(projectWorkbook).SetInfo();
//TODO загрузить наиболее дорогии позиции
}
// заполнение массива Item.bmd d Excel
unsafe private void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook = 1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
// получаем страницы книги
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < ITEM_MAX_TYPES - 1; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < ITEM_MAX_TYPES; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
// заполняем тип вещей
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < ITEM_MAX_IN_TYPE; j++)
{
progressBar2.Value = j;
// заполняем имя
if (Items[i, j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
// заполняем остальные ячейки
fixed(Int64 *buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
// перепрыгиваем на 1 лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Activate();
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
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;
}
}
private void PopulateStatColumn(int column, Excel.Range start, Excel.Range end)
{
try
{
// Determines the range that needs to get filled with data.
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:
// Fills in the daily sales column.
// Gets the addresses of the cells containing the
// standard deviation and average.
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);
// Sets the formulas for the column.
start.Formula = "=" + averageAddress + "+ (2*" + standardDevAddress + ")";
// Format "0.00" - two decimal places.
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.Required:
// Fills in the required column.
// Determines the address for the cell containing
// the expected sales.
Excel.Range expectedSales = start.Previous;
string salesAddress = expectedSales.get_Address(false, false, Excel.XlReferenceStyle.xlA1, defaultParameter, defaultParameter);
// Determines how much inventory is required
// until delivery.
// Determines the number of days until delivery.
int waitDays = this.GetDaysToDelivery();
start.Formula = "=" + waitDays + "*" + salesAddress;
// Format "0.00" - two decimal places.
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.CurrentInventory:
// Fills in the current inventory column.
// Gets the range for the last day from the journal.
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:
// Gets the addresses for the projected sales and
// current inventory.
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);
// Determines the inventory expected on the
// delivery date.
start.Formula = "=MAX(0," + currentInventoryAddress + "-" + requiredAddress + ")";
// Format "0.00" - two decimal places.
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
case (int)StatHeadings.OrderQuanity:
// Determines the addresses for the projected inventory
// and the required amounts.
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);
// Determines the order size needed for each item.
start.Formula = "=" + neededAddress + "-" + projectedInventoryAddress;
// Format "0.00" - two decimal places.
start.NumberFormat = "0.00";
twoLines.AutoFill(fillRange, Excel.XlAutoFillType.xlFillDefault);
break;
default:
break;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
throw;
}
}
/// <summary>
/// If set fileName, the excel will save and quit
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Data"></param>
/// <param name="sheetName"></param>
/// <param name="otherAction"></param>
/// <param name="fileName"></param>
public static void ExportToExcel <T>(this IEnumerable <T> Data, string sheetName, Action <object> otherAction, string fileName = null) where T : class
{
List <PropertyInfo> props = typeof(T).GetProperties().Where(p => (p.PropertyType == typeof(string) || p.PropertyType.IsPrimitive) && p.GetCustomAttribute <IgnoreAttribute>() == null).ToList();
if (exApp == null)
{
exApp = new Ex.Application();
}
exApp.Visible = true;
Ex.Workbooks wbs = exApp.Workbooks;
if (exWb == null)
{
exWb = wbs.Add();
}
var sheet = exWb.Worksheets.Add() as Ex.Worksheet;
sheet.Name = sheetName;
Ex.Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1 + Data.Count(), props.Count]];
object[,] datas = new object[Data.Count() + 1, props.Count];
List <Tuple <string, int> > formualList = new List <Tuple <string, int> >();
for (int i = 0; i < props.Count; i++)
{
var attr = props[i].GetCustomAttribute <DisplayAttribute>();
if (attr != null)
{
datas[0, i] = attr.Name;
}
else
{
datas[0, i] = props[i].Name;
}
var formulaAttr = props[i].GetCustomAttribute <ExcelFormulaAttribute>();
if (formulaAttr != null)
{
Tuple <string, int> item = new Tuple <string, int>(formulaAttr.Formula, i + 1);
formualList.Add(item);
}
}
for (int i = 0; i < Data.Count(); i++)
{
for (int j = 0; j < props.Count; j++)
{
var val = props[j].GetValue(Data.ElementAt(i));
datas[i + 1, j] = val;
}
}
range.Value = datas;
for (int i = 0; i < props.Count; i++)
{
range = sheet.Cells[1, i + 1];
var attr = props[i].GetCustomAttribute <ExcelHeaderStyleAttribute>();
if (attr != null)
{
range.Interior.Color = attr.BackgroundColor;
range.EntireColumn.NumberFormat = attr.NumberFormat;
range.ColumnWidth = attr.Width;
range.Font.Bold = attr.IsFontBold;
range.Font.Size = attr.FontSize;
range.WrapText = attr.IsTextWrap;
range.HorizontalAlignment = attr.HAlign;
range.VerticalAlignment = attr.VAlign;
}
}
if (formualList.Count > 0)
{
foreach (var item in formualList)
{
range = sheet.Cells[2, item.Item2];
range.Formula = item.Item1;
range.AutoFill(sheet.Range[sheet.Cells[2, item.Item2], sheet.Cells[Data.Count() + 1, item.Item2]], Ex.XlAutoFillType.xlFillDefault);
}
}
if (otherAction != null)
{
otherAction(sheet);
}
range = sheet.UsedRange;
range.Borders[Ex.XlBordersIndex.xlEdgeLeft].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeTop].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeRight].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders[Ex.XlBordersIndex.xlEdgeBottom].LineStyle = Ex.XlLineStyle.xlContinuous;
range.Borders.Color = ConsoleColor.Black;
range = sheet.Cells[1, 1] as Ex.Range;
range.Select();
if (!string.IsNullOrEmpty(fileName))
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
exWb.SaveAs(fileName);
exWb.Close();
exApp.Quit();
Marshal.ReleaseComObject(range);
range = null;
Marshal.ReleaseComObject(sheet);
sheet = null;
Marshal.ReleaseComObject(exWb);
exWb = null;
Marshal.ReleaseComObject(wbs);
wbs = null;
Marshal.ReleaseComObject(exApp);
exApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}