poiExcel表格所有操作以及数据导入导出

这个是本人在学习中记录的笔记以供大家参考

1. Poi简介：

1.1什么是poi

Apache POI [1]  是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。

1.2poi结构

HSSF [1]  － 提供读写Microsoft Excel XLS格式档案的功能。

XSSF [1]  － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF [1]  － 提供读写Microsoft Word DOC格式档案的功能。

HSLF [1]  － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF [1]  － 提供读Microsoft Visio格式档案的功能。

HPBF [1]  － 提供读Microsoft Publisher格式档案的功能。

HSMF [1]  － 提供读Microsoft Outlook格式档案的功能。

这些都是poi提供的一些包

 

 

 

1.3:下载地址

这个是最新的jar包，4.0的

https://www.apache.org/dyn/closer.lua/poi/release/bin/poi-bin-4.0.0-20180907.tar.gz

还有源码包的下载

https://www.apache.org/dyn/closer.lua/poi/release/src/poi-src-4.0.0-20180907.tar.gz

下载点击这个

 

1.4:常用类说明

 

类名                     说明

HSSFWorkbook           Excel的文档对象

HSSFSheet          sheet页

Excel的表单

HSSFRow               

Excel的行

HSSFCell     

Excel的格子单元

HSSFFont                Excel字体

 

HSSFDataFormat        格子单元的日期格式

HSSFHeader             Excel文档Sheet的页眉

HSSFFooter             Excel文档Sheet的页脚

HSSFCellStyle          格子单元样式

HSSFDateUtil           日期

HSSFPrintSetup        打印

HSSFErrorConstants   错误信息表

 

 

1. poi基础
   1. 新建一个表格

首先要将下载好的jar包，解压将根目录下的所有包，以及将lib目录下的commons-logging-1.2.jar

和junit-4.12.jar以及log4j-1.2.17.jar这三个通用包导入到项目的lib的目录下，poi-4.0.0.jar包是最

重要的。

主要介绍HSSFWorkbook

/**

 * 多态实现

 * HSSFWorkbook  是对于xls进行操作

 */

Workbook wb=new HSSFWorkbook();//新建一个工作簿

/**

 * 导出所以用到输出流

      *参数为输出的地址

 */

FileOutputStream fout=new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);//Workbook提供了write的方法

fout.close();//将输出流关闭

2.1.1新建sheet页

在新建工作簿的基础上新建sheet页,wb.createSheet() 返回一个Sheet()如果不进行操作不用接收

/**

 * 多态实现

 * HSSFWorkbook  是对于xls进行操作

 */

Workbook wb=new HSSFWorkbook();//新建一个工作簿

/**

 * 导出所以用到输出流

 */

FileOutputStream fout=new FileOutputStream("E:\\Demo\\poi.xls");

/**

 * 有有参和无参数两种

 * 参数为sheet页的名字

 * 不写参数默认名字为sheet0到n

 */

Sheet sheet1 = wb.createSheet();//创建一个sheet页

Sheet sheet2 = wb.createSheet("第二个sheet页");//创建第二个sheet页

wb.write(fout);

fout.close();//将输出流关闭

一些工作表的方法

workbook.setActiveSheet(工作表下标);//设置默认工作表

workbook.setSheetName(2（工作表下标）, "1234"（新名字）);//重命名工作表

sheet1.setZoom(1,2);//50%显示比例

sheet2.setZoom(2,1);//200%显示比例

sheet3.setZoom(1,10);//10%显示比例

显示/隐藏网格线

HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)

HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)

sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true

sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false

 

 

2.1.2新建行和列

创建行要在sheet页的基础上，单元格在行的基础上创建，每一行一个行对象Row

对于单元格赋值cell对象setCellValue()就可以，而且行和单元格的创建都从0 开始

列的值可以设置的类型也比较多样 int  double  String boolean  Date 等都是可以的

Workbook wb=new HSSFWorkbook();//新建一个工作簿

FileOutputStream fout=new FileOutputStream("E:\\Demo\\poi.xls");

Sheet sheet= wb.createSheet("第一个sheet页");//创建一个sheet页

Row row=sheet.createRow(0); // 创建一个行 第一行

Cell cell=row.createCell(0); // 创建一个单元格  第1列

/**

 * 给单元格设置值

 *值类型可以为int  double  String boolean  

 *还有Date等

 */

cell.setCellValue(1);

row.createCell(1).setCellValue(1.2);//创建第一行第二个单元格

row.createCell(2).setCellValue("这是一个单元格");//创建第一行第三个单元格

row.createCell(3).setCellValue(false);//创建第一行第四个单元格

/**

 * HSSFCell一些静态常量

 * short类型和int类型

 */

row.createCell(4).setCellValue(HSSFCell.ENCODING_COMPRESSED_UNICODE);

/**

 * 第二个行  每一行需要一个行对象

 * 所以需要保存一个行对象

 */

Row row1 = sheet.createRow(1);

row1.createCell(0).setCellValue("第二行第一列");

row1.createCell(1).setCellValue(true);

row1.createCell(2).setCellValue("第二行第三列");

row1.createCell(3).setCellValue("第二行第四列");

wb.write(fout);//输出

fout.close();

2.1.2.1组合行列

HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)

sheet.groupRow(1, 3);//组合行

sheet.groupRow(2, 4);//组合行

sheet.groupColumn(2, 7);//组合列

 

 

 这里简单的介绍一下什么叫做组合：组合分为行组合和列组合，所谓行组合，就是让n行组合成一个集合，能够进行展开和合拢操作。

     使用POI也可以取消组合，例如：sheet.ungroupColumn(1, 3);//取消列组合

2.1.2.2锁定列

 在Excel中，有时可能会出现列数太多或是行数太多的情况，这时可以通过锁定列来冻结部分列，不随滚动条滑动，方便查看。

HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)

sheet.createFreezePane(2, 3, 15, 25);//冻结行列

下面对CreateFreezePane的参数作一下说明：

          第一个参数表示要冻结的列数；

          第二个参数表示要冻结的行数，这里只冻结列所以为0；

          第三个参数表示右边区域可见的首列序号，从1开始计算；

          第四个参数表示下边区域可见的首行序号，也是从1开始计算，这里是冻结列，所以为0；

