C# NPOI 匯入EXCEL

C# Workbook create

string xlsName = "name.xls"; //Excel的名稱

//string filePath = HttpContext.Current.Server.MapPath("ExcelSample/") + xlsName; //路徑

//HSSFWorkbook workbook = csExcel.ReadSampleExcel(filePath);  //傳回Excel底檔

//底檔意思是先畫好的Excel 表格

HSSFWorkbook workbook = new HSSFWorkbook();

ISheet u_Sheet = (ISheet)workbook.CreateSheet("sheetName");//sheet 名稱

u_Sheet.DefaultColumnWidth = 11; //預設欄位寬度

C# NPOI Style

//Create a CellStyle for Cell

HSSFCellStyle csStringStyle = (HSSFCellStyle)workbook.CreateCellStyle();

//Border 粗細 分為下左右上

csStringStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

csStringStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;

csStringStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

csStringStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

//Border 顏色 也分為上下左右

csStringStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

csStringStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

csStringStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

csStringStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

//Alignment 對齊 垂直跟水平

csStringStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

csStringStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;

csStringStyle.Indention = 0; //縮排

//自動換行

csStringStyle.WrapText = true;

//Create HSSFFont 

HSSFFont csFontStyle = (HSSFFont)workbook.CreateFont();

csFontStyle.FontName = "標楷體";

csFontStyle.FontHeightInPoints = 10;

//下畫線 csFontStyle.Underline = FontUnderlineType.Single;

csStringStyle.SetFont(csFontStyle);

u_Sheet.SetColumnWidth(3, 6 * 256)

//設定行寬 前面是第幾行 後面是寬度(1/256字元)

u_Sheet.DefaultColumnWidth = 10;

//預設欄寬 一單位等於2char

//Create HSSFDataFormat 

HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();

csNumberStyleRC.DataFormat = format.GetFormat("#,##0.00;-0;;@");

//輸出數字，如果是0則不顯示。

//標題

 u_Sheet.CreateRow(xlsStart); //第xlsStart行

 u_Sheet.GetRow(xlsStart).CreateCell(3).SetCellValue("欄位值"); //第xlsStart列的第三個

 u_Sheet.AddMergedRegion(new CellRangeAddress(xlsStart, xlsStart, 3, 6));

//合併欄位 AddMergedRegion(new CellRangeAddress(起始行,結束行,起始列,結束列))

u_Sheet.GetRow(xlsStart++).GetCell(3).CellStyle = csCellName;  //設定格內Style

#endregion

//Save Excel 

using (MemoryStream ms = new MemoryStream())

{

 workbook.Write(ms);

//寫入緩衝區

xlsName = "ExcelName" + DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss");

string SavexlsName = string.Format("{0}.xls" , xlsName);

//使用時間做區別命名

 

Response.ContentType = "application/vnd.ms-excel";

//ContentType 默認為超文本輸出 是一個標頭檔，告訴網站應該回傳哪種資料

Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(SavexlsName)));

Response.Clear();

Response.BinaryWrite(ms.GetBuffer());

Response.End();

ms.Dispose();

}

//列印用

//在用NPOI匯出EXCEL的時候設定分頁，在網上有查到用sheet1.SetRowBreak(i)方法，但一直都沒有起到作用。經過研究是要設定  sheet1.FitToPage = false; 而這個屬性預設是true的，怪不得一直設定都不起作用。

sheet.RepeatingRows=new CellRangeAddress(0,5,0,5)

Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

sheet1.SetMargin(MarginType.RightMargin, (double)0.5);

sheet1.SetMargin(MarginType.TopMargin, (double)0.6);

sheet1.SetMargin(MarginType.LeftMargin, (double)0.4);

sheet1.SetMargin(MarginType.BottomMargin, (double)0.3);

sheet1.PrintSetup.Copies = 3; sheet1.PrintSetup.NoColor = true;

sheet1.PrintSetup.Landscape = true;

sheet1.PrintSetup.PaperSize = (short)PaperSize.A4;

sheet1.PrintSetup.FitHeight = 2;

sheet1.PrintSetup.FitWidth = 3;

sheet1.IsPrintGridlines = true;

//是否自適應介面 sheet1.FitToPage = false;

//設定列印標題 hssfworkbook.SetRepeatingRowsAndColumns(0, 0, 5, 0, 5);

參考

https://giga0066.pixnet.net/blog/post/29823527

https://www.runoob.com/http/http-content-type.html

https://www.796t.com/content/1544961980.html