protected void btnExcel_Click(object sender, EventArgs e)
{
int i = 0;
try
{
i = this.Grid1.SelectedRowIndexArray[0];
}
catch
{
Alert.Show("Select Record First!");
return;
}
GridRow rowInstance = Grid1.Rows[i];
ToExcel(rowInstance.Values[2].ToString());
}
protected void btnExport_Click(object sender, EventArgs e)
{
string style = "<style>td{mso-number-format:\"\\@\";}</style>";
Response.ClearContent();
Response.Charset = "utf-8";
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.AddHeader("content-disposition", "attachment; filename=GDSReport.xls");
Response.ContentType = "application/excel";
Response.Charset = "utf-8";
Response.Write(style);
Response.Write(GetGridTableHtml(Grid1));
Response.End();
}
private string GetGridTableHtml(Grid grid)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
sb.Append("<tr>");
int j = 1;
foreach (GridColumn column in grid.Columns)
{
if (j > 2)
sb.AppendFormat("<td>{0}</td>", column.HeaderText);
else
j++;
}
sb.Append("</tr>");
foreach (GridRow row in grid.Rows)
{
sb.Append("<tr>");
int i = 1;
foreach (object value in row.Values)
{
if (i <= 2)
{
//sb.AppendFormat("<td>'{0}</td>", value.ToString());
}
else
{
sb.AppendFormat("<td>{0}</td>", value.ToString());
}
i += 1;
}
sb.Append("</tr>");
}
sb.Append("</table>");
return sb.ToString();
}
private void ToExcel(string docno)
{
string factoryid = ddlPlant.SelectedItem.Value;
Aspose.Cells.License lic = new Aspose.Cells.License();
string AsposeLicPath = string.Empty;
//if (ConfigurationSettings.AppSettings["AsposeLicPath"] == null)
//{
// throw new Exception("Please setup [AsposeLicPath] in Web.Config!");
//}
AsposeLicPath = ConfigurationManager.AppSettings["AsposePath"].ToString();
lic.SetLicense(AsposeLicPath + "\\Aspose.Cells.lic");
string templatePath=string.Empty;
string gpro = ConfigurationManager.AppSettings["GPRO"].ToString();
if (factoryid ==gpro)
templatePath = AppDomain.CurrentDomain.BaseDirectory + "GPRONPITemplate.xlsx";
else
templatePath = AppDomain.CurrentDomain.BaseDirectory + "NPITemplate.xlsx";
//string logoPath = @"C:\BACKUP\Template\logo.jpg";
//Instantiate a new Workbook object.
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(templatePath);
//book.LoadData(templatePath); // GET TEMPLATE
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
//設定導出格式
sheet.PageSetup.IsPercentScale = false;
sheet.PageSetup.FitToPagesWide = 1; //自動縮放為一頁寬
sheet.PageSetup.FitToPagesTall = 10;
sheet.PageSetup.LeftMargin = 0.5;
sheet.PageSetup.RightMargin = 0.5;
sheet.PageSetup.TopMargin = 0.5;
sheet.PageSetup.BottomMargin = 0.5;
sheet.PageSetup.Orientation = Aspose.Cells.PageOrientationType.Portrait;
Aspose.Cells.Cells cells = sheet.Cells;
System.Globalization.NumberFormatInfo NFI0 = new System.Globalization.NumberFormatInfo();
NFI0.NumberDecimalDigits = 0; //设定的小数位数
System.Globalization.NumberFormatInfo NFI3 = new System.Globalization.NumberFormatInfo();
NFI3.NumberDecimalDigits = 3; //设定的小数位数
System.Globalization.NumberFormatInfo NFI5 = new System.Globalization.NumberFormatInfo();
NFI5.NumberDecimalDigits = 5; //设定的小数位数
// REPLACE PUBLIC FIELDS
string sql = "SELECT * from tb_NPI_Master where docno='" + docno +"'";
ArrayList opc = new ArrayList();
DataTable dtmaster = sdb.GetDataTable(sql, opc);
DataRow dr = dtmaster.Rows[0];
sheet.Replace("{ProductLine}", dr["productline"].ToString());
sheet.Replace("{ProductName}", dr["productname"].ToString());
sheet.Replace("{Customer}", dr["customer"].ToString());
sheet.Replace("{CurrentPhase}", dr["currentphase"].ToString());
sheet.Replace("{ProVersion}", dr["proversion"].ToString());
sheet.Replace("{CustomerModelName}", dr["customermodelname"].ToString());
sheet.Replace("{CustomerPN}", dr["customerpn"].ToString());
sheet.Replace("{LiteonPN}", dr["pn"].ToString());
sheet.Replace("{Begindate}", Convert.ToDateTime(dr["begindate"]).ToString("yyyy-MM-dd"));
sheet.Replace("{Enddate}", Convert.ToDateTime(dr["enddate"]).ToString("yyyy-MM-dd"));
sheet.Replace("{Lotqty}", dr["lotqty"].ToString());
sheet.Replace("{pmowner}", dr["pm_owner"].ToString());
sheet.Replace("{peowner}", dr["pe_owner"].ToString());
sheet.Replace("{ieowner}", dr["ie_owner"].ToString());
sheet.Replace("{npiqowner}", dr["npiq_owner"].ToString());
sheet = book.Worksheets[1];
sheet.Replace("{Customer}", dr["customer"].ToString());
sheet.Replace("{ProductName}", dr["productname"].ToString());
sheet.Replace("{CurrentPhase}", dr["currentphase"].ToString());
sql = "select * from tb_npi_teamdata where docno='" + docno + "' and [role]=0 order by sortid ";
DataTable dtteam = sdb.GetDataTable(sql, opc);
sql = "select * from tb_npi_teamdata where docno='" + docno + "' and [role]=1 order by sortid ";
DataTable dtleader = sdb.GetDataTable(sql, opc);
//試產報告主管簽核表
string resutl= dr["result"] == System.DBNull.Value ? "" : dr["result"].ToString();
string mdphase = dr["mdphase"] == System.DBNull.Value ? "" : dr["mdphase"].ToString();
sheet = book.Worksheets[2];
cells = sheet.Cells;
sheet.Replace("{Customer}", dr["customer"].ToString());
sheet.Replace("{ProductName}", dr["productname"].ToString());
sheet.Replace("{CurrentPhase}", dr["currentphase"].ToString());
sheet.Replace("{NextPhase}", dr["nextphase"].ToString());
sheet.Replace("{MD_Comment}", dr["md_comment"] == System.DBNull.Value ? "" : dr["md_comment"].ToString());
sheet.Replace("{MD_Approver}", dr["md_approver"] == System.DBNull.Value ? "" : dr["md_approver"].ToString());
sheet.Replace("{MD_Date}", dr["md_date"] == System.DBNull.Value ? "" : Convert.ToDateTime(dr["md_date"]).ToString("yyyy-MM-dd"));
sheet.Replace("{Result}",resutl);
sheet.Replace("{MDPhase}", mdphase );
//REPLACE DETAIL FIELDS
int girdstartindex = cells.FindStringContains("{Grid1}", null).Row + 1;
int gridrowscount = 0;
int insertindex = girdstartindex;
gridrowscount = dtleader.Rows.Count;
cells.InsertRows(insertindex, gridrowscount);
string deptname = string.Empty;
string comment = string.Empty;
string trsuser = string.Empty;
string trsdate = string.Empty;
for (int i = 0; i < gridrowscount; i++)
{
DataRow dr1 = dtleader.Rows[i];
//cells.Merge(insertindex + i, 0, 1, 1);
cells.Merge(insertindex + i, 1, 1, 5);
cells[insertindex + i, 0].PutValue(dr1["deptname"].ToString());
if (dr1["comment"] == System.DBNull.Value)
comment = "";
else
comment = dr1["comment"].ToString();
if (dr1["trsuser"] == System.DBNull.Value)
trsuser = "";
else
trsuser = dr1["trsuser"].ToString();
if (dr1["trsdate"] == System.DBNull.Value)
trsdate = "";
else
trsdate = Convert.ToDateTime(dr1["trsdate"]).ToString("yyyy-MM-dd");
cells[insertindex + i, 1].PutValue(comment);
cells[insertindex + i, 6].PutValue(trsuser);
cells[insertindex + i, 7].PutValue(trsdate);
}
cells.DeleteRows(girdstartindex - 1, 1);
//試產報告成員反饋意見*****************************************************
sheet = book.Worksheets[3];
cells = sheet.Cells;
sheet.Replace("{Customer}", dr["customer"].ToString());
sheet.Replace("{ProductName}", dr["productname"].ToString());
sheet.Replace("{CurrentPhase}", dr["currentphase"].ToString());
sheet.Replace("{NextPhase}", dr["nextphase"].ToString());
//REPLACE DETAIL FIELDS
girdstartindex = cells.FindStringContains("{Grid2}", null).Row + 1;
gridrowscount = 0;
insertindex = girdstartindex;
gridrowscount = dtteam.Rows.Count;
cells.InsertRows(insertindex, gridrowscount);
for (int i = 0; i < gridrowscount; i++)
{
DataRow dr1 = dtteam.Rows[i];
//cells.Merge(insertindex + i, 0, 1, 1);
cells.Merge(insertindex + i, 1, 1, 5);
cells[insertindex + i, 0].PutValue(dr1["deptname"].ToString());
if (dr1["comment"] == System.DBNull.Value)
comment = "";
else
comment = dr1["comment"].ToString();
if (dr1["trsuser"] == System.DBNull.Value)
trsuser = "";
else
trsuser = dr1["trsuser"].ToString();
if (dr1["trsdate"] == System.DBNull.Value)
trsdate = "";
else
trsdate = Convert.ToDateTime(dr1["trsdate"]).ToString("yyyy-MM-dd");
cells[insertindex + i, 1].PutValue(comment);
cells[insertindex + i, 6].PutValue(trsuser);
cells[insertindex + i, 7].PutValue(trsdate);
}
cells.DeleteRows(girdstartindex - 1, 1);
//試產報告*****************************************************
sheet = book.Worksheets[4];
cells = sheet.Cells;
sheet.Replace("{Customer}", dr["customer"].ToString());
sheet.Replace("{ProductName}", dr["productname"].ToString());
sheet.Replace("{CurrentPhase}", dr["currentphase"].ToString());
sheet.Replace("{Lotqty}", dr["lotqty"].ToString());
sheet.Replace("{PCBVersion}", dr["pcbversion"].ToString());
sheet.Replace("{Begindate}", Convert.ToDateTime(dr["begindate"]).ToString("yyyy-MM-dd"));
sheet.Replace("{Enddate}", Convert.ToDateTime(dr["enddate"]).ToString("yyyy-MM-dd"));
sheet.Replace("{Problem}", dr["problem"].ToString());
sheet.Replace("{Deliveryqty}", dr["deliveryqty"].ToString());
sheet.Replace("{DocNo}", dr["docno"].ToString());
sql = "select *from dbo.TB_NPI_StationData where docno='" + docno + "' order by itemid ";
DataTable dtstation = sdb.GetDataTable(sql, opc);
//REPLACE DETAIL FIELDS
girdstartindex = cells.FindStringContains("{Grid3}", null).Row + 1;
gridrowscount = 0;
insertindex = girdstartindex;
gridrowscount = dtstation.Rows.Count;
cells.InsertRows(insertindex, gridrowscount);
for (int i = 0; i < gridrowscount; i++)
{
DataRow dr2 = dtstation.Rows[i];
cells.Merge(insertindex + i, 0, 1, 2);
cells.Merge(insertindex + i, 7, 1, 2);
cells.Merge(insertindex + i, 9, 1, 2);
cells[insertindex + i, 0].PutValue(dr2["station"].ToString());
cells[insertindex + i, 2].PutValue(dr2["inputqty"].ToString());
cells[insertindex + i, 3].PutValue(dr2["defect_mat"].ToString());
cells[insertindex + i, 4].PutValue(dr2["defect_mac"].ToString());
cells[insertindex + i, 5].PutValue(dr2["defect_des"].ToString());
cells[insertindex + i, 6].PutValue(dr2["defect_hum"].ToString());
cells[insertindex + i, 7].PutValue(dr2["rowtotal"].ToString());
cells[insertindex + i, 9].PutValue(dr2["yieldrate"].ToString());
}
cells.DeleteRows(girdstartindex - 1, 1);
//移除模板列 cells.DeleteRows(girdstartindex - 3, 4);
//Save the workbook as a PDF File
//this.EnableViewState = false;
this.Response.Clear();
Aspose.Cells.SaveOptions saveOptions = new Aspose.Cells.XlsSaveOptions(Aspose.Cells.SaveFormat.Xlsx);
book.Save(this.Response,"NPIReport-" + docno + ".xlsx", Aspose.Cells.ContentDisposition.Attachment ,saveOptions);
//End response to avoid unneeded html after xls
//this.Response.End();
}