2.1.2.3上下移动行

FileInputStream stream = new FileInputStream(filePath);

HSSFWorkbook workbook = new HSSFWorkbook(stream);

HSSFSheet sheet = workbook.getSheet("Test0");

sheet.shiftRows(2, 4, 2);//把第3行到第4行向下移动两行

 HSSFSheet.shiftRows(startRow, endRow, n)参数说明

          startRow：需要移动的起始行；

          endRow：需要移动的结束行；

          n：移动的位置，正数表示向下移动，负数表示向上移动；

 

2.1.3创建文档摘要信息

这里只是抽取了关键代码，这个是给文件设置一些摘要信息

HSSFWorkbook wb = new HSSFWorkbook();// 新建一个工作簿

wb.createInformationProperties();//创建文档信息

DocumentSummaryInformation dsi= wb.getDocumentSummaryInformation();//摘要信息

dsi.setCategory("类别:Excel文件");//类别

dsi.setManager("管理者:花花");//管理者

dsi.setCompany("公司:zking");//公司

SummaryInformation si = wb.getSummaryInformation();//摘要信息

si.setSubject("主题:--");//主题

si.setTitle("标题:测试文档");//标题

si.setAuthor("作者:花花");//作者

si.setComments("备注:POI测试文档");//备注

结果为：

2.1.4创建表格批注信息

HSSFWorkbook wb = new HSSFWorkbook();

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

 

HSSFSheet sheet = wb.createSheet("工作表名");// 创建工作表(Sheet页)

 

HSSFPatriarch patr = sheet.createDrawingPatriarch();

HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8, 3);// 创建批注位置

HSSFComment comment = patr.createCellComment(anchor);// 创建批注

comment.setString(new HSSFRichTextString("这是一个批注段落！"));// 设置批注内容

comment.setAuthor("花花");// 设置批注作者

comment.setVisible(true);// 设置批注默认显示

HSSFCell cell = sheet.createRow(2).createCell(1);

cell.setCellValue("测试");

cell.setCellComment(comment);// 把批注赋值给单元格

 

wb.write(fout);

创建批注位置HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法参数说明：

1. dx1 第1个单元格中x轴的偏移量
2. dy1 第1个单元格中y轴的偏移量
3. dx2 第2个单元格中x轴的偏移量
4. dy2 第2个单元格中y轴的偏移量
5. col1 第1个单元格的列号
6. row1 第1个单元格的行号
7. col2 第2个单元格的列号
8. row2 第2个单元格的行号

也就是：

2.1.5 创建页眉和页脚

HSSFWorkbook wb = new HSSFWorkbook();

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFHeader header =sheet.getHeader();//得到页眉

header.setLeft("页眉左边");

header.setRight("页眉右边");

header.setCenter("页眉中间");

String tab = HSSFHeader.tab();//表名

System.out.println(tab);

HSSFFooter footer =sheet.getFooter();//得到页脚

footer.setLeft("页脚左边");

footer.setRight("页脚右边");

footer.setCenter("页脚中间");

 

wb.write(fout);

fout.close();

   也可以使用Office自带的标签定义，你可以通过HSSFHeader或HSSFFooter访问到它们，都是静态属性，列表如下：

1. HSSFHeader.tab &A 表名
2. HSSFHeader.file &F 文件名
3. HSSFHeader.startBold &B 粗体开始
4. HSSFHeader.endBold &B 粗体结束
5. HSSFHeader.startUnderline &U 下划线开始
6. HSSFHeader.endUnderline &U 下划线结束
7. HSSFHeader.startDoubleUnderline &E 双下划线开始
8. HSSFHeader.endDoubleUnderline &E 双下划线结束
9. HSSFHeader.time &T 时间
10. HSSFHeader.date &D 日期
11. HSSFHeader.numPages &N 总页面数
12. HSSFHeader.page &P 当前页号

 

 

2.1.6 时间格式的单元格

由于很多代码都是重复的也出现了多次我在这，也就不写在笔记里了，只摘取了关键部分

CreationHelper  一个小工具类，使用工作簿创建Workbook getCreationHelper()

CellStyle  单元格的样式类，也是使用Workbook创建createCellStyle()

Row row=sheet.createRow(0); // 创建一个行 第一行

Cell cell=row.createCell(0); // 创建一个单元格  第1列

cell.setCellValue(new Date());

/**

 * 小工具

 * 使用工作簿获取

 */

CreationHelper  creationHelper=wb.getCreationHelper();

/**

 * 单元格样式类

 * 使用工作簿Workbook创建

 */

CellStyle  cellStyle=wb.createCellStyle();

/**

 * 设置时间的显示格式

 */

cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd :hh:mm:ss"));

cell=row.createCell(1);//创建第二列

cell.setCellValue(new Date());

/**

 * 设置单元格的格式

 */

cell.setCellStyle(cellStyle);

/**

 * 第三列和第二列的效果是一样的

 */

cell=row.createCell(2);//创建第三列

/**

 * Calendar 日历类 也可以用来获取时间

 */

cell.setCellValue(Calendar.getInstance());

cell.setCellStyle(cellStyle);

2.2.1读取工作簿

获取单元格的内容主要是要区分每个单元格的值类型，不然会报错的，而且4.0和3.9有很大的区别一定要注意版本之间的差别，

