ASP.NET NPOI 凍結 篩選 自動調整欄位

 ASP.NET NPOI 凍結 篩選 自動調整欄位


IWorkbook wb = new HSSFWorkbook();
ISheet ws;

ws.CreateFreezePane(1, 1); //凍結視窗

ws.SetAutoFilter(new CellRangeAddress(0, D_table.Rows.Count, 0, D_table.Columns.Count -1)); //篩選資料，Columns會多一個，所以-1 
               
for (int i = 0; i < D_table.Columns.Count; i++)
{
 ws.AutoSizeColumn(i); //欄位寬度自動調整
}

==============================================

        try
        {     
            if (FileUpload1.HasFile)
            {
                String fileName = FileUpload1.FileName; //沒有上傳，只是要檔案路徑

                HSSFWorkbook workbook = new HSSFWorkbook(FileUpload1.FileContent);

                HSSFSheet u_sheet = (HSSFSheet)workbook.GetSheetAt(0); 
                //-- 0表示：第一個 worksheet工作表

                DataTable D_table = new DataTable();

                HSSFRow headerRow = (HSSFRow)u_sheet.GetRow(ddl_sheet.SelectedIndex);  //-- Excel 表頭列

                for (int k = headerRow.FirstCellNum; k < headerRow.LastCellNum; k++)  //-- 表頭列，共有幾個 "欄位"?（取得最後一欄的數字）
                {   //-- 把上傳的 Excel「表頭列」，每一欄位都寫入 DataTable裡面
                    if (headerRow.GetCell(k) != null)
                    {
                        DataColumn D_column = new DataColumn(headerRow.GetCell(k).StringCellValue);
                        D_table.Columns.Add(D_column);
                    }
                }

                //-- for迴圈的「啟始值」要加一，表示不包含 Excel表頭列
                for (int i = (u_sheet.FirstRowNum + 1); i <= u_sheet.LastRowNum; i++)   //-- 每一列做迴圈
                {
                    HSSFRow row = (HSSFRow)u_sheet.GetRow(i);  //--不包含 Excel表頭列的 "其他資料列"
                    DataRow D_dataRow = D_table.NewRow();

                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)   //-- 每一個欄位做迴圈
                    {
                        if (row.GetCell(j) != null)
                        {                     
                            D_dataRow[j] = row.GetCell(j).ToString();  //--每一個欄位，都加入同一列 DataRow
                        }                                     
                    }
                    row.GetCell(ddl_id_column.SelectedIndex).SetCellValue(ckpatno(row.GetCell(ddl_patno_column.SelectedIndex).ToString()));
                    D_table.Rows.Add(D_dataRow);
                }
                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                //== Excel檔名，請寫在最後面 filename的地方
                Response.AddHeader("Content-Disposition", String.Format("attachment; filename=" + fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"));
                Response.BinaryWrite(ms.ToArray());

                //== 釋放資源
                workbook = null;
                u_sheet = null; 
                ms.Close();
                ms.Dispose();
            }
            else
            {
                Label1.Text = "錯 誤 : ????";
            }      
        }
        catch (Exception err)
        {
            Label1.Text ="錯 誤 : " + err.Message;
        }