一、poi简介
Apache POI是Apache软件基金会的开放源码函式库，是用Java编写的免费开源的跨平台的 Java API，POI提供API给Java程序对Microsoft Office格式档案进行各种操作。
POI主要用到的类有HSSFWorkbook、HSSFSheet、HSSHRow、HSSFCell，HSSFWorkbook是Excel文件对象、HSSFSheet是Excel文件内的分页sheet对象、HSSHRow是行对象、HSSFCell是单元格对象，它们都在org.apache.poi.hssf.usermodel这个package里面。
二、java 操作excel之简单测试：
/*
第四讲：
1.字体处理 https://www.yiibai.com/apache_poi/apache_poi_fonts.html
2.读取和重写工作薄
3.单元格使用换行
4.创建用户自定义格式
*/
@Test
public void test444() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("firstSheet");
short rowNum = 0;
short colNum = 0;
DataFormat format = wb.createDataFormat();
Row row = sheet.createRow(rowNum++);
Cell cell = row.createCell(colNum);
cell.setCellValue(111.21);
CellStyle s = wb.createCellStyle();
s.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(s);
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(111.23);
s.setDataFormat(format.getFormat("#,##0.000"));
cell.setCellStyle(s);
FileOutputStream fileOut = new FileOutputStream("E:\\one.xls");
wb.write(fileOut);
fileOut.close();
}
@Test
public void test44() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("firstSheet");
Row row = sheet.createRow(2);
Cell cell = row.createCell(2);
cell.setCellValue("这里想要执行的是换行的操作啦啦啦来~");
CellStyle s = wb.createCellStyle();
s.setWrapText(true);
cell.setCellStyle(s);
//调整下行的高度和单元格的宽度
row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints());
sheet.autoSizeColumn(2);
FileOutputStream fileOut = new FileOutputStream("E:\\one.xls");
wb.write(fileOut);
fileOut.close();
}
@Test
public void test4() throws IOException {
InputStream in = new FileInputStream("E:\\two.xls");
POIFSFileSystem fs = new POIFSFileSystem(in);
Workbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.getSheetAt(0);
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("ABBAA");
FileOutputStream fileOut = new FileOutputStream("E:\\two.xls");
wb.write(fileOut);
fileOut.close();
}
/*
第三讲：
表格样式的网址【poi-3.17版本】：https://blog.csdn.net/m0_37353769/article/details/81872152
单元格的对其方式
边框处理
填充颜色+颜色操作
单元格合并
*/
//单元格合并
@Test
public void test33() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("firstSheet");
Row row = sheet.createRow(0);
//给三个单元格设置值！【String,double,boolean类型的值，0代表第一行】
Cell cell = row.createCell(0);
cell.setCellValue("第一单元格的值！");
//这个就是合并单元格
//参数说明：1：开始行 2：结束行 3：开始列 4：结束列
//比如我要合并 第二行到第四行的 第六列到第八列 sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));
sheet.addMergedRegion(new CellRangeAddress(0,3,0,1));
//创建一个字体处理类[粗细 样式 斜体 删除线]
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
CellStyle style = wb.createCellStyle();
style.setFont(font);
Cell cell0 = row.createCell((short)1);
cell0.setCellValue("测试字体样式！");
cell0.setCellStyle(style);
FileOutputStream fileOut = new FileOutputStream("E:\\one.xls");
System.out.println("aaa");
wb.write(fileOut);
fileOut.close();
}
@Test
public void test3()throws IOException {
Workbook wb= new HSSFWorkbook();
Sheet sheet = wb.createSheet("firstSheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("111111111111");
//设置样式
createCell(wb,row,(short)0,HorizontalAlignment.CENTER,VerticalAlignment.BOTTOM);
createCell(wb,row,(short)1,HorizontalAlignment.LEFT,VerticalAlignment.JUSTIFY);
createCell(wb,row,(short)2,HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
createCell(wb,row,(short)3,HorizontalAlignment.FILL,VerticalAlignment.TOP);
CellStyle style = wb.createCellStyle();
//边框颜色
style.setBorderBottom(BorderStyle.DOTTED);//下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setBorderTop(BorderStyle.DOTTED); //上边框
cell.setCellStyle(style);//设置单元格样式
FileOutputStream fileOut = new FileOutputStream("E:\\two.xls");
wb.write(fileOut);
fileOut.close();
}
private static void createCell(Workbook wb,Row row,short column ,HorizontalAlignment halign,VerticalAlignment valign)throws IOException {
//创建单元格指定对齐方式[工作薄，行，第几列，水平方向对齐方式【常量】，垂直对齐方式【常量】]
Cell cell = row.createCell(column);
cell.setCellValue(new HSSFRichTextString("Align two"));
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);//设置单元格样式
}
/*
第二讲：
遍历excel中的行和列
[一个：逐一遍历
另一个：使用方法获取所有值，排除不需要的值。]
*/
@Test
public void test22()throws IOException {
InputStream is = new FileInputStream("E:\\two.xls");
POIFSFileSystem fs= new POIFSFileSystem(is);
HSSFWorkbook wb= new HSSFWorkbook(fs);
HSSFSheet hssfSheet = (HSSFSheet) wb.getSheetAt(0);
ExcelExtractor excelExtractor = new ExcelExtractor(wb);
//这个是排除sheet名字
excelExtractor.setIncludeSheetNames(false);
System.out.println(excelExtractor.getText());
}
@Test
public void test2()throws IOException {
InputStream is = new FileInputStream("E:\\two.xls");
POIFSFileSystem fs= new POIFSFileSystem(is);
HSSFWorkbook wb= new HSSFWorkbook(fs);
HSSFSheet hssfSheet = (HSSFSheet) wb.getSheetAt(0);
if( hssfSheet == null) {
return;
}
//遍历行
for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow==null) {
continue;
}
//遍历列
for(int cellNum = 0;cellNum<hssfRow.getLastCellNum();cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if(hssfCell==null) {
continue;
}
System.out.println(" "+ getValue(hssfCell));
}
System.out.println();
}
}
private static String getValue(HSSFCell hssfCell) {
if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
}else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/*
第一讲：
1.poi简介
2.创建excel工作簿
3.创建新sheet
4.创建单元格并填写单元格内容。
5.设置单元格样式：日期类型的格式化
*/
@Test
public void test1()throws IOException {
Workbook wb= new HSSFWorkbook();
Sheet sheet = wb.createSheet("firstSheet");
Row row = sheet.createRow(0);
//给三个单元格设置值！【String,double,boolean类型的值，0代表第一行】
Cell cell = row.createCell(0);
cell.setCellValue("第一单元格的值！");
CreationHelper ctHelp = wb.getCreationHelper();
//单元格样式类
CellStyle cellStyle =wb.createCellStyle();
cellStyle.setDataFormat(ctHelp.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(false);
Cell three = row.createCell(3);
three.setCellValue(new Date());
three.setCellStyle(cellStyle);
Cell four = row.createCell(4);
four.setCellValue(Calendar.getInstance());
four.setCellStyle(cellStyle);
FileOutputStream fileOut = new FileOutputStream("E:\\two.xls");
wb.write(fileOut);
fileOut.close();
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