public static void main(String[] args) throws IOException {

FileInputStream fins=new FileInputStream("E:\\Demo\\好气呀没事改什么版本.xls");

/**

 * poi文件系统 可以接收入一个输入流

 */

POIFSFileSystem  pfs=new POIFSFileSystem(fins);

/**

 * 创建一个工作簿  

 * 因为HSSFWorkbook实现了接口而且有了接口里没有的方法

 * 所以用这个

 */

HSSFWorkbook wb=new HSSFWorkbook(pfs);

/**

 * 获取第一个sheet页

 */

HSSFSheet sheetAt = wb.getSheetAt(0);

/**

 * 如果不存在sheet页就return

 */

if(sheetAt==null) {

return;

}

 

/**

 * 遍历当前的sheet页里面的行

 * sheetAt.getLastRowNum() 获取到最后一行的位置

 */

 

for(int rowIndex=0;rowIndex<=sheetAt.getLastRowNum();rowIndex++){

/**

 * 获取当前的行对象

 */

  HSSFRow hssfRow = sheetAt.getRow(rowIndex);//获取到当前行的对象

if(hssfRow==null){

continue;

}

        /**

          * 同理遍历当前行里的所有列

          * hssfRow.getLastCellNum()

          * 获取到最后一列的位置

          */

for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++){

HSSFCell hssfCell=hssfRow.getCell(cellNum);//获取到列对象

if(hssfCell==null){

continue;

}

/**

 * 调用值处理的方法

 */

System.out.print(" "+getValue(hssfCell));

}

/**

 * 换行的效果

 */

 

System.out.println();

}

}

/**

 *

* @Title: getValue

* @Description: (判断单元格对象的值类型 这是3.9版本的写法)

* @param hssfCell

* @return

* @return String

 */

private static String getValue(HSSFCell hssfCell){

/**

 * 值类型的常量值

 * HSSFCell.CELL_TYPE_BOOLEAN

 */

if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){

return String.valueOf(hssfCell.getBooleanCellValue());

}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){

/**

 * 小的工具类 判断此单元格是不是时间格式

 */

  if(HSSFDateUtil.isCellDateFormatted(hssfCell)){

  /**

   * 取到的时候数字

   * 因为时间格式不能显示给人家数字所以转换

   * 转换格式

   */

              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

              return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));

          }

return String.valueOf(hssfCell.getNumericCellValue());

} else{

return String.valueOf(hssfCell.getStringCellValue());

}

}

 

 

/**

 * s

* @Title: getValue

* @Description: (从单元格内判断取值  这是4.0版本的写法)

* @param hssfCell

* @return

* @return String

 */

public static String getValue(HSSFCell hssfCell) {

/**

 * 这里获取到的是String类型的常量值还是有变化的

 */

 

if(hssfCell.getCellType()==CellType.BOOLEAN){

return String.valueOf(hssfCell.getBooleanCellValue());

}else if(hssfCell.getCellType()==CellType.NUMERIC){

/**

 * 小的工具类 判断此单元格是不是时间格式

 */

  if(HSSFDateUtil.isCellDateFormatted(hssfCell)){

  /**

   * 转换格式

   */

              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

              return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));

          }

  return String.valueOf(hssfCell.getNumericCellValue());

   } else{

return String.valueOf(hssfCell.getStringCellValue());

  }

}

2.2.2文本提取

ExcelExtractor 直接抽取EXcel里的内容为文本，可以设置sheet页的名字是否展示

public static void main(String[] args) throws IOException {

FileInputStream fins=new FileInputStream("E:\\Demo\\好气呀没事改什么版本.xls");

POIFSFileSystem  pfs=new POIFSFileSystem(fins);

HSSFWorkbook wb=new HSSFWorkbook(pfs);

/**

 * 抽取Excel里的内容为文本

 */

ExcelExtractor extractor=new  ExcelExtractor(wb);

extractor.setIncludeSheetNames(false);//不获取sheet页的名字

System.out.println(extractor.getText());

}

2.2.3内容读取与重写

FileInputStream fins = new FileInputStream("E:\\Demo\\好气呀没事改什么版本.xls");

POIFSFileSystem pfs = new POIFSFileSystem(fins);

HSSFWorkbook wb = new HSSFWorkbook(pfs);

HSSFSheet sheet=wb.getSheetAt(0);  // 获取第一个Sheet页

Row row=sheet.getRow(0); // 获取第一行

Cell cell=row.getCell(0); // 获取单元格

if(cell==null){

cell=row.createCell(3);// 如果为空自己创建一个

}

cell.setCellType(Cell.CELL_TYPE_STRING);//设置获取到的值类型

cell.setCellValue("测试单元格");// 修改此单元格内的值

HSSFRow createRow = sheet.createRow(2);// 手动创建一个新的单元格

createRow.createCell(0).setCellValue("手动添加");

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

System.out.println("好了");

 

2.3.1单元格样式

主要是单元格的对齐样式

/**

 * 创建一个单元格并为其设定指定的对其方式

 *

 * @param wb  工作簿

 * @param row        行

 * @param column    列

 * @param halign 水平方向对其方式

 * @param valign 垂直方向对其方式

 */

private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {

Cell cell = row.createCell(column); // 创建单元格

cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值为了效果  默认了

CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式

cellStyle.setAlignment(halign); // 设置单元格水平方向对其方式

cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对其方式

cell.setCellStyle(cellStyle); // 设置单元格样式

}

 

public static void main(String[] args) throws IOException {

Workbook wb = new HSSFWorkbook();// 新建一个工作簿

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

Sheet sheet = wb.createSheet("第一个sheet页");// 创建一个sheet页

Row row = sheet.createRow(2); // 创建一个行

/**

 * 可以设置行高

 */

row.setHeightInPoints(30);// 设置行高

/**

 * 这里的常量样式还是很多的 主要使用的就是这几种

 */

createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM);

createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER);

createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP);

createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP);

 

wb.write(fout);

fout.close();

}

 

水平对齐相关参数

1.如果是居中对齐就是 HSSFCellStyle.ALIGN_CENTER;

2.如果是右侧对齐就是 HSSFCellStyle.ALIGN_RIGHT;

3.如果是跨列举中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION;

4.如果是两端对齐就是 HSSFCellStyle.ALIGN_JUSTIFY;

5.如果是填充就是 HSSFCellStyle.ALIGN_FILL;

  垂直对齐相关参数

1如果是靠上就是 HSSFCellStyle.VERTICAL_TOP;、

2.如果是居中就是 HSSFCellStyle.VERTICAL_CENTER;

3.如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM;

4.如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY;

2.3.2单元格边框样式

