private HSSFWorkbook hssfworkbook = new HSSFWorkbook();
#region Export
public bool exportAttFile()
{
bool ret = false;
try
{
DataTable dt = _dtDetail;
if (dt.Rows.Count <= 0)
{
throw new Exception("沒有任何退料項目可以匯出。");
}
#region Excel
string fileName = string.Format("{0}.xls", _RETURN_NO);
try
{
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
Response.Clear();
InitializeWorkbook();
HSSFCellStyle oHeaderStyle = getHeaderStyle(hssfworkbook);
HSSFCellStyle oContentStyle = getContentStyle(hssfworkbook);
ISheet sheet1 = hssfworkbook.CreateSheet(fileName);
#region 設定寬度
sheet1.SetColumnWidth(0, 5000); // 工單號碼
sheet1.SetColumnWidth(1, 5000); // 批號
sheet1.SetColumnWidth(2, 5000); // 料件編號
sheet1.SetColumnWidth(3, 7000); // 品名
sheet1.SetColumnWidth(4, 10000); // 規格
sheet1.SetColumnWidth(5, 5000); // Product Name
sheet1.SetColumnWidth(6, 5000); // 單位
sheet1.SetColumnWidth(7, 5000); // 倉庫
sheet1.SetColumnWidth(8, 5000); // 儲位
sheet1.SetColumnWidth(9, 5000); // 數量
sheet1.SetColumnWidth(10, 5000); // 備註
#endregion
IRow row = sheet1.CreateRow(0);
int intRow = 0;
row = sheet1.CreateRow(intRow);
#region Title
row.CreateCell(0).SetCellValue("工單號碼");
row.CreateCell(1).SetCellValue("批號");
row.CreateCell(2).SetCellValue("料件編號");
row.CreateCell(3).SetCellValue("品名");
row.CreateCell(4).SetCellValue("規格");
row.CreateCell(5).SetCellValue("Product Name");
row.CreateCell(6).SetCellValue("單位");
row.CreateCell(7).SetCellValue("倉庫");
row.CreateCell(8).SetCellValue("儲位");
row.CreateCell(9).SetCellValue("數量");
row.CreateCell(10).SetCellValue("備註");
#endregion
#region Header Cell Style
row.Cells[0].CellStyle = oHeaderStyle;
row.Cells[1].CellStyle = oHeaderStyle;
row.Cells[2].CellStyle = oHeaderStyle;
row.Cells[3].CellStyle = oHeaderStyle;
row.Cells[4].CellStyle = oHeaderStyle;
row.Cells[5].CellStyle = oHeaderStyle;
row.Cells[6].CellStyle = oHeaderStyle;
row.Cells[7].CellStyle = oHeaderStyle;
row.Cells[8].CellStyle = oHeaderStyle;
row.Cells[9].CellStyle = oHeaderStyle;
row.Cells[10].CellStyle = oHeaderStyle;
#endregion
intRow++;
#region Export list
foreach (DataRow r in dt.Rows)
{
try
{
row = sheet1.CreateRow(intRow);
#region Export Row
row.CreateCell(0).SetCellValue(r["WO"].ToString().Trim());
row.CreateCell(1).SetCellValue(r["LOT"].ToString().Trim());
row.CreateCell(2).SetCellValue(r["PART_NO"].ToString().Trim());
row.CreateCell(3).SetCellValue(r["PART_NAME"].ToString().Trim());
row.CreateCell(4).SetCellValue(r["VERSION_SPEC"].ToString().Trim());
row.CreateCell(5).SetCellValue(r["IM25"].ToString().Trim());
row.CreateCell(6).SetCellValue(r["PART_UNIT"].ToString());
row.CreateCell(7).SetCellValue(r["STOCK"].ToString());
row.CreateCell(8).SetCellValue(r["LOCATION"].ToString());
row.CreateCell(9).SetCellValue(r["LOT_QUANTITY"].ToString().Trim());
row.CreateCell(10).SetCellValue(r["LOT_NOTE"].ToString().Trim());
#endregion
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
#region Content Cell Style
row.Cells[0].CellStyle = oContentStyle;
row.Cells[1].CellStyle = oContentStyle;
row.Cells[2].CellStyle = oContentStyle;
row.Cells[3].CellStyle = oContentStyle;
row.Cells[4].CellStyle = oContentStyle;
row.Cells[5].CellStyle = oContentStyle;
row.Cells[6].CellStyle = oContentStyle;
row.Cells[7].CellStyle = oContentStyle;
row.Cells[8].CellStyle = oContentStyle;
row.Cells[9].CellStyle = oContentStyle;
row.Cells[10].CellStyle = oContentStyle;
#endregion
intRow++;
}
#endregion
Response.BinaryWrite(WriteToStream().GetBuffer());
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
ret = true;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
return ret;
}
/// <summary>
/// WriteToStream
/// </summary>
/// <returns></returns>
private MemoryStream WriteToStream()
{
MemoryStream file = new MemoryStream();
try
{
hssfworkbook.Write(file);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
return file;
}
/// <summary>
/// 工作簿初始化
/// </summary>
private void InitializeWorkbook()
{
try
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Taiflex";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = string.Format("退料單 {0}", _RETURN_NO);
hssfworkbook.SummaryInformation = si;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 設定標頭格樣式
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
public HSSFCellStyle getHeaderStyle(HSSFWorkbook hssfworkbook)
{
HSSFCellStyle ret = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
//設定儲存格框線
ret.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BottomBorderColor = HSSFColor.Black.Index;
ret.LeftBorderColor = HSSFColor.Black.Index;
ret.RightBorderColor = HSSFColor.Black.Index;
ret.TopBorderColor = HSSFColor.Black.Index;
//設定背景顏色
ret.FillForegroundColor = HSSFColor.LemonChiffon.Index;
ret.FillPattern = FillPattern.SolidForeground;
return ret;
}
/// <summary>
/// 設定內容格樣式
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
public HSSFCellStyle getContentStyle(HSSFWorkbook hssfworkbook)
{
HSSFCellStyle ret = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
//設定儲存格框線
ret.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
ret.BottomBorderColor = HSSFColor.Black.Index;
ret.LeftBorderColor = HSSFColor.Black.Index;
ret.RightBorderColor = HSSFColor.Black.Index;
ret.TopBorderColor = HSSFColor.Black.Index;
ret.WrapText = true;
return ret;
}
#endregion