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");
content += "testA1：" + sheet.OpenCellByDefinedName("testA1").Value + "<br/>";
content += "testB1：" + sheet.OpenCellByDefinedName("testB1").Value + "<br/>";
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();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null || Session["UserName"].ToString().Length <= 0)
{
Response.Redirect("Login.aspx");
}
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|demo_excelorder.mdb";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook();;
PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("销售订单");
string sql = "";
if (Request.QueryString["ID"] != null && Request.QueryString["ID"].Length > 0)
{
#region 修改保存
string id = "";
id = Request.QueryString["ID"];
int num;
//保存客户信息
num = UpdateOrInsertCustInfo(cmd, id, workBook, sheet, 0);
if (num > 0) //保存成功
{
int resDelete = 0; //要删除的记录条数
//删除当前orderID下的产品数据
sql = "delete from OrderDetail where OrderId = " + id;
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
strErrHtml += "删除客户ID为" + id + "的产品订单信息失败，失败原因为：" + ex.Message + "\n";
resDelete = -1;
}
//删除成功或无数据可删除时
if (resDelete >= 0)
{
//插入产品信息
InsertProductInfo(cmd, workBook, sheet, id);
}
}
else
{
strErrHtml += "<br>客户信息保存失败！";
}
#endregion
}
else
{
#region 新建保存
int maxId = 0;//OrderMaster表中最大ID号
sql = "select max(ID) from OrderMaster ";
cmd.CommandText = sql;
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
maxId = int.Parse(obj.ToString());
//保存客户信息
if (UpdateOrInsertCustInfo(cmd, "", workBook, sheet, maxId) > 0)
{
//插入产品信息
InsertProductInfo(cmd, workBook, sheet, (maxId + 1).ToString());
}
}
}
catch (Exception ex)
{
strErrHtml += "新建订单失败，失败原因为：" + ex.Message;
}
#endregion
}
//保存失败,弹出提示框
if (strErrHtml.Length > 0)
{
strErrHtml = "\n" + strErrHtml;
workBook.ShowPage(410, 260);
workBook.CustomSaveResult = "error";
}
workBook.Close();
conn.Close();
string mScriptName = "updateorder.aspx";
string mHttpUrl = "http://" + Request.ServerVariables["HTTP_HOST"] + Request.ServerVariables["SCRIPT_NAME"];
BaseUrl = mHttpUrl.Substring(0, mHttpUrl.Length - mScriptName.Length);
}
/// <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"));
}