单元格的高和是可以设置的setHeightInPoints（int） 设置行高，setDefaultColumnWidth(int)设置列宽,边框的颜色需要getindex不然会报错的

Row row = sheet.createRow(1); // 创建一个行 第一行

row.setHeightInPoints(40);

/**

 * 设施列宽

 */

sheet.setDefaultColumnWidth(40);

Cell cell = row.createCell(1);

cell.setCellValue(" 单元格边框");

/**

 * 单元格样式类

 */

CellStyle cellstyle = wb.createCellStyle();

cellstyle.setBorderBottom(CellStyle.BORDER_DOTTED);// 底部边框

/**

 * IndexedColors颜色索引类 选取完颜色之后还要getIndex()

 * 或者index都可以

 */

cellstyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());// 设置底部边框颜色

 

cellstyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框

cellstyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色

 

cellstyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框

cellstyle.setRightBorderColor(HSSFColor.RED.index);// 右边框颜色

 

cellstyle.setTopBorderColor(HSSFCellStyle.BORDER_DOTTED);// 顶部边框

cellstyle.setTopBorderColor(HSSFColor.YELLOW.index);// 顶部边框颜色

 

cell.setCellStyle(cellstyle);

 

wb.write(fout);

fout.close();

 

1. sheet.setColumnWidth(1, 31 * 256);//设置第一列的宽度是31个字符宽度
2. row.setHeightInPoints(50);//设置行的高度是50个点

 这里你会发现一个有趣的现象，setColumnWidth的第二个参数要乘以256，这是怎么回事呢？其实，这个参数的单位是1/256个字符宽度，也就是说，这里是把B列的宽度设置为了31个字符。

   设置行高使用HSSFRow对象的setHeight和setHeightInPoints方法，这两个方法的区别在于setHeightInPoints的单位是点，而setHeight的单位是1/20个点，所以setHeight的值永远是setHeightInPoints的20倍

  你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽或行高。

  其中边框类型分为以下几种：

边框范例图	对应的静态值
	HSSFCellStyle. BORDER_DOTTED
	HSSFCellStyle. BORDER_HAIR
	HSSFCellStyle. BORDER_DASH_DOT_DOT
	HSSFCellStyle. BORDER_DASH_DOT
	HSSFCellStyle. BORDER_DASHED
	HSSFCellStyle. BORDER_THIN
	HSSFCellStyle. BORDER_MEDIUM_DASH_DOT_DOT
	HSSFCellStyle. BORDER_SLANTED_DASH_DOT
	HSSFCellStyle. BORDER_MEDIUM_DASH_DOT
	HSSFCellStyle. BORDER_MEDIUM_DASHED
	HSSFCellStyle. BORDER_MEDIUM
	HSSFCellStyle. BORDER_THICK
	HSSFCellStyle. BORDER_DOUBLE

 

 

2.3.3单元格颜色

图案纹理是有很多种的在这里就不一一列举了,都是常量值，使用HSSFCellStyle获取

Cell cell=row.createCell(1);

cell.setCellValue("单元格颜色");

  CellStyle cellstyle = wb.createCellStyle();

  cellstyle.setFillForegroundColor(HSSFColor.RED.index);//设置图案颜色

 cellstyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());//设置图案背景颜色              

cellstyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);//设置图案纹理

cell.setCellStyle(cellstyle);

图案样式及其对应的值：

图案样式	常量
	HSSFCellStyle. NO_FILL
	HSSFCellStyle. ALT_BARS
	HSSFCellStyle. FINE_DOTS
	HSSFCellStyle. SPARSE_DOTS
	HSSFCellStyle. LESS_DOTS
	HSSFCellStyle. LEAST_DOTS
	HSSFCellStyle. BRICKS
	HSSFCellStyle. BIG_SPOTS
	HSSFCellStyle. THICK_FORWARD_DIAG
	HSSFCellStyle. THICK_BACKWARD_DIAG
	HSSFCellStyle. THICK_VERT_BANDS
	HSSFCellStyle. THICK_HORZ_BANDS
	HSSFCellStyle. THIN_HORZ_BANDS
	HSSFCellStyle. THIN_VERT_BANDS
	HSSFCellStyle. THIN_BACKWARD_DIAG
	HSSFCellStyle. THIN_FORWARD_DIAG
	HSSFCellStyle. SQUARES
	HSSFCellStyle. DIAMONDS

 

2.3.4单元格合并

cell.setCellValue("单元格合并测试");

/**

 * 单元格合并是由sheet控制的

 * 记住从0开始

 */

sheet.addMergedRegion(new CellRangeAddress(

1, // 起始行

2, // 结束行

1, // 其实列

2  // 结束列

));

  CellRangeAddress对象其实就是表示一个区域，其构造方法如下：CellRangeAddress(firstRow, lastRow, firstCol, lastCol)，参数的说明：

1. firstRow 区域中第一个单元格的行号
2. lastRow 区域中最后一个单元格的行号
3. firstCol 区域中第一个单元格的列号
4. lastCol 区域中最后一个单元格的列号

     提示： 即使你没有用CreateRow和CreateCell创建过行或单元格，也完全可以直接创建区域然后把这一区域合并，Excel的区域合并信息是单独存储的，和RowRecord、ColumnInfoRecord不存在直接关系。

 

2.4单元字体样式

HSSFCellStyle  style=wb.createCellStyle();

/**

 * 处理字体的类

 */

HSSFFont font = wb.createFont();

font.setFontName("华文行楷");//设置字体种类

font.setFontHeightInPoints((short)28);//设置字体大小

font.setColor(HSSFColor.RED.index);//设置字体颜色

font.setUnderline(FontFormatting.U_SINGLE);//设置下划线

font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标

font.setStrikeout(true);//设置删除线下划线选项值：

style.setFont(font);

cell.setCellStyle(style);

下划线取值：

单下划线 FontFormatting.U_SINGLE

 

双下划线 FontFormatting.U_DOUBLE

 

会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING

 

会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING

 

无下划线 FontFormatting.U_NONE

 

     上标下标选项值：

 

上标 FontFormatting.SS_SUPER

 

