/// <summary>
/// 导出数据
/// </summary>
/// <param name="DataType"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
public static void OutPut(string fileName,int DataType, DateTime startTime, DateTime endTime)
{
try
{
switch (DataType)
{
case 0:
DataSet ds = new DataSet();
DataTable dt1 = OleHelper.ExecuteDataset(OleHelper.Conn, CommandType.Text, "select * from DataTable where DataTime>=#" + startTime + "# and DataTime<=#" + endTime + "#").Tables[0].Copy();
dt1.TableName = "DataTable";
DataTable dt2 = OleHelper.ExecuteDataset(OleHelper.Conn, CommandType.Text, "select * from CollectInfo").Tables[0].Copy();
dt2.TableName = "CollectInfo";
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.WriteXml(fileName);
break;
case 1:
ds = OleHelper.ExecuteDataset(OleHelper.Conn, CommandType.Text, "select CollectInfo.PipelineName,'',CollectInfo.TestPileID,DataValue,DataTime,CollectInfo.AdminName from DataTable left join CollectInfo on DataTable.CollectInfoId=Collectinfo.ID where DataTime>=#" + startTime + "# and DataTime<=#" + endTime + "#");
foreach (DataRow row in ds.Tables[0].Rows)
{
row["DataValue"] = Convert.ToDouble(row["DataValue"].ToString()).ToString("f3");
row["DataTime"] = Convert.ToDateTime(row["DataTime"].ToString()).ToString("yyyy-MM-dd hh:mm:ss");
}
Excel.ExcelEdit excel = new Excel.ExcelEdit();
excel.Open(Application.StartupPath + "\\Model.xls");
excel.AddTable(ds.Tables[0], "Sheet1", 13, 1);
if (File.Exists(fileName))
File.Delete(fileName);
if (excel.SaveAs(fileName) == false)
{
MessageBox.Show("保存失败");
}
excel.Close();
break;
}
Lib.UpdateApplicationSetting("LastOutputTime", endTime.ToString());
}
catch (Exception ex)
{
MessageBox.Show("导出失败!\n失败原因:\n\t" + ex.Message);
}
}
public void ReadXlsData()
{
Excel.ExcelEdit excel = new Excel.ExcelEdit();
excel.Open(this.fileName);
this.SerialDataTable = new DataTable();
if (this.SerialDataTable.Columns.Count == 0)
{
this.SerialDataTable.Columns.Add("PipelineName");
this.SerialDataTable.Columns.Add("CollectId");
this.SerialDataTable.Columns.Add("TestPileID");
this.SerialDataTable.Columns.Add("DataValue");
this.SerialDataTable.Columns.Add("DataTime");
this.SerialDataTable.Columns.Add("AdminName");
}
if (this.SerialDataTable.Rows.Count > 0)
this.SerialDataTable.Rows.Clear();
excel.ReadTable(this.SerialDataTable, "Sheet1", 13, 1);
}