public void Init_from_XMLobj(Hss_XML_obj hxo) { if (hxo == null) { return; } this.colKey_list.Clear(); string name = hxo.Get_attr("Key"); if (!string.IsNullOrEmpty(name)) { this.Key = name; } List <Hss_XML_obj> list = hxo.Get_all_obj(); for (int i = 0; i < list.Count; ++i) { Hss_XML_obj xo = list[i]; string colKey = xo.value; bool hidden = HssStr.True_or_False(xo.Get_attr("Hidden")); HssGridColumn hgc = new HssGridColumn(colKey, hidden, i); this.colKey_list.Add(hgc); } }
private void Parse_ElementName() { while (this.ReadNext_char()) { if (this.blankChars_hs.Contains(this.currChar)) { string eleName = HssStr.SafeXML_to_ori(this.sb.ToString()); this.element_st.Push(new Hss_XML_obj(eleName, this.ElementType)); this.sb.Clear(); this.ParsingState = XML_ParseState.InElement; break; } else if (this.currChar == '>') { string eleName = HssStr.SafeXML_to_ori(this.sb.ToString()); this.element_st.Push(new Hss_XML_obj(eleName, this.ElementType)); this.sb.Clear(); this.ParsingState = XML_ParseState.OutElement; break; } else { this.sb.Append(this.currChar); } } }
/// <summary> /// Create workbook.xml /// </summary> private void Create_WB_fromDS(DataSet ds, string fileName) { if (string.IsNullOrEmpty(fileName) || ds == null) { return; } if (ds.Tables.Count < 1) { return; } string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine + "<workbook xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' " + HssStr.WinNextLine + "xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships'><sheets>" + HssStr.WinNextLine; FileStream fs = new FileStream(fileName, FileMode.Create); byte[] header_bts = Encoding.UTF8.GetBytes(header_str); fs.Write(header_bts, 0, header_bts.Length); for (int i = 1; i <= ds.Tables.Count; ++i) { DataTable dt = ds.Tables[i - 1]; string str = "<sheet name=\"" + HssStr.ToSafeXML_str(dt.TableName) + "\" sheetId='" + i + "' r:id='hssSheet" + i + "'/>" + HssStr.WinNextLine; byte[] arr = Encoding.UTF8.GetBytes(str); fs.Write(arr, 0, arr.Length); ++this.hLog.RecordNum; } byte[] tail_bts = Encoding.UTF8.GetBytes("</sheets></workbook>"); fs.Write(tail_bts, 0, tail_bts.Length); fs.Close(); }
private StringBuilder Get_groupBy_sb(HashSet <ColumnTemplate> hs) { StringBuilder groupBy_sb = new StringBuilder(); if (!this.HasAggregateFunction(hs)) { return(groupBy_sb); } groupBy_sb.Append(" Group by "); int non_aggCount = 0; foreach (ColumnTemplate ct in hs) { if (ct.AggregateFunc != AggregateFunction.None) { continue; } string colName = ct.ColName.Replace("]", "]]"); groupBy_sb.Append("[").Append(colName).Append("]").Append(','); non_aggCount++; } HssStr.RemoveLastChar(groupBy_sb, ','); if (non_aggCount < 1) { groupBy_sb.Clear(); } return(groupBy_sb); }
/// <summary> /// Get the index of [Table_column] /// </summary> public void Calculate() { string tc_str = this.Table_Column.Value; if (string.IsNullOrEmpty(tc_str)) { return; } int index = 0, baseTen = 1, checkCount = 0; for (int i = tc_str.Length - 1; i >= 0; --i) { char c = tc_str[i]; if (!HssStr.IsNum(c)) { break; } index += (c - '0') * baseTen; baseTen *= 10; if (++checkCount >= DTC_Position_Headers.max_charCheckCount) { break; } } if (checkCount > 0) { this.TableCol_index = index; } }
private void find_textBox_KeyPress(object sender, KeyPressEventArgs e) { if (e.KeyChar == 13 || e.KeyChar == 10) { string toFind = this.find_textBox.Text; if (toFind.Length < 1) { return; } string allText = this.main_richTextBox.Text; if (this.findText_list == null) { this.findText_list = HssStr.FindAll_substrings(allText, toFind, false); } if (this.findText_list.Count < 1) { MessageBox.Show("No Match"); } else { this.main_richTextBox.Select(this.findText_list[this.currFind_index], toFind.Length); if (++this.currFind_index >= this.findText_list.Count) { this.currFind_index = 0; } } } }
public string ToXML() { StringBuilder sb = new StringBuilder(); sb.Append("<col Hidden='").Append(this.hidden_flag).Append("'>"); sb.Append(HssStr.ToSafeXML_str(this.column_key)).Append("</col>"); return(sb.ToString()); }
public SqlCommand GetSQL_cmd() { if (this.template == null) { Console.WriteLine("DB_insert error 0: no template"); return(null); } if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName)) { Console.WriteLine("DB_insert error 1: no name"); return(null); } if (col_val_dic.Count < 1) { Console.WriteLine("DB_insert error 2: no column"); return(null); } StringBuilder sql_sb = new StringBuilder("Insert into "); sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]"); sql_sb.Append("."); sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]"); sql_sb.Append("."); sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "] "); int tempID = 0; SqlCommand cmd = new SqlCommand(); StringBuilder col_sb = new StringBuilder(), val_sb = new StringBuilder(); foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic) { string colName = pair.Key.Replace("]", "]]"); string valName = "@Val" + tempID; col_sb.Append("[").Append(colName).Append("]").Append(','); val_sb.Append(valName).Append(','); SqlParameter val_sp = new SqlParameter(); val_sp.Value = pair.Value.Value; val_sp.SqlDbType = pair.Value.SqlDbType; val_sp.ParameterName = valName; cmd.Parameters.Add(val_sp); ++tempID; } HssStr.RemoveLastChar(col_sb, ','); HssStr.RemoveLastChar(val_sb, ','); sql_sb.Append("(").Append(col_sb).Append(")"); sql_sb.Append(" values "); sql_sb.Append("(").Append(val_sb).Append(") "); cmd.CommandText = sql_sb.ToString(); return(cmd); }
private void Parse_ElementStart() { if (!this.ReadNext_char()) { return; } if (this.currChar == '?') { this.sb.Clear(); this.ParsingState = XML_ParseState.ElementName; this.ElementType = XML_ElementType.Declare; } else if (this.currChar == '!') { this.sb.Clear(); this.ParsingState = XML_ParseState.CommentValue; this.ElementType = XML_ElementType.Comment; } else if (this.currChar == '>') { Hss_XML_obj xo = this.element_st.Peek(); xo.Add_obj(new Hss_XML_obj(null, this.ElementType)); this.ParsingState = XML_ParseState.OutElement; } else if (this.currChar == '/') { Hss_XML_obj xo = this.element_st.Pop(); xo.value = HssStr.SafeXML_to_ori(this.sb.ToString().Trim()); this.sb.Clear(); this.element_st.Peek().Add_obj(xo); //xo.Show();//test output this.ParsingState = XML_ParseState.ElementEnd; } else { this.sb.Clear(); this.sb.Append(this.currChar); this.ParsingState = XML_ParseState.ElementName; this.ElementType = XML_ElementType.Normal; } }
public string GetXML() { StringBuilder sb = new StringBuilder("<EVENT_INFORMATION>").Append(HssStr.WinNextLine); sb.Append("<UniqueUniversalEventIdentifier>").Append(this.UniqueUniversalEventIdentifier).Append("</UniqueUniversalEventIdentifier>").Append(HssStr.WinNextLine); sb.Append("<SECURITY_IDENTIFIER_CUSIP>").Append(this.SECURITY_IDENTIFIER_CUSIP).Append("</SECURITY_IDENTIFIER_CUSIP>").Append(HssStr.WinNextLine); sb.Append("<ISSUER_NAME>").Append(HssStr.ToSafeXML_str(this.ISSUER_NAME)).Append("</ISSUER_NAME>").Append(HssStr.WinNextLine); sb.Append("<ADR_RECORD_DATE>").Append(this.ADR_RECORD_DATE.ToString("yyyy-MM-dd")).Append("</ADR_RECORD_DATE>").Append(HssStr.WinNextLine); sb.Append("<RATIO>").Append(HssStr.WinNextLine); sb.Append("<ADRBase>").Append((int)this.ADRBase).Append("</ADRBase>").Append(HssStr.WinNextLine); sb.Append("<OrdinaryShare>").Append((int)this.OrdinaryShare).Append("</OrdinaryShare>").Append(HssStr.WinNextLine); sb.Append("</RATIO>").Append(HssStr.WinNextLine); sb.Append("</EVENT_INFORMATION>").Append(HssStr.WinNextLine); return(sb.ToString()); }
/*--------------------------------------------------------------------------------------------------------*/ /// <summary> /// For in statement. e.g. "where [ID] in ('1','2')" /// </summary> /// <param name="colName">column name</param> /// <param name="inFlag">true for in, false for not in</param> /// <param name="val_hs">list of values</param> private string Init_in(string colName, bool inFlag, HashSet <string> val_hs) { if (string.IsNullOrEmpty(colName)) { return(null); } if (val_hs == null) { return(null); } StringBuilder list_sb = new StringBuilder(); foreach (string str in val_hs) { if (str == null) { continue; } string val_str = str.Replace("'", "''"); list_sb.Append("'").Append(val_str).Append("'").Append(','); } HssStr.RemoveLastChar(list_sb, ','); StringBuilder sql_sb = new StringBuilder(); sql_sb.Append('[').Append(colName.Replace("]", "]]")).Append(']'); sql_sb.Append(' '); if (inFlag) { sql_sb.Append("in"); } else { sql_sb.Append("not in"); } sql_sb.Append(' '); sql_sb.Append('(').Append(list_sb).Append(')'); return(sql_sb.ToString()); }
/// <summary> /// Create sharedStrings.xml /// </summary> private void Create_SharedString_fromDic(Dictionary <string, int> dic, string fileName) { if (string.IsNullOrEmpty(fileName) || dic == null) { return; } string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine + "<sst xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' " + "count='" + dic.Count + "' uniqueCount='" + dic.Count + "'>" + HssStr.WinNextLine; FileStream fs = new FileStream(fileName, FileMode.Create); byte[] header_bts = Encoding.UTF8.GetBytes(header_str); fs.Write(header_bts, 0, header_bts.Length); StringBuilder sb = new StringBuilder(); int count = 0; foreach (string s in dic.Keys) { sb.Append("<si><t>" + HssStr.ToSafeXML_str(s) + "</t></si>" + HssStr.WinNextLine); if (++count % HssRawExcel.OneTimeAmount == 0) { byte[] arr = Encoding.UTF8.GetBytes(sb.ToString()); fs.Write(arr, 0, arr.Length); sb.Clear(); } ++this.hLog.RecordNum; } if (sb.Length > 0) { byte[] arr = Encoding.UTF8.GetBytes(sb.ToString()); fs.Write(arr, 0, arr.Length); } byte[] tail_bts = Encoding.UTF8.GetBytes("</sst>"); fs.Write(tail_bts, 0, tail_bts.Length); fs.Close(); }
private StringBuilder Get_selectCol_sb(HashSet <ColumnTemplate> hs) { StringBuilder col_sb = new StringBuilder(); if (hs == null || hs.Count == 0) { return(col_sb); } foreach (ColumnTemplate ct in hs) { string colName = "[" + ct.ColName.Replace("]", "]]") + "]"; string alia = ct.aliaName; if (ct.AggregateFunc == AggregateFunction.Sum) { if (string.IsNullOrEmpty(alia)) { alia = "sum_" + ct.ColName; } alia = "[" + alia.Replace("]", "]]") + "]"; col_sb.Append("Sum(").Append(colName).Append(") as ").Append(alia).Append(','); } else { if (string.IsNullOrEmpty(alia)) { col_sb.Append(colName).Append(','); } else { alia = "[" + alia.Replace("]", "]]") + "]"; col_sb.Append(colName).Append(" as ").Append(alia).Append(','); } } } HssStr.RemoveLastChar(col_sb, ','); return(col_sb); }
public string Create_dividendID(int dvdID_offset = 0) { if (dvdID_offset > 49 || dvdID_offset < 0) { dvdID_offset = 0; //only support offset range 0 ~ 49 for now } Depositary depo = null; int sponserID = 99; string depoID = "00"; if (this.Sponsored.Value) { sponserID = 0 + dvdID_offset; depo = DepositaryMaster.GetDepositary_by_name(this.Depositary.Value); } else { sponserID -= dvdID_offset; depo = DepositaryMaster.GetDepositary_by_name(Depositaries.Depositary.Unsponsored); } if (depo != null) { depoID = depo.DepositaryID.Value; } StringBuilder dvdID_sb = new StringBuilder(); dvdID_sb.Append(HssStr.Trim_to_size(sponserID, 2));//sponsered or not dvdID_sb.Append(HssStr.Trim_to_size(depoID, 2)); dvdID_sb.Append(HssStr.Trim_to_size(this.CUSIP.Value, 9)); dvdID_sb.Append(this.RecordDate_ADR.Value.ToString("MMddyyyy")); dvdID_sb.Append(HssStr.Trim_to_size(this.IncomeEventID.Value, 2, '0')); return(dvdID_sb.ToString()); }
/// <summary> /// Map header_hs values to Rate_Position_1,2,3... in "Dividend_DTC_Position" table /// Using [SortOrder] as reference to get the value /// </summary> /// <param name="header_hs">column header names in dataTable from excel</param> /// <returns>Dictionary with [Table_Column] index as key</returns> public Dictionary <int, List <string> > Get_colHeader_mapping(HashSet <string> header_hs) { Dictionary <int, List <string> > mapping = new Dictionary <int, List <string> >(); if (header_hs == null) { return(mapping); } //Rate_Position_1,2,3 as key Dictionary <int, DTC_Position_Headers> dic = new Dictionary <int, DTC_Position_Headers>(); foreach (DTC_Position_Headers mi in this.all_headers_list) { dic[mi.TableCol_index] = mi; } List <DTC_Position_Headers> distinct_TC_list = new List <DTC_Position_Headers>();//we may have duplicate [Table_Column] in [DTC_Position_Headers] foreach (DTC_Position_Headers mi in dic.Values) { distinct_TC_list.Add(mi); } distinct_TC_list.Sort((m1, m2) => (m1.SortOrder.Value - m2.SortOrder.Value));//excel file is created based on sort order string header_keyWord = "Option"; foreach (string header in header_hs) { List <string> word_list = HssStr.Str_to_wordList(header); if (word_list.Count < 2) { continue; } if (!word_list[0].Equals(header_keyWord, StringComparison.OrdinalIgnoreCase)) { continue; } int index = -1; if (!int.TryParse(word_list[1], out index)) { continue; } if (index <= 0 || index > distinct_TC_list.Count) { continue; } else { --index; } int keyVal = distinct_TC_list[index].TableCol_index; if (mapping.ContainsKey(keyVal)) { mapping[keyVal].Add(header); } else { List <string> tempList = new List <string>(); tempList.Add(header); mapping[keyVal] = tempList; } } return(mapping); }
/// <summary> /// Create worksheets\sheet1.xml, sheet2.xml, sheet3.xml... /// </summary> private void Create_Sheet_fromDT(DataTable dt, string fileName, Dictionary <string, int> dic) { if (string.IsNullOrEmpty(fileName) || dt == null || dic == null) { return; } string header_str = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" + HssStr.WinNextLine + "<worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main'><sheetData>" + HssStr.WinNextLine; FileStream fs = new FileStream(fileName, FileMode.Create); byte[] header_bts = Encoding.UTF8.GetBytes(header_str); fs.Write(header_bts, 0, header_bts.Length); int colCount = dt.Columns.Count; //data columns StringBuilder col_sb = new StringBuilder("<row r='1'>").Append(HssStr.WinNextLine); for (int c = 0; c < colCount; ++c) { DataColumn col = dt.Columns[c]; string val_str = col.ColumnName; if (string.IsNullOrEmpty(val_str)) { continue; } if (!dic.ContainsKey(val_str)) { continue; } int sharedStrID = dic[val_str]; string colHeader_str = HssStr.Excel_ID_to_Title(c); col_sb.Append("<c r='" + colHeader_str + "1' t='s'><v>"); col_sb.Append(sharedStrID); col_sb.Append("</v></c>").Append(HssStr.WinNextLine); ++this.hLog.RecordNum; } col_sb.Append("</row>").Append(HssStr.WinNextLine); byte[] col_arr = Encoding.UTF8.GetBytes(col_sb.ToString()); fs.Write(col_arr, 0, col_arr.Length); //data rows StringBuilder row_sb = new StringBuilder(); for (int i = 0, r = 2; i < dt.Rows.Count; ++r, ++i) { DataRow row = dt.Rows[i]; StringBuilder sb = new StringBuilder("<row r='" + r + "'>").Append(HssStr.WinNextLine); for (int c = 0; c < colCount; ++c) { string val_str = row[c].ToString(); if (string.IsNullOrEmpty(val_str)) { continue; } if (!dic.ContainsKey(val_str)) { continue; } int sharedStrID = dic[val_str]; string colHeader_str = HssStr.Excel_ID_to_Title(c); sb.Append("<c r='" + colHeader_str + r + "' t='s'><v>"); sb.Append(sharedStrID); sb.Append("</v></c>").Append(HssStr.WinNextLine); } sb.Append("</row>").Append(HssStr.WinNextLine); row_sb.Append(sb); if (r % HssRawExcel.OneTimeAmount == 0) { byte[] arr = Encoding.UTF8.GetBytes(row_sb.ToString()); fs.Write(arr, 0, arr.Length); row_sb.Clear(); } ++this.hLog.RecordNum; } if (row_sb.Length > 0) { byte[] arr = Encoding.UTF8.GetBytes(row_sb.ToString()); fs.Write(arr, 0, arr.Length); } //last part byte[] tail_bts = Encoding.UTF8.GetBytes("</sheetData></worksheet>"); fs.Write(tail_bts, 0, tail_bts.Length); fs.Close(); }
public SqlCommand GetSQL_cmd() { if (this.template == null) { Console.WriteLine("DB_update error 0: no template"); return(null); } if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName)) { Console.WriteLine("DB_update error 1: no name"); return(null); } if (col_val_dic.Count < 1) { Console.WriteLine("DB_update error 2: no column"); return(null); } if (!this.ExistCondition()) { if (MessageBox.Show("No condition in update SQL", "Warning", MessageBoxButtons.YesNo) != DialogResult.Yes) { return(null); } } StringBuilder sql_sb = new StringBuilder("Update "); sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]"); sql_sb.Append("."); sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]"); sql_sb.Append("."); sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "]"); sql_sb.Append(" set "); StringBuilder val_sb = new StringBuilder(); SqlCommand cmd = new SqlCommand(); int tempID = 0; foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic) { string colName = pair.Key.Replace("]", "]]"); string valName = "@Val" + tempID; val_sb.Append("[").Append(colName).Append("]"); val_sb.Append(" = "); val_sb.Append(valName).Append(", "); SqlParameter val_sp = new SqlParameter(); val_sp.Value = pair.Value.Value; val_sp.SqlDbType = pair.Value.SqlDbType; val_sp.ParameterName = valName; cmd.Parameters.Add(val_sp); ++tempID; } HssStr.RemoveLastChar(val_sb, ','); sql_sb.Append(val_sb); if (this.ExistCondition()) { sql_sb.Append(" where ").Append(this.condition.SQL_str); } cmd.CommandText = sql_sb.ToString(); return(cmd); }