下标 FontFormatting.SS_SUB

 

普通，默认值 FontFormatting.SS_NONE

 

2.5单元格换行

换行的时候记得要设置行的高度不然会非常影响阅读

sheet.getDefaultRowHeightInPoints()获取sheet页默认行高

HSSFSheet sheet=wb.createSheet("第一个Sheet页");  // 创建第一个Sheet页

Row row=sheet.createRow(2); // 创建一个行

Cell cell=row.createCell(2);

cell.setCellValue("我要换行 \n 成功了吗？");

 

HSSFCellStyle style=wb.createCellStyle();

   /**

 * 设置可以换行

 */

  style.setWrapText(true);

  cell.setCellStyle(style);

    // 调整下行的高度

row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints());

// 调整单元格宽度

sheet.autoSizeColumn(2);

 

2.5用户自定义数据结构

1. //设置日期格式--使用Excel内嵌的格式
2. HSSFCell cell=row.createCell(0);
3. cell.setCellValue(new Date());
4. HSSFCellStyle style=workbook.createCellStyle();
5. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
6. cell.setCellStyle(style);
7. //设置保留2位小数--使用Excel内嵌的格式
8. cell=row.createCell(1);
9. cell.setCellValue(12.3456789);
10. style=workbook.createCellStyle();
11. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
12. cell.setCellStyle(style);
13. //设置货币格式--使用自定义的格式
14. cell=row.createCell(2);
15. cell.setCellValue(12345.6789);
16. style=workbook.createCellStyle();
17. style.setDataFormat(workbook.createDataFormat().getFormat("￥#,##0"));
18. cell.setCellStyle(style);
19. //设置百分比格式--使用自定义的格式
20. cell=row.createCell(3);
21. cell.setCellValue(0.123456789);
22. style=workbook.createCellStyle();
23. style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
24. cell.setCellStyle(style);
25. //设置中文大写格式--使用自定义的格式
26. cell=row.createCell(4);
27. cell.setCellValue(12345);
28. style=workbook.createCellStyle();
29. style.setDataFormat(workbook.createDataFormat().getFormat("[DbNum2][$-804]0"));
30. cell.setCellStyle(style);
31. //设置科学计数法格式--使用自定义的格式
32. cell=row.createCell(5);
33. cell.setCellValue(12345);
34. style=workbook.createCellStyle();
35. style.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00"));
36. cell.setCellStyle(style);

HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的区别： 当使用Excel内嵌的（或者说预定义）的格式时，直接用HSSFDataFormat.getBuiltinFormat静态方法即可。当使用自己定义的格式时，必须先调用HSSFWorkbook.createDataFormat()，因为这时在底层会先找有没有匹配的内嵌FormatRecord，如果没有就会新建一个FormatRecord，所以必须先调用这个方法，然后你就可以用获得的HSSFDataFormat实例的getFormat方法了，当然相对而言这种方式比较麻烦，所以内嵌格式还是用HSSFDataFormat.getBuiltinFormat静态方法更加直接一些。

 

3.使用Excel公式

3.1基本公式

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("sheet页码");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

HSSFCell cell = row.createCell(0);

cell.setCellFormula("2+3*4");//设置公式

cell = row.createCell(1);

cell.setCellValue(10);//设置第二个单元格的值

cell = row.createCell(2);

cell.setCellFormula("A1*B1");//设置公式

   

 

3.2SUM()函数

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue(1);

row.createCell(1).setCellValue(2);

row.createCell(2).setCellValue(3);

row.createCell(3).setCellValue(4);

row.createCell(4).setCellValue(5);

row = sheet.createRow(1);

row.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1"

row.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"

结果为

3.3日期函数

HSSFSheet sheet = wb.createSheet("sheet页码");// 创建工作表(Sheet)

HSSFCellStyle style=wb.createCellStyle();

style.setDataFormat(wb.createDataFormat().getFormat("yyyy-mm-dd"));

HSSFRow row = sheet.createRow(0);

/**

 * 设置行高和列宽方便阅读

 */

row.setHeightInPoints(40);

sheet.setDefaultColumnWidth(20);

Calendar date=Calendar.getInstance();//日历对象

HSSFCell cell=row.createCell(0);

date.set(2011,2, 7);

cell.setCellValue(date.getTime());

cell.setCellStyle(style);//第一个单元格开始时间设置完成

cell=row.createCell(1);

date.set(2014,4, 25);

cell.setCellValue(date.getTime());

cell.setCellStyle(style);//第一个单元格结束时间设置完成

cell=row.createCell(2);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置单元格为居中

    cell.setCellValue("相差多久");

    cell.setCellStyle(style);

cell=row.createCell(3);

/**

 * 两个日期之间相差多久

 * 设置函数

 */

cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")");

cell=row.createCell(4);

cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")");

cell=row.createCell(5);

cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"d\"),\"日\")");

结果为

