public override XVar db_gettablelist() { XVar ret = XVar.Array(); var dbcon = ((System.Data.OleDb.OleDbConnection)conn.connect().Value); try { dbcon.Open(); System.Data.DataTable schemaTable = dbcon.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (System.Data.DataRow row in schemaTable.Rows) { if (row["TABLE_TYPE"].ToString() == "TABLE" || row["TABLE_TYPE"].ToString() == "VIEW") { ret.Add(row["TABLE_NAME"].ToString()); } } } finally { dbcon.Close(); } return(ret); }
public override XVar db_gettablelist() { XVar ret = XVar.Array(); var dbcon = ((System.Data.Odbc.OdbcConnection)conn.connect().Value); try { dbcon.Open(); System.Data.DataTable schemaTable = dbcon.GetSchema("Tables"); foreach (System.Data.DataRow row in schemaTable.Rows) { String schema = row["TABLE_SCHEM"].ToString(), table = row["TABLE_NAME"].ToString(), type = row["TABLE_TYPE"].ToString(); if (schema != "") { table = schema + "." + table; } if (type == "TABLE" || type == "VIEW") { ret.Add(table); } } } finally { dbcon.Close(); } return(ret); }
public static XVar getImportExcelFields(XVar data) { XVar fields = XVar.Array(); if (data == null) { return(fields); } try { ExcelPackage xlsPack = data.Value as ExcelPackage; var worksheet = xlsPack.Workbook.Worksheets[1]; int cellIndex = 1; while (worksheet.Cells[1, cellIndex].Value != null) { fields.Add(XVar.Pack(worksheet.Cells[1, cellIndex].Value)); cellIndex++; } } catch (Exception ex) { MVCFunctions.import_error_handler(ex); } return(fields); }
// not yet implemented public static XVar getPreviewDataFromExcel(XVar fileHandle, XVar fieldsData) { XVar previewData = XVar.Array(); XVar tableData = XVar.Array(); var remainNumOfPreviewRows = 100; try { ExcelPackage xlsPack = fileHandle.Value as ExcelPackage; foreach (var record in IterateSheetPreview(xlsPack)) { if (remainNumOfPreviewRows < 0) { break; } XVar row = XVar.Array(); foreach (KeyValuePair <XVar, dynamic> field in record.GetEnumerator()) { row.Add(field.Value); } tableData.Add(row); remainNumOfPreviewRows--; } } catch (Exception ex) { MVCFunctions.import_error_handler(ex); } previewData["tableData"] = tableData; return(previewData); }
public static XVar parceCSVLine(XVar line, XVar delimiter, XVar removeBOM = null) { var linestr = line.ToString(); if (linestr.Length < 3) { return(false); } if (linestr.Substring(0, 3) == "\xEF\xBB\xBF") { linestr = linestr.Substring(3); } CsvReader csvRdr = new CsvReader(new StringReader(linestr), false, delimiter.ToString()[0]); try { if (csvRdr.ReadNextRecord()) { XVar row = XVar.Array(); for (int i = 0; i < csvRdr.FieldCount; i++) { row.Add(csvRdr[i]); } return(row); } } catch (Exception ex) { return(false); } return(false); }
/// <summary> /// Returns list of table names /// </summary> /// <returns></returns> public XVar GetTablesList() { this.FillTablesList(); XVar res = XVar.Array(); foreach (var tbl in this.lstTables.GetEnumerator()) { res.Add(tbl.Value["name"]); } return(res); }
public override XVar db_gettablelist() { XVar ret = XVar.Array(); XVar strSQL = "sp_tables"; var rs = conn.query(strSQL); XVar data; while (data = rs.fetchAssoc()) { if (data["TABLE_OWNER"].ToUpper() != "SYS" && data["TABLE_OWNER"].ToUpper() != "INFORMATION_SCHEMA") { ret.Add(MVCFunctions.Concat(data["TABLE_OWNER"], ".", data["TABLE_NAME"])); } } return(ret); }
public XVar GetDBTableKeys() { if (!GlobalVars.dal_info.KeyExists(this.m_infoKey) || !MVCFunctions.is_array(GlobalVars.dal_info[this.m_infoKey])) { return(XVar.Array()); } XVar ret = XVar.Array(); foreach (var f in GlobalVars.dal_info[this.m_infoKey].GetEnumerator()) { if (f.Value["key"] != null) { ret.Add(f.Key); } } return(ret); }
public static XVar getImportCVSFields(XVar uploadfile) { XVar csvFields = XVar.Array(); try { CsvReader csvRdr = new CsvReader(new StreamReader((uploadfile.Value as HttpPostedFile).InputStream), true); foreach (var header in csvRdr.GetFieldHeaders()) { csvFields.Add(header); } } catch (Exception ex) { MVCFunctions.import_error_handler(ex); } return(csvFields); }
public static XVar getFileNamesFromDir(XVar dirPath) { XVar names = XVar.Array(); var dirPathStr = dirPath.ToString().Replace('/', '\\'); if (!System.IO.Directory.Exists(dirPathStr)) { return(names); } foreach (var file in System.IO.Directory.EnumerateFiles(dirPathStr)) { names.Add(file); } return(names); }
public static IEnumerable <XVar> IterateSheetPreview(ExcelPackage xlsPack) { foreach (var worksheet in xlsPack.Workbook.Worksheets) { for (int row = 1; row <= worksheet.Dimension.End.Row; ++row) { XVar arr = XVar.Array(); for (int i = 0; i < worksheet.Dimension.End.Column; i++) { var cell = worksheet.Cells[row, i + 1]; XVar val = null; if (cell != null && cell.Value != null) { if (cell.Value is DateTime) // sometimes date stored in a DateTime { val = ((DateTime)cell.Value).ToString("yyyy-MM-dd H:mm:ss"); } else if (Regex.IsMatch(cell.Style.Numberformat.Format, ("([ymdHis])"))) // sometimes date stored in a weird number { cell.Style.Numberformat.Format = "yyyy-MM-dd H:mm:ss"; val = cell.Text; } else if (cell.IsRichText) { val = cell.RichText.Text; } else { val = new XVar(cell.Value.ToString()); } } arr.Add(MVCFunctions.runner_htmlspecialchars(val)); } yield return(arr); } } }
public static void ExportExcelRecord(XVar arrdata, XVar datatype, XVar row, XVar objPHPExcel, dynamic pageObj) { XVar rowObj = new XVar("data", arrdata, "datatype", datatype); objPHPExcel.Add(rowObj); int cellIndex = 1; ExcelWorksheet ws = ((ExcelPackage)objPHPExcel["excelObj"].Value).Workbook.Worksheets["Sheet1"]; objPHPExcel["rowIndex"]++; int rowIndex = objPHPExcel["rowIndex"]; foreach (var cell in arrdata.GetEnumerator()) { if (datatype[cell.Key] == "binary") { if (cell.Value == null) { continue; } Image img = Image.FromStream(new MemoryStream((byte[])cell.Value.Value)); InsertImage(img, ws, cellIndex, rowIndex); } else if (datatype[cell.Key] == "file") { if (!cell.Value || !File.Exists(pageObj.pSet.getUploadFolder(cell.Key) + cell.Value.ToString())) { continue; } Image img = Image.FromFile(pageObj.pSet.getUploadFolder(cell.Key) + cell.Value.ToString()); InsertImage(img, ws, cellIndex, rowIndex); } else { // try to get correct cell type var obj = ((XVar)cell.Value).Value; var type = pageObj.pSet.getFieldType(cell.Key); if (CommonFunctions.IsDateFieldType(type)) { ws.Cells[objPHPExcel["rowIndex"], cellIndex].Style.Numberformat.Format = GlobalVars.locale_info["LOCALE_SSHORTDATE"].ToString() + (CommonFunctions.IsDateTimeFieldType(type) ? " hh:mm:ss" : ""); } else if (CommonFunctions.IsNumberType(type) && obj is string) { double objDouble = 0; int objInt = 0; if (CommonFunctions.IsFloatType(type) && double.TryParse(obj as string, out objDouble)) { obj = objDouble; } else if (int.TryParse(obj as string, out objInt)) { obj = objInt; } } ws.Cells[objPHPExcel["rowIndex"], cellIndex].Value = obj; } cellIndex++; } }
public static IEnumerable <XVar> IterateSheet(ExcelPackage xlsPack, XVar fieldsData, bool useFirstLine) { XVar previewData = null; foreach (var worksheet in xlsPack.Workbook.Worksheets) { for (int row = (useFirstLine? 1 : 2); row <= worksheet.Dimension.End.Row; ++row) { XVar arr = XVar.Array(); for (int i = 0; i < worksheet.Dimension.End.Column; i++) { if (!fieldsData.KeyExists(i)) { continue; } var cell = worksheet.Cells[row, i + 1]; XVar val = null; if (cell != null && cell.Value != null) { bool dateCorrectlyExtracted = false; if (cell.Value is DateTime) // sometimes date stored in a DateTime { val = ((DateTime)cell.Value).ToString("yyyy-MM-dd H:mm:ss"); dateCorrectlyExtracted = true; } else if (Regex.IsMatch(cell.Style.Numberformat.Format, ("([ymdHis])"))) // sometimes date stored in a weird number { cell.Style.Numberformat.Format = "yyyy-MM-dd H:mm:ss"; val = cell.Text; dateCorrectlyExtracted = true; } else if (cell.IsRichText) { val = cell.RichText.Text; } else { val = new XVar(cell.Value.ToString()); } // the following code block looks suspicious (!) if (fieldsData.KeyExists(i) && fieldsData[i]["dateTimeType"] && // column should have date value, !dateCorrectlyExtracted && // but in excel it looks awfull (row != 0 || !useFirstLine)) // also we still dont know actual format and it is not header row { previewData = XVar.Array(); // so try to guess format from the value previewData["dateFormat"] = ImportPage.extractDateFormat(val); } } if (previewData != null) { arr.Add(MVCFunctions.runner_htmlspecialchars(val)); } else { arr[fieldsData[i]["fName"]] = val; } } yield return(arr); } } }