POI(EXCEL) 設定樣式

2013-12-26
12344
0
摘要:POI(EXCEL) 設定樣式
		=======設定樣式==============================================================

			private HSSFCellStyle lAlignYellowStyle = null;

			private HSSFCellStyle lAlignStyle = null;

			private HSSFCellStyle rAlignStyle = null;

			private HSSFCellStyle rAlignYellowStyle = null; 

			//一般(靠左+邊框)

			lAlignStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設定儲存格外框

			lAlignStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

			lAlignStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

			lAlignStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

			//合計(靠左+黃色)

			lAlignYellowStyle = wb.createCellStyle();

			lAlignYellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			lAlignYellowStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

			lAlignYellowStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

			lAlignYellowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設定儲存格外框

			lAlignYellowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

			lAlignYellowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

			lAlignYellowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

			//一般(靠右+邊框)

			rAlignStyle = wb.createCellStyle();

			rAlignStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

			rAlignStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設定儲存格外框

			rAlignStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

			rAlignStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

			rAlignStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

			//合計(靠右+黃色)

			rAlignYellowStyle = wb.createCellStyle();

			rAlignYellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			rAlignYellowStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

			rAlignYellowStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

			rAlignYellowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設定儲存格外框

			rAlignYellowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

			rAlignYellowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

			rAlignYellowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

			cell.setCellStyle(lAlignYellowStyle);

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

		MemoryStream ms = new MemoryStream();    

		HSSFWorkbook workbook = new HSSFWorkbook();    

		HSSFSheet sheet = workbook.CreateSheet("sample");

		sheet.createFreezePane(從第幾列, 到第幾列, 從第幾欄, 到第幾欄); //凍結列

			CellRangeAddress region = new CellRangeAddress(0,0,3,16);      //合併欄位  

			sheet.addMergedRegion(region);

				CellRangeAddress region = new CellRangeAddress(0,1,3,3);      //合併一、二列的第三個欄位

				sheet.addMergedRegion(region);

		HSSFCell cell = null;    

		HSSFCellStyle borderStyle = null;    

		HSSFCellStyle colorStyle = null;    

		HSSFCellStyle fontStyle = null;    

		HSSFCellStyle heightStyle = null;    

		HSSFCellStyle spanStyle = null;    

		HSSFCellStyle wrapStyle = null;    

		HSSFFont font = null;

		borderStyle = workbook.CreateCellStyle();    

		colorStyle = workbook.CreateCellStyle();    

		fontStyle = workbook.CreateCellStyle();    

		heightStyle = workbook.CreateCellStyle();    

		spanStyle = workbook.CreateCellStyle();    

		wrapStyle = workbook.CreateCellStyle();

		//Style設定    

		borderStyle.BorderTop = HSSFCellStyle.BORDER_THIN;    

		borderStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;    

		borderStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;    

		borderStyle.BorderRight = HSSFCellStyle.BORDER_THIN;

		colorStyle.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;

		colorStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;

		fontStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;    

		fontStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;    

		wrapStyle.WrapText = true;

		//指定紙張大小 A3=8, A4=9, Letter=1    

		sheet.PrintSetup.PaperSize = 9;

		//指定直式或橫式 true=橫式 false=直式

		sheet.PrintSetup.Landscape = true;

		//藏隱格線

		sheet.DisplayGridlines = false;

		//設定欄寬     

		sheet.SetColumnWidth(0, 24 * 256);    

		sheet.SetColumnWidth(1, 24 * 256);

		//指定列高        

		cell = sheet.CreateRow(0).CreateCell(0);    

		cell.SetCellValue("指定列高");    

		cell.CellStyle = heightStyle;    

		sheet.GetRow(0).HeightInPoints = 50;

		//字型大小    

		font = workbook.CreateFont();    

		font.FontHeightInPoints = 14;    

		font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;   

		fontStyle.SetFont(font);    

		cell = sheet.CreateRow(1).CreateCell(0);    

		cell.CellStyle = fontStyle;    

		cell.SetCellValue("字型大小14粗體");

		//合併儲存格    

		cell = sheet.CreateRow(2).CreateCell(0);    

		cell.SetCellValue("合併儲存格");    

		cell.CellStyle = spanStyle;    

		sheet.AddMergedRegion(new Region(2, 0, 3, 1));

		//Wrap    

		cell = sheet.CreateRow(4).CreateCell(0);    

		cell.SetCellValue(string.Format("換行{0}測試", System.Environment.NewLine));    

		cell.CellStyle = wrapStyle;

		//增加邊框

		cell = sheet.CreateRow(5).CreateCell(1);    

		cell.SetCellValue("邊框                  ");    

		cell.CellStyle = borderStyle;

		//背景    

		cell = sheet.CreateRow(6).CreateCell(0);    

		cell.SetCellValue("背景");    

		cell.CellStyle = colorStyle;

		//插入分頁    

		sheet.SetRowBreak(cell.RowIndex);

		//下一頁資料    

		cell = sheet.CreateRow(7).CreateCell(0);    

		cell.SetCellValue("下一頁資料");

		workbook.Write(ms);        

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

		context.Response.AddHeader("Content-Disposition", "attachment; filename=myExcel.xls");    

		context.Response.BinaryWrite(ms.ToArray());

		參照網址：http://shihshu.blogspot.tw/2010/05/npoi.html
回首頁