以上代码中的公式说明：

     DATEDIF(A1,B1,\"y\") ：取得 A1 单元格的日期与 B1 单元格的日期的时间间隔。 ( “ y ” : 表示以年为单位 , ” m ”表示以月为单位 ; ” d ”表示以天为单位 ) CONCATENATE( str1,str2, … ) ：连接字符串。更多 Excel 的日期函数可参考：http://tonyqus.sinaapp.com/archives/286

3.4字符串相关函数

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue("abcdefg");

row.createCell(1).setCellValue("aa bb cc dd ee fF GG");

row.createCell(3).setCellFormula("UPPER(A1)");

row.createCell(4).setCellFormula("PROPER(B1)");

结果：

 

以上代码中的公式说明：

     UPPER( String ) ：将文本转换成大写形式

   PROPER( String ) ：将文字串的首字母及任何非字母字符之后的首字母转换成大写。

将其余的字母转换成小写。

          更多 Excel 的字符串函数可参考：http://tonyqus.sinaapp.com/archives/289

3.5if函数

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue(12);

row.createCell(1).setCellValue(23);

/**

 * 对于此公式说明就是三元运算符

 * 符合条件输出第一个 不符合第二个

 */

row.createCell(3).setCellFormula("IF(A1>B1,\"A1大于B1\",\"A1小于等于B1\")");

 

结果：

3.5.2CountIf和SumIf函数

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue(78);

row.createCell(1).setCellValue(30);

row.createCell(2).setCellValue(20);

row.createCell(3).setCellValue(51);

row.createCell(4).setCellValue(52);

row.createCell(5).setCellValue(53);

row.createCell(6).setCellValue(1);

row.createCell(7).setCellValue(10);

/**

 * 函数说明

 * A1:F1需要读取的计数

 * \">=60\"条件表达式

 */

row.createCell(8).setCellFormula("COUNTIF(A1:F1,\">=60\")");//对于所有满足条件的计数

row.createCell(9).setCellFormula("SUMIF(A1:F1,\">=60\")");//所有满足条件的和

3.6Lookup函数

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue(0);

row.createCell(1).setCellValue(59);

row.createCell(2).setCellValue("不及格");

row = sheet.createRow(1);

row.createCell(0).setCellValue(60);

row.createCell(1).setCellValue(69);

row.createCell(2).setCellValue("及格");

row = sheet.createRow(2);

row.createCell(0).setCellValue(70);

row.createCell(1).setCellValue(79);

row.createCell(2).setCellValue("良好");

row = sheet.createRow(3);

row.createCell(0).setCellValue(80);

row.createCell(1).setCellValue(100);

row.createCell(2).setCellValue("优秀");

row = sheet.createRow(4);

row.createCell(0).setCellValue(75);

row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)");

row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");

结果：

以上代码中的公式说明：

     LOOKUP(lookup_value,lookup_vector,result_vector) ，第一个参数：需要查找的内容，本例中指向 A5 单元格，也就是 75 ；第二个参数：比较对象区域，本例中的成绩需要与 $A$1:$A$4 中的各单元格中的值进行比较；第三个参数：查找结果区域，如果匹配到会将此区域中对应的数据返回。如本例中返回$C$1:$C$4 中对应的值。

 

 

可能有人会问，字典中没有 75 对应的成绩啊，那么 Excel 中怎么匹配的呢？答案是模糊匹配，并且 LOOKUP 函数只支持模糊匹配。 Excel 会在 $A$1:$A$4 中找小于 75 的最大值，也就是 A3 对应的 70 ，然后将对应的 $C$1:$C$4 区域中的 C3 中的值返回，这就是最终结果“良好”的由来。

     VLOOKUP(lookup_value,lookup_area,result_col,is_fuzzy ) ，第一个参数：需要查找的内容，这里是 A5 单元格；第二个参数：需要比较的表，这里是 $A$1:$C$4 ，注意 VLOOKUP 匹配时只与表中的第一列进行匹配。第三个参数：匹配结果对应的列序号。这里要对应的是成绩列，所以为 3 。第四个参数：指明是否模糊匹配。例子中的 TRUE 表示模糊匹配，与上例中一样。匹配到的是第三行。如果将此参数改为 FALSE ，因为在表中的第 1 列中找不到 75 ，所以会报“#N/A ”的计算错误。

 

另外，还有与 VLOKUP 类似的 HLOOKUP 。不同的是 VLOOKUP 用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。而HLOOKUP 用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

 

3.7随机函数

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellFormula("RAND()");// 取0-1之间的随机数

row.createCell(1).setCellFormula("int(RAND()*100)");// 取0-100之间的随机整数

row.createCell(2).setCellFormula("rand()*10+10");// 取10-20之间的随机实数

row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");// 随机小写字母

row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");// 随机大写字母

/**

 * 随机大小写字母 调用了char函数 参数是ASCII码

 */ row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

Int函数用来取整注意,int函数是不会四舍五入的，无论小数点后面是多少都会被舍弃

 

3.8获得公式返回值

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("第一个sheet页码");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(0);

row.createCell(0).setCellValue(7);//A1

row.createCell(1).setCellValue(8);//B1

HSSFCell cell=row.createCell(2);

cell.setCellFormula("A1*B1+14");//结果为56+14=70

 

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

 

HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);

/**

 * 这个是使用了公式的那个单元格

 */

cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的，则不必调用此方法

System.out.println("公式计算结果："+cell.getNumericCellValue());

 

4.poi使用图形

4.1画线

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)1, 0,(short)4, 4);

HSSFSimpleShape line = patriarch.createSimpleShape(anchor);

line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型

line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式

line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

 通常，利用POI画图主要有以下几个步骤：

          1. 创建一个Patriarch（注意，一个sheet中通常只创建一个Patriarch对象）；

          2. 创建一个Anchor，以确定图形的位置；

          3. 调用Patriarch创建图形；

          4. 设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。

     关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：

          dx1：起始单元格的x偏移量，如例子中的0表示直线起始位置距B1单元格左侧的距离；

          dy1：起始单元格的y偏移量，如例子中的0表示直线起始位置距B1单元格上侧的距离；

          dx2：终止单元格的x偏移量，如例子中的0表示直线起始位置距E5单元格左侧的距离；

          dy2：终止单元格的y偏移量，如例子中的0表示直线起始位置距E5单元格上侧的距离；

          col1：起始单元格列序号，从0开始计算；

          row1：起始单元格行序号，从0开始计算，如例子中col1=1,row1=0就表示起始单元格为B1；

          col2：终止单元格列序号，从0开始计算；

          row2：终止单元格行序号，从0开始计算，如例子中col2=4,row2=4就表示起始单元格为E5；

     最后，关于LineStyle属性，有如下一些可选值，对应的效果分别如图所示：

 

4.2画矩形圆形

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFPatriarch patriarch=sheet.createDrawingPatriarch();

HSSFClientAnchor anchor = new HSSFClientAnchor(255,122,255, 122, (short)1, 0,(short)4, 3);

HSSFSimpleShape hsss = patriarch.createSimpleShape(anchor);

hsss.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

hsss.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//设置边框样式

hsss.setFillColor(200, 0, 0);//设置填充色

hsss.setLineWidth(25400);//设置边框宽度

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

