private DataTable MakeExportID(DataTable dtViewName) { //--- make query using all viewname , execute the query to get distinct ptn_pk and store them in table dtViewPtnPk IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration"); string theQuery = ""; int r = 0; foreach (DataRow theDRV in dtViewName.Rows) { if (theDRV["Ptn_Pk"].ToString() == "T") { if (r == 0) { theQuery = "SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"]; } else if (theQuery.ToString() != "") { theQuery = theQuery + " UNION " + " SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"]; } else { theQuery = " SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"]; } } r = 1; } theQuery = theQuery + " ORDER BY Ptn_Pk"; DataTable dtViewPtnPk = new DataTable(); dtViewPtnPk = ExportManager.RunQuery(theQuery); string Ptn_Pk_List = ""; foreach (DataRow theDR in dtViewPtnPk.Rows) { if (Ptn_Pk_List == "") { Ptn_Pk_List = Ptn_Pk_List + theDR["Ptn_Pk"].ToString(); } else { Ptn_Pk_List = Ptn_Pk_List + "," + theDR["Ptn_Pk"].ToString(); } } dtViewPtnPk = null; DataTable dtPtnPkExport = new DataTable(); if (Ptn_Pk_List != "") { dtPtnPkExport = ExportManager.MakeExportID(Ptn_Pk_List); } return(dtPtnPkExport); }
private DataView ManipulateData(string theQuery) { #region "RUN QUERY AND GET ALL DATA" IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration"); DataTable dtToExport = ExportManager.RunQuery(theQuery); #endregion #region "GET UNIQUE EXPORTID AND STORE IN A TABLE - call MakeExportID" DataTable dtExportId = new DataTable();//--to store ptnpk , export it dtExportId = MakeExportID(dtViewName); DataView theDV = new DataView(dtExportId); foreach (DataRow theDR1 in dtToExport.Rows) { theDV.RowFilter = "Ptn_Pk=" + theDR1["Ptn_Pk"]; theDR1["ExportId"] = theDV[0][1]; //insert exportid in dtToExport } dtExportId = null; #endregion #region " REMOVE ALL ID COLUMNS " string[] IdColNames = new string[50]; int j = 0; foreach (DataColumn theCol in dtToExport.Columns) { if ((theCol.ColumnName.ToString().Trim().ToUpper() != "EXPORTID") && (theCol.ColumnName.ToString().Trim().ToUpper() != "APPOINTMENT PROVIDER") && (theCol.ColumnName.ToString().Trim().ToUpper() != "AIDSRELIEF FINANCED LAB") && (theCol.ColumnName.ToString().Trim().ToUpper() != "AIDSRELIEF FINANCED DRUG")) { if ((theCol.ColumnName.ToString().ToUpper().IndexOf("ID") >= 0) || (theCol.ColumnName.ToString().ToUpper().IndexOf("PTN_PK") >= 0)) { IdColNames[j] = theCol.ColumnName; j++; } } } for (int k = 0; k < j; k++) { if (IdColNames[k].ToString().Trim() == null) { break; } foreach (DataColumn theCol in dtToExport.Columns) { if (theCol.ColumnName.ToString().Trim() == IdColNames[k].ToString().Trim()) { dtToExport.Columns.Remove(theCol); break; } } } #endregion #region "MONTHLY INCOME" //IQCareUtils theUtils = new IQCareUtils(); //int r = 0; //foreach (DataRow theDR in dtToExport.Rows) //{ // int c = 0; // foreach (DataColumn theCol in dtToExport.Columns) // { // if (theCol.ColumnName.ToString().Trim() == "Monthly Income") // { // if (dtToExport.Rows[r][c] == null) // { // dtToExport.Rows[r][c] = System.DBNull.Value; // } // else if (dtToExport.Rows[r][c].ToString() == "") // { // dtToExport.Rows[r][c] = System.DBNull.Value; // } // else if (Convert.ToInt32(dtToExport.Rows[r][c]) == 0) // { // dtToExport.Rows[r][c] = System.DBNull.Value; // } // } // c++; // } // r++; //} #endregion #region "CHANGE COLUMN NAME FROM EXPORTID TO PATIENTID" foreach (DataColumn theDC in dtToExport.Columns) { if (theDC.ColumnName.ToString() == "ExportId") { theDC.ColumnName = "PatientId"; break; } } #endregion #region "REMOVE BLANK ROWS-1 & SORT" dtToExport = RemoveBlankRow(dtToExport); SortDataTable(dtToExport, "PatientId Asc"); #endregion #region "MERGE DATA" //DataTable dtToExport2 = new DataTable(); //foreach (DataColumn theDC in dtToExport.Columns) //{ // dtToExport2.Columns.Add(theDC.ColumnName.ToString().Trim(), System.Type.GetType("System.String")); //} ////--insert unique values //int r = 0; //foreach (DataRow theDR in dtToExport.Rows) //{ // int c = 0; // foreach (DataColumn theDC in dtToExport.Columns) // { // if (c == 0) //insert new row else update // { // DataRow theDR2 = dtToExport2.NewRow(); // theDR2[c] = ""; // dtToExport2.Rows.Add(theDR2); // } // if (r == 0) // { // dtToExport2.Rows[r][c] = theDR[c].ToString().Trim(); // } // else // { // if (dtToExport.Rows[r][0].ToString().Trim() == dtToExport.Rows[r - 1][0].ToString().Trim()) // { // if (dtToExport.Rows[r][c].ToString().Trim() != dtToExport.Rows[r - 1][c].ToString().Trim()) // dtToExport2.Rows[r][c] = theDR[c].ToString().Trim(); // } // else // { // if (dtToExport.Rows[r][c].ToString().Trim().Length != 0) // dtToExport2.Rows[r][c] = theDR[c].ToString().Trim(); // } // } // c++; // } // r++; //} //dtToExport = null; #endregion #region "REMOVE BALNK ROW-2" DataView dvToExport2 = new DataView(); dvToExport2 = RemoveBlankRow2(dtToExport); return(dvToExport2); #endregion }
private void ExportToExcel(string theQuery) { try { IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration"); DataTable dtToExport = ExportManager.RunQuery(theQuery); ExportManager = null; //dg1.DataSource = dvToExport2; dg1.DataSource = dtToExport; dg1.DataBind(); dg1.HeaderStyle.Font.Bold = true; dg1.GridLines = GridLines.Vertical; Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=Export.xls"); Response.Charset = ""; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); dg1.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); //dg1.Visible = false; #region "EXPORT TEST - Column wise" /* * //Excel.ApplicationClass theApp = new Excel.ApplicationClass(); * * Excel._Application theApp = new Excel._Application(); * //Excel.Workbook theWorkBook = theApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); * Excel.Workbook theWorkBook = theApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); * //Excel.Worksheet theSheet = (Excel.Worksheet)theWorkBook.Worksheets[1]; * Excel.Worksheet theSheet = (Excel.Worksheet)theWorkBook.Worksheets[1]; * * /////////////////// Include Header //////////////////// * * Excel.Range theRange = theSheet.get_Range(GetIndexLetter(0) + "1", GetIndexLetter(dg1.Columns.Count) + "1"); * for (int i = 0; i < dg1.Columns.Count; i++) * { * ((Excel.Range)theRange["1", GetIndexLetter(i)]).Value2 = dg1.Columns[i].HeaderText.ToString(); * ((Excel.Range)theRange["1", GetIndexLetter(i)]).ColumnWidth = 20; * } * * // theSheet.SaveAs("c:\\Issues.xls", ".xls", "", "", false, false, false, "", "", true); * * theApp.Workbooks[1].Close(true, "c:\\Issues.xls", null); * //theApp.Save("c:\\issue.xls"); * theSheet = null; * theApp = null; * theWorkBook = null; */ #endregion "EXPORT TEST - Column wise" } catch (Exception err) { MsgBuilder theBuilder = new MsgBuilder(); theBuilder.DataElements["MessageText"] = err.Message.ToString(); IQCareMsgBox.Show("#C1", theBuilder, this); //IQCareMsgBox.Show("TooManyRec",this); } }