internal void AddTable()
{
Excel.Range sumRange = BottomCell().get_Offset(2, 0);
sumRange.Value2 = yr();
object[] years = Utilities.ExcelHelpers.getYears(DS.SourceData);
int ycols = years.Length;
sumRange = sumRange.get_Resize(1, ycols + 1);
for (int i = 0; i < ycols; i++)
{
if (false)//i.Equals(0))//
{
sumRange.get_Offset(0, i + 1).get_Resize(1, 1).Value2 = years[i] + " (Baseline)";
}
else
{
sumRange.get_Offset(0, i + 1).get_Resize(1, 1).Value2 = years[i];
}
}
SummaryData = thisSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, sumRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
SummaryData.Name = "Annual" + SummaryData.Name;
((Excel.Range)SummaryData.Range[1, 1]).Value2 = " ";
SummaryData.TableStyle = "TableStyleMedium4";
}
private void dropDownYear_SelectionChanged(object sender, RibbonControlEventArgs e)
{
Excel.Range thisRange = Globals.ThisAddIn.Application.Selection as Excel.Range;
Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
ExcelHelpers.SetYear(thisList, thisRange, dropDownYear.SelectedItemIndex - 1);
}
private void add_ListColumn(object sender, RibbonControlEventArgs e, string ColName)
{
Excel.Worksheet thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
ExcelHelpers.AddListColumn(thisList, ColName, 0);
}
public ExcelHandlerModel2()
{
SummaryTableTest.Clear();
SummaryTableTest.Columns.Add("Файл");
SummaryTableTest.Columns.Add("Статус");
SummaryTableTest.Columns.Add("Количество контрактов", System.Type.GetType("System.Int32"));
app = new Excel.Application();
workbook = app.Workbooks.Add(Type.Missing);
worksheet = workbook.ActiveSheet;
table = worksheet.ListObjects.Add();
table.Name = "Результат";
Excel.Range header = table.HeaderRowRange;
header[1, 1].Value = "ОСВ";
header[1, 2].Value = "КФО";
header[1, 3].Value = "Контрагент";
header[1, 4].Value = "Договор";
header[1, 5].Value = "Дебет";
header[1, 6].Value = "Кредит";
table.ListColumns.Item[5].Range.NumberFormatLocal = @"# ##0,00";
table.ListColumns.Item[6].Range.NumberFormatLocal = @"# ##0,00";
//table.ListColumns.Item[5].Range.NumberFormat = @"@";
}
public frmDEMapColumnValues(Interop.ListObject inExcelTable, string columnName,
string newColDTusrKey, string oldColDTkey,
Dictionary <object, List <object> > ExCellValToUsrVal)
{
InitializeComponent();
_intputExTable = inExcelTable;
_colToInspect = columnName;
// store the new data type of the column
_colToInspectNewDTKey = newColDTusrKey;
// store the aproximated column value
_colToInspectOldDTKey = oldColDTkey;
// Excel cell value to be mapped to a user value dict
if (ExCellValToUsrVal != null)
{
_ExCellValToUserValDict = ExCellValToUsrVal;
}
else
{
_ExCellValToUserValDict = new Dictionary <object, List <object> >();
}
}
public static Excel.Range getYearRange(Excel.ListObject LO, string year)
{
Excel.ListColumn LCyear = GetListColumn(LO, EnPIResources.yearColName);
int first = 0;
int last = 0;
int j = 0;
if (LCyear != null)
{
foreach (Excel.Range row in LCyear.Range.Rows)
{
j += 1;
if (row.Value2.ToString() == year && first == 0)
{
first = j;
}
if (row.Value2.ToString() == year)
{
last = j;
}
}
}
if (first != 0)
{
return(LO.Range.get_Offset(first - 1, 0).get_Resize(last - (first - 1), LO.ListColumns.Count));
}
else
{
return(null);
}
}
} // func RefreshTableAsync
internal async Task RefreshTableAsync(Excel.ListObject _xlList, bool refreshLayout)
{
using (var progress = CreateProgress())
{
var xlList = Globals.Factory.GetVstoObject(_xlList);
progress.Report(String.Format("Aktualisiere {0}...", xlList.Name ?? "Tabelle"));
if (PpsListObject.TryGet(FindEnvironment, xlList, out var ppsList))
{
await ppsList.RefreshAsync(refreshLayout?PpsXlRefreshList.Style : PpsXlRefreshList.None, PpsMenu.IsSingleLineModeToggle(), null);
}
else
{
//if (refreshColumnLayout)
// ;
switch (xlList.SourceType)
{
case Excel.XlListObjectSourceType.xlSrcQuery:
case Excel.XlListObjectSourceType.xlSrcXml:
try
{
xlList.Refresh();
}
catch (COMException)
{ }
break;
}
}
}
} // func RefreshTableAsync
public void Update()
{
this.Name = this.ListObject.Name;
this.DisplayName = this.Worksheet.Name;
if (string.IsNullOrWhiteSpace(this.DataName))
{
this.Icon = Properties.Resources.table;
this.DataDisplayName = string.Empty;
this.ListObject.Comment = string.Empty;
}
else
{
this.Icon = Properties.Resources.table_link;
Excel.ListObject dataListObject = ListObjectHelper.GetByName(this.Workbook, this.DataName);
if (null != dataListObject)
{
this.DataDisplayName = ListObjectHelper.GetWorksheetName(dataListObject);
}
else
{
this.DataDisplayName = string.Empty;
}
}
}
public TestCase(Excel.Workbook workbook, Excel.Worksheet worksheet, Excel.ListObject listObject)
{
if (null == workbook)
{
throw new ArgumentNullException("workbook");
}
if (null == worksheet)
{
throw new ArgumentNullException("worksheet");
}
if (null == listObject)
{
throw new ArgumentNullException("listObject");
}
this.Workbook = workbook;
this.Worksheet = worksheet;
this.ListObject = listObject;
this.DataName = listObject.Comment;
this.Result = TestResult.None;
this.Load();
this.Update();
}
private void ExecuteInternal()
{
var workbookContext = App.Context.GetActiveWorkbookContext();
Excel.Workbook workbook = workbookContext.Workbook;
Excel.Worksheet worksheet = ExcelHelper.WorksheetAdd(workbook);
ExcelHelper.WorksheetActivate(worksheet);
string name = ListObjectHelper.NewTestDataName(workbook);
worksheet.Name = name;
Excel.ListObject listObject = ListObjectHelper.AddListObject(worksheet);
listObject.Name = name;
listObject.ListColumns[1].Name = "Column1";
listObject.ListColumns[1].Range.EntireColumn.AutoFit();
for (int i = 2; i < 10; i++)
{
Excel.ListColumn listColumn = listObject.ListColumns.Add();
listColumn.Name = string.Format(
CultureInfo.CurrentCulture,
"Column{0}",
i);
listColumn.Range.EntireColumn.AutoFit();
}
ListObjectHelper.AddRows(listObject, DEFAULT_ROW_COUNT, false);
ListObjectHelper.SelectCell(listObject, 2, 1);
}
internal string addProductionColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string colName = "";
string formula = "";
Excel.ListObject dataList = ExcelHelpers.GetListObject(WS, dataListName);
object[,] headers = dataList.HeaderRowRange.Value2 as object[, ];
string dttbl = dataListName;
string dtyearcol = ExcelHelpers.GetListColumn(dataList, EnPIResources.yearColName).Name;
string yearcol = ExcelHelpers.GetListColumn(LO, EnPIResources.yearColName).Name;
int i = headers.GetLowerBound(0);
for (int j = headers.GetLowerBound(1); j <= headers.GetUpperBound(1); j++)
{
if (DataHelper.isProduction(headers[i, j].ToString()))
{
formula += "SUM(" + dttbl + ExcelHelpers.CreateValidFormulaName(headers[i, j].ToString()) + " " +
DataHelper.RowRangebyMatch(dttbl, dtyearcol, "[[#This Row]," + yearcol + "]", dataSheetName)
+ ")" + "+";
}
}
if (formula != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(2);
colName = "Production";
newCol.Name = colName;
newCol.DataBodyRange.Formula = "=" + formula.Substring(0, formula.Length - 1);
newCol.DataBodyRange.Style = "Comma [0]";
}
return(colName);
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//<snippet1>
Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet =
Globals.ThisAddIn.Application.ActiveSheet;
if (nativeWorksheet != null)
{
Microsoft.Office.Tools.Excel.Worksheet vstoSheet =
Globals.Factory.GetVstoObject(nativeWorksheet);
}
//</snippet1>
//<snippet2>
Microsoft.Office.Interop.Excel.Workbook nativeWorkbook =
Globals.ThisAddIn.Application.ActiveWorkbook;
if (nativeWorkbook != null)
{
Microsoft.Office.Tools.Excel.Workbook vstoWorkbook =
Globals.Factory.GetVstoObject(nativeWorkbook);
}
//</snippet2>
//<snippet3>
Microsoft.Office.Interop.Excel.Worksheet sheet =
Globals.ThisAddIn.Application.ActiveSheet;
if (sheet.ListObjects.Count > 0)
{
Excel.ListObject listObject =
sheet.ListObjects[1];
Microsoft.Office.Tools.Excel.ListObject vstoListObject =
Globals.Factory.GetVstoObject(listObject);
}
//</snippet3>
}
internal void AddConditionalFormatting(Excel.ListObject LO)
{
// for each independent variable
foreach (Excel.ListColumn LC in LO.ListColumns)
{
if (DS.IndependentVariables.Contains(LC.Name))
{
double low = 0;
double high = 0;
double[] values = Utilities.DataHelper.objectTOdblArray(LC.Range.Value2 as object[, ]);
string fltr = "VariableName='" + LC.Name.Replace("'", "''") + "'";
DataRow row = DS.PredictorRange().Select(fltr).First();
// get the low value
low = Double.TryParse(row[1].ToString(), out low) ? Double.Parse(row[1].ToString()) : 0;
// get the high value
high = Double.TryParse(row[2].ToString(), out high) ? Double.Parse(row[2].ToString()) : 0;
// apply conditional formatting
if (low != 0 && high != 0)
{
Excel.Range rng = Utilities.ExcelHelpers.GetListColumn(LO, LC.Name).DataBodyRange;
Excel.FormatCondition fc = (Excel.FormatCondition)rng.FormatConditions.Add(
Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotBetween, low, high
, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
fc.Font.ColorIndex = 3;
}
}
}
}
internal void modelInformation()
{
Excel.Range sumRange = BottomCell().get_Offset(2, 0);
((Excel.Range)sumRange[1, 1]).Value2 = " ";
//sumRange.Value2 = "Info";
ModelData = thisSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, sumRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
ModelData.Name = "Model" + ModelData.Name;
((Excel.Range)ModelData.Range[1, 1]).Value2 = " ";
ModelData.TableStyle = "TableStyleMedium4";
ModelData.ListColumns.Add(Type.Missing).Name = "Variables";
ModelData.ListColumns.Add(Type.Missing).Name = "Model is Appropriate for SEP";
ModelData.ListColumns.Add(Type.Missing).Name = "Model Validity";
ModelData.ListColumns.Add(Type.Missing).Name = "Model P-Value";
ModelData.ListColumns.Add(Type.Missing).Name = "Variable P-Value";
ModelData.ListColumns.Add(Type.Missing).Name = "Adjusted R2";
ModelData.ListColumns.Add(Type.Missing).Name = "Model Selected";
//get all the variables from the currently selected model.
foreach (String esv in DS.EnergySourceVariables)
{
ModelData.ListRows.AddEx(Type.Missing, Type.Missing);
ModelData.ListRows[DS.EnergySourceVariables.IndexOf(esv) + 1].Range.Value2 = esv;
}
}
public static void SetYear(Excel.ListObject LO, Excel.Range Sel, int YearNo)
{
Excel.ListColumn yearCol = GetListColumn(LO, EnPIResources.yearColName);
if (yearCol == null)
{
yearCol = AddListColumn(LO, EnPIResources.yearColName, 1);
}
// the selection
int firstRow = Sel.Row;
// if there are header rows in the selection or the selection starts above the header row, return
// we don't know where we are on the sheet relative to the list object
if (Sel.ListHeaderRows > 0 || firstRow < LO.HeaderRowRange.Row)
{
return;
}
// get the corresponding range in the year column
Excel.Range yearRows = yearCol.Range.get_Offset(firstRow - LO.HeaderRowRange.Row, 0).get_Resize(Sel.Rows.Count, 1);
yearRows.Value2 = YearNo;
yearRows.NumberFormat = "@";
}
public ReportingPeriodControl(bool fromWizard)
{
InitializeComponent();
DataLO = ((Excel.Range)Globals.ThisAddIn.Application.Selection).ListObject;
if (DataLO == null)
{
DataLO = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).ListObjects[1];
}
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
bool dateColChk = false;
foreach (Excel.ListColumn cname in thisList.ListColumns)
{
string colName = cname.Name.ToUpper();
if (colName.Equals("DATE"))
{
dateColChk = true;
cname.Name = "Date";
}
}
if (!dateColChk)
{
this.cbBaselineYear.Enabled = false;
this.cbInterval.Enabled = false;
this.cbLabel.Enabled = false;
this.btnReportingPeriod.Enabled = false;
}
this.btnBack.Visible = fromWizard;
this.btnClose.Visible = fromWizard;
this.btnNext.Visible = fromWizard;
}
public static int DataPt(Excel.ListObject LO, string year)
{
int TotDataPt = 0;
TotDataPt = Analytics.getDataPt(rangeTable(LO), year, EnPIResources.yearColName);
return(TotDataPt);
}
private void PopulateDropdown(Excel.ListObject DataLO)
{
this.comboBox1.Items.Clear();
cmbValues.Clear();
if (DataLO != null)
{
foreach (SwitchModel SM in sModCol)
{
string vars = "";
int count = 0;
foreach (string s in SM.VariableNames)
{
if (s != null && s != "(Intercept)")
{
if (SM.VariableNames.Length - 1 > count)
{
vars = vars + s + ", ";
}
else
{
vars = vars + s;
}
}
count++;
}
cmbValues.Add(vars.Substring(0, vars.Length - 1) + " (Adj R^2=" + Math.Round(SM.R2, 4) + ")", SM.ModelNumber);
this.comboBox1.Items.Add(vars.Substring(0, vars.Length - 1) + " (Adj R^2=" + Math.Round(SM.R2, 4) + ")");
}
}
}
public static Excel.ListObject writeDataTable(string startCell, ref Excel.Worksheet WS, ref DataTable dt)
{
// create list objects for the model year and adjusted year data
string start = startCell;
//int rowOffset = 3;
Excel.Range range1 = WS.get_Range(start, missing)
//.get_Offset(rowOffset, 0)
.get_Resize(dt.Rows.Count + 1, dt.Columns.Count);
range1.get_Resize(1, dt.Columns.Count).Value2 = DataHelper.dataTableHeaders(dt);
range1.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArray(dt);
// add in formulae
foreach (DataColumn dc in dt.Columns)
{
if (dc.ExtendedProperties["Formula"] != null)
{
range1.get_Offset(1, dc.Ordinal).get_Resize(dt.Rows.Count, 1).Value2 = dc.ExtendedProperties["formula"];
}
}
Excel.ListObject newObject = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range1, missing, Excel.XlYesNoGuess.xlYes, missing);
string tmp = newObject.Range.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing);
return(newObject);
}
/// <summary>
/// Sets <list type="bullet">
/// <item>parent <see cref="Excel.Worksheet"/> of <see cref="Excel.ListObject"/></item>
/// <item><see cref="Excel.ListObject"/> itself</item>
/// <item><see cref="iClickerQuizPts.ListObjMgmt.XLListObjWrapper.DoesListObjHaveData"/> property</item>
/// </list>
/// </summary>
/// <exception cref="iClickerQuizPts.AppExceptions.MissingWorksheetException"> thrown when the
/// parent <see cref="Excel.Worksheet"/> has either been renamed or deleted.</exception>
/// <exception cref="iClickerQuizPts.AppExceptions.MissingListObjectException"> thrown when
/// the <see cref="Excel.ListObject"/> has either been renamed or deleted.</exception>
/// <remarks>It would be more efficient to call this method from within the class
/// constructor. However, doing some complicates unit testing.</remarks>
public void SetListObjAndParentWshPpts()
{
if (!DoesParentWshExist())
{
MissingWorksheetException ex = new MissingWorksheetException();
ex.WshListObjPair = _wshLoPr;
throw ex;
}
else
{
_ws = Globals.ThisWorkbook.Worksheets[_wshLoPr.WshNm];
}
if (!DoesListObjExist())
{
MissingListObjectException ex = new MissingListObjectException();
ex.WshListObjPair = _wshLoPr;
throw ex;
}
else
{
_lo = _ws.ListObjects[_wshLoPr.ListObjName];
}
// Set fields...
_wshAndListObjIntegrityVerified = true;
_listObjHasData = DoesListObjHaveData();
}
public static List <Contact> ReadExcel(string filename)
{
Excel.Application excel = null;
Excel.Workbook workbook = null;
try {
excel = new Excel.Application();
excel.Visible = false;
workbook = excel.Workbooks.Open(filename, ReadOnly: true);
Excel.Worksheet sheet = workbook.Sheets[1];
Excel.ListObject table = sheet.ListObjects["Information_Table"];
List <ProtoContact> contacts = new List <ProtoContact>();
foreach (Excel.ListRow row in table.ListRows)
{
ProtoContact c = new ProtoContact(table.ListColumns, row);
contacts.Add(c);
}
Dictionary <string, bool> multiple = contacts.GroupBy(c => c.First).ToDictionary(cg => cg.Key, cg => cg.Count() > 1);
return(contacts.Select(c => {
string shortName = multiple[c.First] ? c.First + " " + c.Last.Substring(0, 1) + "." : c.First;
return new Contact(c, shortName);
}).ToList());
} finally {
if (workbook != null)
{
workbook.Close();
}
if (excel != null)
{
excel.Quit();
}
}
}
public static Excel.ListColumn GetListColumn(Excel.ListObject LO, String ColName)
{
Excel.ListColumn thisColumn = null;
if (ColName == null || LO == null)
{
return(null);
}
string thisName = ColName.Replace("\\", "/");
foreach (Excel.ListColumn i in LO.ListColumns)
{
if (i.Name.ToLower() == thisName.ToLower())
{
thisColumn = i;
}
}
//if (thisColumn == null)
//{
// thisColumn = AddListColumn(LO, thisName, Pos);
//}
return(thisColumn);
}
private void btnRun_Click(object sender, EventArgs e)
{
try
{
Excel.Worksheet thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
int orgCount = thisList.ListColumns.Count;
foreach (string t in this.checkedListBox1.CheckedItems)
{
int lcCount = thisList.ListColumns.Count;
int position = this.checkedListBox1.Items.IndexOf(t) + (lcCount - orgCount) + 2;
string colName = t + "(" + this.comboBox2.SelectedItem.ToString() + ")";
string formula = "=" + ExcelHelpers.CreateValidFormulaName(t) + "*" + this.textBox1.Text + "*" + this.textBox2.Text;
string stylename = "Comma";
Excel.ListColumn newColumn = thisList.ListColumns.Add(position);
newColumn.Name = colName;
newColumn.DataBodyRange.Formula = formula;
newColumn.DataBodyRange.Style = stylename;
//set old column property to not show up in regression/actual options
}
this.checkedListBox1.Items.Clear();
AddColumnNames();
}
catch
{
//raise error
}
}
public void DuplicateInExcel()
{
Visio.Window win = vApp.ActiveWindow;
if (win.Type != (short)Visio.VisWinTypes.visDrawing)
{
return;
}
if (win.Selection.Count < 1)
{
return;
}
xRulerOrigin = vApp.ActivePage.PageSheet.Cells["XRulerOrigin"].Result[Visio.VisUnitCodes.visMillimeters];
yRulerOrigin = vApp.ActivePage.PageSheet.Cells["YRulerOrigin"].Result[Visio.VisUnitCodes.visMillimeters];
Excel.Application xlsApp = new Excel.Application();
Excel.Workbook workbook = xlsApp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = "Positions";
worksheet.Cells[1, 1] = "X";
worksheet.Cells[1, 2] = "Y";
Excel.ListObject listObject = worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 2]], null, Excel.XlYesNoGuess.xlYes);
xlsApp.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
worksheet.Columns.AutoFit();
xlsApp.Visible = true;
}
public void FormatAsTable(Excel.Range SourceRange, string TableName)
{
Excel.ListObject table = SourceRange.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
table.Name = TableName;
}
/// <summary>
/// Check to see if there is an named range selected
/// </summary>
/// <param name="showMsg">To show a message </param>
/// <returns>A method that returns true or false if there is a valid list object </returns>
/// <remarks></remarks>
public static bool IsValidListObject(bool showMsg = false)
{
Excel.ListObject tbl = null;
try
{
tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject; // directly after the table is created this is not true
if ((tbl == null))
{
if (showMsg == true)
{
MessageBox.Show("The command could not be completed by using the range specified. Select a single cell within the range and try the command again. [ListObject]", AssemblyInfo.Description, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
else
{
return(true);
}
}
catch (Exception)
{
return(false);
}
finally
{
if (tbl != null)
{
Marshal.ReleaseComObject(tbl);
}
}
}
/// <summary>
/// Change zero string cell values to string "NULL"
/// </summary>
/// <remarks></remarks>
public static void UpdateZeroStringToNull(Excel.ListObject tbl)
{
try
{
if (ErrorHandler.IsAvailable(true) == false)
{
return;
}
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
ErrorHandler.CreateLogRecord();
Excel.Range cell = default(Excel.Range);
Excel.Range usedRange = tbl.Range;
for (int r = 0; r <= tbl.ListRows.Count; r++)
{
for (int c = 1; c <= tbl.ListColumns.Count; c++)
{
if (usedRange[r + 1, c].Value2 == null)
{
cell = tbl.Range.Cells[r + 1, c];
cell.Value = "NULL";
}
}
}
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
private static void WriteTableHeaderRangeToCSV(Interop.ListObject excelTable, char csvDelimiter)
{
Interop.Range tableHeaderRange = excelTable.HeaderRowRange;
string Headerline = String.Empty;
int columnInTableCount = 1;
foreach (Interop.Range cell in tableHeaderRange)
{
if (columnInTableCount == 1)
{
// assume the header is not null or empty
//Headerline = Escape(Convert.ToString(cell.Value2), csvDelimiter);
Headerline = EscapeCSVstyle(Convert.ToString(cell.Text), csvDelimiter);
}
else
{
// assume the header is not null or empty
//Headerline = String.Join(Convert.ToString(csvDelimiter), Headerline, Escape(Convert.ToString(cell.Value2), csvDelimiter));
Headerline = String.Join(Convert.ToString(csvDelimiter), Headerline, EscapeCSVstyle(Convert.ToString(cell.Text), csvDelimiter));
}
columnInTableCount++;
}
Headerline += Environment.NewLine;
File.WriteAllText(filePath, Headerline);
if (tableHeaderRange != null)
{
Marshal.ReleaseComObject(tableHeaderRange);
}
}
private List <ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
{
List <ExcelReferenceTable> Result = new List <ExcelReferenceTable>();
string Temp = "";
Excel.Worksheet xlWorkSheet = null;
Excel.ListObjects xlListObjects = null;
Excel.ListObject ThisItem = null;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
ExcelReferenceTable Item = new ExcelReferenceTable();
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
xlListObjects = xlWorkSheet.ListObjects;
Int32 TotalCount = xlListObjects.Count - 1;
for (int y = 0; y <= TotalCount; y++)
{
ThisItem = xlListObjects.Item[y + 1];
Item.Name = ThisItem.Name;
Item.SheetName = xlWorkSheet.Name;
// TODO: Need to tinker with this.
try
{
Excel.QueryTable QT = ThisItem.QueryTable;
Item.SourceDataFile = QT.SourceDataFile;
ReleaseComObject(QT);
}
catch (Exception)
{
Item.SourceDataFile = "";
}
Excel.Range ThisRange = ThisItem.Range;
Temp = ThisRange.Address;
Item.Address = Temp.Replace("$", "");
Result.Add(Item);
Marshal.FinalReleaseComObject(ThisRange);
ThisRange = null;
Marshal.FinalReleaseComObject(ThisItem);
ThisItem = null;
Marshal.FinalReleaseComObject(xlListObjects);
xlListObjects = null;
}
}
ReleaseComObject(xlWorkSheet);
mReferenceTables = Result;
return(Result);
}
internal bool Import(Commande cmd)
{
string path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
string file = $"{path}\\{dbName}_{cmd.Piece}.xlsm";
if (!File.Exists(file))
{
throw new Exception($"Fichier {file} non trouvé");
}
ExcelApp.Application app = new ExcelApp.Application();
ExcelApp.Workbook workbook = app.Workbooks.Open(file);
try
{
ExcelApp.Worksheet worksheet = workbook.Worksheets["Commande"];
ExcelApp.ListObject listObject = worksheet.ListObjects["tableCommande"];
foreach (ExcelApp.ListRow row in listObject.ListRows)
{
try
{
string Ref = (string)row.Range[2].Value();
double?qt = (double?)row.Range[3].Value();
double stock = ((double?)row.Range[10].Value()) ?? 0;
Debug.Print($"{Ref} - {qt} - {stock}");
LigneCommande ligne = cmd.Lignes.Where(l => l.Ref == Ref).FirstOrDefault();
if (ligne == null)
{
continue;
}
if (qt != ligne.Qte)
{
ligne.Qte = qt;
}
if (stock != ligne.Stock)
{
ligne.Stock = stock;
}
}
catch (Exception)
{
// TODO Log
}
}
return(true);
}
catch (Exception e)
{
throw e;
}
finally
{
if (workbook != null)
{
workbook.Close(false);
}
app.Quit();
}
}
/// <summary>
/// Da Formato a la Hoja de Excel Creando los
/// </summary>
private void RequisitionServiceFormat()
{
try
{
excelApp = Globals.ThisAddIn.Application;
var excelBook = excelApp.Workbooks.Add();
Excel.Worksheet excelSheet = excelBook.ActiveSheet;
excelSheet.Name = SheetName01;
cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Style = cells.GetStyle(StyleConstants.Normal);
cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Clear();
cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Clear();
cells.GetCell("A1").Value = "CERREJÓN";
cells.GetCell("B1").Value = "Requisition Service";
cells.GetCell(1, TittleRow).Value = "Requested By";
cells.GetCell(2, TittleRow).Value = "Requested By Position";
cells.GetCell(3, TittleRow).Value = "Requisition Number";
cells.GetCell(4, TittleRow).Value = "Requisition Type";
cells.GetCell(5, TittleRow).Value = "Transaction Type";
cells.GetCell(6, TittleRow).Value = "Required By Date";
cells.GetCell(6, TittleRow).AddComment("YYYYMMDD");
cells.GetCell(7, TittleRow).Value = "Original Warehouse";
cells.GetCell(8, TittleRow).Value = "Priority Code";
cells.GetCell(9, TittleRow).Value = "Reference Type";
cells.GetCell(10, TittleRow).Value = "Reference";
cells.GetCell(11, TittleRow).Value = "Delivery Instructions";
cells.GetCell(12, TittleRow).Value = "Return Cause";
cells.GetCell(13, TittleRow).Value = "Issue Question";
cells.GetCell(14, TittleRow).Value = "Stock Code";
cells.GetCell(15, TittleRow).Value = "Unit Of Issue";
cells.GetCell(16, TittleRow).Value = "Quantity";
cells.GetCell(ResultColumn, TittleRow).Value = "Result";
cells.GetCell("E1").Value = "OBLIGATORIO";
cells.GetCell("E1").Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell("E2").Value = "OPCIONAL";
cells.GetCell("E2").Style = cells.GetStyle(StyleConstants.TitleOptional);
cells.GetCell("E3").Value = "INFORMATIVO";
cells.GetCell("E3").Style = cells.GetStyle(StyleConstants.TitleInformation);
cells.GetCell("E4").Value = "ACCIÓN A REALIZAR";
cells.GetCell("E4").Style = cells.GetStyle(StyleConstants.TitleAction);
cells.GetCell("E5").Value = "REQUERIDO ADICIONAL";
cells.GetCell("E5").Style = cells.GetStyle(StyleConstants.TitleAdditional);
#region Styles
cells.GetCell(1, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(2, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(3, TittleRow).Style = cells.GetStyle(StyleConstants.TitleInformation);
cells.GetCell(4, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(5, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(6, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(7, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(8, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(9, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(10, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(11, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(12, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
cells.GetCell(13, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
cells.GetCell(14, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(15, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
cells.GetCell(16, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
cells.GetCell(ResultColumn, TittleRow).Style = cells.GetStyle(StyleConstants.TitleInformation);
#endregion
excelSheetItems = excelSheet.ListObjects.AddEx(SourceType: Excel.XlListObjectSourceType.xlSrcRange, Source: cells.GetRange(1, TittleRow, ResultColumn, 10000), XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
excelSheetItems.Name = "RequisitionsData";
excelSheet.Cells.Columns.AutoFit();
excelSheet.Cells.Rows.AutoFit();
//ExcelSheet.Protection.AllowEditRanges.Add("Editable", worksheet.Range["B2:B3,A5:O100000"]);
//ExcelSheet.Protect(Type.Missing, true, true, true, true, true, true, true, false, true, false, false, false, true, true, false);
#region Selection List Options
var optionList = new List<string> { "Work Order", "Equipment No.", "Project No.", "Account Code" };
cells.SetValidationList(cells.GetRange(9, TittleRow + 1, 9, MaxRows), optionList);
optionList = new List<string>
{
"NI - NORMAL REQUISITION",
"PR - PURCHASE REQUISITION",
"CR - CREDIT REQUISITION",
"LN - LOAN REQUISITION"
};
cells.SetValidationList(cells.GetRange(4, TittleRow + 1, 4, MaxRows), optionList);
optionList = new List<string>
{
"DN - DESPACHO NO PLANEADO",
"DP - DESPACHO PLANEADO",
"CN - DEVOLUCION NO PLANEADA",
"CP - DEVOLUCION PLANEADA"
};
cells.SetValidationList(cells.GetRange(5, TittleRow + 1, 5, MaxRows), optionList);
optionList = new List<string> { "", "A - VENTAS", "B - RUBROS" };
cells.SetValidationList(cells.GetRange(13, TittleRow + 1, 13, MaxRows), optionList);
optionList = GetPriorityTable();
cells.SetValidationList(cells.GetRange(8, TittleRow + 1, 8, MaxRows), optionList);
#endregion
cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Style.NumberFormat = "@";
}
catch (Exception error)
{
cells.GetCell(ResultColumn, TittleRow).Value += " Error " + error.Message;
}
}