这里对于设置填充色的参数分析一下

一个参数的

hsss.setFillColor(HSSFColor.BLUE_GREY.index);

三个参数的就是颜色的配比

 

画圆形就是将这一行代码替换

hsss.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);

4.3画Grid

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test");// 创建工作表(Sheet)

HSSFRow row = sheet.createRow(2);

row.createCell(1);

/**

 * 设置行高列宽

 */

row.setHeightInPoints(240);

sheet.setColumnWidth(2, 9000);

int linesCount = 20;

HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

//因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间，这里采用比例的方式

double xRatio = 1023.0 / (linesCount * 10);

double yRatio = 255.0 / (linesCount * 10);

// 画竖线

int x1 = 0;

int y1 = 0;

int x2 = 0;

int y2 = 200;

for (int i = 0; i < linesCount; i++)

{

 HSSFClientAnchor a2 = new HSSFClientAnchor();

a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),

 (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),

 (int) (y2 * yRatio));

 HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);

shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

x1 += 10;

x2 += 10;

}

// 画横线

x1 = 0;

y1 = 0;

x2 = 200;

y2 = 0;

for (int i = 0; i < linesCount; i++)

{

 HSSFClientAnchor a2 = new HSSFClientAnchor();

a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),

 (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),

 (int) (y2 * yRatio));

 HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);

shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

y1 += 10;

y2 += 10;

}

 

4.4插入图片

需要addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_PNG)里的图片类型

要和inputstream里图片类型一致，还有如果报错的话将poi解压文件里lib目录下的所有lib包拷入项目lib里

HSSFWorkbook workbook=new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)

FileInputStream stream=new FileInputStream("E:\\Demo\\2.png");

 byte[] bytes = IOUtils.toByteArray(stream);

int pictureIdx = workbook.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_PNG);

HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)0, 0, (short)5, 5);

HSSFPicture pict = patriarch.createPicture(anchor,pictureIdx);

//pict.resize();//自动调节图片大小,图片位置信息可能丢失

4.5从Excel文件提取图片

/**

 * 文件地址

 */

FileInputStream inp = new FileInputStream("E:\\Demo\\poi.xls");

HSSFWorkbook workbook = new HSSFWorkbook(inp);//读取现有的Excel文件

List<HSSFPictureData> pictures = workbook.getAllPictures();

for(int i=0;i<pictures.size();i++)

{

 HSSFPictureData pic=pictures.get(i);

 String ext = pic.suggestFileExtension();

 if (ext.equals("png"))//判断文件格式

 {

 FileOutputStream png=new FileOutputStream("e:\\Demo\\Apache.png");

png.write(pic.getData());

png.close();//保存图片

 }

}

 

 

1. Excel的其他功能

5.1设置密码

HSSFSheet sheet= wb.createSheet("Test0");// 创建工作表(Sheet)

HSSFRow row=sheet.createRow(1);

HSSFCell cell=row.createCell(1);

cell.setCellValue("已锁定");

HSSFCellStyle locked = wb.createCellStyle();

locked.setLocked(true);//设置锁定

cell.setCellStyle(locked);

cell=row.createCell(2);

cell.setCellValue("未锁定");

HSSFCellStyle unlocked = wb.createCellStyle();

unlocked.setLocked(false);//设置不锁定

cell.setCellStyle(unlocked);

sheet.protectSheet("password");//设置保护密码

 

效果点击了已锁定之后，密码就是设置的密码

5.2数据有效性

HSSFWorkbook workbook=new HSSFWorkbook();

HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFRow row=sheet.createRow(0);

HSSFCell cell=row.createCell(0);

cell.setCellValue("日期列");

CellRangeAddressList regions = new CellRangeAddressList(1, 65535,0, 0);//选定一个区域

DVConstraint constraint = DVConstraint.createDateConstraint(DVConstraint . OperatorType . BETWEEN , "1993-01-01" ,"2014-12-31" , "yyyy-MM-dd" );

HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);

dataValidate.createErrorBox("错误", "你必须输入一个时间！");

sheet.addValidationData(dataValidate);

结果

 

CellRangeAddressList类表示一个区域，构造函数中的四个参数分别表示起始行序号，终止行序号，起始列序号，终止列序号。65535是一个Sheet的最大行数。另外，CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外，还可以设置成如下一些值，大家可以自己一个个去试看看效果：

验证的数据类型也有几种选择，如下：

5.3生成下拉式菜单

CellRangeAddressList regions = new CellRangeAddressList(0, 65535,0, 0);

DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "C++","Java", "C#" });

HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);

sheet.addValidationData(dataValidate);

5.4打印基本设置

HSSFSheet sheet= wb.createSheet("sheet");// 创建工作表(Sheet)

HSSFPrintSetup print = sheet.getPrintSetup();//得到打印对象

print.setLandscape(false);//true，则表示页面方向为横向；否则为纵向

print.setScale((short)80);//缩放比例80%(设置为0-100之间的值)

print.setFitWidth((short)2);//设置页宽

print.setFitHeight((short)4);//设置页高

print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张设置

print.setUsePage(true);//设置打印起始页码不使用"自动"

print.setPageStart((short)6);//设置打印起始页码

sheet.setPrintGridlines(true);//设置打印网格线

print.setNoColor(true);//值为true时，表示单色打印

print.setDraft(true);//值为true时，表示用草稿品质打印

print.setLeftToRight(true);//true表示“先行后列”；false表示“先列后行”

print.setNotes(true);//设置打印批注

sheet.setAutobreaks(false);//Sheet页自适应页面大小

 

5.5超链接

HSSFWorkbook wb=new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Test0");

CreationHelper createHelper = wb.getCreationHelper();

// 关联到网站

Hyperlink link =createHelper.createHyperlink(Hyperlink.LINK_URL);

link.setAddress("http://www.baidu.com/");

sheet.createRow(0).createCell(0).setHyperlink(link);

// 关联到当前目录的文件

link = createHelper.createHyperlink(Hyperlink.LINK_FILE);

link.setAddress("sample.xls");

sheet.createRow(0).createCell(1).setHyperlink(link);

