Class NPOI

1、classDeclaration (声明)
class WL_NPOIExcel2007 extends RunBase
{
NPOI.XSSF.UserModel.XSSFWorkbook workbook;
Name sheetName;
fileName _fileName;
}
2、cellFont (单元格字体)
void CellFont(NPOI.SS.UserModel.ICellStyle _style,
str fontName,
int fontSize,
int fontColor,
int fontType)
{
NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont();
;
cellFont.set_FontName(fontName);
cellFont.set_FontHeightInPoints(System.Convert::ToInt16(fontSize));
cellFont.set_Color(System.Convert::ToInt16(fontColor));
cellFont.set_Boldweight(System.Convert::ToInt16(fontType));
_style.SetFont(cellFont);
}
3、cellLine (单元格线条)
void CellLine(NPOI.SS.UserModel.ICellStyle _style,
int _left,
int _right,
int _top,
int _bottom)
{
;
switch (_left)
{
case 1:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_right)
{
case 1:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_top)
{
case 1:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_bottom)
{
case 1:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::None);
}
}
4、cellLocation (位置)
void CellLocation(NPOI.SS.UserModel.ICellStyle _style,
int _verticalAlignment,
int _alignment)
{
;
switch (_verticalAlignment)
{
case 1:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Center);
break;
case 2:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Top);
break;
case 3:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Bottom);
break;
case 4:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Justify);
break;
case 5:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Distributed);
break;
default :
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::None);
}
switch (_alignment)
{
case 1:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Center);
break;
case 2:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Left);
break;
case 3:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Right);
break;
case 4:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Fill);
break;
case 5:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Distributed);
break;
case 6:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Justify);
break;
case 7:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::CenterSelection);
break;
default :
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::General);
}
}
5、CreateCell (创建单元格)
NPOI.SS.UserModel.ICell CreateCell(NPOI.SS.UserModel.ISheet _isheet,
int irow,
int icol)
{
NPOI.SS.UserModel.IRow row;
NPOI.SS.UserModel.ICell cell;
;
if (irow >= 1)
row = _isheet.CreateRow(irow - 1);
else
row = _isheet.CreateRow(0);
if (icol >= 1)
cell = row.CreateCell(icol - 1);
else
cell = row.CreateCell(0);
return cell;
}
6、CreateStyle (创建格式)
NPOI.SS.UserModel.ICellStyle CreateStyle()
{
NPOI.SS.UserModel.ICellStyle style;
;
style = workbook.CreateCellStyle();
return style;
}
7、 GetCell (得到单元格)
NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.ISheet _isheet,
int irow,
int icol)
{
NPOI.SS.UserModel.IRow row;
NPOI.SS.UserModel.ICell cell;
;
if (irow >= 1)
row = _isheet.GetRow(irow - 1);
else
row = _isheet.GetRow(0);
if (icol >= 1)
cell = row.GetCell(icol - 1);
else
cell = row.GetCell(0);
return cell;
}
8、GetWorkSheet (得到工作表)
NPOI.SS.UserModel.ISheet GetWorkSheet(name _sheetName)
{
;
return workbook.GetSheet(_sheetName);
}
9、InsertPicture (插入图片)
void InsertPicture(NPOI.SS.UserModel.ISheet _isheet,
filename picFilePath,
int icol,
int irow,
int jcol,
int jrow)
{
int png;
NPOI.XSSF.UserModel.XSSFDrawing patriarch;
NPOI.XSSF.UserModel.XSSFClientAnchor anchor;
NPOI.XSSF.UserModel.XSSFPicture pict;
System.Byte[] bytes;
;
bytes = System.IO.File::ReadAllBytes(picFilePath);
if (bytes)
{
png = workbook.AddPicture(bytes,NPOI.SS.UserModel.PictureType::JPEG);
patriarch = _isheet.CreateDrawingPatriarch();
anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(500,200,0,0,icol,irow,jcol,jrow);
pict = patriarch.CreatePicture(anchor, png);
//pict.Resize(1);
}
}
10、 new 方法
void new(System.IO.FileStream file = null)
{
super();
if (!file)
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
else
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
}
11、 pack 方法
public container pack()
{
return conNull();
}
12、parmFileName
FileName parmFileName(FileName curFileName = _fileName)
{
;
_fileName = curFileName;
return _fileName;
}
13、 ParmSheetName
Name parmSheetName(Name _sheetName = sheetName)
{
sheetName = _sheetName;
return sheetName;
}
14、SaveFileName
void saveFileName()
{
FilenameFilter filter = ["@sys101541","*.xlsx"];
;
_filename = WinAPI::getSaveFileName(0,filter,'',"Save Excel File");
if (strLen(_filename) == 0)
{
throw error("文件没有保存");
}
}
15、 unpack
public boolean unpack(container packedClass)
{
return true;
}
16、workSheet
NPOI.SS.UserModel.ISheet workSheet(name _sheetName)
{
;
return workbook.CreateSheet(_sheetName);
}
17、 construct
public static WL_NPOIExcel2007 construct(System.IO.FileStream file = null)
{
return new WL_NPOIExcel2007(file);
}
18、OpenFileName
static FileName OpenFileName()
{
FilenameFilter filter = ["@sys101541","*.xlsx"];
FileName curFileName;
;
curFileName = WinAPI::getOpenFileName(0,filter,'',"Open Excel2007 File");
if (strLen(curFileName) == 0)
throw error("没有选择excel2007文件！");
return curFileName;
}
19、静态方法
1）
Static NPOI.SS.UserModel.ICellStyle CellStyleSetup(NPOI.XSSF.UserModel.XSSFWorkbook workbook,
str fontName,
int fontSize,
int fontColor,
int fontType,
int verticalAlignment,
int alignment,
int leftLine,
int rightLine,
int topLine,
int bottomLine,
boolean isWrapText = false,
boolean isBold = false)
{
NPOI.SS.UserModel.ICellStyle style;
;
style = workbook.CreateCellStyle();
WL_NPOIExportExcel2007::SetCellFont(workbook,style,fontName,fontSize,fontColor,fontType,isBold);
WL_NPOIExportExcel2007::setCellLoctaion(style,verticalAlignment,alignment);
WL_NPOIExportExcel2007::SetCellLine(style,leftLine,rightLine,topLine,bottomLine);
style.set_WrapText(isWrapText);
return style;
}
2）、
static NPOI.SS.UserModel.IFont SetCellFont(NPOI.XSSF.UserModel.XSSFWorkbook workbook,
NPOI.SS.UserModel.ICellStyle _style,
str fontName,
int fontSize,
int fontColor,
int fontType)
{
NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont();
;
cellFont.set_FontName(fontName);
cellFont.set_FontHeightInPoints(System.Convert::ToInt16(fontSize));
cellFont.set_Color(System.Convert::ToInt16(fontColor));
cellFont.set_Boldweight(System.Convert::ToInt16(fontType));
_style.SetFont(cellFont);
return cellFont;
}
3）
Static NPOI.SS.UserModel.ICellStyle SetCellLine(NPOI.SS.UserModel.ICellStyle _style,
int _left,
int _right,
int _top,
int _bottom)
{
;
switch (_left)
{
case 1:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_right)
{
case 1:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderRight(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_top)
{
case 1:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderTop(NPOI.SS.UserModel.BorderStyle::None);
}
switch (_bottom)
{
case 1:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::DashDot);
break;
case 2:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::DashDotDot);
break;
case 3:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Dashed);
break;
case 4:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Dotted);
break;
case 5:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Double);
break;
case 6:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Hair);
break;
case 7:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Medium);
break;
case 8:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashDot);
break;
case 9:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashDotDot);
break;
case 10:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::MediumDashed);
break;
case 11:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::SlantedDashDot);
break;
case 12:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Thick);
break;
case 13:
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::Thin);
break;
default :
_style.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::None);
}
return _style;
}
4）
Static NPOI.SS.UserModel.ICellStyle setCellLoctaion(NPOI.SS.UserModel.ICellStyle _style,
int _verticalAlignment,
int _alignment)
{
;
switch (_verticalAlignment)
{
case 1:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Center);
break;
case 2:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Top);
break;
case 3:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Bottom);
break;
case 4:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Justify);
break;
case 5:
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::Distributed);
break;
default :
_style.set_VerticalAlignment(NPOI.SS.UserModel.VerticalAlignment::None);
}
switch (_alignment)
{
case 1:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Center);
break;
case 2:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Left);
break;
case 3:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Right);
break;
case 4:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Fill);
break;
case 5:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Distributed);
break;
case 6:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::Justify);
break;
case 7:
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::CenterSelection);
break;
default :
_style.set_Alignment(NPOI.SS.UserModel.HorizontalAlignment::General);
}
return _style;
}
posted @ 2019-06-08 15:46  学习是进步的阶梯  阅读(139)  评论(0)  编辑  收藏  举报