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"));
}