// e-mail 关联

link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);

link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");

sheet.createRow(0).createCell(2).setHyperlink(link);

//关联到工作簿中的位置

link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);

link.setAddress("'Test0'!C3");//Sheet名为Test0的C3位置

sheet.createRow(0).createCell(3).setHyperlink(link);

 

FileOutputStream fout = new FileOutputStream("E:\\Demo\\poi.xls");

wb.write(fout);

fout.close();

System.out.println("好了");

结果

 

 

1. POI导出数据基本

Workbook wb=new HSSFWorkbook();//新建一个wb工作簿

Sheet sheet = wb.createSheet("学生的sheet页");

Row row=sheet.createRow(0);

//反射获取属性名作为表头  这里也可以让别人传 

Field[] Fields = clz.getDeclaredFields();

   for (int i = 0; i < Fields.length; i++) {

Fields[i].setAccessible(true);

Cell cell = row.createCell(i);

cell.setCellValue(Fields[i].getName());//获取到属性名赋值到单元格   

   }

//ls数据集合  这里需要注意数据量因为一个sheet页最多只能放置

65536行  但是到excel2010版本最大的行数为1048576所以要特别注意

for (int j = 0; j < ls.size(); j++) {

Row row1=sheet2.createRow(j);

for (int i = 0; i < Fields.length; i++) {

Fields[i].setAccessible(true);

Object object = Fields[i].get(ls.get(j));

Cell cell = row1.createCell(i);

cell.setCellValue(object.toString());

}

//这是普通的输出的方式   在页面上导出数据则需要

FileOutputStream fout=new FileOutputStream("E:\\Demo\\大量数据.xls");

wb.write(fout);

fout.close();

 

 

/**

 *

 * @Title: export

 * @Description: (通用的 输出表格)

 * @param response  用于发送数据的

 * @param wb   workbook工作簿

 * @param fileName  文件名

 * @throws Exception

 * @return void

 */

public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception {

response.setHeader("Content-Disposition",

"attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));

response.setContentType("application/ynd.ms-excel;charset=UTF-8");

OutputStream out = response.getOutputStream();

wb.write(out);

out.flush();

out.close();

}

 

 

 

1. POI导入数据

上传文件的时候要特别注意单元格内的值属性和数据库内的字段要相匹配  而且要注意取值时候的进行值的转换，而且进行值转换的时候要注意poi版本之间的差距，下方的值转换方法并不齐全  如果遇到复杂的值处理  需要补全

// 3.设置参数工厂，临时存放上传文件.

DiskFileItemFactory dfif = new DiskFileItemFactory();

// 4.设置工厂临时文件的大小

dfif.setSizeThreshold(1024 * 1024 * 3);

// 5.设置临时文件存储的位置

dfif.setRepository(new File(System.getProperty("java.io.tmpdir")));

// 5创建上传对象

ServletFileUpload fileUpload = new ServletFileUpload(dfif);

// 6设置上传文件大小

fileUpload.setFileSizeMax(1024 * 1024 * 50);

// 7设置表单的大小

fileUpload.setSizeMax(1024 * 1024 * 100);

// 8设置表单字符

fileUpload.setHeaderEncoding("utf-8");

// 10获取上传文件

List<FileItem> fileItems = fileUpload.parseRequest(request);

FileItem fileItem = fileItems.get(0);

 

/**

 * 获取文件后缀

 */

String str = fileItem.getName().substring(fileItem.getName().lastIndexOf("."));

if (str.equals(".xls") || str.equals(".xlsx")) {// 判断上传的文件是不是表格

POIFSFileSystem pfs = new POIFSFileSystem(fileItem.getInputStream());

HSSFWorkbook wb = new HSSFWorkbook(pfs);

/**

 * 获取到sheet的页数

 */

int sheets = wb.getNumberOfSheets();

HSSFSheet sheetAt = null;

List<String> ls = null;

for (int i = 0; i < sheets; i++) {

sheetAt = wb.getSheetAt(0);

 

/**

 * 遍历当前的sheet页里面的行 sheetAt.getLastRowNum() 获取到最后一行的位置

 */

 

for (int rowIndex = 1; rowIndex <= sheetAt.getLastRowNum(); rowIndex++) {

/**

 * 获取当前的行对象

 */

HSSFRow hssfRow = sheetAt.getRow(rowIndex);// 获取到当前行的对象

if (hssfRow == null) {

continue;

}

ls = new ArrayList<>();

/**

 * 同理遍历当前行里的所有列 hssfRow.getLastCellNum() 获取到最后一列的位置

 */

for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {

HSSFCell hssfCell = hssfRow.getCell(cellNum);// 获取到列对象

if (hssfCell == null) {

continue;

}

             

/**

 * 调用值处理的方法

 */

ls.add(getValue(hssfCell));

}

}

}

} else {

String jsonString = JSON.toJSONString("兄弟上传表格可否");

super.print(jsonString, response);

}}catch (Exception e) {

falg=false;

  throw new RuntimeException("上传失败了");

}

/**

 *

 * @Title: getValue

 * @Description: (判断单元格对象的值类型 这是3.9版本的写法)

 * @param hssfCell

 * @return

 * @return String

 */

private static String getValue(HSSFCell hssfCell) {

/**

 * 值类型的常量值 HSSFCell.CELL_TYPE_BOOLEAN

 */

if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {

return String.valueOf(hssfCell.getBooleanCellValue());

} else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

/**

 * 小的工具类 判断此单元格是不是时间格式

 */

if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {

/**

 * 取到的时候数字 因为时间格式不能显示给人家数字所以转换 转换格式

 */

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));

}

//如何后边出现.0要将其截取掉    不然进行数据处理的时候会有很大的问题

String str = String.valueOf(hssfCell.getNumericCellValue());

String stt = str.substring(str.lastIndexOf("."));

if (stt.endsWith(".0")) {

return str.substring(0, str.lastIndexOf("."));

}

return String.valueOf(hssfCell.getNumericCellValue());

} else {

return String.valueOf(hssfCell.getStringCellValue());

}

}