protected void Export_Click(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["GGFConnectionString"].ConnectionString.ToString());
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql(), Conn);
DataSet ds = new DataSet();
myAdapter.Fill(ds, "ACP"); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//匯出Excel
GGFPortal.ReferenceCode.ConvertToExcel xx = new ReferenceCode.ConvertToExcel();
xx.ExcelWithNPOI(ds.Tables["ACP"], @"xlsx");
}
protected void Export_Click(object sender, EventArgs e)
{
//check資料
if (String.IsNullOrEmpty(PurSearchTB.Text) && String.IsNullOrEmpty(ACPTB.Text) && String.IsNullOrEmpty(StyleNoSeachTB.Text))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('Please enter Search Data');</script>");
}
else
{
SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["GGFConnectionString1"].ConnectionString.ToString());
//----(2). 執行SQL指令（Select陳述句）----
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql(), Conn);
DataSet ds = new DataSet();
myAdapter.Fill(ds, "ACP"); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
//匯出Excel
GGFPortal.ReferenceCode.ConvertToExcel xx = new ReferenceCode.ConvertToExcel();
xx.ExcelWithNPOI(ds.Tables["ACP"], @"xlsx");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string savePath = Server.MapPath(@"~\ExcelUpLoad\Salse\SIZE\");
string tempFolder = System.IO.Path.GetTempPath(); // Get folder
string FileName = FileUpload1.FileName.ToString(); // Get xml file name
string strpo = FileName.Substring(FileName.Length - 12, 8);
//"GREGIA_20180605203002208_02075624.XML"
savePath = savePath + FileName;
if (string.IsNullOrEmpty(FileUpload1.FileName))
{
MessageLB.Text = "請選擇檔案";
AlertPanel_ModalPopupExtender.Show();
}
else
{
try
{
MessageLB.Text = "";
FileUpload1.SaveAs(savePath);
DataSet ds = new DataSet();
//透過DataSet的ReadXml方法來讀取Xmlreader資料
ds.ReadXml(savePath);
//建立DataTable並將DataSet中的第0個Table資料給DataTable
DataTable dt = ds.Tables["ColorSize"];
//轉換數量類型
dt.Columns.Add("數量", typeof(int), "Convert(Quantity,'System.Int32')");
DataTable dt1 = ds.Tables["PrePack"];
DataTable dt2 = ds.Tables["Shipment"];
DataTable dt3 = ds.Tables["Item"];
DataTable dtColor = new DataTable(), dtSize = new DataTable();
dtColor = dt.DefaultView.ToTable(true, new string[] { "Color" });
dtSize = dt.DefaultView.ToTable(true, new string[] { "Size" });
DataTable TempTable = new DataTable();
TempTable.Columns.Add("訂單Style");
TempTable.Columns.Add("客戶PO");
TempTable.Columns.Add("目的地代號");
TempTable.Columns.Add("PO別");
TempTable.Columns.Add("Style");
TempTable.Columns.Add("FOB單價");
TempTable.Columns.Add("客戶交期");
TempTable.Columns.Add("顏色(英文)");
if (dtSize.Rows.Count > 0)
{
for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++)
{
TempTable.Columns.Add(dtSize.Rows[iSizeCount][0].ToString().ToUpper().Trim());
}
}
//----不考慮多單價，所有item都會相同
//for (int po資料筆數 = 0; po資料筆數 < dt3.Rows.Count; po資料筆數++)
//{
//}
for (int i顏色數量 = 0; i顏色數量 < dtColor.Rows.Count; i顏色數量++)
{
DataRow row;
row = TempTable.NewRow();
//----不考慮多單價，所有item都會相同
row["訂單Style"] = dt3.Rows[0]["ItemBuyerOrderNo"];
row["FOB單價"] = dt3.Rows[0]["ItemUnitPriceTotal"];
//----
row["客戶PO"] = strpo;
row["客戶交期"] = dt2.Rows[0]["ShipmentDeliveryDate"];
row["顏色(英文)"] = dtColor.Rows[i顏色數量][0];
for (int iSizeCount = 0; iSizeCount < dtSize.Rows.Count; iSizeCount++)
{
object obtest;
obtest = dt.Compute("sum(數量)", "Color = '" + dtColor.Rows[i顏色數量][0].ToString() + "' and Size = '" + dtSize.Rows[iSizeCount][0] + "'");
row[dtSize.Rows[iSizeCount][0].ToString()] = obtest.ToString().ToUpper().Trim();
}
TempTable.Rows.Add(row);
}
if (TempTable.Columns.Count > 0)
{
for (int i = 0; i < TempTable.Columns.Count; i++)
{
if (TempTable.Columns[i].ColumnName.IndexOf(" (") > 0)
{
//刪除多餘尺寸說明
TempTable.Columns[i].ColumnName = TempTable.Columns[i].ColumnName.Substring(0, TempTable.Columns[i].ColumnName.IndexOf(" ("));
}
}
}
ReferenceCode.ConvertToExcel xx = new ReferenceCode.ConvertToExcel();
xx.ExcelWithNPOI(TempTable, @"xlsx");
}
catch (Exception ex)
{
MessageLB.Text = ex.ToString();
AlertPanel_ModalPopupExtender.Show();
}
}
}
protected void Export_Click(object sender, EventArgs e)
{
//check資料
if (String.IsNullOrEmpty(PurSearchTB.Text) && String.IsNullOrEmpty(StyleNoSeachTB.Text))
{
//ClientScriptManager cs = Page.ClientScript;
//cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('請輸入搜尋資料後再匯出", true);
//Response.Write("<script>var alerttext='Click Checkbox'; alert('Click Checkbox'); </script>");
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('Please enter Search Data');</script>");
}
else
{
#region 讀取Excel
//FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
////Choose one of either 1 or 2
//////1. Reading from a binary Excel file ('97-2003 format; *.xls)
////IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
////2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
////Choose one of either 3, 4, or 5
////3. DataSet - The result of each spreadsheet will be created in the result.Tables
//DataSet result = excelReader.AsDataSet();
//////4. DataSet - Create column names from first row
////excelReader.IsFirstRowAsColumnNames = true;
////DataSet result = excelReader.AsDataSet();
//////5. Data Reader methods
////while (excelReader.Read())
////{
//// //excelReader.GetInt32(0);
////}
////6. Free resources (IExcelDataReader is IDisposable)
//excelReader.Close();
#endregion
//IWorkbook workbook = new XSSFWorkbook(); //-- XSSF 用來產生Excel 2007檔案（.xlsx）
//ISheet u_sheet = (ISheet)workbook.CreateSheet("My Sheet_20方法二");
////== 輸出Excel 2007檔案。==============================
//MemoryStream MS = new MemoryStream(); //==需要 System.IO命名空間
//workbook.Write(MS);
////== Excel檔名，請寫在最後面 filename的地方
//Response.AddHeader("Content-Disposition", "attachment; filename=EmptyWorkbook_2007_1.xlsx");
//Response.BinaryWrite(MS.ToArray());
////== 釋放資源
//workbook = null; //== VB為 Nothing
//MS.Close();
//MS.Dispose();
SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["GGFConnectionString1"].ConnectionString.ToString());
// ---- 不用寫Conn.Open() ，DataAdapter會自動開啟
//作者註解：SqlDataAdapter的 .Fill()方法使用 SQL指令的SELECT，從資料來源擷取資料。
// 此時，DbConnection物件（如Conn）必須是有效的，但不需要是開啟的
// （因為DataAdapter會自動開啟或關閉連結）。
// 如果在呼叫 .Fill ()方法之前關閉 IDbConnection，它會先開啟連接以擷取資料，
// 然後再關閉連接。如果在呼叫 .Fill ()方法之前開啟連接，它會保持開啟狀態。
// 因此，我們使用SqlDataAdapter的時候，不需要寫程式去控制Conn.Open()與 Conn.Close()。
//----(2). 執行SQL指令（Select陳述句）----
SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql(), Conn);
DataSet ds = new DataSet();
myAdapter.Fill(ds, "ACP"); //---- 這時候執行SQL指令。取出資料，放進 DataSet。
////----(3). 自由發揮。由 GridView來呈現資料。----
//GridView1.DataSource = ds.Tables["test"].DefaultView;
////----標準寫法 GridView1.DataSource = ds.Tables["test"].DefaultView; ----
//GridView1.DataBind();
//----(4). 釋放資源、關閉連結資料庫----
//---- 不用寫，DataAdapter會自動關閉
// if (Conn.State == ConnectionState.Open) {
// Conn.Close();
// Conn.Dispose();
// } //使用SqlDataAdapter的時候，不需要寫程式去控制Conn.Open()與 Conn.Close()。
//匯出Excel
GGFPortal.ReferenceCode.ConvertToExcel xx = new ReferenceCode.ConvertToExcel();
xx.ExcelWithNPOI(ds.Tables["ACP"], @"xlsx");
}
}
