protected void Page_Load(object sender, EventArgs e) { PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook(); PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("Sheet1"); PageOffice.ExcelReader.Table table = sheet.OpenTable("B4:D8"); while (!table.EOF) { //获取提交的数值 //DataFields.Count标识的是table的列数 if (!table.DataFields.IsEmpty) { content += "<br/>月份名称:" + table.DataFields[0].Text; content += "<br/>计划完成量:" + table.DataFields[1].Text; content += "<br/>实际完成量:" + table.DataFields[2].Text; content += "<br/>*********************************************"; } //循环进入下一行 table.NextRow(); } table.Close(); workBook.ShowPage(500, 400); workBook.Close(); }
protected void Page_Load(object sender, EventArgs e) { PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook(); PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("Sheet1"); PageOffice.ExcelReader.Table table = sheet.OpenTable("Info"); int result = 0; while (!table.EOF) { //获取提交的数值 //DataFields.Count标识的是提交过来的table的列数 if (!table.DataFields.IsEmpty) { content += "<br/>月份名称:" + table.DataFields[0].Text; content += "<br/>计划完成量:" + table.DataFields[1].Text; content += "<br/>实际完成量:" + table.DataFields[2].Text; content += "<br/>累计完成量:" + table.DataFields[3].Text; if (string.IsNullOrEmpty(table.DataFields[2].Text) || !int.TryParse(table.DataFields[2].Text, out result) || !int.TryParse(table.DataFields[1].Text, out result)) { content += "<br/>完成率:0"; } else { float f = int.Parse(table.DataFields[2].Text); f = f / int.Parse(table.DataFields[1].Text); content += "<br/>完成率:" + string.Format("{0:P}", f); } content += "<br/>*********************************************"; } //循环进入下一行 table.NextRow(); } table.Close(); workBook.ShowPage(500, 400); workBook.Close(); }
/// <summary> /// 插入产品信息 /// </summary> /// <param name="cmd"></param> /// <param name="workBook"></param> /// <param name="sheet"></param> /// <param name="orderId">和产品相对应的客户ID</param> private void InsertProductInfo(OleDbCommand cmd, PageOffice.ExcelReader.Workbook workBook, PageOffice.ExcelReader.Sheet sheet, string orderId) { PageOffice.ExcelReader.Table table = sheet.OpenTable("OrderDetail"); while (!table.EOF) { //根据当前OrderID重新插入产品数据 string sql = "insert into OrderDetail(OrderID, ProductCode, ProductName, ProductType, Unit, Quantity, Price) values(" + orderId; if (!table.DataFields.IsEmpty) //数据字段非空时 { int qua = 0; //数量 if (table.DataFields[4].Value.Trim().Length > 0 && int.TryParse(table.DataFields[4].Value.Trim(), out qua)) { qua = int.Parse(table.DataFields[4].Value.Trim()); } float price = 0.00f;//单价 if (float.TryParse(table.DataFields[5].Value.Trim(), out price)) { price = float.Parse(table.DataFields[5].Value.Trim()); } sql += ",'" + table.DataFields[0].Value + "','" + table.DataFields[1].Value + "','" + table.DataFields[2].Value.Trim() + "','" + table.DataFields[3].Value.Trim() + "'," + qua + ",'" + price + "')"; try { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } catch (Exception ex) { strErrHtml += "客户ID为" + orderId + "的产品订单信息添加失败,失败原因为:" + ex.Message + "\n";; } } table.NextRow(); //跳到下一行 } table.Close(); //关闭table }
/// <summary> /// 提取并保存文档中的数据 /// </summary> /// <param name="FilePath"></param> /// <param name="ProjectID"></param> /// <returns></returns> public ActionResult SaveData(string FilePath, int ProjectID, int RecordID) { PageOffice.ExcelReader.Workbook wb = new PageOffice.ExcelReader.Workbook(); PageOffice.ExcelReader.Sheet sheet = wb.OpenSheet("Sheet1"); E_tb_Project eProject = tProject.GetModel(ProjectID); PageOffice.ExcelReader.Table table = sheet.OpenTable(eProject.SampleDataRange.Replace(":", ":").ToUpper()); DataTable dt = new DataTable(); for (int i = 0; i < table.DataFields.Count; i++) { dt.Columns.Add(table.DataFields[i].Text); } table.NextRow(); while (!table.EOF) { //获取提交的数值 if (!table.DataFields.IsEmpty) { DataRow row = dt.NewRow(); for (int i = 0; i < table.DataFields.Count; i++) { row[i] = table.DataFields[i].Text; } dt.Rows.Add(row); } try { table.NextRow(); //循环进入下一行 } catch { break; } } //删除历史数据 tRecordSample.DeleteListByWhere("RecordFilePath='" + FilePath + "'"); tTestReportData.DeleteByWhere("RecordFilePath='" + FilePath + "'"); //关联检验报告 Regex reg = new Regex("^[0-9]+(.[0-9])?$"); for (int i = 0; i < dt.Rows.Count; i++) { if (eProject.IsPesCheck != null && Convert.ToInt32(eProject.IsPesCheck.ToString()) == 1) //判断是否为检验农药残留项目 { DataRow item = dt.Rows[i]; string SampleName = item["样品名称"].ToString(); //样品名称 string strResult = item["复检值2"].ToString(); //复检值2 if (strResult.Trim() == "") { strResult = item["最终值"].ToString();//最终值 } if (!string.IsNullOrEmpty(SampleName.Trim())) { E_tb_TestReportData eTestReportData = new E_tb_TestReportData(); eTestReportData.RecordID = RecordID; //原始记录ID eTestReportData.RecordFilePath = FilePath; //原始记录文件名 eTestReportData.ReportID = 0; //检验报告ID eTestReportData.TestName = SampleName; //检验名称/检验项目名称 (农药残留检验项目,直接显示样品名称) eTestReportData.TestStandard = ""; //检验标准 eTestReportData.TestResult = strResult; //检验结果 eTestReportData.QualifiedLevel = ""; //是否合格 eTestReportData.TestPersonnelName = ""; //检验人 tTestReportData.Add(eTestReportData); } } else { DataRow item = dt.Rows[i]; string strSampleID = item["编号"].ToString(); string strResult = item["最终值"].ToString(); tb_Sample eSample = tSample.GetModelList("sampleNum='" + strSampleID.Trim() + "'").FirstOrDefault(); if (eSample != null && !string.IsNullOrEmpty(strResult)) { E_tb_TestReportData eTestReportData = new E_tb_TestReportData(); eTestReportData.RecordID = RecordID; //原始记录ID eTestReportData.RecordFilePath = FilePath; //原始记录文件名 eTestReportData.ReportID = 0; //检验报告ID eTestReportData.TestName = ""; //检验名称/检验项目名称 eTestReportData.TestStandard = ""; //检验标准 eTestReportData.TestResult = strResult; //检验结果 eTestReportData.QualifiedLevel = ""; //是否合格 eTestReportData.TestPersonnelName = ""; //检验人 tTestReportData.Add(eTestReportData); } } } table.Close(); wb.Close(); return(View("/views/OriginalRecord/SaveData.cshtml")); }