private void btn_cargar_Click(object sender, EventArgs e)
{
SLDocument sl = new SLDocument("C:/Users/alumno/Documents/archivoPlano.csv");
SLWorksheetStatistics propiedades = sl.GetWorksheetStatistics();
int ultimafila = propiedades.EndRowIndex;
}
///
/// REMOVE COURSES THAT DO NOT NEED MEETINGS CREATED FOR THEM
///
public void CompareExcelDocumentAndLookupTable()
{
List <Course> newCourses = new List <Course>();
SLWorksheetStatistics stats = _Excel_Document.GetWorksheetStatistics();
int end = stats.EndRowIndex;
if (_Excel_Document.GetCellValueAsString(1, 1).ToLower() != "course name" || _Excel_Document.GetCellValueAsString(1, 2).ToLower() != "number of meetings")
{
_valid = false;
return;
}
for (int i = 2; i <= end; i++)
{
string current_course = _Excel_Document.GetCellValueAsString(i, 1).Replace(" ", "_").ToUpper();
int current_number_of_meetings = _Excel_Document.GetCellValueAsInt32(i, 2);
Course c = new Course(current_course, new List <Section>(), current_number_of_meetings);
if (!newCourses.Contains(c) && current_course != "" && current_number_of_meetings != 0)
{
newCourses.Add(c);
}
}
_course_lookup.Clear();
foreach (Course course in newCourses)
{
_course_lookup.Add(course.GetName(), course);
}
}
public void validateClickstreams()
{
//Open Excel Clickstream *Report* File and extract list of clickstreamids
SLDocument repform = new SLDocument(clickstreamfile);
Dictionary <string, bool> reportclickstreams = new Dictionary <string, bool>(); //Bool indicates validated
SLWorksheetStatistics stats1 = repform.GetWorksheetStatistics();
for (int j = repstartrow; j <= stats1.EndRowIndex; j++)
{
string clickstream = repform.GetCellValueAsString(clickstreamcolumn + j); //Clickstream name
string clickstreamkey = repform.GetCellValueAsString(clickstreamkeycolumn + j); //Key Message Name
string presid = repform.GetCellValueAsString(clickstreamprescolumn + j); //Presentation ID
try { reportclickstreams.Add(presid + "#" + clickstreamkey + "@" + clickstream, false); } //Concatenated PresID, Key and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
//Query all presentations from metadatasheets for CLickstream information
ExtractClickStreamNames();
//Run comparison
CompareClickstreams(reportclickstreams);
}
public static string LeerXls(string full)
{
SLDocument documento = new SLDocument(full);
SLWorksheetStatistics propiedades = documento.GetWorksheetStatistics();
int ultimaColumna = propiedades.EndColumnIndex;
ArrayList cabeza = new ArrayList();
for (long i = 0; i < (ultimaColumna); i++)
{
string num = Letracolumna(i);
string ca = documento.GetCellValueAsString(num + 1);
cabeza.Add(ca);
}
List <string> re = new List <string>();
foreach (string sw in cabeza)
{
if (!re.Contains(sw))
{
re.Add(sw);
}
}
string cad = re[0];
for (int i = 1; i < (re.Count); i++)
{
cad = cad + "," + re[i];
}
Console.WriteLine(cad);
return(cad);
}
public void Load(string filePath)
{
if (!System.IO.File.Exists(filePath))
{
throw new System.IO.FileNotFoundException();
}
SLDocument document;
try
{
document = new SLDocument(filePath); //Открываем Excel документ
}
catch
{
throw new System.Exception("Невозможно открыть документ");
}
SLWorksheetStatistics stats = document.GetWorksheetStatistics(); //Статистика документа
int startColumnIndex = stats.StartColumnIndex; //Индекс первого столбца
int headerRowIndex = stats.StartRowIndex; //Индекс строки заголовков
int startRowIndex = headerRowIndex + 1; //Индекс первой строки данных
TableHeader = new List <string>();
//Получаем заголовки:
for (int i = startColumnIndex; i <= stats.EndColumnIndex; ++i)
{
TableHeader.Add(document.GetCellValueAsString(headerRowIndex, i));
}
this.Columns.Clear();
foreach (string h in TableHeader)
{
this.Columns.Add(new DataColumn(h)); //Заполняем DataTable заголовками столбцов
}
this.Rows.Clear();
//Добавление строк в DataTable
for (int rowIndex = startRowIndex; rowIndex <= stats.EndRowIndex; rowIndex++)
{
//Добавляем ячейки в словарь с ключами именами столбцов
string[] newRow = new string[stats.NumberOfColumns]; //Массив ячеек строки
for (int columnIndex = startColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
{
newRow[columnIndex - startColumnIndex] = document.GetCellValueAsString(rowIndex, columnIndex);
}
this.Rows.Add(newRow);
}
this.TableChanged();
}
protected virtual void SetupExcelDocument()
{
document.DocumentProperties.Creator = "Asp Mvc Download Replicator";
document.DocumentProperties.LastModifiedBy = "Asp Mvc Download Replicator";
document.DocumentProperties.ContentStatus = Status;
document.DocumentProperties.Title = DocumentName;
document.DocumentProperties.Description = String.IsNullOrEmpty(DocumentNo) ? DocumentName : String.Format("{0}-{1}", DocumentName, DocumentNo);
documentStat = document.GetWorksheetStatistics();
AdditionalSetupExcelDocument();
}
/// <summary>
/// demonstrate how to get used columns in the format a a letter rather than an integer
/// </summary>
/// <returns></returns>
public string[] UsedCellsInWorkSheet(string pFileName, string pSheetName)
{
using (var doc = new SLDocument(pFileName, pSheetName))
{
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
IEnumerable <string> columnNames = Enumerable.Range(1, stats.EndColumnIndex)
// ReSharper disable once ConvertClosureToMethodGroup
.Select((cellIndex) => SLConvert.ToColumnName(cellIndex));
return(columnNames.ToArray());
}
}
private static DataSet ReadExcelFileSLDocument(string filePath, string sheetName = null)
{
var ds = new DataSet();
DataTable dtStrongTyping = new DataTable("Sheet1");
using (SLDocument sl = sheetName == null ? new SLDocument(filePath) : new SLDocument(filePath, sheetName))
{
SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
int iStartColumnIndex = stats.StartColumnIndex;
//Get headerrow
for (var colIndex = stats.StartColumnIndex; colIndex <= stats.EndColumnIndex; colIndex++)
{
dtStrongTyping.Columns.Add(new DataColumn(sl.GetCellValueAsString(1, colIndex), typeof(string)));
}
for (var row = stats.StartRowIndex + 1; row <= stats.EndRowIndex; ++row)
{
DataRow dr = dtStrongTyping.NewRow();
for (var colIndex = stats.StartColumnIndex; colIndex <= stats.EndColumnIndex; colIndex++)
{
//var fieldType = GetDataType(sl.GetCellStyle(row, colIndex).FormatCode);
if (IsDateTimeType(sl.GetCellStyle(row, colIndex).FormatCode))
{
//DateTime date;
//DateTime.TryParse(sl.GetCellValueAsString(row, colIndex), out date);
try
{
var dateNumber = Convert.ToInt32(sl.GetCellValueAsString(row, colIndex));
dr[colIndex - stats.StartColumnIndex] = FromExcelSerialDate(dateNumber).ToString("dd-MMM-yyyy");
}
catch (FormatException)
{
dr[colIndex - stats.StartColumnIndex] = sl.GetCellValueAsString(row, colIndex);
}
}
else
{
dr[colIndex - stats.StartColumnIndex] = sl.GetCellValueAsString(row, colIndex);
}
}
dtStrongTyping.Rows.Add(dr);
}
}
ds.Tables.Add(dtStrongTyping);
return(ds);
}
public SLDocument LoadTextFile(SLDocument sldocWorksheet, string strTextFileFullPath)
{
sldocWorksheet.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Delimited");
SLTextImportOptions sltioWorksheetOptions = new SLTextImportOptions();
sltioWorksheetOptions.Culture = new CultureInfo("en-US");
SLWorksheetStatistics sldocstatsWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
int iLastColumnWithData = sldocstatsWorksheetStats.EndColumnIndex;
for (int iCounter = 0; iCounter < iLastColumnWithData; iCounter++)
{
sltioWorksheetOptions.SetColumnFormat(iCounter, SLTextImportColumnFormatValues.Text);
}
sldocWorksheet.ImportText(strTextFileFullPath, "A1", sltioWorksheetOptions);
return(sldocWorksheet);
}
///
/// RETURN A LIST OF INSTRUCTORS FROM A SPECIFIED EXCEL WORKBOOK
///
public static List <Instructor> GetInstructorListFromExcelSpreadsheet(string file)
{
if (!ValidExcelDocument(file))
{
return(null);
}
List <Instructor> listOfInstructors = new List <Instructor>();
_excelDocument = new SLDocument(file);
_stats = _excelDocument.GetWorksheetStatistics();
string columnOne = _excelDocument.GetCellValueAsString(1, 1).ToLower();
string columnTwo = _excelDocument.GetCellValueAsString(1, 2).ToLower();
string columnThree = _excelDocument.GetCellValueAsString(1, 3).ToLower();
if (columnOne != "first name" || columnTwo != "last name" || columnThree != "email")
{
LogInformation.AddLineToLogInformation("Error", "The first row of the spreadsheet must have the following columns: first name | last name | email");
return(null);
}
int end = _stats.EndRowIndex + 1;
for (int i = 2; i < end; i++)
{
string first = _excelDocument.GetCellValueAsString(i, 1);
string last = _excelDocument.GetCellValueAsString(i, 2);
string email = _excelDocument.GetCellValueAsString(i, 3);
if (first != "" && last != "" && email != "")
{
Instructor inst = new Instructor(first, last, email);
listOfInstructors.Add(inst);
}
else
{
LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing information and was skipped.");
}
}
return(listOfInstructors);
}
public void SaveWorksheetToTextFile(SLDocument sldocWorksheet, string strPath)
{
using (TextWriter textwriterModifiedDialogExport = new StreamWriter(strPath + "dialogExport - Rename Pairs.txt"))
{
List <string> strListOfRenamePairs = new List <string>();
SLWorksheetStatistics sldocWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
for (int iCounter = 2; iCounter < sldocWorksheetStats.EndRowIndex; iCounter++)
{
string strResponseText = sldocWorksheet.GetCellValueAsString("B" + iCounter.ToString());
string strFileName = sldocWorksheet.GetCellValueAsString("A" + iCounter.ToString());
string strFinalStringLine = strFileName + strResponseText;
strListOfRenamePairs.Add(strFinalStringLine);
}
foreach (String strLineFromList in strListOfRenamePairs)
{
textwriterModifiedDialogExport.WriteLine(strLineFromList);
}
}
}
public void ReadDimensions(string filename, string sheet, int rows)
{
using (SLDocument sl = new SLDocument(filename, sheet))
{
SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
int iStartColumnIndex = CollumnOffset;
for (int row = RowOffset; (row <= (RowOffset + rows)); ++row)
{
Dimensions.Add(new DimensionLine
{
Kote = sl.GetCellValueAsString(row, iStartColumnIndex),
DeltaPlus = sl.GetCellValueAsInt32(row, iStartColumnIndex + 7),
DeltaMinus = sl.GetCellValueAsInt32(row, iStartColumnIndex + 9),
Nazivno = sl.GetCellValueAsInt32(row, iStartColumnIndex + 1),
Mjereno = sl.GetCellValueAsInt32(row, iStartColumnIndex + 4),
// Delta = sl.GetCellValueAsInt32(row, iStartColumnIndex + 10)
});
}
}
}
public void LeerExcel(string fileLocation)
{
if (File.Exists(fileLocation))
{
SLDocument hoja = new SLDocument(fileLocation);
SLWorksheetStatistics statics = hoja.GetWorksheetStatistics();
List <Record> registros = new List <Record>();
Record reg = null;
for (int i = 2; i < statics.EndRowIndex + 1; i++)
{
if (string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 1)) &&
string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 2)) &&
string.IsNullOrEmpty(hoja.GetCellValueAsString(i, 3)))
{
break;
}
reg = new Record();
reg.Marca = hoja.GetCellValueAsString(i, 1);
reg.Fonema = hoja.GetCellValueAsString(i, 2);
reg.Nogaceta = hoja.GetCellValueAsInt32(i, 3);
reg.Fgaceta = hoja.GetCellValueAsDateTime(i, 4);
reg.Codigo_clase = hoja.GetCellValueAsInt32(i, 5);
reg.Fpresenta = hoja.GetCellValueAsDateTime(i, 6);
reg.Nopub = hoja.GetCellValueAsInt32(i, 7);
reg.Noexp = hoja.GetCellValueAsInt32(i, 8);
reg.Solicitant = hoja.GetCellValueAsString(i, 9);
reg.Codigo_pais = hoja.GetCellValueAsInt32(i, 10);
reg.Apoderado = hoja.GetCellValueAsString(i, 11);
reg.Tipo = hoja.GetCellValueAsString(i, 12);
reg.Fdigitacio = hoja.GetCellValueAsDateTime(i, 13);
registros.Add(reg);
}
InsertarRegistroEnBD(registros);
File.Delete(fileLocation);
}
}
protected void ButtonMySQL_Click(object sender, EventArgs e)
{
//leemos el archivo con sldocument
SLDocument sl = new SLDocument(@"C:\Users\13237\OneDrive\Desktop\crudDB.xlsx"); //ubicacion
SLWorksheetStatistics propiedades = sl.GetWorksheetStatistics(); //traer las propiedades del archivo
int ultimaFila = propiedades.EndRowIndex; //saber cuantas filas existen, trae la ultima fila
MySqlConnection conexionBD = ClsMySQL.conexion();
conexionBD.Open(); //se abre la conexion
for (int x = 2; x <= ultimaFila; x++) //leer todas las filas
{
//se trae el codigo y se le coloca un alias
string sql = "INSERT INTO tabla_alumnos(Correlativo, Nombre, Parcial1, Parcial2, Parcial3, Parcial4, Seccion) " +
"VALUES(@Correlativo, @Nombre, @Parcial1, @Parcial2, @Parcial3, @Parcial4, @Seccion)";
try
{
//transaccion a mysql
//con el objeto se agrega el alias, el valor que se le asigna, la columna
MySqlCommand comando = new MySqlCommand(sql, conexionBD);
comando.Parameters.AddWithValue("@Correlativo", sl.GetCellValueAsString("A" + x));
comando.Parameters.AddWithValue("@Nombre", sl.GetCellValueAsString("B" + x));
comando.Parameters.AddWithValue("@Parcial1", sl.GetCellValueAsString("C" + x));
comando.Parameters.AddWithValue("@Parcial2", sl.GetCellValueAsString("D" + x));
comando.Parameters.AddWithValue("@Parcial3", sl.GetCellValueAsString("E" + x));
comando.Parameters.AddWithValue("@Parcial4", sl.GetCellValueAsString("F" + x));
comando.Parameters.AddWithValue("@Seccion", sl.GetCellValueAsString("G" + x));
comando.ExecuteNonQuery(); //se ejecuta la insercion
}//si hay un error se utiliza mysqlexception
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}//si funciona aparecerá un messagebox
MessageBox.Show("El archivo se ha cargado");
}
/// <summary>
/// Based on ImportDataTable2, added background color to odd rows,
/// conditional formatting, in this case the State column.
/// </summary>
/// <param name="pFileName">Existing excel file</param>
/// <param name="pStartReference">Row to start import</param>
/// <param name="pDataTable">Data source</param>
/// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
/// <returns></returns>
public bool ImportDataTable4(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
{
bool success = false;
var sheetName = "Demo4";
try
{
// open to Sheet1 (will always exists in this code sample)
using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
{
var helper = new LightHelpers();
if (helper.SheetExists(pFileName, sheetName))
{
doc.DeleteWorksheet(sheetName);
}
// add the sheet
doc.AddWorksheet(sheetName);
var isSet = doc.SelectWorksheet(sheetName);
// next three lines are responsible for removal of grid-lines in the current WorkSheet
SLPageSettings ps = new SLPageSettings();
ps.ShowGridLines = false;
doc.SetPageSettings(ps);
// setup a format for the date column
var birthdayColumnStyle = doc.CreateStyle();
birthdayColumnStyle.FormatCode = "mm-dd-yyyy";
// create style for odd rows (we skip the header row)
var oddRowStyle = doc.CreateStyle();
oddRowStyle.SetPatternFill(PatternValues.LightGray, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color);
var moCellStyle = doc.CreateStyle();
moCellStyle.Font.Bold = true;
moCellStyle.Font.FontColor = SysDraw.Color.Red;
// since we need these several times, set them up as variables
var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1;
var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;
// set birthday style to our data
doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);
// do the Datatable import.
doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
// change format of the bool column
for (int row = 1; row < stats.EndRowIndex + 1; row++)
{
var value = doc.GetCellValueAsString(row, activeCellIndex);
if (value == "TRUE")
{
doc.SetCellValue(row, activeCellIndex, "Yes");
}
else if (value == "FALSE")
{
doc.SetCellValue(row, activeCellIndex, "No");
}
// apply background color style to odd rows
if (row.IsOdd() && row > 1)
{
doc.SetRowStyle(row, oddRowStyle);
}
else
{
var cellReference = SLConvert.ToCellReference(row, 6);
var stateValue = doc.GetCellValueAsString(cellReference);
if (stateValue == "MO")
{
doc.SetCellStyle(cellReference, moCellStyle);
}
}
}
// delete the primary key column
doc.DeleteColumn(1, 1);
// does what it says, auto-fit cell content
doc.AutoFitColumn(2, pDataTable.Columns.Count);
// de-select the current sheet and select sheet1
doc.SelectWorksheet("Sheet1");
doc.Save();
success = true;
}
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
success = false;
}
return(success);
}
///
/// RETURN A LIST OF COURSES FROM A SPECIFIED EXCEL WORKBOOK
///
public static List <Course> GetCourseListFromExcelSpreadsheet(string file)
{
if (!ValidExcelDocument(file))
{
LogInformation.AddLineToLogInformation("Error", "The file must be an Excel file with the extension .xlsx");
return(null);
}
_excelDocument = new SLDocument(file);
_stats = _excelDocument.GetWorksheetStatistics();
string columnOne = _excelDocument.GetCellValueAsString(1, 1).ToLower();
string columnTwo = _excelDocument.GetCellValueAsString(1, 2).ToLower();
string columnThree = _excelDocument.GetCellValueAsString(1, 3).ToLower();
if (columnOne != "course name" || columnTwo != "section" || columnThree != "number of meetings")
{
LogInformation.AddLineToLogInformation("Error", "The first row of the spreadsheet must have the following columns: course name | section | number of meetings");
return(null);
}
List <Course> listOfCourses = new List <Course>();
int end = _stats.EndRowIndex + 1;
for (int i = 2; i < end; i++)
{
bool valid = true;
string name = _excelDocument.GetCellValueAsString(i, 1).ToUpper();
string section = _excelDocument.GetCellValueAsString(i, 2);
int numberOfMeetings = _excelDocument.GetCellValueAsInt32(i, 3);
if (name == "")
{
LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing the name of the course and was skipped.");
valid = false;
}
if (section == "")
{
LogInformation.AddLineToLogInformation("Error", "Row " + i + " is missing the section and was skipped.");
valid = false;
}
if (valid)
{
List <Section> sections = new List <Section>();
Section s = new Section(section, numberOfMeetings);
sections.Add(s);
Course c = new Course(name, sections, numberOfMeetings);
if (listOfCourses.Contains(c))
{
int idx = listOfCourses.IndexOf(c);
listOfCourses[idx].AddSections(sections);
}
else
{
listOfCourses.Add(c);
}
}
}
return(listOfCourses);
}
public List <DocType> ImportEXCEL(string fname, int ntime, int etime)
{
if (!System.IO.File.Exists(fname))
{
return(new List <DocType>());
}
;
try
{
SLDocument sl = new SLDocument(fname, "工作表1");
SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
if (stats.EndRowIndex <= 0)
{
return(new List <DocType>());
}
List <DocType> ndocs = new List <DocType>();
for (int i = 0; i < stats.EndRowIndex; i++)
{
if (string.IsNullOrEmpty(sl.GetCellValueAsString(i + 2, 2)))
{
break;
}
DocType docs = new DocType
{
//Index = sl.GetCellValueAsString(i + 2, 1),
ID = sl.GetCellValueAsString(i + 2, 2).Trim(),
Color = sl.GetCellStyle(i + 2, 2).Font.FontColor.ToString(),
webID = sl.GetCellValueAsString(i + 2, 13).Trim(),
Name = sl.GetCellValueAsString(i + 2, 4).Trim(),
Version = sl.GetCellValueAsString(i + 2, 5).Trim(),
Depart = sl.GetCellValueAsString(i + 2, 6).Trim(),
doctp = sl.GetCellValueAsString(i + 2, 7).Trim(),
Stime = sl.GetCellValueAsDateTime(i + 2, 8),
Rtime = sl.GetCellValueAsDateTime(i + 2, 9),
Own = sl.GetCellValueAsString(i + 2, 11).Trim()
};
docs.Ntime = docs.Rtime.AddYears(ntime);
docs.Etime = docs.Rtime.AddYears(etime);
if (DateTime.TryParseExact(sl.GetCellValueAsString(i + 2, 8), "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime _date))
{
docs.Stime = _date;
}
if (DateTime.TryParseExact(sl.GetCellValueAsString(i + 2, 9), "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out _date))
{
docs.Rtime = _date;
}
if (sl.GetCellStyle(i + 2, 2).Font.FontColor == System.Drawing.Color.FromArgb(255, 255, 0, 0))
{
docs.Eng = true;
}
if (docs.webID.Contains("documentId"))
{
var m = Regex.Match(docs.webID, @"documentId=(\d+)");
if (m.Success)
{
docs.webID = m.Groups[1].ToString();
}
}
else if (Int64.TryParse(sl.GetCellValueAsString(i + 2, 3).Trim(), out long id2))
{
docs.webID = id2.ToString();
}
else
{
docs.webID = "-1";
}
/*if (Int64.TryParse(sl.GetCellValueAsString(i + 2, 1).Trim(), out long id))
* {
* docs.Index = id.ToString();
* }
* else
* docs.Index = "-1";*/
if (Double.TryParse(sl.GetCellValueAsString(i + 2, 5).Trim(), out double id3))
{
docs.Version = id3.ToString();
}
else
{
docs.Version = "-1";
}
ndocs.Add(docs);
}
sl.CloseWithoutSaving();
return(ndocs);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(new List <DocType>());
}
/*
* odocs = ADocs.GroupBy(o => o.Depart).ToDictionary(o => o.Key, o => o.ToList());
* try
* {
* foreach (var x in odocs)
* {
* int i = 0;
* SLDocument sl = new SLDocument();
* foreach (var y in x.Value)
* {
* sl.SetCellValue(i + 1, 1, y.ID);
* sl.SetCellValue(i + 1, 2, y.webID);
* sl.SetCellValue(i + 1, 3, y.Name);
* sl.SetCellValue(i + 1, 4, y.Version);
* sl.SetCellValue(i + 1, 5, y.Depart);
* sl.SetCellValue(i + 1, 6, y.Own);
* i++;
* }
* string fpath = Environment.CurrentDirectory + @"\部門";
* if (!System.IO.Directory.Exists(fpath))
* {
* System.IO.Directory.CreateDirectory(fpath);
* }
* sl.SaveAs(fpath + @"\" + x.Key + ".xlsx");
* }
* }
* catch (Exception ex)
* {
* MessageBox.Show(ex.Message);
* }
*/
}
//TODO refactor with Validate Clickstreams for the report extraction bit
public void validatePresentations()
{
Dictionary <string, string> presreport = new Dictionary <string, string>(); //Reported Presenation IDs
Dictionary <string, bool> pubformpres = new Dictionary <string, bool>(); //Publishing Form Presenation IDs - bool indicates if validated or not
SortedDictionary <string, string> missingpubs = new SortedDictionary <string, string>();
//Grab Presentation IDs from each publishing form using presentation report
//List publising forms
listFileNames(pubfolder); //Populate list of Publishing Form spreadsheets
foreach (var f in pubforms)
{
SLDocument pubform = openPubForm(pubfolder + "\\" + f.Value);
string curSheet = pubform.GetCurrentWorksheetName();
pubform.SelectWorksheet(PresTab);
string PresID = pubform.GetCellValueAsString(PresIDCell);
try { pubformpres.Add(PresID, false); } //Default to not validated
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
//Then verify against the Presentation IDs in Veeva (and those coded into "GotoSlide" in the pages? No, rely on manual checks for that for now)
//Use publishing report
//Open Excel Clickstream Report File and extract list of clickstreamids
SLDocument repform = new SLDocument(presrepfile);
SLWorksheetStatistics stats1 = repform.GetWorksheetStatistics();
for (int j = repstartrow; j <= stats1.EndRowIndex; j++)
{
string Pres = repform.GetCellValueAsString(prescolumn + j); //Presentation ID
try { presreport.Add(Pres, Pres); }
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
//Now Compare
HashSet <string> lines = new HashSet <string>();
lines.Add("Presentation IDs Missing from Publishing Forms :");
string[] headermessage = new string[] { "Presentations found in Publising Form but not found in Veeva" };
//Validate names in publishing form match folders
//Don't use a clever (big O) comparison as may not be a perfect match
foreach (var v in presreport)
{
if (pubformpres.ContainsKey(v.Key))
{
pubformpres[v.Key] = true; //Flag as validated
}
else
{
//Presentation from spreadsheets has no corresponding entry in Veeva
missingpubs.Add(v.Key, v.Value);
}
}
//Identify any pubforms that are still invalid (i.e. no presenation in veeva matches)
if (pubformpres.ContainsValue(false))
{
var matches = pubformpres.Where(pair => pair.Value != true).Select(pair => pair.Key);
Console.Write(matches.ToString());
System.IO.File.WriteAllLines(folderPath + "\\PresentationValidation" + logfile, headermessage);
System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, matches.ToArray <string>());
}
//Also need to identify if any veeva presentations lack a corresponding pub form
foreach (var v in missingpubs)
{
Console.WriteLine(v.Key);
lines.Add(v.Key);
}
System.IO.File.AppendAllLines(folderPath + "\\PresentationValidation" + logfile, lines.ToArray <string>());
//For completeness, probably worth writing out the "true", i.e. validated, publishing form names, so can see that all the lists tie up together.
}
public SLDocument SanitizeInvalidDialogCharacters(SLDocument sldocWorksheet)
{
SLWorksheetStatistics sldocWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
int iNumberOfCells = sldocWorksheetStats.EndRowIndex;
int iEmptyStringCounter = 0000;
for (int iCounter = 2; iCounter <= iNumberOfCells; iCounter++)
{
//Begin working on Column A - RESPONSE_TEXT
string strCurrentCell = sldocWorksheet.GetCellValueAsString("A" + iCounter.ToString());
string strModifiedCurrentCell;
if (strCurrentCell.Length > iMaxCharacters)
{
strModifiedCurrentCell = strCurrentCell.Remove(iMaxCharacters - 1);
}
else
{
strModifiedCurrentCell = strCurrentCell;
}
if (strModifiedCurrentCell.Length > 0)
{
for (int iCharArrayCounter = 0; iCharArrayCounter < chararrayInvalidCharacters.Length; iCharArrayCounter++)
{
strModifiedCurrentCell = strModifiedCurrentCell.Replace(chararrayInvalidCharacters[iCharArrayCounter], chararrayValidCharacters[iCharArrayCounter]);
}
strModifiedCurrentCell = "|" + strModifiedCurrentCell;
strModifiedCurrentCell = strModifiedCurrentCell + ".fuz";
strModifiedCurrentCell = RemoveRedundantPeriods(strModifiedCurrentCell);
sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
//End
}
else
{
iEmptyStringCounter++;
strModifiedCurrentCell = "|[Empty_" + iEmptyStringCounter.ToString("000000") + "].fuz";
sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
}
if (strModifiedCurrentCell == "|.fuz")
{
iEmptyStringCounter++;
strModifiedCurrentCell = "|[Empty_" + iEmptyStringCounter.ToString("000000") + "].fuz";
sldocWorksheet.SetCellValue("A" + iCounter.ToString(), strModifiedCurrentCell);
}
//Begin working on Column B - FILENAME
//We just want to append the dialog file extension to the cell's string.
strCurrentCell = sldocWorksheet.GetCellValueAsString("B" + iCounter.ToString());
strModifiedCurrentCell = strCurrentCell + ".fuz";
sldocWorksheet.SetCellValue("B" + iCounter.ToString(), strModifiedCurrentCell);
//End
}
return(sldocWorksheet);
}
//TODO refactor as similar code to above
//Extract Clickstream details from publishing form
private void ExtractClickstream(string filename, string thisFolder)
{
caseErrors = new HashSet <string>();
unabletoreadErrors = new HashSet <string>();
//Extract clickstreams from publishing form
SLDocument pubform = openPubForm(thisFolder + "\\" + filename);
var worksheetnames = pubform.GetWorksheetNames();
caseErrors.Add("Case Sensitive Errors :");
pubform.SelectWorksheet(MetaPresTab);
string PresID = pubform.GetCellValueAsString(MetaPresIDCell);
unabletoreadErrors.Add(PresID);
SLWorksheetStatistics stats1 = pubform.GetWorksheetStatistics();
Dictionary <string, string> keymessagenumberstonames = new Dictionary <string, string>();
Dictionary <string, string> caseinsensitivekeymessagenumberstonames = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
//Get Key Message Number to Name Mapping - no need to store Presentation id as disposable lookup dictionary on a per spreadsheet basis
for (int j = metakeymessagestartrow; j <= stats1.EndRowIndex; j++)
{
string kmnumber = pubform.GetCellValueAsString(Metadatakeynumbercolumn + j);
kmnumber = String.Format("{0:0.00}", kmnumber);
string kmname = pubform.GetCellValueAsString(Metadatakeynamecolumn + j);
string kmzip = kmnumber + "_" + pubform.GetCellValueAsString(MetadataKeyMessageCol + j) + ".zip";
try { keymessagenumberstonames.Add(kmnumber + "_" + kmname, kmzip); } catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
try { caseinsensitivekeymessagenumberstonames.Add(kmnumber + "_" + kmname, kmzip); } catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
pubform.SelectWorksheet(ClickTab);
stats1 = pubform.GetWorksheetStatistics();
for (int j = clickstreamstartrow; j < stats1.EndRowIndex; j++)
{
//Need to resolve the decimal issue (e.g. Pub Form has stored just 61 formatted to 61.00 while Clickstream Form has 61.00 stored actually as 61.00
string clickstream = pubform.GetCellValueAsString(pubclickstreamcolumn + j); //Clickstream name
string clickstreamkey = pubform.GetCellValueAsString(pubclickstreamkeycolumn + j); //Key Message Number
/* clickstreamkey = String.Format("{0:0.00}", clickstreamkey);
* //Quick bodge to make match new metadata sheets
* //string clickstreamname = clickstream + ".zip";
*
* //Deal with the ones where James has put the name in as well as the number. Clunky Christmas Eve coding.
*
* int clickstreamkeyint;
* if (int.TryParse(clickstreamkey, out clickstreamkeyint))
* {
* //Already an integer
* clickstreamkey = clickstreamkeyint.ToString();
* }
* else
* {
* int endindex = clickstreamkey.IndexOf(" ");
* if (endindex > 0) {
* clickstreamkey = clickstreamkey.Substring(0, endindex);
* }
* }
*
* string clickstreamname = "";
* bool hasValue = keymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname); //Lookup Key Message Number using dictionary generated earlier
*
* if (!hasValue) { } //TODO: Handle the lack of a lookup?
*/
string clickstreamname = "";
if (clickstream == "")
{
unabletoreadErrors.Add(clickstreamkey);
}
else
{
bool hasValue = keymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname); //Lookup Key Message Number using dictionary generated earlier
if (clickstream != "" && clickstreamname != "")
{
try { clickstreams.Add(PresID + "#" + clickstreamname + "@" + clickstream, clickstream); } //Concatenated PresID, Key Name and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
else
{
hasValue = caseinsensitivekeymessagenumberstonames.TryGetValue(clickstreamkey, out clickstreamname);
caseErrors.Add(clickstreamkey);
if (clickstream != "" && clickstreamname != "")
{
try { clickstreams.Add(PresID + "#" + clickstreamname + "@" + clickstream, clickstream); } //Concatenated PresID, Key Name and Clicksteam so can still just use string dictionaries in case can refactor later to be same as key message
catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} } //Allow for duplicates
}
}
}
}
System.IO.File.AppendAllLines(folderPath + "\\unabletoreadErrors.txt", unabletoreadErrors.ToArray <string>());
}
public void UpdateCoordinatesSingle()
{
//string current = GetActiveWindowsTXT();
//Form currForm = Form.ActiveForm;
//Control.ControlCollection coll = currForm.Controls;
//PPLMain pMain = currForm.ActiveControl as PPLMain;
//foreach (Control ctl in coll)
//{
//}
//pMain.Focus();
//FormCollection frmColl = Application.OpenForms;
foreach (Form f in Application.OpenForms)
{
//if (f.Name.Contains("PGE"))
//{
// PPLMain main = f.ActiveControl as PPLMain;
//}
}
//PPLMain main = WeifenLuo.WinFormsUI.ThemeVS2013.VS2013DockPane as PPLMain;
bool enabled = false;
ToolStripMenuItem proposed = GetSaveAsProposedDesignButton();
ToolStripMenuItem overrideFAA = GetFAAButton();
enabled = overrideFAA.Enabled;
if (enabled)
{
PPLPole pole = cPPLMain.GetPole();
string sapPM = pole.GetValueString("Aux Data 1");
string poleLoc = pole.GetValueString("Aux Data 3");
string slLoc = poleLoc.Substring(poleLoc.LastIndexOf('_') + 1);
string poleLat = string.Empty;
string poleLong = string.Empty;
string pdFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + sapPM + " Planning Data For PGE Computer.xlsx";
string treeAttachSheet = "Existing Pole Data";
SLDocument planningData = new SLDocument(pdFilePath, treeAttachSheet);
SLWorksheetStatistics wsStats = planningData.GetWorksheetStatistics();
int numRows = wsStats.NumberOfRows;
int numCols = wsStats.NumberOfColumns;
bool found = false; for (int i = 2; i <= numRows; i++)
{
if (planningData.GetCellValueAsString(i, 2).Equals(slLoc))
{
poleLat = planningData.GetCellValueAsString(i, 17);
poleLong = planningData.GetCellValueAsString(i, 18);
found = true;
PPLMessageBox.Show("Pole found");
}
}
if (!found)
{
PPLMessageBox.Show("Pole not found");
}
planningData.CloseWithoutSaving();
}
else
{
PPLMessageBox.Show("Override Coordinates for FAA not enabled for this pole.");
}
}
//TODO compare common code to ExtractClickstream and refactor
private void ExtractKeyMessage(string filename, string thisFolder, bool current = true)
{
//Extract key messages from publishing form
string PresID = "";
SortedDictionary <int, string> thisPresKeyMessages = new SortedDictionary <int, string>();
SLDocument pubform = new SLDocument(thisFolder + "\\" + filename);
string curSheet = pubform.GetCurrentWorksheetName();
if (curSheet != "")
{
pubform.SelectWorksheet(PresTab);
}
SLWorksheetStatistics stats1 = pubform.GetWorksheetStatistics();
//Get Presentation ID
PresID = pubform.GetCellValueAsString(PresIDCell);
//Vulnerability on EndRowIndex (AbbVie Care and Safety Profile for RA)//Think I fixed it - need to ensure worksheet is selected before making the Statistics call
for (int j = keymessagestartrow; j <= stats1.EndRowIndex; j++)
{
string kmzip = pubform.GetCellValueAsString(KeyMessageCol + j);
int DisplayOrder = pubform.GetCellValueAsInt32(pubdisplaynumbercolumn + j);
if (kmzip.Contains(".zip"))
{
try
{
if (current)
{
thisPresKeyMessages.Add(DisplayOrder, kmzip.Replace(".zip", "")); //Doing it this way round means that where a slide is duplicated we still get it in each presentation for the tree view
keymessages.Add(kmzip.Replace(".zip", ""), PresID); //Temp To Lower
}
else
{
oldkeymessages.Add(kmzip.Replace(".zip", ""), PresID);
} //Temp To Lower
}
catch (Exception e)
{ if (e.HResult == -2147024809)
{
//Ignore as duplicate keys can occur if same key message in more than one presentation
}
else
{
throw e;
} }
}
else
{//May be shared message row
try {
var skm = pubform.GetCellValueAsString(pubsharedcolumn + j);
if (skm.Contains(".zip"))
{
sharedkeymessages.Add(PresID, skm); //We can do this by Presentation rather than by key message
}
else
{//Trying to cater for other eventualities
if (pubform.GetCellValueAsString(pubsharedcolumn + (j - 1)).Contains("Shared Resources\nFile Name"))
{
sharedkeymessages.Add(PresID, skm);
}
}
}catch (Exception e) { if (e.HResult == -2147024809)
{
}
else
{
throw e;
} }
}
}
//Resort thisPresKeymessages into display order
thisPresKeyMessages.OrderBy(key => key.Key);
try
{
orderedKeymessages.Add(PresID, thisPresKeyMessages);
}
catch (Exception e)
{
if (e.HResult == -2147024809)
{
}
else
{
throw e;
}
}
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public void TreeBuilder()
{
/////////////Catalog Guids and other constants//////////////////
const string spoolGuid = "21670682-ab38-484c-bed8-a212650ee25a";
const string dummyLoadGuid = "85a21fd2-86ad-4744-b1eb-fc6fb232596b";
const double pi = Math.PI;
/////////////End Catalog Guids and other constants//////////////
PPLPole pole = cPPLMain.GetPole();
List <TreeNode> treeNodeList = cPPLMain.cCatalogManager.GetNodes(PPLCatalogForm.CATALOG_TYPE.MASTER);
//dummy load case needs to be added to the pole in order to populate the Aux Data fields
PPLEnvironment dummyLoadCase = FindElement(dummyLoadGuid, treeNodeList) as PPLEnvironment;
pole.AddChild(dummyLoadCase);
string sapPM = pole.GetValueString("Aux Data 1");
string poleLoc = pole.GetValueString("Aux Data 3");
//Get pole info from planning spreadsheet
string pdFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + sapPM + " Planning Data.xlsx";
string treeAttachSheet = "Ocalc Tree Attachments";
SLDocument planningData = new SLDocument(pdFilePath, treeAttachSheet);
SLWorksheetStatistics wsStats = planningData.GetWorksheetStatistics();
int numRows = wsStats.NumberOfRows;
int numCols = wsStats.NumberOfColumns;
string instructionStr = string.Empty;
for (int i = 2; i <= numRows; i++)
{
if (planningData.GetCellValueAsString(i, 1).Contains(poleLoc))
//if (planningData.GetCellValueAsString(i, 1).Equals(poleLoc))
{
instructionStr = planningData.GetCellValueAsString(i, 2);
}
}
planningData.CloseWithoutSaving();
//assign variables from instruction array
string[] instructionArr = instructionStr.Split(',');
string hftdGuid = instructionArr[0];
string go95Guid = instructionArr[1];
string wireGuid = instructionArr[2];
double[] distanceArr = instructionArr[3].Split(':').Select(Double.Parse).ToArray();
//check for spans over 125' and exit program if found
for (int i = 0; i <= distanceArr.Length - 1; i++)
{
if (distanceArr[i] > 125)
{
PPLMessageBox.Show("One or more spans to this pole exceeds 125'. Interset is required", "Cannot Build Pole!");
goto end;
}
}
double[] headingArr = instructionArr[4].Split(':').Select(Double.Parse).ToArray();
////if length of distanceArr != 2 ... need to figure out logic for this though the case would be very rare
//if (distanceArr.Length == 2)
//{
// double buryDepth = 7 * 12;
// pole.SetValue("BuryDepthInInches", buryDepth);
// PPLEnvironment hftdLoadCase = FindElement(hftdGuid, treeNodeList) as PPLEnvironment;
// PPLEnvironment go95LoadCase = FindElement(go95Guid, treeNodeList) as PPLEnvironment;
// //in the PPL_Lib library the Substitute method takes two arguments, PPLElement and PPLMain
// //but this gives an error in the script. When using this script for testing you have to comment out
// //the pPPLMain. But when using it on the PGE computer you must uncomment it or it will throw an error
// dummyLoadCase.Substitute(hftdLoadCase, pPPLMain);
// //pole.AddChild(hftdLoadCase);
// pole.AddChild(go95LoadCase);
// PPLInsulator spool = FindElement(spoolGuid, treeNodeList) as PPLInsulator;
// double deltaAngle = Math.Abs(headingArr[1] - headingArr[0]);
// if (deltaAngle > 180)
// {
// spool.CoordinateA = ((Math.Max(headingArr[0], headingArr[1]) - Math.Min(headingArr[0], headingArr[1])) / 2 + Math.Min(headingArr[0], headingArr[1]) + 180) * pi / 180;
// }
// else
// {
// spool.CoordinateA = ((Math.Max(headingArr[0], headingArr[1]) - Math.Min(headingArr[0], headingArr[1])) / 2 + Math.Min(headingArr[0], headingArr[1])) * (pi / 180);
// }
// spool.CoordinateZ = pole.LengthInInches - 9;
// spool.SetValue("Side", "Inline");
// pole.AddChild(spool);
// spool.SnapToParent();
// PPLSpan[] spans = new PPLSpan[distanceArr.Length];
// for (int i = 0; i <= distanceArr.Length - 1; i++)
// {
// spans[i] = FindElement(wireGuid, treeNodeList) as PPLSpan;
// spans[i].CoordinateA = (headingArr[i] * pi / 180) - spool.CoordinateA;
// spans[i].SetValue("SpanDistanceInInches", distanceArr[i] * 12);
// spool.AddChild(spans[i]);
// spans[i].SnapToInsulator();
// }
//}
//else
//{
// PPLMessageBox.Show("Cannot build pole!");
//}
double buryDepth = 6.5 * 12;
pole.SetValue("BuryDepthInInches", buryDepth);
PPLEnvironment hftdLoadCase = FindElement(hftdGuid, treeNodeList) as PPLEnvironment;
PPLEnvironment go95LoadCase = FindElement(go95Guid, treeNodeList) as PPLEnvironment;
//in the PPL_Lib library the Substitute method takes two arguments, PPLElement and PPLMain
//but this gives an error in the script. When using this script for testing you have to comment out
//the pPPLMain. But when using it on the PGE computer you must uncomment it or it will throw an error
dummyLoadCase.Substitute(hftdLoadCase /*, cPPLMain*/);
//pole.AddChild(hftdLoadCase);
pole.AddChild(go95LoadCase);
double minHeading;
double maxHeading;
if (distanceArr.Length < 4)
{
PPLInsulator spool = FindElement(spoolGuid, treeNodeList) as PPLInsulator;
if (distanceArr.Length == 2)
{
minHeading = Math.Min(headingArr[0], headingArr[1]);
maxHeading = Math.Max(headingArr[0], headingArr[1]);
}
else if (distanceArr.Length == 3)
{
minHeading = headingArr[0];
maxHeading = headingArr[0];
for (int i = 0; i < headingArr.Length - 1; i++)
{
if (minHeading > headingArr[i])
{
minHeading = headingArr[i];
}
else if (maxHeading < headingArr[i])
{
maxHeading = headingArr[i];
}
}
}
else
{
PPLMessageBox.Show("Cannot build pole!");
goto end;
}
double deltaAngle = maxHeading - minHeading;
if (deltaAngle > 180)
{
spool.CoordinateA = ((deltaAngle / 2) + minHeading + 180) * pi / 180;
}
else
{
spool.CoordinateA = ((deltaAngle / 2) + minHeading) * (pi / 180);
}
spool.CoordinateZ = pole.LengthInInches - 9;
spool.SetValue("Side", "Inline");
pole.AddChild(spool);
spool.SnapToParent();
PPLSpan[] spans = new PPLSpan[distanceArr.Length];
for (int i = 0; i <= distanceArr.Length - 1; i++)
{
spans[i] = FindElement(wireGuid, treeNodeList) as PPLSpan;
spans[i].CoordinateA = (headingArr[i] * pi / 180) - spool.CoordinateA;
spans[i].SetValue("SpanDistanceInInches", distanceArr[i] * 12);
spool.AddChild(spans[i]);
spans[i].SnapToInsulator();
}
}
else if (distanceArr.Length == 4)
{
PPLInsulator[] spools = new PPLInsulator[2];
PPLSpan[] multiSpans = new PPLSpan[headingArr.Length];
double[] minHead = new double[2];
minHead[0] = Math.Min(headingArr[0], headingArr[2]);
minHead[1] = Math.Min(headingArr[1], headingArr[3]);
double[] maxHead = new double[2];
maxHead[0] = Math.Max(headingArr[0], headingArr[2]);
maxHead[1] = Math.Max(headingArr[1], headingArr[3]);
double[] deltaAng = new double[2];
//deltaAng[0] = Math.Abs(headingArr[0] - headingArr[2]);
//deltaAng[1] = Math.Abs(headingArr[1] - headingArr[3]);
int spansIndex = 0;
int lessThan = 2;
for (int i = 0; i <= spools.Length - 1; i++)
{
deltaAng[i] = maxHead[i] - minHead[i];
spools[i] = FindElement(spoolGuid, treeNodeList) as PPLInsulator;
if (deltaAng[i] > 180)
{
spools[i].CoordinateA = ((deltaAng[i] / 2) + minHead[i] + 180) * pi / 180;
}
else
{
spools[i].CoordinateA = ((deltaAng[i] / 2) + minHead[i]) * (pi / 180);
}
spools[i].CoordinateZ = pole.LengthInInches - 9;
spools[i].SetValue("Side", "Inline");
pole.AddChild(spools[i]);
spools[i].SnapToParent();
for (int j = spansIndex; j <= lessThan; j += 2)
{
multiSpans[j] = FindElement(wireGuid, treeNodeList) as PPLSpan;
multiSpans[j].CoordinateA = (headingArr[j] * pi / 180) - spools[i].CoordinateA;
multiSpans[j].SetValue("SpanDistanceInInches", distanceArr[j] * 12);
spools[i].AddChild(multiSpans[j]);
multiSpans[j].SnapToInsulator();
}
spansIndex++;
lessThan++;
}
}
OcalcReload(cPPLMain);
end:
{ }
}
/// <summary>
/// Import DataTable,
/// BirthDate is formatted
/// Active field is set manually
/// </summary>
/// <param name="pFileName">Existing excel file</param>
/// <param name="pStartReference">Row to start import</param>
/// <param name="pDataTable">Data source</param>
/// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
/// <returns></returns>
/// <remarks>
/// Unlike example above we will create Demo2 worksheet, import our DataTable
/// and do some formatting. Each time we run this code I check to see if Demo2
/// sheet exist, if so, remove and add it back in.
/// </remarks>
public bool ImportDataTable2(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
{
bool success = false;
try
{
using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
{
var helper = new LightHelpers();
if (helper.SheetExists(pFileName, "Demo2"))
{
doc.DeleteWorksheet("Demo2");
}
doc.AddWorksheet("Demo2");
doc.SelectWorksheet("Demo2");
// create a date style for birthday column
var birthdayColumnStyle = doc.CreateStyle();
birthdayColumnStyle.FormatCode = "mm-dd-yyyy";
// since we need these several times, set them up as variables
var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1;
var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;
// set birthday style to our data
doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);
// do the Datatable import.
doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
// there is no method to set all bool values from TRUE/FALSE to Yes/No so we do it manually
// we add +1 to EndRowIndex because of the header
for (int row = 1; row < stats.EndRowIndex + 1; row++)
{
var value = doc.GetCellValueAsString(row, activeCellIndex);
if (value == "TRUE")
{
doc.SetCellValue(row, activeCellIndex, "Yes");
}
else if (value == "FALSE")
{
doc.SetCellValue(row, activeCellIndex, "No");
}
}
// set the column header stype
var headerSyle = doc.CreateStyle();
headerSyle.Font.FontColor = SysDraw.Color.White;
headerSyle.Font.Strike = false;
headerSyle.Fill.SetPattern(PatternValues.Solid, SysDraw.Color.Green, SysDraw.Color.White);
headerSyle.Font.Underline = UnderlineValues.None;
headerSyle.Font.Bold = true;
headerSyle.Font.Italic = false;
doc.SetCellStyle(1, 1, 1, pDataTable.Columns.Count, headerSyle);
// set one column to auto fit
//doc.AutoFitColumn(birthDayCellIndex, stats.EndRowIndex + 1);
// set all columns to auto fix beginning at data rather than column header
doc.AutoFitColumn(2, pDataTable.Columns.Count);
// save to the document we opened rather than a new document.
doc.Save();
success = true;
}
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
success = false;
}
return(success);
}
/// <summary>
/// Focus here is on removal of grid-lines while using code from
/// ImportDataTable2 method above. Also removed header formatting.
/// </summary>
/// <param name="pFileName">Existing excel file</param>
/// <param name="pStartReference">Row to start import</param>
/// <param name="pDataTable">Data source</param>
/// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
/// <returns></returns>
public bool ImportDataTable3(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
{
bool success = false;
var sheetName = "Demo3";
try
{
// open to Sheet1 (will always exists in this code sample)
using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
{
var helper = new LightHelpers();
if (helper.SheetExists(pFileName, sheetName))
{
doc.DeleteWorksheet(sheetName);
}
// add the sheet
doc.AddWorksheet(sheetName);
var isSet = doc.SelectWorksheet(sheetName);
// next three lines are responsible for removal of grid-lines in the current WorkSheet
SLPageSettings ps = new SLPageSettings();
ps.ShowGridLines = false;
doc.SetPageSettings(ps);
// setup a format for the date column
var birthdayColumnStyle = doc.CreateStyle();
birthdayColumnStyle.FormatCode = "mm-dd-yyyy";
// since we need these several times, set them up as variables
var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1;
var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;
// set birthday style to our data
doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);
// do the Datatable import.
doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
// change format of the bool column
for (int row = 1; row < stats.EndRowIndex + 1; row++)
{
var value = doc.GetCellValueAsString(row, activeCellIndex);
if (value == "TRUE")
{
doc.SetCellValue(row, activeCellIndex, "Yes");
}
else if (value == "FALSE")
{
doc.SetCellValue(row, activeCellIndex, "No");
}
}
// does what it says, auto-fit cell content
doc.AutoFitColumn(2, pDataTable.Columns.Count);
doc.Save();
success = true;
}
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
success = false;
}
return(success);
}
public static DataSet ReadData(DataSet dataSet)
{
Dictionary <int, int> votanti = new Dictionary <int, int>();
Dictionary <string, int> denumireFacultateCuId = new Dictionary <string, int>();
Dictionary <string, int> denumirePSCuId = new Dictionary <string, int>();
DataRow row;
bool auFormatCorectProgrameleDeStudii = true;
using (SLDocument sl = new SLDocument(GlobalValues.PATH_SOURCE_FILE))
{
SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
int iStartColumnIndex = stats.StartColumnIndex, psStartColumnIndex = 0;
String denumireFacultate = String.Empty, denumirePS = String.Empty;
int indexColumnEmail = 0, indexColumnNume = 0, indexColumnPrenume = 0;
int indexColumnGradDidactic = 0, indexColumnFacultatea = 0;
for (int columnIndex = stats.StartColumnIndex; columnIndex <= 6; columnIndex++)
{
string columnHeader = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
if (String.IsNullOrEmpty(columnHeader))
{
break;
}
if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_NUME))
{
indexColumnNume = columnIndex;
continue;
}
if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_PRENUME))
{
indexColumnPrenume = columnIndex;
continue;
}
if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_EMAIL))
{
indexColumnEmail = columnIndex;
continue;
}
if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_GRAD_DIDACTIC))
{
indexColumnGradDidactic = columnIndex;
continue;
}
if (columnHeader.ToUpper().Equals(GlobalValues.ColumnHeader_FACULTATEA))
{
indexColumnFacultatea = columnIndex;
psStartColumnIndex = indexColumnFacultatea + 1;
continue;
}
}
for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
{
denumirePS = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);
if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
{
break;
}
if (!String.IsNullOrEmpty(denumirePS) && !denumirePS.ToUpper().StartsWith("L") && !denumirePS.ToUpper().StartsWith("M"))
{
auFormatCorectProgrameleDeStudii = false;
break;
}
}
for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
{
denumirePS = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);
if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
{
break;
}
if (String.IsNullOrEmpty(denumirePS))
{
continue;
// TO DO : ISSUE WARNING?
}
// adauga programul de studiu
row = dataSet.Tables["ProgramStudiu"].NewRow();
row["DenumireScurta"] = denumirePS;
row["Facultate"] = denumireFacultate;
row["NumarAbsolventi"] = 0;
row["NumarVotanti"] = 0;
if ((auFormatCorectProgrameleDeStudii && denumirePS.ToUpper().StartsWith("M")) || sl.GetCellStyle(stats.StartRowIndex, columnIndex).Fill.PatternForegroundColor.R == 195) //PS : Master
{
row["ID_TipCiclu"] = GlobalValues.Map_TIP_CICLU_INVATAMANT_ID[GlobalValues.Denumire_TIP_CICLU_INVATAMANT_MASTER];
}
else //PS : LICENTA
{
row["ID_TipCiclu"] = GlobalValues.Map_TIP_CICLU_INVATAMANT_ID[GlobalValues.Denumire_TIP_CICLU_INVATAMANT_LICENTA];
}
dataSet.Tables["ProgramStudiu"].Rows.Add(row);
denumirePSCuId.Add(denumirePS, (int)row["ID_ProgramStudiu"]);
}
string nume = String.Empty, prenume = String.Empty, email = String.Empty, gradDidactic = String.Empty, facultateServiciu = String.Empty;
int idProfesor;
for (int rowIndex = stats.StartRowIndex + 4; rowIndex <= stats.EndRowIndex; ++rowIndex)
{
nume = sl.GetCellValueAsString(rowIndex, indexColumnNume);
prenume = sl.GetCellValueAsString(rowIndex, indexColumnPrenume);
if (String.IsNullOrEmpty(nume) && String.IsNullOrEmpty(prenume))
{
break;
}
row = dataSet.Tables["Profesor"].NewRow();
row["Nume"] = nume;
row["Prenume"] = prenume;
if (indexColumnEmail != 0)
{
email = sl.GetCellValueAsString(rowIndex, indexColumnEmail);
row["Email"] = email;
}
else
{
row["Email"] = String.Empty;
}
gradDidactic = sl.GetCellValueAsString(rowIndex, indexColumnGradDidactic);
facultateServiciu = sl.GetCellValueAsString(rowIndex, indexColumnFacultatea);
row["GradDidactic"] = gradDidactic;
row["FacultateServiciu"] = facultateServiciu;
if (gradDidactic.ToUpper().Equals(GlobalValues.DenumireScurta_CADRU_EXTERN))
{
row["EligibilRemunerare"] = false;
}
else
{
row["EligibilRemunerare"] = true;
}
dataSet.Tables["Profesor"].Rows.Add(row);
//ADD REZULTATE VOT
for (int columnIndex = psStartColumnIndex; columnIndex <= stats.EndColumnIndex; columnIndex++)
{
denumirePS = sl.GetCellValueAsString(stats.StartRowIndex, columnIndex);
denumireFacultate = sl.GetCellValueAsString(stats.StartRowIndex + 1, columnIndex);
if (String.IsNullOrEmpty(denumirePS) && String.IsNullOrEmpty(denumireFacultate))
{
break;
}
if (String.IsNullOrEmpty(denumirePS))
{
continue;
}
if (!sl.GetCellValueAsString(rowIndex, columnIndex).ToUpper().Equals("DA"))
{
continue;
}
idProfesor = (int)row["ID_Profesor"];
row = dataSet.Tables["RezultatVotProfesorProgramStudiu"].NewRow();
row["ID_Profesor"] = idProfesor;
row["ID_ProgramStudiu"] = denumirePSCuId[denumirePS];
row["NumarVoturi"] = 0;
dataSet.Tables["RezultatVotProfesorProgramStudiu"].Rows.Add(row);
}
}
}
return(dataSet);
}
public void ValidarConvenio(string cotizaCliente, string nombre, string usuario, string cotizacionClienteID)
{
log4net.Config.XmlConfigurator.ConfigureAndWatch(
new FileInfo(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile));
string CarpetaParaGuardar = "";
CarpetaParaGuardar = Path.Combine(Server.MapPath("~/Archivos"), nombre); //Obtengo la posición del archivo
HttpContext Contexto = HttpContext.Current;
Logger.Info("Comenzando método ValidarConvenio. Parámetros: " + cotizaCliente + ", " + nombre + ", " + usuario + ", " + cotizacionClienteID);
string str;
string strSearch = "Redespacho";
int fCnt = 0;
int cCnt = 0;
int rw = 0;
int cl = 0;
//string cpD = "";
//string cpO = "";
char[] splitParams = new char[] { '(', ')' };
bool pais = false;
//Session["ExcelTarifario"] = "";
ExcelMasivoTarifario emt = new ExcelMasivoTarifario(); //emt = excel masivo tarifario
IConveniosClientesDominio _conv = new ConveniosClientesDominio(); //_conv = convenio
var lemt = new List <ExcelMasivoTarifario>(); //lemt = listado excel masivo tarifario
List <int> sess = new List <int>();
List <OrigenDestinoSEL> listaOrgDst = new List <OrigenDestinoSEL>();
List <TarifarioZonaSEL> listaTrZona = new List <TarifarioZonaSEL>();
int ClienteID = Convert.ToInt32(_conv.GetClienteID(Convert.ToInt32(cotizacionClienteID)));
#region try
//Abro el archivo guardado y lo recorro para validar los datos
try
{
//Support (http://spreadsheetlight.com)
SLDocument libro = new SLDocument(CarpetaParaGuardar);
SLWorksheetStatistics stats = libro.GetWorksheetStatistics();
Logger.Info("Iniciando try.");
rw = stats.EndRowIndex;
cl = stats.EndColumnIndex;
Logger.Info("Filas: " + rw + ". Columnas: " + cl);
#region Recorrido de datos
if (cl == 8)
{
for (fCnt = 2; fCnt <= rw; fCnt++)
{
emt = new ExcelMasivoTarifario();
for (cCnt = 1; cCnt <= cl; cCnt++)
{
str = libro.GetCellValueAsString(fCnt, cCnt);
str = str.Replace(",", " ");
str = str.Replace("'", " ");
str = str.Replace("º", " ");
str = str.Replace(Environment.NewLine, "");
switch (cCnt) //Aplico el control por cada fila y columna para validar los datos
{
case 1: //Unidad de Venta
if (str != "")
{
List <ConsUnidadVentaSEL> listaUV = new List <ConsUnidadVentaSEL>();
listaUV = _conv.GetUnidadVenta(str);
if (listaUV.Count >= 1)
{
foreach (ConsUnidadVentaSEL uv in listaUV)
{
emt.UnidadVentaID = uv.UnidadVentaID;
emt.UnidadVenta = uv.UnidadVentaDescrip;
}
}
else
{
throw new ArgumentException("La Unidad de Venta no es la correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else
{
//emt.UnidadVenta = str;
throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
}
break;
case 2: //Modalidad de Entrega
if (str != "")
{
List <ModalidadEntregaSEL> listaME = new List <ModalidadEntregaSEL>();
listaME = _conv.GetModalidadEntrega(str);
if (listaME.Count >= 1)
{
foreach (ModalidadEntregaSEL me in listaME)
{
emt.ModalidadEntregaID = me.ModalidadEntregaID;
emt.ModalidadEntrega = me.ModalidadEntregaDescrip;
emt.CotizacionClienteUVentaModalidadID = _conv.GetCotizacionClienteUVModalidad(Convert.ToInt32(cotizaCliente), me.ModalidadEntregaID);
}
}
else
{
throw new ArgumentException("La Modalidad de Entrega no es la correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else
{
//emt.ModalidadEntrega = str;
throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
}
break;
case 3: //Origen
if (str != "")
{
string[] esAgrup_Loc = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);
if (esAgrup_Loc.Count() > 1)
{
if (esAgrup_Loc[1] == "Agrup.")
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(7, esAgrup_Loc[0], ClienteID, false);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoOrigen = od.ClienteOrigenDestinoID;
emt.TipoOrigenID = 7;
}
}
else
{
throw new ArgumentException("La Agrupación Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[1] == "Loc")
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(3, esAgrup_Loc[0], ClienteID, false);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoOrigen = od.ClienteOrigenDestinoID;
emt.TipoOrigenID = 3;
}
}
else
{
throw new ArgumentException("La Localidad Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[1] == "ZONA")
{
List <ZonaOrigenDestinoSEL> listaZona = new List <ZonaOrigenDestinoSEL>();
listaZona = _conv.GetZonaUV(ClienteID, esAgrup_Loc[0].ToString());
if (listaZona.Count() > 0)
{
foreach (ZonaOrigenDestinoSEL lz in listaZona)
{
emt.ClienteZonaID = lz.ClienteZonaID;
}
}
else
{
throw new ArgumentException("La Localidad Origen no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[0].Contains(strSearch.ToUpper())) //Busco por Redespacho
{
string unionZonaD = str.Remove(str.Length - 7).TrimEnd();
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(6, unionZonaD, ClienteID, true);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
emt.TipoDestinoID = 6;
}
}
else
{
throw new ArgumentException("El Redespacho Destino no es correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, false);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoOrigen = od.ClienteOrigenDestinoID;
emt.TipoOrigenID = 4;
}
}
else
{
throw new ArgumentException("La Provincia Origen no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
}
else
{
listaOrgDst.Clear();
if (esAgrup_Loc[0] == "Argentina")
{
pais = true;
listaOrgDst = _conv.GetOrigenDestino(5, esAgrup_Loc[0], ClienteID, false);
}
else
{
listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, false);
}
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoOrigen = od.ClienteOrigenDestinoID;
if (pais)
{
pais = false;
emt.TipoOrigenID = 5;
}
else
{
emt.TipoOrigenID = 4;
}
}
}
else
{
throw new ArgumentException("La Provincia Origen no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
}
else
{
throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
}
break;
case 4: //Destino
if (str != "")
{
string[] esAgrup_Loc = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);
if (esAgrup_Loc.Count() > 1)
{
if (esAgrup_Loc[1] == "Agrup.")
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(7, esAgrup_Loc[0], ClienteID, true);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
emt.TipoDestinoID = 7;
}
}
else
{
throw new ArgumentException("La Agrupación Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[1] == "Loc")
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(3, esAgrup_Loc[0], ClienteID, true);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
emt.TipoDestinoID = 3;
}
}
else
{
throw new ArgumentException("La Localidad Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[1] == "ZONA")
{
List <ZonaOrigenDestinoSEL> listaZona = new List <ZonaOrigenDestinoSEL>();
listaZona = _conv.GetZonaUV(ClienteID, esAgrup_Loc[0].ToString());
if (listaZona.Count() > 0)
{
foreach (ZonaOrigenDestinoSEL lz in listaZona)
{
emt.ClienteZonaID = lz.ClienteZonaID;
}
}
else
{
throw new ArgumentException("La Localidad Destino no es correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esAgrup_Loc[0].Contains(strSearch.ToUpper())) //Busco por Redespacho
{
string unionZonaD = str.Remove(str.Length - 7).TrimEnd();
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(6, unionZonaD, ClienteID, true);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
emt.TipoDestinoID = 6;
}
}
else
{
throw new ArgumentException("El Redespacho Destino no es correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else
{
listaOrgDst.Clear();
listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, true);
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
emt.TipoDestinoID = 4;
}
}
else
{
throw new ArgumentException("La Provincia Destino no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
}
else
{
listaOrgDst.Clear();
if (esAgrup_Loc[0] == "Argentina")
{
pais = true;
listaOrgDst = _conv.GetOrigenDestino(5, esAgrup_Loc[0], ClienteID, true);
}
else
{
listaOrgDst = _conv.GetOrigenDestino(4, esAgrup_Loc[0], ClienteID, true);
}
if (listaOrgDst.Count >= 1)
{
foreach (OrigenDestinoSEL od in listaOrgDst)
{
emt.CodigoDestino = od.ClienteOrigenDestinoID;
if (pais)
{
pais = false;
emt.TipoOrigenID = 5;
}
else
{
emt.TipoDestinoID = 4;
}
}
}
else
{
throw new ArgumentException("La Provincia Destino no es el correcta. Columna: " + cCnt + " Fila: " + fCnt);
}
}
}
else
{
throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
}
break;
case 5: //Tarifario
if (str != "")
{
string[] esTarifario = str.Split(splitParams, StringSplitOptions.RemoveEmptyEntries);
if (esTarifario.Count() > 1)
{
if (esTarifario[1] == "PART.Esp.")
{
List <TarifarioEspecificoSEL> listaTrEsp = new List <TarifarioEspecificoSEL>();
listaTrEsp = _conv.GetTarifarioEspecifico(esTarifario[0]);
if (listaTrEsp.Count >= 1)
{
foreach (TarifarioEspecificoSEL te in listaTrEsp)
{
emt.TarifarioEspecificoID = te.TarifarioClienteFleteID;
emt.TarifarioEspecifico = te.TarifarioClienteFleteDescrip;
}
}
else
{
throw new ArgumentException("El Tarifario Específico no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esTarifario[1] == "PART. Zonas")
{
listaTrZona = _conv.GetTarifarioZona(esTarifario[0]);
if (listaTrZona.Count >= 1)
{
foreach (TarifarioZonaSEL tz in listaTrZona)
{
emt.TarifarioZonasID = tz.TarifarioFleteID;
emt.TarifarioZonas = tz.TarifarioFleteDescrip;
}
}
else
{
throw new ArgumentException("El Tarifario por Zonas no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esTarifario[1] == "Estand.")
{
listaTrZona = _conv.GetTarifarioZona(esTarifario[0]);
if (listaTrZona.Count >= 1)
{
foreach (TarifarioZonaSEL tz in listaTrZona)
{
emt.TarifarioEstandarizadoID = tz.TarifarioFleteID;
emt.TarifarioEstandarizado = tz.TarifarioFleteDescrip;
}
}
else
{
throw new ArgumentException("El Tarifario Estandarizado no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
else if (esTarifario[1] == "Redesp.")
{
List <TarifarioRedespachoSEL> listaRedesp = new List <TarifarioRedespachoSEL>();
listaRedesp = _conv.GetTarifarioRedespacho(esTarifario[0]);
if (listaRedesp.Count >= 1)
{
foreach (TarifarioRedespachoSEL tr in listaRedesp)
{
emt.TarifarioRedespachoID = tr.TarifarioVentaReDespachoID;
emt.TarifarioRedespacho = tr.TarifarioVentaReDespachoDescrip;
}
}
else
{
throw new ArgumentException("El Tarifario Redespacho no es el correcto. Columna: " + cCnt + " Fila: " + fCnt);
}
}
}
else
{
throw new ArgumentException("El Tarifario utilizado no expecifica a ningún tarifario.");
}
}
else
{
//emt.TarifarioEspecifico = str;
throw new ArgumentException("La celda se encuentra vacia. Columna: " + cCnt + " Fila: " + fCnt);
}
break;
case 6:
break;
case 7:
break;
case 8:
break;
}
}
#endregion
lemt.Add(emt);
}
foreach (var em in lemt) //Guardo cada convenio por separado
{
em.NombreExcel = nombre;
em.UsuarioID = Convert.ToInt32(usuario);
_conv.AddConvenio(em);
sess.Add(em.ExcelMasivoTarifarioID);
}
Session["ExcelTarifario"] = sess;
}
else
{
throw new ArgumentException("La Cantidad de Columnas no son correctas");
}
lemt.Clear();
Logger.Info("Finalizado correctamente.");
}
catch (Exception ex)
{
Contexto.Response.ContentType = "application/json";
Contexto.Response.Write("{\"error\":false,\"msg\":\"" + ex.Message.ToString() + "\"}");
Logger.Error("Error: " + ex.Message.ToString());
}
}
private void mcatBrowseButton_Click(object sender, EventArgs e)
{
if (cn.IsOpen)
{
//Open the dialog for the user to choose a spreadsheet
OpenFileDialog openSheetDialog = new OpenFileDialog();
openSheetDialog.FileName = "";
openSheetDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openSheetDialog.FilterIndex = 1;
if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
{
using (new CursorWait())
{
label1.Text = "Importing spreadsheet data. This may take a moment.";
label1.Refresh();
mcatBrowseButton.Visible = false;
mcatBrowseButton.Refresh();
pcatBrowseButton.Visible = false;
//open the file as a Spreadsheet Light document
SLDocument ss = new SLDocument(openSheetDialog.FileName);
SLWorksheetStatistics stats = ss.GetWorksheetStatistics();
//iterate through rows until the final row is reached
for (int r = 2; r <= stats.EndRowIndex; r++)
{
//Get the string values from the table
string legal_state_cd = ss.GetCellValueAsString(r, 1).Trim();
string aamc = ss.GetCellValueAsString(r, 2).Trim();
DateTime date = ss.GetCellValueAsDateTime(r, 3);
string lname = ss.GetCellValueAsString(r, 4).Trim();
string fname = ss.GetCellValueAsString(r, 5).Trim();
string mname = ss.GetCellValueAsString(r, 6).Trim();
string suffix = ss.GetCellValueAsString(r, 7).Trim();
string address = ss.GetCellValueAsString(r, 8).Trim();
string city = ss.GetCellValueAsString(r, 9).Trim();
string state = ss.GetCellValueAsString(r, 10).Trim();
string postal_cd = ss.GetCellValueAsString(r, 11).Trim();
string country = ss.GetCellValueAsString(r, 12).Trim();
string email = ss.GetCellValueAsString(r, 13).Trim();
string sex = ss.GetCellValueAsString(r, 14).Trim();
int age = ss.GetCellValueAsInt32(r, 15);
string major_cd = ss.GetCellValueAsString(r, 16).Trim();
string major = ss.GetCellValueAsString(r, 17).Trim();
string primary_interest_cd = ss.GetCellValueAsString(r, 18).Trim();
string primary_interest = ss.GetCellValueAsString(r, 19).Trim();
int cpbs_score = ss.GetCellValueAsInt32(r, 20);
int cpbs_cb_lower = ss.GetCellValueAsInt32(r, 21);
int cpbs_cb_upper = ss.GetCellValueAsInt32(r, 22);
int cpbs_percentile_rank = ss.GetCellValueAsInt32(r, 23);
int cars_score = ss.GetCellValueAsInt32(r, 24);
int cars_cb_lower = ss.GetCellValueAsInt32(r, 25);
int cars_cb_upper = ss.GetCellValueAsInt32(r, 26);
int cars_percentile_rank = ss.GetCellValueAsInt32(r, 27);
int bbfl_score = ss.GetCellValueAsInt32(r, 28);
int bbfl_cb_lower = ss.GetCellValueAsInt32(r, 29);
int bbfl_cb_upper = ss.GetCellValueAsInt32(r, 30);
int bbfl_percentile_rank = ss.GetCellValueAsInt32(r, 31);
int psbb_score = ss.GetCellValueAsInt32(r, 32);
int psbb_cb_lower = ss.GetCellValueAsInt32(r, 33);
int psbb_cb_upper = ss.GetCellValueAsInt32(r, 34);
int psbb_percentile_rank = ss.GetCellValueAsInt32(r, 35);
int total_score = ss.GetCellValueAsInt32(r, 36);
int total_cb_lower = ss.GetCellValueAsInt32(r, 37);
int total_cb_upper = ss.GetCellValueAsInt32(r, 38);
int total_percentile_rank = ss.GetCellValueAsInt32(r, 39);
//add each row to the standard data table
mcatTable.Rows.Add("", legal_state_cd, aamc, date, lname, fname, mname, suffix, address, city, state, postal_cd, country, email, sex, age, major_cd, major, primary_interest_cd, primary_interest, cpbs_score, cpbs_cb_lower, cpbs_cb_upper, cpbs_percentile_rank, cars_score, cars_cb_lower, cars_cb_upper, cars_percentile_rank, bbfl_score, bbfl_cb_lower, bbfl_cb_upper, bbfl_percentile_rank, psbb_score, psbb_cb_lower, psbb_cb_upper, psbb_percentile_rank, total_score, total_cb_lower, total_cb_upper, total_percentile_rank);;
}
//close the SLDocument, as the data is now in a datatable
ss.CloseWithoutSaving();
if (cn.IsOpen)
{
//clear any old results from the table
cn.Execute("DELETE FROM LECOM_MATCHING_MCAT_TABLE", SQLTypes.Text);
SqlParameters dtParm = new SqlParameters();
dtParm.Add("exampleDT", mcatTable, SqlDbType.Structured);
dtParm.List[0].TypeName = "dbo.LecomMatchingMcatTableType";
cn.Execute("dbo.Lecom_MatchingImportMcat", SQLTypes.StoredProcedure, dtParm);
label1.Text = "Excel data import complete! Click below to view the match report.";
mcatReportViewButton.Visible = true;
}
}
}
else
{
MessageBox.Show("Please select a valid Excel 2007 file!");
openSheetDialog.ShowDialog();
}
}
}
public void ColorCells(SLDocument document)
{
float value = 0;
DateTime today_from = new DateTime();
today_from = DateTime.Today;
DateTime current_date = new DateTime(today_from.Year, today_from.Month, today_from.Day, new GregorianCalendar());
float.TryParse(oreMacchina.Text, out value); // parso la stringa in un float, lo faccio per poter fare i calcoli
var list = new List <string>();
SLWorksheetStatistics stats = document.GetWorksheetStatistics();
int endColumnIndex = stats.EndColumnIndex;
// adesso faccio i calcoli per prima fase
float result_1 = (value * storage_value_data_1) / 100; // risultato per le ore relative alla prima lavorazione
TimeSpan timespan_1 = TimeSpan.FromHours(result_1); // con il timespan converto i decimali in ore
// method which don't respect the DRY guide line
SLConditionalFormatting cf; // I need this for color
int i, j;
for (i = today_from.Day; i < 5; ++i) // control and set the value of the cell
{
cf = new SLConditionalFormatting("B5", "H5"); // control the color of the cells
for (j = 1; j < 7; ++j) // control and set the value of the cell
{
if (result_1 > 16)
{
cf = new SLConditionalFormatting("B5", "H5"); // control the color of the cells
}
document.SetCellValue(i, j, result_1);
// set a custom color for
cf.SetCustom3ColorScale(SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent1Color, 0.2,
SLConditionalFormatRangeValues.Percentile, "35", SLThemeColorIndexValues.Accent3Color, -0.1,
SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent6Color, 0.5);
document.AddConditionalFormatting(cf);
}
}
foreach (var sheetName in document.GetWorksheetNames())
{
// with this for I read data as string in the second row of the excel sheet
for (int z = 1; z <= endColumnIndex; z++)
{
list.Add(document.GetCellValueAsString(3, z));
}
// NON FUNZIONA, NON VIENE INSERITA LA DATA ODIERNA NELLA CELLA D4
var print_current_day_test_1_cell = document.SetCellValue("D4", current_date.Day + "/" + current_date.Month + "/" + current_date.Year);
// se metto diverso funziona ma dovrebbe funzionare con l'uguale uguale
if (document.GetCellValueAsDateTime("2") != document.GetCellValueAsDateTime("D4")) // !!!!!!!!!!!!!!!!!!!!!! scorta di punti esclamativi
{
// works with the dateTime if was stored in a cell but if I try to get the cell value and compare it with DateTime.Today or current_date it doesn't work
MessageBox.Show("Ho trovato la data odierna");
for (int y = 3; y <= 500; y++)
{
if (document.GetCellValueAsInt32("A" + y) == 0)
{
document.SetCellValue("A" + y, y);
}
}
document.SetCellValue("G4", "Sono riuscito a trovare la data odierna");
}
}
}
private void pcatBrowseButton_Click(object sender, EventArgs e)
{
if (cn.IsOpen)
{
//Open the dialog for the user to choose a spreadsheet
OpenFileDialog openSheetDialog = new OpenFileDialog();
openSheetDialog.FileName = "";
openSheetDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openSheetDialog.FilterIndex = 1;
if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
{
using (new CursorWait())
{
label1.Text = "Importing spreadsheet data. This may take a moment.";
label1.Refresh();
//Update UI
mcatBrowseButton.Visible = false;
mcatBrowseButton.Refresh();
pcatBrowseButton.Visible = false;
//open the file as a Spreadsheet Light document
SLDocument ss = new SLDocument(openSheetDialog.FileName);
SLWorksheetStatistics stats = ss.GetWorksheetStatistics();
//iterate through rows until the final row is reached
for (int r = 2; r <= stats.EndRowIndex; r++)
{
//Get the string values from the table
string lname = ss.GetCellValueAsString(r, 1).Trim();
string fname = ss.GetCellValueAsString(r, 2).Trim();
string mi = ss.GetCellValueAsString(r, 3).Trim();
string email = ss.GetCellValueAsString(r, 4).Trim();
string address1 = ss.GetCellValueAsString(r, 5).Trim();
string address2 = ss.GetCellValueAsString(r, 6).Trim();
string city = ss.GetCellValueAsString(r, 8).Trim();
string state = ss.GetCellValueAsString(r, 9).Trim();
string zip = ss.GetCellValueAsString(r, 10).Trim();
string country = ss.GetCellValueAsString(r, 11).Trim();
//add each row to the standard data table
pcatTable.Rows.Add("", lname, fname, mi, email, address1, address2, city, state, zip, country);
}
//close the SLDocument, as the data is now in a datatable
ss.CloseWithoutSaving();
if (cn.IsOpen)
{
//clear any old results from the table
cn.Execute("DELETE FROM LECOM_MATCHING_PCAT_TABLE", SQLTypes.Text);
SqlParameters dtParm = new SqlParameters();
dtParm.Add("exampleDT", pcatTable, SqlDbType.Structured);
dtParm.List[0].TypeName = "dbo.LecomMatchingPcatTableType";
cn.Execute("dbo.Lecom_MatchingImportPcat", SQLTypes.StoredProcedure, dtParm);
label1.Text = "Excel data import complete! Click below to view the match report.";
pcatReportViewButton.Visible = true;
}
}
}
else
{
MessageBox.Show("Please select a valid Excel 2007 file!");
openSheetDialog.ShowDialog();
}
}
}