/// <summary> /// 菜单列表项 /// </summary> /// <param name="menutb">level=0的一级菜单</param> /// <param name="subMenutb">level=1的二级菜单</param> /// <param name="parentMenutb">isparent=1的具有二级菜单的一级菜单</param> public static void GetMenuItemBLL(out DataTable menutb,out DataTable subMenutb,out DataTable parentMenutb) { DataTable dt = Front_DataCollectorDAL.GetMenuItemDAL(); menutb = dt.Clone();//存放level=0的1层菜单 subMenutb = dt.Clone();//存放level=1的子菜单 parentMenutb = dt.Clone();//存放isparent=1的1层菜单 if (dt!=null && dt.Rows.Count>0) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; if (dr["menu_level"].ToString()=="0") { menutb.ImportRow(dr); if (dr["menu_isparent"].ToString()=="1") { parentMenutb.ImportRow(dr); } } else if(dr["menu_level"].ToString()=="1") { subMenutb.ImportRow(dr); } } } }
public DataSet GetModelHtmlValue(string ChId, string Id) { int MyChId = int.Parse(ChId); ChannelModel = MyChId <= 0 ? null : ChannelBll.GetChannel(MyChId); MInfoModel = BInfoModel.GetModel(ChannelModel.ModelType); DataTable dt=new DataTable(); DataRow dr = BInfoOper.GetInfo(MInfoModel.TableName, int.Parse(Id)); dt = dr.Table.Copy(); dt.Clear(); dt.ImportRow(dr); dt.TableName = "DrInfo"; DataSet ds = new DataSet(); try { ds.Tables.Add(BModelField.GetList(ChannelModel.ModelType).Copy()); ds.Tables.Add(dt); } catch(Exception ex) { Response.Write(ex); } return ds; }
public static void ImportRowToDataTable(ref DataTable dt, DataRow[] rows) { foreach (DataRow r in rows) { dt.ImportRow(r); } }
public FlawForm(DataRow flaw, Dictionary<string, NMap.Model.Unit> currentUnitList) { InitializeComponent(); // Initialize datatable struct _flawData = new DataTable(); _flawData.Columns.Add("FlawID", typeof(string)); _flawData.Columns.Add("FlawType", typeof(int)); _flawData.Columns.Add("FlawClass", typeof(string)); _flawData.Columns.Add("Area", typeof(string)); _flawData.Columns.Add("CD", typeof(decimal)); _flawData.Columns.Add("MD", typeof(decimal)); _flawData.Columns.Add("Width", typeof(decimal)); _flawData.Columns.Add("Length", typeof(decimal)); _flawData.ImportRow(flaw); _drFlaw = _flawData.Rows[0]; _currentCdConversion = currentUnitList["FlawMapCD"].Conversion; _currentMdConversion = currentUnitList["FlawMapMD"].Conversion; _currentUnitList = currentUnitList; _drFlaw["CD"] = Convert.ToDecimal(_drFlaw["CD"]) / _currentCdConversion; _drFlaw["MD"] = Convert.ToDecimal(_drFlaw["MD"]) / _currentMdConversion; DataHelper dh = new DataHelper(); _imageList = dh.GetFlawImageFromDb(_drFlaw); }
public DataTable ReadUserInfo(string str) { if (bll.GetCookie() == null) { return null; } else { int UserId = bll.GetCookie().UserID; DataTable dt = new DataTable(); try { dr = bll.GetUserAllInfo(UserId); dt = dr.Table.Copy(); dt.Clear(); dt.ImportRow(dr); } catch (Exception ex) { Response.Write(ex); } return dt; } }
protected void Page_Load(object sender, EventArgs e) { #region Check Login CheckStateAdminSession(); #endregion Check Login #region Initialize Values and Form Execution DataTable table = (DataTable)Session["StudentList"]; studentList = table.Clone(); int startIndex = Convert.ToInt32(CareerCruisingWeb.CCLib.Common.Strings.GetFormString("Start")); int endIndex = Convert.ToInt32(CareerCruisingWeb.CCLib.Common.Strings.GetFormString("End")); // select occupations from above list that correspond to cluster code passed for (int counter = startIndex; counter <= endIndex; counter++) { studentList.ImportRow(table.Rows[counter]); } if (studentList.Rows.Count > 0) rptMassPrintPLPSignOff.DataBind(); #endregion Initialize Values and Form Execution #region Properties For The State Base Class HasTopHeader = false; #endregion Properties For The State Base Class }
private void FenYe(System.Data.DataTable dataTable) { // if (oo != -1) // { // num = oo; // } System.Data.DataTable dt = dataTable.Clone(); //复制原数据的结构 rows = dataTable.Rows.Count; //总行数 sum = rows % ii == 0 ? rows / ii : (rows / ii) + 1; //总页数=总行数/每页的行数(如果除不尽则+1) if (sum == 0) { sum++; } int ks = (num * ii) - ii; //开始行数(当前页数*每页的行数)-每页的行数 int js = num * ii; //结束行数 if (num == sum) { js = ks + (rows - ((num - 1) * ii)); //当最后一页时,结束的行数 = 开始行数 + (总行数 - ((总页数-1) * 每页的行数)) } //实现分页 if (rows != 0) //必须要有一条记录 { for (int i = ks; i < js; i++) { dt.ImportRow(dataTable.Rows[i]); //复制行 } } label6.Text = num.ToString() + "/" + sum.ToString(); }
public static void Upload(this System.Data.DataTable dt, string tableName) { string query = "SELECT * from " + tableName; using (SqlConnection conn = new SqlConnection(SqlConnStr)) { using (SqlDataAdapter oda = new SqlDataAdapter()) { using (SqlCommandBuilder bu = new SqlCommandBuilder()) { using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn)) { oda.SelectCommand = new SqlCommand(query, conn); bulkcopy.DestinationTableName = "dbo." + tableName; DataTable dtsql = new DataTable(); oda.Fill(dtsql); List<DataRow> lst_temp = dt.AsEnumerable().ToList(); foreach (DataRow row in lst_temp) { dtsql.ImportRow(row); } conn.Open(); bulkcopy.WriteToServer(dtsql); conn.Close(); } } } } }
public static System.Data.DataTable GetDataTableDynamic(int AtSegment) { InitialPage(); if (AtSegment < _totalpages) { Segment = AtSegment * _itemsonpage; Offset = _itemsonpage; } else if (AtSegment == _totalpages) { Segment = (_parrity)? AtSegment * _itemsonpage : (AtSegment - 1) * _itemsonpage; Offset = _itemsonlastpage; } else { Segment = 0; Offset = _itemsonpage; } System.Data.DataTable DtableDynamic = tablestatic.Clone(); for (int i = Segment; i < Segment + Offset; i++) { if (i == _totalitems) { break; } DtableDynamic.ImportRow(tablestatic.Rows[i]); } _currentpages = AtSegment; return(DtableDynamic); }
private static DataTable ReorderDeptRow(DataTable dtReturn, int deptID, DataTable dtDept, int align) { //获取部门的子部门 DataRow[] drSubDept = dtDept.Select("SuperDeptID = " + deptID); //遍历所有子部门 for (int i = 0; i < drSubDept.Length; i++) { //通过对齐位置,来控制该部门前空格数 string alignPosition = string.Empty; for (int j = 0; j < align; j++) { alignPosition += " "; } //获取子部门数据 DataRow drSubDeptTemp = (DataRow)drSubDept[i]; //获取子部门ID int subDeptID = (int)drSubDeptTemp["ID"]; drSubDeptTemp["DeptName"] = alignPosition + drSubDeptTemp["DeptName"].ToString(); //导入子部门 dtReturn.ImportRow(drSubDeptTemp); //生成子部门的子部门信息 dtReturn = ReorderDeptRow(dtReturn, subDeptID, dtDept, align + 1); } return dtReturn; }
/// <summary> /// Obtem todos atendimentos a partir do Ultimo Registro. /// </summary> /// <param name="m_iQtdeRegistros">Quantidade de Registro</param> /// <returns></returns> public void ProcessarAtendimentosHIS(Int32 m_iQtdeRegistros) { string m_sUltimoRegistro; DataSet DsDados = new DataSet(); ServiceHBD.WSLerAtendimentosSoapClient DadosOrigens = new WSLerAtendimentosSoapClient(); //Obtem o Ultimo Registro m_sUltimoRegistro = DadosOrigens.RetornarUltimoRegistro(mUnidade); //Obtem as Informações DsDados = DadosOrigens.RetornarAtendimentos(m_sUltimoRegistro, m_iQtdeRegistros); //Gravar no DWSATELITE for (int i = 0; i < DsDados.Tables[0].Rows.Count; i++) { DataRow Dr0 = DsDados.Tables[0].Rows[i]; DataSet Ds = new DataSet(); DataTable Dt = new DataTable(DsDados.Tables[0].TableName); Dt = DsDados.Tables[0].Clone(); Dt.ImportRow(Dr0); Ds.Tables.Add(Dt); DadosOrigens.GravarAtendimento(mUnidade, Ds); //m_oDataSet = Ds; //Salvar(); } }
private void 打开ToolStripMenuItem_Click(object sender, EventArgs e) { openFileDialog1.Filter = "CSV文件|*.CSV"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { string fileName = openFileDialog1.FileName; System.Data.DataTable newDataTable; newDataTable = csvHelper.CSVToDataTable(fileName); m_GradeTable.Clear(); foreach (DataRow row in newDataTable.Rows) { m_GradeTable.ImportRow(row); } toolStripStatusLabel1.Text = (dataGridView1.RowCount - 1).ToString() + "行"; MessageBox.Show("成功显示CSV数据!"); } }
private static void AppendData(DataTable source, ref DataTable target) { if (target == null) target = source.Clone(); foreach (DataRow row in source.Rows) target.ImportRow(row); }
/// <summary> /// 处理DataTable,把DataTable里面的true 或 false 换成文字 /// </summary> /// <param name="dt">要处理的DataTable</param> /// <returns>处理后的DataTable</returns> private System.Data.DataTable ProcessDataTable(System.Data.DataTable dt) { try { if (dt != null && dt.Rows.Count > 0) { bool flag = false; System.Data.DataTable dtClone = dt.Clone(); foreach (DataColumn dc in dtClone.Columns) { if (dc.DataType.Name.ToLower() == "boolean") { dc.DataType = typeof(string); flag = true; } } if (flag == true) { //复制数据到克隆的datatable里 try { foreach (DataRow dr in dt.Rows) { dtClone.ImportRow(dr); } } catch { } dt = dtClone; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j].ToString().ToLower() == "true") { dt.Rows[i][j] = "是"; } else if (dt.Rows[i][j].ToString().ToLower() == "false") { dt.Rows[i][j] = ""; } } } } return(dt); } catch (Exception e) { if (!e.Message.Equals("正在中止线程。")) { MessageBox.Show("发生错误,错误信息[" + e.Message + "]", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(null); } }
protected void Page_Load(object sender, EventArgs e) { #region Properties For The Base Class and Check Login LoginID = Request.QueryString["LoginID"]; LoginInfo = CareerCruisingWeb.CCLib.Login.GetLoginInfo(LoginID); Redirect(); PageTitle = TextCode(5672); Section = "connect"; CSS = "connect"; LeftBar = "<TABLE WIDTH='100%' BORDER='0' CELLSPACING='0' CELLPADDING='0'><TR VALIGN='TOP' BGCOLOR='#000000'><TD BACKGROUND='/media/connect/i_c_indigo_bar_bg.gif'><IMG SRC='/media/connect/i_c_connect_icon.gif' WIDTH='24' HEIGHT='23' BORDER='0' alt=''><IMG SRC='/media1/Connect/ClientBranding/Shared/h_network_" + NWBrandingNamePic + SuffixCode() + ".gif' WIDTH='141' HEIGHT='23' ALIGN='TOP' alt='" + NWBrandingShortName + "'>"; ucHeader.strTitle = TextCode(5677); #endregion Properties For The Base Class and Check Login #region Get related careers string jobInfoTableName = "Jobinfo" + SuffixCountryLanguageCode(AbbreviatedCountryCode()); string tblClusterCareer = "Clusters" + ConSysInfo["ConSysCountry"].ToString(); strClusterID = Request.QueryString["cID"]; if ((LoginID != "")&&(strClusterID!="")&&(ConSysID!="")) { dtClusterInfo = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable("select ClusterType,ClusterCode,ClusteName" + NonEngSuffixCode() + " from ClusterCodes where ClusterID=" + strClusterID); if (dtClusterInfo.Rows.Count > 0) { strSelect = dtClusterInfo.Rows[0]["ClusteName" + NonEngSuffixCode()].ToString(); strClusterCode = dtClusterInfo.Rows[0]["ClusterCode"].ToString(); } strSQL = "SELECT DISTINCT ji.OccNumber,ji.OccName,ji.OneDesc, ic." + ConSysInfo["ClusterType"].ToString() + " FROM " + jobInfoTableName + " as ji inner join " + tblClusterCareer + " as ic on ji.OccNumber=ic.OccNumber inner join Con_CareerCoaches as cc on ji.OccNumber=cc.OccNumber "; strSQL += " inner join Con_PartnerUsers cp on cp.PartnerUserID=cc.PartnerUserID "; strSQL += " where (cc.IsApproved=1) and cp.ConSysID="+ConSysID+" and cp.PartnerStatusID=4 ORDER by ji.OccName "; dtCareerClusters = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQL); dtCareerSearchResults = new DataTable(); dtCareerSearchResults = dtCareerClusters.Clone(); // select occupations from above list that correspond to cluster code passed foreach (DataRow row in dtCareerClusters.Rows) { allClusterCodes = row[ConSysInfo["ClusterType"].ToString()].ToString().Split(','); foreach (string clusteCode in allClusterCodes) { if (clusteCode == strClusterCode) { dtCareerSearchResults.ImportRow(row); break; } } } uc1.dtCoachClusterResults = dtCareerSearchResults; uc1.LoginID = LoginID; uc1.UserLanguage = UserLanguage; uc1.PageT = "Cluster"; uc1.ConSysID = ConSysID; } #endregion }
private void Form7_Load(object sender, EventArgs e) { xmlFile = XmlReader.Create("Veiculo.xml", new XmlReaderSettings()); DataSet ds = new DataSet(); ds.ReadXml(xmlFile); dt = ds.Tables[0].Clone(); //Tabela recebe dados XML //Passando dados do DataSet para Tabela foreach (DataRow row in ds.Tables[0].Rows) { dt.ImportRow(row); } view = dt.DefaultView; //View recebe dados da tabela VeiculoBindingSource.DataSource = view; this.reportViewer1.RefreshReport(); #region Populando Filtro de Tipo de Veículo List<String> veiculos = new List<String>(); //Carregando o xml dos Trechos XElement xmlVeiculos = XElement.Load("Veiculo.xml"); //Populando a lista de cidade com o as cidadesA foreach (XElement x in xmlVeiculos.Elements()) { veiculos.Add(x.Element("TipoVeiculo").Value); } foreach (string vei in veiculos) { this.checkBoxcomboBox_RelVeiculos.Items.Add(vei); } #endregion #region Populando Filtro de Cargas List<String> cargas = new List<String>(); //Carregando o xml dos Trechos XElement xmlCargas = XElement.Load("Veiculo.xml"); //Populando a lista de cidade com o as cidadesA foreach (XElement x in xmlCargas.Elements()) { cargas.Add(x.Element("CargaMaxima").Value); } foreach (string car in cargas) { this.checkBoxComboBox2.Items.Add(car); } #endregion }
private DataTable GetNewDataTable(DataTable dt, string condition) { DataTable newdt = new DataTable(); newdt = dt.Clone(); DataRow[] dr = dt.Select(condition); for (int i = 0; i < dr.Length; i++) newdt.ImportRow((DataRow)dr[i]); return newdt; }
public SourceTableProvider(TableLink sourceTableLink, RowLink[] sourceRows) { this.sourceTableLink = sourceTableLink; //this.table = sourceTableLink.Table.Copy(); this.table = sourceTableLink.Table.Clone(); foreach (RowLink sourceRow in sourceRows) { table.ImportRow(sourceRow.DataRow); } }
/// 執行 DataTable 中的查詢返回新的 DataTable /// </summary> /// <param name="dt">來源資料 DataTable</param> /// <param name="condition">查詢條件</param> /// <returns></returns> public DataTable QueryDataTable(DataTable dt, string condition, string sortstr) { DataTable newdt = new DataTable(); newdt = dt.Clone(); DataRow[] dr = dt.Select(condition, sortstr); for (int i = 0; i < dr.Length; i++) { newdt.ImportRow((DataRow)dr[i]); } return newdt; }
private DataTable DtFilter(DataTable dtData, string strFilter) { DataTable dtTemp = new DataTable(); dtTemp = dtData.Clone(); DataRow[] dr = dtData.Select(strFilter); for (int i = 0; i < dr.Length; i++) { dtTemp.ImportRow((DataRow)dr[i]); } return dtTemp;//返回的查询结果 }
private static DataTable CloneTable(DataTable dt) { System.Data.DataTable temporaryHoldingTable = new System.Data.DataTable(); temporaryHoldingTable = dt.Clone(); temporaryHoldingTable.Columns[dt.Columns.Count - 1].DataType = typeof(double); foreach (DataRow row in dt.Rows) { temporaryHoldingTable.ImportRow(row); } return temporaryHoldingTable; }
private DataTable specialDataSort(DataTable dt) { DataTable sortedDt = dt.Clone(); string Fac = null; dt.AcceptChanges(); foreach (DataRow row in dt.Rows) { if (row.RowState != DataRowState.Deleted) { string newFac = row[dalMac.MacLocation].ToString(); if (Fac != newFac) { foreach (DataRow row2 in dt.Rows) { if (row2.RowState != DataRowState.Deleted) { string FacSearch = row2[dalMac.MacLocation].ToString(); if (Fac == FacSearch) { DataRow newRow2 = sortedDt.NewRow(); newRow2 = row2; sortedDt.ImportRow(newRow2); row2.Delete(); } } } } DataRow newRow = sortedDt.NewRow(); newRow = row; sortedDt.ImportRow(newRow); row.Delete(); Fac = newFac; } } return(sortedDt); }
// String Variabllera göre listeleyen fonksiyon// coloumn index gerekli public DataTable auctionListByVariable(string Variable, int ColoumnIndex) { DataTable tabl2 = new DataTable(); tabl2 = auctionTable.Clone(); for (int i = 0; i < auctionTable.Rows.Count; i++) { if (auctionTable.Rows[i][ColoumnIndex].ToString().StartsWith(Variable)) { tabl2.ImportRow(auctionTable.Rows[i]); } } return tabl2; }
public DataTable chequeListByChecked(bool check,int ColoumnIndex) { DataTable tabl4=new DataTable(); tabl4=table.Clone(); for (int i = 0; i < table.Rows.Count; i++) { if (Convert.ToBoolean(table.Rows[i][ColoumnIndex])==check) { tabl4.ImportRow(table.Rows[i]); } } return tabl4; }
public DataTable productBuyTableByVariable(string Variable, int ColoumnIndex) { DataTable table6=new DataTable(); table6 = productBuyTable.Clone(); for (int i = 0; i < productBuyTable.Rows.Count; i++) { if (productBuyTable.Rows[i][ColoumnIndex].ToString().StartsWith(Variable)) { table6.ImportRow(productBuyTable.Rows[i]); } } return table6; }
// String Variabllera göre listeleyen fonksiyon// coloumn index gerekli public DataTable chargeListByVariable(string Variable, int ColoumnIndex) { DataTable tabl2 = new DataTable(); tabl2 = tablecharge3.Clone(); for (int i = 0; i < tablecharge3.Rows.Count; i++) { if (tablecharge3.Rows[i][ColoumnIndex].ToString().StartsWith(Variable)) { tabl2.ImportRow(tablecharge3.Rows[i]); } } return tabl2; }
private DataSet GenerateExcelData(string fileName) { Console.WriteLine("generate excel data started"); DataSet projectDs = new DataSet(); DataTable projectTable = new DataTable(); OleDbConnection oledbConn = null; try { if (!File.Exists(fileName)) throw new Exception("No excel input file specified"); oledbConn = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"")); oledbConn.Open(); OleDbCommand cmd = new OleDbCommand(); ; OleDbDataAdapter oleda = new OleDbDataAdapter(); DataSet ds = new DataSet(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from [Sheet1$]"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds, "Project"); var groups = ds.Tables[0].AsEnumerable().GroupBy(t => t.Field<string>("Project Name")); int i = 0; foreach (var group in groups) { projectTable = ds.Tables[0].Clone(); projectTable.TableName = "Project" + i++; var rowList = ds.Tables[0].AsEnumerable().Where(t => t.Field<string>("Project Name") == group.Key); //If excel data contains TASK ID sort all the tasks by TASK ID if (DataSetBuilder.Mapping.TaskMap.ContainsKey("TASK_ID") && rowList.CopyToDataTable().Columns.Contains(DataSetBuilder.Mapping.TaskMap["TASK_ID"])) { rowList = rowList.OrderBy(t => t.Table.Columns[DataSetBuilder.Mapping.TaskMap["TASK_ID"]]); } foreach (DataRow row in rowList) projectTable.ImportRow(row); projectDs.Tables.Add(projectTable); } return projectDs; } // need to catch possible exceptions catch (Exception ex) { throw ex; } finally { oledbConn.Close(); } Console.WriteLine("generate excel data done successfully"); }
/// <summary> /// 导入所有父行(包括自己) /// </summary> private static void ImportParentRow(DataTable dtSource, DataTable dtSlt, DataRow currentRow) { if (!dtSlt.Rows.Contains(currentRow["ID"])) //不存在则导入行 { dtSlt.ImportRow(currentRow); } if (!string.IsNullOrEmpty(currentRow["PARENT_ID"] + "")) //如果还有父项 { DataRow row = dtSource.Select(string.Format("ID='{0}'", currentRow["PARENT_ID"]))[0]; ImportParentRow(dtSource, dtSlt, row); } }
public static string FillExpGrid(int PageNumber, int PageSize, string filter) { if (HttpContext.Current.Session["UserName"] == null) { return "Error: You are not logged-in."; } DBClass db = new DBClass("SCM"); db.Con.Open(); if (String.IsNullOrEmpty(filter)) db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] ORDER BY [id]"; else db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] where Branchid = '"+filter+"' ORDER BY [id]"; SqlDataAdapter da = new SqlDataAdapter(db.Com); DataTable dt = new DataTable(); da.Fill(dt); DataTable paggedtable = new DataTable(); paggedtable = dt.Clone(); int initial = (PageNumber - 1) * PageSize + 1; int last = initial + PageSize; for (int i = initial - 1; i < last - 1; i++) { if (i >= dt.Rows.Count) { break; } paggedtable.ImportRow(dt.Rows[i]); } System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); Dictionary<string, object> row; foreach (DataRow dr in paggedtable.Rows) { row = new Dictionary<String, Object>(); foreach (DataColumn col in paggedtable.Columns) { row.Add(col.ColumnName, dr[col]); } rows.Add(row); } db.Con.Close(); db = null; return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows)); }
/// <summary> /// ��ȡ���ڵ���ΪparentId�����нڵ㣬����DataTable���� /// </summary> /// <param name="parentId">���ڵ���</param> /// <returns>DataTable��ʽ�����к��ӽڵ�����</returns> private DataTable GetChilds(int parentId) { DataTable childNodes = new DataTable(); childNodes = this._dataTable.Clone(); foreach (DataRow dr in this._dataTable.Rows) { if (Convert.ToInt32(dr["ParentId"]) == parentId) { childNodes.ImportRow(dr); } } return childNodes; }
private void button3_Click(object sender, EventArgs e) { DataTable list3 = new DataTable(); string importtext = textBox1.Text; list3 = list2.Clone(); DataRow[] dr = list2.Select("dataname like '%" + importtext + "%'"); for (int i = 0; i < dr.Length; i++) { list3.ImportRow(dr[i]); } listBox2.DataSource = list3; listBox2.DisplayMember = "dataname"; listBox2.ValueMember = "quandlcode"; }
public void BindDropDownList() { string query = "(&(objectCategory=person))"; string[] columns = new string[] { "displayName", "sAMAccountName", "userAccountControl" }; string ldapPath = "LDAP://dc=classroom,dc=org"; DataSet ds = (new LdapAuthentication(ldapPath)).FindUsers(query, columns, ldapPath, true); DataTable users = new DataTable(); foreach (string prop in columns) { users.Columns.Add(prop, typeof(string)); } string[] exclude = new string[] { "sh1pryor", "sh1lalley", "sh1smith", "sh1curtis", "sh1bailey", "sh1russell", "sh1morris", "sh1patterson", "sh1barron", "sh1vanfrank", "sh1bush", "sh1twilson", "sh1fqureshi", "sh1brunt", "sh1larsen", "sh1boren", "sh1tqureshi", "sh1garrone", "sh1swanson", "sh1jackson", "sh1vaughn", "sh1rnew", "testjl", "Members", "training", "sqlalerts", "exam", "sonictest", "impact", "commview", "ach", "ittest", "teachmoney", "hrtest", "helpdesk", "ibmdirector", "ittestaccount", "dosstest", "stapler", "Mastercard", "Test1", "ipads", "oci", "ULTEST", "TellerTest", "mshift" }; foreach (DataTable dt in ds.Tables) { foreach (DataRow dr in dt.Rows) { if (!exclude.Contains(dr["sAMAccountName"])) { if (dr["sAMAccountName"].ToString() != _user && (dr["userAccountControl"].ToString() == "512" || dr["userAccountControl"].ToString() == "1114624")) { users.ImportRow(dr); } } } } DataView dvSelectOUUsers = users.DefaultView; dvSelectOUUsers.Sort = "displayName ASC"; selectOUUsers.DataSource = users; selectOUUsers.DataTextField = "displayName"; selectOUUsers.DataValueField = "sAMAccountName"; selectOUUsers.DataBind(); }
public void ShowFor(Receipt par, String ItemName, decimal SKU, DataTable SuggestedComposition, String Batch) { lblItemName.Text = ItemName; lblTotalSKUs.Text = SKU.ToString(); lblBatchNumber.Text = Batch; suggestion = SuggestedComposition.Clone(); totalSKU = SKU; foreach (DataRow d in SuggestedComposition.Rows) { suggestion.ImportRow(d); } gridControl1.DataSource = suggestion; instance = par; this.ShowDialog(par); }
public DataTable productBuyTableByDate(DateTime date1,DateTime date2, int ColoumnIndex) { DataTable table7 = new DataTable(); table7 = productBuyTable.Clone(); for (int i = 0; i < productBuyTable.Rows.Count; i++) { if (date1 <= Convert.ToDateTime(productBuyTable.Rows[i][ColoumnIndex])) { if (Convert.ToDateTime(productBuyTable.Rows[i][ColoumnIndex]) <= date2) { table7.ImportRow(productBuyTable.Rows[i]); } } } return table7; }
public DataTable cashListByDate(DateTime date1,DateTime date2,int ColoumnIndex) { DataTable tabl3 = new DataTable(); tabl3=cashTable.Clone(); for (int i = 0; i < cashTable.Rows.Count; i++) { if (date1<=Convert.ToDateTime(cashTable.Rows[i][ColoumnIndex])) { if (Convert.ToDateTime(cashTable.Rows[i][ColoumnIndex])<=date2) { tabl3.ImportRow(cashTable.Rows[i]); } } } return tabl3; }
public System.Data.DataTable FiltraDataTable(System.Data.DataTable dtprincipal, string ctrFiltro) { /* Seleccionamos las filas que se correspondan con el identificador */ System.Data.DataRow[] rows = dtprincipal.Select(ctrFiltro); /* Clonamos la estructura del objeto DataTable principal */ System.Data.DataTable dt1 = dtprincipal.Clone(); // Importamos los registros al nuevo DataTable foreach (DataRow row in rows) { dt1.ImportRow(row); } return(dt1); }
private System.Data.DataTable FilterComprobantes(string comprobantesCuentaCorriente, System.Data.DataTable tableParam) { System.Data.DataTable table = new System.Data.DataTable(); table.Columns.Add("IdTipoDeComprobante"); table.Columns.Add("Descripcion"); ArrayList aux = mz.erp.systemframework.Util.Parse(comprobantesCuentaCorriente, ","); foreach (System.Data.DataRow row in tableParam.Rows) { if (aux.Contains(row["IdTipoDeComprobante"])) { table.ImportRow(row); } } return(table); }
private void ListData() { DataTable dt = new DataTable(); Koneksi koneksi = new Koneksi(); dt = koneksi.tampil_data_dw("SELECT pemb_id as 'PEMBIAYAAN ID',pemb_kd_bidang as 'KODE BIDANG',pemb_keterangan as 'KETERANGAN' FROM dim_pembiayaan"); try { paging_PageIndex = 1; if (dt.Rows.Count > 0) { DataTable tmpTable = new DataTable(); tmpTable = dt.Clone(); if (dt.Rows.Count >= paging_NoOfRecPerPage) { for (int i = 0; i < paging_NoOfRecPerPage; i++) { tmpTable.ImportRow(dt.Rows[i]); } } else { for (int i = 0; i < dt.Rows.Count; i++) { tmpTable.ImportRow(dt.Rows[i]); } } listDatadimensi.DataContext = tmpTable.DefaultView; tmpTable.Dispose(); dt_TempData = dt; } else { // MessageBox.Show("Message"); } } catch (Exception ex) { MessageBox.Show("Error Message" + ex); } } //void closing
private System.Data.DataTable ShowData(int pageNumber) { System.Data.DataTable dt = new System.Data.DataTable(); int startIndex = PageSize * (pageNumber - 1); var result = DataSource.AsEnumerable().Where((s, k) => (k >= startIndex && k < (startIndex + PageSize))); foreach (DataColumn colunm in DataSource.Columns) { dt.Columns.Add(colunm.ColumnName); } foreach (var item in result) { dt.ImportRow(item); } grd_order.Rows.Clear(); foreach (var item in result) { // grd_order.Rows.Add(item); //grd_order.Rows[i].Cells[1].Value = dtselect.Rows[i]["Client_Order_Number"].ToString(); //grd_order.Rows[i].Cells[2].Value = dtselect.Rows[i]["Client_Name"].ToString(); //grd_order.Rows[i].Cells[3].Value = dtselect.Rows[i]["Sub_ProcessName"].ToString(); //grd_order.Rows[i].Cells[4].Value = dtselect.Rows[i]["Date"].ToString(); //grd_order.Rows[i].Cells[5].Value = dtselect.Rows[i]["Client_Order_Ref"].ToString(); //grd_order.Rows[i].Cells[6].Value = dtselect.Rows[i]["Order_Type"].ToString(); //grd_order.Rows[i].Cells[7].Value = dtselect.Rows[i]["Borrower_Name"].ToString();//BArrower Namr //grd_order.Rows[i].Cells[8].Value = dtselect.Rows[i]["Address"].ToString(); //grd_order.Rows[i].Cells[9].Value = dtselect.Rows[i]["County"].ToString(); //grd_order.Rows[i].Cells[10].Value = dtselect.Rows[i]["State"].ToString(); //grd_order.Rows[i].Cells[11].Value = dtselect.Rows[i]["County_Type"].ToString(); //grd_order.Rows[i].Cells[12].Value = dtselect.Rows[i]["Date"].ToString();//Process date Should be Completed or Recived date //grd_order.Rows[i].Cells[13].Value = dtselect.Rows[i]["Order_Status"].ToString(); //grd_order.Rows[i].Cells[14].Value = dtselect.Rows[i]["Progress_Status"].ToString(); //grd_order.Rows[i].Cells[15].Value = dtselect.Rows[i]["User_Name"].ToString(); //grd_order.Rows[i].Cells[16].Value = dtselect.Rows[i]["ORDER_PRIORITY_STATUS"].ToString(); //grd_order.Rows[i].Cells[17].Value = "";//Client TAT //grd_order.Rows[i].Cells[18].Value = "";//EMP TAT //grd_order.Rows[i].Cells[19].Value = dtselect.Rows[i]["Order_ID"].ToString(); //grd_order.Rows[i].Cells[20].Value = "Delete"; } //txtPaging.Text = string.Format("Page {0} Of {1} Pages", pageNumber, (DataSource.Rows.Count / PageSize) + 1); return(dt); }
private bool obtieneDatosDeProducto() { if ((textBoxClave.Text == "") || (textBoxClave.Text == null)) { MessageBox.Show("Introduce una clave de producto"); return(false); } try { System.Data.DataTable foundRowsTable = new System.Data.DataTable(); DataRow[] foundRows; foundRowsTable = new System.Data.DataTable(); foundRowsTable = tablaEspecificaciones.Clone(); foundRows = tablaEspecificaciones.Select("clave like " + "'" + textBoxClave.Text + "' AND clave not like '%OBSOLETO%'"); foreach (DataRow row in foundRows) { foundRowsTable.ImportRow(row); } if (foundRowsTable.Rows.Count == 1) { textBoxClave.Text = foundRowsTable.Rows[0]["clave"].ToString(); textBoxCliente.Text = foundRowsTable.Rows[0]["cliente"].ToString(); textBoxCalibre.Text = foundRowsTable.Rows[0]["calibre"].ToString(); textBoxMaterial.Text = foundRowsTable.Rows[0]["material"].ToString(); textBoxColor.Text = foundRowsTable.Rows[0]["color"].ToString(); textBoxPerfil.Text = foundRowsTable.Rows[0]["perfil"].ToString(); textBoxCorte.Text = foundRowsTable.Rows[0]["corte"].ToString(); return(true); } else { MessageBox.Show("Clave no encontrada"); return(false); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(false); } }
private void LoadData() { int nStartPos = 0; //当前页面开始记录行 int nEndPos = 0; //当前页面结束记录行 DataSet ds; ds = new projectBAL().Select(filetype, pId, gdmethod); DataTable dt = ds.Tables[0]; System.Data.DataTable dtTemp = dt.Clone(); //克隆DataTable结构框架 if (dtTemp != null || dtTemp.Rows.Count > 0) { if (pageCurrent == pageCount) { nEndPos = nMax; } else { nEndPos = pageSize * pageCurrent; } nStartPos = nCurrent; lblPageCount.Text = pageCount.ToString(); //在bdnInfo中添加lable记录总页数 txtCurrentPage.Text = Convert.ToString(pageCurrent); //在bdnInfo中添加Textbox记录当前页数 //从元数据源复制记录行 for (int i = nStartPos; i < nEndPos; i++) { if (i < dt.Rows.Count) { dtTemp.ImportRow(dt.Rows[i]); nCurrent++; } } bdsInfo.DataSource = dtTemp; bdnInfo.BindingSource = bdsInfo; dataGridView1.DataSource = bdsInfo; } else { return; } }
//condences datatable one in order to export it into an excel spreadsheet public static void ChopTable(DataTable tableOne, DataTable tableTwo) { DataColumn system = new DataColumn("System"); DataColumn priority_Number = new DataColumn("Priority_Number"); DataColumn category = new DataColumn("Category"); DataColumn bid = new DataColumn("BID"); DataColumn opened_Date = new DataColumn("Opened_Date"); DataColumn title = new DataColumn("Title"); tableTwo.Columns.Add(system); tableTwo.Columns.Add(priority_Number); tableTwo.Columns.Add(category); tableTwo.Columns.Add(bid); tableTwo.Columns.Add(opened_Date); tableTwo.Columns.Add(title); foreach (DataRow row in tableOne.Rows) { tableTwo.ImportRow(row); } }
private void SetTodaysSkierVisits() { lblLastUpdate.Text = CF.GetSQLField(AM.MirrorConn, "SELECT TIME(MAX(dtverwdat)) FROM axess_cwc.tablesertrans"); //dateTimePicker1.Value = DateTime.Today.AddDays(-1); //set to DateTime.Today for release; string Today = DateTime.Today.ToString(Mirror.AxessDateFormat); string Tomorrow = DateTime.Today.AddDays(1).ToString(Mirror.AxessDateFormat); //System.Data.DataTable dtTodaysSkierVisits = CF.LoadTable(DW.dwConn, $"SELECT A.tgroup, A.descrip, COUNT(*) AS Visits FROM {DW.ActiveDatabase}.tgroups AS A inner join {DW.ActiveDatabase}.skivisits AS B ON B.tgroup=A.tgroup LEFT JOIN {DW.ActiveDatabase}.altadata AS C ON C.descrip = A.descrip WHERE B.readdate='{DateTime.Today.ToString(Mirror.AxessDateFormat)}' AND B.ALTAFLAG='Y' AND A.tgroup NOT IN ('AJ','AR','SC','SS','GC','SO') GROUP BY A.tgroup ORDER BY C.sorder", "SkiVisits"); System.Data.DataTable dtTodaysSkierVisits = CF.LoadTable(AM.MirrorConn, $"SELECT HOUR(DTVERWDAT) AS SkierHour, COUNT(DISTINCT concat_ws(' - ',nkassanr, nseriennr)) AS SkierRides FROM axess_cwc.tablesertrans WHERE dtverwdat >= '{Today}' AND dtverwdat < '{Tomorrow}' AND NZUTRNR < 50 GROUP BY HOUR(dtverwdat)", "todaysreads"); //Int32 SkierVisitsTotal = 0; //foreach (DataRow tRow in dtTodaysSkierVisits.Rows) //{ // SkierVisitsTotal += Convert.ToInt32(tRow["Visits"].ToString()); //} object[] rowVals = new object[2]; System.Data.DataTable table = new System.Data.DataTable(); table.Columns.Add("SkierHour", typeof(string)); table.Columns.Add("SkierRides", typeof(Int64)); DataRowCollection rowCollection = table.Rows; foreach (DataRow DR in dtTodaysSkierVisits.Rows) { rowVals[0] = Hours[DR.Field <Int64>("SkierHour")]; rowVals[1] = DR.Field <Int64>("SkierRides"); DataRow row = rowCollection.Add(rowVals); } System.Data.DataTable DTSkiers = new System.Data.DataTable(); DTSkiers.Columns.Add("SkierHour", typeof(string)); DTSkiers.Columns.Add("SkierRides", typeof(Int64)); foreach (DataRow row in rowCollection) { DTSkiers.ImportRow(row); } gvTodaysSkierVisits.DataSource = DTSkiers; lblLastUpdate.Text = CF.GetSQLField(AM.MirrorConn, $"SELECT MAX(dtverwdat) FROM {AM.ActiveDatabase}.tablesertrans"); txtTodayVisits.Text = CF.GetSQLField(AM.MirrorConn, $"SELECT COUNT(DISTINCT concat_ws('-',nkassanr, nseriennr)) FROM {AM.ActiveDatabase}.tablesertrans WHERE dtverwdat >= '{Today}' AND dtverwdat < '{Tomorrow}' AND nzutrnr < 50"); Refresh(); }
/*/// <summary> * /// 绑定DropDownList的方法 * /// </summary> * /// <param name="dropDownList"></param> * /// <param name="configTypeName"></param> * /// <param name="tipString"></param> * protected void DropDownListDataBind(DropDownList dropDownList, ConfigTypeName configTypeName, string tipString) * { * System.Data.DataTable dt = new ConfigureBLL().GetConfig(configTypeName.ToString()); * dropDownList.DataSource = dt; * dropDownList.DataTextField = "configvalue"; * dropDownList.DataValueField = "configkey"; * dropDownList.DataBind(); * dropDownList.Items.Insert(0, new ListItem(tipString, string.Empty)); * }*/ /// <summary> /// 绑定DropDownList的方法 /// </summary> /// <param name="dropDownList"></param> /// <param name="configTypeName"></param> /// <param name="tipString"></param> protected void DropDownListDataBind(DropDownList dropDownList, System.Data.DataTable dtConfig, ConfigTypeName configTypeName, string tipString) { try { //DataTable dt = new ConfigureBLL().GetConfig(configTypeName.ToString()); System.Data.DataTable dtNew = dtConfig.Clone(); DataRow[] drArray = dtConfig.Select("configtypename='" + configTypeName + "'"); for (int i = 0; i < drArray.Length; i++) { dtNew.ImportRow(drArray[i]); } dropDownList.DataSource = dtNew; dropDownList.DataTextField = "configvalue"; dropDownList.DataValueField = "configkey"; dropDownList.DataBind(); dtNew = null; dropDownList.Items.Insert(0, new ListItem(tipString, string.Empty)); } catch (Exception ex) { LogHelper.WriteLine(ex.Message + ex.StackTrace); ExecuteScript("AlertDialog('程序出错!', null);"); } }
private void LoadProductionRecord() { recordLoaded = false; dgvMoreDetail.DataSource = null; DataTable dt = NewProductionRecordTable(); dt_ProRecord = dalProRecord.SelectWithItemInfo(); DataTable dt_Mac = dalMac.Select(); int index = 1; foreach (DataRow row in dt_ProRecord.Rows) { int macID = int.TryParse(row[dalPlan.machineID].ToString(), out macID) ? macID : 0; string fac = tool.getFactoryNameFromMachineID(macID.ToString(), dt_Mac); DateTime proDate = Convert.ToDateTime(row[dalProRecord.ProDate]).Date; int planID = int.TryParse(row[dalPlan.planID].ToString(), out planID) ? planID : 0; int sheetID = int.TryParse(row[dalProRecord.SheetID].ToString(), out sheetID) ? sheetID : 0; string shift = row[dalProRecord.Shift].ToString(); string itemCode = row[dalItem.ItemCode].ToString(); string itemName = row[dalItem.ItemName].ToString(); int totalStockIn = int.TryParse(row[dalProRecord.TotalProduced].ToString(), out totalStockIn) ? totalStockIn : 0; string rawMat = row[dalItem.ItemMaterial].ToString(); string colorMat = row[dalItem.ItemMBatch].ToString(); bool dataMatched = true; #region filter date DateTime dateFrom = dtpFrom.Value.Date; DateTime dateTo = dtpTo.Value.Date; if (proDate < dateFrom || proDate > dateTo) { dataMatched = false; } #endregion #region filter item string filterName = cmbPartName.Text; string filterCode = cmbPartCode.Text; if (!string.IsNullOrEmpty(filterName) && filterName != itemName) { dataMatched = false; } if (!string.IsNullOrEmpty(filterCode) && filterCode != itemCode) { dataMatched = false; } #endregion #region filter factory string filterFac = cmbFac.Text; if (filterFac != "All" && !string.IsNullOrEmpty(filterFac) && filterFac != fac) { dataMatched = false; } #endregion #region filter machine string filterMac = cmbMac.Text; if (!string.IsNullOrEmpty(filterMac) && filterMac != macID.ToString()) { dataMatched = false; } #endregion if (dataMatched) { DataRow dt_Row = dt.NewRow(); dt_Row[header_Index] = index; dt_Row[header_Fac] = fac; dt_Row[header_Mac] = macID; if (cbShowOnlyOneRowForEachPlan.Checked) { dt_Row[header_ProDateTo] = proDate; dt_Row[header_ProDateFrom] = proDate; } else { dt_Row[header_ProDate] = proDate; dt_Row[header_SheetID] = sheetID; dt_Row[header_Shift] = shift; } dt_Row[header_PlanID] = planID; dt_Row[header_PartName] = itemName; dt_Row[header_StockIn] = totalStockIn; if (cbShowRawMat.Checked) { dt_Row[header_RawMat] = rawMat; } if (cbShowColorMat.Checked) { dt_Row[header_ColorMat] = colorMat; } dt.Rows.Add(dt_Row); index++; } } #region show only 1 row for each plan if (cbShowOnlyOneRowForEachPlan.Checked) { DataTable dt_FilterDuplicatePlan = NewProductionRecordTable(); string previousPlanID = null; int totalStockIn = 0; //sorting dt by planID and date dt.DefaultView.Sort = header_PlanID + " ASC"; dt = dt.DefaultView.ToTable(); foreach (DataRow row in dt.Rows) { DateTime proDate = Convert.ToDateTime(row[header_ProDateTo]); if (previousPlanID == row[header_PlanID].ToString()) { totalStockIn += Convert.ToInt32(row[header_StockIn].ToString()); dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_ProDateFrom] = proDate; dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_StockIn] = totalStockIn; } else { previousPlanID = row[header_PlanID].ToString(); totalStockIn = Convert.ToInt32(row[header_StockIn].ToString()); dt_FilterDuplicatePlan.ImportRow(row); } //if(previousPlanID == null) //{ // previousPlanID = row[header_PlanID].ToString(); // totalStockIn = Convert.ToInt32(row[header_StockIn].ToString()); // dt_FilterDuplicatePlan.Rows.Add(row); //} //else if(previousPlanID == row[header_PlanID].ToString()) //{ // totalStockIn += Convert.ToInt32(row[header_StockIn].ToString()); // dt_FilterDuplicatePlan.Rows[dt_FilterDuplicatePlan.Rows.Count - 1][header_StockIn] = totalStockIn; //} //else //{ // previousPlanID = row[header_PlanID].ToString(); // totalStockIn = Convert.ToInt32(row[header_StockIn].ToString()); // dt_FilterDuplicatePlan.Rows.Add(row); //} } dt_FilterDuplicatePlan.DefaultView.Sort = header_Mac + " ASC, " + header_ProDateFrom + " ASC"; dt_FilterDuplicatePlan = dt_FilterDuplicatePlan.DefaultView.ToTable(); int indexNo = 1; foreach (DataRow row in dt_FilterDuplicatePlan.Rows) { row[header_Index] = indexNo; indexNo++; } dt = dt_FilterDuplicatePlan.Copy(); } #endregion dgvProductionRecord.DataSource = dt; dgvProductionRecord.ClearSelection(); recordLoaded = true; }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { List <ErrorList> list = new List <ErrorList>(); ExcelTableFinal = new DataTable(); try { CallParameter cp3 = (CallParameter)e.Argument; ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "ProductSheet$"); while (ExcelTable.Columns.Count > 17) { ExcelTable.Columns.RemoveAt(17); } ExcelTableFinal = ExcelTable.Clone(); ExcelTable.AsEnumerable().Where(row => row["product id"] != null && row["product id"].ToString() != "").ToList().ForEach(row => ExcelTableFinal.ImportRow(row)); if (ExcelTableFinal != null) { if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCT ID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "PRODUCT NAME" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "PRODUCT DESCRIPTION" || ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "UOM" || ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "SORT ORDER" || ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "ACTIVE" || ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "BC ACTIVE" || ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "PRODUCT CATEGORY" || ExcelTable.Columns[8].ColumnName.Trim().ToUpper() != "PRODUCT CODE" || ExcelTable.Columns[9].ColumnName.Trim().ToUpper() != "PRICE DISPLAY CODE" || ExcelTable.Columns[10].ColumnName.Trim().ToUpper() != "COST CENTRE CODE" || ExcelTable.Columns[11].ColumnName.Trim().ToUpper() != "MINI BILL COMPLETED" || ExcelTable.Columns[12].ColumnName.Trim().ToUpper() != "STATE" || ExcelTable.Columns[13].ColumnName.Trim().ToUpper() != "ISSTUDIOMPRODUCT" || ExcelTable.Columns[14].ColumnName.Trim().ToUpper() != "INTERNAL DESCRIPTION" || ExcelTable.Columns[15].ColumnName.Trim().ToUpper() != "ADDITIONAL NOTES" || ExcelTable.Columns[16].ColumnName.Trim().ToUpper() != "OPERATION ONLY" ) { ErrorList l = new ErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } else { e.Result = IsSourceValidate(ExcelTableFinal); } } else { ErrorList l = new ErrorList(); l.MessageText = "The excel is empty."; list.Add(l); e.Result = list; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public System.Data.DataTable FormatDataTable(System.Data.DataTable dt) { List <string> colNames = new List <string>(); List <string> DateNames = new List <string>(); string DepartmentColumn = ""; System.Data.DataTable dtCloned = dt.Clone(); foreach (DataColumn c in dt.Columns) { if (c.DataType == typeof(String)) { if (c.ColumnName.Equals("Avdelings Navn") || c.ColumnName.Equals("Rabatt Type") || c.ColumnName.Equals("AvdelingsNavn")) { DepartmentColumn = c.ColumnName; } } else if (c.DataType == typeof(Decimal)) { dtCloned.Columns[c.ColumnName].DataType = typeof(Decimal); colNames.Add(c.ColumnName); } else if (c.DataType == typeof(int)) { dtCloned.Columns[c.ColumnName].DataType = typeof(int); colNames.Add(c.ColumnName); } else if (c.DataType == typeof(float)) { dtCloned.Columns[c.ColumnName].DataType = typeof(float); colNames.Add(c.ColumnName); } //if (c.DataType == typeof(DateTime)) //{ // DateNames.Add(c.ColumnName); //} } String lstDepartment = ""; foreach (DataRow row in dt.Rows) { if (DepartmentColumn != "") { String Departmentname = row.Field <String>(DepartmentColumn); if (!lstDepartment.Equals(Departmentname)) { DataRow row2 = dtCloned.NewRow(); // probalby set background color here somehow row2.SetField(DepartmentColumn, Departmentname); row.SetField(DepartmentColumn, ""); dtCloned.Rows.Add(row2); lstDepartment = Departmentname; } else { row.SetField(DepartmentColumn, ""); } } dtCloned.ImportRow(row); } foreach (DataRow dr in dtCloned.Rows) { foreach (DataColumn dc in dtCloned.Columns) { if (colNames.Contains(dc.ColumnName)) { double value; string whatYouWant = ""; double dts = 00; if (double.TryParse((dr[dc]).ToString(), out value)) { dts = Convert.ToDouble(dr[dc]); if (decimalCount == 0) { whatYouWant = dts.ToString("#,##0"); } else if (decimalCount == 2) { whatYouWant = dts.ToString("#,##0.00"); } int intvalue; if (int.TryParse(whatYouWant.Trim(), out intvalue)) { dr[dc] = intvalue; } else { if (dc.DataType == typeof(int)) { int.TryParse(whatYouWant, out intvalue); dr[dc] = intvalue; } else { dr[dc] = whatYouWant; } } } //if (double.TryParse((dr[dc]).ToString(), out value)) //{ // dts = Convert.ToDouble(dr[dc]); // if (decimalCount == 0) // { // whatYouWant = dts.ToString("#,##0"); // } // else if (decimalCount == 2) // { // //whatYouWant = dts.ToString("C2"); // whatYouWant = dts.ToString("#,##0.00"); // } // if (dr[dc].GetType() == typeof(int)) // { // int intvalue; // dr[dc] = int.TryParse(whatYouWant, out intvalue); // } // else // { // dr[dc] = whatYouWant; // } //} } //if (DateNames.Contains(dc.ColumnName)) //{ // if (!dr[dc].Equals(null)) // { // DateTime tmpDateTime = Convert.ToDateTime(dr[dc]); // dr[dc] = Convert.ToDateTime(dr[dc]); // } //} } } return(dtCloned); }
/// <summary> /// Arrange cycles by as of date and planning period /// </summary> /// <param name="pdtmFromMonth">From Month</param> /// <param name="pdtmToMonth">To Month</param> /// <param name="pdtbCycles">All cycles</param> /// <param name="parrPlanningPeriod">All Planning Period</param> /// <param name="sbCycleIDs">out: cycle ids in range</param> /// <returns>Arranged cycles</returns> private DataTable ArrangeCycles(DateTime pdtmFromMonth, DateTime pdtmToMonth, DataTable pdtbCycles, ArrayList parrPlanningPeriod, out StringBuilder sbCycleIDs) { DataTable dtbResult = pdtbCycles.Clone(); DateTime dtmFromDate = new DateTime(pdtmFromMonth.Year, pdtmFromMonth.Month, 1); DateTime dtmToDate = dtmFromDate.AddMonths(1).AddDays(-1); if (pdtmToMonth > DateTime.MinValue) { dtmToDate = pdtmToMonth; } sbCycleIDs = new StringBuilder(); DataTable dtbTemp = pdtbCycles.Clone(); ArrayList arrMonths = GetAllMonthInRange(dtmFromDate, dtmToDate); #region find all cycle go thru the date range foreach (DateTime dtmPeriod in parrPlanningPeriod) { DataRow[] drowPeriod = pdtbCycles.Select("PlanningPeriod = '" + dtmPeriod.ToString("G") + "'" , "Version DESC"); foreach (DataRow period in drowPeriod) { DateTime dtmFromDateCycle = (DateTime)period["FromDate"]; DateTime dtmToDateCycle = (DateTime)period["ToDate"]; ArrayList arrCycleMonths = GetAllMonthInRange(dtmFromDateCycle, dtmToDateCycle); foreach (DateTime dtmDate in arrMonths) { if (arrCycleMonths.Contains(dtmDate)) { dtbTemp.ImportRow(period); } } } } #endregion #region sorting all cycle // order by planning period, from date and version DataRow[] drowCycles = dtbTemp.Select("", "PlanningPeriod ASC, FromDate ASC, Version DESC"); DateTime dtmPreFromDate = DateTime.MinValue; int intPreVersion = -1; DateTime dtmPlanningPeriod = DateTime.MinValue; if (drowCycles.Length > 0) { dtmPlanningPeriod = (DateTime)drowCycles[0]["PlanningPeriod"]; } for (int i = 0; i < drowCycles.Length; i++) { DataRow drowCycle = drowCycles[i]; // from date of current cycle DateTime dtmCurFromDate = (DateTime)drowCycle["FromDate"]; // version of current cycle int intVersion = Convert.ToInt32(drowCycle["Version"]); // this cycle is old version of period, from date is greater than new version then ignore it if (intVersion < intPreVersion && dtmCurFromDate > dtmPreFromDate && dtmPlanningPeriod.Equals(drowCycle["PlanningPeriod"])) { continue; } // re-assign value intPreVersion = intVersion; dtmPreFromDate = dtmCurFromDate; dtmPlanningPeriod = (DateTime)drowCycle["PlanningPeriod"]; // update ToDate of previous cycle if (i > 0) { // previous cycle DataRow drowPreCycle = drowCycles[i - 1]; // as of date of current cycle DateTime dtmAsOfDate = (DateTime)drowCycle["FromDate"]; // update to date of previous cycle drowPreCycle["ToDate"] = dtmAsOfDate.AddDays(-1); } } if (drowCycles.Length > 0) { drowCycles[drowCycles.Length - 1]["ToDate"] = dtmToDate; } // import to result table foreach (DataRow drowCycle in drowCycles) { sbCycleIDs.Append(drowCycle["DCOptionMasterID"].ToString() + ","); dtbResult.ImportRow(drowCycle); } #endregion sbCycleIDs.Append("0"); return(dtbResult); }
private void InsertAllDataToSheet(string path, string fileName) { DataGridView dgv = dgvCommon; DataTable dt = (DataTable)dgv.DataSource; Excel.Application excelApp = new Excel.Application { Visible = true }; Workbook g_Workbook = excelApp.Workbooks.Open( path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); object misValue = Missing.Value; if (dt.Rows.Count > 0)//if datagridview have data { Worksheet xlWorkSheet = null; int count = g_Workbook.Worksheets.Count; xlWorkSheet = g_Workbook.Worksheets.Add(Type.Missing, g_Workbook.Worksheets[count], Type.Missing, Type.Missing); xlWorkSheet.Name = "COMMON PART"; xlWorkSheet.PageSetup.LeftHeader = "&\"Courier New\"&8 " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"); xlWorkSheet.PageSetup.CenterHeader = "&\"Courier New\"&12 SPP INVENTORY REPORT: COMMON PART"; xlWorkSheet.PageSetup.RightHeader = "&\"Courier New\"&8 PG -&P"; xlWorkSheet.PageSetup.CenterFooter = "&\"Courier New\"&8 Printed By " + dalUser.getUsername(MainDashboard.USER_ID); xlWorkSheet.PageSetup.CenterHorizontally = true; xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait; xlWorkSheet.PageSetup.Zoom = false; xlWorkSheet.PageSetup.FitToPagesWide = 1; xlWorkSheet.PageSetup.FitToPagesTall = false; double pointToCMRate = 0.035; xlWorkSheet.PageSetup.TopMargin = 1.2 / pointToCMRate; xlWorkSheet.PageSetup.BottomMargin = 1.2 / pointToCMRate; xlWorkSheet.PageSetup.HeaderMargin = 0.6 / pointToCMRate; xlWorkSheet.PageSetup.FooterMargin = 0.6 / pointToCMRate; xlWorkSheet.PageSetup.LeftMargin = 0.7 / pointToCMRate; xlWorkSheet.PageSetup.RightMargin = 0.7 / pointToCMRate; xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1"; // Paste clipboard results to worksheet range copyDGVtoClipboard(dgvCommon); xlWorkSheet.Select(); Range CR = (Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); #region old format setting Range tRange = xlWorkSheet.UsedRange; tRange.Font.Size = 11; tRange.RowHeight = 30; tRange.VerticalAlignment = XlHAlign.xlHAlignCenter; tRange.Font.Name = "Courier New"; tRange.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); //tRange.Borders.LineStyle = XlLineStyle.xlContinuous; //tRange.Borders.Weight = XlBorderWeight.xlThin; //Range FirstRow = (Range)xlWorkSheet.Application.Rows[1, Type.Missing]; Range FirstRow = xlWorkSheet.get_Range("a1:d1").Cells; FirstRow.WrapText = true; FirstRow.Font.Size = 8; FirstRow.HorizontalAlignment = XlHAlign.xlHAlignCenter; FirstRow.VerticalAlignment = XlHAlign.xlHAlignCenter; FirstRow.RowHeight = 20; FirstRow.Interior.Color = Color.WhiteSmoke; FirstRow.Borders.LineStyle = XlLineStyle.xlContinuous; FirstRow.Borders.Weight = XlBorderWeight.xlThin; //tRange.EntireColumn.AutoFit(); //DataTable dt = (DataTable)dgv.DataSource; int TypeIndex = dgv.Columns["TYPE"].Index; int SizeIndex = dgv.Columns["SIZE"].Index; int UnitIndex = dgv.Columns["UNIT"].Index; int StockIndex = dgv.Columns["STOCK(PCS)"].Index; xlWorkSheet.Cells[1, TypeIndex + 1].ColumnWidth = 25; xlWorkSheet.Cells[1, SizeIndex + 1].ColumnWidth = 6; xlWorkSheet.Cells[1, UnitIndex + 1].ColumnWidth = 6; xlWorkSheet.Cells[1, StockIndex + 1].ColumnWidth = 25; //xlWorkSheet.Cells[1, StdIndex + 1].Font.Italic = true; //rangeForecast1.Font.Italic = true; //DataTable dt_Source = (DataTable)dgv.DataSource; for (int j = 0; j <= dt.Rows.Count - 1; j++) { Range rangeStock = (Range)xlWorkSheet.Cells[j + 2, StockIndex + 1]; Range rangeType = (Range)xlWorkSheet.Cells[j + 2, TypeIndex + 1]; rangeStock.HorizontalAlignment = XlHAlign.xlHAlignCenter; rangeType.HorizontalAlignment = XlHAlign.xlHAlignCenter; System.Drawing.Color color = System.Drawing.Color.Black; rangeStock.Borders[XlBordersIndex.xlEdgeRight].Color = color; rangeStock.Borders[XlBordersIndex.xlEdgeLeft].Color = color; rangeType.Borders[XlBordersIndex.xlEdgeRight].Color = color; rangeType.Borders[XlBordersIndex.xlEdgeLeft].Color = color; } #endregion releaseObject(xlWorkSheet); Clipboard.Clear(); dgvCommon.ClearSelection(); } dgv = dgvUnique; dt = (DataTable)dgv.DataSource; #region divide by type DataTable dt_type = dt.Clone(); string previousType = null; string currentType = null; foreach (DataRow row in dt.Rows) { //get type. if previous type is null, set previous = current type currentType = row["TYPE"].ToString(); if (string.IsNullOrEmpty(currentType)) { currentType = previousType; } if (previousType == null || previousType == currentType) { previousType = currentType; //move data to new table dt_type.ImportRow(row); } else if (previousType != currentType) { DataRow lastRow = dt_type.Rows[dt_type.Rows.Count - 1]; if (string.IsNullOrEmpty(lastRow["CODE"].ToString())) { dt_type.Rows.RemoveAt(dt_type.Rows.Count - 1); } dgvUnique.DataSource = dt_type; MoveUniqueDataToSheet(g_Workbook, previousType); previousType = currentType; dt_type = dt.Clone(); if (!string.IsNullOrEmpty(row["TYPE"].ToString())) { dt_type.ImportRow(row); } } } if (dt_type.Rows.Count > 0) { dgvUnique.DataSource = dt_type; MoveUniqueDataToSheet(g_Workbook, previousType); } dgvUnique.DataSource = dt; #endregion g_Workbook.Worksheets.Item[1].Delete(); g_Workbook.Save(); releaseObject(g_Workbook); //frmLoading.CloseForm(); Cursor = Cursors.Arrow; // change cursor to normal type }
private void btnAutoInOut_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Confirm to process In/Out action?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dialogResult == DialogResult.Yes) { DataTable dt = NewDeliverTable(); //DataRow dt_row; DataTable dt_deliver = (DataTable)dgvDeliver.DataSource; foreach (DataRow row in dt_deliver.Rows) { string matCode = row[headerMatCode].ToString(); bool itemChecked = row[headerCheck] == DBNull.Value ? false : Convert.ToBoolean(row[headerCheck].ToString()); if (!string.IsNullOrEmpty(matCode) && itemChecked) { dt.ImportRow(row); } } MatTrfDate = dtpTrfDate.Text; frmInOutEdit frm = new frmInOutEdit(dt); frm.StartPosition = FormStartPosition.CenterScreen; frm.ShowDialog();//Item Edit DataTable dt_MatPlan = dalMatPlan.Select(); if (frmInOutEdit.TrfSuccess) { foreach (DataRow row in dt.Rows) { string from = row[headerFrom].ToString(); float preparingQty = float.TryParse(row[headerQty].ToString(), out float i) ? Convert.ToSingle(row[headerQty].ToString()) : 0; string to = row[headerTo].ToString(); string matCode = row[headerMatCode].ToString(); foreach (DataRow mat in dt_MatPlan.Rows) { bool active = Convert.ToBoolean(mat[dalMatPlan.Active]); if (active) { float checkingQty = float.TryParse(mat[dalMatPlan.Prepare].ToString(), out float j) ? Convert.ToSingle(mat[dalMatPlan.Prepare].ToString()) : 0; bool match = true; if (matCode != mat[dalMatPlan.MatCode].ToString()) { match = false; } else if (from != mat[dalMatPlan.MatFrom].ToString()) { match = false; } else if (to != mat[dalMac.MacLocation].ToString()) { match = false; } else if (preparingQty != checkingQty) { match = false; } if (match) { //get plan id int PlanID = int.TryParse(mat[dalMatPlan.PlanID].ToString(), out int k) ? Convert.ToInt32(mat[dalMatPlan.PlanID]) : 0; float Transferred = float.TryParse(mat[dalMatPlan.Transferred].ToString(), out float l) ? Convert.ToSingle(mat[dalMatPlan.Transferred].ToString()) : 0; uMatPlan.mat_code = matCode; uMatPlan.plan_id = PlanID; uMatPlan.mat_transferred = preparingQty + Transferred; uMatPlan.mat_preparing = 0; uMatPlan.mat_from = ""; uMatPlan.updated_date = DateTime.Now; uMatPlan.updated_by = MainDashboard.USER_ID; if (!dalMatPlan.MatPrepareUpdate(uMatPlan)) { MessageBox.Show("Failed to update material preparing data."); } } } } } itemChecking = false; btnItemCheck.Text = "ITEM CHECK"; btnAutoInOut.Visible = false; dgvDeliver.Columns[headerCheck].Visible = false; cbCheckAll.Checked = false; cbCheckAll.Visible = false; dtpTrfDate.Visible = false; LoadDeliverData(); } } }
private System.Data.DataTable taxDetails4Print(List<invoiceoutdetail> IODetails, string documentID) { int HSNLength = 0; System.Data.DataTable dt = new System.Data.DataTable(); System.Data.DataTable dtTax = new System.Data.DataTable(); try { if (documentID == "PRODUCTINVOICE") { HSNLength = 4; } else if (documentID == "SERVICEINVOICE") { HSNLength = 6; } { dt.Columns.Add("HSNCode", typeof(string)); dt.Columns.Add("TaxCode", typeof(string)); dt.Columns.Add("Amount", typeof(double)); dt.Columns.Add("TaxAmount", typeof(double)); dt.Columns.Add("TaxItem", typeof(string)); dt.Columns.Add("TaxItemPercentage", typeof(double)); dt.Columns.Add("TaxItemAmount", typeof(double)); } //fill hsn code wise tax details in dt foreach (invoiceoutdetail iod in IODetails) { string tstr = iod.TaxDetails; string[] lst1 = tstr.Split('\n'); for (int j = 0; j < lst1.Length - 1; j++) { string[] lst2 = lst1[j].Split('-'); if (Convert.ToDouble(lst2[1]) > 0) { dt.Rows.Add(); dt.Rows[dt.Rows.Count - 1][0] = iod.HSNCode.Substring(0, HSNLength); dt.Rows[dt.Rows.Count - 1][1] = iod.TaxCode; dt.Rows[dt.Rows.Count - 1][2] = iod.Quantity * iod.Price; dt.Rows[dt.Rows.Count - 1][3] = iod.Tax; dt.Rows[dt.Rows.Count - 1][4] = lst2[0]; dt.Rows[dt.Rows.Count - 1][5] = iod.HSNCode; //need to replace with percentage dt.Rows[dt.Rows.Count - 1][6] = lst2[1]; } } } } catch (Exception ex) { } try { //fill tax rate for each tax item in dt TaxCodeWorkingDB tcwdb = new TaxCodeWorkingDB(); List<taxcodeworking> tcwDetails = tcwdb.getTaxCodeDetails(); for (int i = 0; i < (dt.Rows.Count); i++) { foreach (taxcodeworking tcwd in tcwDetails) { if (dt.Rows[i][1].ToString() == tcwd.TaxCode && dt.Rows[i][4].ToString() == tcwd.TaxItemName) { dt.Rows[i][5] = tcwd.OperatorValue; break; } } } //prepare HSN Code wise totals in a new table System.Data.DataTable dttotal = new System.Data.DataTable(); dttotal = dt.Copy(); dttotal.Clear(); for (int i = 0; i < (dt.Rows.Count); i++) { Boolean fount = false; string tstr1 = dt.Rows[i][0].ToString(); string tstr2 = dt.Rows[i][4].ToString(); string tstr3 = dt.Rows[i][5].ToString(); for (int j = 0; j < (dttotal.Rows.Count); j++) { string tstr4 = dttotal.Rows[j][0].ToString(); string tstr5 = dttotal.Rows[j][4].ToString(); string tstr6 = dttotal.Rows[j][5].ToString(); if (tstr1 == tstr4 && tstr2 == tstr5 && tstr3 == tstr6) { dttotal.Rows[j][2] = Convert.ToDouble(dttotal.Rows[j][2].ToString()) + Convert.ToDouble(dt.Rows[i][2].ToString()); dttotal.Rows[j][6] = Convert.ToDouble(dttotal.Rows[j][6].ToString()) + Convert.ToDouble(dt.Rows[i][6].ToString()); fount = true; } } if (!fount) { dttotal.ImportRow(dt.Rows[i]); } } string tstr = ""; ////for (int i = 0; i < (dttotal.Rows.Count); i++) ////{ //// tstr = tstr+ //// dttotal.Rows[i][0].ToString() + "," + //// dttotal.Rows[i][1].ToString() + "," + //// dttotal.Rows[i][2].ToString() + "," + //// dttotal.Rows[i][3].ToString() + "," + //// dttotal.Rows[i][4].ToString() + "," + //// dttotal.Rows[i][5].ToString() + "," + //// dttotal.Rows[i][6].ToString() + "\n"; ////} ////MessageBox.Show(tstr); //create print table tstr = ""; //find distinct tax item in dttotal DataTable dtDistinct = dttotal.AsEnumerable().GroupBy(row => row.Field<string>("TaxItem")).Select(group => group.First()).CopyToDataTable(); ////for (int i = 0; i < (dtDistinct.Rows.Count); i++) ////{ //// tstr = tstr + //// dtDistinct.Rows[i][0].ToString() + "," + //// dtDistinct.Rows[i][1].ToString() + "," + //// dtDistinct.Rows[i][2].ToString() + "," + //// dtDistinct.Rows[i][3].ToString() + "," + //// dtDistinct.Rows[i][4].ToString() + "," + //// dtDistinct.Rows[i][5].ToString() + "," + //// dtDistinct.Rows[i][6].ToString() + "\n"; ////} ////MessageBox.Show(tstr); //create columns in dttax table. dynamically creating the columns for each tax item { dtTax.Columns.Add("HSNCode", typeof(string)); dtTax.Columns.Add("Amount", typeof(double)); for (int i = 0; i < dtDistinct.Rows.Count && i < 3; i++) { dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString(), typeof(string)); dtTax.Columns.Add(dtDistinct.Rows[i][4].ToString() + "Amount", typeof(double)); } dtTax.Columns.Add("Total", typeof(double)); } //add data in dttax table for (int i = 0; i < (dttotal.Rows.Count); i++) { Boolean hsnFount = false; string tstr1 = dttotal.Rows[i][0].ToString(); //for domestic int j = 0; for (j = 0; j < (dtTax.Rows.Count); j++) { string tstr2 = dtTax.Rows[j][0].ToString(); //for domestic if (tstr1 == tstr2) { hsnFount = true; break; } } if (!hsnFount) { dtTax.Rows.Add(); j = dtTax.Rows.Count - 1; dtTax.Rows[j][0] = tstr1; dtTax.Rows[j][1] = dttotal.Rows[i][2]; ; } string tstr3 = dttotal.Rows[i][4].ToString(); string tstr4 = dttotal.Rows[i][4].ToString() + "Amount"; try { dtTax.Rows[j][tstr3] = dttotal.Rows[i][5]; dtTax.Rows[j][tstr4] = dttotal.Rows[i][6]; string t1 = String.IsNullOrEmpty(dtTax.Rows[j]["Total"].ToString()) ? "0" : dtTax.Rows[j]["Total"].ToString(); string t2 = String.IsNullOrEmpty(dttotal.Rows[i][6].ToString()) ? "0" : dttotal.Rows[i][6].ToString(); double d1 = Convert.ToDouble(t1) + Convert.ToDouble(t2); dtTax.Rows[j]["Total"] = d1; } catch (Exception ex) { MessageBox.Show("Error creating HSN wise tax summary"); } } tstr = ""; ////double td1=0, td2=0, td3 = 0; ////for (int i = 0; i < (dtTax.Rows.Count); i++) ////{ //// for (int j=0; j<dtTax.Columns.Count;j++) //// { //// tstr = tstr + dtTax.Rows[i][j].ToString() + ","; //// } //// tstr = tstr+"\n"; //// td1 = td1 + Convert.ToDouble(dtTax.Rows[i][1].ToString()); //// td2 = td2 + Convert.ToDouble(dtTax.Rows[i][dtTax.Columns.Count-1].ToString()); ////} ////MessageBox.Show(tstr); ////MessageBox.Show(td1.ToString()); ////MessageBox.Show(td2.ToString()); } catch (Exception ex) { MessageBox.Show("taxDetails4Print() : Error - " + ex.ToString()); } return dtTax; }
/// <summary> /// 导出Excel /// </summary> /// <param name="dt"></param> /// <param name="head"></param> /// <param name="absFileName"></param> public static void ExportToExcelForTwo(System.Data.DataTable dt, string[] head, string absFileName) { //设置多少行为一个Sheet int RowsToDivideSheet = 65535; //计算Sheet数 int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); Application excel = null; _Workbook xBk = null; _Worksheet xSt = null; try { excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //excel.Visible = true; //循环中要使用的变量 int dvRowStart; int dvRowEnd; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dt.Rows.Count - 1) { dvRowEnd = dt.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //题头导出 int rowCount = head.Length; int colCount = 1; object[,] dataArray = new object[colCount, rowCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[j, i] = head[i]; } } xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10; //数据导出 System.Data.DataTable SheetTable = new System.Data.DataTable(); foreach (DataColumn dc in dt.Columns) { DataColumn newdc = new DataColumn(); newdc.ColumnName = dc.ColumnName; newdc.DataType = dc.DataType; SheetTable.Columns.Add(newdc); } for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { SheetTable.ImportRow(dt.Rows[drvIndex]); } //保存数据 int MRProw = -1;//记忆MRP所在行号 rowCount = SheetTable.Rows.Count; colCount = SheetTable.Columns.Count; object[,] dataArray1 = new object[rowCount, colCount]; excel.DisplayAlerts = false; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray1[i, j] = SheetTable.Rows[i][j]; } if (SheetTable.Rows[i][1].ToString().Length == 0) { dataArray1[i, 1] = SheetTable.Rows[i][2]; SheetTable.Rows[i][1] = SheetTable.Rows[i][2]; xSt.get_Range(xSt.Cells[i + 2, 2], xSt.Cells[i + 2, 3]).Merge(Type.Missing); } } xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1; int start = 0; int start1 = 0; string strstart = SheetTable.Rows[start][0].ToString(); string strstart1 = SheetTable.Rows[start1][1].ToString(); double num1, num2; for (int i = 0; i < rowCount; i++) { if (strstart.Trim().ToUpper() != SheetTable.Rows[i][0].ToString().Trim().ToUpper()) { xSt.get_Range(xSt.Cells[start + 2, 1], xSt.Cells[i + 1, 1]).Merge(Type.Missing); start = i; strstart = SheetTable.Rows[start][0].ToString(); } if (SheetTable.Rows[i][1].ToString().CompareTo("MRP") == 0) { MRProw = i; } if (SheetTable.Rows[i][1].ToString().Contains("Promised Capacity")) { for (int j = 3; j < colCount; j++) { if (Convert.ToDouble(SheetTable.Rows[MRProw][j].ToString()) > Convert.ToDouble(SheetTable.Rows[i][j].ToString())) { xSt.get_Range(xSt.Cells[i + 2, j + 1], xSt.Cells[i + 2, j + 1]).Interior.ColorIndex = 3; } } } if (SheetTable.Rows[i][1].ToString().Contains("Bottleneck Capacity")) { for (int j = 3; j < colCount; j++) { if (Convert.ToDouble(SheetTable.Rows[MRProw][j].ToString()) > Convert.ToDouble(SheetTable.Rows[i][j].ToString())) { xSt.get_Range(xSt.Cells[i + 2, j + 1], xSt.Cells[i + 2, j + 1]).Interior.ColorIndex = 3; } } } if (strstart1.Trim().ToUpper() != SheetTable.Rows[i][1].ToString().Trim().ToUpper()) { xSt.get_Range(xSt.Cells[start1 + 2, 2], xSt.Cells[i + 1, 2]).Merge(Type.Missing); start1 = i; strstart1 = SheetTable.Rows[start1][1].ToString(); } } if (start != rowCount) { xSt.get_Range(xSt.Cells[start + 2, 1], xSt.Cells[rowCount + 1, 1]).Merge(Type.Missing); } if (start1 != rowCount) { xSt.get_Range(xSt.Cells[start1 + 2, 2], xSt.Cells[rowCount + 1, 2]).Merge(Type.Missing); } excel.DisplayAlerts = true; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10; //单元格边框 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐 xSt.Columns.AutoFit(); //自适应宽度 } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true; //注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } catch (Exception e) { //throw (e); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } }
/*<summary> * 将DataTable中的数据导出到Excel中,并在服务器端AppData文件夹中生成xls文件 * </summary> * <param name="dt">要导出数据的DataTable</param> * <param name="head">题头数据</param> * <param name="absFileName">文件的绝对路径</param> * <returns></returns> */ public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName) { //设置多少行为一个Sheet int RowsToDivideSheet = 65535; //计算Sheet数 int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径 string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径 string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径 Excel.Application excel = null; Excel._Workbook xBk = null; Excel._Worksheet xSt = null; Excel.Workbooks workbooks; //工作簿集合 Excel.Sheets sheets; //SHEET页集合 #region //读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称 excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban"); tempEFileXPath = "~/moban" + tempEFileXPath; tempEFilePath = excelTemplateDPath + tempEFilePath; excelTemplateDPath += "\\TaskTemplet9.xls"; //验证EXCEL临时文件夹是否存在 if (!File.Exists(tempEFilePath)) { Directory.CreateDirectory(tempEFilePath); } #endregion try { #region //启动excel进程并加载模板 //启动EXCEL进程 excel = new Excel.Application(); excel.Visible = false; excel.UserControl = true; excel.DisplayAlerts = false; //加载读取模板 workbooks = excel.Workbooks; xBk = workbooks.Add(excelTemplateDPath); sheets = xBk.Worksheets; xSt = (Excel._Worksheet)sheets.get_Item(1); #endregion //循环中要使用的变量 int dvRowStart; int dvRowEnd; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dt.Rows.Count - 1) { dvRowEnd = dt.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //题头导出 int rowCount = head.Length; int colCount = 1; object[,] dataArray = new object[colCount, rowCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[j, i] = head[i]; } } xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10; //数据导出 System.Data.DataTable SheetTable = new System.Data.DataTable(); foreach (DataColumn dc in dt.Columns) { DataColumn newdc = new DataColumn(); newdc.ColumnName = dc.ColumnName; newdc.DataType = dc.DataType; SheetTable.Columns.Add(newdc); } for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { SheetTable.ImportRow(dt.Rows[drvIndex]); } //保存数据 rowCount = SheetTable.Rows.Count; colCount = SheetTable.Columns.Count; object[,] dataArray1 = new object[rowCount, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray1[i, j] = SheetTable.Rows[i][j]; } } xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10; //单元格边框 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐 xSt.Columns.AutoFit(); //自适应宽度 //设置图标的标题 string Charttitle = dt.Rows[0][0].ToString(); } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true; //注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } catch (Exception e) { //throw (e); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } }
/*<summary> * 将DataTable中的数据导出到Excel中,并在服务器端AppData文件夹中生成xls文件 * </summary> * <param name="dt">要导出数据的DataTable</param> * <param name="head">题头数据</param> * <param name="absFileName">文件的绝对路径</param> * <returns></returns> */ public static void ExportToExcel(System.Data.DataTable dt, string[] head, string absFileName) { //设置多少行为一个Sheet int RowsToDivideSheet = 65535; //计算Sheet数 int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); Application excel = null; _Workbook xBk = null; _Worksheet xSt = null; try { excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //excel.Visible = true; //循环中要使用的变量 int dvRowStart; int dvRowEnd; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dt.Rows.Count - 1) { dvRowEnd = dt.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //题头导出 int rowCount = head.Length; int colCount = 1; object[,] dataArray = new object[colCount, rowCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[j, i] = head[i]; } } xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10; //数据导出 System.Data.DataTable SheetTable = new System.Data.DataTable(); foreach (DataColumn dc in dt.Columns) { DataColumn newdc = new DataColumn(); newdc.ColumnName = dc.ColumnName; newdc.DataType = dc.DataType; SheetTable.Columns.Add(newdc); } for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { SheetTable.ImportRow(dt.Rows[drvIndex]); } //保存数据 rowCount = SheetTable.Rows.Count; colCount = SheetTable.Columns.Count; object[,] dataArray1 = new object[rowCount, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray1[i, j] = SheetTable.Rows[i][j]; } } //注释掉下句可以解决无法读取时间的格式的问题 //xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0"; //保留小数位数为2; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial"; xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10; //单元格边框 xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1; xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐 xSt.Columns.AutoFit(); //自适应宽度 } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true; //注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } catch (Exception e) { //throw (e); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; } }