public void Convert(IList <string> destinationFolderPaths)
{
var slideHeaders = new List <ListDataItem>();
var quotes = new List <Quote>();
var connnectionString =
String.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";",
_sourceFilePath);
using (var connection = new OleDbConnection(connnectionString))
{
try
{
connection.Open();
}
catch (Exception)
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
};
}
if (connection.State == ConnectionState.Open)
{
{
var dataAdapter = new OleDbDataAdapter("SELECT * FROM [6ms$]", connection);
var dataTable = new DataTable();
try
{
dataAdapter.Fill(dataTable);
var columnsCount = dataTable.Columns.Count;
if (columnsCount > 0)
{
var processReading = false;
foreach (DataRow row in dataTable.Rows)
{
var rowValue = row[0]?.ToString().Trim() ?? String.Empty;
if (rowValue.StartsWith("*Presentation Titles", StringComparison.OrdinalIgnoreCase))
{
processReading = true;
continue;
}
if (processReading && rowValue.StartsWith("*", StringComparison.OrdinalIgnoreCase))
{
break;
}
if (String.IsNullOrEmpty(rowValue))
{
break;
}
if (!processReading)
{
continue;
}
var slideHeader = new ListDataItem();
slideHeader.Value = rowValue;
slideHeader.IsDefault = String.Equals(row[1]?.ToString().Trim(), "D", StringComparison.OrdinalIgnoreCase);
slideHeaders.Add(slideHeader);
}
}
}
catch
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
};
}
finally
{
dataAdapter.Dispose();
dataTable.Dispose();
}
slideHeaders.Sort((x, y) =>
{
int result = y.IsDefault.CompareTo(x.IsDefault);
if (result == 0)
{
result = WinAPIHelper.StrCmpLogicalW(x.Value, y.Value);
}
return(result);
});
}
{
var dataAdapter = new OleDbDataAdapter("SELECT * FROM [quotes$]", connection);
var dataTable = new DataTable();
try
{
dataAdapter.Fill(dataTable);
var columnsCount = dataTable.Columns.Count;
if (columnsCount > 0)
{
foreach (DataRow row in dataTable.Rows)
{
var value = row[0]?.ToString().Trim();
var author = row[1]?.ToString().Trim();
if (!String.IsNullOrEmpty(value) && !String.IsNullOrEmpty(author) &&
!value.StartsWith("*", StringComparison.OrdinalIgnoreCase))
{
quotes.Add(new Quote(value, author));
}
}
}
}
catch
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
};
}
finally
{
dataAdapter.Dispose();
dataTable.Dispose();
}
}
}
else
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
}
};
connection.Close();
}
var xml = new StringBuilder();
xml.AppendLine("<CoverSlide>");
foreach (ListDataItem slideHeader in slideHeaders)
{
xml.Append(@"<SlideHeader ");
xml.Append("Value = \"" + slideHeader.Value.Replace(@"&", "&").Replace("\"", """) + "\" ");
xml.Append("IsDefault = \"" + slideHeader.IsDefault + "\" ");
xml.AppendLine(@"/>");
}
foreach (Quote quote in quotes)
{
xml.Append(@"<Quote ");
xml.Append("Value = \"" + quote.Value.Replace(@"&", "&").Replace("\"", """) + "\" ");
xml.Append("Author = \"" + quote.Author.Replace(@"&", "&").Replace("\"", """) + "\" ");
xml.AppendLine(@"/>");
}
xml.AppendLine(@"</CoverSlide>");
foreach (var folderPath in destinationFolderPaths)
{
var xmlPath = Path.Combine(folderPath, DestinationFileName);
using (var sw = new StreamWriter(xmlPath, false))
{
sw.Write(xml.ToString());
sw.Flush();
}
}
}
}
public static void convertExcelToCSV(string sourceFile, bool includeHeader)
{
string excel2010ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile +
";Extended Properties=\"Excel 12.0;{0}\"",
includeHeader ? "" : "HDR=Yes");
string excel2003ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile +
";Extended Properties=\"Excel 8.0;{0}\"",
includeHeader ? "" : "HDR=Yes");
string strConn = sourceFile.EndsWith("xls") ? excel2003ConnectionString : excel2010ConnectionString;
OleDbConnection conn = null;
StreamWriter wrtr = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
FileInfo fileinfo = new FileInfo(sourceFile);
string filePath = fileinfo.FullName;
try
{
conn = new OleDbConnection(strConn);
conn.Open();
var exceldt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string[] sheets = new string[exceldt.Rows.Count];
for (int s = 0; s < sheets.Length; s++)
{
sheets[s] = exceldt.Rows[s].ItemArray[2].ToString();
}
int sheetNumber = 0;
foreach (var sheet in sheets)
{
sheetNumber++;
string targetFile = sourceFile + "_" + sheetNumber.ToString() + ".csv";
cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
wrtr = new StreamWriter(targetFile);
da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int x = 0; x < dt.Rows.Count; x++)
{
string rowString = "";
for (int y = 0; y < dt.Columns.Count; y++)
{
rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
}
wrtr.WriteLine(rowString);
}
Console.WriteLine();
Console.WriteLine(string.Format("Sheet {0} converted to csv", sheet.ToString()));
}
Console.WriteLine("Done! Your " + sourceFile + " has been converted to csv");
Console.WriteLine();
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
Console.ReadLine();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
da.Dispose();
wrtr.Close();
wrtr.Dispose();
}
}
public System.Data.DataTable Read()
{
bool needDelete = false;
string newFileName = "";
if (FileName == string.Empty)
{
return(null);
}
//HDR ( HeaDer Row )
// 若設定值為 Yes，代表 Excel 檔中的工作表第一列是欄位名稱
// 若設定值為 No，代表 Excel 檔中的工作表第一列就是資料了，沒有欄位名稱
//IMEX ( IMport EXport mode )
// 當 IMEX=0 時為「匯出模式」， Excel 檔案只能用來做「寫入」用途。
// 當 IMEX=1 時為「匯入模式」， Excel 檔案只能用來做「讀取」用途。
// 當 IMEX=2 時為「連結模式」，Excel 檔案「讀寫」。
if (Path.GetExtension(FileName).ToLower().Equals(".xls"))
{
OleDbConnStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=Yex;IMEX=1;'", FileName);
}
else if (Path.GetExtension(FileName).ToLower().Equals(".xlsx"))
{
OleDbConnStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 12.0;HDR=Yex;IMEX=1;'", FileName);
}
OleDbConnection conn = new OleDbConnection(OleDbConnStr);
System.Data.DataTable dt = null;
try
{
try
{
conn.Open();
}
catch (Exception ex)
{
//嘗試再連線
try
{
newFileName = FileName.Replace(".xls", "@.xls").Replace(".XLS", "@.XLS");
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook myBook = myExcel.Workbooks.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
myBook.SaveAs(newFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
myBook.Close();
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
myBook = null;
myExcel = null;
GC.Collect();
conn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=Yex;IMEX=1;'", newFileName));
conn.Open();
needDelete = true;
}
catch
{
throw ex;
}
}
System.Data.DataTable dtSheetName = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "Table" }
);
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
strTableNames[i] = dtSheetName.Rows[i]["TABLE_NAME"].ToString();
}
OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(ExcelContent, strTableNames[0]), conn);
dt = new System.Data.DataTable();
adapter.Fill(dt);
adapter.Dispose();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
if (needDelete)
{
File.Delete(newFileName);
}
return(dt);
}
public bool SetCheckIn(Usuarios usuario)
{
bool checkInComplete = false;
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
int id = DataBaseUtilities.GetNextIdForUse("Asistencia", "IdAsistencia", connection);
if (id != 0)
{
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Asistencia WHERE IdAsistencia = 0", connection);
dataAdapter.Fill(dataSet, "Asistencia");
dr = dataSet.Tables["Asistencia"].NewRow();
dr["IdAsistencia"] = id;
dr["IdPersonal"] = usuario.IdUsuario;
dr["IdLibreria"] = usuario.IdLibreria;
dr["Fecha"] = DateTime.Now;
dr["FechaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dataSet.Tables["Asistencia"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Asistencia(IdAsistencia,IdPersonal,IdLibreria,Fecha,HoraEntrada,FechaInt) " +
"VALUES (@IdAsistencia,@IdPersonal,@IdLibreria,@Fecha,TIME(),@FechaInt)";
dataAdapter.InsertCommand.Parameters.Add("@IdAsistencia", OleDbType.Numeric, 0, "IdAsistencia");
dataAdapter.InsertCommand.Parameters.Add("@IdPersonal", OleDbType.Numeric, 0, "IdPersonal");
dataAdapter.InsertCommand.Parameters.Add("@IdLibreria", OleDbType.Numeric, 0, "IdLibreria");
dataAdapter.InsertCommand.Parameters.Add("@Fecha", OleDbType.Date, 0, "Fecha");
dataAdapter.InsertCommand.Parameters.Add("@FechaInt", OleDbType.Numeric, 0, "FechaInt");
dataAdapter.Update(dataSet, "Asistencia");
dataSet.Dispose();
dataAdapter.Dispose();
connection.Close();
checkInComplete = true;
}
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
finally
{
connection.Close();
}
return(checkInComplete);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (mlCOMPANYID == null || mlCOMPANYID == "")
{
mlCOMPANYID = "ISSP3";
}
mlDATATABLE = new DataTable();
mlDATATABLE = this.createTable(mlDATATABLE);
if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
try
{
string fileName = Server.MapPath("~/file/upload_deliv") + "\\" + FileUpload1.PostedFile.FileName;//Path.Combine(Server.MapPath("~/file/upload_deliv"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
FileUpload1.PostedFile.SaveAs(fileName);
string conString = "";
string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
if (ext.ToLower() == ".xls")
{
conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";;
}
else if (ext.ToLower() == ".xlsx")
{
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Invoice No#, Cust# Code, Cust# name, Invoice Date, Branch, Invoice, Site Card, Product Offering, OCM, FCM, collector, code Messenger
mlSQLTEMP = "SELECT * FROM [Data$]";
OleDbConnection con = new OleDbConnection(conString);
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
OleDbCommand mlDBCMD = new OleDbCommand(mlSQLTEMP, con);
OleDbDataAdapter mlDA = new OleDbDataAdapter(mlDBCMD);
DataSet ds = new DataSet();
mlDA.Fill(ds);
mlDA.Dispose();
con.Close();
con.Dispose();
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(dr[0].ToString()))
{
mlSQL = "INSERT INTO INV_DELIVERY (InvNo, InvCustCode, InvCustName, InvDate, InvBranch, InvAmount, InvSiteCard, " +
"InvProdOffer, InvOCM, InvFCM, InvCollector, InvCodeMess, InvMessName, InvStatus, InvDesc, InvReceiptCode, " +
"InvProceedsDate, InvDeliveredDate, InvReturnedDate, InvDoneDate, InvCustPenerima, UserID, UserName, [Disabled]) " +
"VALUES ('" + dr[0].ToString() + "', '" + dr[1].ToString() + "', " +
"'" + dr[2].ToString() + "', '" + dr[3].ToString() + "', '" + dr[4].ToString() + "', " +
"'" + Convert.ToDecimal(dr[5]) + "', '" + dr[6].ToString() + "', " +
"'" + dr[7].ToString() + "', '" + dr[8].ToString() + "', " +
"'" + dr[9].ToString() + "', '" + dr[10].ToString() + "', '" + dr[11].ToString() + "', " +
"'', 'PROCEEDS', '', '', GETDATE(), null, null, null, '', '" + Session["mgUSERID"].ToString() + "', '" + Session["mgNAME"].ToString() + "', '0')";
//mlSQL = "INSERT INTO INV_DELIVERY (InvNo, InvCustCode, InvCustName, InvDate, InvBranch, InvAmount, InvSiteCard, " +
// "InvProdOffer, InvOCM, InvFCM, InvCollector, InvCodeMess, InvMessName, InvStatus, InvDesc, InvReceiptCode, " +
// "InvProceedsDate, InvDeliveredDate, InvReturnedDate, InvDoneDate, InvCustPenerima, UserID, UserName, [Disabled]) " +
// "VALUES ('" + dr["Invoice No#"].ToString() + "', '" + dr["Cust# Code"].ToString() + "', " +
// "'" + dr["Cust# name"].ToString() + "', '" + dr["Invoice Date"].ToString() + "', '" + dr["Branch"].ToString() + "', " +
// "'" + Convert.ToDecimal(dr["Invoice"]) + "', '" + dr["Site Card"].ToString() + "', " +
// "'" + dr["Product Offering"].ToString() + "', '" + dr["OCM"].ToString() + "', " +
// "'" + dr["FCM"].ToString() + "', '" + dr["collector"].ToString() + "', '" + dr["code Messenger"].ToString() + "', " +
// "'', 'PROCEEDS', '', '', GETDATE(), null, null, null, '', '" + Session["mgUSERID"].ToString() + "', '" + Session["mgNAME"].ToString() + "', '0')";
mlOBJGS.ExecuteQuery(mlSQL, "PB", mlCOMPANYID, false, null);
}
}
//OleDbDataReader dr = oledbcmd.ExecuteReader();
//if (dr.HasRows)
//{
// while (dr.Read())
// {
// //mlDATAROW = mlDATATABLE.NewRow();
// //mlDATAROW["InvNo"] = dr["[Invoice No.]"].ToString();
// //mlDATAROW["InvCustCode"] = dr["[Cust. Code]"].ToString();
// //mlDATAROW["InvCustName"] = dr["[Cust. name]"].ToString();
// //mlDATAROW["InvDate"] = dr["[Invoice Date]"].ToString();
// //mlDATAROW["InvBranch"] = dr["[Branch]"].ToString();
// //mlDATAROW["InvAmount"] = Convert.ToDecimal(dr["[Invoice]"]);
// //mlDATAROW["InvSiteCard"] = dr["[Site Card]"].ToString();
// //mlDATAROW["InvProdOffer"] = dr["[Product Offering]"].ToString();
// //mlDATAROW["InvOCM"] = dr["[OCM]"].ToString();
// //mlDATAROW["InvFCM"] = dr["[FCM]"].ToString();
// //mlDATAROW["InvCollector"] = dr["[collector]"].ToString();
// //mlDATAROW["InvCodeMess"] = dr["[code Messenger]"].ToString();
// //mlDATATABLE.Rows.Add(mlDATAROW);
// mlSQL = "INSERT INTO (InvNo, InvCustCode, InvCustName, InvDate, InvBranch, InvAmount, InvSiteCard, " +
// "InvProdOffer, InvOCM, InvFCM, InvCollector, InvCodeMess)" +
// "VALUES ('" + dr["[Invoice No.]"].ToString() + "', '" + dr["[Cust. Code]"].ToString() + "', " +
// "'" + dr["[Cust. name]"].ToString() + "', '" + dr["[Branch]"].ToString() + "', " +
// "'" + Convert.ToDecimal(dr["[Invoice]"]) + "', '" + dr["[Site Card]"].ToString() + "', " +
// "'" + dr["[Product Offering]"].ToString() + "', '" + dr["[OCM]"].ToString() + "', " +
// "'" + dr["[FCM]"].ToString() + "', '" + dr["[collector]"].ToString() + "', '" + dr["[code Messenger]"].ToString() + "')";
// mlOBJGS.ExecuteQuery(mlSQL, "PB", mlCOMPANYID, false, null);
// }
//}
mlMESSAGE.Text = "Uploading data success.";
RetrieveFieldsDetail();
}
catch (Exception ex)
{
mlMESSAGE.Text = "Uploading data error : " + ex.Message.ToString();
}
}
}
protected void btnImportFromCSV_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
try
{
string fileName = System.IO.Path.Combine(Server.MapPath("~/UploadDocuments"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
FileUpload1.PostedFile.SaveAs(fileName);
string conString = "";
string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
if (ext.ToLower() == ".xls")
{
conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (ext.ToLower() == ".xlsx")
{
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
string query = "Select [ClientID], [First_Name], [Last_Name], [Employer], [Title], [Phone_Number], [Zip] from [ClientList$]";
OleDbConnection con = new OleDbConnection(conString);
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
OleDbCommand cmd = new OleDbCommand(query, con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
con.Close();
con.Dispose();
// Update database data
using (ClientDataEntities1 dc = new ClientDataEntities1())
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string empID = dr["ClientID"].ToString();
var v = dc.ClientMasters.Where(a => a.ClientID.Equals(empID)).FirstOrDefault();
if (v != null)
{
v.First_Name = dr["First_Name"].ToString();
v.Last_Name = dr["Last_Name"].ToString();
v.Employer = dr["Employer"].ToString();
v.Title = dr["Title"].ToString();
v.Phone_Number = dr["Phone_Number"].ToString();
v.Zip = dr["Zip"].ToString();
}
else
{
dc.ClientMasters.Add(new ClientMaster
{
ClientID = dr["ClientID"].ToString(),
First_Name = dr["First_Name"].ToString(),
Last_Name = dr["Last_Name"].ToString(),
Employer = dr["Employer"].ToString(),
Title = dr["Title"].ToString(),
Phone_Number = dr["Phone_Number"].ToString(),
Zip = dr["Zip"].ToString(),
});
}
}
dc.SaveChanges();
// populate updated data
populateDatabaseData();
lblMessage.Text = "Excel file has been imported Successfully!";
}
}
catch (Exception)
{
throw;
}
}
}
public void SetTiempoInscripcion(List <Inscripcion> inscripciones)
{
foreach (Inscripcion insc in inscripciones)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
OleDbCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
bool insertCompleted = false;
try
{
connection.Open();
DataSet dataSet = new DataSet();
DataRow dr;
const string OleDbQuery = "SELECT * FROM 2017_Consolidado WHERE Id = @Id";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(OleDbQuery, connection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@Id", insc.Id);
dataAdapter.Fill(dataSet, "2017_Consolidado");
if (dataSet.Tables["2017_Consolidado"].Rows.Count > 0)
{
dr = dataSet.Tables["2017_Consolidado"].Rows[0];
dr.BeginEdit();
dr["DiasInsc"] = insc.DiasInscripcion;
dr["HorasInsc"] = insc.HorasInscripcion;
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = "UPDATE 2017_Consolidado SET DiasInsc = @DiasInsc, HorasInsc = @HorasInsc WHERE Id = @Id"; //Aqui Tambien hay que validar con el nombre
dataAdapter.UpdateCommand.Parameters.Add("@DiasInsc", OleDbType.Numeric, 0, "DiasInsc");
dataAdapter.UpdateCommand.Parameters.Add("@HorasInsc", OleDbType.Numeric, 0, "HorasInsc");
dataAdapter.UpdateCommand.Parameters.Add("@Id", OleDbType.Numeric, 0, "Id");
dataAdapter.Update(dataSet, "2017_Consolidado");
}
dataSet.Dispose();
dataAdapter.Dispose();
insertCompleted = true;
}
catch (OleDbException ex)
{
//string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
//ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,PadronModel", "Padron");
}
catch (Exception ex)
{
//string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
//ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,PadronModel", "Padron");
}
finally
{
connection.Close();
}
}
}
private void btnAfficher_Click(object sender, EventArgs e)
{
if (chk.Checked && rdFc.Checked)
{
if (Factory.idAgentConnecte > 0)
{
loadReportTous1(rdFc.Text.ToString());
}
else
{
loadReportTous(rdFc.Text.ToString());
}
}
else if (chk.Checked && rdUsd.Checked)
{
if (Factory.idAgentConnecte > 0)
{
loadReportTous1(rdUsd.Text.ToString());
}
else
{
loadReportTous(rdUsd.Text.ToString());
}
}
else
{
if (Factory.idAgentConnecte > 0)
{
try
{
FicheDepot rpt = new FicheDepot();
OleDbCommand cmd = new OleDbCommand(@"SELECT Sortie.date_sortie, Sortie.montant, Client.matricule, Client.nom, Client.postnom, Client.prenom, Compte.designation, Compte.typeCompte FROM Agent
INNER JOIN ((Client INNER JOIN Compte ON Client.id = Compte.id_Client)
INNER JOIN Sortie ON Compte.id = Sortie.id_compte) ON Agent.id = Client.id_agent
WHERE (((Sortie.date_sortie)=@date) AND ((Client.id)=@idClient) AND ((Agent.id)=@idAgent))", Factory.Instance.connectDB());
IDataParameter paramDate = cmd.CreateParameter();
paramDate.ParameterName = "@date";
paramDate.Value = Convert.ToDateTime(dtSortie.Text);
IDataParameter paramid = cmd.CreateParameter();
paramid.ParameterName = "@idClient";
paramid.Value = ((Client)cboClient.SelectedItem).Id;
IDataParameter paramidAgent = cmd.CreateParameter();
paramidAgent.ParameterName = "@idClient";
paramidAgent.Value = Factory.idAgentConnecte;
cmd.Parameters.Add(paramDate);
cmd.Parameters.Add(paramid);
cmd.Parameters.Add(paramidAgent);
cmd.ExecuteNonQuery();
OleDbDataAdapter sa = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
sa.Fill(ds, "doc");
rpt.SetDataSource(ds.Tables["doc"]);
crvEntree.ReportSource = rpt;
crvEntree.Refresh();
sa.Dispose();
ds.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erreur de l'afichage du rapport", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
loadReport();
}
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
gdStudentData.DataSource = "";
gdStudentData.DataBind();
string sNuid = txtNUID.Text;
string sLastName = txtLastName.Text;
string sOption = ddlOption.SelectedValue;
string sActive = ddlActive.SelectedValue;
string strWhere = string.Empty;
string connString = @DATAACCESS_DATABASENAME;
OleDbConnection conn = new OleDbConnection(connString);
//Open the Connection
conn.Open();
string query = "SELECT FLD_NU_ID, FLD_FIRST_NAME, FLD_LAST_NAME, FLD_PHONE, FLD_DEPT,";
query += " FLD_PROGRAM, FLD_CONCENTRATION_NAME, FLD_TRANSITION, FLD_SEMESTER, FLD_DOJ, FLD_ACTIVE_STATUS";
query += " FROM STUDENT_DATA";
if (!string.IsNullOrEmpty(sNuid) || !string.IsNullOrEmpty(sLastName) || !string.IsNullOrEmpty(sOption) || !string.IsNullOrEmpty(sActive))
{
query += " WHERE ";
if (!string.IsNullOrEmpty(sNuid))
{
strWhere = "[FLD_NU_ID] = '" + sNuid + "'";
}
if (!string.IsNullOrEmpty(sLastName))
{
if (!string.IsNullOrEmpty(strWhere))
{
strWhere += " AND ";
}
strWhere += "[FLD_LAST_NAME] = '" + sLastName + "'";
}
if (!string.IsNullOrEmpty(sOption))
{
if (!string.IsNullOrEmpty(strWhere))
{
strWhere += " AND ";
}
strWhere += "[FLD_PROGRAM] = '" + sOption + "'";
}
if (!string.IsNullOrEmpty(sActive))
{
if (!string.IsNullOrEmpty(strWhere))
{
strWhere += " AND ";
}
strWhere += "[FLD_ACTIVE_STATUS] = '" + sActive + "'";
}
query += strWhere;
}
OleDbDataAdapter cmd = new OleDbDataAdapter(query, conn);
DataTable dt = new DataTable();
cmd.Fill(dt);
gdStudentData.DataSource = dt;
gdStudentData.DataBind();
cmd.Dispose();
//Close the connection
conn.Close();
}
private void ImportExcelData(string strFilePath, bool bJudge, SqlConnection oneConn, SqlCommand oneComm)
{
string strConn = null;
if (bJudge)
{
strConn = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
}
else
{
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConn);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
myAdapter.Dispose();
myConn.Dispose();
if (myTable.Rows.Count == 0)
{
MessageBox.Show("There is no data to upload.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
myAdapter.Dispose();
myTable.Clear();
myTable.Dispose();
return;
}
oneComm.CommandText = @"SELECT * FROM B_MultiUser";
string strUserName = Convert.ToString(oneComm.ExecuteScalar());
if (!String.IsNullOrEmpty(strUserName))
{
if (String.Compare(strUserName.Trim().ToUpper(), funcLib.getCurrentUserName().Trim().ToUpper()) != 0)
{
MessageBox.Show(strUserName + " is handling RM Balance/Drools Balance data. Please wait for his/her to finish the process.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
oneComm.Dispose();
oneConn.Dispose();
return;
}
}
else
{
oneComm.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName().ToUpper();
oneComm.CommandText = @"INSERT INTO B_MultiUser([UserName]) VALUES(@UserName)";
oneComm.ExecuteNonQuery();
oneComm.Parameters.RemoveAt("@UserName");
}
oneComm.CommandText = @"SELECT [Item No], [Lot No], [RM EHB], [BGD No], [Customs Balance], [Available RM Balance] FROM C_RMBalance";
SqlDataAdapter oneAdapter = new SqlDataAdapter();
oneAdapter.SelectCommand = oneComm;
DataTable dtRmBal = new DataTable();
oneAdapter.Fill(dtRmBal);
oneAdapter.Dispose();
for (int i = 0; i < myTable.Rows.Count; i++)
{
string strAdjAvailableQty = myTable.Rows[i]["ADJ Available Qty"].ToString().Trim();
string strAdjCustomsQty = myTable.Rows[i]["ADJ Customs Qty"].ToString().Trim();
decimal dAdjAvailableQty = 0.0M, dAdjCustomsQty = 0.0M;
if (!String.IsNullOrEmpty(strAdjAvailableQty))
{
dAdjAvailableQty = Math.Round(Convert.ToDecimal(strAdjAvailableQty), 6);
}
if (!String.IsNullOrEmpty(strAdjCustomsQty))
{
dAdjCustomsQty = Math.Round(Convert.ToDecimal(strAdjCustomsQty), 6);
}
if (dAdjAvailableQty != 0.0M || dAdjCustomsQty != 0.0M)
{
string strItemNo = myTable.Rows[i]["Item No"].ToString().Trim().ToUpper();
string strLotNo = myTable.Rows[i]["Lot No"].ToString().Trim().ToUpper();
string strRmEhb = myTable.Rows[i]["RM EHB"].ToString().Trim().ToUpper();
string strBgdNo = myTable.Rows[i]["BGD No"].ToString().Trim().ToUpper();
DataRow[] dr = dtRmBal.Select("[Item No]='" + strItemNo + "' AND [Lot No]='" + strLotNo + "' AND [RM EHB]='" + strRmEhb + "' AND [BGD No]='" + strBgdNo + "'");
if (dr.Length > 0)
{
string strCustomsBal = sqlLib.doubleFormat(Double.Parse(dr[0]["Customs Balance"].ToString().Trim()));
string strAvailRMBal = sqlLib.doubleFormat(Double.Parse(dr[0]["Available RM Balance"].ToString().Trim()));
decimal dCustomsBalance = Convert.ToDecimal(strCustomsBal) + dAdjCustomsQty;
decimal dAvailableRMBalance = Convert.ToDecimal(strAvailRMBal) + dAdjAvailableQty;
oneComm.Parameters.Add("@CustomsBalance", SqlDbType.Decimal).Value = dCustomsBalance;
oneComm.Parameters.Add("@AvailableRMBalance", SqlDbType.Decimal).Value = dAvailableRMBalance;
oneComm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = strItemNo;
oneComm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = strLotNo;
oneComm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = strRmEhb;
oneComm.Parameters.Add("@BGDNo", SqlDbType.NVarChar).Value = strBgdNo;
oneComm.CommandText = @"UPDATE C_RMBalance SET [Customs Balance] = @CustomsBalance, [Available RM Balance] = @AvailableRMBalance " +
"WHERE [Item No] = @ItemNo AND [Lot No] = @LotNo AND [RM EHB] = @RMEHB AND [BGD No] = @BGDNo";
oneComm.ExecuteNonQuery();
oneComm.Parameters.Clear();
oneComm.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = strItemNo;
oneComm.Parameters.Add("@LotNo", SqlDbType.NVarChar).Value = strLotNo;
oneComm.Parameters.Add("@RMEHB", SqlDbType.NVarChar).Value = strRmEhb;
oneComm.Parameters.Add("@BGDNo", SqlDbType.NVarChar).Value = strBgdNo;
oneComm.Parameters.Add("@AdjAvailableQty", SqlDbType.Decimal).Value = dAdjAvailableQty;
oneComm.Parameters.Add("@AdjCustomsQty", SqlDbType.Decimal).Value = dAdjCustomsQty;
oneComm.Parameters.Add("@Creater", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName();
oneComm.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy"));
oneComm.CommandText = @"INSERT INTO L_RMBalance_Adjustment([Item No], [Lot No], [RM EHB], [BGD No], [ADJ Available Qty], [ADJ Customs Qty], [Creater], " +
"[Created Date]) VALUES(@ItemNo, @LotNo, @RMEHB, @BGDNo, @AdjAvailableQty, @AdjCustomsQty, @Creater, @CreatedDate)";
oneComm.ExecuteNonQuery();
oneComm.Parameters.Clear();
}
}
}
dtRmBal.Dispose();
myTable.Dispose();
oneComm.CommandText = @"DELETE FROM B_MultiUser";
oneComm.ExecuteNonQuery();
}
public void zdbtext()
{
Database db = HostApplicationServices.WorkingDatabase;
Editor ed = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument.Editor;
ed.WriteMessage("请选取一行单行文字作为表格第一行\n"
+ "每列文字的字体与此列第一行文字相同。");
//从数据库读取文件
OleDbConnection con = null;
OleDbDataAdapter da = null;
try
{
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=D:\\xl2cad.mdb");
con.Open();
string sql = "select * from excel2cad";
da = new OleDbDataAdapter(sql, con);
System.Data.DataSet ds = new System.Data.DataSet();
dt = new System.Data.DataTable();
da.Fill(ds, "excel2cad");
dt = ds.Tables[0];
con.Close();
con.Dispose();
da.Dispose();
System.GC.Collect();
}
catch (SystemException ex)
{
ed.WriteMessage(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
System.GC.Collect();
}
//选取第一行DBText
//单行文字的过滤器
FilterType[] filter = new FilterType[1];
filter[0] = FilterType.Text;
DBObjectCollection EntityCollection = GetFirstRow(filter);
//写入数据
//判断选取的列数和数据库的列数是否相同
//按照选好的行距，拷贝一行，修改这一行的数据，循环
//提交
int rowCountofSource = dt.Rows.Count;
int columnCountofSource = dt.Columns.Count;
//必须是同一行的才能算入选中的列数
//先取第一个的Y值
int columnCountofSelect = 0;
DBText firstText = EntityCollection[EntityCollection.Count - 1] as DBText;
double Y = firstText.AlignmentPoint.Y;
foreach (DBObject dbo in EntityCollection)
{
DBText dbt = dbo as DBText;
if (dbt.AlignmentPoint.Y == Y)
{
columnCountofSelect = columnCountofSelect + 1;
}
}
double dis = 0;
if (columnCountofSource == columnCountofSelect)
{
//获取行距
PromptDoubleResult pdr = ed.GetDistance("请输入行距\n");
if (pdr.Status == PromptStatus.OK)
{
dis = pdr.Value;
}
//拷贝、修改
using (Transaction transaction = db.TransactionManager.StartTransaction())
{
BlockTable bt = transaction.GetObject(db.BlockTableId, OpenMode.ForRead) as BlockTable;
BlockTableRecord modelSpace = transaction.GetObject(bt[BlockTableRecord.ModelSpace], OpenMode.ForWrite) as BlockTableRecord;
try
{
for (int i = 0; i < rowCountofSource; i++)
{
for (int j = 0; j < EntityCollection.Count; j++)
{
Entity ent = EntityCollection[EntityCollection.Count - j - 1] as Entity;
Entity newEnt = ent.Clone() as Entity;
//(-1 * dis * i)就是向下逐行添加，改为(1 * dis * i)就是向上，以后有需求可以增加判断
newEnt.TransformBy(Matrix3d.Displacement(new Vector3d(0, (-1 * dis * i), 0)));
DBText newtext = newEnt as DBText;
newtext.TextString = dt.Rows[i][j].ToString();
modelSpace.AppendEntity(newtext);
transaction.AddNewlyCreatedDBObject(newtext, true);
}
}
transaction.Commit();
ed.WriteMessage("\n已成功写入！");
}
catch
{
ed.WriteMessage("Error！");
}
finally
{
transaction.Dispose();
}
}
//删除原始第一行用来选择的文字
using (Transaction transaction = db.TransactionManager.StartTransaction())
{
BlockTable bt = transaction.GetObject(db.BlockTableId, OpenMode.ForRead) as BlockTable;
BlockTableRecord modelSpace = transaction.GetObject(bt[BlockTableRecord.ModelSpace], OpenMode.ForWrite) as BlockTableRecord;
try
{
foreach (DBObject dbo in EntityCollection)
{
Entity ent = transaction.GetObject(dbo.ObjectId, OpenMode.ForWrite, true) as Entity;
ent.Erase(true);
}
transaction.Commit();
}
catch
{
ed.WriteMessage("Error！");
}
finally
{
transaction.Dispose();
}
}
}
else
{
ed.WriteMessage(String.Format("Excel表头({0}个)与选取的个数({1}个)不符！", columnCountofSource, columnCountofSelect));
}
}
public static DataTable ReadExcelFile(string filename, string ext, string sheetname)
{
OleDbConnection _conn = null;
OleDbCommand _comm = null;
OleDbDataAdapter _adapter = null;
DataSet _ds = null;
string sQuery = "SELECT * FROM " + sheetname;
string sConnString = "";
if (ext == ".xls")
{
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else
{
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
try
{
//connect
_conn = new OleDbConnection(sConnString);
//command
_comm = new OleDbCommand(sQuery, _conn);
//adapter
_adapter = new OleDbDataAdapter(_comm);
//dataset
_ds = new DataSet();
//open connection
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
}
//execute query and convert result to dataset
_adapter.Fill(_ds);
//disposing
_adapter.Dispose();
_comm.Dispose();
_conn.Close();
_conn.Dispose();
if (_ds != null && _ds.Tables.Count > 0)
{
return(_ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
return(null);
}
}
private void importExcelThucAn(string dir, string file)
{
string NguoiChoAn = "";
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT = 0;
DateTime NgayAn = DateTime.MinValue;
string columnName = "";
decimal value = 0;
int idVatTu = 0;
int SoCaAn = 0;
string StrChuong = "";
string StrKL = "";
decimal kl = 0;
int LoaiCa = 0;
string[] aChuongOnly = null;
string[] aChuongExcept = null;
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
int numSheet = int.Parse(txtNumSheet.Text);
for (int num = 1; num <= numSheet; num++)
{
string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 2;
foreach (DataRow r in dTable.Rows)
{
DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture);
DateTime NgayChoAnOutput = DateTime.MinValue;
int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput);
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = dTable.Columns[i].ColumnName;
if (columnName.StartsWith("LoaiCa"))
{
LoaiCa = Convert.ToInt32(r[i]);
aChuongOnly = null;
aChuongExcept = null;
}
else if (columnName.StartsWith("Chuong-Only"))
{
string sChuongOnly = r[i].ToString();
sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2);
aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Chuong-Except"))
{
string sChuongExcept = r[i].ToString();
sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2);
aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else
{
value = 0;
if (r[i] != DBNull.Value) value = Convert.ToDecimal(r[i]);
if (value != 0)
{
idVatTu = int.Parse(columnName.Substring(columnName.IndexOf('(') + 1, columnName.IndexOf(')') - columnName.IndexOf('(') - 1));
DataTable tblChuong = csCont.CaSauAn_GetChuongByThucAnByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NguoiChoAn, out NgayAn);
if (aChuongOnly != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
int k = 0;
for (k = 0; k < aChuongOnly.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k]))
{
break;
}
}
if (k == aChuongOnly.Length) tblChuong.Rows.RemoveAt(j);
}
}
if (aChuongExcept != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
for (int k = 0; k < aChuongExcept.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k]))
{
tblChuong.Rows.RemoveAt(j);
break;
}
}
}
}
SoCaAn = 0;
StrChuong = "";
StrKL = "";
foreach (DataRow rC in tblChuong.Rows)
{
SoCaAn += Convert.ToInt32(rC["SoLuong"]);
}
foreach (DataRow rC in tblChuong.Rows)
{
StrChuong += "@" + rC["IDChuong"].ToString() + "@";
kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn);
StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@";
}
csCont.CaSauAn_InsertUpdateThucAn_UpdateChuong(IDCaSauAn, idVatTu, LoaiCa, SoCaAn, StrChuong, StrKL);
}
}
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
}
/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static DataTable GetDataTable(string filePath)
{
string connStr = getConnectionString(filePath);;
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem);
foreach (DataColumn column in dsItem.Tables[0].Columns)
{
column.ColumnName = column.ColumnName.Replace("#", ".");
}
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
public bool UpdateTermino(Terminos termino)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
OleDbCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
bool insertCompleted = false;
try
{
connection.Open();
DataSet dataSet = new DataSet();
DataRow dr;
const string sqlQuery = "SELECT * FROM Terminos WHERE IdTermino = @IdTermino";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlQuery, connection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@IdTermino", termino.IdTermino);
dataAdapter.Fill(dataSet, "Terminos");
dr = dataSet.Tables["Terminos"].Rows[0];
dr.BeginEdit();
dr["Termino"] = termino.Termino;
dr["TerminoStr"] = termino.TerminoStr;
dr["Definicion"] = termino.Definicion;
dr["Bibliografia"] = termino.Bibliografia;
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = "UPDATE Terminos SET Termino = @Termino, TerminoStr = @TerminoStr, Definicion = @Definicion, Bibliografia = @Bibliografia WHERE IdTermino = @IdTermino";
dataAdapter.UpdateCommand.Parameters.Add("@Termino", OleDbType.VarChar, 0, "Termino");
dataAdapter.UpdateCommand.Parameters.Add("@TerminoStr", OleDbType.VarChar, 0, "TerminoStr");
dataAdapter.UpdateCommand.Parameters.Add("@Definicion", OleDbType.VarChar, 0, "Definicion");
dataAdapter.UpdateCommand.Parameters.Add("@Bibliografia", OleDbType.VarChar, 0, "Bibliografia");
dataAdapter.UpdateCommand.Parameters.Add("@IdTermino", OleDbType.Numeric, 0, "IdTermino");
dataAdapter.Update(dataSet, "Terminos");
dataSet.Dispose();
dataAdapter.Dispose();
insertCompleted = true;
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TerminosModel", "BusquedaLatinos");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TerminosModel", "BusquedaLatinos");
}
finally
{
connection.Close();
}
return(insertCompleted);
}
protected void imgPrint_Click(object sender, EventArgs e)
{
GridViewRow selectedRow = ((Control)sender).Parent.NamingContainer as GridViewRow;
string sNuId = selectedRow.Cells[3].Text;
string connString = @DATAACCESS_DATABASENAME;
OleDbConnection conn = new OleDbConnection(connString);
//Open the Connection
conn.Open();
string query = "SELECT FLD_NU_ID, FLD_FIRST_NAME, FLD_LAST_NAME, FLD_EMAIL, FLD_PHONE, FLD_DEPT,";
query += " FLD_PROGRAM, FLD_CONCENTRATION_NAME, FLD_TRANSITION, FLD_CREDITS, FLD_SEMESTER, FLD_DOJ";
query += " FROM STUDENT_DATA";
query += " WHERE [FLD_NU_ID] = '" + sNuId + "'";
OleDbDataAdapter cmd = new OleDbDataAdapter(query, conn);
DataTable dt = new DataTable();
cmd.Fill(dt);
string query1 = "SELECT FLD_COURSE_ID AS 'Course Id', FLD_COURSE_NAME as 'Course Name',";
query1 += " FLD_CREDITS as 'Credits', FLD_SEMESTER as 'Semester' , FLD_YEAR as 'Year', FLD_TYPE as 'Type', FLD_TYPE_ID ";
query1 += " FROM PLAN_OF_STUDY";
query1 += " WHERE FLD_NU_ID = '" + sNuId + "' ORDER BY FLD_TYPE_ID ASC, FLD_YEAR ASC, FLD_SEMESTER DESC";
OleDbDataAdapter cmd1 = new OleDbDataAdapter(query1, conn);
DataTable dt1 = new DataTable();
cmd1.Fill(dt1);
dt1.Columns.Remove("FLD_TYPE_ID");
cmd1.Dispose();
//Close the connection
conn.Close();
// Create a Document object
var document = new Document(PageSize.A4, 20, 20, 40, 10);
// Create a new PdfWrite object, writing the output to a MemoryStream
var output = new MemoryStream();
var writer = PdfWriter.GetInstance(document, output);
// Open the Document for writing
document.Open();
// First, create our fonts...
var titleFont = FontFactory.GetFont("Arial", 18, Font.BOLD);
var subTitleFont = FontFactory.GetFont("Arial", 14, Font.BOLD);
var boldTableFont = FontFactory.GetFont("Arial", 12, Font.BOLD);
var endingMessageFont = FontFactory.GetFont("Arial", 10, Font.ITALIC);
var bodyFont = FontFactory.GetFont("Arial", 12, Font.NORMAL);
// Add the title
document.Add(new Paragraph("UNO Student Plan of Study Details", titleFont));
var logo = iTextSharp.text.Image.GetInstance(Server.MapPath("~/images/uno.jpg"));
logo.ScalePercent(24f);
logo.SetAbsolutePosition(460, 780);
document.Add(logo);
// Now add the "" message
document.Add(new Paragraph(" ", bodyFont));
document.Add(Chunk.NEWLINE);
// Add the "Student Information" subtitle
document.Add(new Paragraph("Student Information", subTitleFont));
// Create the Student Information table
var studentInfoTable = new PdfPTable(2);
studentInfoTable.HorizontalAlignment = 0;
studentInfoTable.SpacingBefore = 10;
studentInfoTable.SpacingAfter = 10;
studentInfoTable.DefaultCell.Border = 0;
studentInfoTable.SetWidths(new int[] { 1, 3 });
studentInfoTable.AddCell(new Phrase("Nu Id:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[0].ToString());
studentInfoTable.AddCell(new Phrase("First Name:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[1].ToString());
studentInfoTable.AddCell(new Phrase("Last Name:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[2].ToString());
studentInfoTable.AddCell(new Phrase("Email Id:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[3].ToString());
studentInfoTable.AddCell(new Phrase("Phone:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[4].ToString());
studentInfoTable.AddCell(new Phrase("Department:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[5].ToString());
studentInfoTable.AddCell(new Phrase("Option:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[6].ToString());
studentInfoTable.AddCell(new Phrase("Concentration:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[7].ToString());
studentInfoTable.AddCell(new Phrase("Transition:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[8].ToString());
studentInfoTable.AddCell(new Phrase("Credits:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[9].ToString());
studentInfoTable.AddCell(new Phrase("Semester:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[10].ToString());
studentInfoTable.AddCell(new Phrase("Joining Year:", boldTableFont));
studentInfoTable.AddCell(dt.Rows[0].ItemArray[11].ToString());
document.Add(studentInfoTable);
document.Add(Chunk.NEWLINE);
document.Add(Chunk.NEWLINE);
// Add the "Student Information" subtitle
document.Add(new Paragraph("Student Plan of Study Details", subTitleFont));
document.Add(new Paragraph(" ", bodyFont));
//---- Add Result of DataTable to PDF file With Header -----
PdfPTable pdfTable = new PdfPTable(dt1.Columns.Count);
pdfTable.DefaultCell.Padding = 2;
pdfTable.WidthPercentage = 100; // percentage
pdfTable.DefaultCell.BorderWidth = 2;
pdfTable.DefaultCell.HorizontalAlignment = Element.ALIGN_CENTER;
foreach (DataColumn column in dt1.Columns)
{
pdfTable.AddCell(FormatHeaderPhrase(column.ColumnName));
}
pdfTable.HeaderRows = 1; // this is the end of the table header
pdfTable.DefaultCell.BorderWidth = 1;
foreach (DataRow row in dt1.Rows)
{
foreach (object cell in row.ItemArray)
{
//assume toString produces valid output
pdfTable.AddCell(FormatPhrase(cell.ToString()));
}
}
document.Add(pdfTable);
document.Add(Chunk.NEWLINE);
// Create the Student Information table
var signatureTable = new PdfPTable(2);
signatureTable.HorizontalAlignment = 0;
signatureTable.SpacingBefore = 10;
signatureTable.SpacingAfter = 10;
signatureTable.DefaultCell.Border = 0;
//signatureTable.SetWidths(new int[] { 1, 1 });
signatureTable.WidthPercentage = 100;
signatureTable.AddCell("__________________________");
//signatureTable.AddCell(" __________________________");
signatureTable.AddCell("\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t __________________________");
signatureTable.AddCell(new Phrase("\t\t\t\t\t\t\t\t Advisor Signature", boldTableFont));
//signatureTable.AddCell(new Phrase (" Student Signature", boldTableFont));
signatureTable.AddCell(new Phrase("\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t Student Signature", boldTableFont));
document.Add(signatureTable);
//document.Footer;
document.Close();
Response.ContentType = "application/pdf";
//Response.AddHeader("Content-Disposition", string.Format("inline;filename=" + sNuId + "-{0}.pdf", "POS Details"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + sNuId + " - POS Details.pdf");
Response.BinaryWrite(output.ToArray());
Response.End();
}
public bool UpdatePertinencia(Terminos termino, int ius, bool esPertinente)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
OleDbCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
bool insertCompleted = false;
try
{
connection.Open();
DataSet dataSet = new DataSet();
DataRow dr;
const string sqlQuery = "SELECT * FROM RelTesis WHERE IdTermino = @IdTermino AND Ius = @Ius";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlQuery, connection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@IdTermino", termino.IdTermino);
dataAdapter.SelectCommand.Parameters.AddWithValue("@Ius", ius);
dataAdapter.Fill(dataSet, "RelTesis");
dr = dataSet.Tables["RelTesis"].Rows[0];
dr.BeginEdit();
dr["Pertinencia"] = (esPertinente) ? 1 : 0;
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = "UPDATE RelTesis SET Pertinencia = @Pertinencia WHERE IdTermino = @IdTermino AND Ius = @Ius";
dataAdapter.UpdateCommand.Parameters.Add("@Pertinencia", OleDbType.Numeric, 0, "Pertinencia");
dataAdapter.UpdateCommand.Parameters.Add("@IdTermino", OleDbType.Numeric, 0, "IdTermino");
dataAdapter.UpdateCommand.Parameters.Add("@Ius", OleDbType.Numeric, 0, "Ius");
dataAdapter.Update(dataSet, "RelTesis");
dataSet.Dispose();
dataAdapter.Dispose();
insertCompleted = true;
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TerminosModel", "BusquedaLatinos");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TerminosModel", "BusquedaLatinos");
}
finally
{
connection.Close();
}
return(insertCompleted);
}
public void SetNewTerminoGenericos(Genericos generico)
{
OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Diccionario"].ToString());
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
generico.IdGenerico = DataBaseUtilities.GetNextIdForUse("Genericos", "IdConcepto", connection);
string sqlCadena = "SELECT * FROM Genericos WHERE IdConcepto = 0";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Genericos");
dr = dataSet.Tables["Genericos"].NewRow();
dr["IdConcepto"] = generico.IdGenerico;
dr["Concepto"] = StringUtilities.UppercaseFirst(generico.Termino);
dr["ConceptoStr"] = ScjnUtilities.StringUtilities.PrepareToAlphabeticalOrder(generico.Termino);
dr["FechaAlta"] = DateTime.Now;
dr["FechaAltaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dataSet.Tables["Genericos"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Genericos VALUES (@IdConcepto,@Concepto,@ConceptoStr,@FechaAlta,@FechaAltaInt)";
dataAdapter.InsertCommand.Parameters.Add("@IdConcepto", OleDbType.Numeric, 0, "IdConcepto");
dataAdapter.InsertCommand.Parameters.Add("@Concepto", OleDbType.VarChar, 0, "Concepto");
dataAdapter.InsertCommand.Parameters.Add("@ConceptoStr", OleDbType.VarChar, 0, "ConceptoStr");
dataAdapter.InsertCommand.Parameters.Add("@FechaAlta", OleDbType.Date, 0, "FechaAlta");
dataAdapter.InsertCommand.Parameters.Add("@FechaAltaInt", OleDbType.Numeric, 0, "FechaAltaInt");
dataAdapter.Update(dataSet, "Genericos");
dataSet.Dispose();
dataAdapter.Dispose();
if (generico.Definiciones != null)
{
foreach (Definiciones definicion in generico.Definiciones)
{
definicion.IdGenerico = generico.IdGenerico;
new DefinicionModel().SetDefiniciones(definicion);
}
}
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetNewTerminoGenericos", "Diccionario");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetNewTerminoGenericos", "Diccionario");
}
finally
{
connection.Close();
}
}
/// <summary>
/// 将指定Excel文件的内容读取到DataTable中
/// </summary>
public static DataTable ReadXlsxFile(string filePath, out string errorString)
{
// 检查文件是否存在且没被打开
FILE_STATE fileState = Utils.GetFileState(filePath);
if (fileState == FILE_STATE.INEXIST)
{
errorString = string.Format("{0}文件不存在", filePath);
return(null);
}
else if (fileState == FILE_STATE.IS_OPEN)
{
errorString = string.Format("{0}文件正在被其他软件打开，请关闭后重新运行本程序", filePath);
return(null);
}
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataSet ds = null;
try
{
// 初始化连接并打开
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
conn = new OleDbConnection(connectionString);
conn.Open();
// 获取数据源的表定义元数据
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 找到Sheet名为data的数据表
bool isFound = false;
for (int i = 0; i < dtSheet.Rows.Count; ++i)
{
string sheetName = dtSheet.Rows[i]["TABLE_NAME"].ToString();
if (sheetName == AppValues.EXCEL_SHEET_NAME)
{
isFound = true;
break;
}
}
if (!isFound)
{
errorString = string.Format("错误：{0}中不含有Sheet名为{1}的数据表", filePath, AppValues.EXCEL_SHEET_NAME.Replace("$", ""));
return(null);
}
// 初始化适配器
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(String.Format("Select * FROM [{0}]", AppValues.EXCEL_SHEET_NAME), conn);
ds = new DataSet();
da.Fill(ds, AppValues.EXCEL_SHEET_NAME);
}
catch
{
errorString = "错误：连接Excel失败，你可能尚未安装Office数据连接组件: http://www.microsoft.com/en-US/download/details.aspx?id=23734 \n";
return(null);
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
// 由于C#运行机制，即便因为表格中没有Sheet名为data的工作簿而return null，也会继续执行finally，而此时da为空，故需要进行判断处理
if (da != null)
{
da.Dispose();
}
conn.Dispose();
}
}
errorString = null;
return(ds.Tables[0]);
}
/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static DataSet LoadExcel(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType))
{
return(null);
}
if (fileType == ".xls")
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
}
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
System.Data.DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
conn = new OleDbConnection(connStr);
conn.Open();
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
//for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[0]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
//continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return(ds);
}
public void ImportExcelData_Read(string fileName, DataGridView dgv)
{
// 엑셀 문서 내용 추출
string connectionString = string.Empty;
if (File.Exists(fileName)) // 파일 확장자 검사
{
if (Path.GetExtension(fileName).ToLower() == ".xls")
{ // Microsoft.Jet.OLEDB.4.0 은 32 bit 에서만 동작되므로 빌드할 때 64비트로 하면 에러가 발생함.
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0;", fileName);
}
else if (Path.GetExtension(fileName).ToLower() == ".xlsx")
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties=Excel 12.0;", fileName);
}
}
DataSet data = new DataSet();
string strQuery = "SELECT * FROM [Sheet1$]"; // 엑셀 시트명 Sheet1의 모든 데이터를 가져오기
OleDbConnection oleConn = new OleDbConnection(connectionString);
oleConn.Open();
OleDbCommand oleCmd = new OleDbCommand(strQuery, oleConn);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleCmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
data.Tables.Add(dataTable);
dgv.DataSource = data.Tables[0].DefaultView;
// 데이터에 맞게 칼럼 사이즈 조정하기
for (int i = 0; i < dgv.Columns.Count; i++)
{
dgv.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells);
}
dgv.AllowUserToAddRows = false; // 빈레코드 표시 안하기
dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue;
//dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; // 화면크기에 맞춰 채우기
dataTable.Dispose();
dataAdapter.Dispose();
oleCmd.Dispose();
oleConn.Close();
oleConn.Dispose();
if (dgv.Columns.Count > 9)
{
dgv.Columns.Remove("column0");
}
if (dgv.Columns.Count == 9)
{
dgv.Columns.Add("column0", "비고");
}
UpdataData();
}
private void frmMain_Load(object sender, EventArgs e)
{
try
{
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(CommonClass.BasePath);
foreach (var file in dir.GetFiles())
{
System.IO.File.Delete(file.FullName);
}
}
catch { }
DataSet HdrXML = new DataSet();
OleDbCommand OledbCmd = new OleDbCommand();
OleDbDataAdapter OledbAdp = new OleDbDataAdapter();
try
{
OledbCmd.Connection = CommonClass.fnGetConnection();
OledbCmd.CommandTimeout = 0;
OledbCmd.CommandType = CommandType.StoredProcedure;
OledbCmd.CommandText = "PrcBillRpt";
OledbCmd.Parameters.Add("@Action", "DSHBRD");
OledbAdp.SelectCommand = OledbCmd;
OledbAdp.Fill(HdrXML);
chart1.DataSource = HdrXML.Tables[0];
chart1.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Pie;
chart1.Series[0].XValueMember = "PaymentTyp";
chart1.Series[0].YValueMembers = "Amount";
chart1.Series[0].IsValueShownAsLabel = true;
chart1.Titles.Add("Todays Collection");
chart2.DataSource = HdrXML.Tables[1];
chart2.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Column;
chart2.Series[0].XValueMember = "Category";
chart2.Series[0].YValueMembers = "Amount";
chart2.Series[0].IsValueShownAsLabel = true;
chart2.Series[0].IsVisibleInLegend = false;
chart2.Titles.Add("Todays Sales");
chart3.DataSource = HdrXML.Tables[2];
chart3.Titles.Add("Last 7 Days Sales by Category");
chart3.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
chart3.Series[0].XValueMember = "RptDate";
chart3.Series[0].YValueMembers = "Cards";
chart3.Series[0].IsValueShownAsLabel = true;
chart3.Series[0].LegendText = "Cards";
chart3.Series[0].BorderWidth = 2;
chart3.Series.Add("Print");
chart3.Series[1].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
chart3.Series[1].XValueMember = "RptDate";
chart3.Series[1].YValueMembers = "Printing";
chart3.Series[1].IsValueShownAsLabel = true;
chart3.Series[1].BorderWidth = 2;
chart3.Series.Add("Gift");
chart3.Series[2].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
chart3.Series[2].XValueMember = "RptDate";
chart3.Series[2].YValueMembers = "Gifts";
chart3.Series[2].IsValueShownAsLabel = true;
chart3.Series[2].BorderWidth = 2;
chart3.Series.Add("Other");
chart3.Series[3].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
chart3.Series[3].XValueMember = "RptDate";
chart3.Series[3].YValueMembers = "Others";
chart3.Series[3].IsValueShownAsLabel = true;
chart3.Series[3].BorderWidth = 2;
}
catch
{ }
finally
{
HdrXML.Dispose(); HdrXML = null;
OledbCmd.Dispose(); OledbCmd = null;
OledbAdp.Dispose(); OledbAdp = null;
}
}
private void fnGetData()
{
DataSet HdrXML = new DataSet();
OleDbCommand OledbCmd = new OleDbCommand();
OleDbDataAdapter OledbAdp = new OleDbDataAdapter();
try
{
OledbCmd.Connection = CommonClass.fnGetConnection();
OledbCmd.CommandTimeout = 0;
OledbCmd.CommandType = CommandType.StoredProcedure;
OledbCmd.CommandText = "PrcBillSystem";
OledbCmd.Parameters.Add("@Action", "S");
OledbCmd.Parameters.Add("@HdrXML", DBNull.Value);
OledbCmd.Parameters.Add("@GrdXML", DBNull.Value);
OledbCmd.Parameters.Add("@DocPk", _RefKey);
OledbAdp.SelectCommand = OledbCmd;
OledbAdp.Fill(HdrXML);
int docRef = _RefKey;
fnClear();
_RefKey = docRef;
txt_BillNo.Text = HdrXML.Tables[0].Rows[0]["InvBilNo"].ToString();
txt_EmailAddress.Text = HdrXML.Tables[0].Rows[0]["InvEmailId"].ToString();
txt_MobilNo.Text = HdrXML.Tables[0].Rows[0]["InvMobNo"].ToString();
txt_Name.Text = HdrXML.Tables[0].Rows[0]["InvCusNm"].ToString();
txt_OrderNo.Text = HdrXML.Tables[0].Rows[0]["InvOrdNo"].ToString();
txt_Remarks.Text = HdrXML.Tables[0].Rows[0]["InvRmks"].ToString();
txt_Address.Text = HdrXML.Tables[0].Rows[0]["Add"].ToString();
lbl_Gst.Text = HdrXML.Tables[0].Rows[0]["InvVAT"].ToString();
txt_Discount.Text = HdrXML.Tables[0].Rows[0]["Dis"].ToString();
lbl_RoundOff.Text = HdrXML.Tables[0].Rows[0]["Rnd"].ToString();
lbl_Taxable.Text = HdrXML.Tables[0].Rows[0]["InvGross"].ToString();
lbl_Total.Text = HdrXML.Tables[0].Rows[0]["InvNett"].ToString();
dat_ProofDate.Text = HdrXML.Tables[0].Rows[0]["InvProfDt"].ToString();
dat_BillDate.Text = HdrXML.Tables[0].Rows[0]["InvBilDt"].ToString();
dat_DeliveryDate.Text = HdrXML.Tables[0].Rows[0]["InvDelDt"].ToString();
dat_DeliveredDate.Text = HdrXML.Tables[0].Rows[0]["DelyDt"].ToString();
isOrderClosed = Convert.ToBoolean(HdrXML.Tables[0].Rows[0]["DelySt"]);
if (isOrderClosed == true)
{
Chk_Delivered.Checked = true;
}
else
{
Chk_Delivered.Checked = false;
}
lbl_Balance.Text = string.Format("Balance Amount : {0}", HdrXML.Tables[0].Rows[0]["Bal"].ToString());
lbl_Balance.Visible = true;
paymnt = new PaymentDetails();
paymnt.AmountPaid = Convert.ToDecimal(HdrXML.Tables[0].Rows[0]["InvAmntRcvd"].ToString());
//txt_Bal.Text = HdrXML.Tables[0].Rows[0]["Bal"].ToString();
foreach (DataRow dRow in HdrXML.Tables[1].Rows)
{
OrderDtls_DS.Tables[0].ImportRow(dRow);
}
dataGridView1.DataSource = OrderDtls_DS.Tables[0].DefaultView;
dataGridView1.Refresh();
}
catch { }
finally
{
HdrXML.Dispose(); HdrXML = null; OledbCmd.Dispose(); OledbCmd = null;
OledbAdp.Dispose(); OledbAdp = null;
}
}
//private void importExcel(string dir, string file)
//{
// IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
// string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
// string SQLstr = "SELECT * FROM [Sheet1$]";
// OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
// ExcelCon.Open();
// OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
// OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
// DataTable dTable = new DataTable();
// try
// {
// dataAdapter.Fill(dTable);
// int idx = 1;
// DateTime NgayChet = DateTime.MinValue;
// DateTime currNgay = DateTime.MinValue;
// string StrIDCaSau = "";
// string StrDaBung = "";
// string StrDaPhanLoai = "";
// string StrDau = "";
// string LyDo = "";
// foreach (DataRow r in dTable.Rows)
// {
// NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 22:00:00", culture);
// if (NgayChet != currNgay)
// {
// if (StrIDCaSau != "")
// {
// string fail = csCont.CaSauChet(StrIDCaSau, currNgay, UserId, LyDo, StrDaBung, StrDaPhanLoai, StrDau);
// if (fail != "")
// {
// int sidx = idx - 1;
// lblMessage.Text += "<br/>Dòng " + sidx.ToString() + ":" + fail;
// }
// StrIDCaSau = "";
// StrDaBung = "";
// StrDaPhanLoai = "";
// StrDau = "";
// }
// currNgay = NgayChet;
// }
// string s = csCont.CaSau_Chet_GetCaByLoaiCaByChuongAtDate(Convert.ToInt32(r["LoaiCa"]), r["Chuong"].ToString(), Convert.ToInt32(r["SL"]), NgayChet);
// string sTemp = s.Replace("@", "");
// if ((s.Length - sTemp.Length) / 2 != Convert.ToInt32(r["SL"]))
// {
// lblMessage.Text += "<br/>Dòng " + idx.ToString() + " không import do số lượng cá không đủ.";
// idx++;
// continue;
// }
// StrIDCaSau += s;
// if (r["SanPham"] != DBNull.Value && r["SanPham"].ToString() != "")
// {
// if(r["SanPham"].ToString() == "-1")
// {
// for(int i = 0; i<Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@0@";
// StrDaPhanLoai += "@4@";
// StrDau += "@1@";
// }
// }
// else
// {
// for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@" + r["SanPham"].ToString() + "@";
// StrDaPhanLoai += "@4@";
// StrDau += "@0@";
// }
// }
// }
// else
// {
// for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
// {
// StrDaBung += "@0@";
// StrDaPhanLoai += "@4@";
// StrDau += "@0@";
// }
// }
// idx++;
// }
// if (StrIDCaSau != "")
// {
// string fail = csCont.CaSauChet(StrIDCaSau, currNgay, UserId, LyDo, StrDaBung, StrDaPhanLoai, StrDau);
// if (fail != "")
// {
// int sidx = idx - 1;
// lblMessage.Text += "<br/>Dòng " + sidx.ToString() + ":" + fail;
// }
// }
// // dispose used objects
// dTable.Dispose();
// dataAdapter.Dispose();
// dbCommand.Dispose();
// ExcelCon.Close();
// ExcelCon.Dispose();
// lblMessage.Text += "<br/>Đã import xong!";
// }
// catch (Exception ex)
// {
// // dispose used objects
// dTable.Dispose();
// dataAdapter.Dispose();
// dbCommand.Dispose();
// ExcelCon.Close();
// ExcelCon.Dispose();
// Response.Write(ex.ToString());
// }
//}
private void importExcel(string dir, string file)
{
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string SQLstr = "SELECT * FROM [Sheet1$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 1;
DateTime NgayChet = DateTime.MinValue;
string StrIDCaSau = "";
string StrDaBung = "";
string StrDaPhanLoai = "";
string StrDau = "";
string StrPPM = "";
string StrLDC = "";
string StrKL = "";
string LyDo = "";
int hh = 11;
int mm = 10;
int ss = 10;
string TenChuong = "";
int So = 0;
string BienBan = "";
foreach (DataRow r in dTable.Rows)
{
//NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 22:00:00", culture);
NgayChet = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " " + hh.ToString() + ":" + mm.ToString() + ":" + ss.ToString(), culture);
ss++; if (ss > 59)
{
ss = 10; mm++; if (mm > 59)
{
mm = 10; hh++;
}
}
csCont.ParseChuong(r["Chuong"].ToString().Trim(), out TenChuong, out So);
string s = csCont.CaSau_Chet_GetCaByLoaiCaByChuongAtDate(Convert.ToInt32(r["LoaiCa"]), TenChuong, So, Convert.ToInt32(r["SL"]), NgayChet, Convert.ToBoolean(r["Giong"]));
string sTemp = s.Replace("@", "");
if ((s.Length - sTemp.Length) / 2 != Convert.ToInt32(r["SL"]))
{
lblMessage.Text += "<br/>Dòng " + idx.ToString() + " không import do số lượng cá không đủ.";
idx++;
continue;
}
if (r["BienBan"] != DBNull.Value)
{
BienBan = r["BienBan"].ToString();
}
else
{
BienBan = "";
}
StrIDCaSau = s;
if (r["SanPham"] != DBNull.Value && r["SanPham"].ToString() != "")
{
if (r["SanPham"].ToString() == "-1")
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@0@";
StrDaPhanLoai += "@4@";
StrDau += "@1@";
StrPPM += "@CL@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
else
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@" + r["SanPham"].ToString() + "@";
StrDaPhanLoai += "@" + r["PhanLoai"].ToString() + "@";
StrDau += "@0@";
StrPPM += "@" + r["PPM"].ToString() + "@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
}
else
{
for (int i = 0; i < Convert.ToInt32(r["SL"]); i++)
{
StrDaBung += "@0@";
StrDaPhanLoai += "@4@";
StrDau += "@0@";
StrPPM += "@CL@";
StrLDC += "@" + r["LyDoChet"].ToString() + "@";
StrKL += "@" + r["KhoiLuong"].ToString().Replace(",", ".") + "@";
}
}
if (StrIDCaSau != "")
{
string fail = csCont.CaSauChet(StrIDCaSau, NgayChet, UserId, StrDaBung, StrDaPhanLoai, StrDau, StrPPM, StrLDC, StrKL, BienBan, "-1", "");
if (fail != "")
{
lblMessage.Text += "<br/>Dòng " + idx.ToString() + ":" + fail;
}
StrIDCaSau = "";
StrDaBung = "";
StrDaPhanLoai = "";
StrDau = "";
StrPPM = "";
StrLDC = "";
StrKL = "";
}
else
{
StrDaBung = "";
StrDaPhanLoai = "";
StrDau = "";
StrPPM = "";
StrLDC = "";
StrKL = "";
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
private void Initialize(string fileDir) //初始化
{
try
{
var connstr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileDir + "; " + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
Conn = new OleDbConnection(connstr);
Conn.Open();
DataTable dtTemp;
string datatype;
datatype = finddatatype(Path.GetFileName(fileDir));
if (datatype == null)
{
throw new Exception("无法确认表格检测种类！");
}
dtTemp = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtTemp == null)
{
return;
}
String[] excelSheets = new string[dtTemp.Rows.Count];
int i = 0;
foreach (DataRow row in dtTemp.Rows)
{
if (!row["TABLE_NAME"].ToString().Contains("FilterDatabase"))
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
}
dtTemp.Dispose();
// Loop through all of the sheets
for (int j = 0; j < i; j++)
{
try
{
Command = new OleDbCommand("Select * from [" + excelSheets[j] + "]", Conn);
Adapter = new OleDbDataAdapter();
Table = new DataTable();
Adapter.SelectCommand = Command;
Adapter.Fill(Table);
string columnTimeName = null;
string columnConcName = null;
DataTable dtColomn = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[]
{ null, null, excelSheets[j], null });
List <string> listColumn = new List <string>();
foreach (DataRow row in dtColomn.Rows)
{
listColumn.Add(row["Column_name"].ToString());
}
dtColomn.Dispose();
bool timefound = false, concfound = false;
foreach (string column in listColumn)
{
var result = findColumnbelong(column);
switch (result)
{
case "Time_Found":
columnTimeName = column;
timefound = true;
break;
case "Conc_Found":
columnConcName = column;
concfound = true;
break;
default:
break;
}
}
if (timefound == false || concfound == false)
{
throw new Exception("查找时间或者浓度失败！");
}
if (Table.Rows.Count < 2)
{
throw new Exception("没有足够数据以查找时间间隔！");
}
TimeSpan time_interval = new TimeSpan(0, 0, 0);
long intervalcount_thatmakesupaday;
long intervalcount_thatmakesup8hrs = 1;
try
{
DateTime Value1, Value2;
if (DateTime.TryParse(Table.Rows[0][columnTimeName].ToString(), out Value1) && DateTime.TryParse(Table.Rows[1][columnTimeName].ToString(), out Value2))
{
time_interval = Value2 - Value1;
}
if (time_interval > TimeSpan.FromDays(1) || time_interval == TimeSpan.FromDays(0))
{
throw new Exception("时间间隔有误！");
}
if (TimeSpan.FromDays(1).Ticks % time_interval.Ticks != 0)
{
throw new Exception("时间间隔不能以24小时进行区分！");
}
if (datatype == "O3")
{
if (TimeSpan.FromHours(8).Ticks % time_interval.Ticks != 0)
{
throw new Exception("时间间隔不能以8小时进行区分！");
}
intervalcount_thatmakesup8hrs = TimeSpan.FromHours(8).Ticks / time_interval.Ticks;
}
intervalcount_thatmakesupaday = TimeSpan.FromDays(1).Ticks / time_interval.Ticks;
}
catch (Exception)
{
throw;
}
if (Table.Rows.Count < intervalcount_thatmakesupaday)
{
throw new Exception("数据不足以求出日均！");
}
int rowCount = 1;
double day_sum = 0;
Datapoint prev = new Datapoint()
{
Concentration = 0,
Continued_time = new TimeSpan(0),
IsHighPollution = false,
IsMonitorError = false,
Time = new DateTime(0),
Monitor_Error = "",
Pollution_Level = ""
};
Datapoint current = new Datapoint()
{
Concentration = 0,
Continued_time = new TimeSpan(0),
IsHighPollution = false,
IsMonitorError = false,
Time = new DateTime(0),
Monitor_Error = "",
Pollution_Level = ""
};
List <double> day_elements = new List <double>();
foreach (DataRow row in Table.Rows)
{
string problems_found = "";
string pollution_level = "";
current.Concentration = (double)row[columnConcName];
DateTime result = new DateTime();
if (row[columnTimeName].GetType() == typeof(DateTime))
{
result = (DateTime)row[columnTimeName];
}
else
{
if (!DateTime.TryParse((string)row[columnTimeName], out result))
{
throw new Exception(string.Format("无法分析第{0}行的时间数据", rowCount));
}
}
current.Time = result;
if (rowCount < intervalcount_thatmakesupaday)
{
day_sum += (double)row[columnConcName];
if (datatype == "O3")
{
day_elements.Add((double)row[columnConcName]);
}
}
else if (rowCount > intervalcount_thatmakesupaday)
{
day_sum -= (double)Table.Rows[rowCount - 1 - (int)intervalcount_thatmakesupaday][columnConcName];
day_sum += (double)row[columnConcName];
if (datatype == "O3")
{
day_elements.RemoveAt(0);
day_elements.Add((double)row[columnConcName]);
pollution_level = O3avg_results(day_elements.OrderByDescending(item => item).Take((int)intervalcount_thatmakesup8hrs).Sum() / intervalcount_thatmakesup8hrs, (double)row[columnConcName]);
}
else
{
pollution_level = calculateAvgandPollutionlvl((double)row[columnConcName], day_sum, intervalcount_thatmakesupaday, datatype);
}
}
else
{
if (datatype == "O3")
{
pollution_level = O3avg_results(day_elements.OrderByDescending(item => item).Take((int)intervalcount_thatmakesup8hrs).Sum() / intervalcount_thatmakesup8hrs, (double)row[columnConcName]);
}
else
{
pollution_level = calculateAvgandPollutionlvl((double)row[columnConcName], day_sum, intervalcount_thatmakesupaday, datatype);
}
}
var monitorinfo = examineMonitorErrors((double)row[columnConcName], datatype);
if (monitorinfo != null)
{
problems_found += monitorinfo;
}
if (problems_found == "")
{
current.IsMonitorError = false;
current.Monitor_Error = "";
}
else
{
current.IsMonitorError = true;
current.Monitor_Error = problems_found;
}
current.Pollution_Level = pollution_level;
if (pollution_level == "Intermidiate Pollution" || pollution_level == "Heavy Pollution" || pollution_level == "Severe Pollution")
{
current.IsHighPollution = true;
}
else
{
current.IsHighPollution = false;
current.Pollution_Level = "";
}
//Console.WriteLine(string.Format("{0},{1},{2},{3},{4}", prev.Concentration, prev.Time, prev.Continued_time, prev.Pollution_Level, prev.Monitor_Error));
current.Field = datatype;
if (current.Equals(prev))
{
current.Mergewith(prev, time_interval);
}
else if (!current.Equals(prev) && prev.isProblematic())
{
ReportSQL(prev);
//Console.WriteLine(string.Format("{0},{1},{2},{3},{4}", prev.Concentration, prev.Time, prev.Continued_time, prev.Pollution_Level, prev.Monitor_Error));
}
else
{
}
prev.Copy(current);
current.Reset();
rowCount++;
}
//Disposeof();
}
catch (Exception ex)
{
MessageBox.Show(String.Format("在表格{0}内的{1}表单，{2}", fileDir, excelSheets[j], ex.Message), "分析文件错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Command.Dispose();
Adapter.Dispose();
Table.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(String.Format("在表格{0}内，{1}", fileDir, ex.Message), "分析文件错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
Conn.Close();
Conn.Dispose();
}
/*当选择框中选择的条目发生改变时*/
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
/*清空图标中的图像*/
chart1.Series[0].Points.Clear();
/*获取当前的月份*/
DateTime dateNow = DateTime.Now;
int monNow = dateNow.Month;
/*利用数组来存储总销售金额*/
double[] sum = new double[12];
string choosetype = comboBox1.SelectedIndex.ToString();
switch (choosetype)
{
case "0":
choosetype = "All";
break;
case "1":
choosetype = "G 文化、科学、教育、体育";
break;
case "2":
choosetype = "H 语言、文字";
break;
case "3":
choosetype = "O 数理科学和化学";
break;
case "4":
choosetype = "T 工业技术";
break;
case "5":
choosetype = "V 航空、航天";
break;
default:
choosetype = "-1";
break;
}
string mySql = "select bookcode as 条形码,bookname as 书名 ,bookauthor as 作者,bookpublisher as 出版社,booktype as 分类 ,bookprice as 定价 ,saleprice as 售价 ,saledate as 日期 from Salelist where booktype ='" + choosetype + "'";
/*判断是否选择某一项*/
if (choosetype == "-1")
{
MessageBox.Show("未选择分类");
}
else
{
/*当选择所有图书时*/
if (choosetype == "All")
{
mySql = "select bookcode as 条形码,bookname as 书名 ,bookauthor as 作者,bookpublisher as 出版社,booktype as 分类 ,bookprice as 定价 ,saleprice as 售价 ,saledate as 日期 from Salelist ";
}
string strPath = "stu.mdb";
OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";");
OleDbCommand myCommand = new OleDbCommand(mySql, myCon);
OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);
DataTable myTable = new DataTable();
myCon.Open();
myAdapter.Fill(myTable);
/*将数据表绑定到dataGridView视图中*/
this.dataGridView1.DataSource = myTable;
for (int i = 0; i < myTable.Rows.Count; i++)
{
/*逐个查询月份，将相同的月份的金额求和，存入数组中，数组的下标即月份*/
if (Convert.ToDateTime(myTable.Rows[i]["日期"]).Month == monNow)
{
sum[monNow] += Convert.ToDouble(myTable.Rows[i]["售价"]);
}
else if (Convert.ToDateTime(myTable.Rows[i]["日期"]).Month == monNow - 1)
{
sum[monNow - 1] += Convert.ToDouble(myTable.Rows[i]["售价"]);
}
else if (Convert.ToDateTime(myTable.Rows[i]["日期"]).Month == monNow - 2)
{
sum[monNow - 2] += Convert.ToDouble(myTable.Rows[i]["售价"]);
}
}
/*向图表中添加信息*/
chart1.Series[0].Points.AddXY(monNow - 2, sum[monNow - 2]);
chart1.Series[0].Points.AddXY(monNow - 1, sum[monNow - 1]);
chart1.Series[0].Points.AddXY(monNow, sum[monNow]);
myCommand.Dispose();
myAdapter.Dispose();
myCon.Dispose();
myCon.Close();
}
}
/// <summary>
/// 通过文件，sql ,取出Table.
/// </summary>
/// <param name="filePath"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetTableByExt(string filePath, string sql)
{
DataTable Tb = new DataTable("Tb");
Tb.Rows.Clear();
string typ = System.IO.Path.GetExtension(filePath).ToLower();
string strConn;
switch (typ.ToLower())
{
case ".xls":
if (sql == null)
{
sql = "SELECT * FROM [" + GenerFirstTableName(filePath) + "]";
}
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + filePath + ";Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter ada = new OleDbDataAdapter(sql, conn);
try
{
conn.Open();
ada.Fill(Tb);
Tb.TableName = Path.GetFileNameWithoutExtension(filePath);
}
catch (System.Exception ex)
{
conn.Close();
throw ex; //(ex.Message);
}
conn.Close();
break;
case ".xlsx":
if (sql == null)
{
sql = "SELECT * FROM [" + GenerFirstTableName(filePath) + "]";
}
try
{
strConn = "Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
System.Data.OleDb.OleDbConnection conn121 = new OleDbConnection(strConn);
OleDbDataAdapter ada91 = new OleDbDataAdapter(sql, conn121);
conn121.Open();
ada91.Fill(Tb);
Tb.TableName = Path.GetFileNameWithoutExtension(filePath);
conn121.Close();
ada91.Dispose();
}
catch (System.Exception ex1)
{
try
{
strConn = "Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
System.Data.OleDb.OleDbConnection conn1215 = new OleDbConnection(strConn);
OleDbDataAdapter ada919 = new OleDbDataAdapter(sql, conn1215);
ada919.Fill(Tb);
Tb.TableName = Path.GetFileNameWithoutExtension(filePath);
ada919.Dispose();
conn1215.Close();
}
catch
{
}
throw ex1; //(ex.Message);
}
break;
case ".dbf":
strConn = "Driver={Microsoft dBASE Driver (*.DBF)};DBQ=" + System.IO.Path.GetDirectoryName(filePath) + "\\"; //+FilePath;//
OdbcConnection conn1 = new OdbcConnection(strConn);
OdbcDataAdapter ada1 = new OdbcDataAdapter(sql, conn1);
conn1.Open();
try
{
ada1.Fill(Tb);
}
catch //(System.Exception ex)
{
try
{
int sel = ada1.SelectCommand.CommandText.ToLower().IndexOf("select") + 6;
int from = ada1.SelectCommand.CommandText.ToLower().IndexOf("from");
ada1.SelectCommand.CommandText = ada1.SelectCommand.CommandText.Remove(sel, from - sel);
ada1.SelectCommand.CommandText = ada1.SelectCommand.CommandText.Insert(sel, " top 10 * ");
ada1.Fill(Tb);
Tb.TableName = "error";
}
catch (System.Exception ex)
{
conn1.Close();
throw new Exception("读取DBF数据失败！" + ex.Message + " SQL:" + sql);
}
}
conn1.Close();
break;
default:
break;
}
return(Tb);
}
//進行Web Service上傳至BBI主機
private void BBI_WS_upload()
{
OleDbDataAdapter ole_adp;
OleDbConnection ole_conn = new OleDbConnection(AUTO_SCHE.Properties.Settings.Default.jh815Connection);;
OleDbCommand ole_cmd;
OleDbDataReader ole_dr = null;
int v_cnt = 0;
try
{
//判斷有無未上傳的資料
v_sql = "SELECT COUNT(*) FROM BBI_SHIP_QUEUE WHERE STATUS = 'N' ";
ole_cmd = new OleDbCommand(v_sql, ole_conn);
ole_cmd.Connection.Open();
ole_dr = ole_cmd.ExecuteReader();
ole_dr.Read();
v_cnt = Convert.ToInt16(ole_dr[0].ToString());
ole_cmd.Connection.Close();
ole_dr.Dispose();
if (v_cnt > 0)
{
DataTable PDA_DataTable = new DataTable("PDA_Data");
DataSet PDA_DataSet = new DataSet();
//PDAWebService.PDAServiceForVendorSoapClient ws1 = new PDAWebService.PDAServiceForVendorSoapClient();
PDAWebService.Result ExeResult = new PDAWebService.Result();
int row = 0;
string SQL = "SELECT trantype,prepno,sono,item,fromMaker,toMaker,palletNo,rstat,adduser,addtime,chguser,chgtime,barcode,seqno,tdate,lotno FROM BBI_SHIP_QUEUE_V " +
" UNION ALL SELECT trantype,prepno,sono,item,fromMaker,toMaker,palletNo,rstat,adduser,addtime,chguser,chgtime,barcode,seqno,tdate,lotno FROM BBI_SHIP_QUEUE_V2";
ole_cmd = new OleDbCommand(SQL, ole_conn);
ole_adp = new OleDbDataAdapter(ole_cmd);
ole_conn.Open();
ole_adp.Fill(PDA_DataTable);
PDA_DataSet.Tables.Add(PDA_DataTable);
//呼叫Web Service上傳資料
string ParamXML = String.Format("<Parameters SQLTableName=\"{0}\" MakerID=\"{1}\" UploadTime=\"{2}\"></Parameters>", "VendorTransDetl", "J250", DateTime.Now);
ExeResult = ws.UploadData(PDA_DataSet, ParamXML);
row = ExeResult.RowCount;
if (ExeResult.success)
{
//上傳成功的話要做的事...
//rTB_log2.Text += "資料上成功：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\n";
v_sql = "UPDATE BBI_SHIP_QUEUE SET STATUS = 'Y' ";
ts_conn.trans_oleDb(v_sql, "update");
}
else if ((ExeResult.success && row == 0) || !ExeResult.success)
{
//MessageBox.Show(String.Format("{0}", ExeResult.message), "Web Service Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
rTB_log2.Text += "資料上傳錯誤：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\n";
rTB_log2.ScrollToCaret();
}
PDA_DataTable.Clear();
PDA_DataSet.Tables.Remove(PDA_DataTable);
PDA_DataTable.Dispose();
PDA_DataSet.Dispose();
ole_conn.Close();
ole_adp.Dispose();
if ((ExeResult.success && row == 0 && ExeResult.message.Length > 0) || !ExeResult.success)
{
MessageBox.Show(String.Format("Web Service:{0}", ExeResult.message), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
rTB_log2.Text += "資料上傳錯誤：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "," + ExeResult.message + "\n";
rTB_log2.ScrollToCaret();
}
else
{
//MessageBox.Show("資料上傳作業已完成!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
rTB_log2.Text += "資料上傳作業已完成：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\n";
rTB_log2.ScrollToCaret();
}
}
}
catch (Exception err)
{
//MessageBox.Show(String.Format("{0}", err.Message), "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
rTB_log2.Text += "資料上傳錯誤：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "," + err.Message + "\n";
rTB_log2.ScrollToCaret();
}
}
public void ExcelToCSVConversion(string sourceFile, string worksheetName, string targetFile)
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile + @";Extended Properties=""Excel 12.0 Xml;HDR=YES""";
OleDbConnection connection = null;
StreamWriter writer = null;
OleDbCommand command = null;
OleDbDataAdapter dataAdapter = null;
try
{
if (Store.Default.outputDir == string.Empty)
{
return;
}
connection = new OleDbConnection(connectionString);
connection.Open();
command = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", connection)
{
CommandType = CommandType.TableDirect,
};
Output.ProcessingTable = new DataTable("ganttOutFile");
Output.ProcessingTable.Clear();
writer = new StreamWriter(targetFile);
dataAdapter = new OleDbDataAdapter(command);
dataAdapter.Fill(Output.ProcessingTable);
dataAdapter.Dispose();
Output.ProcessingTable.Rows.Remove(Output.ProcessingTable.Rows[0]);
Output.ProcessingTable.Rows.Remove(Output.ProcessingTable.Rows[1]);
Output.ProcessingTable.Rows.Remove(Output.ProcessingTable.Rows[2]);
Output.ProcessingTable.AcceptChanges();
foreach (DataRow row in Output.ProcessingTable.Rows)
{
if (string.IsNullOrEmpty(row.ItemArray.GetValue(0).ToString()))
{
row.Delete();
}
}
Output.ProcessingTable.AcceptChanges();
for (int row = 0; row < Output.ProcessingTable.Rows.Count; row++)
{
var tmpStr = string.Empty;
for (int column = 0; column < Output.ProcessingTable.Columns.Count; column++)
{
var strToCollect = Output.ProcessingTable.Rows[row][column];
var typeOfStr = strToCollect.GetType();
if (typeOfStr == typeof(DateTime))
{
strToCollect = Convert.ToDateTime(Output.ProcessingTable.Rows[row][column]).ToString("MM/dd/yyyy", CultureInfo.CurrentCulture);
}
tmpStr += strToCollect + ",";
}
writer.WriteLine(tmpStr);
}
Console.WriteLine();
Console.WriteLine("The XLS file " + sourceFile + " has been converted to CSV " +
"into " + targetFile + ".");
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
Console.ReadLine();
Console.WriteLine("File may be corrupted or already running.");
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
command.Dispose();
writer.Close();
writer.Dispose();
Output.ProcessingTable.AcceptChanges();
Output g = new Output();
var columns = g.FileColumns;
for (var c = 0; c <= Output.ProcessingTable.Columns.Count - 6; c++)
{
Output.ProcessingTable.Columns[c].ColumnName = columns[c].ToString();
}
}
Output.ProcessingTable.AcceptChanges();
}
public ActionResult ExternalConnection(string country, string button, string refresh)
{
if (button == null)
{
InitializeExternalConnection();
return(View());
}
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Create the workbook with default sheet
IWorkbook workbook = application.Workbooks.Create();
workbook.Version = ExcelVersion.Excel2016;
//Get the 1st sheet from the workbook
IWorksheet sheet = workbook.Worksheets[0];
connectionstring = "Data Source = " + ResolveApplicationDataPath("Northwind.mdb");
//connection string for DataSource
string Connectionstring = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;" + connectionstring;
//query for the datasource
string query;
if (country != null)
{
query = "select * from Customers where country='" + country + "'";
}
else
{
query = "select * from Customers";
}
//Add the connection to workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", Connectionstring, query, ExcelCommandType.Sql);
//Add the QueryTable to sheet object
sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["C3"]);
#endregion
#region Refresh the Connection
//Refresh the Connection for include the data
if (refresh != null)
{
try
{
sheet.ListObjects[0].Refresh();
sheet.UsedRange.AutofitColumns();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
#endregion
try
{
return(excelEngine.SaveAsActionResult(workbook, "ExternalConnection.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
adapter.Dispose();
connection.Close();
return(View());
}