摘要:NPOI系列-Excel表頭、樣式的設定
一、前言
前面有介紹如何建立一個excel,今天介紹如何設定樣式以及表頭的部份
二、本文
1.以下的code會建立多列的excel,建立之後開啟excel會發現,表頭不像表頭,而第二欄的內容跟表頭並沒有一致
//建立Excel 2003檔案
IWorkbook wb = new HSSFWorkbook();
ISheet idpWorkSheet = wb.CreateSheet("IDP");//建立sheet
//利用sheet建立列
idpWorkSheet.CreateRow(0);//建立第一列檔標題
idpWorkSheet.GetRow(0).CreateCell(0).SetCellValue("Employee");
idpWorkSheet.GetRow(0).CreateCell(1).SetCellValue("IDP內容");
//建立第二行以後作為資料內容(產生100行)
for (int i = 1; i <= 100; i++)
{
idpWorkSheet.CreateRow(i);
idpWorkSheet.GetRow(i).CreateCell(0).SetCellValue("Andrew" + i.ToString());
idpWorkSheet.GetRow(i).CreateCell(1).SetCellValue("這是IDP內容的第" + i.ToString() + "行");
}
FileStream file = new FileStream(@"D:\程式練習\NPOI\ExcelPractice\simple.xls", FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
2.以下的code會設定標題及內容的格式,列印時重覆標題列
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace ExcelPractice
{
public class NpoiTitleAndStyle
{
public void CreateTitleRepeat()
{
//建立Excel 2003檔案
IWorkbook wb = new HSSFWorkbook();
ISheet idpWorkSheet = wb.CreateSheet("IDP");//建立sheet
//利用sheet建立列
idpWorkSheet.CreateRow(0);//建立第一列檔標題
idpWorkSheet.GetRow(0).CreateCell(0).SetCellValue("Employee");
idpWorkSheet.GetRow(0).CreateCell(1).SetCellValue("IDP內容");
//設定標題的style
HSSFCellStyle oStyle = this.GetTitleStyle(wb);
idpWorkSheet.GetRow(0).GetCell(0).CellStyle = oStyle;//將標題row的第一欄指定style
idpWorkSheet.GetRow(0).GetCell(1).CellStyle = oStyle;//將標題row的第二欄指定style
int sheetNo = 0;
int firstColumn = 0;
int secondColumn = 1;
int startRow = 0;
int endRow = 0;
wb.SetRepeatingRowsAndColumns(sheetNo, firstColumn, secondColumn, startRow, endRow);
//設定1、2欄寬
idpWorkSheet.SetColumnWidth(0, 10 * 256);
idpWorkSheet.SetColumnWidth(1, 30 * 256);
HSSFCellStyle contentStyle = this.GetDetailContent(wb);//設定IDP內谷的格式
//建立第二行以後作為資料內容(產生100行)
for (int i = 1; i <= 100; i++)
{
idpWorkSheet.CreateRow(i);
idpWorkSheet.GetRow(i).CreateCell(0).SetCellValue("Andrew" + i.ToString());
idpWorkSheet.GetRow(i).CreateCell(1).SetCellValue("這是IDP內容的第" + i.ToString() + "行");
idpWorkSheet.GetRow(i).GetCell(0).CellStyle = contentStyle;
idpWorkSheet.GetRow(i).GetCell(1).CellStyle = contentStyle;
}
FileStream file = new FileStream(@"D:\程式練習\NPOI\ExcelPractice\simple.xls", FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
private HSSFCellStyle GetTitleStyle(IWorkbook wb)
{
HSSFCellStyle oStyle = (HSSFCellStyle)wb.CreateCellStyle();
//設定背景顏色
oStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey40Percent.Index;
oStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//灰色,顏色參考資料http://www.dotblogs.com.tw/lastsecret/archive/2010/12/20/20250.aspx
return oStyle;
}
private HSSFCellStyle GetDetailContent(IWorkbook wb)
{
HSSFCellStyle oStyle = (HSSFCellStyle)wb.CreateCellStyle();
//設定上下左右的框線
oStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;//粗
oStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//細實線
oStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Hair;//虛線
oStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Dotted;//...
return oStyle;
}
}
}
2.1標題的設定
2.2內容的格式設定