#region 建立Datatable
//建立Datatable
DataTable dt = new DataTable();
//宣告欄位名稱
dt.Columns.Add(new DataColumn("會計科目", typeof(string)));
dt.Columns.Add(new DataColumn("科目代碼", typeof(string)));
dt.Columns.Add(new DataColumn("分公司小計", typeof(Int32)));
dt.Columns.Add(new DataColumn("合計", typeof(Int32)));
//新增一筆資料
DataRow dr = dt.NewRow();
dr["會計科目"] = "小計:";
dr["科目代碼"] = "1001";
dr["分公司小計"] = 12345;
dr["合計"] = 12345;
dt.Rows.Add(dr);
//排序
dt.DefaultView.Sort = "科目代碼,會計科目";
//過濾出分公司小計有不為零的才呈現該筆資料
dt.DefaultView.RowFilter = "ISNULL(分公司小計, 0) <> 0";
//刪除多餘欄位
dt.Columns.Remove("完整科目代碼");
dt.Columns.Remove("科目代碼前四碼");
dt.Columns.Remove("分公司代碼");
//匯出EXCEL
GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
ExportToFile(gv, "表單名稱");
#endregion
#region 匯出Excel ExportToFile
///
/// 匯出Excel ExportToFile
///
public static void ExportToFile(GridView gv, string ExcelName)
{
try
{
//建立 WorkBook 及試算表
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet mySheet1 = (HSSFSheet)workbook.CreateSheet(ExcelName);
mySheet1.DefaultColumnWidth = 15; //預設的字元寬度
//建立標題列 Header
HSSFRow rowHeader = (HSSFRow)mySheet1.CreateRow(0);
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
//若有啟用排序,Header會變成 LinkButton
LinkButton lb = null;
if (gv.HeaderRow.Cells[i].Controls.Count > 0)
{
lb = gv.HeaderRow.Cells[i].Controls[0] as LinkButton;
}
string strValue = (lb != null) ? lb.Text : gv.HeaderRow.Cells[i].Text;
HSSFCell cell = (HSSFCell)rowHeader.CreateCell(i);
cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim());
NPOI.SS.UserModel.CellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //設為居中
cell.CellStyle = cellStyle;
}
//建立內容列 DataRow
for (int i = 0; i < gv.Rows.Count; i++)
{
HSSFRow rowItem = (HSSFRow)mySheet1.CreateRow(i + 1);
for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
{
string value1 = HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim();
long number1 = 0;
bool isNumeric = long.TryParse(value1, out number1);
if (string.IsNullOrEmpty(value1))
{
//空白
rowItem.CreateCell(j).SetCellValue("");
}
else if (!isNumeric)
{
//文字格式
rowItem.CreateCell(j).SetCellValue(value1);
mySheet1.SetColumnWidth(j, 30 * 256); //欄位寬度設為30
}
else
{
//數字格式
HSSFCell cell = (HSSFCell)rowItem.CreateCell(j);
cell.SetCellValue(number1);
//NPOI.SS.UserModel.CellStyle cellStyle = workbook.CreateCellStyle();
//cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");
//cell.CellStyle = cellStyle;
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("#,##0;[RED](#,##0)");
cell.CellStyle = cellStyle;
}
}
}
//匯出
workbook.Write(ms);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("Attachment; Filename=" + HttpUtility.UrlEncode(ExcelName) + ".xls"));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
//釋放資源
workbook = null;
ms.Close();
ms.Dispose();
}
catch (Exception)
{ }
}
#endregion