private void btnAccept_Click(object sender, EventArgs e)
{
int cantRows = 0;
if (txtCantIndices.Text.Trim() != string.Empty)
{
cantRows = Convert.ToInt32(txtCantIndices.Text);
Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
if ((cantRows > 0) && (cantRows <= NewActiveWorksheet.Rows.Count))
{
Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells;
NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
Generales.InsertIndice(NewActiveWorksheet, cantRows, currentCell, ConFormula, NroPrincipal);
NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
this.Close();
}
else
{
MessageBox.Show("Especifique por favor un dato válido.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Especifique por favor la cantidad de índices a insertar.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void ExelOpenButton_Click(object sender, EventArgs e)
{
excelapp = new Microsoft.Office.Interop.Excel.Application();
excelapp.SheetsInNewWorkbook = 1;
excelworkbook = excelapp.Workbooks.Add(Type.Missing);
excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkbook.Sheets[1];
excelworksheet.Cells.Font.Size = 10;
excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Font.Size = 12;
excelworksheet.Cells.ColumnWidth = 10;
excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[1, 2]].Merge();
excelworksheet.Range[excelworksheet.Cells[1, 3], excelworksheet.Cells[1, 6]].Merge();
excelworksheet.Range[excelworksheet.Cells[1, 7], excelworksheet.Cells[1, 8]].Merge();
for (int i = 2; i <= 9; i++)
{
excelworksheet.Range[excelworksheet.Cells[i, 1], excelworksheet.Cells[i, 2]].Merge();
excelworksheet.Range[excelworksheet.Cells[i, 3], excelworksheet.Cells[i, 8]].Merge();
}
excelworksheet.Cells[1, 1] = "Клиент:";
excelworksheet.Cells[2, 1] = "Пол:";
excelworksheet.Cells[3, 1] = "Возраст:";
excelworksheet.Cells[4, 1] = "Зарегестрирован:";
excelworksheet.Cells[5, 1] = "Паспорт:";
excelworksheet.Cells[6, 1] = "Права до:";
excelworksheet.Cells[7, 1] = "Задолженность:";
excelworksheet.Cells[9, 1] = "Список заказов:";
excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[10, 2]].Merge();
excelworksheet.Cells[10, 1] = "Автомобиль";
excelworksheet.Range[excelworksheet.Cells[10, 3], excelworksheet.Cells[10, 4]].Merge();
excelworksheet.Range[excelworksheet.Cells[10, 5], excelworksheet.Cells[11, 5]].Merge();
excelworksheet.Cells[10, 5] = "Кол-во дней опоздания возврата";
excelworksheet.Cells[10, 3] = "Даты";
excelworksheet.Range[excelworksheet.Cells[10, 6], excelworksheet.Cells[10, 8]].Merge();
excelworksheet.Cells[10, 6] = "Сумма";
excelworksheet.Cells[11, 1] = "Инв. №";
excelworksheet.Cells[11, 2] = "Модель";
excelworksheet.Cells[11, 3] = "выдачи";
excelworksheet.Cells[11, 4] = "возврата";
excelworksheet.Cells[11, 6] = "проката";
excelworksheet.Cells[11, 7] = "пени";
excelworksheet.Cells[11, 8] = "ИТОГО";
excelworksheet.Cells.VerticalAlignment = true;
excelworksheet.Cells.WrapText = true;
excelworksheet.Range[excelworksheet.Cells[11, 1], excelworksheet.Cells[11, 8]].RowHeight = excelworksheet.Cells[10, 1].RowHeight * 2;
excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Borders.Color = XlRgbColor.rgbWhite;
excelworksheet.Range[excelworksheet.Cells[1, 1], excelworksheet.Cells[9, 8]].Borders.Item[XlBordersIndex.xlEdgeRight].Color = XlRgbColor.rgbBlack;
excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[11, 8]].Font.Bold = true;
excelworksheet.Range[excelworksheet.Cells[10, 1], excelworksheet.Cells[11, 8]].Borders.LineStyle = XlLineStyle.xlContinuous;
excelworksheet.Cells[1, 3].Font.Bold = true;
excelworksheet.Cells[1, 7].HorizontalAlignment = XlHAlign.xlHAlignCenter;
excelworksheet.Cells[1, 7].Borders.Color = XlRgbColor.rgbBlack;
excelworksheet.Range[excelworksheet.Cells[1, 7], excelworksheet.Cells[1, 8]].Borders.LineStyle = XlLineStyle.xlContinuous;
setExcelTableCells();
excelworksheet.Protect();
excelapp.Visible = true;
}
public static void Append(RequestPackage rqp)
{
try
{
Guid guid = Guid.NewGuid();
if (!String.IsNullOrWhiteSpace(rqp["f0"] as String))
{
Guid.TryParse((rqp["f0"] as String), out guid);
}
String f0 = System.Convert.ToBase64String(guid.ToByteArray()).Substring(0, 22);
XlConnection.Open();
Excel.Worksheet sheet = XlConnection.Sheet;
// ищем первую пустую строку начиная с третьей
int ri;
for (ri = 3; ri < 10000; ri++)
{
if (sheet.Cells[ri, 1].Value == null)
{
break;
}
}
if (ri < 10000)
{
// добавляем ячейки
Excel.Range row = sheet.Range[sheet.Cells[ri, 1], sheet.Cells[ri, 19]];
sheet.Unprotect();
XlConnection.App.EnableEvents = false;
row[1, 1] = f0;
XlConnection.App.EnableEvents = true;
sheet.Protect();
row[1, 4] = rqp["f3"] as String;
//row[1, 5] = rqp["f4"] as String; // сама обновится через макрос Exel
row[1, 6] = rqp["f5"] as String;
row[1, 7] = rqp["f6"] as String;
row[1, 8] = rqp["f7"] as String;
row[1, 9] = XlConvert.ToXlDatetime(rqp["f8"] as String);
row[1, 10] = XlConvert.ToXlDatetime(rqp["f9"] as String);
row[1, 11] = XlConvert.ToXlDatetime(rqp["f10"] as String);
row[1, 12] = XlConvert.ToXlDatetime(rqp["f11"] as String);
row[1, 13] = rqp["f12"] as String;
row[1, 14] = rqp["f13"] as String;
row[1, 15] = rqp["f14"] as String;
row[1, 16] = XlConvert.ToXlFloat(rqp["f15"] as String);
row[1, 17] = XlConvert.ToXlInt(rqp["f16"] as String);
row[1, 18] = rqp["f17"] as String;
row[1, 19] = rqp["f18"] as String;
XlConnection.App.DisplayAlerts = false;
XlConnection.Book.Save();
}
}
catch (Exception ex)
{
Log.Write("XlStoredProcedure.Append(): " + ex.ToString());
}
finally { XlConnection.Close(); }
}
private void barBtnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在保存数据, 请稍候...");
//判断文件夹xls是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
try
{
//保存excel文件
fpSpread1.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls");
//以下是打开文件设表格自动换行
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls";
workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
wait.Close();
MsgBox.Show("保存成功");
}
catch (System.Exception ee)
{
wait.Close();
MsgBox.Show("保存错误！确定您安装有Office Excel,或者关闭所有Excel文件重试");
}
}
public static void CleanPassword(this Excel.Worksheet sheet)
{
#region 一个小功能
sheet.Protect
(
DrawingObjects: Office.MsoTriState.msoTrue,
Contents: Office.MsoTriState.msoTrue,
Scenarios: Office.MsoTriState.msoTrue,
AllowFiltering: Office.MsoTriState.msoTrue,
AllowUsingPivotTables: Office.MsoTriState.msoTrue
);
sheet.Protect
(
DrawingObjects: Office.MsoTriState.msoTrue,
Contents: Office.MsoTriState.msoTrue,
Scenarios: Office.MsoTriState.msoTrue,
AllowFiltering: Office.MsoTriState.msoTrue,
AllowUsingPivotTables: Office.MsoTriState.msoTrue
);
sheet.Protect
(
DrawingObjects: Office.MsoTriState.msoTrue,
Contents: Office.MsoTriState.msoTrue,
Scenarios: Office.MsoTriState.msoTrue,
AllowFiltering: Office.MsoTriState.msoTrue,
AllowUsingPivotTables: Office.MsoTriState.msoTrue
);
sheet.Protect
(
DrawingObjects: Office.MsoTriState.msoTrue,
Contents: Office.MsoTriState.msoTrue,
Scenarios: Office.MsoTriState.msoTrue,
AllowFiltering: Office.MsoTriState.msoTrue,
AllowUsingPivotTables: Office.MsoTriState.msoTrue
);
sheet.Unprotect();
#endregion
}
private void btnAccept_Click(object sender, EventArgs e)
{
string Mensaje = string.Empty;
Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells;
Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
if (TxtExplicacion.Text.Trim() == string.Empty)
{
MessageBox.Show("Especifique por favor la explicación.", "Explicación índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (TxtExplicacion.Text.Length < 100)
{
Mensaje = "La explicación especificada tiene " + lblcontador.Text + " caracteres, debe contener al menos 100. ¿Desea continuar ? ";
DialogResult dialogo = MessageBox.Show(Mensaje,
"Explicación índice", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogo == DialogResult.Yes)
{
NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text);
NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
this.Close();
}
}
else
{
if (TxtExplicacion.Text.Length >= 100)
{
NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text);
NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
this.Close();
}
}
}
}
private void GenerateSheet(Excel.Worksheet sheet, string sheetName)
{
sheet.Name = sheetName;
sheet.Cells.Locked = false;
var columns = definition.Worksheets[EnumUtils.EnumValue <ConfigurationFile.WorkSheets>(sheetName)].Columns;
foreach (var column in columns)
{
if (EnumUtils.IsOptional(column.TargetField))
{
sheet.Cells[1, column.Position] = "(Optional)";
sheet.Columns[column.Position].Style = "Optional";
}
else
{
sheet.Cells[1, column.Position] = "(Required)";
}
sheet.Cells[1, column.Position].Style = "HeadingDetail";
sheet.Cells[2, column.Position] = column.Heading;
sheet.Cells[2, column.Position].Style = "HeadingTitle";
var defaultValue = EnumUtils.GetDefault(column.TargetField);
if (defaultValue != null)
{
sheet.Cells[3, column.Position] = $"(Default: {defaultValue.ToString()})";
}
sheet.Cells[3, column.Position].Style = "HeadingDetail";
var options = EnumUtils.GetOptions(column.TargetField);
if (options.Count() > 0)
{
var cell = (Excel.Range)sheet.Columns[column.Position];
cell.Validation.Add(
Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop,
Excel.XlFormatConditionOperator.xlBetween,
string.Join(", ", options));
}
}
sheet.Columns.AutoFit();
sheet.Protect(Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
// Freeze the header rows.
sheet.Activate();
sheet.Application.ActiveWindow.SplitRow = 3;
sheet.Application.ActiveWindow.FreezePanes = true;
}
public void ProtectSheet(ExcelInterop.Worksheet concernedSheet)
{
if (concernedSheet != null && !concernedSheet.ProtectContents)
{
concernedSheet.Cells.Locked = false;
concernedSheet.Protect(Type.Missing, false, false, Type.Missing, false, true,
true, true,
false, false,
false,
false, false, false, true,
true);
}
}
/// <summary>
/// 保护工作表.
/// </summary>
/// <param name="sheetName"> 工作表名 </param>
/// <param name="password"> 密码 </param>
public void Protect(
string sheetName,
string password = "")
{
// 选择源工作表.
Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName);
mySheet.Protect(
Password: password,
DrawingObjects: true,
Contents: true,
Scenarios: true);
}
public static void RefreshLog()
{
Excel.Worksheet log = _wb.Sheets["Log"];
bool prot = log.ProtectContents;
if (prot)
{
log.Unprotect(Password);
}
DataBase db = new DataBase();
db.RefreshLog();
if (prot)
{
log.Protect(Password);
}
}
public static void InsertLog(Core.DataBase.TipologiaLOG logType, string message)
{
Excel.Worksheet log = _wb.Sheets["Log"];
bool prot = log.ProtectContents;
if (prot)
{
log.Unprotect(Password);
}
DataBase db = new DataBase();
db.InsertLog(logType, message);
if (prot)
{
log.Protect(Password);
}
}
private void btnEliminaeExplicacion_Click(object sender, RibbonControlEventArgs e)
{
Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
int NroRow = currentCell.Row;
Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
currentCell = (Excel.Range)NewActiveWorksheet.Cells[NroRow, 1];
string indice = currentCell.Value2;
if (indice.ToUpper().Trim() == "EXPLICACION")
{
NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
//ref
string NombreHoja = NewActiveWorksheet.Name.ToUpper().Replace(" ", "");
List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja);
int _Registro = 1;
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
string _NameFile = wb.Name;
int row = Generales.DameRangoPrincipal(NroRow, NewActiveWorksheet);
foreach (oSubtotal ST in ColumnasST)
{
if (_Registro == 1)
{
_Registro += 1;
Generales.ActualizarReferencia(_NameFile, NewActiveWorksheet.Name.ToUpper(), ST.Columna + row.ToString(), 0, ST.Columna, row.ToString(), 1, "E");
}
}
//ref
NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
}
else
{
MessageBox.Show("La fila seleccionada no es una explicación ", "Eliminar Explicación", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
void activeSheet_SelectionChange(object sh, Excel.Range target)
{
_sheet1 = (Excel.Worksheet)sh;
if (target.Row != 1 && (_objeto.FirstOrDefault(x => x.Nombre == _sheet1.Name) != null))
{
try
{
_sheet1.Unprotect();
Globals.ThisAddIn.Application.Cells.Locked = false;
//BloquearRango(_rowCount);
_sheet1.Protect(AllowSorting: true, AllowFiltering: true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
else
{
_sheet1.Unprotect();
}
}
/// <summary>Función para Proteger y Desproteger las hojas de un archivo de Excel.
/// <para>Desprotege y Protege un archivo de Excel. Referencia: <see cref="Proteccion(bool)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para>
/// <seealso cref="Proteccion(bool)"/>
/// </summary>
public static void Proteccion(bool accion)
{
int f;
FileInfo _Excel = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName);
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
using (ExcelPackage _package = new ExcelPackage(_Excel))
{
for (f = 1; f <= _package.Workbook.Worksheets.Count(); f++)
{
Excel.Worksheet xlSht = wb.Worksheets[f];
if (!accion)
{
xlSht.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
}
else
{
xlSht.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
}
}
}
}
private void createConceptList()
{
Excel.Worksheet origin = (Excel.Worksheet)application.ActiveSheet;
conceptList = (Excel.Worksheet)application.ActiveWorkbook.Worksheets.Add(Type.Missing, application.ActiveSheet, Type.Missing, Type.Missing);
conceptList.Name = "concept_list";
Excel.Range column = ((Excel.Range)conceptList.Cells[1, 1]);
//ID
column.Value2 = "ID";
column.Font.Bold = true;
column.Font.Background = Excel.XlBackground.xlBackgroundOpaque;
column.EntireColumn.ColumnWidth = 25;
column.EntireColumn.WrapText = true;
column.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
//Name
column.Next.Value2 = "Name";
column.Next.Font.Bold = true;
column.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque;
column.Next.EntireColumn.ColumnWidth = 30;
column.Next.EntireColumn.WrapText = true;
column.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
//Definition
column.Next.Next.Value2 = "Definition";
column.Next.Next.Font.Bold = true;
column.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque;
column.Next.Next.EntireColumn.ColumnWidth = 60;
column.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
column.Next.Next.EntireColumn.WrapText = true;
column.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify;
//Source
column.Next.Next.Next.Value2 = "Source";
column.Next.Next.Next.Font.Bold = true;
column.Next.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque;
column.Next.Next.Next.EntireColumn.ColumnWidth = 30;
column.Next.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
column.Next.Next.Next.EntireColumn.WrapText = true;
column.Next.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify;
//Definition
column.Next.Next.Next.Next.Value2 = "Mapped Cells";
column.Next.Next.Next.Next.Font.Bold = true;
column.Next.Next.Next.Next.Font.Background = Excel.XlBackground.xlBackgroundOpaque;
column.Next.Next.Next.Next.EntireColumn.ColumnWidth = 60;
column.Next.Next.Next.Next.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
column.Next.Next.Next.Next.EntireColumn.WrapText = true;
column.Next.Next.Next.Next.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignJustify;
conceptList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//((Excel.Worksheet)application.Worksheets.get_Item(origin.Name)).Activate();
(origin as Microsoft.Office.Interop.Excel._Worksheet).Activate();
}
protected void handleProp(XElement selectedNode)
{
string id = selectedNode.Element(rs + "names").Element(rs + "id").Value;
//Removing the institution identifying prefix from CADSR elements on client request
if (id.Contains("-CADSR-"))
{
string[] idarr = id.Split('-');
id = idarr[idarr.Length - 2] + " v." + idarr[idarr.Length - 1];
}
string preferredName = selectedNode.Element(rs + "names").Element(rs + "preferred").Value;
string definition = selectedNode.Element(rs + "definition").Value;
if (definition == null || definition.Length == 0)
{
definition = "(No definition supplied)";
}
else
{
//Handle special caDSR/EVS format
definition = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("<![CDATA[", "").Replace("]]>", "");
if (definition.Contains("<def-source>"))
{
XElement e = XElement.Parse("<def>" + definition + "</def>");
definition = e.Element("def-definition").Value + "\n(Source: " + e.Element("def-source").Value + ")";
}
}
string label = preferredName;
//Get selected range
Excel.Range selected = (Excel.Range)application.Selection;
if (selected.Value2 == null || selected.Value2.ToString().Length == 0)
{
selected.Value2 = label;
}
else
{
//Refuse to add? Do you have to remove first?
}
//Create concept list if not exists
propList = useList(propList, "prop_list");
//Add new concept entry to concept_list
propList.Unprotect(dummyPass);
Excel.Range c = (Excel.Range)propList.Cells[2, 1];
Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
if (found == null) //if (existingIndex == 0)
{
for (int i = 3; c.Value2 != null; i++)
{
c = (Excel.Range)ocList.Cells[i, 1];
}
c.Value2 = id;
c.Next.Value2 = preferredName;
c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&");
//c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&");
//Cells mapped counter
c.Next.Next.Next.Next.Value2 = 1;
}
else
{
found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString());
}
propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
/* Remove hyperlinks for now until a suitable two way link method is found */
selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, label);
selected.Font.Bold = true;
selected.Font.Underline = false;
selected.Font.ColorIndex = 1;
selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3;
selected.Interior.TintAndShade = 0.2;
}
/// <summary>
/// Export Attendance
/// </summary>
private void ExportAttendance()
{
try
{
Excel1.Application myExcelApp;
Excel1.Workbooks myExcelWorkbooks;
Excel1.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel1.Application();
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
string fileName = HttpContext.Current.Server.MapPath(".") + "\\Templates\\AttendanceExport.xlsx";
myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel1.Worksheet xlSheet = (Excel1.Worksheet)myExcelWorkbook.Sheets[1];
EmpBL objEmp = new EmpBL();
objEmp.Date = DateTime.ParseExact(txtDate.Text.Trim().ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
objEmp.WorkLocation = Session["Location"].ToString();
objEmp.ReportType = rblType.SelectedItem.Value;
DataTable dtEmp = objEmp.GetAttendanceLineByDate();
if (dtEmp.Rows.Count > 0)
{
WriteToExcelAttendance(dtEmp, xlSheet);
// ExportToPdf(dtEmp);
string Password = "******";
xlSheet.Protect(Password, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue);
Random rnd = new Random();
string filePath = Server.MapPath(".") + "\\Reports\\Attendance_" + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + "_" + rnd.Next() + ".xlsx";
ViewState["FileNameAtt"] = filePath;
myExcelWorkbook.SaveAs(@filePath);
myExcelWorkbook.Close();
myExcelWorkbooks.Close();
btnDownload.Visible = true;
lblMessage.Text = "Successfully Exported";
lblMessage.ForeColor = System.Drawing.Color.Green;
}
else
{
lblMessage.Text = "No Data Found";
}
}
catch (Exception ex)
{
lblMessage.Text = ex.ToString();
}
}
/// <summary>Función para obtener la contraseña de la hoja activa de Excel.
/// <para>Obtiene la contraseña de la hoja activa de Excel para desbloquearla. Referencia: <see cref="_Macro(bool, Excel.Worksheet, string)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para>
/// <seealso cref="_Macro(bool, Excel.Worksheet, string)"/>
/// </summary>
public static void _Macro(bool accion, Excel.Worksheet xlSht, string PwsExcel)
{
int a, b, c, d, e, f, a1, a2, a3, a4, a5, a6;
string _Contrasena = PwsExcel;
try
{
if (!accion)
{
xlSht.Unprotect(_Contrasena);
if (xlSht.ProtectContents == false)
{
//Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena);
//ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data);
return;
}
}
else
{
xlSht.Protect(_Contrasena, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
if (xlSht.ProtectContents == true)
{
return;
}
}
}
catch (Exception ex)
{
for (a = 65; a <= 66; a++)
{
for (b = 65; b <= 66; b++)
{
for (c = 65; c <= 66; c++)
{
for (d = 65; d <= 66; d++)
{
for (e = 65; e <= 66; e++)
{
for (a1 = 65; a1 <= 66; a1++)
{
for (a2 = 65; a2 <= 66; a2++)
{
for (a3 = 65; a3 <= 66; a3++)
{
for (a4 = 65; a4 <= 66; a4++)
{
for (a5 = 65; a5 <= 66; a5++)
{
for (a6 = 65; a6 <= 66; a6++)
{
for (f = 32; f <= 126; f++)
{
_Contrasena = "";
_Contrasena += Convert.ToChar(a);
_Contrasena += Convert.ToChar(b);
_Contrasena += Convert.ToChar(c);
_Contrasena += Convert.ToChar(d);
_Contrasena += Convert.ToChar(e);
_Contrasena += Convert.ToChar(a1);
_Contrasena += Convert.ToChar(a2);
_Contrasena += Convert.ToChar(a3);
_Contrasena += Convert.ToChar(a4);
_Contrasena += Convert.ToChar(a5);
_Contrasena += Convert.ToChar(a6);
_Contrasena += Convert.ToChar(f);
try
{
if (!accion)
{
xlSht.Unprotect(_Contrasena);
if (xlSht.ProtectContents == false)
{
Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena);
ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data);
//Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena);
//Configuration configServ = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//configServ.AppSettings.Settings["VAL7"].Value = Convert.ToBase64String(_data);
//configServ.Save(ConfigurationSaveMode.Modified);
return;
}
}
else
{
xlSht.Protect(_Contrasena, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
if (xlSht.ProtectContents == true)
{
Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena);
ConfigurationManager.AppSettings["VAL7"] = Convert.ToBase64String(_data);
//Byte[] _data = ASCIIEncoding.ASCII.GetBytes(_Contrasena);
//Configuration configServ = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//configServ.AppSettings.Settings["VAL7"].Value = Convert.ToBase64String(_data);
//configServ.Save(ConfigurationSaveMode.Modified);
return;
}
}
}
catch (Exception exc) { }
}
}
}
}
}
}
}
}
}
}
}
}
return;
}
}
protected void handleProp(QueryServiceControl.QueryServiceManager.property property)
{
string id = property.names.id;
//Removing the institution identifying prefix from CADSR elements on client request
if (id.Contains("-CADSR-"))
{
string[] idarr = id.Split('-');
id = idarr[idarr.Length - 2] + " v." + idarr[idarr.Length - 1];
}
string preferredName = property.names.preferred;
string definition = QueryServiceControl.QueryServiceControl.getDefinition(property.definition);
string label = preferredName;
//Get selected range
Excel.Range selected = (Excel.Range)application.Selection;
if (selected.Value2 == null || selected.Value2.ToString().Length == 0)
{
selected.Value2 = label;
}
else
{
//Refuse to add? Do you have to remove first?
}
//Create concept list if not exists
propList = useList(propList, "prop_list");
//Add new concept entry to concept_list
propList.Unprotect(dummyPass);
Excel.Range c = (Excel.Range)propList.Cells[2, 1];
Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
if (found == null) //if (existingIndex == 0)
{
for (int i = 3; c.Value2 != null; i++)
{
c = (Excel.Range)ocList.Cells[i, 1];
}
c.Value2 = id;
c.Next.Value2 = preferredName;
c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&");
//c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&");
//Cells mapped counter
c.Next.Next.Next.Next.Value2 = 1;
}
else
{
found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString());
}
propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
/* Remove hyperlinks for now until a suitable two way link method is found */
selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, label);
selected.Font.Bold = true;
selected.Font.Underline = false;
selected.Font.ColorIndex = 1;
selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3;
selected.Interior.TintAndShade = 0.2;
}
public void Update_Data()
{
if (!File.Exists(Report_File_Name))
{
return;
}
try
{
Read_Data(Report_File_Name);
int i = 0;
string kStr = "";
int indx = 0;
#region Update_ExcelData
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.ApplicationClass();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Excel_File_Name; // set this to your file you want
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["LL-OG1 & OG2"];
String cellFormulaAsString = myExcelWorksheet.get_Range("B12", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string.
//int cel_index = 30;
//int cel_index = 32;
int cel_index = 26;
int ci = (int)('B');
char c = (char)ci;
//Excel_User_Input_Data ex_uip = new Excel_User_Input_Data();
double L = MyList.StringToDouble(PSC_Girder_User_Inputs[0].Input_Value, 0.0);
double ang = MyList.StringToDouble(PSC_Girder_User_Inputs[5].Input_Value, 0.0);
PSC_Girder_User_Inputs[0].Input_Value = (L / Math.Cos(ang * Math.PI / 180.0)).ToString("f2");
#region Write Live Load Forces
List <int> lst_title_cell = new List <int>();
lst_title_cell.Add(8);
lst_title_cell.Add(26);
lst_title_cell.Add(46);
lst_title_cell.Add(65);
lst_title_cell.Add(84);
lst_title_cell.Add(103);
lst_title_cell.Add(122);
lst_title_cell.Add(141);
lst_title_cell.Add(160);
lst_title_cell.Add(179);
lst_title_cell.Add(198);
lst_title_cell.Add(217);
lst_title_cell.Add(236);
int r = 0, cl = 0;
//for (i = 12; i <= 274; i++)
//for (i = 8; i <= 252; i++)
for (i = 8; i <= 274; i++)
{
if (lst_title_cell.Contains(i) && this.Count > r)
{
myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).Formula = this[r].Title;
continue;
}
if ((i >= 8 && i <= 11) ||
(i >= 17 && i <= 19) ||
(i >= 25 && i <= 29) ||
(i >= 35 && i <= 37) ||
(i >= 43 && i <= 49) ||
(i >= 55 && i <= 57) ||
(i >= 63 && i <= 68) ||
(i >= 74 && i <= 76) ||
(i >= 82 && i <= 87) ||
(i >= 93 && i <= 95) ||
(i >= 101 && i <= 106) ||
(i >= 112 && i <= 114) ||
(i >= 120 && i <= 125) ||
(i >= 131 && i <= 133) ||
(i >= 139 && i <= 144) ||
(i >= 150 && i <= 152) ||
(i >= 158 && i <= 163) ||
(i >= 169 && i <= 171) ||
(i >= 177 && i <= 182) ||
(i >= 188 && i <= 190) ||
(i >= 196 && i <= 201) ||
(i >= 207 && i <= 209) ||
(i >= 215 && i <= 220) ||
(i >= 226 && i <= 228) ||
(i >= 234 && i <= 239) ||
(i >= 245 && i <= 247) ||
(i >= 253 && i <= 261) ||
(i >= 267 && i <= 269)
)
{
//myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = true;
continue;
}
else
{
try
{
if (i > 269)
{
r = this.Count - 1;
}
else if (i > 261)
{
r = this.Count - 2;
}
if (i > 269)
{
r = this.Count - 1;
}
myExcelWorksheet.get_Range(("B" + i.ToString()), misValue).Formula = this[r].Bending_Moments[cl].ToString();
myExcelWorksheet.get_Range(("F" + i.ToString()), misValue).Formula = this[r].Shear_Forces[cl++].ToString();
if (cl == 5)
{
r++;
cl = 0;
}
}
catch (Exception ex)
{
//myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = false;
//myExcelWorksheet.get_Range(("A" + i.ToString()), misValue).EntireRow.Hidden = true;
}
}
}
#endregion DL_Self_Weight
#region User Inputs
if (PSC_Girder_User_Inputs != null)
{
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Input"];
List <int> lst_F = new List <int>();
int cnt = 0;
for (i = 4; i <= 71; i++)
{
if ((i >= 5 && i <= 7) ||
i == 12 ||
i == 19 ||
i == 24 ||
(i >= 40 && i <= 41) ||
(i >= 47 && i <= 48) ||
(i >= 52 && i <= 53) ||
(i >= 61 && i <= 62) ||
i == 65 ||
i == 67 ||
i == 69)
{
continue;
}
lst_F.Add(i);
PSC_Girder_User_Inputs[cnt++].Excel_Cell_Reference = "G" + i;
}
Excel.Range ran = myExcelWorksheet.get_Range("G4:G71", misValue);
//myExcelWorksheet.Range["F4:F46"].Locked = false;
//if ((bool)ran.Locked)
//{
// MessageBox.Show("");
//}
myExcelWorksheet.Unprotect("2011ap");
ran.Locked = false;
for (i = 0; i < PSC_Girder_User_Inputs.Count; i++)
{
//myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value;
myExcelWorksheet.get_Range(PSC_Girder_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = PSC_Girder_User_Inputs[i].Input_Value;
}
ran.Locked = true;
myExcelWorksheet.Protect("2011ap");
}
#endregion User Inputs
#region Cable
if ((false))
{
if (PSC_Girder_User_Inputs != null)
{
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable"];
//22
//23
//24
//26
//27
//28
//31
//36
myExcelWorksheet.get_Range("F22", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable;
myExcelWorksheet.get_Range("F31", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable;
myExcelWorksheet.get_Range("F36", misValue).Formula = PSC_Girder_User_Inputs.Type_of_Cable;
myExcelWorksheet.get_Range("F23", misValue).Formula = PSC_Girder_User_Inputs.Strand_Area;
myExcelWorksheet.get_Range("F24", misValue).Formula = PSC_Girder_User_Inputs.UTS;
myExcelWorksheet.get_Range("F26", misValue).Formula = PSC_Girder_User_Inputs.Es;
myExcelWorksheet.get_Range("F27", misValue).Formula = PSC_Girder_User_Inputs.Permissible_Slip;
myExcelWorksheet.get_Range("F28", misValue).Formula = PSC_Girder_User_Inputs.Jacking_Distance;
}
}
#region Update Cable 1
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 1"];
for (i = 0; i < cab1_ref.Count; i++)
{
if (cab1_ref[i] != "")
{
myExcelWorksheet.get_Range(cab1_ref[i], misValue).Formula = DGV_Cable[2, i].Value.ToString().Replace("K", "");
}
//if (cab2_ref[i] != "")
//{
// myExcelWorksheet.get_Range(cab2_ref[i], misValue).Formula = DGV_Cable[3, i].Value;
//}
//if (cab3_ref[i] != "")
//{
// myExcelWorksheet.get_Range(cab3_ref[i], misValue).Formula = DGV_Cable[4, i].Value;
//}
//if (cab4_ref[i] != "")
//{
// myExcelWorksheet.get_Range(cab4_ref[i], misValue).Formula = DGV_Cable[5, i].Value;
//}
}
#endregion Update Cable 1
#region Update Cable 2
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 2"];
for (i = 0; i < cab2_ref.Count; i++)
{
if (cab2_ref[i] != "")
{
myExcelWorksheet.get_Range(cab2_ref[i], misValue).Formula = DGV_Cable[3, i].Value.ToString().Replace("K", "");;
}
}
#endregion Update Cable 1
#region Update Cable 3
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 3"];
for (i = 0; i < cab3_ref.Count; i++)
{
if (cab3_ref[i] != "")
{
myExcelWorksheet.get_Range(cab3_ref[i], misValue).Formula = DGV_Cable[4, i].Value.ToString().Replace("K", "");;
}
}
#endregion Update Cable 3
#region Update Cable 4
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Cable 4"];
for (i = 0; i < cab4_ref.Count; i++)
{
if (cab4_ref[i] != "")
{
myExcelWorksheet.get_Range(cab4_ref[i], misValue).Formula = DGV_Cable[5, i].Value.ToString().Replace("K", "");;
}
}
#endregion Update Cable 1
#endregion Cable
//PSC_Girder_User_Inputs
//Deckslab_User_Live_loads.Clear();
myExcelWorkbook.Save();
//myExcelWorkbook.Close(true, fileName, null);
Marshal.ReleaseComObject(myExcelWorkbook);
#endregion Update_ExcelData
}
catch (Exception ex) { }
}
private void fpSpread_addsheet()
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
try
{
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
fpSpread1.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls");
fc.SpreadRemoveEmptyCells(fpSpread1);
//保持格式
sh31.CellType(fpSpread1.Sheets[0]);
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
fpSpread1.Sheets.Clear();
Firstadddata();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
//保存excel文件
fpSpread1.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls");
//以下是打开文件设表格自动换行
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\PDWXZ" + year + ".xls";
workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
}
wait.Close();
}
/// <summary>
/// Funzione per il salvataggio delle modifiche apportate a ranges anche non contigui.
/// </summary>
/// <param name="Target">L'insieme dei ranges modificati</param>
/// <param name="annotaModifica">Se la modifica va segnalata all'utente attraverso il commento sulla cella oppure no.</param>
/// <param name="fromCalcolo">Flag per eseguire azioni particolari nel caso la provenienza del salvataggio sia da un calcolo.</param>
/// <param name="tableName">La tabella in cui inserire le modifiche. Di default Tab.Modifica. Utile specificarme una diversa nel caso di esportazione XML.</param>
public static void StoreEdit(Excel.Range Target, int annotaModifica = -1, bool fromCalcolo = false, string tableName = DataBase.TAB.MODIFICA)
{
if (Workbook.IdUtente != 0 && Workbook.CategorySheets.Contains(Target.Worksheet)) //non salva sulla tabella delle modifiche se l'utente non è configurato
{
Excel.Worksheet ws = Target.Worksheet;
bool wasProtected = ws.ProtectContents;
bool screenUpdating = ws.Application.ScreenUpdating;
if (wasProtected)
{
ws.Unprotect(Workbook.Password);
}
if (screenUpdating)
{
Workbook.ScreenUpdating = false;
}
DefinedNames definedNames = new DefinedNames(Target.Worksheet.Name, DefinedNames.InitType.SaveDB);
DataTable dt = Workbook.Repository[tableName];
if (ws.ChartObjects().Count > 0 && !fromCalcolo)
{
Sheet s = new Sheet(ws);
s.AggiornaGrafici();
}
string[] ranges = Target.Address.Split(',');
foreach (string range in ranges)
{
Range rng = new Range(range);
Range merged = null;
object mergedVal = null;
try
{ //controllo se c'è un merge nel range
merged = new Range(ws.Range[rng.ToString()].MergeArea.Address);
//salvo il valore
mergedVal = ws.Range[rng.ToString()].Value;
rng = merged;
}
catch { }
foreach (Range row in rng.Rows)
{
if (definedNames.SaveDB(row.StartRow))
{
bool annota = annotaModifica == -1 ? definedNames.ToNote(row.StartRow) : annotaModifica == 1;
foreach (Range column in row.Columns)
{
string[] parts = definedNames.GetNameByAddress(column.StartRow, column.StartColumn).Split(Simboli.UNION[0]);
string data;
if (parts.Length == 4)
{
data = Date.GetDataFromSuffisso(parts[2], parts[3]);
}
else
{
data = Date.GetDataFromSuffisso(parts[2], "");
}
if (!Workbook.Application.WorksheetFunction.IsErr(ws.Range[column.ToString()]))
{
DataRow r = dt.Rows.Find(new object[] { parts[0], parts[1], data });
if (r != null)
{
object val = ws.Range[column.ToString()].Value ?? "";
if (merged == null)
{
r["Valore"] = (val.Equals("-") ? "0" : val);
}
else
{
r["Valore"] = mergedVal ?? "";
}
}
else
{
DataRow newRow = dt.NewRow();
object val = ws.Range[column.ToString()].Value ?? "";
newRow["SiglaEntita"] = parts[0];
newRow["SiglaInformazione"] = parts[1];
newRow["Data"] = data;
if (merged == null)
{
newRow["Valore"] = (val.Equals("-") ? "0" : val);
}
else
{
newRow["Valore"] = mergedVal ?? "";
}
newRow["AnnotaModifica"] = annota ? "1" : "0";
newRow["IdApplicazione"] = Workbook.IdApplicazione;
newRow["IdUtente"] = Workbook.IdUtente;
dt.Rows.Add(newRow);
}
if (annota)
{
ws.Range[column.ToString()].ClearComments();
ws.Range[column.ToString()].AddComment("Valore inserito manualmente").Visible = false;
}
}
}
}
}
}
if (wasProtected)
{
ws.Protect(Workbook.Password);
}
if (screenUpdating)
{
ws.Application.ScreenUpdating = true;
}
}
}
public void Insert_Values_into_Excel_Long_Girder()
{
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.ApplicationClass();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Excel_File_Name; // set this to your file you want
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["8. Summ Force"];
String cellFormulaAsString = myExcelWorksheet.get_Range("A1", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string.
int cel_index = 7;
VDataCollection vdc = dr.DL_Self_Weight;
#region DL_Self_Weight
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion DL_Self_Weight
cel_index = 16;
vdc = dr.DL_Deck_Wet_Conc;
#region DL_Deck_Wet_Conc
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion DL_Deck_Wet_Conc
cel_index = 25;
vdc = dr.DL_Deck_Dry_Conc;
#region DL_Deck_Dry_Conc
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion DL_Deck_Dry_Conc
cel_index = 35;
vdc = dr.DL_Self_Deck;
#region DL_Self_Deck
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion DL_Self_Deck
cel_index = 48;
vdc = dr.SIDL_Crash_Barrier;
#region SIDL_Crash_Barrier
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion SIDL_Crash_Barrier
cel_index = 61;
vdc = dr.SIDL_Wearing;
#region SIDL_Wearing
for (int i = 0; i < vdc.Count; i++)
{
myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion SIDL_Wearing
cel_index = 75;
vdc = dr.LL_Moving;
#region LL_Moving
for (int i = 0; i < vdc.Count; i++)
{
if (vdc[i].LoadText != "")
{
myExcelWorksheet.get_Range("L" + (cel_index + i), misValue).Formula = vdc[i].LoadText;
}
myExcelWorksheet.get_Range("M" + (cel_index + i), misValue).Formula = vdc[i].Support / 10;
myExcelWorksheet.get_Range("N" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening / 10;
myExcelWorksheet.get_Range("O" + (cel_index + i), misValue).Formula = vdc[i]._L_8 / 10;
myExcelWorksheet.get_Range("P" + (cel_index + i), misValue).Formula = vdc[i]._L_4 / 10;
myExcelWorksheet.get_Range("Q" + (cel_index + i), misValue).Formula = vdc[i]._3L_8 / 10;
myExcelWorksheet.get_Range("R" + (cel_index + i), misValue).Formula = vdc[i].Mid / 10;
}
if ((cel_index + vdc.Count) <= 84)
{
//myExcelWorksheet.get_Range("A84" + (i), misValue).EntireRow.Hidden = true;
cel_index = cel_index + vdc.Count;
for (int i = cel_index; i <= 84; i++)
{
myExcelWorksheet.get_Range("A" + (i), misValue).EntireRow.Hidden = true;
myExcelWorksheet.get_Range("L" + (i), misValue).Formula = "'";
myExcelWorksheet.get_Range("M" + (i), misValue).Formula = 0.0;
myExcelWorksheet.get_Range("N" + (i), misValue).Formula = 0.0;
myExcelWorksheet.get_Range("O" + (i), misValue).Formula = 0.0;
myExcelWorksheet.get_Range("P" + (i), misValue).Formula = 0.0;
myExcelWorksheet.get_Range("Q" + (i), misValue).Formula = 0.0;
myExcelWorksheet.get_Range("R" + (i), misValue).Formula = 0.0;
}
}
#endregion LL_Moving
#region User Inputs
if (Long_User_Inputs != null)
{
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["1.Input"];
List <int> lst_F = new List <int>();
int cnt = 0;
int i = 0;
for (i = 5; i <= 71; i++)
{
if (i == 6 ||
i == 12 ||
i == 17 ||
i == 22 ||
i == 39 ||
i == 40 ||
i == 45 ||
i == 46 ||
i == 50 ||
i == 51 ||
i == 55 ||
i == 59 ||
i == 60 ||
i == 63 ||
i == 65)
{
continue;
}
lst_F.Add(i);
Long_User_Inputs[cnt++].Excel_Cell_Reference = "F" + i;
}
Excel.Range ran = myExcelWorksheet.get_Range("F5:F71", misValue);
//myExcelWorksheet.Range["F4:F46"].Locked = false;
//if ((bool)ran.Locked)
//{
// MessageBox.Show("");
//}
myExcelWorksheet.Unprotect("2011ap");
ran.Locked = false;
for (i = 0; i < Long_User_Inputs.Count; i++)
{
//myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value;
myExcelWorksheet.get_Range(Long_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = Long_User_Inputs[i].Input_Value;
}
ran.Locked = true;
myExcelWorksheet.Protect("2011ap");
}
#endregion User Inputs
myExcelWorkbook.Save();
//myExcelWorkbook.Close(true, fileName, null);
Marshal.ReleaseComObject(myExcelWorkbook);
}
private void xlsPut()
{
DialogResult ret;
int sCnt = 0;
int dCnt = 0;
//ダイアログボックスの初期設定
string pathName = string.Empty;
ret = folderBrowserDialog1.ShowDialog();
if (ret == System.Windows.Forms.DialogResult.OK)
{
pathName = folderBrowserDialog1.SelectedPath;
if (MessageBox.Show("出力先は以下のフォルダでよろしいですか？" + Environment.NewLine + Environment.NewLine + pathName, "確認", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
;
}
}
else
{
return;
}
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
// Excelテンプレートシート開く
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.xlsTempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing));
Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2];
try
{
// 会員情報を取得
JfgDataClassDataContext db = new JfgDataClassDataContext();
var s = db.会員情報.OrderBy(a => a.カード番号);
//オーナーフォームを無効にする
this.Enabled = false;
//プログレスバーを表示する
frmPrg frmP = new frmPrg(s.Count(), 0);
frmP.Owner = this;
frmP.Show();
// 会員情報を読み込む
foreach (var item in s)
{
//プログレスバー表示
dCnt++;
frmP.Text = "エクセル予定申告書作成中 " + dCnt.ToString() + "/" + s.Count().ToString();
frmP.progressValue = dCnt;
frmP.ProgressStep();
// 選択条件
if (chkGen.CheckState == CheckState.Checked && item.JFG会員歴 == 1 ||
chkTai.CheckState == CheckState.Checked && item.JFG会員歴 == 2 ||
chkKyu.CheckState == CheckState.Checked && item.JFG会員歴 == 3 ||
chkFumei.CheckState == CheckState.Checked && item.JFG会員歴 == 4 ||
chkHi.CheckState == CheckState.Checked && item.JFG会員歴 == 5)
{
// シートの保護を解除する
oxlsSheet.Unprotect();
// セルに値を渡す
oxlsSheet.Cells[2, 7] = item.氏名 + "さん";
oxlsSheet.Cells[2, 11] = item.カード番号.ToString();
oxlsSheet.Cells[5, 2] = DateTime.Today.Year.ToString();
oxlsSheet.Cells[5, 3] = DateTime.Today.Month.ToString();
oxlsSheet.Cells[5, 6] = DateTime.Today.AddMonths(1).Year.ToString();
oxlsSheet.Cells[5, 7] = DateTime.Today.AddMonths(1).Month.ToString();
oxlsSheet.Cells[5, 10] = DateTime.Today.AddMonths(2).Year.ToString();
oxlsSheet.Cells[5, 11] = DateTime.Today.AddMonths(2).Month.ToString();
oxlsSheet.Cells[5, 14] = DateTime.Today.AddMonths(3).Year.ToString();
oxlsSheet.Cells[5, 15] = DateTime.Today.AddMonths(3).Month.ToString();
oxlsSheet.Cells[5, 18] = DateTime.Today.AddMonths(4).Year.ToString();
oxlsSheet.Cells[5, 19] = DateTime.Today.AddMonths(4).Month.ToString();
oxlsSheet.Cells[5, 22] = DateTime.Today.AddMonths(5).Year.ToString();
oxlsSheet.Cells[5, 23] = DateTime.Today.AddMonths(5).Month.ToString();
// 申告欄クリア
for (int iRow = 8; iRow < 39; iRow++)
{
oxlsSheet.Cells[iRow, 3] = string.Empty;
oxlsSheet.Cells[iRow, 7] = string.Empty;
oxlsSheet.Cells[iRow, 11] = string.Empty;
oxlsSheet.Cells[iRow, 15] = string.Empty;
oxlsSheet.Cells[iRow, 19] = string.Empty;
oxlsSheet.Cells[iRow, 23] = string.Empty;
}
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// シートの保護
oxlsSheet.Protect(Type.Missing, false, true, false, false, false, false, false, false, false, false, false, false, false, false, false);
// シートの保存
string fileName = pathName + @"\" + item.カード番号.ToString() + " " + item.氏名 + ".xlsx";
oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// カウント
sCnt++;
}
}
// いったんオーナーをアクティブにする
this.Activate();
// 進行状況ダイアログを閉じる
frmP.Close();
// オーナーのフォームを有効に戻す
this.Enabled = true;
// 終了メッセージ
MessageBox.Show(sCnt.ToString() + "人の予定申告シートを出力しました", "予定申告シート出力完了", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エクセル予定申告シート出力", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
private void button2_Click(object sender, RibbonControlEventArgs e)
{
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
string AktifSayfa = activeWorksheet.Name;
if (IslemSayfasi == AktifSayfa) //Doğru sayfada olup olmadığı kontrol edilir.
{ //veriler sıfırlanır.
bool Hata_Yakalama = false;
EKOCT_TOPLAM = 0;
EKCT_TOPLAM = 0;
KCT_TOPLAM = 0;
KOCT_TOPLAM = 0;
NKCT_TOPLAM = 0;
ONCT_TOPLAM = 0;
CVPSZCT_TOPLAM = 0;
ARADCT_TOPLAM = 0;
//bütün soruları işaretlemeyen cevaplayıcılar tespit edilir.
Bos_Satirlar = new ArrayList();
int Bos_Satir_Sayisi = 0;
for (int i = 1; i <= Katilimci_Sayisi; i++)
{//Bütün boş satırların sayısını ve indis numaralarını bulur.
int sayac = 0;
for (int j = 1; j <= Madde_Sayisi; j++)
{
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki == "" || siradaki == " " || siradaki == "0")
{
sayac++;
}
}
if (sayac == Madde_Sayisi)
{
Bos_Satir_Sayisi++;
Bos_Satirlar.Add(i + 1);
}
}
Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range row;
int[] rows = new int[Bos_Satirlar.Count];
int SilinenSayac = 0;
foreach (var item in Bos_Satirlar)
{//Bütün soruları işaretlemeyen cevaplayıcılar silinir.
row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) - SilinenSayac];
row.Delete();
SilinenSayac++;
}
Katilimci_Sayisi = Katilimci_Sayisi - Bos_Satir_Sayisi;
Sonuclar = new double[Katilimci_Sayisi, 9];
for (int i = 1; i <= Katilimci_Sayisi; i++)
{
Hata_Yakalama = false;
ArrayList Katilimci_Cevaplari = new ArrayList();//Her katılımcı için liste oluşturulur.
for (int j = 1; j <= Madde_Sayisi; j++)
{
string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1));
if (siradaki == "" || siradaki == " ")
{
Katilimci_Cevaplari.Add(siradaki);
}
else
{
try
{//Doğru aralıkta veri girişi yapılmış ise katılımcının cevapları bir listeye eklenir.
//yanlış veri girişi yapılmış ise program hata mesajı verir.
if (Convert.ToInt32(siradaki) > Anket_Tipi || Convert.ToInt32(siradaki) < 0)
{
i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break;
}
else
{
Katilimci_Cevaplari.Add(siradaki);
}
}
catch (Exception ex) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; }
}
}
//formüllerin hesaplanması için ilgili metod çağırılır.
EKOCT_Metod(Katilimci_Cevaplari, i);
EKCT_Metod(Katilimci_Cevaplari, i);
ECT = EKOCT + EKCT;//Ekstrem Cevaplama Tarzı.
Sonuclar[(i - 1), 0] = ECT;
if (Anket_Tipi % 2 == 1)
{//Orta Noktası Bulunan Anket Türleri İçin.
KCT_Metod(Katilimci_Cevaplari, i);
KCT_TOPLAM += KCT;
KOCT_Metod(Katilimci_Cevaplari, i);
KOCT_TOPLAM += KOCT;
ONCT_Metod(Katilimci_Cevaplari, i);
ONCT_TOPLAM += ONCT;
}
NKCT = KCT - KOCT;
NKCT_TOPLAM += NKCT;
Sonuclar[(i - 1), 5] = NKCT;
CVPSZCT_Metod(Katilimci_Cevaplari, i);
CVPSZCT_TOPLAM += CVPSZCT;
ARADCT = 1 - ECT;
ARADCT_TOPLAM += ARADCT;
Sonuclar[(i - 1), 8] = ARADCT;
EKOCT_TOPLAM += EKOCT;
EKCT_TOPLAM += EKCT;
}
if (Hata_Yakalama == false)//bir hata ile karşılaşılmadı ise devam eder.
{
Excel.Worksheet ws1 = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range range = (Excel.Range)ws1.Range[ws1.Cells[2, Madde_Sayisi + 3], ws1.Cells[Katilimci_Sayisi + 1, Madde_Sayisi + 11]];
range.Value2 = Sonuclar;
//her formül için ortalama değerleri bulunur.
EKOCT_TOPLAM = EKOCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 5, EKOCT_TOPLAM.ToString("0.#####"));
EKCT_TOPLAM = EKCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 4, EKCT_TOPLAM.ToString("0.#####"));
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 3, (EKCT_TOPLAM + EKOCT_TOPLAM).ToString("0.#####"));
KCT_TOPLAM = KCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 6, KCT_TOPLAM.ToString("0.#####"));
KOCT_TOPLAM = KOCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 7, KOCT_TOPLAM.ToString("0.#####"));
NKCT_TOPLAM = NKCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 8, NKCT_TOPLAM.ToString("0.#####"));
ONCT_TOPLAM = ONCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 9, ONCT_TOPLAM.ToString("0.#####"));
CVPSZCT_TOPLAM = CVPSZCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 10, CVPSZCT_TOPLAM.ToString("0.#####"));
ARADCT_TOPLAM = ARADCT_TOPLAM / Katilimci_Sayisi;
Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 11, ARADCT_TOPLAM.ToString("0.#####"));
//Hücreleri kilitleme ve renk ayarı.
//kullanılmayan alanlar kullanıma kapatılır.
string sutun = harfler[Madde_Sayisi + 10].ToString() + (Katilimci_Sayisi + 2).ToString();
Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range fr;
fr = ws.get_Range("A1", sutun);
fr.Borders.Weight = 2;
fr.Borders.Color = Color.Black;
fr.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
Globals.ThisAddIn.Application.Cells.Locked = true;
Globals.ThisAddIn.Application.get_Range("A1", sutun).Locked = false;
Excel.Worksheet Sheet1 = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Sheet1.Protect();
}
}
else
{
MessageBox.Show("Hesaplama İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Handles insertion of common class elements into worksheet.
///
/// TODO: Refactor code
/// </summary>
/// <param name="selectedNode">Common Class element element to use</param>
protected void handleCommon(XElement selectedNode, string name, bool annotate)
{
string id = selectedNode.Element(rs + "names").Element(rs + "id").Value;
string fullId = selectedNode.Element(rs + "names").Element(rs + "id").Value;
//Removing the institution identifying prefix from CADSR elements on client request
if (id.Contains("-CADSR-"))
{
string[] idarr = id.Split('-');
id = idarr[idarr.Length - 2] + "v" + idarr[idarr.Length - 1];
}
string preferredName = selectedNode.Element(rs + "names").Element(rs + "preferred").Value;
string definition = selectedNode.Element(rs + "definition").Value;
if (definition == null || definition.Length == 0)
{
definition = "(No definition supplied)";
}
else
{
//Handle special caDSR/EVS format
definition = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("<![CDATA[", "").Replace("]]>", "");
if (definition.Contains("<def-source>"))
{
XElement e = XElement.Parse("<def>" + definition + "</def>");
definition = e.Element("def-definition").Value + "\n(Source: " + e.Element("def-source").Value + ")";
}
}
string attr = createAttrs(selectedNode, name);
string label = id + ":" + preferredName;
string[] concepts = null;
Excel.Range selected = (Excel.Range)application.Selection;
string hLinkLabel = "";
if (annotate && (!name.Equals("vd") && !name.Equals("dec") && !name.Equals("cd")))
{
concepts = extractConcepts(attr).Split(';');
Excel.Range leftCell = selected.get_Offset(0, -1);
string prim = concepts[concepts.Length - 1];
string quals = "";
for (int i = 0; i < concepts.Length - 1; i++)
quals += concepts[i] + ";";
if (quals.Length > 0)
quals = quals.Substring(0, quals.Length - 1);
if ((selected.Value2 == null || selected.Value2.ToString().Length == 0) && (((leftCell.Value2 == null || leftCell.Value2.ToString().Length == 0) || quals.Length == 0)))
{
selected.Value2 = prim;
hLinkLabel = prim;
if (quals.Length > 0)
leftCell.Value2 = quals;
}
}
else
{
//Get selected range
if (selected.Value2 == null || selected.Value2.ToString().Length == 0)
{
selected.Value2 = label;
hLinkLabel = label;
}
else
{
//Refuse to add? Do you have to remove first?
}
}
// empty hlink indicates non-empty cell. don't overwrite
if (hLinkLabel.Length > 0)
{
//Create concept list if not exists
propList = useList(propList, name + "_list");
//Add new concept entry to concept_list
propList.Unprotect(dummyPass);
Excel.Range c = (Excel.Range)propList.Cells[2, 1];
Excel.Range found = propList.Cells.Find(id, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
if (found == null) //if (existingIndex == 0)
{
for (int i = 3; c.Value2 != null; i++)
{
c = (Excel.Range)propList.Cells[i, 1];
}
c.Value2 = fullId;
c.Next.Value2 = preferredName;
c.Next.Next.Value2 = definition.Trim().Replace(">", ">").Replace("<", "<").Replace("&", "&");
c.Next.Next.Next.Value2 = attr.Trim().Replace(",", "\n\n").Replace(",", ", ").Replace(">", ">").Replace("<", "<").Replace("&", "&");
//Cells mapped counter
//c.Next.Next.Next.Next.Value2 = 1;
}
else
{
found.Next.Next.Next.Next.Value2 = 1 + Convert.ToInt16(found.Next.Next.Next.Next.Value2.ToString());
}
propList.Protect(dummyPass, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
/* Remove hyperlinks for now until a suitable two way link method is found */
//Do not overwrite if something is in there already
selected.Hyperlinks.Add(selected, "", getSelectedRangeAddress(c), Type.Missing, hLinkLabel);
selected.Font.Bold = true;
selected.Font.Underline = false;
if (annotate)
annotateComment(selected);
else
{
selected.Font.ColorIndex = 1;
selected.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3;
selected.Interior.TintAndShade = 0.2;
}
}
}
private void btnEliminarIndice_Click(object sender, RibbonControlEventArgs e)
{
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet sheetControl = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
string IndiceActivo = "";
string IndiceSiguiente = "";
string _NameFile = wb.Name;
bool Eliminar = false;
List <string> NombreRangos = new List <string>();
List <string> NombreRangosDEL = new List <string>();
List <int> FilaPadre = new List <int>();
int FilapadreAux = 0;
long dif = 0;
string NamedRange = "";
bool tienedif = false;
Excel.Range objRange = null;
Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection; // filas seleccionadas
try
{
foreach (Excel.Range cell in currentCell.Cells)
{
try
{
foreach (Excel.Name item1 in wb.Names)
{
// comparo la direccion de la celda con la del nombre del rango
if (item1.Name.Substring(0, 3) == "IA_")
{
if (item1.RefersToRange.Cells.get_Address() == cell.Address)
{
NamedRange = item1.Name;
break;
}
}
}
FilapadreAux = cell.Row;
if (!FilaPadre.Contains(FilapadreAux))
{
FilaPadre.Add(FilapadreAux);
}
objRange = (Excel.Range)sheet.Cells[cell.Row, 1];
IndiceActivo = objRange.Value2;
if (IndiceActivo.ToUpper().Trim() == "EXPLICACION")
{
MessageBox.Show("No es posible eliminar el índice EXPLICACION.", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Eliminar = false;
break;
}
if ((NamedRange != "IA_" + IndiceActivo) || (NamedRange == ""))
{
MessageBox.Show("No es posible eliminar un índice de formato guía", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Eliminar = false;
break;
}
else
{
Eliminar = true;
NombreRangosDEL.Add("IA_" + IndiceActivo);
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
}
if (Eliminar)
{
sheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection;
int CantRowDelete = currentCell.Cells.Rows.Count;
objRange = (Excel.Range)sheet.Cells[currentCell.Cells.Row + 1, 1];
IndiceSiguiente = objRange.Value2;
if (IndiceSiguiente != null)
{
if (IndiceSiguiente.ToUpper().Trim() == "EXPLICACION")
{
objRange.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
CantRowDelete += 1;
}
}
currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
NombreRangosDEL.Sort();
string NM = NombreRangosDEL.FirstOrDefault();
sheetControl.Controls.Remove(NM);
foreach (Excel.Name item2 in wb.Names)
{
if (item2.Name.Substring(0, 3) == "IA_")
{
NombreRangos.Add(item2.Name);
}
}
string[] split = NM.Split('_');
NM = split[1];
// foreach (string Nm in NombreRangosDEL)
long NamedRng = Convert.ToInt64(NM) + 100;
string IndiceSig = "0" + Convert.ToString(NamedRng);
string IndiceAnt = "";
while (NombreRangos.Contains("IA_" + IndiceSig))
{
sheetControl.Controls.Remove("IA_" + IndiceSig);
NamedRng = Convert.ToInt64(IndiceSig) + 100;
IndiceSig = "0" + Convert.ToString(NamedRng);
}
FilaPadre.Sort();
int row = FilaPadre.FirstOrDefault();
objRange = (Excel.Range)sheet.Cells[row, 1];
if (objRange.get_Value(Type.Missing) != null)
{
IndiceActivo = objRange.get_Value(Type.Missing).ToString();
}
objRange = (Excel.Range)sheet.Cells[row - 1, 1];
if (objRange.get_Value(Type.Missing) != null)
{
IndiceAnt = objRange.get_Value(Type.Missing).ToString();
}
//me salto la explciacion
if (IndiceAnt.Trim() == "EXPLICACION")
{
objRange = (Excel.Range)sheet.Cells[row - 2, 1];
if (objRange.get_Value(Type.Missing) != null)
{
IndiceAnt = objRange.get_Value(Type.Missing).ToString();
}
}
while (NombreRangos.Contains("IA_" + IndiceActivo))
{
tienedif = false;
dif = Convert.ToInt64(IndiceActivo) - Convert.ToInt64(IndiceAnt);
while (dif != 100)
{
IndiceAnt = "0" + Convert.ToString(Convert.ToInt64(IndiceActivo) - 100);
IndiceActivo = IndiceAnt;
dif = dif - 100;
tienedif = true;
}
objRange = (Excel.Range)sheet.Cells[row, 1];
objRange.Value2 = IndiceAnt;
if (tienedif)
{
Generales.AddNamedRange(row, 1, "IA_" + Convert.ToString(IndiceAnt));
}
//busco el siguiente activo
row++;
objRange = (Excel.Range)sheet.Cells[row, 1];
if (objRange.get_Value(Type.Missing) != null)
{
IndiceActivo = objRange.get_Value(Type.Missing).ToString();
}
else
{
break;
}
}
row = Generales.DameRangoPrincipal(FilaPadre.FirstOrDefault(), sheet);// busco el numero de fila OTRO para agregarle luego la sumatoria de los indices nuevos
Excel.Range objRangeJ = ((Excel.Range)sheet.Cells[FilaPadre[0], 1]);
objRangeJ.Select();
try
{ // limpio si hay error en la formula
Excel.Range objRangeI = ((Excel.Range)sheet.Cells[row, 1]); //.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Excel.XlSpecialCellsValue.xlErrors);//obten las celdas con errores
string NombreHoja = sheet.Name.ToUpper().Replace(" ", "");
List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja);
int _Registro = 1;
foreach (oSubtotal ST in ColumnasST)
{
objRangeI = sheet.get_Range(ST.Columna + row.ToString(), ST.Columna + row.ToString());
//objRangeI.Clear();
if (_Registro == 1)
{
_Registro += 1;
Generales.ActualizarReferencia(_NameFile, sheet.Name.ToUpper(), ST.Columna + row.ToString(), NombreRangos.Count, ST.Columna, row.ToString(), CantRowDelete, "E");
}
}
//wb.Save();
}
catch (Exception ex)
{
}
sheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
}
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
}
}
public void SyuYakuDL(DataGridView gr, DataTable ds)
{
string saveFileName = "集約_" + DateTime.Now.ToString("yyyyMMdd");
string templetFile = @"template\集約.xlsx";
SaveFileDialog saveDialog = new SaveFileDialog
{
Filter = "Excel文件 |*.xlsx",
FileName = saveFileName,
RestoreDirectory = true
};
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
File.Copy(templetFile, saveFileName, true);
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(saveFileName);
Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = "集約";
excel.Visible = false;
int excelRow = 0;
int number = 1;
int selectRow = 0;
try
{
log.Info("EXEC BEGIN");
//一行目値取得
//datagridview行数判断
for (int a = 0; a < gr.RowCount; a++)
{
//選択行
if ((bool)gr.Rows[a].Cells[0].EditedFormattedValue == true)
{
//DataSet行数判断
for (int i = 0; i < ds.Rows.Count; i++)
{
if (gr.Rows[a].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) &&
gr.Rows[a].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) &&
gr.Rows[a].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) &&
gr.Rows[a].Cells["SEQNO"].Value.Equals(ds.Rows[i][18]))
{
//NO行取得
worksheet.Cells[2, 1] = 1;
for (int r = 0; r < 19; r++)
{
worksheet.Cells[2, r + 2] = ds.Rows[i][r];
}
selectRow = a;
break;
}
else
{
continue;
}
}
break;
}
}
for (int b = selectRow + 1; b < gr.RowCount; b++)
{
if ((bool)gr.Rows[b].Cells[0].EditedFormattedValue == true)
{
for (int i = 0; i < ds.Rows.Count; i++)
{
if (gr.Rows[b].Cells["DENPYONO"].Value.Equals(ds.Rows[i][13]) &&
gr.Rows[b].Cells["SOKOCD"].Value.Equals(ds.Rows[i][19]) &&
gr.Rows[b].Cells["SYKFILENM"].Value.Equals(ds.Rows[i][17]) &&
gr.Rows[b].Cells["SEQNO"].Value.Equals(ds.Rows[i][18]))
{
Excel.Range RngToCopy = worksheet.get_Range("A2").EntireRow;
Excel.Range RngToInsert = worksheet.get_Range("A" + (number + 2)).EntireRow;
RngToInsert.Insert(Excel.XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy());
//NO行取得
number++;
worksheet.Cells[excelRow + 3, 1] = number;
for (int j = 0; j < 19; j++)
{
worksheet.Cells[excelRow + 3, j + 2] = ds.Rows[i][j];
}
excelRow++;
}
}
}
}
}
catch (Exception e)
{
log.Error("ERROR" + e.Message);
}
finally
{
Application.DoEvents();
var xml = XDocument.Load(@"..\Mitsui.xml");
var queryC = xml.Root.Descendants("rndflg")
.Elements("col")
.Select(a => a.Value);
List <string> cols = new List <string>();
foreach (var element in queryC)
{
cols = element.Split(',').ToList();
}
foreach (var b in cols)
{
worksheet.get_Range(b + ":" + b).Locked = false;
}
worksheet.Protect();
//workbook.Saved = true;
workbook.Save();
excel.Quit();
Marshal.FinalReleaseComObject(excel);
log.Info("EXEC END");
}
}
else
{
MessageBox.Show("処理を中止しました。");
}
}
public override void Range(object Sh, Excel.Range Target)
{
if (Workbook.CategorySheets.Contains(Target.Worksheet))
{
//Se la funzione scrive in altre celle, ricordarsi di disabilitare gli handler per la modifica delle celle
//Workbook.WB.SheetChange -= Handler.StoreEdit;
Workbook.RemoveStdStoreEdit();
Workbook.WB.SheetChange -= this.Range;
Excel.Worksheet ws = Target.Worksheet;
Excel.Worksheet wsMercato = Workbook.Sheets[Workbook.Mercato];
bool wasProtected = wsMercato.ProtectContents;
if (wasProtected)
{
wsMercato.Unprotect(Workbook.Password);
ws.Unprotect(Workbook.Password);
}
DefinedNames definedNames = new DefinedNames(ws.Name);
DefinedNames definedNamesMercato = new DefinedNames(Workbook.Mercato);
DataTable entita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA];
string[] ranges = Target.Address.Split(',');
foreach (string range in ranges)
{
Range rng = new Range(range);
foreach (Range cell in rng.Cells)
{
string[] parts = definedNames.GetNameByAddress(cell.StartRow, cell.StartColumn).Split(Simboli.UNION[0]);
string siglaEntita = parts[0];
string siglaInformazione = parts[1];
string suffissoData = parts[2];
string suffissoOra = parts[3];
var rif =
(from r in entita.AsEnumerable()
where r["IdApplicazione"].Equals(Workbook.IdApplicazione) && r["SiglaEntita"].Equals(siglaEntita)
select new { SiglaEntita = r["Gerarchia"] is DBNull ? r["SiglaEntita"] : r["Gerarchia"], Riferimento = r["Riferimento"] }).First();
string quarter = Regex.Match(siglaInformazione, @"Q\d").Value;
quarter = quarter == "" ? "Q1" : quarter;
Range rngMercato = new Range(definedNamesMercato.GetRowByName(rif.SiglaEntita, "UM", "T") + 2, definedNamesMercato.GetColFromName("RIF" + rif.Riferimento, "PROGRAMMA" + quarter));
rngMercato.StartRow += (Date.GetOraFromSuffissoOra(suffissoOra) - 1);
wsMercato.Range[rngMercato.ToString()].Value = ws.Range[cell.ToString()].Value;
ws.Range[cell.ToString()].Interior.ColorIndex = wsMercato.Range[rngMercato.ToString()].DisplayFormat.Interior.ColorIndex;
}
}
if (wasProtected)
{
wsMercato.Protect(Workbook.Password);
ws.Protect(Workbook.Password);
}
//Se la funzione scrive in altre celle, ricordarsi di riabilitare gli handler per la modifica delle celle
//Workbook.WB.SheetChange += Handler.StoreEdit;
Workbook.AddStdStoreEdit();
Workbook.WB.SheetChange += this.Range;
}
}
public void Read_Update_Data()
{
Clear();
if (!File.Exists(Report_File_Name))
{
return;
}
List <string> list = new List <string>(File.ReadAllLines(Report_File_Name));
MyList mlist = null;
int i = 0;
string kStr = "";
Deck_Data dc = null;
int indx = 0;
for (i = 0; i < list.Count; i++)
{
kStr = MyList.RemoveAllSpaces(list[i]);
if (kStr == "" || kStr.StartsWith("--------------"))
{
continue;
}
mlist = new MyList(kStr, ' ');
dc = new Deck_Data();
try
{
indx = mlist.Count - 1;
for (int j = 0; j < 15; j++)
{
dc.Add(mlist.GetDouble(indx--));
}
dc.Reverse();
this.Add(dc);
}
catch (Exception ex) { }
}
kStr = "";
#region Update_ExcelData
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.Application();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Excel_File_Name; // set this to your file you want
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(fileName, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["5.Design"];
String cellFormulaAsString = myExcelWorksheet.get_Range("A1", misValue).Formula.ToString(); // this puts the formula in Cell A2 or text depending whats in it in the string.
//int cel_index = 30;
//int cel_index = 32;
int cel_index = 26;
int ci = (int)('B');
char c = (char)ci;
#region DL_Self_Weight
for (i = 0; i < Count; i++)
{
ci = (int)('B');
for (int j = 0; j < (this[i].Count); j++)
{
c = (char)ci++;
myExcelWorksheet.get_Range(c.ToString() + (cel_index + i), misValue).Formula = this[i][j].ToString();
}
//myExcelWorksheet.get_Range("C" + (cel_index + i), misValue).Formula = vdc[i].Support;
//myExcelWorksheet.get_Range("D" + (cel_index + i), misValue).Formula = vdc[i].Web_Widening;
//myExcelWorksheet.get_Range("E" + (cel_index + i), misValue).Formula = vdc[i]._L_8;
//myExcelWorksheet.get_Range("F" + (cel_index + i), misValue).Formula = vdc[i]._L_4;
//myExcelWorksheet.get_Range("G" + (cel_index + i), misValue).Formula = vdc[i]._3L_8;
//myExcelWorksheet.get_Range("H" + (cel_index + i), misValue).Formula = vdc[i].Mid;
}
#endregion DL_Self_Weight
#region User Inputs
if (Deckslab_User_Inputs != null)
{
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["1.Input"];
List <int> lst_F = new List <int>();
int cnt = 0;
for (i = 4; i <= 49; i++)
{
if (i == 5 ||
i == 16 ||
i == 19 ||
i == 30 ||
i == 31 ||
i == 36 ||
i == 37 ||
(i > 43 && i < 49))
{
continue;
}
lst_F.Add(i);
Deckslab_User_Inputs[cnt++].Excel_Cell_Reference = "F" + i;
}
Excel.Range ran = myExcelWorksheet.get_Range("F4:F50", misValue);
//myExcelWorksheet.Range["F4:F46"].Locked = false;
//if ((bool)ran.Locked)
//{
// MessageBox.Show("");
//}
myExcelWorksheet.Unprotect("2011ap");
ran.Locked = false;
for (i = 0; i < Deckslab_User_Inputs.Count; i++)
{
//myExcelWorksheet.get_Range("F" + lst_F[i].ToString(), misValue).Formula = Deckslab_User_Inputs[i].Input_Value;
myExcelWorksheet.get_Range(Deckslab_User_Inputs[i].Excel_Cell_Reference, misValue).Formula = Deckslab_User_Inputs[i].Input_Value;
}
ran.Locked = true;
myExcelWorksheet.Protect("2011ap");
}
#endregion User Inputs
//Deckslab_User_Live_loads.Clear();
#region Live Loads
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["2.LiveLoad"];
double max_val = llc[0].Get_Maximum_Load();
double val = (max_val * 10 / 2.0);
List <string> lst_xls = new List <string>();
//lst_xls.Add(string.Format("57"));
lst_xls.Add(string.Format("L28"));
lst_xls.Add(string.Format("L29"));
lst_xls.Add(string.Format("F105"));
lst_xls.Add(string.Format("F106"));
lst_xls.Add(string.Format("M105"));
lst_xls.Add(string.Format("M106"));
lst_xls.Add(string.Format("G369"));
lst_xls.Add(string.Format("M369"));
lst_xls.Add(string.Format("G374"));
lst_xls.Add(string.Format("M374"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString();
}
lst_xls.Clear();
max_val = llc[3].Get_Maximum_Load();
val = (max_val * 10);
//val = (max_val * 10 / 2.0);
//lst_xls.Add(string.Format("100"));
lst_xls.Add(string.Format("G161"));
lst_xls.Add(string.Format("M161"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString();
}
//lst_xls.Add(string.Format(""));
//lst_xls.Add(string.Format(""));
//lst_xls.Add(string.Format(""));
//lst_xls.Add(string.Format(""));
lst_xls.Clear();
max_val = llc[4].Get_Maximum_Load();
val = (max_val * 10);
//val = (max_val * 10 / 2.0);
//lst_xls.Add(string.Format("50"));
lst_xls.Add(string.Format("G229"));
lst_xls.Add(string.Format("G230"));
lst_xls.Add(string.Format("M229"));
lst_xls.Add(string.Format("M230"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString();
}
//lst_xls.Add(string.Format(""));
lst_xls.Clear();
max_val = llc[1].Total_Loads / 2.0;;
val = (max_val * 10);
//lst_xls.Add(string.Format("35"));
lst_xls.Add(string.Format("G297"));
lst_xls.Add(string.Format("M297"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString();
}
//lst_xls.Add(string.Format(""));
//lst_xls.Add(string.Format(""));
lst_xls.Clear();
max_val = llc[2].Get_Maximum_Load();
val = (max_val * 10 / 2.0);
//lst_xls.Add(string.Format("85"));
lst_xls.Add(string.Format("G363"));
lst_xls.Add(string.Format("M363"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = val.ToString();
}
lst_xls.Clear();
//lst_xls.Add(string.Format("2.600"));
lst_xls.Add(string.Format("I12"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[0].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("0"));
lst_xls.Add(string.Format("B24"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[1].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("250"));
lst_xls.Add(string.Format("L30"));
lst_xls.Add(string.Format("G370"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[5].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("500"));
lst_xls.Add(string.Format("L31"));
lst_xls.Add(string.Format("M370"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[6].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1.200"));
lst_xls.Add(string.Format("L32"));
lst_xls.Add(string.Format("G371"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[7].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1.800"));
lst_xls.Add(string.Format("L33"));
lst_xls.Add(string.Format("M108"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[8].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("0.400"));
lst_xls.Add(string.Format("L34"));
lst_xls.Add(string.Format("G372"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[9].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("5.273"));
lst_xls.Add(string.Format("G166"));
lst_xls.Add(string.Format("G232"));
lst_xls.Add(string.Format("G364"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[2].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("810"));
lst_xls.Add(string.Format("G164"));
lst_xls.Add(string.Format("G365"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[10].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1.22"));
lst_xls.Add(string.Format("G165"));
lst_xls.Add(string.Format("G234"));
lst_xls.Add(string.Format("G366"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[3].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1.93"));
lst_xls.Add(string.Format("I166"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[11].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1.25"));
lst_xls.Add(string.Format("I167"));
lst_xls.Add(string.Format("G235"));
lst_xls.Add(string.Format("G300"));
lst_xls.Add(string.Format("G367"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[4].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("360"));
lst_xls.Add(string.Format("G233"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[12].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("840"));
lst_xls.Add(string.Format("G298"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[13].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("30"));
lst_xls.Add(string.Format("G299"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[14].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("4570"));
lst_xls.Add(string.Format("M298"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_User_Live_loads[15].Input_Value;
}
#endregion Live Loads
#region Design User Inputs
if (Deckslab_Design_Inputs != null)
{
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["5.Design"];
lst_xls.Clear();
//lst_xls.Add(string.Format("16"));
lst_xls.Add(string.Format("G87"));
lst_xls.Add(string.Format("E111"));
lst_xls.Add(string.Format("E115"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[0].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("1000"));
lst_xls.Add(string.Format("B92"));
lst_xls.Add(string.Format("N205"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[1].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("10"));
lst_xls.Add(string.Format("I111"));
lst_xls.Add(string.Format("I115"));
lst_xls.Add(string.Format("F134"));
lst_xls.Add(string.Format("F141"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[2].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("200"));
lst_xls.Add(string.Format("G111"));
lst_xls.Add(string.Format("G115"));
lst_xls.Add(string.Format("K111"));
lst_xls.Add(string.Format("K115"));
lst_xls.Add(string.Format("H134"));
lst_xls.Add(string.Format("H141"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[3].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("0.8"));
lst_xls.Add(string.Format("N188"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[4].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("0.5"));
lst_xls.Add(string.Format("N191"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[5].Input_Value;
}
lst_xls.Clear();
//lst_xls.Add(string.Format("0.5"));
lst_xls.Add(string.Format("N216"));
for (i = 0; i < lst_xls.Count; i++)
{
myExcelWorksheet.get_Range(lst_xls[i], misValue).Formula = Deckslab_Design_Inputs[6].Input_Value;
}
}
#endregion User Inputs
try
{
myExcelWorkbook.Save();
//myExcelWorkbook.Close(true, fileName, null);
Marshal.ReleaseComObject(myExcelWorkbook);
}
catch (Exception ex) { }
#endregion Update_ExcelData
}
