protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["PatientID"] != null)
{
try
{
patientid = Convert.ToInt32(Session["PatientId"]);
IReports theQBuilderReports = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports, BusinessProcess.Reports");
theRepDS = theQBuilderReports.GetbluecartIEFUinfo(patientid);
// theRepDS.WriteXml("c:\\TestHL7.xml");
#region "TableNames"
// Moh reg
theRepDS.Tables[0].TableName = "patientinfo";
theRepDS.Tables[1].TableName = "EmergContact";
theRepDS.Tables[2].TableName = "Referred";
theRepDS.Tables[3].TableName = "PrevARV";
theRepDS.Tables[4].TableName = "HIVDiagnosis";
theRepDS.Tables[5].TableName = "patientAllergy";
theRepDS.Tables[6].TableName = "FamilyInfo";
theRepDS.Tables[7].TableName = "patientlabresults";
theRepDS.Tables[8].TableName = "firstgegimen";
// theRepDS.Tables[9].TableName = "secondregimen";
// Moh visit
theRepDS.Tables[9].TableName = "IEFuinfopervisit";
theRepDS.Tables[10].TableName = "Disclosure";
theRepDS.Tables[11].TableName = "NewOisOtherProblems";
// theRepDS.Tables[12].TableName = "Assessment";
theRepDS.Tables[12].TableName = "CotrimoxazoleAdherence";
theRepDS.Tables[13].TableName = "INHdrug";
theRepDS.Tables[14].TableName = "sideeffect";
theRepDS.Tables[15].TableName = "Patientvisitinfo";
theRepDS.Tables[16].TableName = "Tuberclulosis";
theRepDS.Tables[17].TableName = "otherMedication";
theRepDS.Tables[18].TableName = "labinvestigation";
theRepDS.Tables[19].TableName = "ArvdrugAdherence";
theRepDS.Tables[20].TableName = "HIVTest";
#endregion
// Use For HL7 add namespace <ClinicalDocument xmlns=""urn:hl7-org:v3"">")
StringBuilder a = new StringBuilder();
StringBuilder b = new StringBuilder();
StringBuilder c = new StringBuilder();
StringBuilder d = new StringBuilder();
b.Append(@"<?xml version=""1.0""?><ClinicalDocument xmlns=""urn:hl7-org:v3"">");
d.Append("</ClinicalDocument>");
//Response.Redirect("..\\ExcelFiles\\TZNACPMonthlyReport.xls");
theRepDS.WriteXml(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"), XmlWriteMode.WriteSchema);
// Use For HL7 new xml file with namespace
XmlDocument doc1 = new XmlDocument();
doc1.Load(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
XmlNode rootNode = doc1.SelectSingleNode("NewDataSet");
a.Append(rootNode.OuterXml.ToString());
c.Append(b).Append(a).Append(d);
doc1 = new XmlDocument();
doc1.LoadXml(c.ToString());
doc1.Save(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
//theRepDS.WriteXml(Server.MapPath("..\\XMLFiles\\HL7\\" + patientid.ToString() + "HLData.xml"));
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\Bluecard.xml");
theApp.XMLURL = theFilePath;
fillMohRegSheet(theApp);
fillMohVisitsheet(theApp);
// theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportAsAppropriate);
theApp.Export(Server.MapPath("..\\ExcelFiles\\Bluecard.xls"), Excel.SheetExportActionEnum.ssExportActionNone, Excel.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\Bluecard.xls"), Response);
releaseObject(theApp);
}
catch (System.IO.DirectoryNotFoundException exd)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = exd.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
}
catch (COMException cex)
{
throw cex;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
//
private void fillMohVisitsheet(Excel.SpreadsheetClass theApp)
{
Int32 theColIdx = 73;
Int32 theRepIdx = 64;
string xlcolumn;
isvisitSheet = true;
string lastcolumn = string.Empty;
Excel.Worksheet worksheet = (Excel.Worksheet)theApp.Sheets[3];
writeInCell(worksheet, "E5", "Patientvisitinfo", "IQNumber", 0);
//xlcolumn = 'I';
Excel.Range theRange;
for (int a = 0; a < theRepDS.Tables["IEFuinfopervisit"].Rows.Count; a++)
{
visitid = Convert.ToInt32(theRepDS.Tables["IEFuinfopervisit"].Rows[a]["Visit_id"].ToString());
if (a > 17)
{
if (theColIdx > 90)
{
theColIdx = 65;
theRepIdx = theRepIdx + 1;
}
xlcolumn = Convert.ToChar(theRepIdx).ToString() + Convert.ToChar(theColIdx).ToString();
theColIdx = theColIdx + 1;
}
else
{
xlcolumn = Convert.ToChar(theColIdx).ToString();
theColIdx = theColIdx + 1;
}
if (a > 0)
{
writeInCell(worksheet, xlcolumn + "7", "", "V" + (a + 1).ToString(), 0);
//
theRange = (Excel.Range)theApp.ActiveSheet.Cells[7, 73 + a];
theRange.EntireRow.Font.set_Bold(true);
for (int b = 7; b <= 44; b++)
{
theRange = worksheet.Cells.get_Range(xlcolumn + b.ToString(), xlcolumn + b.ToString());
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
lastcolumn = xlcolumn;
writeCellWiseInExcel(worksheet, xlcolumn, a);
}
/////////////////////
if (!string.IsNullOrEmpty(lastcolumn))
{
theRange = worksheet.get_Range("B4", lastcolumn + "6");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B7", lastcolumn + "7");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
object obj = new Object();
obj = (object)("0xD8D8D8").ToString();
theRange.Interior.set_Color(ref obj);
theRange = worksheet.get_Range("B4", lastcolumn + "44");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B10", lastcolumn + "11");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B12", lastcolumn + "15");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B16", lastcolumn + "17");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B18", lastcolumn + "19");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B20", lastcolumn + "21");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B22", lastcolumn + "22");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B23", lastcolumn + "23");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B24", lastcolumn + "24");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B25", lastcolumn + "26");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B27", lastcolumn + "27");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B28", lastcolumn + "28");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B29", lastcolumn + "31");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B32", lastcolumn + "36");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B37", lastcolumn + "37");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B38", lastcolumn + "38");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B39", lastcolumn + "42");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B43", lastcolumn + "43");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B44", lastcolumn + "44");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
//////////////////////////
}
else
{
theRange = worksheet.get_Range("B4", "I6");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
theRange = worksheet.get_Range("B7", "I7");
theRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
releaseObject(theRange);
releaseObject(worksheet);
}
protected void btn_Export_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 7]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[1, 1] = "加班记录(" + txts_time1.Text + "至" + txts_time2.Text + ")";
//xlSheet.get_Range(xlSheet.Cells[2, 13], xlSheet.Cells[2, 20]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 1]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 2], xlSheet.Cells[1, 4]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 5], xlSheet.Cells[1, 7]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 8], xlSheet.Cells[1, 10]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 11], xlSheet.Cells[1, 13]).set_MergeCells(true);
//xlSheet.get_Range(xlSheet.Cells[1, 14], xlSheet.Cells[1, 16]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[2, 1] = "序号";
xlSheet.ActiveSheet.Cells[2, 2] = "部门";
xlSheet.ActiveSheet.Cells[2, 3] = "加班日期";
xlSheet.ActiveSheet.Cells[2, 4] = "加班人员";
xlSheet.ActiveSheet.Cells[2, 5] = "加班时间段";
xlSheet.ActiveSheet.Cells[2, 6] = "加班小时数";
xlSheet.ActiveSheet.Cells[2, 7] = "工作内容";
for (int i = 0; i < grdvw_List.Rows.Count; i++)
{
if (grdvw_List.Rows[i].Cells[0].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 1] = grdvw_List.Rows[i].Cells[0].Text;
}
if (grdvw_List.Rows[i].Cells[2].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 2] = grdvw_List.Rows[i].Cells[2].Text;
}
if (grdvw_List.Rows[i].Cells[3].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 3] = grdvw_List.Rows[i].Cells[3].Text;
}
if (grdvw_List.Rows[i].Cells[4].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 4] = grdvw_List.Rows[i].Cells[4].Text;
}
if (grdvw_List.Rows[i].Cells[5].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 5] = grdvw_List.Rows[i].Cells[5].Text;
}
if (grdvw_List.Rows[i].Cells[6].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 6] = grdvw_List.Rows[i].Cells[6].Text;
}
if (grdvw_List.Rows[i].Cells[7].Text.Trim() != " ")
{
xlSheet.ActiveSheet.Cells[3 + i, 7] = grdvw_List.Rows[i].Cells[7].Text;
}
}
try
{
string strFileName = "加班记录(" + txts_time1.Text + "至" + txts_time2.Text + ").xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.location.href='ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "';", true);
RemoveFiles(Server.MapPath("."));
}
catch
{
}
}
private void fillMohRegSheet(Excel.SpreadsheetClass theApp)
{
Excel.Worksheet theSheet = (Excel.Worksheet)theApp.Sheets[2];
writeInCell(theSheet, "F4", "patientinfo", "FacilityName", 0);
writeInCell(theSheet, "X4", "patientinfo", "PatientClinicNumber", 0);
writeInCell(theSheet, "S8", "patientinfo", "UniquePatNumber", 0);
if (theRepDS.Tables["patientinfo"].Rows.Count > 0)
{
string name = theRepDS.Tables["patientinfo"].Rows[0]["FirstName"].ToString() + " " + theRepDS.Tables["patientinfo"].Rows[0]["LastName"].ToString();
writeInCell(theSheet, "C11", "", name, 0);
if (theRepDS.Tables["patientinfo"].Rows[0]["Gender"].ToString().Contains("Female"))
{
// writeInCell(theSheet, "D13", "", "N", 0);
writeInCell(theSheet, "I13", "", "Y", 0);
}
else
{
writeInCell(theSheet, "D13", "", "Y", 0);
// writeInCell(theSheet, "I13", "", "N", 0);
}
writeInCell(theSheet, "W11", "", theRepDS.Tables["PatientInfo"].Rows[0]["DOB"].ToString(), 0);;
writeInCell(theSheet, "AD11", "patientinfo", "Age", 0);
writeInCell(theSheet, "X13", "patientinfo", "Address", 0);
writeInCell(theSheet, "D14", "patientinfo", "TelContact", 0);
writeInCell(theSheet, "D15", "patientinfo", "District", 0);
writeInCell(theSheet, "W15", "patientinfo", "Location", 0);
writeInCell(theSheet, "D16", "patientinfo", "SubLocation", 0);
writeInCell(theSheet, "W16", "patientinfo", "LandMark", 0);
if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Single"))
{
//writeInCell(theSheet, "D19", "", "N", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
writeInCell(theSheet, "T21", "", "Y", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Married"))
{
writeInCell(theSheet, "D21", "", "Y", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
//writeInCell(theSheet, "T21", "", "N", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Divorced"))
{
// writeInCell(theSheet, "D19", "", "N", 0);
writeInCell(theSheet, "N19", "", "Y", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
//writeInCell(theSheet, "T21", "", "N", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Cohabiting"))
{
//writeInCell(theSheet, "D19", "", "N", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
writeInCell(theSheet, "T19", "", "Y", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
//writeInCell(theSheet, "T21", "", "N", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Married Polygamous"))
{
//writeInCell(theSheet, "D19", "", "N", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
writeInCell(theSheet, "D19", "", "Y", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
//writeInCell(theSheet, "T21", "", "N", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Married Monogamous"))
{
//writeInCell(theSheet, "D19", "", "N", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
writeInCell(theSheet, "D21", "", "Y", 0);
// writeInCell(theSheet, "T21", "", "N", 0);
}
else if (theRepDS.Tables["patientinfo"].Rows[0]["MaritalStatus"].ToString().Contains("Widowed"))
{
//writeInCell(theSheet, "D19", "", "N", 0);
//writeInCell(theSheet, "N19", "", "N", 0);
//writeInCell(theSheet, "T19", "", "N", 0);
//writeInCell(theSheet, "D21", "", "N", 0);
//writeInCell(theSheet, "N21", "", "N", 0);
//writeInCell(theSheet, "T21", "", "N", 0);
writeInCell(theSheet, "N21", "", "Y", 0);
}
// EmergContact
writeInCell(theSheet, "F23", "EmergContact", "TSName", 0);
writeInCell(theSheet, "Y23", "EmergContact", "TSRelation", 0);
writeInCell(theSheet, "F24", "EmergContact", "TSAddress", 0);
writeInCell(theSheet, "W24", "EmergContact", "TSPhone", 0);
}
//
// for (int i = 0; i < theRepDS.Tables["Referred"].Rows.Count; i++)
if (theRepDS.Tables["Referred"].Rows.Count > 0)
{
if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("VCT"))
{
writeInCell(theSheet, "D30", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("PMTCT"))
{
writeInCell(theSheet, "D28", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("IPD - Ad"))
{
writeInCell(theSheet, "N28", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("TB Clinic"))
{
writeInCell(theSheet, "V28", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("OPD"))
{
writeInCell(theSheet, "AD28", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("IPD - Ch"))
{
writeInCell(theSheet, "L30", "", "Y", 0);
}
else if (theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString().Contains("MCH child"))
{
writeInCell(theSheet, "V30", "", "Y", 0);
}
else
{
writeInCell(theSheet, "D32", "", "Y", 0);
writeInCell(theSheet, "M32", "Referred", "Transfered From Facility", 0);
}
}
//if(!String.IsNullOrEmpty(theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"].ToString()))
//{
//if (Convert.ToString(theRepDS.Tables["Referred"].Rows[0]["ReferredFrom"]) !="")
//{
// writeInCell(theSheet, "V30", "", "Y", 0);
//}
//}
//}
writeInCell(theSheet, "G34", "Referred", "TransferInDate", 0);
writeInCell(theSheet, "V34", "Referred", "ReferredFrom", 0);
writeInCell(theSheet, "G36", "Referred", "Transfered From Facility", 0);
writeInCell(theSheet, "Z36", "Referred", "ARTStartDate", 0);
//PrevARV
if (theRepDS.Tables["PrevARV"].Rows.Count > 0)
{
if (!String.IsNullOrEmpty(Convert.ToString(theRepDS.Tables["PrevARV"].Rows[0]["PrevARV1"])))
{
writeInCell(theSheet, "P43", "PrevARV", "PrevARV1", 0);
writeInCell(theSheet, "AB43", "PrevARV", "PrevARV1dtUsed", 0);
}
if (!String.IsNullOrEmpty(Convert.ToString(theRepDS.Tables["PrevARV"].Rows[0]["PrevARV2"])))
{
writeInCell(theSheet, "P45", "PrevARV", "PrevARV2", 0);
writeInCell(theSheet, "AB45", "PrevARV", "PrevARV2dtUsed", 0);
}
if (!String.IsNullOrEmpty(Convert.ToString(theRepDS.Tables["PrevARV"].Rows[0]["PrevARV3"])))
{
writeInCell(theSheet, "P47", "PrevARV", "PrevARV3", 0);
writeInCell(theSheet, "AB47", "PrevARV", "PrevARV3dtUsed", 0);
}
}
//HIVDiagnosis
writeInCell(theSheet, "I49", "HIVDiagnosis", "DtConfirmHIVPositive", 0);
writeInCell(theSheet, "I50", "HIVDiagnosis", "dtEnrolledHIVCare", 0);
writeInCell(theSheet, "AB51", "HIVDiagnosis", "WHOStage", 0);
writeInCell(theSheet, "I51", "patientAllergy", "Allergy", 0);
//FamilyInfo
for (int i = 0; i < 5; i++)
{
writeInCell(theSheet, "C" + (57 + i).ToString(), "", (i + 1).ToString(), 0);
writeInCell(theSheet, "D" + (57 + i).ToString(), "FamilyInfo", "RName", i);
writeInCell(theSheet, "K" + (57 + i).ToString(), "FamilyInfo", "AgeYear", i);
writeInCell(theSheet, "M" + (57 + i).ToString(), "FamilyInfo", "Relation", i);
writeInCell(theSheet, "T" + (57 + i).ToString(), "FamilyInfo", "HIVStatus", i);
writeInCell(theSheet, "W" + (57 + i).ToString(), "FamilyInfo", "InCare", i);
writeInCell(theSheet, "Z" + (57 + i).ToString(), "FamilyInfo", "CCCNumber", i);
}
//
if (theRepDS.Tables["patientlabresults"].Rows.Count > 0)
{
// if (!String.IsNullOrEmpty(theRepDS.Tables["patientlabresults"].Rows[0]["CD4Count"].ToString()))
// {
writeInCell(theSheet, "BA10", "", "Y", 0);
writeInCell(theSheet, "BI11", "patientlabresults", "CD4Count", 0);
writeInCell(theSheet, "AV11", "patientlabresults", "WHOStage", 0);
writeInCell(theSheet, "AO17", "patientlabresults", "Weight", 0);
writeInCell(theSheet, "AV17", "patientlabresults", "Height", 0);
writeInCell(theSheet, "BD17", "patientlabresults", "BMI", 0);
writeInCell(theSheet, "BK17", "patientlabresults", "WHOStage", 0);
// }
}
for (int i = 0; i < 3; i++)
{
writeInCell(theSheet, "AS" + (i + 22).ToString(), "firstgegimen", "RegimenType", i);
writeInCell(theSheet, "AK" + (i + 22).ToString(), "firstgegimen", "RegDate", i);
}
releaseObject(theSheet);
}
private void Query(int Export)
{
string strtitle = "select * from t_R_Menu inner join t_chart_main on uid=t_R_Menu.id where type=3 order by orderid";
// string strtitle = "select * from t_R_Menu where fatherID=30 and id!=49 order by OrderID";
DataSet dstitle = new MyDataOp(strtitle).CreateDataSet();
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "序号";
xlSheet.ActiveSheet.Cells[1, 2] = "报告标识";
xlSheet.ActiveSheet.Cells[1, 3] = "项目名称";
xlSheet.ActiveSheet.Cells[1, 4] = "委托单位";
}
int i = 5;
if (txt_StartTime.Text.Trim() != "" && txt_EndTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + " 受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else if (txt_StartTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "之后受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else if (txt_EndTime.Text.Trim() != "")
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + "之前受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='98%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP = strTable;
}
strTable += "<th>序号</th>";
strTable += "<th>报告标识</th>";
strTable += "<th>项目名称</th>";
strTable += "<th>委托单位</th>";
// strTableP = "<table id='tableid' width='98%' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1'> <caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月dd日") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月dd日") + " 受理委托监测列表</b></font></caption><tbody><tr align='center'>";
strTableP += "<th>序号</th>";
strTableP += "<th>报告标识</th>";
strTableP += "<th>项目名称</th>";
strTableP += "<th>委托单位</th>";
foreach (DataRow dr in dstitle.Tables[0].Rows)
{
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, i++] = dr["Name"].ToString();
}
strTable += "<th>" + dr["Name"].ToString() + "</th>";
strTableP += "<th>" + dr["Name"].ToString() + "</th>";
}
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, i]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, i]).Font.set_Size(10);
}
strTable += "</tr>";
strTableP += "</tr>";
#region 受理流程
string condstr = "";
if (txt_StartTime.Text.Trim() != "")
{
condstr += " and t.ReportAccessDate >= '" + txt_StartTime.Text.Trim() + " 0:00:00" + "'";
}
if (txt_EndTime.Text.Trim() != "")
{
condstr += " and t.ReportAccessDate <= '" + txt_EndTime.Text.Trim() + " 23:59:59" + "'";
}
if (txt_wtQuery.Text.Trim() != "")
{
condstr += " and wtdepart like '%" + txt_wtQuery.Text.Trim() + "%'";
}
if (txt_bsQuery.Text.Trim() != "")
{
condstr += " and ReportName like '%" + txt_bsQuery.Text.Trim() + "%'";
}
if (drop_fanganQuery.SelectedValue.ToString().Trim() != "")
{
condstr += " and hanwether=1 and statusID>'0'";
}
if (drop_statusQuery.SelectedIndex != drop_statusQuery.Items.Count - 1)
{
switch (drop_statusQuery.SelectedValue.ToString())
{
case "0":
condstr += " and statusID='0' and wether=0"; //受理中
break;
case "1":
condstr += " and statusID='1'"; //初审中
break;
case "1.5":
condstr += " and statusID='1.5' and hanwether=0"; break; //指派中（不出方案）
case "1.6":
condstr += " and statusID='1.5' and hanwether=1"; //指派中（出方案）
break;
case "1.7":
condstr += " and statusID='0' and wether=1"; //初审不通过
break;
case "2":
condstr += " and statusID='2' and wether=0 and hanwether=1"; //踏勘中
break;
case "2.5":
condstr += " and statusID='3' and tkwether=1 and hanwether=1"; //函编写
break;
case "3":
condstr += " and statusID='3' and tkwether=0 and hanwether=1"; //方案编写中
break;
case "4":
condstr += " and statusID='4' and tkwether=0 and hanwether=1"; //报告编制中
break;
case "5":
condstr += " and statusID='5' and tkwether=0 and hanwether=1"; //报告编制完成
break;
case "6":
condstr += " and statusID='5' and tkwether=1 and hanwether=1"; //函编写完成
break;
}
}
string strSql = "select * from t_Y_FlowInfo t where 1=1 " + condstr + " order by t.ReportAccessDate desc";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string wtdw = "";
string itemname = "";
string accessman = "";
string accessdate = "";
string accessremark = "";
string varman1 = "";
string vardate1 = "";
string varremark1 = "";
string varman2 = "";
string vardate2 = "";
string varremark2 = "";
string varman3 = "";
string vardate3 = "";
string varremark3 = "";
string varman4 = "";
string vardate4 = "";
string varremark4 = "";
string reportbs = "";
//string vardate5 = "";
//string varremark5 = "";
string varman0 = "";
string vardate0 = "";
string varremark0 = "";
double status = 0;
string strtemp = "select Name,UserID from t_R_UserInfo";
DataSet ds_User = new MyDataOp(strtemp).CreateDataSet();
for (int j = 0; j < m; j++)
{
wtdw = "";
reportbs = "";
itemname = "";
accessman = "";
accessdate = "";
accessremark = "";
varman0 = "";
varman1 = "";
vardate1 = "";
varremark1 = "";
varman2 = "";
vardate2 = "";
varremark2 = "";
varman3 = "";
vardate3 = "";
varremark3 = "";
varman4 = "";
vardate4 = "";
varremark4 = "";
vardate0 = "";
varremark0 = "";
int flag = 1; //是否出方案
int wether = 1; //初审是否通过
int tkwether = 1; //踏勘是否通过
try
{
flag = int.Parse(ds.Tables[0].Rows[j]["hanwether"].ToString());
wether = int.Parse(ds.Tables[0].Rows[j]["wether"].ToString());
tkwether = int.Parse(ds.Tables[0].Rows[j]["tkwether"].ToString());
}
catch
{ }
DataRow[] drs = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["UserID"].ToString() + "'");
if (drs.Length == 1)
{
accessman = drs[0]["Name"].ToString();
}
DataRow[] drvarman0 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["kschargeman"].ToString() + "'");
if (drvarman0.Length == 1)
{
varman0 = drvarman0[0]["Name"].ToString();
}
DataRow[] drvarman1 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["chargeman"].ToString() + "'");
if (drvarman1.Length == 1)
{
varman1 = drvarman1[0]["Name"].ToString();
}
DataRow[] drvarman2 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman2"].ToString() + "'");
if (drvarman2.Length == 1)
{
varman2 = drvarman2[0]["Name"].ToString();
}
DataRow[] drvarman3 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman3"].ToString() + "'");
if (drvarman3.Length == 1)
{
varman3 = drvarman3[0]["Name"].ToString();
}
if (ds.Tables[0].Rows[j]["varman4"].ToString() != "")
{
DataRow[] drvarman4 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["varman4"].ToString() + "'");
if (drvarman4.Length > 0)
{
varman4 += " 报告编制：" + drvarman4[0]["Name"].ToString();;
}
}
if (ds.Tables[0].Rows[j]["ReportdataUser"].ToString() != "")
{
DataRow[] dryj4 = ds_User.Tables[0].Select("UserID='" + ds.Tables[0].Rows[j]["ReportdataUser"].ToString() + "'");
if (dryj4.Length > 0)
{
varman4 += "数据移交：" + dryj4[0]["Name"].ToString();;;
}
}
wtdw = ds.Tables[0].Rows[j]["wtdepart"].ToString();
itemname = ds.Tables[0].Rows[j]["Projectname"].ToString();
reportbs = ds.Tables[0].Rows[j]["ReportName"].ToString();
accessdate = ds.Tables[0].Rows[j]["ReportAccessDate"].ToString();
accessremark = ds.Tables[0].Rows[j]["urgent"].ToString();
// varman1 = ds.Tables[0].Rows[j]["varman1"].ToString();
vardate1 = ds.Tables[0].Rows[j]["vardate1"].ToString();
varremark1 = ds.Tables[0].Rows[j]["varremark1"].ToString();
// varman2 = ds.Tables[0].Rows[j]["varman2"].ToString();
vardate2 = ds.Tables[0].Rows[j]["vardate2"].ToString();
varremark2 = ds.Tables[0].Rows[j]["varremark2"].ToString();
// varman3 = ds.Tables[0].Rows[j]["varman3"].ToString();
vardate3 = ds.Tables[0].Rows[j]["vardate3"].ToString();
varremark3 = ds.Tables[0].Rows[j]["varremark3"].ToString();
if (ds.Tables[0].Rows[j]["ReportdataDate"].ToString() != "")
{
vardate4 += "数据移交：" + ds.Tables[0].Rows[j]["ReportdataDate"].ToString();
}
if (ds.Tables[0].Rows[j]["vardate4"].ToString() != "")
{
vardate4 += " 报告编制：" + ds.Tables[0].Rows[j]["vardate4"].ToString();
}
varremark4 += ds.Tables[0].Rows[j]["varremark4"].ToString();
status = double.Parse(ds.Tables[0].Rows[j]["StatusID"].ToString());
strTableC += "<tr><td rowspan = '3'>" + (j + 1).ToString() + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ reportbs + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ itemname + "</td><td rowspan = '3' " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ wtdw + "</td><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessman + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ varman0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ varman1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ varman2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ varman3 + "</td>";
strTableC += "<td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ varman4 + "</td>";
strTableC += "</td></tr><tr><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessdate + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ vardate0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ vardate1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ vardate2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ vardate3 + "</td><td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ vardate4 + "</td>";
strTableC += "</td></tr><tr><td " + BgStyle(status, 0, flag, wether, tkwether) + ">"
+ accessremark + "</td><td " + BgStyle(status, 1, flag, wether, tkwether) + ">"
+ varremark0 + "</td><td " + BgStyle(status, 1.5, flag, wether, tkwether) + ">"
+ varremark1 + "</td><td " + BgStyle(status, 2, flag, wether, tkwether) + ">"
+ varremark2 + "</td><td " + BgStyle(status, 3, flag, wether, tkwether) + ">"
+ varremark3 + "</td>";
strTableC +=
"<td " + BgStyle(status, 4, flag, wether, tkwether) + ">"
+ varremark4 + "</td>";
strTableC += "</tr>";
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 1], xlSheet.Cells[j * 3 + 4, 1]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 2], xlSheet.Cells[j * 3 + 4, 2]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 3], xlSheet.Cells[j * 3 + 4, 3]).set_MergeCells(true);
xlSheet.get_Range(xlSheet.Cells[j * 3 + 2, 4], xlSheet.Cells[j * 3 + 4, 4]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[j * 3 + 2, 1] = (j + 1).ToString();
xlSheet.ActiveSheet.Cells[j * 3 + 2, 2] = reportbs;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 3] = itemname;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 4] = wtdw;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 5] = accessman;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 6] = varman0;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 7] = varman1;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 8] = varman2;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 9] = varman3;
xlSheet.ActiveSheet.Cells[j * 3 + 2, 10] = varman4;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 5] = accessdate;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 6] = vardate0;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 7] = vardate1;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 8] = vardate2;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 9] = vardate3;
xlSheet.ActiveSheet.Cells[j * 3 + 3, 10] = vardate4;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 5] = accessremark;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 6] = varremark0;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 7] = varremark1;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 8] = varremark2;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 9] = varremark3;
xlSheet.ActiveSheet.Cells[j * 3 + 4, 10] = varremark4;
//xlSheet.ActiveSheet.Cells[j * 3 + 4, 9] = varremark4;
}
}
if (Export == 1)
{
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * 3 + 1, 10]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * 3 + 1, 10]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTableC += "<tr>";
for (int p = 0; p <= 9; p++)
{
strTableC += "<td>-</td>";
}
strTableC += "</tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "-";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.ActiveSheet.Cells[2, 5] = "-";
xlSheet.ActiveSheet.Cells[2, 6] = "-";
xlSheet.ActiveSheet.Cells[2, 7] = "-";
xlSheet.ActiveSheet.Cells[2, 8] = "-";
xlSheet.ActiveSheet.Cells[2, 9] = "-";
xlSheet.ActiveSheet.Cells[2, 10] = "-";
}
}
#endregion
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "委托监测列表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
private void Query(int Export)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "月份";
xlSheet.ActiveSheet.Cells[1, 2] = "例行监测";
xlSheet.ActiveSheet.Cells[1, 3] = "委托监测";
xlSheet.ActiveSheet.Cells[1, 4] = "数据总量";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 4]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 4]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
strTable = "<table id='tableid' class='listTable' border='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
//string strSql = "select m as [Date],";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType <> 13 THEN 1 ELSE 0 END) AS 监测报告,";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType = 13 THEN 1 ELSE 0 END) AS 测试报告, ";
//strSql += "SUM(CASE WHEN datepart(month, AccessDate) = m and ItemType <> '' THEN 1 ELSE 0 END) AS 数据总量 ";
//strSql += "from t_M_SampleInfor c,( select month('" + dtStartTime + "') m ";
string strSql = "select m as [Date],";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =0 THEN 1 ELSE 0 END) AS 例行监测,";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =1 THEN 1 ELSE 0 END) AS 委托监测,";
strSql += "SUM(CASE WHEN datepart(month, n.fxdate) = m and r.ItemType <> '' THEN 1 ELSE 0 END) AS 数据总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_MonitorItemDetail n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.fxdate >= '" + dtStartTime + "' and n.fxdate < '" + dtEndTime + "' ";
strSql += "and r.id = s.reportid ";
strSql += "and s.SampleID = n.sampleid ";
strSql += "GROUP BY m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string theMonths = "";
string jcReportsN = "";
string csReportsN = "";
string sumReportsN = "";
int jcSum = 0;
int csSum = 0;
for (int i = 0; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
jcReportsN = ds.Tables[0].Rows[i][1].ToString();
csReportsN = ds.Tables[0].Rows[i][2].ToString();
sumReportsN = ds.Tables[0].Rows[i][3].ToString();
jcSum += int.Parse(jcReportsN);
csSum += int.Parse(csReportsN);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + jcReportsN + "</td><td>" + csReportsN + "</td><td>" + sumReportsN + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i + 2, 1] = theMonths;
xlSheet.ActiveSheet.Cells[i + 2, 2] = jcReportsN;
xlSheet.ActiveSheet.Cells[i + 2, 3] = csReportsN;
xlSheet.ActiveSheet.Cells[i + 2, 4] = sumReportsN;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + jcSum.ToString() + "</td><td>" + csSum.ToString() + "</td><td>" + (jcSum + csSum).ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m + 2, 1] = "总计";
xlSheet.ActiveSheet.Cells[m + 2, 2] = jcSum.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 3] = csSum.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 4] = (jcSum + csSum).ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 4]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 4]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + txt_StartTime.Text.Trim() + " 00时至" + txt_EndTime.Text.Trim() + " 24时 监测数据统计表</b></font></caption><tbody><tr align='center'><th>月份</th><th>监测报告</th><th>测试报告</th><th>数据总量</th></tr>";
strTableC += "<tr align='center'><td>总计</td><td>-</td><td>-</td><td>-</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "总计";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 4]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 4]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "监测数据统计表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
private void Query(int Export)
{
int totalM = 0;
string strSql = "";
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "样品类型";
xlSheet.ActiveSheet.Cells[1, 2] = "月份";
xlSheet.ActiveSheet.Cells[1, 3] = "例行监测";
xlSheet.ActiveSheet.Cells[1, 4] = "委托监测";
xlSheet.ActiveSheet.Cells[1, 5] = "数据总量";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 5]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 5]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
DataSet dstype = new MyDataOp("select * from t_M_AnalysisMainClassEx where 1=1 order by orderid").CreateDataSet();
if (dstype != null && dstype.Tables.Count > 0)
{
int p = 0;
foreach (DataRow dr in dstype.Tables[0].Rows)
{
//string type = "";
//DataSet dstypeitem = new MyDataOp("select * from t_M_AnalysisMainClassEx where class='" + dr["ClassID"] + "'").CreateDataSet();
//if (dstypeitem != null && dstypeitem.Tables.Count > 0 && dstypeitem.Tables[0].Rows.Count == 0)
//{
// type = dr["ClassID"].ToString();
//}
//else
//{
// foreach (DataRow drr in dstypeitem.Tables[0].Rows)
// {
// type += drr["ClassID"].ToString() + ",";
// }
// if(type!=""&&type.Length>0)
// type=type.Substring(0,type.Length-1);
//}
strSql = "select m as [Date],s.typeid,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass =0 THEN 1 ELSE 0 END) AS 例行监测,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m and r.rwclass = 1 THEN 1 ELSE 0 END) AS 委托监测,";
strSql += "sum(CASE WHEN datepart(month, n.fxdate) = m THEN 1 ELSE 0 END) AS 数据总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_MonitorItemDetail n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.fxdate >= '" + dtStartTime + "' and n.fxdate < '" + dtEndTime + "' ";
strSql += "and s.typeid =(" + dr["ClassID"].ToString() + ") ";
strSql += "and r.id = s.reportid ";
strSql += "and s.SampleID = n.SampleID ";
strSql += "GROUP BY m,s.typeid order by m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据组成表</b></font></caption><tbody><tr align='center'><th>样品类型</th><th>月份</th><th>例行监测</th><th>委托监测</th><th>数据总量</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 监测数据组成表</b></font></caption><tbody><tr align='center'><th>样品类型</th><th>月份</th><th>例行监测</th><th>委托监测</th><th>数据总量</th></tr>";
#region 地表水
strTableC += "<tr align='center'><td rowspan = '" + (subMonth + 1).ToString() + "'>" + dr["ClassName"].ToString() + "</td>";
if (m != 0)
{
if (Export == 1)
{
// xlSheet.get_Range(xlSheet.Cells[2 + m * p, 1], xlSheet.Cells[subMonth + 2, 1]).set_MergeCells(true);
xlSheet.ActiveSheet.Cells[2 + m * p, 1] = dr["ClassName"].ToString();
}
string theMonths = "";
string jcReportsN = "";
string csReportsN = "";
string sumReportsN = "";
int jcSum = 0;
int csSum = 0;
strTableC += "<td>"
+ ds.Tables[0].Rows[0][0].ToString() + "月份</td><td>"
+ ds.Tables[0].Rows[0][2].ToString() + "</td><td>"
+ ds.Tables[0].Rows[0][3].ToString() + "</td><td>"
+ ds.Tables[0].Rows[0][4].ToString() + "</tr>";
jcSum = int.Parse(ds.Tables[0].Rows[0][2].ToString());
csSum = int.Parse(ds.Tables[0].Rows[0][3].ToString());
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2 + m * p, 2] = ds.Tables[0].Rows[0][0].ToString() + "月份";
xlSheet.ActiveSheet.Cells[2 + m * p, 3] = ds.Tables[0].Rows[0][2].ToString();
xlSheet.ActiveSheet.Cells[2 + m * p, 4] = ds.Tables[0].Rows[0][3].ToString();
xlSheet.ActiveSheet.Cells[2 + m * p, 5] = ds.Tables[0].Rows[0][4].ToString();
}
for (int i = 1; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
jcReportsN = ds.Tables[0].Rows[i][2].ToString();
csReportsN = ds.Tables[0].Rows[i][3].ToString();
sumReportsN = ds.Tables[0].Rows[i][4].ToString();
jcSum += int.Parse(jcReportsN);
csSum += int.Parse(csReportsN);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + jcReportsN + "</td><td>" + csReportsN + "</td><td>" + sumReportsN + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i * p + 2, 2] = theMonths;
xlSheet.ActiveSheet.Cells[i * p + 2, 3] = jcReportsN;
xlSheet.ActiveSheet.Cells[i * p + 2, 4] = csReportsN;
xlSheet.ActiveSheet.Cells[i * p + 2, 5] = sumReportsN;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + jcSum.ToString() + "</td><td>" + csSum.ToString() + "</td><td>" + (jcSum + csSum).ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m * p + 2, 2] = "总计";
xlSheet.ActiveSheet.Cells[m * p + 2, 3] = jcSum.ToString();
xlSheet.ActiveSheet.Cells[m * p + 2, 4] = csSum.ToString();
xlSheet.ActiveSheet.Cells[m * p + 2, 5] = (jcSum + csSum).ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * p + 2, 5]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m * p + 2, 5]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTableC += "<td>" + dt.Month.ToString() + "月份</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2 + m * p, 2] = dt.Month.ToString() + "月份";
xlSheet.ActiveSheet.Cells[2 + m * p, 3] = "0";
xlSheet.ActiveSheet.Cells[2 + m * p, 4] = "0";
xlSheet.ActiveSheet.Cells[2 + m * p, 5] = "0";
}
for (int mth = 1; mth < subMonth; mth++)
{
strTableC += "<tr align='center'><td>" + (int.Parse(dt.Month.ToString()) + mth).ToString() + "月份</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[mth + 2, 2] = (int.Parse(dt.Month.ToString()) + mth).ToString() + "月份";
xlSheet.ActiveSheet.Cells[mth + 2, 3] = "0";
xlSheet.ActiveSheet.Cells[mth + 2, 4] = "0";
xlSheet.ActiveSheet.Cells[mth + 2, 5] = "0";
}
}
strTableC += "<tr align='center'><td>总计</td><td>0</td><td>0</td><td>0</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m * p + 2, 2] = "总计";
xlSheet.ActiveSheet.Cells[m * p + 2, 3] = "0";
xlSheet.ActiveSheet.Cells[m * p + 2, 4] = "0";
xlSheet.ActiveSheet.Cells[m * p + 2, 5] = "0";
}
}
totalM += subMonth;
#endregion
p++;
}
}
#region
#endregion
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "监测数据组成表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}
private void writeCellWiseInExcel(Excel.SpreadsheetClass theSheet)
{
try
{
if ((ddQuarter.SelectedValue.ToString() != "0") && (txtyears.Text != ""))
{
writeInCell(theSheet, "C4", "FacilityInfo1", "Date");
writeInCell(theSheet, "C6", "FacilityInfo1", "FacilityName");
writeInCell(theSheet, "C8", "FacilityInfo1", "District");
writeInCell(theSheet, "E4", "", Application["AppCurrentDate"].ToString());
writeInCell(theSheet, "E8", "FacilityInfo1", "Region");
writeInCell(theSheet, "E15", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE15");
writeInCell(theSheet, "E16", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE16");
writeInCell(theSheet, "E17", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE17");
writeInCell(theSheet, "E18", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE18");
writeInCell(theSheet, "E21", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE21");
writeInCell(theSheet, "E22", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE22");
writeInCell(theSheet, "E23", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE23");
writeInCell(theSheet, "E24", theRepQuarterDS.Tables[1].TableName.ToString(), "Table3ColE24");
writeInCell(theSheet, "D35", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColD35");
writeInCell(theSheet, "D36", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColD36");
writeInCell(theSheet, "D37", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColD37");
writeInCell(theSheet, "D38", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColD38");
writeInCell(theSheet, "D41", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColD41");
writeInCell(theSheet, "E41", theRepQuarterDS.Tables[2].TableName.ToString(), "Table4ColE41");
}
else if ((ddMonth.SelectedValue.ToString() != "0") && (txtYear.Text.Trim() != ""))
{
writeInCell(theSheet, "C5", "FacilityInfo", "Date");
writeInCell(theSheet, "C7", "FacilityInfo", "FacilityName");
writeInCell(theSheet, "C9", "FacilityInfo", "District");
writeInCell(theSheet, "E5", "", Application["AppCurrentDate"].ToString());
writeInCell(theSheet, "E9", "FacilityInfo", "Region");
writeInCell(theSheet, "D16", theRepDS.Tables[1].TableName.ToString(), "Table1ColD16");
writeInCell(theSheet, "D17", theRepDS.Tables[1].TableName.ToString(), "Table1ColD17");
writeInCell(theSheet, "D18", theRepDS.Tables[1].TableName.ToString(), "Table1ColD18");
writeInCell(theSheet, "D19", theRepDS.Tables[1].TableName.ToString(), "Table1ColD19");
writeInCell(theSheet, "E16", theRepDS.Tables[1].TableName.ToString(), "Table1ColE16");
writeInCell(theSheet, "E17", theRepDS.Tables[1].TableName.ToString(), "Table1ColE17");
writeInCell(theSheet, "E18", theRepDS.Tables[1].TableName.ToString(), "Table1ColE18");
writeInCell(theSheet, "E19", theRepDS.Tables[1].TableName.ToString(), "Table1ColE19");
writeInCell(theSheet, "G16", theRepDS.Tables[1].TableName.ToString(), "Table1ColG16");
writeInCell(theSheet, "G17", theRepDS.Tables[1].TableName.ToString(), "Table1ColG17");
writeInCell(theSheet, "G18", theRepDS.Tables[1].TableName.ToString(), "Table1ColG18");
writeInCell(theSheet, "G19", theRepDS.Tables[1].TableName.ToString(), "Table1ColG19");
writeInCell(theSheet, "D24", theRepDS.Tables[2].TableName.ToString(), "Table2ColD24");
writeInCell(theSheet, "D25", theRepDS.Tables[2].TableName.ToString(), "Table2ColD25");
writeInCell(theSheet, "D26", theRepDS.Tables[2].TableName.ToString(), "Table2ColD26");
writeInCell(theSheet, "D27", theRepDS.Tables[2].TableName.ToString(), "Table2ColD27");
writeInCell(theSheet, "D29", theRepDS.Tables[2].TableName.ToString(), "Table2ColD29");
writeInCell(theSheet, "D30", theRepDS.Tables[2].TableName.ToString(), "Table2ColD30");
writeInCell(theSheet, "E24", theRepDS.Tables[2].TableName.ToString(), "Table2ColE24");
writeInCell(theSheet, "E25", theRepDS.Tables[2].TableName.ToString(), "Table2ColE25");
writeInCell(theSheet, "E26", theRepDS.Tables[2].TableName.ToString(), "Table2ColE26");
writeInCell(theSheet, "E27", theRepDS.Tables[2].TableName.ToString(), "Table2ColE27");
writeInCell(theSheet, "E29", theRepDS.Tables[2].TableName.ToString(), "Table2ColE29");
writeInCell(theSheet, "E30", theRepDS.Tables[2].TableName.ToString(), "Table2ColE30");
writeInCell(theSheet, "F32", theRepDS.Tables[2].TableName.ToString(), "Table2ColF32");
}
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
return;
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (validation() == false)
{
return;
}
IQCareUtils theUtils = new IQCareUtils();
if ((ddQuarter.SelectedValue.ToString() != "0") && (txtyears.Text.Trim() != ""))
{
IReports ReportDetails = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports,BusinessProcess.Reports");
theRepQuarterDS = (DataSet)ReportDetails.GetNACPQuarterlyReportData(Convert.ToInt32(ddQuarter.SelectedValue.ToString()), Convert.ToInt32(txtyears.Text.Trim()), Convert.ToInt32(Session["AppLocationId"]));
#region "TableNames"
theRepQuarterDS.Tables[0].TableName = "FacilityInfo1";
theRepQuarterDS.Tables[1].TableName = "Table1Data1";
theRepQuarterDS.Tables[2].TableName = "Table2Data1";
#endregion
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\TZNACPQuarterlyReport.xml");
theApp.XMLURL = theFilePath;
writeCellWiseInExcel(theApp);
//theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"),Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPQuarterlyReport.xls"), Response);
}
else if ((ddMonth.SelectedValue.ToString() != "0") && (txtYear.Text.Trim() != ""))
{
int years = Convert.ToInt32(txtYear.Text);
int Months = Convert.ToInt32(ddMonth.SelectedItem.Value);
IReports ReportDetails = (IReports)ObjectFactory.CreateInstance("BusinessProcess.Reports.BReports,BusinessProcess.Reports");
theRepDS = (DataSet)ReportDetails.GetNACPMonthlyReportData(Months, years, Convert.ToInt32(Session["AppLocationId"]));
#region "TableNames"
theRepDS.Tables[0].TableName = "FacilityInfo";
theRepDS.Tables[1].TableName = "Table1Data";
theRepDS.Tables[2].TableName = "Table2Data";
#endregion
//Response.Redirect("..\\ExcelFiles\\TZNACPMonthlyReport.xls");
Excel.SpreadsheetClass theApp = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
string theFilePath = Server.MapPath("..\\ExcelFiles\\Templates\\TanzaniaNACPMonthlyreport.xml");
theApp.XMLURL = theFilePath;
writeCellWiseInExcel(theApp);
// theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionOpenInExcel, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportAsAppropriate);
theApp.Export(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
//theUtils.OpenExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
IQWebUtils theUtl = new IQWebUtils();
theUtl.ShowExcelFile(Server.MapPath("..\\ExcelFiles\\TZNACPMonthlyReport.xls"), Response);
}
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
return;
}
}
private void writeInCell(Excel.SpreadsheetClass theSheet, string cell, string tablename, string column)
{
Excel.Range theRange = theSheet.Cells.get_Range(cell, cell);
//theRange.WrapText = true;
string theExitvalue = "";
if (theRange.Value2 != null)
{
theExitvalue = theRange.Value2.ToString();
}
else
{
theExitvalue = "";
}
if ((ddQuarter.SelectedValue.ToString() != "0") && (txtyears.Text != ""))
{
try
{
if (tablename != "")
{
if (theExitvalue.ToString().Trim() == "")
{
theRange.Value2 = theRepQuarterDS.Tables[tablename].Rows[0][column].ToString();
}
else
{
theRange.Value2 = theExitvalue + " " + theRepQuarterDS.Tables[tablename].Rows[0][column].ToString();
}
}
else
{
if (theExitvalue.ToString().Trim() == "")
{
theRange.Value2 = column;
}
else
{
theRange.Value2 = theExitvalue + " " + column;
}
}
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
return;
}
}
else if ((ddMonth.SelectedValue.ToString() != "0") && (txtYear.Text != ""))
{
try
{
if (tablename != "")
{
if (theExitvalue.ToString().Trim() == "")
{
theRange.Value2 = theRepDS.Tables[tablename].Rows[0][column].ToString();
}
else
{
theRange.Value2 = theExitvalue + " " + theRepDS.Tables[tablename].Rows[0][column].ToString();
}
}
else
{
if (theExitvalue.ToString().Trim() == "")
{
theRange.Value2 = column;
}
else
{
theRange.Value2 = theExitvalue + " " + column;
}
}
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
return;
}
}
}
private void Query(int Export)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlSheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[1, 1] = "月份";
xlSheet.ActiveSheet.Cells[1, 2] = "南湖区";
xlSheet.ActiveSheet.Cells[1, 3] = "秀洲区";
xlSheet.ActiveSheet.Cells[1, 4] = "联合污水";
xlSheet.ActiveSheet.Cells[1, 5] = "五县";
xlSheet.ActiveSheet.Cells[1, 6] = "其他";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 6]).Font.set_Bold(true);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 6]).Font.set_Size(10);
}
DateTime dtStartTime, dtEndTime;
DateTime dt = Convert.ToDateTime(txt_StartTime.Text);
DateTime dt2 = Convert.ToDateTime(txt_EndTime.Text);
dtStartTime = Convert.ToDateTime(dt.Year + "-" + dt.Month + "-1");
dtEndTime = Convert.ToDateTime(dt2.Year + "-" + dt2.Month + "-1");
dtEndTime = dtEndTime.AddMonths(1);
int subMonth = int.Parse(dt2.Month.ToString()) - int.Parse(dt.Month.ToString()) + 1;
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
strTableP = "<table id='tableid' style= 'BORDER-COLLAPSE:collapse' borderColor= '#00000' border= '1' width='95%'><caption><FONT style='size=3 HEIGHT: 50px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
string strSql = "select m as [Date],";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 1 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 南湖区,";
strSql += "SUM(CASE WHEN datepart(month,n.ReportDate) = m and r.ClientID = 2 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 秀洲区,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 6 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 联合污水,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID = 5 and r.ItemType = 13 THEN n.num ELSE 0 END) AS 五县,";
strSql += "SUM(CASE WHEN datepart(month, n.ReportDate) = m and r.ClientID <> '' and r.ItemType = 13 THEN n.num ELSE 0 END) AS 总量 ";
strSql += "from t_M_ReporInfo r,t_m_sampleinfor s,t_m_monitoritem n,( select month('" + dtStartTime + "') m ";
for (int mth = 1; mth < subMonth; mth++)
{
strSql += " union all select " + (int.Parse(dt.Month.ToString()) + mth).ToString();
}
strSql += ") aa ";
strSql += "where n.ReportDate >= '" + dtStartTime + "' and n.ReportDate < '" + dtEndTime + "' ";
strSql += "and r.id = s.reportid ";
strSql += "and s.id = n.sampleid ";
strSql += "GROUP BY m";
DataSet ds = new MyDataOp(strSql).CreateDataSet();
int m = ds.Tables[0].Rows.Count;
if (m != 0)
{
string theMonths = "";
string nh = "";
string xz = "";
string lh = "";
string wx = "";
string qt = "";
int nhN = 0;
int xzN = 0;
int lhN = 0;
int wxN = 0;
int qtN = 0;
for (int i = 0; i < m; i++)
{
theMonths = ds.Tables[0].Rows[i][0].ToString() + "月份";
nh = ds.Tables[0].Rows[i][1].ToString();
xz = ds.Tables[0].Rows[i][2].ToString();
lh = ds.Tables[0].Rows[i][3].ToString();
wx = ds.Tables[0].Rows[i][4].ToString();
qt = (int.Parse(ds.Tables[0].Rows[i][5].ToString()) - (int.Parse(nh) + int.Parse(xz) + int.Parse(lh) + int.Parse(wx))).ToString();
nhN += int.Parse(nh);
xzN += int.Parse(xz);
lhN += int.Parse(lh);
wxN += int.Parse(wx);
qtN += int.Parse(qt);
strTableC += "<tr align='center'><td>" + theMonths + "</td><td>" + nh + "</td><td>" + xz + "</td><td>" + lh + "</td><td>" + wx + "</td><td>" + qt + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[i + 2, 1] = theMonths;
xlSheet.ActiveSheet.Cells[i + 2, 2] = nh;
xlSheet.ActiveSheet.Cells[i + 2, 3] = xz;
xlSheet.ActiveSheet.Cells[i + 2, 4] = lh;
xlSheet.ActiveSheet.Cells[i + 2, 5] = wx;
xlSheet.ActiveSheet.Cells[i + 2, 6] = qt;
}
}
strTableC += "<tr align='center'><td>总计</td><td>" + nhN.ToString() + "</td><td>" + xzN.ToString() + "</td><td>" + lhN.ToString() + "</td><td>" + wxN.ToString() + "</td><td>" + qtN.ToString() + "</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[m + 2, 1] = "总计";
xlSheet.ActiveSheet.Cells[m + 2, 2] = nhN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 3] = xzN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 4] = lhN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 5] = wxN.ToString();
xlSheet.ActiveSheet.Cells[m + 2, 6] = qtN.ToString();
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 6]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[m + 2, 6]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
else
{
strTable = "<table id='tableid' class='listTable' boder='0' cellspacing='1' width='90%'><caption><FONT style='WIDTH: 102.16%; COLOR: #2292DD;font-size:12pt; LINE-HEIGHT: 150%; FONT-FAMILY: 楷体_GB2312; HEIGHT: 30px'><b>" + DateTime.Parse(txt_StartTime.Text.Trim()).ToString("yyyy年MM月") + "至" + DateTime.Parse(txt_EndTime.Text.Trim()).ToString("yyyy年MM月") + " 测试报告数据组成表</b></font></caption><tbody><tr align='center'><th>月份</th><th>南湖区</th><th>秀洲区</th><th>联合污水</th><th>五县</th><th>其他</th></tr>";
strTableC += "<tr align='center'><td>总计</td><td>-</td><td>-</td><td>-</td><td>-</td><td>-</td></tr>";
if (Export == 1)
{
xlSheet.ActiveSheet.Cells[2, 1] = "总计";
xlSheet.ActiveSheet.Cells[2, 2] = "-";
xlSheet.ActiveSheet.Cells[2, 3] = "-";
xlSheet.ActiveSheet.Cells[2, 4] = "-";
xlSheet.ActiveSheet.Cells[2, 5] = "-";
xlSheet.ActiveSheet.Cells[2, 6] = "-";
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 6]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[2, 6]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
}
}
strTableC += "</tbody></table>";
strTable = strTable + strTableC;
strTableP = strTableP + strTableC;
if (Export == 1)
{
//导出报表
try
{
RemoveFiles(Server.MapPath("."));
string strFileName = "测试报告数据组成表_" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
xlSheet.Export(Server.MapPath(".") + "\\" + strFileName, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "window.open('ShowXls.aspx?file_name=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + "');", true);
}
catch
{
}
}
}